# Spring框架之JdbcTemplate增删改查操作

前言

提前言：本文记录自己在学习spring框架中JdbcTemplate的增删改查操作。如有缺点望大家多多指教。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、JDBCTemplate简单介绍

大家可以看看这个，我就不多加介绍了。
https://blog.csdn.net/qq_22075913/article/details/108196005

二、使用步骤

1.引入所需的依赖

代码如下（示例）：

<!-- mysql驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>
<!-- C3P0连接池 -->
<dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
</dependency>
<!-- Druid连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>
<!-- jdbc依赖 --> 
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.0.5.RELEASE</version>
</dependency>
<!--此处需要注意的是，spring5 及以上版本要求 junit 的版本必须是 4.12 及以上-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.0.5.RELEASE</version>
</dependency>
<!--事务-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.0.5.RELEASE</version>
        </dependency>

2.连接数据库的操作

2.1、jdbc.properties文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/account
jdbc.username=root
jdbc.password=123456

2.2、连接数据库

@PropertySource("classpath:jdbc.properties")
public class mysqlConfig {
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.driver}")
    private String driver;
    @Value("${jdbc.username}")
    private String username;
    @Value("${jdbc.password}")
    private String password;
    
    @Bean("dataSource")
    public ComboPooledDataSource getSource() throws Exception {
        //创建数据源
        ComboPooledDataSource source = new ComboPooledDataSource();
        //设置数据库连接参数
        source.setJdbcUrl(url);
        source.setDriverClass(driver);
        source.setUser(username);
        source.setPassword(password);
        return source;
    }
    ```
    /**
     * 将数据源给到jdbcTemplate
     * @param dataSource
     * @return
     */
    @Bean("jdbcTemplate")
    public JdbcTemplate jdbcTemplate(ComboPooledDataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
        /**
     * 配置事务
     * @param dataSource
     * @return
     */
    @Bean("dataSourceTransactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(ComboPooledDataSource dataSource){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);
        return dataSourceTransactionManager;
    }
    

2.2、SpringConfig 用于合并写的配置文件

@Configuration
@ComponentScan("com.qiu")
@Import(mysqlConfig.class)
@EnableTransactionManagement //开启注解事务
public class SpringConfig {
}

3.实体类

代码如下（示例）：

public class Study {
    private int id;
    private String name;
    private Integer chinese;
    private Integer math;
    private Integer english;
    private Integer sno;

    public Study() {
    }
    public Study(int id, String name, Integer chinese, Integer math, Integer english, Integer sno) {
        this.id = id;
        this.name = name;
        this.chinese = chinese;
        this.math = math;
        this.english = english;
        this.sno = sno;
    }

该处的get，set，toString方法请自己添加哦。

---

4.数据库操作的代码

4.1、dao层

StudyDao类

public interface StudyDao {

    /**
     * 添加学生成绩信息
     * @param study
     */
    void addStudy(Study study);

    /**
     * 根据id删除学生信息
     * @param id
     * @return
     */
    boolean delete(Long id);

    /**
     * 根据id查询学生信息
     * @param id
     * @return
     */
    Study selectStudy(Long id);

    /**
     * 查询所有学生信息
     * @return
     */
    List<Study> findAllStudy();


    /**
     * 批量添加学生成绩
     * @param bathStudy
     */
    void bathAddStudy(List<Object[]> bathStudy);


    /**
     * 修改学生成绩
     * @param study
     */
    void updateStudy(Study study);


    /**
     * 批量删除
     * @param batchStudy
     */
    void bathDeleteStudy(List<Object[]> batchStudy);

    /**
     * 批量修改学生成绩
     * @param batchUpdate
     */
    void batchUpdateStudy(List<Object[]> batchUpdate);
}

实现StudyDao类

@Repository("studyDao")
public class StudyDaoImpl implements StudyDao {
    //注入JdbcTemplate
    @Resource(description = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @Override
    public void addStudy(Study study) {
        //编写sql 方法一
        String sql = "insert into study(name,chinese,math,english,sno) values (?,?,?,?,?)";
        //sql参数
        Object[] objs ={study.getName(),study.getChinese(),study.getMath(),study.getEnglish(),study.getSno()};
        //执行语句
        int update = jdbcTemplate.update(sql,objs);
        //方法二
//        jdbcTemplate.update("insert into study(name,chinese) values (?,?)",study.getName(),study.getChinese());

        System.out.println(update);
    }

    @Override
    public boolean delete(Long id) {
        //编写sql
        String sql = "delete from study where id = ?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println(update);
        return true;
    }

    @Override
    public Study selectStudy(Long id) {
        //编写sql
        String sql = "select * from study where id = ?";
        //执行语句
        Study study = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<Study>(Study.class),id);
        return study;
    }

    @Override
    public List<Study> findAllStudy() {
        //编写sql
        String sql = "select * from study";
        //执行语句
        List<Study> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Study>(Study.class));
        return query;
    }

    @Override
    public void bathAddStudy(List<Object[]> bathStudy) {
        //编写sql
        String sql = "insert into study(name,chinese,math,english,sno) values (?,?,?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, bathStudy);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void updateStudy(Study study) {
        String sql = "update study set name = ?,chinese = ?,math = ?,english = ?,sno = ? where id = ?";
        Object[] objects = {study.getName(),study.getChinese(),study.getMath(),study.getEnglish(),study.getSno(),study.getId()};
        int update = jdbcTemplate.update(sql, objects);
        System.out.println(update);
    }

    @Override
    public void bathDeleteStudy(List<Object[]> batchStudy) {
        String sql = "delete from study where id = ?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchStudy);
        System.out.println(Arrays.toString(ints));
    }

    @Override
    public void batchUpdateStudy(List<Object[]> batchUpdate) {
        String sql = "update study set name = ?,chinese = ?,math = ?,english = ?,sno = ? where id = ?";
        int[] ints = jdbcTemplate.batchUpdate(sql, batchUpdate);
        System.out.println(Arrays.toString(ints));
    }
}

4.2、service

StudyService 类

public interface StudyService {
    /**
     * 添加学生成绩
     * @param study
     * @return
     */
    boolean addStudy(Study study);


    /**
     * 根据id查询学生信息
     * @param id
     * @return
     */
    Study selectStudy(Long id);

    /**
     * 查询所有学生成绩
     * @return
     */
    List<Study> findAllStudy();

    /**
     * 批量添加学生成绩
     * @param studies
     */
    void batchAddStudy(List<Study> studies);

    /**
     * 根据id删除学生成绩
     * @param id
     * @return
     */
    boolean deleteStudy(Long id);

    /**
     * 修改学生成绩信息
     * @param study
     */
    void updateStudy(Study study);

    /**
     * 批量删除学生成绩信息
     * @param objects
     */
    void batchDeleteStudy(List<Object[]> objects);

    /**
     * 批量修改学生成绩信息
     * @param batchUpdates
     */
    void batchUpdateStudy(List<Object[]> batchUpdates);
}

实现StudyService 类

@Service
public class StudyServiceImpl implements StudyService{

    @Resource
    private StudyDao studyDao;


    @Override
    @Transactional
    public boolean addStudy(Study study) {
        studyDao.addStudy(study);
//        int[] arr = new int[3];
//        arr[4] = 0;

        return false;
    }

    @Override
    public Study selectStudy(Long id) {
        Study study = studyDao.selectStudy(id);
        return study;
    }

    @Override
    public List<Study> findAllStudy() {
        List<Study> allStudy = studyDao.findAllStudy();
        return allStudy;
    }

    @Override
    public void batchAddStudy(List<Study> studies) {
        List<Object[]> objectsList = new ArrayList<Object[]>();
        for (Study study:studies){
            //将学生列表中的数据提取出来
            Object[] objects = {study.getName(),study.getChinese(),study.getMath(),study.getEnglish(),study.getSno()};
            objectsList.add(objects);
        }
        //将数据添加到数据库
        studyDao.bathAddStudy(objectsList);
    }

    @Override
    public boolean deleteStudy(Long id) {
        boolean delete = studyDao.delete(id);
        return delete;
    }

    @Override
    public void updateStudy(Study study) {
        studyDao.updateStudy(study);
    }

    @Override
    public void batchDeleteStudy(List<Object[]> objects) {
        studyDao.bathDeleteStudy(objects);
    }

    @Override
    public void batchUpdateStudy(List<Object[]> batchUpdates) {
        studyDao.batchUpdateStudy(batchUpdates);
    }
}

测试

/**
 * spring集成junit
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {SpringConfig.class})
public class SpringJunitTest {

    @Autowired
    private StudyService studyService;

    @Test
    public void testAddStudy(){
        Study study = new Study();
        study.setChinese(88);
        study.setEnglish(90);
        study.setMath(88);
        study.setName("七");
        study.setSno(20);
        studyService.addStudy(study);
    }


    @Test
    public void testSelectStudy(){
        Study study = studyService.selectStudy(1l);
        System.out.println(study);
    }

    @Test
    public void testFindAllStudy(){
        List<Study> allStudy = studyService.findAllStudy();
        System.out.println(allStudy);
    }


    @Test
    public void testBatchAddStudy(){
        ArrayList<Study> list = new ArrayList<>();
        for (int i = 0;i < 10;i++){
            Study study = new Study();
            study.setChinese(88);
            study.setEnglish(90);
            study.setMath(88);
            study.setName("七七九九");
            study.setSno(20);
            list.add(study);
        }
        studyService.batchAddStudy(list);
    }

    @Test
    public void testDelete(){
        boolean b = studyService.deleteStudy(226l);
        System.out.println(b);
    }

    @Test
    public void testUpdate(){
        Study study = new Study();
        study.setId(48);
        study.setName("九九");
        study.setMath(60);
        study.setEnglish(90);
        study.setChinese(99);
        study.setSno(202099);
        studyService.updateStudy(study);
    }

    @Test
    public void testBatchDeleteStudy(){
        ArrayList<Object[]> list = new ArrayList<>();
//        Object[] os = {"239"};
//        Object[] os2 = {"240"};
//        list.add(os);
//        list.add(os2);
        for (int i = 241;i<=247;i++){
            Object[] o = {i};
            list.add(o);
        }
        studyService.batchDeleteStudy(list);
    }

    @Test
    public void testBatchUpdateStudy(){
        ArrayList<Object[]> list = new ArrayList<>();
        Object[] o1 = {"九九","44","44","44","23232","16"};
        Object[] o2 = {"九九","44","44","44","23232","17"};
        list.add(o1);
        list.add(o2);
        studyService.batchUpdateStudy(list);
    }
}

总结

有问题的大家可以多多指教。