Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Servlets insert into statement in sql using servlets

Related Tutorials


 
 

Share on Google+Share on Google+

insert into statement in sql using servlets

Advertisement
In this tutorial we are going to learn how we can insert a value from a html form in the table stored in the database.

insert into statement in sql using servlets

     

In this tutorial we are going to learn how we can insert a value from a html form in the table stored in the database. 

For inserting the values in the database table it is required to have a table in which we are going to insert the values. Now make one jsp page or  html page where we will insert the values. In this program we have made one simple enquiry form which will get stored in the database table and when the data gets entered into the database then you will get a message. Make one submit button for inserting the values into the database. Firstly this values will go to the controller and retrieved the variables enter in the form by the method getParameter() method of the request object. Pass a query to insert the values retrieved from the html form. To set the values into the database use setString() method. To retrieve the values from the database use getString() method of the PreparedStatement object.

The code of the program is given below:

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>
<body>
<form method="POST" action="/sqlStatServlet/ServletUserEnquiryForm">
  <!--webbot bot="SaveResults" 
  
U-File="fpweb:///_private/form_results.txt"
  S-Format="TEXT/CSV" S-Label-Fields="TRUE" -->
  <p>User Id:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
   <input type=
"text" name="userId" size="20"></p>
  <p>First Name: <input type="text" name="firstname" size="20"></p>
  <p>Surname: <input type="text" name="surname" size="20"></p>
  <p>Address1: <input type="text" name="address1" size="20"></p>
  <p>Address2:<input type="text" name="address2" size="20"></p>
  <p>Town:<input type="text"   name="town" size="20"></p>
  <p>City: <input type="text" name="country" size="20"></p>
  <p>Zip code:<input type="text" name="zipcode" size="20"></p>
  <p>  <input type="submit" value="Submit" name="B1"></p>
</form>
</body>
</html>

