JDBC stands for Java Database Connectivity. It is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.
It includes APIs for the tasks mentioned below:
- Making a connection to a database.
- Creating SQL statements.
- Executing SQL statements.
- Viewing & Modifying the resulting records.
Steps to use JDBC
To use jdbc, we must add related libs firstly in the buildpath/classpath. For example, with mysql, we must download mysql-connector-java-*.jar. According to different IDEs, we will also have different ways to add them. In IDEA, it needs to put jars in a lib folder on your root and right click, choose add as Library.
Import the following two packages if we need to use extended functionality provided by the Oracle driver. Check more details here
Register JDBC driver
Prior to JDBC 4.0, we must manually load drivers with the method
For the other drivers, we could check JDBC Driver List for details.
According to Oracle Java Tutorials, any JDBC >= 4.0 drivers that are found in your classpath are automatically loaded.
Open a connection
To open a connection, we must invoke
DriverManager.getConnection() with following parameters:
- URL (jdbc:sqlType://hostname:port/dataBaseName?Param=Value&Param=Value)
MySQL URL example
useUnicode=true&characterEncoding=utf-8 here is very useful to avoid some character problems like Chinese even if you already set utf-8 when created tables.
- Keep all the DB configurations in a properties file
For example, now we have DBConfig.properties:
Then load this file and get connection by a DBHelper class:
Creat a statement
There are three kinds of statements:
Statement is for general-purpose. Useful when you are using static SQL statements at runtime. Do not accept parameters.
PreparedStatement interface extends the Statement interface. It is used to execute a statement many times. It accepts input parameters at runtime.
CallableStatement is used to access the database stored procedures. It accepts runtime input parameters.
Statement does not accept parameters, so we could only use it to execute some static queries.
? symbol is known as the parameter marker. We must supply values for every parameter before executing SQL statement.
setXXX() methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter. Each parameter marker is referred by its ordinal position. The first marker represents position 1, then 2, and so forth. (Not from 0!)
So with the help of parameter marker, we could prepare a SQL statement which will be used many times by
PreparedStatement. We only need to set the values before executing it.
Execute a statement
boolean execute(String SQL) returns true if a ResultSet object can be retrieved; otherwise, false. Use this method to execute SQL DDL statements which you only need to know whether excuted sucessfully or not.
int executeUpdate(String SQL) returns the number of rows affected. Use this method when we expect to get number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.
ResultSet executeQuery(String SQL) returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.
For prepared statement, do not need to string sql as input.
ResultSet.getXXX() to etract datas from result set by column name.
We must clean up environment
JDBC is in auto-commit mode by default which means every SQL statement is committed to the database upon its completion. For Transaction of JDBC, we need to set false of
setAutoCommit() for a connection. So in this way, only when we invoke manually
commit() of a connection, all the operations will be validated. If any exception happens, we need to
rollback() which will cancel all the operations:
With a savepoint, we could rollback to use the rollback method to undo only the changes made after the savepoint. The
PreparedStatement object has three methods about it:
setSavepoint(String savepointName): defines a new savepoint. It returns a
rollback(Savepoint savepoint): rolls back to the specified savepoint.
releaseSavepoint(Savepoint savepoint): releases a savepoint. Do not forget it!
Batch Processing allows us to group related SQL statements into a batch and submit them with one call to the database.
CallableStatement have following methods to support it:
addBatch()is used to add individual statements to the batch.
executeBatch()is used to start the execution of all the statements grouped together. It returns an array of integers, and each element of the array represents the update count for the respective update statement.
clearBatch()removes all the statements you added with the addBatch() method. However, you cannot selectively choose which statement :(
Another example with