Home Jsp Insert data in mysql database through jsp using prepared statement

Insert data in mysql database through jsp using prepared statement
Posted on: July 7, 2008 at 12:00 AM

This is detailed jsp code that how to insert data into database by using prepared statement instead of statement.

Insert data in mysql database through jsp using prepared statement

This is detailed jsp code that how to insert data into database by using prepared statement instead of statement.

Create a database: First create a database named 'student' in mysql and table named "stu_info" in same database by sql query given below:

create database student;

create table stu_info (
ID int not null auto_increment,
Name varchar(20),
City varchar(20),
Phone varchar(15),
primary key(ID)
);

Create a new directory named "user" in the tomcat-6.0.16/webapps and WEB-INF directory in same directory. Before running this java code you need to paste a .jar file named mysql connector.jar in the Tomcat-6.0.16/webapps/user/WEB-INF/lib.

prepared_statement_query.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
"http://www.w3.org/TR/html4/loose.dtd" > 
      <%@ page import="java.sql.*" %> 
<%@ page import="java.io.*" %> 
<HTML>
<HEAD>
  <TITLE>insert data using prepared statement </TITLE>
</HEAD>
    <BODY bgcolor="#ffffcc">
  <font size="+3" color="green"><br>Welcome in www.roseindia.net !</font>
  <FORM action="prepared_statement_query.jsp" method="get">
    <TABLE style="background-color: #ECE5B6;" WIDTH="30%" >
         <TR>
	      <TH width="50%">Name</TH>
		  <TD width="50%"><INPUT TYPE="text" NAME="name"></TD>
	  </tr>
      <TR>
	     <TH width="50%">City</TH>
		 <TD width="50%"><INPUT TYPE="text" NAME="city"></TD>
	  </tr>
	  <TR>
	     <TH width="50%">Phone</TH>
		 <TD width="50%"><INPUT TYPE="text" NAME="phone"></TD>
	  </tr>
	  	  <TR>
	      <TH></TH>
		  <TD width="50%"><INPUT TYPE="submit" VALUE="submit"></TD>
	  </tr>
   </TABLE>
<%
   String name = request.getParameter("name");
   String city = request.getParameter("city");
   String phone = request.getParameter("phone");
   /* Create string of connection url within specified 
   format with machine name, 
    port number and database name. Here machine name id 
    localhost and database name is student. */
    String connectionURL = "jdbc:mysql://localhost:3306/student";
          // declare a connection by using Connection interface 
    Connection connection = null;
        // declare object of Statement interface that uses for 
    executing sql statements.
     PreparedStatement pstatement = null;
         // Load JBBC driver "com.mysql.jdbc.Driver"
     Class.forName("com.mysql.jdbc.Driver").newInstance();
          int updateQuery = 0;
     
     	 // check if the text box is empty
	 if(name!=null && city!=null && phone!=null){
	 		 // check if the text box having only blank spaces
	     if(name!="" && city!="" && phone!="") {
	                 try {
              /* Create a connection by using getConnection()
              method that takes parameters of string type 
              connection url, user name and password to connect 
		to database. */
              connection = DriverManager.getConnection
              (connectionURL, "root", "root");
                            // sql query to insert values in the secified table.
              String queryString = "INSERT INTO stu_info(Name,
              Address,Phone) VALUES (?, ?, ?)";
              	      /* createStatement() is used for create statement
              object that is used for 
		sending sql statements to the specified database. */
              pstatement = connection.prepareStatement(queryString);
              pstatement.setString(1, name);
			  pstatement.setString(2, city);
			  pstatement.setString(3, phone);
              updateQuery = pstatement.executeUpdate();
                            if (updateQuery != 0) { %>
	           <br>
	           <TABLE style="background-color: #E3E4FA;" 
                   WIDTH="30%" border="1">
		      <tr><th>Data is inserted successfully 
                    in database.</th></tr>
		   </table>
              <%
              }
            } 
            catch (Exception ex) {
            out.println("Unable to connect to batabase.");
   
               }
            finally {
                // close all the connections.
                pstatement.close();
                connection.close();
            }
	  }
	}
%>
  </FORM>
 </body> 
</html>

Save this code as a .jsp file named "prepared_statement_query.jsp" in the directory Tomcat-6.0.16/webapps/user/ and you can run this jsp page with url http://localhost:8080/user/prepared_statement_query.jsp in address bar of the browser.

Fill all the fields and click on submit button, that shows a response message. If any field is blank or only blank spaces are there page will remain same after clicking on submit button.

Download source code

Related Tags for Insert data in mysql database through jsp using prepared statement:
c, database, jsp, data, insert, state, using, int, this, ai, tab, js, statement, to, tail, base, e, il, in, as, sta, m, nt, par, j, prepared, ad, em, ase, me, how, into, detail, detail, s, sp, at, is, ha, pre, ea, ar, stat, cod, code, rt, s, s, tea, th, st, ab, hat, detailed, pr, ode, o

«Previous Index Next»