Java Database Connectivity

known as JDBC

Java Database Connectivity is an API for Java applications to access databases and executing SQL statements. The JDK 1.1 version released on February 19, 1997 already includes the implementation of JDBC. Starting from JDBC 3.0 version, JDBC is developed iteratively under the JCP (Java Community Process) mechanism. This blog will review JDBC related knowledge and common integration of JDBC.

1. JDBC Version History

JDBC was developed by Oracle Corporation and has been released for 26 years. The code is distributed in the java.sql and javax.sql packages. The latest version of JDBC is 4.3, released in 2017. The following is the JDBC release history schedule and the corresponding JDK implementation.

2. JDBC Version Characteristics

JDBC 3.0 merges different JDBC 2.x version specifications and begins iterative development under JSR. The main goal of JDBC 3.0 is to improve the technical functions, and the main goal of JDBC 4.0 is to provide a better development experience.

2.1 JDBC Driver

JDBC 4.0 supports automatic driver loading, but 3.0 requires specifying driver loading.

// 3.0
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:13306/mydb?user=root&password=123456");

// 4.0
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:13306/mydb?user=root&password=123456");

2.2 JDBC 4.X New Features

• 4.0 driver automatic loading, no need to explicitly load classes using Class.forName(..).
• 4.1 try-with-resource supported.
• 4.2 REF CURSOR supported.
• 4.3 Sharding supported.

3. JDBC Driver Type

JDBC mainly has the following four driver types:

• Type1 JDBC-ODBC bridge.
• Type2 Native-API driver.
• Type3 Middleware driver.
• Type4 Database-Protocol driver.

3.1 Driver Type Differences

As mentioned above Type 4 drivers have the best performance. In addition, mysql-connector-j is a Type 4 driver.

4. JDBC Core APIs

• DataSource represents the actual connected database.
• Connection database connection can be obtained through the DataSource object.
• Statement can be obtained through the Connection object and is used to execute SQL statements.
• PrepareStatement can precompile SQL statements to prevent SQL injection.
• ResultSet represents the query results executed on the data.

5. Vendor Driver

Almost all mainstream databases provide corresponding JDBC Drivers, such as Hive’s hive-jdbc, Mysql’s mysql-connector-j, Clickhouse’s clickhouse-jdbc, etc. The design of JDBC fully complies with OCP and DIP principles, and it is very convenient to expand data sources.

If there is a need to change the storage solution in the project, theoretically you only need to change the corresponding jdbc driver.

Get 8techlife’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The following example is the basic operation of JDBC API Implemented by Mysql vendor.

MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setPort(13306);
dataSource.setDatabaseName("mydb");
dataSource.setUser("root");
dataSource.setPassword("123456")
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
String sql = "INSERT INTO test VALUES ('hello')";
stmt.executeUpdate(sql);

6. JDBC Integration

Although JDBC provides complete database access capabilities, using JDBC directly to operate the database is too complex and will add too much boilerplate code. In real project development, we will use the database connection pool Hikari to improve the throughput of database operations, and introduce a persistence layer framework to reduce code complexity, such as using Spring JDBC Template, MyBatis, Hibernate, etc., and based on different scenarios, we can Choose to use different persistence layer frameworks. For example, you can use Spring JDBC Template to add and update large data set records in batches, rewriteBatchedStatements=true needs to be configured.

6.1 Integrate With HikariCP

HikariCP is a high-performance JDBC connection pool.

// use maven to import dependency packages
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

// create connection pool
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:13306/mydb?user=root&password=123456");
HikariDataSource dataSource = new HikariDataSource(config);

6.2 Integrate With MyBatis

MyBatis is a popular ORM framework that can bind and configure SQL for database access through the Mapper interface. It is easy to use.

// use maven to import dependency packages
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.11</version>
</dependency>

// Mapper
public interface BlogMapper {
  @Select("SELECT * FROM blog WHERE id = #{id}")
  Blog selectBlog(int id);
}

// SqlSessionFactory
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource());
return factoryBean.getObject();

// SqlSession
try (SqlSession session = factoryBean.openSession()) {
  BlogMapper mapper = session.getMapper(BlogMapper.class);
  // obtain Blog
  Blog blog = mapper.selectBlog(10);
}

6.3 Integrate With JDBC Template

JDBC Template is provided by Spring to simplify JDBC operations.

// use maven to import dependency packages
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-webflux</artifactId>
</dependency>

// create JDBC Template
DataSource dataSource = ...;
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

// drop table
jdbcTemplate.execute("DROP TABLE customers IF EXISTS");