What is JDBC?
JDBC (Java Database Connectivity) is a set of classes/interfaces/methods to provide a standard interface for accessing relational databases. The standard was created by Sun Microsystems, and it allows third party vendors to create and provide their own JDBC drivers.
JDBC Drivers
JDBC drivers implement the defined interfaces in the JDBC API for interacting with the database server, e.g., using JDBC drivers enable you to open database connections and to interact with it by sending SQL commands.
Types of JDBC technology drivers
JDBC technology drivers fit into one of four categories.
- Type 1: A JDBC-ODBC bridge implements JDBC operations by translating them into ODBC operations. The Bridge implements JDBC for any database for which an ODBC driver is available. It is recommended to be used only when no other alternative is available.
- Type 2: A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS.
- Type 3: A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server.
- Type 4: A native-protocol fully Java technology-enabled driver converts JDBC calls into the vendor-specific database management system (DBMS) protocol so that client applications can communicate directly with the database server.
Connecting to Databases
Here is an example code connecting to databases. SampleQuery.java
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
public class SampleQuery {
public static void main(String[] args) {
query();
}
public static void query() {
Connection conn = null;
try {
// Register the JDBC driver
String driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
// Alternative to register the JDBC driver
//DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
// Open a connection
System.out.println("Connecting to the database...");
//Database to use
String jdbcUrl = "jdbc:mysql://localhost/fly-four-less";
//Enter database username
String user = "username";
//Enter database password
String password = "password";
//Connect with collected information
conn = DriverManager.getConnection(jdbcUrl, user, password);
// Execute a query
String sql = "SELECT * FROM price";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// Extract data from the result set
while(rs.next()) {
int pcId = rs.getInt("PC_ID");
int pcLevel = rs.getInt("PC_LEVEL");
int price = rs.getInt("PRICE");
Date fromDate = rs.getDate("FROM_DATE");
Date toDate = rs.getDate("TO_DATE");
//Display the result set
System.out.println("---------------------------------");
System.out.println("PC_ID - " + pcId);
System.out.println("PC_LEVEL - " + pcLevel);
System.out.println("PRICE - " + price);
System.out.println("FROM_DATE - " + fromDate);
System.out.println("TO_DATE - " + toDate);
System.out.println("---------------------------------");
System.out.println("");
}
// Close resources
System.out.println("Closing the resources...");
rs.close();
stmt.close();
conn.close();
conn = null;
} catch(SQLException se) {
// Handle SQL exception
se.printStackTrace();
} catch(Exception e) {
// Handle other exceptions
e.printStackTrace();
} finally {
// Close resources
try {
if(conn != null) {
conn.close();
}
} catch(SQLException se) {
se.printStackTrace();
}
}
}
}
The essential steps connecting to the database using JDBC are as follows.
1) Choose the driver
Type 4 drivers will generally be the most efficient choice, since they establish a direct connection rather than a call relay.
If the Type 4 driver is not available for the particular database you are using, it is a choice between Type 2 and Type 3. Type 2 drivers are not used often and there are not many such drivers around.
You will seldom need a Type 1 driver unless you are already using a database that supports ODBC but not JDBC.
2) Import the packages
The basic package that you must import is java.sql. Additionally, you may need vendor specific packages, e.g. oracle.jdbc.driver and oracle.sql if you are using Oracle as the database.
3) Register the driver
You must register your installed driver with your program. You do this with the static method registerDriver() from the DriverManager class. This class provides a basic service for managing JDBC drivers.You need to register the driver once in the application with a line of code
e.g//DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
The alternative is using Class.forName(String driverClassName) method. TheClass.forName() method affords more flexibility since it accepts a String
parameter representing the driver class name. This enables you to obtain driver value at run time from the property file. The following snippet
illustrates the use of Class.forName() method
String driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
4) Open the connection
To open a connection to the database, you use the static methodgetConnection() of the DriverManager class. This method returns a Connection object which needs the inputs of userid, password, and the connect string that identifies the type of driver, schema, and database to use. The code is as follows:Connection conn = null;
conn = DriverManager.getConnection(jdbcUrl, user, password);
5) Create the statement
After you connect successfully to the database, you need to create a Statement object from the Connection object. The standard syntax to use is:Statement stmt = conn.createStatement();
The java.sql package, from where the Statement class comes from, throws a SQLException. You must either create a class that throws this exception or handle the exception in the try-catch block.
6) Execute the query
To query the database, you use the executeQuery() method of the Statement object. This method will take a SQL statement as a parameter and returns an object of the ResultSet class. This method will populate the result set with the results from the query passed in. See the line of code below.ResultSet rs = stmt.executeQuery(sql);
7) Process the result set (Not required if the result set is not a concern)Once you have the ResultSet populated, you will need to process the results constructively. When the ResultSet object is first populated, the “pointer” to the rows in the ResultSet is positioned just before the first row. To put the pointer to the next row you use the next () method of the ResultSet class. This needs to be done at least once for any result to be produced. So if the query will only return one result, you still need to call the next() method. The next() method will automatically detect the end of the result set when it is reached.
To pull data out of the ResultSet you use one of the various getXXX() methods provided by the ResultSet class, where the XXX refer to a Java datatype, e.g. getString(...)
Check out the example code below:java
while(rs.next()) {
int pcId = rs.getInt("PC_ID");
int pcLevel = rs.getInt("PC_LEVEL");
.....................................
}
8) Close the result set (Not required if the result set is not a concern)
You must explicitly close the ResultSet and Statement objects after you finish using them. If you do not explicitly close your ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database.
Closing a result set or statement releases the corresponding cursors in the database. The code for this operation is as follows:rs.close();
stmt.close();
Note: When you close a Statement object that a given Connection object creates, the connection itself remains open.
9) Close the connection
You must close your connection to the database once you finish the work. Use the close() method of the Connection class to do this.
The code for our purposes is as follows:conn.close();
Note: It is a good practice to check whether the connection object is null before you close it.
Using JDBC Statements
The JDBC Statement interface defines the methods and properties that enable you to send commands to the database or retrieve data from it.
Three methods that interest us are execute(), executeUpdate() and executeQuery()
These methods act as the conduits to the database for sending commands and retrieving data.
– executeQuery(): Used to query the database with SELECT statements. A result set will be returned.
– executeUpdate(): Used to submit INSERT, UPDATE, DELETE, or DDL statements. The method returns the update count of the rows affected by the statement or null for statements that return nothing such as DDL statements.
– execute(): Used to process any DDL, DML, or database specific command. It will return one or more ResultSet objects or the update counts depending on the statement type. This method provides the most flexibility, however you may find it difficult to process the results.
– executeBatch() : Used to submit a batch of commands to the database for execution. If all commands execute successfully, returns an array of update counts.
The PreparedStatement and CallableStatement interfaces extend Statement interface. They are suitable for different scenarios.
Statement interface is used for general–purpose access to the database. And the Statement interface cannot accept parameters. It is useful when you are using static SQL statements.
The PreparedStatement interface accepts input parameters at runtime. It is faster than the generic Statement object since the SQL statement is precompiled. It is useful when you plan to use the SQL statements many times.
The CallableStatement interface can also accept input parameters at runtime. It could be used when you want to call the stored procedures in the database.
PreparedStatement
PreparedStatement interface extends the Statement interface. It adds some functionalities and it has some advantages over a generic Statement object.
PreparedStatement object accepts input parameters at runtime. This is done by assign the values to the appropriate parameter placeholders.
When the PreparedStatement object is created, the JDBC will prepare the SQL statement for execution by sending it to the database. Then the statement will be parsed, and compiled. The parsed statement lives in memory and remains ready to use during the database session.
CallableStatement
A stored procedure is a set of SQL statements that can be stored in the database. It is supported in MySQL 5.0 and above. The CallableStatement object enables you to execute stored procedures located on the database from the Java application.
In the example, we call a stored procedure: insert_price to insert one record into the price table of the fly-four-less database.
Using Data Source
DataSource object offers an alternative to DriverManager for opening database connections. By using DataSource object, you avoid having to register the JDBC driver; the DataSource object handle the detail. So you not need to hard-code the driver class.
There are three types of standard DataSource objects:
1. The basic DataSource that produces standard Connection objects just like those the DriverManager does.
2. The PooledDataSource supports connection pooling. Pooled connections are returned to a pool for reuse by another transaction.
3. The DistributedDataSource supports distributed transactions accessing two or more DBMS servers.
The DataSource interface defines a getConnection() method. This method is used to returns a standard physical connection.
Conclusion
Having gone through this tutorial, you will realize that it is easy for you to use JDBC drivers to enable you open database connections and to interact with it by sending SQL commands.
Share this Article!
