Spring中JdbcTemplate的CRUD和Dao的两种编写方式

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());
    }
}