Establish MySQL database connection with JSP

Making of dynamic website required a database. The database may be MySQL, Oracle, SQL etc., in my case it is MySQL. In this tutorial, I will explain to you how to establish MySQL database connection with JSP (Java Server Pages). We will use eclipse editor for doing so. Also, you need the mysql_connector.jar file which has to be upload inside the WebContent\WEB-INF\lib folder.

Establish MySQL database connection with JSP

Java code – Database connection

Create a JSP page and write the below content in it. In this example, we are using MySQL database. So we need to know the following information about the MySQL database.

com.mysql.jdbc.Driver is the driver class for the MySQL database. “jdbc:mysql://localhost:3306/database_name” is the connection URL for the MySQL database where jdbc is the API, mysql is the database, localhost is the server name, 3306 is the port number, database_name is the name of the database, username and password are the username and password of the MySQL database.

<%
  Connection con = null;
  try{
    Class.forName("com.mysql.jdbc.Driver"); 
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name","username","password");
    Statement st= con.createStatement(); 
    ResultSet rs=st.executeQuery("select * from user_details");
    
    while(rs.next()) {
      out.print(rs.getString(2));
      .................
      ................. 
    }
    con.close();
  } catch (Exception e) {
    out.println(e.getStackTrace());
    con.close();
  }
%>

Class.forName returns the class object for the class with the specified name. DriverManager.getConnection() connects an application to a data source, which is specified by a database URL. The object of the ResultSet maintains a cursor pointing to the first row of a table. rs.next() method of ResultSet class help to move the cursor to the next row of a returned result set which is rs in our example. con.close() method close the open database connection.

Complete code – MySQL database connection with JSP

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"  %>
<!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>Establish MySQL database connection with JSP -- Mitrajit's Tech Blog </title>
  <style>
  h1 { clear:both; margin-bottom:30px; font-size:17px; }
  h1 a { font-weight:bold; color:#0099FF; }
  
  table { border:1px solid #ccc; width:600px; color:#fff; }
  table th {
    background-color:#0099FF;
    color:#fff;
  }
  td {
    background-color:#00CCFF;
    height:40px;
    text-align:center;
  }
  </style>
  </head>
  
  <body>
    <h1>Read the full article -- <a href="http://www.mitrajit.com/2016/11/establish-mysql-database-connection-jsp/" target="_blank">Establish MySQL database connection with JSP</a> in Mitrajit's Tech Blog</h1>
  <%
  Connection con = null;
  try{
    Class.forName("com.mysql.jdbc.Driver"); 
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name","username","password");
    Statement st= con.createStatement(); 
    ResultSet rs=st.executeQuery("select * from user_details");
    %>
    <table cellpadding="5" cellspacing="1">
      <tbody>
        <tr>
          <th>SL.</th>
          <th>NAME</th>
          <th>MOBILE</th>
          <th>COUNTRY</th>
        </tr>
    <%
    while(rs.next()) {
      %>
       <tr>
        <td align="center"><%=rs.getInt(1) %></td>
        <td><%=rs.getString(2) %></td>
        <td align="center"><%=rs.getString(3) %></td>
        <td><%=rs.getString(4) %></td>
      </tr>
      <% 
    }
    con.close();
    %>
      </tbody>
    </table>
  <%
  } catch (Exception e) {
    out.println(e.getStackTrace());
    con.close();
  }
  %>
  </body>
</html>

Update the JSP page with the above code and run it.

Establish MySQL database connection with JSP

Download the complete code from the download link and please like and share the tutorial link to others.

Leave a Reply

Your email address will not be published. Required fields are marked *