Insert & Retrieve data from M.S Access/SQL table using Java Servlet Programming

Home	»	Java	»	Advance Java	»	Java Servlet	»	Insert & Retrieve data from Table

The following are two Java Servlet programs Client Side and Server Side. One Program will insert a data into M.S.Access/SQL table and second will retrieve data from M.S.Access Table. For this first of all you need to create a M.S.Access database or SQL... but in this example I'm using M.S.Access. Create one table in M.S Access as shown below:

Table Definitions:

Table Name	: [ICTCashBook.mdb]-Download
Primary Key	: VoucherNo
Foreign Key	: Nil

Column Definition:

Column Name	Data Type
ID	AutoNumber
Session	Text
Month	Text
DD	Number
MM	Number
YY	Number
VoucherNo	Number
Articles	Memo
FromWhichPurchsed	Memo
Amount	Number
Remarks	Memo

Table Description:

Column Name	Description
ID	Create AutoNumber for image id
Session	insert Session for ex. 2011-12
Month	insert month in text...
DD	insert date in numeric
MM	insert month in numeric
YY	insert year in numeric
VoucherNo	insert unique numeric no
Articles	insert text
FromWhichPurchsed	insert text
Amount	insert numeric value
Remarks	insert text/numeric value

After create table in M.S. Access database now you will create DSN.

Following is the client side source code of the MonthlyExpenditureClient.java

/*
===========================================================
File Name : MonthlyExpenditureClient.java (Client-Side)
WebSite : http://javaproglang.blogspot.in/
Facebook : https://www.facebook.com/AdvanceJavaProgramming
Twitter ID : https://twitter.com/AdvanceJava
Created By : Bintu Chaudhary
===========================================================
*/
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.Calendar;

public class MonthlyExpenditureClient extends HttpServlet
{
        Connection conn;
        Statement stmt;
        ResultSet rs;
        PrintWriter pw;
        boolean stat;
        String sql,sql_insert;
        int int_pre_amt,int_total;
        int vno;

        public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
        {
                res.setContentType("text/html");
                pw=res.getWriter();
                String url="jdbc:odbc:khokhar"; //you can change dsn name                

                try
                {
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        url="jdbc:odbc:khokhar";
                        conn=DriverManager.getConnection(url);
                        stmt=conn.createStatement();

      // HTML Code Here....
                        
                        String sql="Select * from ComputerCashBookExpenditure";
                        rs=stmt.executeQuery(sql);
                        while(rs.next())
                        {
                                vno=rs.getInt("VoucherNo");
                        }
                        //vno=vno-1;
                        sql="Select * from ComputerCashBookExpenditure Where VoucherNo="+vno;
                        rs=stmt.executeQuery(sql);
                        pw.println("<Table  Border=1 Height=175 align=center Cellspacing=6 Cellpadding=6 BorderColor=blue>");
                        pw.println("<Tr><Th colspan=2><Font size=4>Last Entry of Expenditure</Font></Th></Tr>");
                        int tot_amt=0;
                        while(rs.next())
                        {
                                String sess     =rs.getString("Session");
                                String mon      =rs.getString("Month");
                                String dd       =rs.getString("DD");
                                String mm       =rs.getString("MM");
                                String yy       =rs.getString("YY");
                                int int_vno     =rs.getInt("VoucherNo");
                                String art      =rs.getString("Articles");
                                String frmpur   =rs.getString("FromWhomPurchsed");
                                int amount      =rs.getInt("Amount");
                                String remarks  =rs.getString("Remarks");
                
                                dd= dd +"/"+ mm +"/"+ yy;

                                pw.println("<Tr><Th align=left>Session</Th><Td align=center>"+sess+"</Td></Tr>");
                                pw.println("<Tr><Th align=left>Date</Th><Td align=center>"+dd+"</Td></Tr>");
                                pw.println("<Tr><Th align=left>Month & Year</Th><Td align=center>"+mon+"-"+yy+"</Td></Tr>");
                                pw.println("<Tr><Th align=left>VoucherNo</Th><Th align=center>"+int_vno+"</Th></Tr>");
                                pw.println("<Tr><Th align=left>Articles</Th><Td align=center>"+art+"</Td></Tr>");
                                pw.println("<Tr><Th align=left>From Whom Purchse</Th><Td align=center>"+frmpur+"</Td></Tr>");
                                pw.println("<Tr><Th align=left>Amount</Th><Th align=center>"+amount+"</Th></Tr>");
                                pw.println("<Tr><Th align=left>Remarks</Th><Td>"+remarks+"</Td></Tr>");                                                                

                        }
                        pw.println("</Table>");
                        pw.println("</Th></Tr>");
                        pw.println("</Table>");

                        pw.println("<BR>");
                        pw.println("<Table align=center cellspacing=2 width=35% Border=0>");
                        pw.println("<Tr><Th align=center><Input Type=Button OnClick='history.back();' Value='       Back       ' ></Th>");
                        pw.println("<Th align=center><Input Type=Submit Value='       Save       ' ></Th>");
                        pw.println("<Th align=center><U><input Type=Reset value='    Reset    '></U></Th>");
                        pw.println("<Th align=center><a href='javascript:location.reload(true)'><Input Type=Button Value='  Refresh  '></a></Th>");                        
                        //pw.println("<Th align=center><a href='http://localhost:8080/school/2011-12/ViewAllGrants.html'><Input Type=Button Value='View Grants'></a></Th></Tr>");

                        pw.println("</Table>");

                        pw.println("<BR>");
                        pw.println("<Table width=26% align=center>");
                        pw.println("<tr><th><a href='http://localhost:8080/school/2011-12/index2011_12.html'><b>Home</b></a>     | </th>");
                        pw.println("<th><a href='http://localhost:8080/school/2011-12/contactus.html'><b>Contact Me</b></a>     |    </th>");
                        pw.println("<th><a href='http://localhost:8080/school/2011-12/README.html'><b>Help</b></a></th></tr></Table>");
                
                        pw.println("<Center><BR>");
                        pw.println("<Font size=2 color=gray>");
                        pw.println("Copyright © 2011-12, School-Management System, Developed by <A Href='MailTo:bintu.chaudhary.mks@gmail.com'> <Font color=gray><B>Bintu Chaudhary </b></Font></A>  | Disclaimer");
                        pw.println("<BR>Best viewed at 1024 x 768 resolution with Google Chrome or Mozila Firefox 3.6 and higher not Internet Explorer</Font></Center>");
                        pw.println("</Body>");
                        pw.println("</html>");
                
                }
                catch(SQLException se)
                {
                        System.out.println(se.getMessage());
                        pw.println("<html><head><Title>Admission-Form 2011_12</Title></Head>");
                        pw.println("<Body background='background.bmp'>");                        
                        pw.println("<BR><BR><BR><Center><h1>Currently There is a Problem with the Database</h1></Center>");
                        pw.println("<BR><BR><Center><Input Type=Button OnClick='history.back();' Value='   Try Again   '></Center><BR><BR>");
                        pw.println("</Body>");
                        pw.println("</html>");                                
                }
                catch(ClassNotFoundException cnfc)
                {
                        System.out.println(cnfc.getMessage());                        
                }
        }
}

