Introduction to JDBC

by Ismail Sirma


Posted on 9.8.2015 10:51:18


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:jdbc

  • 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!");
}
}

Blog Search

Message

Welcome to Blog of Ismail Sirma.

Back to List