JDBC

JDBC

JDBC

Overview

JDBC (Java DataBase Connectivity) is a Java API for executing SQL statements. It provides a unified way to access various relational databases and consists of a set of classes and interfaces written in Java.

JDBC is an official Java specification (set of interfaces) that helps developers quickly implement connections to different relational databases.

Core Classes

DriverManager

DriverManager: the driver management object.

• Register a driver:

  • Register the given driver: public static void registerDriver(Driver driver)
  • Code syntax: Class.forName("com.mysql.jdbc.Driver)
  • com.mysql.jdbc.Driver contains a static initializer block
  • No need to call a static method on DriverManager registerDriver; when the Driver class is used, its static block runs automatically to register the driver
  • A java.sql.Driver configuration file exists under the JAR’s META-INF directory, specifying com.mysql.jdbc.Driver

• Obtain a database connection and return the connection object: method public static Connection getConnection(String url, String user, String password)

  • url: the connection URL, syntax ​jdbc:mysql://ip地址(域名):端口号/数据库名称
  • user: username
  • password: password

Connection

Connection: the database connection object

• Get executor objects

  • Get a regular executor: Statement createStatement()
  • Get a prepared executor: PreparedStatement prepareStatement(String sql)

• Transaction management

  • Begin a transaction: setAutoCommit(boolean autoCommit) (false = manual transaction, true = auto‑commit mode, the default)
  • Commit a transaction: void commit()
  • Roll back a transaction: void rollback()

• Release resources

  • Release the database and JDBC resources of this Connection object: void close()

Statement

Statement: object for executing SQL statements

• Execute DML statements: int executeUpdate(String sql)

  • Returns an int: the number of rows affected
  • Parameter sql: can be an INSERT, UPDATE, or DELETE statement

• Execute DQL statements: ResultSet executeQuery(String sql)

  • Returns a ResultSet: encapsulates the query result
  • Parameter sql: can be a SELECT statement

• Release resources

  • Release the database and JDBC resources of this Statement object: void close()

ResultSet

ResultSet: result‑set object; it maintains a cursor pointing to the current row, initially positioned before the first row

• Check whether the result set contains data: boolean next()

  • Returns true and moves the cursor down one row if data exists
  • Returns false if no more data

• Retrieve data from the current row: XXX getXxx("列名")

  • XXX represents the data type (the type of the column you want to fetch)
  • Example: String getString("name"); int getInt("age");

• Release resources

  • Release the database and JDBC resources of this ResultSet object: void close()

Code Example

• Data preparation

  • JDBC connection code:

Injection Attacks

Attack Demonstration

SQL injection demonstration:

• On the login page, entering an incorrect username or password can still result in a successful login.

Mechanism: Everything typed into the password field is treated as part of the password, but the Statement object concatenates part of that input into the SQL query as a condition SELECT * FROM user WHERE loginname='aaa' AND password='aaa' OR '1'='1';.

Mitigation

PreparedStatement: an executor object for pre‑compiled SQL statements, extending Statement.

• Before executing the SQL, the statement is compiled in advance, fixing its structure; the remaining input is treated solely as parameters.
• Parameters in the SQL are represented by ? as placeholders.

Assigning values to the ? placeholder: setXxx(int parameterIndex, xxx data)

• Parameter 1: the position index of ? (indices start at 1)
• Parameter 2: the actual value for ?

Executing the SQL:

• For INSERT, UPDATE, DELETE statements: int executeUpdate()
• For SELECT statements: ResultSet executeQuery()

Connection Pools

Concept

Database connections are a critical, limited, and expensive resource—especially in multi‑user web applications. Proper connection management greatly influences an application’s scalability, robustness, and performance metrics.

A database connection pool is responsible for allocating, managing, and releasing connections. It allows an application to reuse an existing connection instead of creating a new one each time, which markedly improves database operation performance.

Returning Connections

Improved via dynamic proxies.

Custom connection‑pool class:

Open‑Source Projects

C3P0 – using the C3P0 connection pool:

• Configuration file name: c3p0-config.xml, must be placed in the src directory

  Code example:

Druid – Druid connection pool:

• Configuration file: druid.properties, must be placed in the src directory

  Code example.

---

Originally written by Li Wei (李唯_) and published in Chinese on 后端技术栈全书 (Full-Stack Backend Engineering). Translated and adapted for DriftSeas with permission.