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 Source Code

 import java.sql.*;  
 public class MyData  
 { public static void main(String[] args)  
  Connection con=DriverManager.getConnection("jdbc:odbc:mydsn");  
  Statement st=con.createStatement();
  /*Select Query and Display Data  
  ResultSet rs=st.executeQuery("select * from login");  

  /*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");  
  catch(Exception 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(?,?,?)");  
 statement.setString(2, "Himanshu");  
 statement.setString(3, "him123");  
       int i=statement.executeUpdate();  
       out.println(i+" Record(s) inserted Successfully"); */  

Execute code:

No comments:

Popular Posts