A simple web application showing how to query a table in a database i.e performing a insert query using JDBC
Steps for querying a Database using JDBC -
1. Download the driver jar from Internet and place it in WEB-INF/lib folder of your Web application. Here I am using MySql as Database so I have downloaded the jar with name as : "mysql-connector-java-5.1.20-bin.jar"
2. Create a Database (am using MySql as backend) name as "javaee".
3. Create a table in "javaee" database as "customer". Run the create query stated below -
CREATE TABLE customer ( 'First Name' varchar(30) , 'Last Name' varchar(30) , 'E-Mail' varchar(45) , 'City' varchar(30) , 'password' varchar(30) NOT NULL, PRIMARY KEY ('password') )
4. Create a Servlet. It will perform necessary insert query. The code for servlet is shown below -
package com.hubberspot.jdbc; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.servlet.ServletConfig; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @WebServlet("/InsertJdbcQueryServlet") public class InsertJdbcQueryServlet extends HttpServlet { Connection connection = null; ResultSet resultSet = null; Statement statement = null; static String query = null; String url = null; String username = null; String password = null; public void init(ServletConfig config) throws ServletException { url = "jdbc:mysql://localhost:3306/javaee"; username = "root"; password = "root"; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); connection = DriverManager.getConnection(url, username , password); } catch (Exception e) { e.printStackTrace(); } } protected void doGet( HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException { doPost(request, response); } protected void doPost( HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException { query = "insert into customer () values " + "('Jonty','Magic','jonty@magic.com','Pune','123456');"; executeQuery(query); query = "insert into customer () values " + "('Java','Sun','java@sun.com','New York','456');"; executeQuery(query); query = "insert into customer () values " + "('Jesse','lool','jesse@lool.com','Jamaica','23456');"; executeQuery(query); query = "insert into customer () values " + "('Cameroon','Black','cameroon@black.com','Sydney','34');"; executeQuery(query); } private void executeQuery(String query) { try { statement = connection.createStatement(); statement.execute(query); } catch (SQLException e) { e.printStackTrace(); } } @Override public void destroy() { try { statement.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Output of the program :