Complete Source Code of MonthlyExpenditureClient - Download

Now, Server-Side coding....

/*
===========================================================
File Name : MonthlyExpenditure.java (Server-Side)
WebSite : http://javaproglang.blogspot.in/
Facebook : https://www.facebook.com/AdvanceJavaProgramming
Twitter ID : https://twitter.com/AdvanceJava
Created By : Bintu Chaudhary
===========================================================
*/
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.Calendar;

public class MonthlyExpenditure extends HttpServlet
{
        Connection conn;
        Statement stmt;
        ResultSet rs;
        PrintWriter pw;
        boolean stat;
        String sql,sql_insert;
        int int_amount,int_totamount;
        String str_mm1;

        public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
        {
                res.setContentType("text/html");
                pw=res.getWriter();
                String url="jdbc:odbc:khokhar";

                String str_mm="Month";
                String str_yy="Year";
                
                pw.println("<SCRIPT LANGUAGE=JAVASCRIPT>");
                pw.println("function PrintIt(){");
                pw.println("document.all['Print'].style.visibility = 'hidden';");
                pw.println("window.print();");
                pw.println("document.all['Print'].style.visibility = 'visible';");
                pw.println("}</script>");

                String  STRSESS,STRDD,STRMM,STRYY,STRCS,STRAMT,STRTOT,STRREMARKS;                

                pw.println("<html><head><Title>Monthly Expenditure</Title></Head>");
                pw.println("<Body background='http://localhost:8080/school/school_images/background.bmp'>");                        
                pw.println("<Table width=100% align=center>");
                pw.println("<Tr><Th align=left><img src='http://localhost:8080/school/schoollogo2.JPG' alt=schoollogo.jpg border=0 width=550 height=65></Th></Tr>");
                pw.println("</Table><hr>");

                try
                {
                        String sess    =req.getParameter("sess");                
                        String month   =req.getParameter("month");
                        String dd      =req.getParameter("dd");
                        String mm      =req.getParameter("mm");                
                        String yy      =req.getParameter("yy");
                        String vno     =req.getParameter("voucherno");
                        String articles=req.getParameter("articles");                
                        String whompur =req.getParameter("whompur");
                        String amount  =req.getParameter("amount");
                        String remarks =req.getParameter("remarks");
                        
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        url="jdbc:odbc:khokhar";
                        conn=DriverManager.getConnection(url);
                        stmt=conn.createStatement();

                        int intvno=Integer.parseInt(vno);
                        int int_yy=Integer.parseInt(yy);
                        int_amount=Integer.parseInt(amount);

                        String sql_vno="Select * from ComputerCashBookExpenditure Where VoucherNo="+intvno;

                        rs=stmt.executeQuery(sql_vno);
                        stat=rs.next();
                        
      //actually below line will insert records into m.s access ComputerCashBookExpenditure named table or sql table...
      
                        sql_insert ="insert into ComputerCashBookExpenditure"+"(Session,Month,DD,MM,YY,VoucherNo,Articles,FromWhomPurchsed,Amount,Remarks)"+"values('"+sess+"','"+month+"',"+dd+","+mm+","+yy+","+vno+",'"+articles+"','"+whompur+"',"+int_amount+",'"+remarks+"')";

                        if(stat==true)
                        {
                                pw.println("<BR><BR><Font size=5 color=red><Center>Sorry!!! This VoucherNo. "+vno+" Already Exists<BR>Enter another VoucherNo</Center></Font><BR><BR>");
                                pw.println("<BR><BR><Center><Input Type=Button OnClick='history.back();' Value='   Try Again   '></Center>");

                        }                        
                        else
                        {
                                stmt.executeUpdate(sql_insert);
        
        //below line will open records from ComputerCashBookExpenditure Named Table...
                                String sql="Select * from ComputerCashBookExpenditure Where Session='"+sess+"' and YY="+int_yy+" and Month='"+month+"'";
                                rs=stmt.executeQuery(sql);
        
                                pw.println("<Table align=center Border=0>");
                                pw.println("<Tr><Th align=left><Font size=5>for the month of "+mm+" - "+yy+"</Font></Th><Th align=right><Font size=5><B>Expenditure</B></Font></Th></Tr>");
                                pw.println("</Table>");
        
                                pw.println("<Table align=center Border=1>");
                                pw.println("<Tr bgcolor=gray><Th>Date</Th><Th>Voucher No.</Th><Th>Full Particulars of Articles Purchased</Th>");
                                pw.println("<Th>From whom Purchsed</Th><Th>Amount</Th><Th>Remarks</Th></Tr>");

    int tot_amt=0;
                                while(rs.next())
                                {
                                        String strmonth        =rs.getString("Month");
                                        String strdd           =rs.getString("DD");
                                        String strmm           =rs.getString("MM");
                                        String stryy           =rs.getString("YY");
                                        String strvno          =rs.getString("VoucherNo");
                                        String strart          =rs.getString("Articles");
                                        String strpur          =rs.getString("FromWhomPurchsed");
                                        int stramt             =rs.getInt("Amount");
                                        //String strtotamt     =rs.getString("TotalAmount"); 
                                        String strremarks      =rs.getString("Remarks");
        
                                        strdd= strdd +"/"+ strmm +"/"+ stryy;
        
                                        pw.println("<Tr><Td>"+strdd+"</Td>");
                                        pw.println("<Td>"+strvno+"</Td>");
                                        pw.println("<Td>"+strart+"</Td>");
                                        pw.println("<Td>"+strpur+"</Td>");
                                        pw.println("<Td align=right>"+stramt+"</Td>");
                                        //pw.println("<Td>"+strtotamt+"</Td>");
                                        pw.println("<Td align=center>"+strremarks+"</Td></Tr>");

                                        tot_amt=tot_amt+stramt;
                                }
                                pw.println("<Tr><Th colspan=4>Total Amount</Th><Th align=right>"+tot_amt+"</Th><Th>---</Th></Tr>");
                                pw.println("</Table>");
                                pw.println("<BR><BR><BR>");
                                pw.println("<Center><Input Type=Button OnClick='history.back();' Value='       Back       '>");                                
                                pw.println("<input type='Button' name='Print' value='        Print        ' Onclick='PrintIt(this)'></Center>");                        
        
                                stmt.close();
                                conn.close();  
                        }
                }
                catch(SQLException se)
                {
                        System.out.println(se.getMessage());
                        pw.println("<html><head><Title>Admission-Form 2011_12</Title></Head>");
                        pw.println("<Body background='http://localhost:8080/school/school_images/background.bmp'>");                        
                        pw.println("<BR><BR><BR><Center><h1>Currently There is a Problem with the Database</h1></Center>");
                        pw.println("<BR><BR><Center><Input Type=Button OnClick='history.back();' Value='   Try Again   '></Center><BR><BR>");
                        pw.println("</Body>");
                        pw.println("</html>");                                
                }
                catch(ClassNotFoundException cnfc)
                {
                        System.out.println(cnfc.getMessage());                        
                }
        }
}