ServletUserEnquiryForm.java

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class ServletUserEnquiryForm extends HttpServlet{
  public void init(ServletConfig configthrows ServletException{
  super.init(config);
  }
  /**Process the HTTP Get request*/
  public void doPost(HttpServletRequest req, 
  HttpServletResponse res
throws ServletException,
  IOException
{
  String connectionURL = "jdbc:mysql://localhost/zulfiqar";
  Connection connection=null;
  ResultSet rs;
  res.setContentType("text/html");
  PrintWriter out = res.getWriter();
  //get the variables entered in the form
  String uId = req.getParameter("userId");
  String fname = req.getParameter("firstname");
  String sname = req.getParameter("surname");
  String address1 = req.getParameter("address1");
  String address2 = req.getParameter("address2");
  String town = req.getParameter("town");
  String county = req.getParameter("country");
  String zipcode = req.getParameter("zipcode")
  try {
  // Load the database driver
  Class.forName("org.gjt.mm.mysql.Driver");
  // Get a Connection to the database
  connection = DriverManager.getConnection
  (connectionURL, 
"root""admin")
  //Add the data into the database
  String sql = 
  "insert into emp_details values (?,?,?,?,?,?,?,?)"
;
  PreparedStatement pst = 
  connection.prepareStatement
(sql);
  pst.setString(1, uId);
  pst.setString(2, fname);
  pst.setString(3, sname);
  pst.setString(4, address1);
  pst.setString(5, address2);
  pst.setString(6, town);
  pst.setString(7, county);
  pst.setString(8, zipcode);
  int numRowsChanged = pst.executeUpdate();
  // show that the new account has been created
  out.println(" Hello : ");
  out.println(" '"+fname+"'");
  pst.close();
  }
  catch(ClassNotFoundException e){
  out.println("Couldn't load database driver: " 
  + e.getMessage
());
  }
  catch(SQLException e){
  out.println("SQLException caught: " 
 
+ e.getMessage());
  }
  catch (Exception e){
  out.println(e);
  }
  finally {
  // Always close the database connection.
  try {
  if (connection != nullconnection.close();
  }
  catch (SQLException ignored){
  out.println(ignored);
  }
  }
  }
}

web.xml file for this program:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app
 PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>
 <servlet>
 <servlet-name>Zulfiqar</servlet-name>
 <servlet-class>ServletUserEnquiryForm</servlet-class>
 </servlet>
 <servlet-mapping>
 <servlet-name>Zulfiqar</servlet-name>
 <url-pattern>/ServletUserEnquiryForm</url-pattern>
 </servlet-mapping>
</web-app>

The output of the program is given below:

Login.html form for the data input:

The output of the input data:

Download this example:

Advertisements

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 19, 2007

Related Tutorials

Discuss: insert into statement in sql using servlets   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:11
Anand
October 8, 2011
servlet code

I have to browse multiple files and store into database and HTML with java servlet code for email sending please send code as soon as possible
sujeeth reddy
December 10, 2011
thank you very much

hello, I had a problem with jsp inserting values in to mysql,but my problem solved with this. I used net beans, in that make sure that you put url pattern with .java extension.
sheetal
January 7, 2012
hello

this code helped a lot.thanks
rajesh
February 8, 2012
comment

good
vijay
February 8, 2012
insert into DB using jsp & servlet

i.create a jsp page <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <script type="text/javascript"> function getMonth(val) { var monthArray = new Array("JAN","FEB","MAR","APR","MAY","JUN", "JUL","AUG","SEP","OCT","NOV","DEC"); for (var i=0; i<monthArray.length; i++) { if (monthArray[i].toLowerCase() == val.toLowerCase()) { return(i); } } return(-1); } function checkDate(fld) { var success = true; var mo, day, yy, testDate; var val = fld.value; var re = new RegExp("[0-9]{1,2}[-][A-z]{3}[-][0-9]{4}", "g"); if (re.test(val)) { var delimChar = "-"; var delim1 = val.indexOf(delimChar); var delim2 = val.lastIndexOf(delimChar); day = parseInt(val.substring(0,delim1),10); mo = getMonth(val.substring(delim1+1,delim2),10); yy = parseInt(val.substring(delim2+1),10); testDate = new Date(yy,mo,day); if (testDate.getDate() == day) { if (testDate.getMonth() == mo) { if (!testDate.getFullYear() == yy) { alert("Invalid year Entry"); fld.focus(); fld.value=""; success = false; } if((yy<=1987)||(yy>=2020)){ alert("Invalid Year Entry"); fld.focus(); fld.value=""; success = false; } else { } } else { alert("Invalid month entry"); fld.focus(); fld.value=""; success = false; } } else { alert("Invalid day entry."); fld.focus(); fld.value=""; success = false; } } else { alert("Incorrect date format.Enter dd-mon-yyyy"); fld.focus(); fld.value=""; success = false; } return(success); } function checkForm() { var getBank=document.Accountform.bankName.value; var alphaExp = /^[\,\ \-0-9a-zA-Z]+$/; var message=""; r=document.Accountform.MitStudentId.value ; if(document.Accountform.MitStudentId.value==""){ document.getElementById("error_MitStudentId").innerText = "*Enter Mit Student Id"; message=message+"Enter the Mit Student Id"; } else if(r.charAt(0)=="0"){ document.getElementById("error_MitStudentId").innerText = "*Enter non zero Mit Student Id"; message=message+"Enter the Mit Student Id"; } else if(document.Accountform.MitStudentId.value.length!=6){ document.getElementById("error_MitStudentId").innerText = "*Enter 6 digits Mit Student Id"; message=message+"Enter the Mit Student Id"; } else{ document.getElementById("error_MitStudentId").innerText = ""; } x=document.Accountform.studentAccountNo.value; if(document.Accountform.studentAccountNo.value==""){ document.getElementById("error_studentAccountNo").innerText = "*Enter StudentID"; message=message+"Enter the Student Id"; } else if(x.charAt(0)=="0"){ document.getElementById("error_studentAccountNo").innerText = "*Enter non zero Student Id"; message=message+"Enter the Student Id"; } else if(document.Accountform.MitStudentId.value.length!=6){ document.getElementById("error_MitstudentAccountNo").innerText = "*Enter 6 digits Student Id"; message=message+"Enter the Student Id"; } else{ document.getElementById("error_MitstudentAccountNo").innerText = ""; } y=document.Accountform.MitAccountNo.value; if(document.Accountform.MitAccountNo.value==""){ document.getElementById("error_MitAccountNo").innerText = "*Enter AccountID"; message=message+"Enter the Account Id"; } else if(y.charAt(0)=="0"){ document.getElementById("error_MitstudentAccountNo").innerText = "*Enter non zero AccountID"; message=message+"Enter the AccountID"; } else if(document.Accountform.MitStudentId.value.length!=6){ document.getElementById("error_studentAccountNo").innerText = "*Enter 6 digits AccountID"; message=message+"Enter the AccountID"; } else{ document.getElementById("error_MitAccountNo").innerText = ""; } if(getBank=="none"){ document.getElementById("error_committedamount").innerText = "*Select Committed Amount "; message=message+"Select Committed Amount"; } else{ document.getElementById("error_committedamount").innerText = ""; } if(document.Accountform.dateOfOpening.value==""){ document.getElementById("error_dateOfOpening").innerText = "*Enter the Date"; message=message+"Enter the Date"; } else{ document.getElementById("error_dateOfOpening").innerText = ""; } if(getBank=="none"){ document.getElementById("error_bank").innerText = "*Select Bank"; message=message+"Select Bank"; } else{ document.getElementById("error_bank").innerText = ""; } if(document.Accountform.accountType.value=="none"){ document.getElementById("error_accountType").innerText = "*Select Type"; message=message+"Select Year"; } else{ document.getElementById("error_accountType").innerText = ""; } if(message=="") return true; else return false; } function isNumberKey(evt) { var charCode = (evt.which) ? evt.which : event.keyCode; if ( ( charCode > 47 && charCode < 58 ) || charCode ==127 || charCode ==8) return true; alert("Enter only the numbers"); return false; }window.history.forward(); /* function noBack() { window.history.forward(); } */ function checknull() { c=document.getElementById("commAmount").value; var numericExpression = /^[0-9]+$/; if ((c!=1000&& c!=2000 && c!=3000 && c!=4000 && c!=5000) || (c.match(numericExpression)==null)) { alert("Enter only multiples of 1000 and upto 5000"); flag=1; } } </script> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>NM</title> <link href="${pageContext.request.contextPath}/css/style.css" rel="stylesheet" type="text/css" /> </head> <body> <%-- <%if(session.getAttribute("name")==null||session.isNew()) response.sendRedirect("Home.jsp");%> <%session.setMaxInactiveInterval(20000);%> --%> <div id="header"> <a href="${pageContext.request.contextPath}/jsp/Home.jsp" rel="stylesheet"><img id="logo" src="${pageContext.request.contextPath}/images/logo-Mit1.JPG" alt=""width="200" height="200"/></a> <div id="menu"> <ul> <li><a href="${pageContext.request.contextPath}/jsp/MitWelcome.jsp">Home</a></li> <li><a href="${pageContext.request.contextPath}/jsp/Home.jsp">Log Out</a></li> </ul> </div> <h1><font size="5" color="white"><br/> N</font></h1> </div><!-- header --> <div id="main"><div id="main2"> <h3>Create Mit Account</h3> <body onload="noBack();" onpageshow="if (event.persisted) noBack();" onunload=""oncontextmenu="return false;"> </div> <form method="post" name="Accountform" onSubmit="return checkForm()" action="<%=request.getContextPath()%>/AccountController"> <input type="hidden" name="page" value="Mitaccount"/> <table border="0" STYLE="margin-left:150px;margin-top:20px;"> <tr> <td>MitStudentId <span class=mandatory>*</span></td> <td><input type="text" id="MitStudentId" name="MitStudentId" maxlength="6" style="width:153px;" onKeyPress="return isNumberKey(event)"/></td> <td><div id="error_MitStudentId"></div></td> </tr> <tr> <td>MitstudentAccountNo <span class=mandatory>*</span></td> <td><input type="text" id="MitstudentAccountNo" name="MitstudentAccountNo" maxlength="6" style="width:153px;" onKeyPress="return isNumberKey(event)"/></td> <td><div id="error_MitstudentAccountNo"></div></td> </tr> <tr> <td>MitAccountNo<span class=mandatory>*</span></td> <td><input type="text" id="MitAccountNo" name="MitAccountNo" maxlength="6" style="width:153px;" onKeyPress="return isNumberKey(event)"/></td> <td><div id="error_MitAccountNo"></div></td> </tr> <tr> <td>Bank name<span class=mandatory>*</span></td> <td> <select name="bankName" id="course" style="width:158px;"> <option value="none">----Select Bank----</option> <option >Indian Bank</option> <option >State Bank of India</option> <option >Punjab National Bank</option> <option >Syndicate Bank</option> <option >Oriental Bank of Commerce</option> </select> </td> <td><div id="error_bank"></div></td> </tr> <tr> <td>Committed Amount<span class=mandatory>*</span></td> <td> <select name="commAmount" id="course" style="width:158px;"> <option value="none">----Select Committed Amount----</option> <option >1000</option> <option >2000</option> <option >3000</option> <option >4000</option> <option >5000</option> </select> </td> <td><div id="error_committedamount"></div></td> </tr> <%-- <tr> <td>Committed Amount<span class=mandatory>*</span></td> <td><input type="text" id="commAmount" maxlength="4" name="commAmount" onchange="return checknull()"/></td> <td><div id="error_committedamount"></div></td> </tr> --%> <tr> <td>dateOfOpening<span class=mandatory>*</span></td> <td><input type="text" id="dateOfOpening" name="dateOfOpening" onChange="return checkDate(this)"/></td> <td><div id="error_dateOfOpening"></div></td> </tr> <tr> <td>AccountType<span class=mandatory>*</span></td> <td> <select name="accountType" id="course" style="width:158px;"> <option value="none">----Select Type----</option> <option >Saving Type</option> <option >Related Type</option> </select> </td> <td><div id="error_accountType"></div></td> </tr> <tr></tr><tr></tr><tr></tr><tr> <td><center><input type="submit" name="sub" value="CREATE"></center></td> <td><center><input type="Reset" name ="rename" value="RESET"></center></td> <td></td> </tr> </table> <br></br> </form></div> </body> </html> 2. servlet in dopost if (request.getParameter("page").equalsIgnoreCase("Mitaccount")) { AccountDao dao=new AccountDao(); try { String MitStudentId = request .getParameter("MitStudentId"); String studentAccountNo = request .getParameter("studentAccountNo"); String MitAccountNo = request .getParameter("MitstudentAccountNo"); String bankName = request.getParameter("bankName"); String committedAmount = request.getParameter("commAmount"); String dateOfOpening = request .getParameter("dateOfOpening"); String AccountType = request.getParameter("Accounttype"); AccountBean bean = new AccountBean(); bean.setMitStudentId(MitStudentId); bean.setStudentAccountNo(Integer.parseInt(studentAccountNo)); bean.setMitAccountNo(Integer.parseInt(MitAccountNo)); bean.setBankName(bankName); bean.setCommAmount(Integer.parseInt(committedAmount)); bean.setDateOfOpening(dateOfOpening); bean.setAccountType(AccountType); dao.checkhistory(bean); int history =bean.getHistory(); if(history==0) { AccountDao insertDao= new AccountDao(); try { insertDao.dbInsert(bean); request.getRequestDispatcher("/jsp/MitCreatedOut.jsp").forward(request, response); } catch (SQLException e) { e.printStackTrace(); } } else{ request.getRequestDispatcher("/jsp/MitCreateError.jsp").forward(request, response); } } catch (Throwable theException) { System.out.println(theException); } } 3.Data Access Object: public void dbInsert(AccountBean bean)throws Exception,SQLException{ Connection con=DbUtil.getConnection(); int balance=0; int history=1; @SuppressWarnings("unused") ResultSet rs=null; String sql="insert into BANKACCOUNT values(?,?,?,?,?,?,?,?,?)"; PreparedStatement state=con.prepareStatement(sql); state.setString(1,bean.getNsepStudentId()); state.setInt(2,bean.getStudentAccountNo()); state.setInt(3,bean.getNsepAccountNo()); state.setString(4,bean.getBankName()); state.setInt(5,bean.getCommAmount()); state.setString(6,bean.getDateOfOpening()); state.setString(7,bean.getAccountType()); state.setInt(8,balance); state.setInt(9,history); rs=state.executeQuery(); } public void checkhistory(AccountBean b) { Connection con=null; ResultSet rs=null; try { con=DbUtil.getConnection(); PreparedStatement state=con.prepareStatement("select history from NSEPBANKACCOUNT where nsepStudentId =?"); state.setString(1,b.getMitStudentId()); rs=state.executeQuery(); while(rs.next()) { int history=rs.getInt("history"); b.setHistory(history); } } catch(Exception e) { } finally{ try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } DB Connection: package com.nsep.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import com.sun.org.apache.xalan.internal.xsltc.compiler.Constants; import com.nsep.constants.*; @SuppressWarnings("unused") public class DbUtil { static Connection con; static String url; public static Connection getConnection() throws ClassNotFoundException, SQLException { String url = ConstantAccount.URL; String userName=ConstantAccount.USERNAME; String passWord=ConstantAccount.PASSWORD; Class.forName("oracle.jdbc.driver.OracleDriver"); con = DriverManager.getConnection(url,userName,passWord); return con; } } for constant class package com.nsep.constants; import java.sql.Connection; public class ConstantAccount { static Connection con; public static String URL = "jdbc:oracle:thin:@xxx:portno:servicename"; public static String USERNAME="xxx"; public static String PASSWORD="yyy"; }
chaitra
March 5, 2012
inserting data into ms access

i need complete procedure for executing this code in netbeans using ms-access as my back-end and java as front end,using servlets,DAO,beans....plz let me know as soon as possible,it is urgent....i am unable to run this code properly
rosy
June 20, 2013
error

I had downloaded a program from your link http://www.roseindia.net/servlets/ServletUserEnquiryForm.shtml i am getting an error given below SQLException caught: No suitable driver found for jdbc:SQL Server://http://localhost:8080/DEMO/ServletUserEnquiryForm Please suggest ThankYou
rosy
June 21, 2013
error

Hi i am using program dispaly at http://www.roseindia.net/servlets/ServletUserEnquiryForm.shtml but now my queries are 1) info are not store in my db sql server. 2)my output is Hello: null plz suggest me at rosy.bakshi1@yahoo.com
tejomay
July 12, 2013
J2EE

ITZ AWSME TO LEARN FROM HERE
DMCA.com