Java2all.com  




Share This Topic -

JDBC example with access:

To connect java application to access database we must have at least one database created in access.

Steps to create a database in MS-Access:

(1) Open Microsoft Office Access.

(2) Click on Blank Database.

(3) Type an appropriate name of database in File Name: box for example, HOD_DATA and click on Create Button.

(4) Create appropriate field name in table and value as per the field.

EX.:

Java2All.Com

(5) Right click on Table1 and select Save. Type the name of Table for example, DATA and click on OK button.

(6) Close the Table by right clicking on DATA and select Close. and Exit from Database

(7) Move this database to the appropriate drive where you want.

Now lets create TYPE 1 driver program for JDBC with access.

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Type_One 
{
	public static void main(String[] args) 
	{
		try
               {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Load Driver
			Connection con = DriverManager.getConnection("jdbc:odbc:HOD_DATA"); //Create Connection with Data Source Name : HOD_DATA
			Statement s = con.createStatement(); // Create Statement
			String query = "select * from Data"; // Create Query
			s.execute(query); // Execute Query 
			ResultSet rs = s.getResultSet(); //return the data from Statement into ResultSet
			while(rs.next()) // Retrieve data from ResultSet
			{
				System.out.print("Serial number : "+rs.getString(1)); //1st column of Table from database
				System.out.print(" , Name : "+rs.getString(2)); //2nd column of Table 
				System.out.print(" , City : "+rs.getString(3)); //3rd column of Table 
				System.out.println(" and Age : "+rs.getString(4)); //4th column of Table 
			}
			s.close();
			con.close();
		}
		catch (Exception e) 
                {
			System.out.println("Exception : "+e);
		}
	}
}

 
Output:

Serial number : 1 , Name : Ashutosh Abhangi , City : Dhoraji and Age : 27

Serial number : 2 , Name : Kamal Kotecha , City : Junagadh and Age : 24

Key point:

String which we are writing in Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") to load the driver.

String which we are writing in Connection con = DriverManager.getConnection("jdbc:odbc:HOD_DATA") to create connection with particular database.

Here HOD_DATA is our DSN (Data Source Name).

Steps for creating DSN for access.

(1) Go to Control Panel.

(2) Click on Administrative Tools(Window XP) for (Window 7) System and Security then Administrative Tools.

(3) Click on Data Sources (ODBC).

(4) Select MS Access Database and Click on Add button

Here in Windows XP you can easily add new DSN  but if you are getting an error or not able to add new DSN in Window 7 go to C:\Windows\SysWOW64 and then open odbcad32.exe and repeate step 4.

EX:

Java2All.Com

(5) Select Microsoft Access Driver (*.mdb,*.accdb) and Click on Finish button.

If you cant find the below driver then you should download JDBC ODBC Driver for ms access.

Java2All.Com

 

(6) Type Data Source Name, for example HOD_DATA then click on Select button in the Database frame.

(7) Select particular database which we saved on particular drive and created at beginning of this page (HOd_DATA). and click on OK button.

EX:

 

Java2All.Com

 

(8) Click on OK button and Check out the textarea of Data Sources Administrator. Now it contains a new DSN as a HOD_DATA.

 

Java2All.Com

 

(9) Click on OK button and close the Administrative Tools (Control Panel).

NOTE:

Do not confuse your self due to Database Name and Data Source Name, Here Both are same HOD_DATA but we can take different name too.

One more thing there may be a 32 bit or 64 bit issue like architecture mismatch so java2all recommend you that please make them all same.

Your java IDE tool, Microsoft Aceess and JVM or JDK all must be the same bit (32/64) version.

Now run the above program and check out the output.

PreparedStatement in access:

Lets now move to PreparedStatement example for access.

First of all lets assume that we have table named PA in access.

Java2All.Com

 

And our DSN is DATA.

 import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Prepare_Demo 
{
	public static void main(String[] args) 
	{
		try
		{	
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection con=DriverManager.getConnection("jdbc:odbc:DATA");
		
			PreparedStatement ps = con.prepareStatement("insert into PA (ID,Name,CITY,AGE)values(?,?,?,?)");
				
				ps.setInt(1,200);
				ps.setString(2, "hello");
				ps.setInt(4,101);
				ps.setString(3, "brd");
				
				ps.executeUpdate();
				System.out.println("inserted");
				con.close();		
		}
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
}

 
Output:
 
inserted
 
First run the above program with suitable table and DSN after running it refresh your access database and you can see one record inserted as per our program.
 
Java2All.Com
 
You can run PreparedStatement program for JSP too with dynamic data.
 
For this we will cretae two JSP file one for inserting data (simple form with text box as per our table).
 
Second JSP file contains logic for connecting data base as well as PreparedStatement logic for inserting data.
 
Insert_data.jsp
 <html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert DATA</title>
</head>
<body>
<form action="datams.jsp">
ID: <input type="text" name="ID"/>
NAME : <input type="text" name="NAME"/>
AGE : <input type="text" name="AGE"/>
CITY : <input type="text" name="CITY"/>
<input type="submit" value ="INSERT"> 
</form>
</body>
</html> 

 
Now insert value in text box as you want to insert in database as shown below.
 
Remember here too our DSN and Table are same as above program.

Java2All.Com

You can see the data here which i want to insert in our database.

datams.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<% 
	int id = Integer.parseInt(request.getParameter("ID"));
	int age = Integer.parseInt(request.getParameter("AGE"));
	String nm = request.getParameter("NAME");
	String ct = request.getParameter("CITY");
	try
	{	
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con=DriverManager.getConnection("jdbc:odbc:DATA");

		PreparedStatement ps = con.prepareStatement("insert into PA (ID,Name,CITY,AGE)values(?,?,?,?)");
		
		ps.setInt(1,id);
		ps.setString(2,nm);
		ps.setInt(4,age);
		ps.setString(3,ct);
		
		ps.executeUpdate();
		System.out.println("inserted");
		con.close();		
	}
	catch (Exception e) 
	{
		System.out.println(e);
	}
%>
</body>
</html> 

 
Output:
 
inserted
 
Now again refresh your table data and you can see one more new record which we inserted dynamically.
 
Java2All.Com
 
With access we can not do CallableStatement because access does not support stored procedure.
 
We will do it in mysql and oracle in next chapters.