Complete Source Code of MonthlyExpenditure - Download

"Start -> All Programs -> Apache Tomcat 4.0 -> Start Tomcat." and minimize window

How to Install Apache Tomcat 4.0 then Click Here

and for Download Apache Tomcat 4.0 then Click Here

Now compile the above both code using javac compiler...

C:\jdk1.4\bin>javac MonthlyExpenditureClient.java -classpath "C:/Program Files/Apache Tomcat 4.0/common/lib/servlet.jar"

C:\jdk1.4\bin>javac MonthlyExpenditure.java -classpath "C:/Program Files/Apache Tomcat 4.0/common/lib/servlet.jar"

Now, copy "SimpleServlet.class" and "MonthlyExpenditure.class" Files from c:/jdk1.4/bin/ and then paste it under below dir...

"C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

or use below command

C:\jdk1.4\bin>copy MonthlyExpenditureClient.class "C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

C:\jdk1.4\bin>copy MonthlyExpenditure.class "C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

or you can use "*.class" for copy all classes...

C:\jdk1.4\bin>copy *.class "C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

Now Open any web browser and type client side URL into address bar as below...

http://localhost:8080/examples/servlet/MonthlyExpenditureClient

Inserting records into M.S Access Table

Opening records from M.S Access Table

7 comments:

