Java JDBC Tutorials

The Java Database Connectivity (JDBC) API enables Java application to interact with database.

1. Getting Started

• JDBC + Oracle database
• JDBC + MySQL database
• JDBC + PostgreSQL database

2. Statement

This Statement has no cache, good for simple and static SQL statements like CREATE or DROP. In Statement, the way we construct the condition or parameters in SQL is prone to SQL injection, remember escape the quotes and special characters.

• statement.execute(sql) – Normally for DDL like CREATE or DROP
• statement.executeUpdate(sql) – Normally for DML like INSERT, UPDATE, DELETE
• statement.executeQuery(sql) – Run SELECT query and return a ResultSet
• statement.executeBatch() – Run SQL commands as a batch

Articles:

• JDBC Statement – Create a table
• JDBC Statement – Insert a row
• JDBC Statement – Update a row
• JDBC Statement – Delete a row
• JDBC Statement – Select list of rows
• JDBC Statement – Batch Update

3. PreparedStatement

PreparedStatement extends Statement to provide better performance by precompiled and cached the SQL statement, good for SQL statement that need to execute multiple times. Furthermore, it provides many setXxx() to protect SQL injection by escaping the quotes and special characters.

• preparedStatement.execute() – Normally for DDL like CREATE or DROP
• preparedStatement.executeUpdate() – Normally for DML like INSERT, UPDATE, DELETE
• preparedStatement.executeQuery() – Run SELECT query and return a ResultSet
• preparedStatement.executeBatch() – Run SQL commands as a batch

Articles:

• JDBC PreparedStatement – Create a table
• JDBC PreparedStatement – Insert a row
• JDBC PreparedStatement – Update a row
• JDBC PreparedStatement – Delete a row
• JDBC PreparedStatement – Select list of rows
• JDBC PreparedStatement – Batch Update
• JDBC PreparedStatement SQL IN condition

4. CallableStatement

CallableStatement extends PreparedStatement, for executing stored procedures or functions from the database.

• conn.prepareCall(sql)

Oracle database

• JDBC CallableStatement – Stored Procedure IN parameter example
• JDBC CallableStatement – Stored Procedure OUT parameter example
• JDBC CallableStatement – Stored Procedure CURSOR example

PostgreSQL

• JDBC CallableStatement – Stored Function

5. Transaction

• JDBC Transaction example

conn.setAutoCommit(false); // default true
// start transaction block

// SQL statements

// end transaction block
conn.commit();
conn.setAutoCommit(true);

6. Spring JDBC Database Access

JdbcTemplate examples.

• Spring Boot JDBC Examples
• Spring Boot JDBC Stored Procedure Examples
• Spring JdbcTemplate Querying Examples
• Spring JdbcTemplate Handle Large ResultSet
• Spring JdbcTemplate batchUpdate() Example
• Spring Boot JDBC Image BLOB Examples

FAQs

• How to add Oracle JDBC driver in your Maven local repository
• Connect to Oracle DB via JDBC driver
• JDBC – How to print all table names from a database?
• JDBC Class.forName() is no longer required
• Oracle – ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
• java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!
• java.sql.SQLException: The server time zone value ‘xx time’ is unrecognized

References

• Oracle – JDBC tutorials
• SQL in Java 8: ResultSet Streams
• Wikipedia – Java Database Connectivity
• Wikipedia – SQL injection
• Statement JavaDocs
• PreparedStatement JavaDocs
• CallableStatement JavaDocs
• java.sql summary JaavDocs

Oracle

• Oracle Database
• Oracle GUI – SQL Developer

MySQL

• MySQL Database
• MySQL GUI – Workbench

PostgreSQL

• PostgreSQL Database
• PostgreSQL GUI – pgAdmin