Java Servlet Database Connectivity with MySQL

Java Servlet Database Connectivity with MySQL

 

Looking for Database Connectivity using Java Servlet then you have reached the right place, Here is the complete step by step guide to connect your servlet with database connectivity. If you want to learn Servlet Basic fundamental then please check out more article about java servlet, if you are new in java language then check out article on java programming tutorial You can also check out java tutorial video for easy quick start

 

Now Let’s start with JavaServlet Database Connectivity, Here I have taken an example of MySQL Database. MySQL is the world’ popular open source database, You need to install xampp or wamp for easy configuration of MySQL of else you can also install mysql form its official website. I hope you have already preconfigured mysql, Now Let’s Start!

 

Prerequisites to Execute Database Connectivity:

  • Download Jar and save to lib folder of Apache (ojdbc14-1.0.jar Required): Download Here
  • Update Classpath to following (Path in Bold are new): C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib\servlet-api.jar;C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib\jsp-api.jar;C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib\ojdbc14-1.0.jar; C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib\ com.mysql.jdbc_5.1.5.jar;.
  • Download and Install MySQL.

 

First Create Your Database and Tables

Table Name: student

FieldName Type
sid number(10) Primary Key
sname varchar2(20)

 

Query for Table:

CREATE TABLE “STUDENT”
(           “SID” NUMBER(4,0),
“SNAME” VARCHAR2(20),
CONSTRAINT “STUDENT_PK” PRIMARY KEY (“SID”) ENABLE
)

 Hello JDBC MySQL using Servlet Example

FilePath : C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\example03\WEB-INF\classes

FileName: ex03.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ex03 extends HttpServlet
{
protected void doGet(HttpServletRequest req,HttpServletResponse res)throws ServletException,IOException
{
PrintWriter pw=res.getWriter();
res.setContentType(“text/html”);
String tb=”student”;
pw.println(“Initializing Connection….”);

try
{

Class.forName(“com.mysql.jdbc.Driver”);
Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/test”,”adarsh”,”patel”);
Statement st=con.createStatement();
pw.println(“connection established successfully…!!”);
ResultSet rs=st.executeQuery(“Select * from “+tb);
pw.println(“<table border=1>”);

while(rs.next())
{
pw.println(“<tr><td>”+rs.getInt(1)+”</td><td>”+rs.getString(2)+”</td>”+”</tr>”);
}
pw.println(“</table>”);
pw.close();
}
catch (Exception e)
{
pw.println(“The error is:” + e.getMessage());
}
}
}

 

Hello JDBC using Servlet Example Explanation:

Line : Class.forName(“com.mysql.jdbc.Driver “);

Explanation : This Line if to load Driver for MySQL Database Connectivity with Java Servlet.

 

Line: Connection con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/test”,”root”,””);

Explanation: This Line is most important as it contains user id and password of MySQL. Here I have used user id : adarsh and password : patel. Only 2 Fields you need to change is userid and password. (Default userid for MySQL is root and password is blank);

 

FilePath : C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\example03\WEB-INF\

FileName: web.xml

<?xml version=”1.0″ encoding=”ISO-8859-1″?>
<web-app>
<display-name>Example 02 </display-name>
<description> JDBC Using Servlet </description>
<servlet>
<servlet-name>ex03</servlet-name>
<servlet-class>ex03</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>ex03</servlet-name>
<url-pattern>/ex03</url-pattern>
</servlet-mapping>

</web-app>

 

 

FilePath : C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\example03\

FileName: index.jsp

<%
response.sendRedirect(getServletContext().getContextPath()+”/ex03″);
%>

 Output

Java Servlet Database Connectivity with MySQL

Adarsh Patel

My Self Adarsh Suryakant Patel. Currently I am working as Assistant Professor at Parul Instiute of Engineering and Technology, MCA Department, Limda, Vadodara. Major Responsibility : * Lectures * Organizing Training and Expert Sessions for * Placement and Recruitment * Faculty Representative