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
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
