Saturday, November 17, 2012

Java Database Connectivity(JDBC) using Microsoft Access Database And Data Source Name(DSN)


Create Database and Table using Microsoft Access

Step 1:Open Microsoft Access->Click Blank Database
File Name->Create


Step 2: DataSheet View After Creating Database
Click on view->Design View

When You Clicked on Design View it'll ask you to Save already open table with Save As(Table Name) Option

Give Field name and data type


Step 3: View->DataSheet View
It'll ask you to save table and press yes
DataSheet View of login Table After Save


Setting DSN Path
Step 1:Control Panel->All Control Panel Items->Administrative Tools->Data Source(ODBC)

Step 2:open Data Source(ODBC)
ODBC data source Administrator->User DSN
Click on Add Button
 Step 3:In Create New Data Source, select Microsoft Accesss Driver(*.mdb,*.accdb)
click finish

Step 4:In Odbc Microsoft Access Setup, give Data source Name and Select Database which you have created in Microsoft Access.

Step 5: Select Database from location where you have stored your Ms Access Database
click ok

Step 6: Odbc Microsoft Access Setup after selection of database
click ok


JDBC Code for Select,Insert and Delete data handle with Microsoft Access Database
MyData.java Source Code

 import java.sql.*;  
 public class MyData  
 { public static void main(String[] args)  
   {  
  try  
  {  
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
  Connection con=DriverManager.getConnection("jdbc:odbc:mydsn");  
  Statement st=con.createStatement();
  
  /*Select Query and Display Data  
  ResultSet rs=st.executeQuery("select * from login");  
  While(rs.next())  
  {System.out.println(rs.getInt(1));  
  System.out.println(rs.getString(2));  
  System.out.println(rs.getString(3));  
  }*/  

  /*Updation by deletion  
  int i=st.executeUpdate("delete from login where id="+1);  
  System.out.println(i+" Record(s) Deleted successfully");*/  

  /*Updation by Insertion*/  
  String sql="insert into login values("+1+",'Himanshu','him123')";  
  int j=st.executeUpdate(sql);  
  System.out.println(j+" Record(s) Inserted successfully");  
  con.close();  
  }  
  catch(Exception e)  
  {  
  System.out.println(e);  
  }  
   }  
 }  

You can also use following command to insert data in place of above insert command
 /*Updation by Insertion using PreparedStatement  
 PreparedStatement statement=connection.prepareStatement("insert into login values(?,?,?)");  
 connection.setAutoCommit(true);  
 statement.setInt(1,1);  
 statement.setString(2, "Himanshu");  
 statement.setString(3, "him123");  
       int i=statement.executeUpdate();  
       if(i!=0)  
       out.println(i+" Record(s) inserted Successfully"); */  

Execute code:
  
Output:

No comments:

Popular Posts