JSP - SPLessons

Chapter 34

JSP Database Access

JSP Database Access

Working with the database is always an interesting task, while working with bigger projects, database is the mandatory resource why because without database applications will not exist. Before learning JSP database access developer should have knowledge in JDBC, MySQL or Oracle, Servlet, HTML. Already Splessons have discussed how doing the work with servlet database access, following is the link to see servlet database access. Servlet Database Access. Following is an example which describes the database connection with JSP.

Following is an example which describes the JSP Database Access. Here first create the database as follows.

ConnectionProvider.java

package bean;  
import java.sql.*;  
import static bean.Provider.*;  
  
public class ConnectionProvider {  
private static Connection con=null;  
static{  
try{  
Class.forName(DRIVER);  
con=DriverManager.getConnection(CONNECTION_URL,USERNAME,PASSWORD);  
}catch(Exception e){}  
}  
  
public static Connection getCon(){  
    return con;  
}  
  
}  

Class.forName() is used to register the driver. DriverManager.getConnection() method is used to establish the connection.
Provider.java

package bean;

public interface Provider {  
String DRIVER="com.mysql.jdbc.Driver";  
String CONNECTION_URL="jdbc:mysql://localhost:3306/test_db";  
String USERNAME="root";  
String PASSWORD="root";  
  
}  

Here the developer is using MySQL driver to connect to the database and user name and password also setted.
RegisterDao.java

package bean;  
  
import java.sql.*;  
  
public class RegisterDao {  
  
public static int register(User u){  
int status=0;  
try{  
Connection con=ConnectionProvider.getCon();  
PreparedStatement ps=con.prepareStatement("insert into SPLESSON values(?,?,?)");  
ps.setString(1,u.getUname());  
ps.setString(2,u.getUemail());  
ps.setString(3,u.getUpass());  
              
status=ps.executeUpdate();  
}catch(Exception e){}  
      
return status;  
}  
  
}  

The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query.
User.java

package bean;

public class User {  
private String uname,upass,uemail;  
  
public String getUname() {  
    return uname;  
}  
  
public void setUname(String uname) {  
    this.uname = uname;  
}  
  
public String getUpass() {  
    return upass;  
}  
  
public void setUpass(String upass) {  
    this.upass = upass;  
}  
  
public String getUemail() {  
    return uemail;  
}  
  
public void setUemail(String uemail) {  
    this.uemail = uemail;  
}  
  
}  

Here used Set and Get methods on the data members.
index.jsp

<form action="process.jsp">  
Name:<input type="text" name="uname" value="Name..." onclick="this.value=''"/><br/>  
Email ID:<input type="text" name="uemail"  value="Email ID..." onclick="this.value=''"/><br/>  
Password:<input type="password" name="upass"  value="Password..." onclick="this.value=''"/><br/>  </br>
<input type="submit" value="register"/>  
</form>  

Here created the text fields to enter the user name, password and also created register button.
process.jsp

<%@page import="bean.RegisterDao"%>  
<jsp:useBean id="obj" class="bean.User"/>  
  
<jsp:setProperty property="*" name="obj"/>  
  
<%  
int status=RegisterDao.register(obj);  
if(status>0)  
out.print("You are successfully registered");  
  
%>  

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>JSPDATABASEACCESS</display-name>
  <welcome-file-list>
    
    <welcome-file>index.jsp</welcome-file>
    
  </welcome-file-list>
</web-app>

Here code will be compile from the index.jsp .
Output
When compile the program following is the output will be displayed. Where enter the details.

When submit the details following is the message will be displayed.
Now check the database.

Summary

  • JSP Database Access – In this code MySQL database has been used so MySQL connector jar should be imported.
  • JSP Database Access – The performance of the application will be faster if you use Prepared Statement interface because query is compiled only once.