JSP Database Connection Example

Some times it is required to connect database directly from JSP. There are 2 ways to connect database from JSP.

1. Using JAVA Code written in Scriptlets on JSP.

2. Using JAVA Standard Tag Library (JSTL).

JSP Database Connection Example using Scriptlets:

 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
                            "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>JSP Database Connection Example</title>
</head>
<body>
  <%
        //declare connection, statement and resutset objects 
        Connection connection = null;
	Statement statement = null;
	ResultSet resultSet = null;
	
	//load jdbc driver for mysql database
	try {
		Class.forName("com.mysql.jdbc.Driver");
	}catch(Exception e) {
		out.println("Unable to load Driver");
	}
	
	//Establish connection using DriverManager 
	try {
	 connection = 
        DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "root", "root");
	} catch (SQLException e) {
		out.println("Unable to connect to database");
	}
		
	//if connection is successfully established, create statement
        if(connection != null) {
	    try {
	       statement = connection.createStatement();
	    } catch (SQLException e) {
	       System.out.println("Unable to create statement");
	    }
	}
		
	//if statement is created successfully, execute query and get results
        if(statement != null) {
	   try {
	        resultSet = statement.executeQuery("SELECT * FROM products");
	   } catch (SQLException e) {
	        out.println("Unable to create statement");
	   }
        }
		
	//if resultset is received and is not empty,
   	// iterate over resultset to get values
        if(resultSet != null) {
	   try {
		while(resultSet.next()) {
		   out.println("Value in 1st column "+resultSet.getString(1));
		}
	   } catch (SQLException e) {
	        out.println("Unable to iterate over resultset");
	   }
	}
		
	try {
	     resultSet.close();
	     statement.close();
	     connection.close();
	} catch (SQLException e) {
	     e.printStackTrace();
	}
     }
%>
</body>
</html>

As you can see above, we connected to the database using JDBC written in scriptlets and printed the value in 1st column in products table. 


JSP Database Connection Example using JSTL:

There is another way and more simple way to connect database directly from JSP using JSTL SQL Tags. You can get more information with example about JSTL SQL Tags here.