JdbcTemplate的CRUD
/**
* @Date 2019/8/1 - 18:16
* JdbcTemplate的CRUD
*/
public class JdbcTemplateTest {
@Test
public void insertTest() {
//获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//获取对象
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
//保存
String sql = "insert into account(name,money) values (?,?)";
jt.update(sql, "ccc", 1000f);
}
@Test
public void updateTest() {
//获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//获取对象
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
//更新
String sql = "update account set name=?,money=? where id=?";
jt.update(sql, "ccc", 2000f, 5);
}
@Test
public void deleteTest() {
//获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//获取对象
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
//删除
String sql = "delete from account where id=?";
jt.update(sql, 5);
}
@Test
public void findAllTest() {
//获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//获取对象
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
//查询所有
String sql = "select * from account where money > ?";
List<Account> accounts = jt.query(sql, new BeanPropertyRowMapper<Account>(Account.class), 10f);
for (Account account : accounts) {
System.out.println(account);
}
}
@Test
public void findOneTest() {
//获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//获取对象
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
//查询一个
String sql = "select * from account where id = ?";
List<Account> accounts = jt.query(sql, new BeanPropertyRowMapper<Account>(Account.class), 1);
System.out.println(accounts.isEmpty() ? "" : accounts.get(0));
}
@Test
public void useAggregateFunTest() {
//获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//获取对象
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
//聚合函数使用
String sql = "select count(*) from account where money > ?";
Long count = jt.queryForObject(sql, Long.class, 10f);
System.out.println(count);
}
}
Dao两种编写方式
使用xml配置的实现方式
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
public Account findAccountById(Integer id) {
String sql = "select * from account where id = ?";
List<Account> accounts = super.getJdbcTemplate().query(sql, new BeanPropertyRowMapper<Account>(Account.class), id);
return accounts.isEmpty() ? null : accounts.get(0);
}
public Account findAccountByName(String name) {
String sql = "select * from account where name = ?";
List<Account> accounts = getJdbcTemplate().query(sql, new BeanPropertyRowMapper<Account>(Account.class), name);
if (accounts.isEmpty()) {
return null;
} else if (accounts.size() > 1) {
throw new RuntimeException();
} else {
return accounts.get(0);
}
}
public void updateAccount(Account account) {
String sql = "update account set name=?,money=? where id=?";
getJdbcTemplate().update(sql, account.getName(), account.getMoney(), account.getId());
}
}
xml
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/eesy_spring"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<bean id="accountDao" class="com.sx.dao.impl.AccountDaoImpl">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
使用注解的方式
public class AccountDaoImpl2 implements IAccountDao {
@Autowired
private JdbcTemplate template;
public Account findAccountById(Integer id) {
String sql = "select * from account where id = ?";
List<Account> accounts = template.query(sql, new BeanPropertyRowMapper<Account>(Account.class), id);
return accounts.isEmpty() ? null : accounts.get(0);
}
public Account findAccountByName(String name) {
String sql = "select * from account where name = ?";
List<Account> accounts = template.query(sql, new BeanPropertyRowMapper<Account>(Account.class), name);
if (accounts.isEmpty()) {
return null;
} else if (accounts.size() > 1) {
throw new RuntimeException();
} else {
return accounts.get(0);
}
}
public void updateAccount(Account account) {
String sql = "update account set name=?,money=? where id=?";
template.update(sql, account.getName(), account.getMoney(), account.getId());
}
}