Ads

Tutorials

Roseindia Java Servlet Servlet Tutorials for Beginners Servlet tutorials: Learn Servlet Easily Cookies in Servlet Learn Servlet Life Cycle Servlet Tutorials What is Servlet in Java? getparametervalues in java Java HttpServletRequest RequestDispatcher in java Servlet Database Connectivity Example Servlet hello world example Difference Between Servlet and JSP Servlet Methods Sendredirect In Servlet Difference Between ServletConfig And ServletContext Servlet Context getServletContext Servlet Tutorial Servlet Life Cycle Servlet: What is it How to Get Referer URL Servlet Specifying Security for Basic Authentication using @ServletSecurity Specifying an Authentication Mechanism in the Deployment Descriptor Authenticating Users Programmatically Assembling web.xml from fragments Ordering of web fragments Introduction to web fragment @MultipartConfig @WebListener @WebFilter @WebInitParam @WebServlet AsyncListener Example Class AsyncEvent important methods AsyncContext complete method example AsyncContext Interface addListener AsyncListener Interface important methods AsyncContext Interface important methods AsyncContext Example

How to connect to MySql Database from Servlet?

In this example we will show you how to connect to MySQL database and perform select operation. You will learn the JDBC steps necessary to connect to the MySQL Database and execute the query.

Here we are using the MySQL jdbc driver for making the connection. You can download the jdbc driver for MySQL from http://dev.mysql.com/downloads/connector/j/5.1.html and then put the driver jar file into the classpath.

You have to first create the a table in MySQL database and then connect it through JDBC to show all the records present there.

MySql Table Structure:

CREATE TABLE `servlet` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(256) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `servlet` */

insert into `servlet`(`id`,`name`) values (1,'sandeep'),(2,'amit'),(3,'anusmita'),(4,'vineet');

Here is the code of Example:


// *DataBase Connectivity from the Servlet.

import java.io.*;

import java.util.*;

import javax.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;



public class DBConnection extends HttpServlet {

  public void service(HttpServletRequest request,

  HttpServletResponse response)

  throws IOException, ServletException{

  response.setContentType("text/html");

  PrintWriter out = response.getWriter();

  out.println("<html>");

  out.println("<head><title>Servlet JDBC</title></head>");

  out.println("<body>");

  out.println("<h1>Servlet JDBC</h1>");

  out.println("</body></html>");  

  // connecting to database

  Connection con = null;  

  Statement stmt = null;

  ResultSet rs = null;

  try {

  Class.forName("com.mysql.jdbc.Driver");

  con =DriverManager.getConnection 

  ("jdbc:mysql://192.168.10.59:3306/example","root","root");

  stmt = con.createStatement();

  rs = stmt.executeQuery("SELECT * FROM servlet");

  // displaying records

  while(rs.next()){

  out.print(rs.getObject(1).toString());

  out.print("\t\t\t");

  out.print(rs.getObject(2).toString());

  out.print("<br>");

  }

  } catch (SQLException e) {

 throw new ServletException("Servlet Could not display records.", e);

  } catch (ClassNotFoundException e) {

  throw new ServletException("JDBC Driver not found.", e);

  } finally {

  try {

  if(rs != null) {

  rs.close();

  rs = null;

  }

  if(stmt != null) {

  stmt.close();

  stmt = null;

  }

  if(con != null) {

  con.close();

  con = null;

  }

  } catch (SQLException e) {}

  }

  out.close();

  }

  }

Program Description:

The following query is used to fetch the records from database and display on the screen.

stmt = con.createStatement(); rs = stmt.executeQuery("SELECT * FROM servlet"); // displaying records while(rs.next()){ out.print(rs.getObject(1).toString());//You can also user rs.getString(1); out.print("\t\t\t"); out.print(rs.getObject(2).toString());//You can also user rs.getString(2); out.print("<br>"); }

Other JDBC statement you can understand easily.

Output:

Download Source Code

Spring Framework Training

R Programming Training

Share on Google+

How to connect to MySql Database from Servlet?

Posted on: June 20, 2008 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Related Tutorials

Roseindia Java Servlet

Servlet Tutorials for Beginners

Servlet tutorials: Learn Servlet Easily

Cookies in Servlet

Learn Servlet Life Cycle

Discuss: How to connect to MySql Database from Servlet? View All Comments

Post your Comment

Comments:5

NABEEL. AHMAD
February 3, 2013
How to connect to MySql Database from Servlet

there is some error like file parsing error while compiling this file in netbeans..if u know any solution than plz let me know it______ import java.io.*; import java.util.*; import javax.sql.*; import javax.servlet.*; import javax.servlet.http.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class connection extends HttpServlet { @Override public void service(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException{ response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println("<html>"); out.println("<head><title>Servlet JDBC</title></head>"); out.println("<body>"); out.println("<h1>Servlet JDBC</h1>"); out.println("</body></html>"); // connecting to database Connection con = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); con =DriverManager.getConnection ("jdbc:mysql://192.168.10.59:3306/example","root","root"); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT * FROM servlet"); // displaying records while(rs.next()){ out.print(rs.getObject(1).toString()); out.print("\t\t\t"); out.print(rs.getObject(2).toString()); out.print("<br>"); } } catch (SQLException e) { throw new ServletException("Servlet Could not display records.", e); } catch (ClassNotFoundException e) { throw new ServletException("JDBC Driver not found.", e); } finally { try { if(rs != null) { rs.close(); rs = null; } if(stmt != null) { stmt.close(); stmt = null; } if(con != null) { con.close(); con = null; } } catch (SQLException e) {} } out.close(); } }

ankit
October 20, 2011
java servlet

want to know how to connect java servlet with mysql

ramkumar
March 18, 2012
how to connect to mysql Database from servlet?

how to connect to mysql Database from servlet?

jovian
July 5, 2012
Thanks dear

Thanks dear your code has helped me understand servlet to mysql database communication

anurag
November 22, 2012
mysql servlet

very nice explanation of servlet, mysql interaction.

Ads