【无标题】_Java

Spring框架之JdbcTemplate增删改查操作

文章目录

Spring框架之JdbcTemplate增删改查操作
前言
一、JDBCTemplate简单介绍
二、使用步骤
- 1.引入所需的依赖
- 2.连接数据库的操作
- - 2.1、jdbc.properties文件
  - 2.2、连接数据库
  - 2.2、SpringConfig 用于合并写的配置文件
- 3.实体类
- 4.数据库操作的代码
- - 4.1、dao层
  - - StudyDao类
    - 实现StudyDao类
  - 4.2、service
  - - StudyService 类
    - 实现StudyService 类
  - 测试
总结

前言

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

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

一、JDBCTemplate简单介绍

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

二、使用步骤 1.引入所需的依赖

代码如下（示例）：



    mysql
    mysql-connector-java
    5.1.49



    c3p0
    c3p0
    0.9.1.2



    com.alibaba
    druid
    1.1.10

 

    org.springframework
    spring-jdbc
    5.0.5.RELEASE



    org.springframework
    spring-test
    5.0.5.RELEASE


        
            org.springframework
            spring-tx
            5.0.5.RELEASE

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;
    }
    ```
    
    @Bean("jdbcTemplate")
    public JdbcTemplate jdbcTemplate(ComboPooledDataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
        
    @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 {

    
    void addStudy(Study study);

    
    boolean delete(Long id);

    
    Study selectStudy(Long id);

    
    List findAllStudy();


    
    void bathAddStudy(List bathStudy);


    
    void updateStudy(Study study);


    
    void bathDeleteStudy(List batchStudy);

    
    void batchUpdateStudy(List 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.class),id);
        return study;
    }

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

    @Override
    public void bathAddStudy(List 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 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 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 {
    
    boolean addStudy(Study study);


    
    Study selectStudy(Long id);

    
    List findAllStudy();

    
    void batchAddStudy(List studies);

    
    boolean deleteStudy(Long id);

    
    void updateStudy(Study study);

    
    void batchDeleteStudy(List objects);

    
    void batchUpdateStudy(List 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 findAllStudy() {
        List allStudy = studyDao.findAllStudy();
        return allStudy;
    }

    @Override
    public void batchAddStudy(List studies) {
        List objectsList = new ArrayList();
        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 objects) {
        studyDao.bathDeleteStudy(objects);
    }

    @Override
    public void batchUpdateStudy(List batchUpdates) {
        studyDao.batchUpdateStudy(batchUpdates);
    }
}

测试


@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 allStudy = studyService.findAllStudy();
        System.out.println(allStudy);
    }


    @Test
    public void testBatchAddStudy(){
        ArrayList 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 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 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);
    }
}

总结

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

【无标题】

Java相关栏目本月热门文章