Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Servlets How to connect to MySql Database from Servlet?

Related Tutorials


 
 

Share on Google+Share on Google+

How to connect to MySql Database from Servlet?

Advertisement
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.

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

Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: June 20, 2008

Related Tutorials

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

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
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.
DMCA.com