最近学了一下 Spring 的 JDBC 框架,写一篇文章总结一下,因为不太罕用,我就简略总结一下增删改查的用法,这里是基于 XML 配置进行的
留神:我这里应用的是本人的数据库,请将配置信息里的 datasource 数据源信息以及 sql 语句改成本人的电脑上的
- domain 实体类
public class User {
private String account;
private String password;
private Float balance;
public User(String account, String password, Float balance) {
this.account = account;
this.password = password;
this.balance = balance;
}
public User(){}
@Override
public String toString() {
return "User{" +
"account='" + account + '''+", password='"+ password +''' +
", balance=" + balance +
'}';
}
public String getAccount() {return account;}
public void setAccount(String account) {this.account = account;}
public String getPassword() {return password;}
public void setPassword(String password) {this.password = password;}
public Float getBalance() {return balance;}
public void setBalance(Float balance) {this.balance = balance;}
}
- dao 层的类
public class TestJDBC {
// 数据源:在配置文件中配置,通过 Spring 主动注入
private DataSource dataSource;
// 调用增删改查的执行者
private JdbcTemplate jdbcTemplate;
public TestJDBC(){}
// 初始化
public TestJDBC(DataSource dataSource){
this.dataSource = dataSource;
jdbcTemplate = new JdbcTemplate(this.dataSource);
}
public void insert(User user){
// 通过?代替传递进去的参数,必须和 update 办法传进去的参数程序统一
String sql = "insert into atm values(?, ?, ?)";
jdbcTemplate.update(sql, user.getAccount(), user.getPassword(), user.getBalance());
}
public void delete(String account){
String sql = "delete from atm where account = ?";
jdbcTemplate.update(sql, account);
}
public void update(User user){
String sql = "update atm set account = ?, password = ?, balance = ? where account = ?";
jdbcTemplate.update(sql, user.getAccount(), user.getPassword(), user.getBalance(), user.getAccount());
}
// 查问一条记录
public User selectOne(String account){
// 通过策略模式,将查问的后果包装成 User 对象
String sql = "select * from atm where account = ?";
return jdbcTemplate.queryForObject(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {String account = resultSet.getString("account");
String password = resultSet.getString("password");
Float balance = resultSet.getFloat("balance");
return new User(account, password, balance);
}
}, account);
}
// 查问多条记录
public List<User> selectList(){
String sql = "select *from atm";
//query 办法依据该办法的返回值类型,将所有记录包装成 List 对象,而策略模式是将每一行记录包装成 User 对象
return jdbcTemplate.query(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {String account = resultSet.getString("account");
String password = resultSet.getString("password");
Float balance = resultSet.getFloat("balance");
return new User(account, password, balance);
}
});
}
}
- 配置信息
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- JDBC-->
<bean id="userJdbcTemplate" class="com.cjh.dao.TestJDBC" autowire="constructor"></bean>
<bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/bank?serverTimezone=CST"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
</beans>
- 主函数调用
public class TestMain {public static void main(String[] args) {System.out.println(userDao.selectUserByAccount("2018"));
ApplicationContext context = new ClassPathXmlApplicationContext("ApplicationContext.xml");
TestJDBC testJDBC = (TestJDBC) context.getBean("userJdbcTemplate");
//testJDBC.insert(new User("0000", "0000", 999.0f));
//testJDBC.insert(new User("0001", "0000", 999.0f));
//testJDBC.delete("0000");
//testJDBC.update(new User("0001", "1234", 999.0f));
System.out.println(testJDBC.selectList());
}
}