JdbcTemplate 操作 user 表

1
package demo.jdbc;
2

3
import org.springframework.jdbc.core.JdbcTemplate;
4
import org.springframework.jdbc.core.RowMapper;
5
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
6
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
7
import org.springframework.transaction.support.TransactionTemplate;
8

9
import javax.sql.DataSource;
10
import java.sql.ResultSet;
11
import java.sql.SQLException;
12
import java.util.List;
13
import java.util.Map;
14

15
public class JdbcTemplateDemo {
16
    private static final RowMapper<User> USER_ROW_MAPPER = (rs, rowNum) -> mapRow(rs);
17

18
    public static void main(String[] args) {
19
        DataSource ds = Db.dataSource();
20
        JdbcTemplate jdbc = new JdbcTemplate(ds);
21

22
        SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbc)
23
                .withTableName("user")
24
                .usingGeneratedKeyColumns("id");
25
        Number id = insert.executeAndReturnKey(Map.of(
26
                "user_name", "diana",
27
                "email", "diana@example.com",
28
                "status", 1
29
        ));
30
        System.out.println("insert id=" + id);
31

32
        User one = jdbc.queryForObject(
33
                "SELECT id, user_name, email, status, created_at FROM user WHERE id = ?",
34
                USER_ROW_MAPPER,
35
                id.longValue()
36
        );
37
        System.out.println("query: " + one);
38

39
        List<User> list = jdbc.query(
40
                "SELECT id, user_name, email, status, created_at FROM user WHERE status = ? ORDER BY id",
41
                USER_ROW_MAPPER,
42
                1
43
        );
44
        System.out.println("list size=" + list.size());
45

46
        TransactionTemplate tx = new TransactionTemplate(new DataSourceTransactionManager(ds));
47
        tx.execute(status -> {
48
            jdbc.update("UPDATE user SET email = ? WHERE id = ?", "diana.tx@example.com", id.longValue());
49
            return null;
50
        });
51
        System.out.println("after tx: " + jdbc.queryForObject(
52
                "SELECT id, user_name, email, status, created_at FROM user WHERE id = ?",
53
                USER_ROW_MAPPER,
54
                id.longValue()
55
        ));
56

57
        jdbc.update("DELETE FROM user WHERE id = ?", id.longValue());
58
        System.out.println("deleted diana");
59
    }
60

61
    private static User mapRow(ResultSet rs) throws SQLException {
62
        User user = new User();
63
        user.setId(rs.getLong("id"));
64
        user.setUserName(rs.getString("user_name"));
65
        user.setEmail(rs.getString("email"));
66
        user.setStatus(rs.getInt("status"));
67
        if (rs.getTimestamp("created_at") != null) {
68
            user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
69
        }
70
        return user;
71
    }
72
}