Spring-使用JDBC模板CRUD

插入操作

• 代码如下：

/**
 * @author: BNTang
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Demo {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void insertDemo() {
        jdbcTemplate.update("insert into account values (null, ?, ?)", "BNTang",10000d);
    }
}

删除操作

• 代码如下：

/**
 * @author: BNTang
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Demo {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void deleteDemo() {
        jdbcTemplate.update("delete from account where id = ?", 1);
    }
}

更新操作

• 代码如下：

/**
 * @author: BNTang
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Demo {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void updateDemo() {
        jdbcTemplate.update("update account set name = ?,money = ? where id = ?", "JonathanTang", 2000d, 8);
    }
}

查询操作

查询某一个字段

• 代码如下，通过 queryForObject 进行查询

/**
 * @author: BNTang
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Demo {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void queryDemo() {
        String name = jdbcTemplate.queryForObject("select name from account where id = ?", String.class, 8);

        System.out.println(name);
    }
}

• 还可以查询聚合函数的结果，代码如下：

/**
 * @author: BNTang
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Demo {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void queryDemo() {
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);

        System.out.println(count);
    }
}

查询返回对象的集合

• 创建 Account 实体类用来封装查询结果数据

/**
 * @author: BNTang
 **/
public class Account {

    private Integer id;
    private String name;
    private BigDecimal money;
    
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public BigDecimal getMoney() {
        return money;
    }

    public void setMoney(BigDecimal money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

• 测试代码如下：
• 这是查询 单行 数据
• 主要就是自己创建一个类实现 RowMapper 接口，实现当中的一个方法自己手动的进行封装

/**
 * @author: BNTang
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Demo {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void queryDemo() {
        Account account = jdbcTemplate.queryForObject("select * from account where id = ?", new MyRowMapper(), 8);

        System.out.println(account);
    }
}

class MyRowMapper implements RowMapper<Account>{

    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();

        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getBigDecimal("money"));

        return account;
    }
}

• 查询多行数据
• 测试代码如下：

/**
 * @author: BNTang
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Demo {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void queryDemo() {
        List<Account> accountList = jdbcTemplate.query("select * from account", new MyRowMapper());

        accountList.forEach(System.out::println);
    }
}

class MyRowMapper implements RowMapper<Account>{

    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        Account account = new Account();

        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getBigDecimal("money"));

        return account;
    }
}