1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
package com.example.fp.dao.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import com.example.fp.User;
import com.example.fp.dao.UserDAO;
public class JDBCUserDAO implements UserDAO {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void create(User user) {
final String INSERT_SQL = "insert into user_account (username, full_name, password, active) values(?, ?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"uid"});
ps.setString(1, user.getUsername());
ps.setString(2, user.getFullName());
ps.setString(3, user.getPassword());
ps.setInt(4, user.isActive() ? 1 : 0);
return ps;
}
}, keyHolder);
// keyHolder.getKey() now contains the generated key
user.setUid(keyHolder.getKey().intValue());
}
@Override
public void delete(int uid) {
jdbcTemplate.update("delete from user_account where uid = ?", Integer.valueOf(uid));
}
@Override
public List<User> findAll() {
return this.jdbcTemplate.query( "select * from user_account", new UserMapper());
}
@Override
public User find(int uid) {
return this.jdbcTemplate.queryForObject( "select * from user_account where uid = ?", new Object[] {Integer.valueOf(uid)}, new UserMapper());
}
@Override
public User find(String username) {
return this.jdbcTemplate.queryForObject( "select * from user_account where username = ?", new Object[] {username}, new UserMapper());
}
@Override
public void update(User user) {
this.jdbcTemplate.update("update user_account set username = ?, full_name = ?, password = ?, active = ? where uid = ?",
user.getUsername(), user.getFullName(), user.getPassword(), user.isActive() ? 1 : 0, user.getUid());
}
private static final class UserMapper implements RowMapper<User> {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setUid(rs.getInt("uid"));
user.setUsername(rs.getString("username"));
user.setFullName(rs.getString("full_name"));
user.setPassword(rs.getString("password"));
user.setActive(rs.getBoolean("active"));
return user;
}
}
}
|
