springboot JdbcTemplate工具类封装

以你之姓@ 2023-06-20 06:13 21阅读 0赞

pom引入以下依赖

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                 <version>1.2.3</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.38</version>
            </dependency>
    
    
    
    
    
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.2.1.RELEASE</version>
     </dependency>
         <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency>
    </dependency>

application.properties添加数据库配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://localhost:3306/wangshengdb??characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
    spring.datasource.username=root
    spring.datasource.password=wangsheng

创建JdbcTemplate工具类

import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.stereotype.Service;
    import java.util.List;
    import java.util.Map;
    /**
     * 创 建 人 ： wangsheng 创建日期：2019年10月9日
     */
    @Service
    public class JDBC {
    
        @Autowired
        JdbcTemplate jdbcTemplate;
    
        //增加修改删除都用这个
        public void insertUpdate(String sql, Object[] obj) {
            jdbcTemplate.update(sql, obj);
        }
    
        //查询返回对象集合
        public <T> List selectListPojo(String sql, Class<T> t, Object[] obj) {
            List<Object> list = jdbcTemplate.query(sql, obj, (RowMapper<Object>) new BeanPropertyRowMapper<T>(t));
            return (List) list;
    
        }
    
        //查询返回对象
        public <T> T selectpojo(String sql, Class<T> t, Object[] obj) throws ClassNotFoundException {
            Object object = jdbcTemplate.queryForObject(sql, obj, new BeanPropertyRowMapper<T>(t));
            return (T) object;
        }
    
        //查询返回List<Map<String, Object>>
        public <T> List<Map<String, Object>> selectListMap(String sql, Object[] obj) {
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, obj);
            return (List) list;
        }
    
        //查询返回integer
        public int selectInteger(String sql, Object[] obj) {
            return jdbcTemplate.queryForObject(sql, Integer.class, obj);
        }
    
        //查询返回String
        public String selectString(String sql, Object[] obj) {
            return jdbcTemplate.queryForObject(sql, String.class, obj);
        }
    
        //查询返回List< String>
        public <T> List selectListString(String sql, Object[] obj) {
            List<String> list = jdbcTemplate.queryForList(sql, String.class, obj);
            return (List) list;
        }
    
        //查询返回List< Integer>
        public <T> List selectListObject(String sql, Object[] obj) {
            List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class, obj);
            return (List) list;
        }
    
    }

创建实体类

/**
     * 创 建 人 ： wangsheng 创建日期：2019年10月9日
     */
    public class Score {
        public Integer id;
        public String smart_card;
        public Integer score;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getSmart_card() {
            return smart_card;
        }
    
        public void setSmart_card(String smart_card) {
            this.smart_card = smart_card;
        }
    
        public Integer getScore() {
            return score;
        }
    
        public void setScore(Integer score) {
            this.score = score;
        }
    }

测试

import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.List;
    import java.util.Map;
    /**
     * 创 建 人 ： wangsheng 创建日期：2019年10月9日
     */
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class controller {
        @Autowired
        JDBC jdbc;
        
        @Test
        public void selectpojo() throws ClassNotFoundException {
            String sql = "select * from dtv_score where id =?";
            Object[] obj = new Object[]{1};
           score score = jdbc.selectpojo(sql, score.class,obj);
            System.out.println(score);
        }
        @Test
        public void selectListpojo() {
            String sql = "select * from dtv_score where id between  ? and ?";
            Object[] obj = new Object[]{1,4};
          List<score> list = jdbc.selectListPojo(sql,score.class,obj);
            System.out.println(list.get(0).getSmart_card());
        }
        @Test
        public void add() {
            String sql = "insert into dtv_score(smart_card,score) values(?,?)";
            Object[] obj = new Object[]{"safsad",10};
           jdbc.insertUpdate(sql,obj);
    
        }
        @Test
        public void selectListMap() {
            String sql = "select * from dtv_score where id between  ? and ?";
            Object[] obj = new Object[]{1, 4};
            List<Map<String, Object>> list = jdbc.selectListMap(sql, obj);
            Map<String, Object> map = list.get(0);
            System.out.println( list);
        }
    }