This is first most commonly used database connectivity by entry level java applications. We have to write our code in JSP file and open connection in same file. Advance JSP database connection can include a connection JSP file in all related JSP file where database connection is required.
<%@ page language="java" import="java.sql.*"%>
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String sURL="jdbc:mysql://localhost:3306/DBName";
String sUserName="UserName";
String sPwd="Password";
conn = DriverManager.getConnection(sURL,sUserName,sPwd);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
if(conn!=null)
conn.close();
%>
Advance JSP connection by including connection file in connection required JSP
dbConn.jsp
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String sURL="jdbc:mysql://localhost:3306/DBName";
String sUserName="UserName";
String sPwd="Password";
conn = DriverManager.getConnection(sURL,sUserName,sPwd);
%>
include this file in JSP where connection is required
<%@ page language="java" import="java.sql.*"%>
<%@ include file="dbConn.jsp" %>
<%
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
if(conn!=null)
conn.close();
%>
2. javaBean database connectivity
javaBean database connection is similar to JSP connection file. But all connection code is written in a javaBean and this javaBean can be used in JSP file with JSP useBean tag to make connection with database.
DBConnection.java
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public Connection getDBConnection() throws Exception{
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String sURL="jdbc:mysql://localhost:3306/DBName";
String sUserName="UserName";
String sPwd="Password";
conn = DriverManager.getConnection(sURL,sUserName,sPwd);
return conn;
}
}
Use this javabean connection in JSP
<%@ page language="java" import="java.sql.*"%>
<%
Connection conn=dbConn.getDBConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
if(conn!=null)
conn.close();
%>
3. web.xml and servlet database connectivity
This is little good and advance technique of connectivity with database. We can define all database variables in web.xml and on load of servlet we can open connection in servlet’s init method through javaBean. In this method, ServletConfig object can be used to get variable parameter field name and value. This method is flexible and can change database name, server name, user and password in web.xml file without changing in the source code.
web.xml
Servlet to initialize the values
DBInit.java
package com.db;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import com.db.DBConnection;
public class DBInit extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
String jdbcDriverName=config.getInitParameter("DriverName");
String jdbcServerName=config.getInitParameter("ServerName");
String jdbcPort=config.getInitParameter("Port");
String jdbcDatabaseName=config.getInitParameter("DatabaseName");
String jdbcUserName=config.getInitParameter("UserName");
String jdbcPassword=config.getInitParameter("Password");
ServletContext sc = config.getServletContext();
// set in application scope of web application to access in future
DBConnection dbConn=null;
try{
dbConn=new DBConnection();
dbConn.setSDriverName(jdbcDriverName);
dbConn.setSServerName(jdbcServerName);
dbConn.setSPort(jdbcPort);
dbConn.setSDatabaseName(jdbcDatabaseName);
dbConn.setSUserName(jdbcUserName);
dbConn.setSPassword(jdbcPassword);
sc.setAttribute("dbConn", dbConn);
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
DBConnection.java
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public String sDriverName=null;
public String sServerName=null;
public String sPort=null;
public String sDatabaseName=null;
public String sUserName=null;
public String sPassword=null;
public String getSDriverName() {
return sDriverName;
}
public void setSDriverName(String driverName) {
sDriverName = driverName;
}
public String getSServerName() {
return sServerName;
}
public void setSServerName(String serverName) {
sServerName = serverName;
}
public String getSPort() {
return sPort;
}
public void setSPort(String port) {
sPort = port;
}
public String getSDatabaseName() {
return sDatabaseName;
}
public void setSDatabaseName(String databaseName) {
sDatabaseName = databaseName;
}
public String getSUserName() {
return sUserName;
}
public void setSUserName(String userName) {
sUserName = userName;
}
public String getSPassword() {
return sPassword;
}
public void setSPassword(String password) {
sPassword = password;
}
public Connection getDBConnection() throws Exception{
Connection conn = null;
Class.forName(sDriverName).newInstance();
String sURL ="jdbc:mysql://"+sServerName+":"+sPort+"/"+sDatabaseName;
conn = DriverManager.getConnection(sURL,sUserName, sPassword);
return conn;
}
}
Connection can be used in JSP as shown in this JSP file
<%@ page language="java" import="java.sql.*"%>
<%
Connection conn=dbConn.getDBConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
while(rs.next())
{
rs.getString("id");
}
%>
<%
if(conn!=null)
conn.close();
%>
4. properties file database connectivity
When you need more flexible environment of web application and database connectivity, properties file is another good option for database connectivity. You can change database variable name without changing restarting web server and without changing in java source code. In this technique we have to use properties file and define all database variables values in this file. This properties value can be accessed through ResourceBundle of java. Connection code can be written in javaBean or in JSP file to make connection.
This properties file should be copied in WEB-INF/classes folder
connection_config.properties
# Usually com.mysql.jdbc.Driver
driver.name=com.mysql.jdbc.Driver
# Usually localhost
server.name=localhost
# Usually 3306
port.no=3306
# Usually project
database.name=dbName
# Usually you define
user.name=UserName
# Usually you define
user.password=Password
javaBean is used to make connection
DBConnection.java
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ResourceBundle;
public class DBConnection {
public String sDriverName=null;
public String sServerName=null;
public String sPort=null;
public String sDatabaseName=null;
public String sUserName=null;
public String sPassword=null;
public Connection getDBConnection() throws Exception{
Connection conn = null;
ResourceBundle rb=ResourceBundle.getBundle("connection_config");
sDriverName=rb.getString("driver.name");
sServerName=rb.getString("server.name");
sPort=rb.getString("port.no");
sDatabaseName=rb.getString("database.name");
sUserName=rb.getString("user.name");
sPassword=rb.getString("user.password");
Class.forName(sDriverName).newInstance();
String sURL ="jdbc:mysql://"+sServerName+":"+sPort+"/"+sDatabaseName;
conn = DriverManager.getConnection(sURL,sUserName, sPassword);
return conn;
}
}
connection can be used in JSP file
<%@ page language="java" import="java.sql.*"%>
<%
Connection conn=dbConn.getDBConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
while(rs.next())
{
rs.getString("id");
}
%>
<%
if(conn!=null)
conn.close();
%>
5. XML file database connectivity
When your application is more focused on XML based pattern. You can use XML file to make database connection in java. XML is getting more popularity and easy to handle use and implement in web application. These results, we can define all database properties in XML node tree. These values can be getting from any XML parser. Connection code can be defined in javaBean and in JSP file. This is more flexible method. We do not have to restart web server when we do change in XML file and no change will be required to do in source code.
dbConnection.xml
DBConnection.java
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
import org.w3c.dom.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
public class DBConnection {
public String sDriverName=null;
public String sServerName=null;
public String sPort=null;
public String sDatabaseName=null;
public String sUserName=null;
public String sPassword=null;
public Connection getDBConnection() throws Exception{
Connection conn = null;
DocumentBuilderFactory dbf=DocumentBuilderFactory.newInstance();
DocumentBuilder db =dbf.newDocumentBuilder();
Document doc=db.parse("c:\\tomcat\\webapps\\myApp\\dbConnection.xml");
NodeList driverName = doc.getElementsByTagName("driverName");
NodeList serverName = doc.getElementsByTagName("serverName");
NodeList port = doc.getElementsByTagName("port");
NodeList databaseName = doc.getElementsByTagName("databaseName");
NodeList userName = doc.getElementsByTagName("userName");
NodeList pwd = doc.getElementsByTagName("pwd");
Node nDriverName = driverName.item(0);
sDriverName=nDriverName.getFirstChild().getNodeValue().trim();
Node nServerName = serverName.item(0);
sServerName=nServerName.getFirstChild().getNodeValue().trim();
Node nPort = port.item(0);
sPort=nPort.getFirstChild().getNodeValue().trim();
Node nDatabaseName = databaseName.item(0);
sDatabaseName=nDatabaseName.getFirstChild().getNodeValue().trim();
Node nUserName = userName.item(0);
sUserName=nUserName.getFirstChild().getNodeValue().trim();
Node nPwd = pwd.item(0);
sPassword=nPwd.getFirstChild().getNodeValue().trim();
Class.forName(sDriverName).newInstance();
String sURL ="jdbc:mysql://"+sServerName+":"+sPort+"/"+sDatabaseName;
conn = DriverManager.getConnection(sURL,sUserName, sPassword);
return conn;
}
}
Use in JSP
<%@ page language="java" import="java.sql.*"%>
<%
Connection conn=dbConn.getDBConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
if(conn!=null)
conn.close();
%>
6. Connection pooling in java database connectivity
Connection pooling gives good performance on database connectivity over all previous database connection. How it increase performance and efficiency of the database connectivity. When we open new connection in database and after using that connection we closed that connection and it returns again to the connection pool for next waiting. The next time when you need a connection with database, it takes connection from connection pool and reuses the previous connection.
This is database Connection Pooling with Tomcat
tomcat/config/context.xml
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
username="DBUserName"
password="DBPassword"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dbName?autoReconnect=true"
maxWait="1000"
removeAbandoned="true"
maxActive="30"
maxIdle="10"
removeAbandonedTimeout="60"
logAbandoned="true"/>
Copy mysql-connector-java.jar MySQL jdbc driver in tomcat/lib folder
DBConnection.java
package com.db;
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class DBConnection {
public static Connection getConnection() throws Exception
{
return getPooledConnection();
}
public static Connection getPooledConnection() throws Exception{
Connection conn = null;
try{
Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("No Context");
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/PooledDB");
if (ds != null) {
conn = ds.getConnection();
return conn;
}else{
return null;
}
}catch(Exception e) {
e.printStackTrace();
throw e;
}
}
}
connection can be used in JSP file
<%@ page language="java" import="java.sql.*"%>
<%
Connection conn=dbConn.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from tableName");
%>
<%
if(conn!=null)
conn.close();
%>
On the basis of all connection method, you can use best suitable method in your application to make database connection.
