Introduction to JDBC

Introduction to JDBC

Java Database Connectivity (JDBC) is an Java API for managing database connection between Java application (Java apps, Applets, Servlets, EJBs, JSPs)  and various sources of databases (such as MySQL or MsSQL).

JDBC API helps accomplishing following tasks:

• Making a connection to a database.


• Creating SQL statements.


• Executing SQL queries in the database.


• Viewing & Modifying the records in the database.

JDBC Components:

• DriverManager:  Manages database driver for different types of databases and selects proper database driver for connection.


• Driver:  Interface handling communications with the database. DriverManager itself manages this object. (No need to be called).


• Connection:  Interface including all methods for reaching the database.


• Statement:  Objects being created from this interface to pass SQL statements which are to be planned and executed.


• ResulSet:  Resulted set of rows after the query being run.

Steps of Creating a JDBC application:

1. Import required packages (Ex: import java.sql.*)


2. Register required JDBC driver and initiliaze it (Ex: For MySQL, com.mysql.jdbc.Driver)


3. Open a connection by using DriverManager.getConnection() method


4. Create Statement object & Execute the query statement object.


5. Extract data from result set with ResultSet.getDATA_TYPE()  method


6. Close all database components (Statements, ResultSets, Connection etc.)

Requirements for JDBC:

In order to work with JDBC with a selected database type, according JDBC drivers package (For MySQL, it is mysql-connector-java-x.y.z-bin.jar file) should be added as a dependency or External Jar File.

Example of an JDBC Application:

Following is an example code for this topic. In the example, there is a MySQL database service running in local machine. In this server, there is a database named “machinelearning“. Our aim is to display all the values from the “decisiontree1” table.

import java.sql.*;
/**
* Created by ismail sirma on 9.8.2015.
*/
public class readData {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/machinelearning";
    // machinelearning is the database name
 
 
    //  Database login credentials
    static final String USER = "your-mysql-user";
    static final String PASS = "your-password";
 
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register required JDBC driver
            Class.forName("com.mysql.jdbc.Driver");
 
            //STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
 
            //STEP 4: Execute the query statement
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String sql;
            String table = "decisiontree1";
            sql = "SELECT * FROM " + table;
            ResultSet rs = stmt.executeQuery(sql);
 
            //STEP 5: Extract data from result set
            while(rs.next()){
                //Retrieve by column name
                String outlook = rs.getString("Outlook");
                String temperature = rs.getString("Temperature");
                String humidity = rs.getString("Humidity");
                Boolean windy = rs.getBoolean("Windy");
                String playtennis = rs.getString("PlayTennis");
 
                //Display values
                System.out.print("Outlook: " + outlook);
                System.out.print(", Temperature: " + temperature);
                System.out.print(", Humidity: " + humidity);
                System.out.println(", Windy: " + windy);
                System.out.println(", Play Tennis: " + playtennis);
            }
            //STEP 6: Close all database components
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se2){
            }// nothing we can do
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }//end of try
        System.out.println("All task have been finished!");
    }
}