JDBC stands for Java Database Connectivity, which is an API that allows us to build applications that can access and manipulate with many different types of databases. In this tutorial, I will talk to you about the basic knowledge of JDBC.
First, I will create a new Maven project as an example:
JDBC provides us with an overview of database connectivity, execution of SQL statements, and processing of returned results. It consists of multiple interfaces and each interface assumes a different role.
There are four main JDBC interfaces in the java.sql package: Driver, Connection, Statement, ResultSet. Different types of databases, such as MySQL, Oracle, …. will rely on these interfaces to implement objects that match those types of databases. Set of these implementation objects, we call them JDBC Driver. Each database provides different JDBC Driver and is packaged into .jar files.
In the above project, I added MySQL Driver dependency:
|
1 2 3 4 5 |
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency> |
because I will use MySQL database to illustrate this tutorial.
Now I will create a new database for example.
I will have two tables that contain classroom information and information about the students of those classes. The specific structure of the two tables will be as follows:
|
1 2 3 4 5 |
CREATE TABLE `clazz` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `code` varchar(10) NOT NULL, `date_of_birth` datetime DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `clazz_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `student_clazz_idx` (`clazz_id`), CONSTRAINT `student_clazz` FOREIGN KEY (`clazz_id`) REFERENCES `clazz` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
I will add some data so I can use JDBC queries, manipulate these data.
OK, now we will start writing code.
The first thing we need to do is: establish a connection between our application and the database.
Interface DriverManager will help us do this!
The JDBC Driver when the application runs, will automatically register itself with DriverManager and from the DriverManager object we can get the Connection object containing the connection to the database, so setting up the connection between our application and the database will be easy.
For example, using MySQL, the interface class of MySQL Driver is com.mysql.cj.jdbc.Driver. If you open the code of this class, you will see the following:
As you can see, the Driver object of the MySQL Driver will have a code in the static block to register itself with DriverManager. Our job is only to declare the JDBC Driver that we need to use.
For JDBC version 3.0 or earlier, we need to call Class.forName() and pass in the forName() method the class implement interface Driver of the JDBC Driver interface, so that we can use the JDBC Driver.
For MySQL Driver, we need to call:
|
1 |
Class.forName("com.mysql.cj.jdbc.Driver"); |
With JDBC from version 4.0 onwards, as in the example of this tutorial, we do not need to call Class.forName() anymore because JDBC has automated support for registering all JDBC drivers in your application by using what is called Service Provider Mechanism. All JDBC Drivers for JDBC versions 4.0 and above must contain a configuration file called java.sql.Driver in the META-INF/services directory of the .jar file. This file will contain all full class names that implement Driver interface of the JDBC Driver.
Now if you open MySQL Driver in our project, you will see this file.
And the content of this file is:
OK, I will use the automatic registration mechanism of JDBC driver so I will not need to call Class.forName() method.
Now, I will use DriverManager to get the Connection object.
DriverManager is the object that manages all of the JDBC drivers that our application will use. We retrieve the Connection object from it by calling the getConnection() method. Here we have all three overload methods of getConnection():
|
1 2 3 |
public static Connection getConnection(String url) throws SQLException public static Connection getConnection(String url, Properties info) throws SQLException public static Connection getConnection(String url, String user, String pwd) throws SQLException |
I will use the third method to get Connection from DriverManager.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
package com.huongdanjava.javajdbcbasic; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class Application { public static void main(String[] args) throws SQLException { Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/example", "root", "123456"); } } |
The URL here is formatted as:
|
1 |
jdbc:subprotocol://<host>:<port>/<database_name> |
The subprotocol is a major factor for DriverManager to find the appropriate JDBC driver in the list of JDBC drivers it manages.
OK, now that we have the connection, we will now start writing code to query the database.
From the Connection object, we will create a Statement object that will allow us to execute an SQL statement as we wish:
|
1 2 3 4 5 6 7 8 9 10 11 |
package com.huongdanjava.javajdbcbasic; import java.sql.*; public class Application { public static void main(String[] args) throws SQLException { Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/example", "root", "123456"); Statement statement = connection.createStatement(); } } |
The Statement object has two main methods, executeUpdate() and executeQuery(), to manipulate with the database. You need to distinguish when to use executeUpdate() and when to use executeQuery().
The executeUpdate() method is used to execute an SQL statement to add a row to the table, edit or delete existing tables. In short, this method is used to execute SQL statements that affect your database such as adding, editing, deleting, …
The executeQuery() method is only used if you want to read data from the database. If you use for other purposes, you may get SQLException error when you run it.
In this tutorial, we need to get the list of classes available. So we’ll just use the executeQuery() method, and the Statement object executes the following SQL statement:
|
1 |
SELECT * FROM clazz |
The result of this statement will return the ResultSet object, which we will use to read the result of our SQL statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
package com.huongdanjava.javajdbcbasic; import java.sql.*; public class Application { public static void main(String[] args) throws SQLException { Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/example", "root", "123456"); Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("SELECT * FROM clazz"); while (rs.next()) { System.out.print(rs.getInt("id") + "-"); System.out.print(rs.getString("name") + "\n"); } } } |
Result:
As you can see, from the ResultSet object we can read the data returned through the getInt(), getString(), …
In a nutshell, this tutorial I demonstrates the basic things need to know when working with JDBC for all of you. Hope you can add more knowledge to yourself.
