Spring JDBC

Spring Framework (24 Part Series)

1 Spring Configuration
2 External Properties in Spring
… 20 more parts…
3 Profiles in Spring
4 Spring Expression Language (SpEL)
5 How Spring implements Singleton Beans
6 Converting explicit into implicit configuration in Spring
7 Autowiring in Spring
8 Lazy Beans in Spring
9 @PostConstruct and @PreDestroy in Spring
10 Stereotype and Meta Annotations in Spring
11 Spring’s FactoryBean Interface
12 The Spring Bean Lifecycle
13 Spring AOP
14 Caching in Spring
15 Spring JDBC
16 Spring Transaction Management
17 JPA with Spring
18 JPA with Spring Boot
19 Spring Web MVC
20 Spring Boot for war
21 Spring MVC REST
22 Reactive Spring Applications
23 Spring Security
24 Spring Injection Types

With traditional JDBC we got redundant (boilerplate) and error-prone code:

public Account findByNumber(String number ) {
        String sql = "select * from accounts " +
            "where number = ?";     
        Account account = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, number);
            rs = ps.executeQuery();
            account = mapAccount(rs);
        } catch (SQLException e) {
            throw new RuntimeException("SQL exception occurred", e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) { // OMG
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) { // OMG
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) { // OMG
                }
            }
        }
        return account;
    }

Enter fullscreen mode Exit fullscreen mode

With a try with resources we could make it easier, but not too much. Instead, with Spring JdbcTemplate we are using the Template Method Pattern, cleaning the code, eliminating repetitive boilerplate code, avoiding common bugs, and handling exceptions properly:

public Account findByNumber(String number) {
        String sql = "select * from accounts where number = ?";
        return jdbcTemplate.query(sql, accountExtractor, number);
    }

private class AccountExtractor implements ResultSetExtractor<Account> {

        public Account extractData(ResultSet rs) throws SQLException, DataAccessException {
            return mapAccount(rs);
        }

    }

Enter fullscreen mode Exit fullscreen mode

Spring acquires the connection, executes the statement, processes the result set, handles the exceptions, and releases the connection. You can query for a single or multiple rows. JdbcTemplate returns each row of a ResultSet as a Map. When expecting a single row use queryForMap, when expecting multiple rows use queryForList, when inserting, updating, or deleting use the update method:

    public Map<String, Object> getUser(int id) {
        String sql = "select * from users where id = ?";
        return jdbcTemplate.queryForMap(sql, id);
    }

    public List<Map<String, Object>> getUsers() {
        String sql = "select * from users";
        return jdbcTemplate.queryForList(sql);
    }

    public int insertUpdateOrDeleteUser(String sql) {
        return jdbcTemplate.update(sql);
    }

Enter fullscreen mode Exit fullscreen mode

See Spring Framework Documentation – Data Access with JDBC

See Spring JdbcTemplate Example

Spring Framework (24 Part Series)

1 Spring Configuration
2 External Properties in Spring
… 20 more parts…
3 Profiles in Spring
4 Spring Expression Language (SpEL)
5 How Spring implements Singleton Beans
6 Converting explicit into implicit configuration in Spring
7 Autowiring in Spring
8 Lazy Beans in Spring
9 @PostConstruct and @PreDestroy in Spring
10 Stereotype and Meta Annotations in Spring
11 Spring’s FactoryBean Interface
12 The Spring Bean Lifecycle
13 Spring AOP
14 Caching in Spring
15 Spring JDBC
16 Spring Transaction Management
17 JPA with Spring
18 JPA with Spring Boot
19 Spring Web MVC
20 Spring Boot for war
21 Spring MVC REST
22 Reactive Spring Applications
23 Spring Security
24 Spring Injection Types

原文链接：Spring JDBC