UnknownOctober 19, 2016 at 3:52 AM
This is good information about retrive data from ms access to sql. More useful.

Microsoft Access to SQL Server Migration
UnknownMarch 21, 2017 at 4:33 AM
Good details about inserting and remove from database.

https://www.migrateto.net/access-to-sql-migration/
indiantrendingblogAugust 8, 2019 at 3:58 AM
Thanks For Sharing his nice post. Read Our Article in Fitness And Health Releted.
web Development Company in Surat
AnonymousDecember 1, 2019 at 8:34 PM
Good Post! Thank you so much for sharing the post, it was so good to read and useful to improve
Java Training in Electronic City
Lain LabNovember 5, 2020 at 10:29 PM
Visit our website accessdatabasedevelopment.co.uk/ for develop custom databases tailored to your business needs and providing your business with a competitive advantage by enabling you to increase efficiency, enhance quality of service and make the work lives of staff simpler. We ms access database design company in UK specialise in building bespoke business database solutions and form creation in ms access using industry standard tools such as Microsoft Access.
Mindful GuidesMay 31, 2021 at 11:53 PM
Visit our website accessdatabasedevelopment.co.uk/ for develop custom databases tailored to your business needs and providing your business with a competitive advantage by enabling you to increase efficiency, enhance quality of service and make the work lives of staff simpler. We microsoft access development in London specialise in building bespoke business database solutions and form creation in ms access using industry standard tools such as Microsoft Access.
Jeron MobikJanuary 20, 2022 at 7:06 AM
Thankyou for sharing such as nice post. It is really helpful for developer, Keep it sharing
Visit our website accessdatabasedevelopment.co.uk/ for develop custom databases tailored to your business needs and providing your business with a competitive advantage by enabling you to increase efficiency, enhance quality of service and make the work lives of staff simpler. We microsoft access database development London specialise in building bespoke business database solutions and form creation in ms access using industry standard tools such as Microsoft Access.
microsoft access developer London

	Section 1
	Home
	Introduction To Java
	Java Evolution
	Overview of Java
	Constants, Variables & Data Types
	Java Operators
	Branching Statements
	Looping Statements
	Section 2
	Classes, Objects & Methods
	Inheritance
	Array, String & Vectors
	Java Interfaces
	Java Packages
	Java Applet Programming
	Java Graphics Programming
Advance Java Programming
	Section 1
	Java AWT
	Java Event Handling
	Java File Handling
	Java MultiThreading
	Java Swing-I
	Java Swing-II
	Java Exception Handling
	Section 2
	Java Socket Programming
	Java Database Connectivity
	Java RMI
	Java Servlets
	Java Beans

Insert & Retrieve data from M.S Access/SQL table using Java Servlet Programming

7 comments:

Useful Links

Visitors

Java Tutorials

Follow by Email

Follow Advanced Java Programming on Facebook

Popular Programs

Follow Advanced Java Programming on Twitter