Insert records into MS-Access database using JDBC

MS-Access is a type of database from Microsoft Office product. This is a file type database to access quickly and easily. In previous tutorial we learnt How to Connect MS Access Database using JDBC go through this one time to configure your driver and Connection.

For any database the we use the same procedure to insert records into database but DataSourceName(DSN) and driver class names will be unique. If you have multiple databases on your system then you should configure each one with different datasource names with JDBC.

Every JDBC operations are done with "Connection", "Statement" and "ResultSet" objects defined in package java.sql. Better you can create a class to call these object every time by using OOP concepts in java.

Here we have syntax and example to connect ms-access. we allocate a Connection object (called conn) via static method DriverManager.getConnection(database-url, db-user, password). The Java program uses a so-called database-URL to connect to the server:

For MS Access
// Syntax
Connection conn = DriverManager.getConnection(
"jdbc:odbc:{dsn-name}");
// Example
Connection conn = DriverManager.getConnection(
"jdbc:odbc:accdsn");

Program to Insert records into MS-Access database using JDBC

package com.javabynataraj.jdbc;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//http://javabynataraj.blogspot.com

public class InsertEmp {
 public static void main(String[] args)throws Exception {
  
  BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
  System.out.print("Enter student number: ");
  String tno=br.readLine();
  int eid=Integer.parseInt(tno.trim());
  System.out.print("Enter student name: ");
  String ename=br.readLine();
  System.out.print("Enter Salary: ");
  String esal=br.readLine();
  
  System.out.println("before loading driver");
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  System.out.println("---Driver Loaded---");
  Connection conn=DriverManager.getConnection("jdbc:odbc:accdsn","","");
  System.out.println("---Connection Established---"); 
  Statement stmt=conn.createStatement();
  ResultSet rs=stmt.executeQuery("select * from employee");
  
  stmt=conn.createStatement();
  String query="insert into employee values("+eid+",' "+ename+" ', "+esal+")";
  System.out.println(query);
  int res=stmt.executeUpdate(query);
  // insert query is a non-select statement so use excecuteUpdate
  if(res==0)
   System.out.println("record not inserted");
  else
   System.out.println("record Inserted");
  //  For each row, retrieve the contents of the cells with getXxx(columnName)
  while(rs.next()){
   System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3));
  }
  //Close the resources
  rs.close();
  stmt.close();
  conn.close();
 }
}

We given Statement object (called stmt) within the Connection via conn.createStatement( ).
The executeQuery returns the query result in a ResultSet object (called rs). ResultSet models the returned table, which can be access via a row cursor. The cursor initially positions before the first row in the ResultSet. rs.next( ) moves the cursor to the first row. We can then use rs.getXxx(columnName) to retrieve the value of a column for that row, where Xxx corresponds to the type of the column, such as int, float, double and String. The rs.next( ) returns false at the last row, which terminates the while-loop.

You may use rs.getString(columnName) to retrieve all types (int, double, etc).

For maximum portability, ResultSet columns within each row should be read in left-to-right order, and each column should be read only once via the getXxx() methods.

Create a query to insert values into employee table and call the executeUpdate statement using Statement(stmt) object. This will return interger value eighter 0 or 1 wether the data inserted or not. 1 is for successfull insertion and 0 is for failure.

You can check the data saved in your access file where you saved. Go through this tutorial.
Output: