Friday, May 27, 2011

JDBC and Microsoft Access

Howdy,

Here another most simple program.

How to make a database connection in Java class and Microsoft Access
There are following simple steps to do:

  1. To connect ms access with your program (of java) you need to create a database in ms access and then create a table in it. make sure that table name and database are different. so that you will not have confusion.
  2. Save the table and then database.
  3. Go to control panel.
  4. Go to Administrative Tools
  5. Click on Data Sources(ODBC)
  6. On User DSN(Data Source Name) tab you can easily see the MS Access Database in User Data Sources.
  7. Select the MS Access Database and click on configure (there will be ODBC Microsoft Access Setup open)
  8. Specify Data Source Name in Text field. The Description field is optional and you can leave it blank.
  9. In Database grid click on "select" and then go to path where database file is saved that was created by you.
  10. After this. Click on "Advanced " button and then specify a log in name and password and then click OK
  11. On System Database grid. click on none. and its done!! tadaaaaaaaa

I made an "employee" name database in MS Access and "emptable" named table which I am using here.
take a look at this.

This is the sample program that i had used for showing the database connectivity for a java program with Microsoft access.

import java.sql.*;
class Dbdemo
{
public static void main(String[] args)
{
try
{
try
{
/*Loading a database driver by calling Class.forName() with the Driver class name as an argument.*/
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(Exception x)
{
System.out.println("Unable to load driver class");
}
/*The getConnection method of DriverManager class returns an open connection you can use to create JDBC statements that pass your SQL statement to DBMS*/
Connection con =
DriverManager.getConnection("jdbc:odbc:MS Access Database","ravi","123");

String query = "select * from emptable";
/*Instantiating the object of Statement interface(as a reference) from Connection class object con by using createStatment() method. this statement object is used to send and execute SQL statements to a database.*/
Statement stm = con.createStatement();
/*executeQuery is a method of Statement Interface for Select queries. this method takes query as an argument.
Variable rs , which is an Instance of ResultSet, contains the details of Employees*/
ResultSet rs = stm.executeQuery(query);
System.out.println("Connection established");
/*ResultSet object maintains a cursor, which points to its current row of data. The next() method moves cursor to next row*/
while(rs.next())
{
/*The getXXX() method can get any any of the SQL types and they are automatically converted to a String.
In getXXX() method XXX represent many types that you can check by
"javap java.sql.ResultSet"*/
String id = rs.getString(1);
String nm = rs.getString(2);
String dept = rs.getString(3);
String sal = rs.getString(4);
System.out.println("epmloyee id: "+id+" employee name: "+nm
+" employee dept: "+dept+" employee salary: "+sal);
}
}catch(Exception e)
{
System.out.println(e);
}
}
}

No comments:

Post a Comment