JDBC stands for Java DataBase Connectivity and is a technology for database connectivity from java program. JDBC is a collection of multiple interfaces and classes which provides methods for database connection and operations.
Operations include executing SQL queries for fetching records, inserting and updating or deleting data from a database table directly from a java program.
For performing any operation on a database using JDBC, requires some steps. They are :
- Load Database driver class.
- Retrieving Database Connection.
- Getting Statement or PreparedStatement object.
- Getting ResultSet (Required only when a Select operation is performed and you want to iterate through the records fetched)
We shall dig into each of the above steps in detail in this post.
Load the appropriate driver class in memory for the database type which we want to connect. Driver classes for each database vendor are different. For Example, database driver class for MySQL is
com.mysql.jdbc.Driver.This is done using forName method of java.lang.Class class as :
Class.forName("com.mysql.jdbc.Driver");
As soon as we load the driver class, this class is registered with java.sql.DriverManager class which uses it while making a database connection. Starting java 6 JDBC 4.0, this step is not necessary as the driver manager automatically loads the driver class if it finds the driver library added in the classpath.
Retrieving Database Connection
Database connection can be retrieved using one of various getConnection() methods of java.sql.DriverManager class. All methods differ in the type of arguments they take but all methods take the url of the database to which you want to connect.
URL of the database is in format jdbc:subprotocol:subname which means that the URL for every database should start with jdbc and they differ in subprotocol and subname according to the database vendor, where subprotocol and subname are arbitrary strings. URL also contains the name of the database to which we want to connect.
All connection methods return a java.sql.Connection object which may be visualized as a database connection. java.sql.Connection is an interface and its implementation lies in the database driver which we need to add in the classpath of our application.
Different versions of getConnection() method are as follows :
- getConnection(String url) – This method takes only the url of the database to which we are connecting. This method is generally used when the database to which we are connecting does not require a user name and password to get connected.
- getConnection(String url, String user, String password) – This method takes the database URL and the user name and password required to connect to the database.
- getConnection(String url, Properties properties) – This method takes the URL of the database and a Properties object which is of type
java.util.Propertiesand contains connection properties such as user name and password in the form of name-value pairs.
Here is how we would get connection for a MySQL database using second variant of getConnection() method :
DriverManager.getConnection("jdbc:mysql://localhost:3306/codippa","root","root");
// here codippa is the database to which we are connecting
Getting Statement or PreparedStatement object
Either of these two objects are required when we want to execute a query over a table in the database to which we connected above. Both of these objects belong to java.sql package and can be used to execute queries. But then comes the question, why two objects? when both do the same job.
They both do the same job but there are differences between the two. Elaborating the differences requires a separate space but for now, the main difference is that java.sql.Statement can execute only static queries. Static means you cannot set explicit parameter values in an SQL query using a java.sql.Statement object except using String manipulations while java.sql.PreparedStatement has the capability of setting parameter values in dynamic queries.
For example, Select * from users where userId=1 is a static query while Select * from users where userId=? is a dynamic query where you don’t know the userId until execution time.
If you are using java.sql.Statement then you cannot set the value of userId parameter using Statement object. You can only modify the query by appending the userId to its end by String manipulation and pass it to the Statement object for execution. But with java.sql.PreparedStatement, you can set userId values using its setInt() method.
java.sql.Statement and java.sql.PreparedStatement can be obtained using createStatement() and prepareStatement() methods of java.sql.Connection respectively, which was retrieved using one of the DriverManager.getConnection() methods above. Code follows :
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/codippa","root","root");
// get statement object
Statement statement = connection.createStatement();
// get prepared statement object
PreparedStatement prepStatement = connection.prepareStatement();
Getting ResultSet
java.sql.ResultSet object is required only when you fetch records from the database using methods using java.sql.Statement or java.sql.PrepareStatement objects.This object is required for iterating through the records and represents the table data.
java.sql.ResultSet maintains a cursor pointing to a row of table data and it initially positioned before the first row.It has a
next() method which moves this pointer to the next row and returns true if it is positioned to the next row and false, if there are no more rows to point to.
java.sql.ResultSet object has methods to retrieve column values based on their index (such as getString(int columnIndex)) and their names (such as getString(String columnName)).
JDBC example
Concluding the post, complete example for java database connectivity for executing a query, fetching a record and iterating through it follows :
public static void executeQuery() throws ClassNotFoundException, SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// load driver class
Class.forName("com.mysql.jdbc.Driver");
// get database connection
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/codippa", "root", "root");
statement = connection.createStatement();
// fetch a record
resultSet = statement.executeQuery("select * from post where Id=1");
int column = 1;
// iterate over the record
while (resultSet.next()) {
// fetch column value based on its index
System.out.println(resultSet.getString(column));
++column;
}
} catch (ClassNotFoundException e) {
throw e;
} catch (SQLException sqe) {
throw sqe;
} finally {
if (statement != null) {
statement .close();
}
if (connection != null) {
connection.close();
}
}
}
Above code example assumes MySQL as the database.
Let’s tweak in
java.sql.Connection,java.sql.Statement,java.sql.PreparedStatementand most other entities in JDBC are interfaces and their implementation lies in driver libraries.
This makes sense so that each Database vendor can provide its own implementation for these interfaces according to their database products.- People might ask that instead of loading a class using
Class.forName, why can’t we directly make an instance of the driver class as that would also load the class.
Well, think of the scenario when the class name is being read from a properties file or XML. In such scenario,Class.forNamecan only be used. - Besides holding user name and password in
DriverManager.getConnection()method, the properties object may also hold other connection parameters such as database port. - Database name which is given in
getConnection()method should exist at at the time of getting connection otherwise an unknown database error is thrown. - All methods of database interaction throw a
java.sql.SQLExceptionwhich needs to be handled or re-thrown by your code. - Loading a class has a risk that the class could not be found on the classpath and hence
Class.forNamemethod throws ajava.lang.ClassNotFoundException. - It is a good practice to close the
java.sql.Connectionandjava.sql.Statementobjects in finally block and put a null check before closing them. - Closing a
java.sql.Statementobject automatically closes anyjava.sql.ResultSetobject that the statement may have opened so there is no need of explicitly closing thejava.sql.ResultSetobject.
Learnt something from this post? Don’t forget to hit the clap button below and share this post.
Was really helpful. Thank you
Thanks Ruchika. Keep visiting !!!
very good article!
Thank you..Keep visiting !!!