Spring JDBC

What is Spring JDBC & how it works?

Java Database Connectivity (JDBC) API is the answer. JDBC makes it quite easy and simple to connect to databases and perform database related operations.

Using JDBC, a Java application can access a variety of relational databases such as Oracle, MS Access, MySQL, SQL Server, etc.

The JDBC API belongs to the java.sql package and consists of various interfaces and classes.

As the Java environment doesn’t know how to interact with the database environment, we’ll need an interface between the two.
A driver is such an interface that will act as a translator between the two parties.

To avail the help of JDBC API for working with any particular database, we should have the driver of that specific database. The driver takes the responsibility of establishing a connection to the database, and exchange queries and their results with it.

JDBC is a specification that instructs different database vendors on developing a driver that can connect Java programs to their respective databases. A driver written according to this standard is called a JDBC Driver. All JDBC Drivers implement the Driver interface of the java.sql package.

There are 4 Types of Drivers:

• Type 1 (JDBC — ODBC Bridge Driver): A JDBC bridge is used for accessing the installed ODBC drivers that are available on each client machine. This type of driver is recommended only for experimental usage or if there is no other alternative available.
• Type 2 (Native — API Driver): JDBC API calls get converted to the native C/C++ API calls corresponding to the database. Used only when Type 3 and 4 are not available.
• Type 3 (Network Protocol Driver): A three-tier style is used to access the databases and is flexible very much. This type of driver is preferred when there are multiple databases being accessed by the Java application.
• Type 4 (Pure Java-Based Driver): A pure Java driver communicates via socket connection directly with the vendor’s database. This is considered as best among others in performance. This is the most preferred driver for accessing a single database from an application.

Steps used to establish connection-

1. Load the driver.

2. Make a connection to the database.

3. Send SQL queries to the database.

4. Process the result.

Step 1 is to load and register a JDBC Driver.

class.forName() method can be used to load the driver’s class file into the memory, dynamically. This also automatically registers it.

Class.forName(oracle.jdbc.driver.OracleDriver);

DriverManager class can be used to register a driver:

Driver orclDriver = new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver(orclDriver);

Step 2 is to connect to the database.

The DriverManager class not only helps in managing and registering drivers but also in connecting to databases.
To connect to a database, the DriverManager class has the static methods which return a Connection object:

• getConnection(String url) — url is a database address that points to the database.
• getConnection(String url, Properties info) — info is a collection of tag/value pairs as connection parameters eg: username/password.
• getConnection(String url, String user, String password) — a user is the database user’s username on whose behalf the connection is being established, and the password is the user’s password.

Have a look at the URL formats of some Databases:

Hostname: RandomHostName

Database: Demo

Port: 1521

Username: Ravi

Password: passwd

Entering DB details as :

1. String url = “jdbc:oracle:thin:@RandomeHostName:1521:Demo”;
2. String user = “Ravi”;
3. String password = “passwd”;
4. Connection conn = DriverManager.getConnection(url, user, password);

Note: Within an application, we can have more than one connection with a single database or multiple connections with multiple databases.

Step 3 is to send an SQL statement to the database.
This is done in two parts:

• creating a statement.
• sending and executing it.

Class.forName(“oracle.jdbc.driver.OracleDriver”);
// Create and get the connection
String url = “jdbc:oracle:thin:Ravi/passwd@kecmachine:1521:Demo”;
Connection conn = DriverManager.getConnection(url);

// Create the statement
Statement stmt = conn.createStatement();
// Execute the query
ResultSet rs = stmt.executeQuery(“select * from course where type=’online’”);
// Rest of the code

stmt.close();
conn.close();

PreparedStatement interface helps us to work with precompiled SQL statements.

Precompiled SQL statements are faster than normal SQL statements. So if an SQL statement is to be reused, it is better to use PreparedStatement.

Have a look at how to work with a prepared statement:

1. // Create the prepared statement
2. PreparedStatement preStmt = conn.prepareStatement("select * from faculty where faculty_id=1001");
3. ResultSet rsFaculty = preStmt.executeQuery();

Since we write the SQL statements as a String in our JDBC application, we can pass some dynamic values at run time and concatenate it with the query as shown below:

1. String facultyId = "1001";
2. PreparedStatement preStmt = connection.prepareStatement("SELECT * FROM faculty WHERE faculty_id = "+facultyId);

Now, the SQL statement that gets fired to the database is,

SELECT * FROM faculty WHERE faculty_id = 1001

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

This query can go reused several times with different values supplied for the facultyId.

1. String sql = "SELECT * FROM faculty WHERE faculty_id = ? "; //indicates position of parameter in the query
2. PreparedStatement preStmt = connection.prepareStatement(sql);
3. preStmt.setInt(1,1001); //setting the parameter value

Here ‘?’ is used in the query to indicate the position of the parameter which starts from 1(one). The parameter can be bound with a value using setXXX(parameterIndex, value) of PreparedStatement, where XXX represents the Java data type of the parameter that we wish to bind.

These types of queries where parameters are set at run time using parameter index are called Parameterized queries. This is the solution for the SQL injection attack.

As we set the parameters using the type of parameters itself (i.e. setXXX()), it will consider the whole value as of that type. In this case, if some erroneous code supplies a value for facultyId as “1001 or 1=1”, it will be a compilation error as Integer will not accept the String value.

Thus preparedStatement helps us prevent SQL Injection vulnerability.

Best practice: It’s preferred to use the prepared statement as it provides features like prevention from SQL injection, precompiled SQL queries and use of bind variables.

_________________________________________________________________

Step 4 is to process the result.

The java.sql.ResultSet interface denotes a database query result.

ResultSet objects always keep a cursor pointing to the current row of the result set. It has methods for navigating, viewing, and updating the data.
Commonly used navigational methods of ResultSet are:

Final Code Change -:

Transactions —
Case — If a student data fails to get added, their list of courses should not get persisted and vice versa. i.e. there are two statements that must be executed in tandem, else their actions must be taken back.

For such situations, JDBC provides transactions.

A transaction treats one or more statements as a single logical entity.
If and only if all the statements finish successfully, the transaction is considered successful and is committed.
In any other case (even if one statement fails), the entire transaction is assumed to have failed and is rolled back.

A transaction is usually created using the following methods that are available in the Connection interface:

• setAutoCommit(boolean autoCommit) — Sets the auto-commit mode of the connection. It is true by default.
• commit() — Commits the transaction and makes all its changes permanent in the database.
• rollback() — Discards the changes made inside the transaction and reverts to the previous consistent state of the database.

Best practice: Auto commit mode can be disabled when multiple statements need to be executed as a block and these sets of SQL statements frame a transaction unit (between setAutoCommit(false) and commit(true)). In the case of a default mode (setAutoCommit(true)), every single statement being encountered is transacted and committed as and when the execution of the statement gets completed.