Java Database Connectivity (JDBC)

Four key interfaces are needed to develop any database application using Java: Driver, Connection, Statement, and ResultSet. 

These interfaces define a framework for generic SQL database access. The JDBC API defines these interfaces, and the JDBC driver vendors provide the implementation for the interfaces. 

Programmers use these interfaces.  A JDBC application loads an appropriate driver using the Driver interface, connects to the database using the Connection interface, creates and executes SQL statements using the Statement interface, and processes the result using the ResultSet interface if the statements return results.

Step 1. Loading drivers

Before JDBC version 3.0, the driver has to be loaded manually. 

A driver is a concrete class that implements the java.sql.Driver interface.

Class.forName("com.mysql.jdbc.Driver");

After the JDBC version 4.0, the driver can be loaded automatically, if the driver-jar file is located with the classpath. The SPM automates the driver loading mechanism. Using SPM, every JDBC 4.0 driver implementation must include the configuration file with the name java.sql.Driver within the META-INF/services folder in their .jar file. The file java.sql.Driver contains the full name of the class that the vendor used to implement the interface jdbc.sql.Driver. For example, com.mysql.jdbc.Driver is the name for the MySQL driver. When DriverManager requests a database connection, it loads these driver classes.

Step 2. Establishing Connections

To connect to a database, use the static method getConnection(databaseURL) in the
DriverManager class, as follows:
Connection connection = DriverManager.getConnection(databaseURL);

java.sql.Connection

The interface java.sql.Connection represents a connection session with the specified database. 

The Connection object provides a database’s metadata, information regarding the data stored in a database. It makes accessible the SQL grammar supported by the database, its stored procedures, and other database-related information.

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

url = jdbc:subprotocol://<host>:<port>/<database_name>

specify additional parameters like the default connectTimeout, autoConnect, and others.

url = jdbc:subprotocol://<host>:<port>/<database_name>?<connectTimeout>

There are two ways to connect to a database: by using class java.sql.DriverManager or the interface javax.sql.DataSource. Class DriverManager is the preferred class to establish database connections with Java SE applications because DataSource works with the Java Naming and Directory Interface (JNDI). JNDI is usually supported by Java applications with a container that supports JNDI like Java Enterprise Edition Server.

Step 3. Creating Statements

Statement statement = connection.createStatement("sql-query");

The interface java.sql.Statement is used to create and execute static SQL statements and retrieve their results. The results from the database are returned as ResultSet objects or int values indicating the number of affected rows.

Step 4. Executing Statements

Method executeQuery() is used for SQL SELECT statements

ResultSet resultSet = statement.executeQuery("select firstName, mi, lastName from Student where lastName = 'Smith'");

Though the case of SQL is ignored by the underlying database, the use of uppercase for keywords is recommended and practised for better readability.

Although everything in Java is 0-based, column indexes in a ResultSet are 1-based.

Method executeUpdate() returns a count of the rows that are
or would be affected in the database for row insertions, modifications,
and deletion.

Method executeUpdate() is used to execute SQL queries to insert new rows in a table and update and delete existing rows.  It’s also used to execute DDL queries, such as the creation, modification, and deletion of database objects like tables.  If you use method executeQuery() for any of these operations, you’ll get an SQLException at runtime.

Step 5. Processing ResultSet

The ResultSet maintains a table whose current row can be retrieved. The initial row position is null. You can use the next method to move to the next row and the various getter methods to retrieve values from a current row. For example, the following code displays all the results from the preceding SQL query.

// Iterate through the result and print the student names
while (resultSet.next())

The interface java.sql.ResultSet is retrieved as a result of executing a SQL SELECT statement against

a database. It represents a table of data. The ResultSet object can be read-only, scrollable, or updatable. By default, a ResultSet object is only read-only and can be traversed in only one (forward) direction. You can create a scrollable ResultSet (that can be traversed forward and backwards) and/or an

updatable ResultSet bypassing the relevant parameters during the creation of a Statement object.

Method executeUpdate() is used to execute SQL queries to insert new rows in a table and update and delete existing rows.  It’s also used to execute DDL queries, such as the creation, modification, and deletion of database objects like tables.  If you use method executeQuery() for any of these operations, you’ll get an SQLException at runtime.

Using Transaction
It is a way to making sure the amounts consistent. A transaction is a set of one or more statements that are executed as a unit, so either all of the statements are executed, or none of the statements is executed.

Disable  auto commit first: con.setAutoCommit(false);

Committing Transactions:  con.commit();

Using Transactions to Preserve Data Integrity
Setting and Rolling Back to Savepoints