JDBC Tutorial
What is JDBC?
JDBC (Java DataBase Connectivity) is an API which defines how to connect and access a database from Java application.
Steps to connect Java application with database
Register the driver
Class.forName() can be used to explicitly load and register the driver class. From jdbc 4.0 onwards we don’t need to load the driver explicitly.
Class.forName("com.mysql.jdbc.Driver");
Create a connection
getConnection() of DriverManager class is used to create connection.
DriverManager.getConnection(url, usernamae, password);
Create SQL statement
createStatement() method of Connection interface is used to create static SQL Statement.
preparedStatement is an interface which extends Statement interface. It supports parameterized query as well. preparedStatement is more recommended.
prepareCall is used to execute a call to a database stored procedure.
Both createStatement and preparedStatement can use one of the three execute methods:
- execute(String SQL) – returns boolean. True if query can return a result set object else False.
- executeUpdate(String SQL) – returns integer, indicating number of rows impacted by the execution of SQL statement.Generally preferred with insertion, delete and update statements.
- executeQuery(String SQL) – returns ResultSet object.
Execute SQL statement
executeQuery() method of Statement interface is used to execute queries to the database
pstmt.executeQuery();
Close connection
close() method of Connection interface is used to close the connection.
conn.close();
Lets see an example, where we will connect to a database (say MySQL database), send a query and print the data received. Here, We are using Employee table from jdbcexample Database.
Fetch all the records from database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCExample
{
public static void main(String[] args)
{
readData();
}
public static Connection getConnection()
{
String url = "jdbc:mysql://localhost:3306/jdbcexample";
String username = "root";
String password = "admin";
Connection conn = null;
try
{
conn = DriverManager.getConnection(url, username, password);
System.out.println("Connected to DB successfully");
}
catch (SQLException e){
System.out.println(e.getMessage()); }
return conn;
}
}public static void readData()
{
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
String sql = "select * from employee";
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("Employee id: " + rs.getString(1) +
" Name: " + rs.getString(2));
}
}
catch (Exception e) {
System.out.println(e.getMessage());
}
finally {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}Update name for the record where id is 3
public static void updateData()
{
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
String sql = "update employee set name=? where id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "Reddy");
pstmt.setInt(2, 3);
int count = pstmt.executeUpdate();
System.out.println(count + " record updated");
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
finally
{
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}Insert a new record
public static void insertData()
{
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
String sql = "insert into employee values(?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 16);
pstmt.setString(2, "Reddy");
pstmt.setInt(3, 16);
int count = pstmt.executeUpdate();
System.out.println(count + " record inserted");
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
finally
{
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}Delete a record with id 3
public static void deleteData()
{
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
String sql = "delete from employee where id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 3);
int count = pstmt.executeUpdate();
System.out.println(count + " record deleted");
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
finally
{
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}Transaction Management
In jdbc every SQL statement is considered as independent request and by default it is committed to database upon its completion.
However, at certain cases we need changes to be committed to database only when certain set of SQL statements are executed successfully, and if either one of the statement is failed, then everything needs to rollback.
This can be achieved through JDBC transaction by setting conn.setAutoCommit(false); and also, it gives us control on how and when a transaction should commit into database and rollback the changes in case of any failure.
Let’s see a simple example to understand how JDBC transaction works. Here, we are trying to insert a record and update another record. If transaction goes fine then commit the changes else both the changes should rollback.
public static void multipleDBUpdatesUsingTransaction()
{
Connection conn = null;
PreparedStatement pstmtInsert = null;
PreparedStatement pstmtUpdate = null;
String insertSQL = "insert into employee values(?,?,?)";
String updateSQL = "update employee set email=? where id=?";
try
{
conn = getConnection();
conn.setAutoCommit(false);
pstmtInsert=conn.prepareStatement(insertSQL);
pstmtInsert.setInt(1, 3);
pstmtInsert.setString(2, "Prasad");
pstmtInsert.setString(3, "prasad@admin.com");
int insertRowCount = pstmtInsert.executeUpdate();
System.out.println(insertRowCount + " record inserted");
pstmtUpdate = conn.prepareStatement(updateSQL);
pstmtUpdate.setString(1, "prasad@admin.com");
pstmtUpdate.setInt(2, 13);
int updateRowCount = pstmtUpdate.executeUpdate();
System.out.println(updateRowCount + " record updated");
conn.commit();
System.out.println("Commited changes successfully");
}
catch (Exception e)
{
try {
conn.rollback();
System.out.println("Changes rolled back"+e.getMessage());
}
catch (SQLException e1) { System.out.println(e.getMessage()); }
}
finally
{
try {
if(pstmtInsert != null)
pstmtInsert.close();
if(pstmtUpdate != null)
pstmtUpdate.close();
if(conn!=null)
conn.close();
} catch (SQLException e) { }
}
}