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

Related Tutorials


 
 

Share on Google+Share on Google+

Insert data in mysql database through jsp using prepared statement

Advertisement
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

Advertisement

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: July 7, 2008

Related Tutorials

Discuss: Insert data in mysql database through jsp using prepared statement   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:19
Santosh
December 21, 2012
PreparedStatement (database) and Jsp

I am getting an error in Eclipse wen i am workin vth a project of two files index.html and display.jsp i hav used prepare statement to give the values in to the oracle database, i.e the error is prepared stmt's objs z nt resolve ps is nt reslove....help me and urgent.
shan
November 8, 2012
unable to connect (eclipse )

hi im using eclipse to use java program..in this program code not inserted in database , plz help me out of the problem regards sundhar
Bagavan
April 5, 2013
do really it works

do really it works?
main
April 28, 2013
Good Job

you are a master brother! thank a lot.
sathya
August 6, 2013
small error in this progrom

Error occur in this line "String queryString = "INSERT INTO stu_info(Name,Address,Phone) VALUES (?, ?, ?)"; change Address -> City, then only the program will provide the correct answer. (i.e)String queryString = "INSERT INTO stu_info(Name, City,Phone) VALUES (?, ?, ?)";
James
January 5, 2014
awesome just what i needed

Great job
Aman
July 7, 2014
unable to connect

hi im using eclipse to use java program..in this program code not inserted in database , plz help me out of the problem...
Kushan
June 19, 2014
Great!

Great! code is working perfectly.
nitesh
April 15, 2011
jsp hepl

sir, i am mca final year student a need your help for crating forum in jsp please send me the code and link to my mail nitesh gadekar
DMCA.com