JDBC

JDBC provides an interface which allows you to perform SQL operations independently of the instance of the used database. To use JDBC you require the database specific implementation of the JDBC driver.
There are mainly 6 steps in it:

  • Load the driver
  • Establish Connection (Defining Connection Url and getting connection)
  • Prepare the Query (Statement/Prepared Statement/Callable Statement)
  • Execute the query
  • Process Result
  • Close Connection

Lets see an example. Here I have used MySQL as my database:


package mynotes.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTest {

public Connection getConnection() {

Connection connection = null;

try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306", "root", "admin");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

return connection;
}

public void testDb(){
Connection connection = getConnection();
Statement statement = null;
ResultSet rs=null;
PreparedStatement ps=null;
try {
statement=connection.createStatement();
rs=statement.executeQuery("Select * from test.employee");
showResultSet(rs);
ps=connection.prepareStatement("Insert into test.employee values (?,?,?,?,?)");
ps.setInt(1,200);
ps.setString(2, "Jane");
ps.setString(3, "Doe");
ps.setInt(4, 32);
ps.setInt(5, 5500);
int rowModified=ps.executeUpdate();
System.out.println("RowModified=>"+rowModified);
rs=statement.executeQuery("Select * from test.employee");
showResultSet(rs);
rs.close();
statement.close();
ps.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

public void showResultSet(ResultSet rs) throws SQLException{
for  (int i = 1; i<= rs.getMetaData().getColumnCount(); i++){
System.out.print(rs.getMetaData().getColumnName(i)+"\t");
}
System.out.println();
while(rs.next()){
System.out.print(rs.getInt(1)+"\t");
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
System.out.print(rs.getInt(4)+"\t");
System.out.print(rs.getInt(5));
System.out.println();

}

}

public static void main(String[] args) {
JdbcTest aJdbcTest=new JdbcTest();
aJdbcTest.testDb();
}

}

Here is the output:
Id    Fname    Lname    Age    Salary
100    John    Smith    30    6000
114    John    Tyler    26    5500
115    Mike    Taylor    26    5000
RowModified=>1
Id    Fname    Lname    Age    Salary
100    John    Smith    30    6000
114    John    Tyler    26    5500
115    Mike    Taylor    26    5000
200    Jane    Doe    32    5500

Drivers: There are many possible implementations of JDBC drivers. see here

Connection: Provide relevant details like dabase url, username, password. If your code is running on a server, use of datasource is usually preferred.

Creating Statement :  There are 3 ways in java:
1) Statement represents the base statements interface. In terms of efficiency, it is suitable to use Statement only when we know that we will not need to execute the SQL query multiple times. In contrast to PreparedStatement the Statement doesn’t offer support for the parameterized SQL queries, which is an important protection from SQL injection attacks. With that said, Statement would be suitable for the execution of the DDL (Data Definition Language) statements, such as CREATE, ALTER, DROP.
2) PreparedStatment extends the Statement interface. In most cases it is more efficient (in the context of multiple executions) to use the PreparedStatement because the SQL statement that is sent gets pre-compiled in the DBMS. Furthermore, we can use
PreparedStatement to safely provide values to the SQL parameters, through a range of setter methods (i.e. setInt(int,int), setString(int,String), etc.).
3) CallableStatement extends the PreparedStatement interface. This interface is used for executing the SQL stored procedures. One particular advantage of using CallableStatement is that it adds a level of abstraction, so the execution of stored procedures does not have to be DBMS-specific. However, it should be noted that the output parameters need to be explicitly defined through the corresponding registerOutParameter() methods; whereas the input parameters are provided in the same manner as with the PreparedStatement. Here’s the example of how the CallableStatement interacts with a MySQL Stored Procedure that does basic multiplication of integers:
//Procedure in DB
CREATE PROCEDURE MULTIPLY(OUT RESULT INT, IN a INT, IN b INT) SET
RESULT = a * b;
//Java Call
CallableStatement cstmt = con.prepareCall(“{call MULTIPLY(?, ?, ?)}”);
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setInt(2, 4);
cstmt.setInt(3, 8);
cstmt.execute();
int result = cstmt.getInt(1);

Query Execution: Statement has 3 methods by which we can execute and update the data :

  • execute()//insert – returns true or false
  • executeQuery()//select- returns resultSet object that contains the data produced by the given query
  • executeUpdate()//returns either the row count for SQL Data Manipulation Language (DML) statements or for SQL statements that return nothing

Auto-commit: If you dont want to commit immediately in execution of your query use connection.setAutoCommit(false) at the beginning and later can use commit or rollback accordingly.

SavePoints:  conn.setSavepoint(“Savepoint1”);

A savepoint marks a point that the current transaction can roll back to. Instead of rolling all of its changes back, it can choose to roll back only some of them. For example, suppose you:

  • start a transaction,
  • insert 10 rows into a table,
  • set a savepoint,
  • insert another 5 rows,
  • rollback to the savepoint,
  • commit the transaction.

After doing this, the table will contain the first 10 rows you inserted. The other 5 rows will have been deleted by the rollback.

Setting a savepoint doesn’t ‘save’ any data to the database. It doesn’t make database changes visible to any other transaction. A savepoint is just a marker that the current transaction can roll back to.

 

Grab all the code from my GitHub repository.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: