Level of Understanding : Beginner Level (first time Java learners)

This post will tell you to handle database connection with Java. There are 4 types of drivers for database.

Type 1 (JDBC-ODBC Driver)

Client –> JDBC Driver –> ODBC Driver –> Database

Suitable only for Windows OS

ODBC to be installed on Client Side

Not Suitable for Applets

Type 2 (Native API)

Client –> JDBC Driver –> Vendor Client –> DB Library – Database

Better than Type 1 driver (Overhead is avoided)

Database Libraries has to be installed on Client Machine

Type 3 (JDBC Net)

Client –> JDBC Driver –> Middleware Net Server –> Any Database

Uses three tier architecture

Interface to Multiple databases

Middleware is Database Independent

Type 4 (Native Protocol)

Client –> Native Protocol JDBC Driver –> DB Server

Purely written in Java, so platform independent

Java Program to access Microsoft Access Driver

Step 1: Create a Data Source Name (DSN) in Windows

• Open Microsoft Access 2003 or 2007 or 2010
• Create a blank database and create a database table with fields and their properties
• Name the table and the file (One database file can have any number of tables)
• Go to Control Panel –> Administrative Tools –> Data Source (ODBC) –> User DSN
• Click Add and you will get a list of drivers, select the “Microsoft Access Driver (*.mdb,*.accdb)”
• Give a name to the DSN (do not give any blank space) and click “Select” and give the location of the DB file that you have created.

Here is the table which can be created using MS Access (the name of the table be student)

Basic Database Queries are

Select * from student; (this will fetch all the records from the student table)

Select * from student where regno=1001 (this will fetch the result with only one record)

insert into student (name, regno, age, marks) values (‘hari’,3003,30,67);

(the above query will insert a data into the table student)

Update student set marks=36 where regno=1001 (this query will change the marks field of student table and changed to 36)

zdff

//Java program to select all the records from a table

import java.sql.*;
class JdbcOdbcConnection
{
    public static void main(String[] args)
    {
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
    String sql=”Select * from student where marks > ’30′”;
        try
        {
            Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); //Driver to connect the ODBC JDBC Bridge
            con=DriverManager.getConnection(“jdbc:odbc:mydsn”); //Connect the Data Source Name
            System.out.println(“Database Created”);
            stmt=con.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs.next())  //till the ResultSet becomes null, fetch all the records
            {
                int n=rs.getInt(“name”);
                int i=rs.getInt(“number”);
                int j=rs.getInt(“age”);
                double marks=rs.getDouble(“marks”);
                System.out.println(“Name -> “+n + ” number -> ” +i +” Age -> “+j + ” Marks is “+marks);
            }
        }
        catch (Exception e)
        {
            System.out.println(“Unable to create Database”);
        }

    }
}

 

//Program to insert the data that is fed through a Terminal using Input Stream

import java.sql.*;
import java.io.*;

public class InsertQueryExample
{
    public static void main(String[] args) throws IOException
    {
        Connection con=null;
        Statement stmt=null;
        BufferedReader br=new BufferedReader(new InputStreamReader(System.in)); 
        System.out.println(“Enter the name of the customer”);
        String n1=br.readLine();
        System.out.println(“Enter the Customer ID of the customer”);
        String n2=br.readLine();
        System.out.println(“Initial Balance”);
        String n3=br.readLine();
        System.out.println(“Type of Account Savings/Current”);
        String n4=br.readLine();

String sql=”insert into account (custname,custid,balance,type) values (‘”+n1+”‘,’”+n2+”‘,’”+n3+”‘,’”+n4+”‘)”; //Query for inserting
        try
        {
            Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
            con=DriverManager.getConnection(“jdbc:odbc:mydsn”);
            System.out.println(“Database Created”);
            try
            {
                stmt=con.createStatement();
  System.out.println(“Inserting Status “+stmt.execute(sql)); //return false after inserting the record
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
            finally
            {//closing the connections, first statement is closed, then connection to DB is closed
                stmt.close();
                con.close();
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

    }
}