目的
减少用JDBC编写访问数据库的程序中的重复代码.
连接数据库, 创建JdbcTemplate
以连接JDK自带的Java DB(Apache Derby)为例
package conf;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class AppConfig {
public JdbcTemplate jdbcTemplate() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.apache.derby.jdbc.ClientDriver");
dataSource.setUrl("jdbc:derby://localhost:1527/sample");
dataSource.setUsername("app");
dataSource.setPassword("app");
return new JdbcTemplate(dataSource);
}
}
访问数据库的DAO
访问数据库的DAO编写示例
package dao;
import java.util.List;
import model.User;
import org.springframework.jdbc.core.JdbcTemplate;
public class UserDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public List<User> getAll() {
List<User> users = this.jdbcTemplate.query("select * from person", (rs, rownum) -> {
return new User(rs.getString("userID"), rs.getInt("age"));
});
return users;
}
public void createTable() {
String sql = "CREATE TABLE person\n"
+ "(\n"
+ "userID varchar(255),\n"
+ "age int,\n"
+ "CONSTRAINT pk_userID PRIMARY KEY (userID)"
+ ")";
this.jdbcTemplate.execute(sql);
}
public void add(User user){
String sql = "INSERT INTO APP.PERSON (USERID, AGE) VALUES (?, ?)";
this.jdbcTemplate.update(sql, user.getUserID(), user.getAge());
}
public static void main(String[] args) {
JdbcTemplate jdbcTemplate1 = new conf.AppConfig().jdbcTemplate();
UserDao userDao = new UserDao();
userDao.setJdbcTemplate(jdbcTemplate1);
//userDao.createTable();
userDao.add(new User("用户03",27));
List<User> all = userDao.getAll();
all.stream().forEach(user -> System.out.println(user.getUserID() + " : " + user.getAge()));
}
}
模型
package model;
public class User {
private String userID;
private int age;
public User() {
}
public User(String userID, int age) {
this.userID = userID;
this.age = age;
}
public String getUserID() {
return userID;
}
public void setUserID(String userID) {
this.userID = userID;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User{" + "userID=" + userID + ", age=" + age + '}';
}
}
数据库
app.person表的数据如下
运行结果
user00 : 19
用户01 : 22
用户03 : 27
结果说明, 增加一个用户, userDao.add(new User(“用户03”,27));
然后用 List all = userDao.getAll(); 查询出增加后的全部用户.