Play Store Application link – Spring Framework in 9 steps – Apps on Google Play
Github Project Link – (XML based) – https://github.com/kuldeep101990/SpringJDBC
Github Project Link – (Annotation based) – https://github.com/kuldeep101990/SpringJDBCAnnotation
To make it easier for developers to work with databases, Spring provides an abstraction layer on top of JDBC called the JDBC Template. This abstraction layer provides a simplified interface that encapsulates the low-level details of working with JDBC. With the JDBC Template, developers can focus on writing SQL queries and working with the data returned, without worrying about the underlying implementation details.
DAO (Data Access Object) is a design pattern that separates the business logic of an application from the details of accessing data from a database. In essence, a DAO provides a way for developers to encapsulate the CRUD (Create, Read, Update, Delete) operations for a particular data entity (e.g. a Customer or Order) in a single place.
First, here’s an example without Spring’s JDBC abstraction layer or DAO pattern:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CustomerDAOWithoutSpring {
private final String url = "jdbc:mysql://localhost:3306/mydb";
private final String user = "root";
private final String password = "password";
public void createCustomer(Customer customer) throws SQLException {
try (Connection conn = DriverManager.getConnection(url, user, password)) {
String sql = "INSERT INTO customers (name, email) VALUES (?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, customer.getName());
stmt.setString(2, customer.getEmail());
stmt.executeUpdate();
}
}
}
public Customer getCustomerById(int id) throws SQLException {
try (Connection conn = DriverManager.getConnection(url, user, password)) {
String sql = "SELECT * FROM customers WHERE id = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setInt(1, id);
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
String name = rs.getString("name");
String email = rs.getString("email");
return new Customer(id, name, email);
} else {
return null;
}
}
}
}
}
}
This code defines a CustomerDAOWithoutSpring class that uses raw JDBC to perform basic CRUD operations on a MySQL database. Note that the code is verbose and requires developers to manually handle database connections, prepared statements, and result sets.
Now, here’s an example that uses Spring’s JDBC abstraction layer and DAO pattern:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.util.List;
@Repository
public class CustomerDAOWithSpring {
private final JdbcTemplate jdbcTemplate;
@Autowired
public CustomerDAOWithSpring(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
private final String url = "jdbc:mysql://localhost:3306/mydb";
private final String user = "root";
private final String password = "password";
private final DataSource dataSource = createDataSource();
private DataSource createDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
public void createCustomer(Customer customer) {
String sql = "INSERT INTO customers (name, email) VALUES (?, ?)";
jdbcTemplate.update(sql, customer.getName(), customer.getEmail());
}
public Customer getCustomerById(int id) {
String sql = "SELECT * FROM customers WHERE id = ?";
RowMapper<Customer> rowMapper = (rs, rowNum) ->
new Customer(rs.getInt("id"), rs.getString("name"), rs.getString("email"));
return jdbcTemplate.queryForObject(sql, new Object[]{id}, rowMapper);
}
public List<Customer> getAllCustomers() {
String sql = "SELECT * FROM customers";
RowMapper<Customer> rowMapper = (rs, rowNum) ->
new Customer(rs.getInt("id"), rs.getString("name"), rs.getString("email"));
return jdbcTemplate.query(sql, rowMapper);
}
public void updateCustomer(Customer customer) {
String sql = "UPDATE customers SET name = ?, email = ? WHERE id = ?";
jdbcTemplate.update(sql, customer.getName(), customer.getEmail(), customer.getId());
}
public void deleteCustomer(int id) {
String sql = "DELETE FROM customers WHERE id = ?";
jdbcTemplate.update(sql, id);
}
}
This code defines a CustomerDAOWithSpring class that uses Spring’s JdbcTemplate to perform the same CRUD operations on the same MySQL database. Note that the code is much shorter and easier to read, and the JdbcTemplate takes care of the low-level details of managing database.