Home Jsp Insert data in mysql database through jsp using prepared statement
Questions:Ask|Latest


 
 

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

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

Ask Questions?    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
AJAY ARANKERI
April 11, 2011
java

i wants to insert in database the current system date through the java on frame button event
nat
April 18, 2011
error

Getting error: Connection cannot be resolved to a type PreparedStatement cannot be resolved to a type
Gaurav Trivedi
September 29, 2011
inserting values in database

running given code i got fallowing error type Exception report message description The server encountered an internal error () that prevented it from fulfilling this request. exception org.apache.jasper.JasperException: An exception occurred processing JSP page /prepared_statement_query.jsp at line 91 88: } 89: finally { 90: // close all the connections. 91: pstatement.close(); 92: connection.close(); 93: } 94: } Stacktrace: org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:553) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:457) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:391) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334) javax.servlet.http.HttpServlet.service(HttpServlet.java:722) org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393) root cause java.lang.NullPointerException org.apache.jsp.prepared_005fstatement_005fquery_jsp._jspService(prepared_005fstatement_005fquery_jsp.java:144) org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) javax.servlet.http.HttpServlet.service(HttpServlet.java:722) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:419) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:391) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334) javax.servlet.http.HttpServlet.service(HttpServlet.java:722) org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393) note The full stack trace of the root cause is available in the Apache Tomcat/7.0.11 logs.
anuja
October 28, 2011
There is error in the query

The Query string should be String queryString = "INSERT INTO stu_info(Name, city,Phone) VALUES (?, ?, ?)"; instead of String queryString = "INSERT INTO stu_info(Name, Address,Phone) VALUES (?, ?, ?)";
Asha
November 22, 2011
jsp

Hai....if u r not getting the output,the problem made by this line i.e insert into stud_info(Name,City,Phone)values(?,?,?);...Rectify this line and get the output buddies...bye
Nagesh
February 24, 2012
query

Dear sir i hav a problem that... I hav created the sign up form and validated trough the form bean..for error message to display to the user,that is working fine and once registration is sucssfully completed it should display to the user that ---dear so and so u have successfully register(along with whatever details that the person entered should display)--this is also working fine.. I hav probelm in storing it in mysql (Database)table.... I hav problem that once the form is validated then only it should be display in another jsp page(showing details t the user) and also store it in database...so can anybody solve ma issue please...(without connecting to the database its working fine.....)
neha
April 10, 2012
about the code

i did everything as mentioned above but still its showing connection error. What should i do to get this code running? Am i making some silly mistake? please help me!
naouel
October 6, 2012
insert data in database through jsp

i have do every steps : create database and table ..ect and have copy this code but it dosen't work please help me
ANiket
October 9, 2012
Not able to connect to db

I am totally unable to connect to mysql. Getting message as "Unable to connect to database. " Please help
THOMASKUTTY BABY
December 29, 2012
mistake

in this code one mistake "insert into" statement has a field name Address that is not the field of table stu_info check it
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
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