How to connect to MySql Database from Servlet?

Ads
 
  • Home
  • Servlets
  • How to connect to MySql Database from Servlet?

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.

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

Ads

Ads