使用jdbcTemplate操作数据库

返回目录

为简化jdbc操作，可以直接使用spring提供的JdbcTeamplate进行数据库的操作。

1. 配置依赖

相关数据库SQL

/* 用户表 */
drop database if exists boot0107;
create database boot0107;
use boot0107;

/* 创建用户表*/
create table if not exists tb_user(
    id varchar(36) comment '主键' not null,
    name varchar(50) comment '用户名',
    age int COMMENT '年龄',
    birthday datetime comment '出生日期',
		primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment '用户表' ;


insert into tb_user(id,name,age,birthday) values('1','张三',15,str_to_date('2019-01-23','%Y-%m-%d'));
insert into tb_user(id,name,age,birthday) values('2','李四',17,str_to_date('2012-03-21','%Y-%m-%d'));
insert into tb_user(id,name,age,birthday) values('3','王五',23,str_to_date('2010-11-23','%Y-%m-%d'));
insert into tb_user(id,name,age,birthday) values('4','赵六',35,str_to_date('2009-07-23','%Y-%m-%d'));
insert into tb_user(id,name,age,birthday) values('5','王二麻子',79,str_to_date('2018-12-23','%Y-%m-%d'));

添加maven依赖

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

添加数据库相关配置

spring:
  datasource:
    url: "jdbc:mysql://127.0.0.1:3306/boot0107?useSSL=false&useunicode=true&characterEncoding=utf8&autoReconnect=true\
              &rewriteBatchedStatements=true&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull"
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

2. 编写service

编写简单的增删改查，具体说明请看代码中注释

@Service("userService")
public class UserService {
    private static final Logger log = LoggerFactory.getLogger(UserService.class);
    @Resource
    private JdbcTemplate jdbcTemplate;

    public void addUser(User user){
        jdbcTemplate.update("insert into tb_user(id,name,age,birthday) values(?,?,?,?)",user.getId(),user.getName(),
                user.getAge(),user.getBirthday());
    }

    /**
     * 按照指定字段更新
     * @param params map类型参数
     * @param tableName 需要进行更新的数据库表
     * @param idName 主键字段名称
     */
    public void updateUser(Map<String,Object> params,String tableName,String idName){
        StringBuilder sqlBuff = new StringBuilder("update "+tableName+" set ");
        List<Object> paramsList =new ArrayList<>();
        for(Map.Entry entry:params.entrySet()){
            String key = entry.getKey().toString();
            if(!key.equals(idName)){
                sqlBuff.append(key+"=?,");
                paramsList.add(entry.getValue());
            }
        }
        paramsList.add(params.get(idName));
        String sql = sqlBuff.substring(0,sqlBuff.length()-1)+" where "+idName+" = ?";
        if(log.isInfoEnabled()){
            log.info("执行SQL。 sql->{},params-->{}",sql,paramsList.toArray());
        }
        jdbcTemplate.update(sql, paramsList.toArray());
    }

    /**
     * 按照主键进行删除
     * @param params 主键拼接的列表
     * @param tableName 需要进行更新的数据库表
     * @param idName 主键字段名称
     */
    public void deleteUser(List<Object> params,String tableName,String idName){
        if(params==null ||params.size()==0){
            return;
        }
        StringBuilder sqlBuff = new StringBuilder("delete from "+tableName+" where "+idName+" in ( ");
        int length = params.size();
        for(int i=0;i<length;i++){
            sqlBuff.append("?,");
        }
        String sql = sqlBuff.substring(0,sqlBuff.length()-1)+")";
        if(log.isDebugEnabled()){
            log.debug("执行SQL。 sql->{},params-->{}",sql,params.toArray());
        }
        jdbcTemplate.update(sql, params.toArray());
    }

    /**
     * @param idList  需要进行查询的id列表，使用RowMapper接口
     * @return
     */
    public List<User> queryList(List<Object> idList){
        StringBuilder sqlBuff  =new StringBuilder("select * from tb_user ");
        String sql =null;
        if(idList!=null&&idList.size()>0){
            int length = idList.size();
            if(length>0){
                sqlBuff.append("where id in ( ");
                for(int i=0;i<length;i++){
                    sqlBuff.append("?,");
                }
                sql = sqlBuff.substring(0,sqlBuff.length()-1)+")";
            }
        }else{
            sql = sqlBuff.toString();
        }
       List<User> retList = jdbcTemplate.query(sql, idList.toArray(), (rs,i)->{
           User user = new User();
           user.setId(rs.getString("id"));
           user.setBirthday(rs.getDate("birthday").toLocalDate());
           user.setName(rs.getString("name"));
           user.setAge(rs.getInt("age"));
           return user;
       });

       return retList;
    }

    /**
     * @param idList  需要进行查询的id列表，使用BeanPropertyRowMapper接口
     * @return
     */
    public List<User> queryList2(List<Object> idList){
        StringBuilder sqlBuff  =new StringBuilder("select * from tb_user ");
        String sql =null;
        if(idList!=null&&idList.size()>0){
            int length = idList.size();
            if(length>0){
                sqlBuff.append("where id in ( ");
                for(int i=0;i<length;i++){
                    sqlBuff.append("?,");
                }
                sql = sqlBuff.substring(0,sqlBuff.length()-1)+")";
            }
        }else{
            sql = sqlBuff.toString();
        }
        List<User> retList = jdbcTemplate.query(sql, idList.toArray(), new BeanPropertyRowMapper<>(User.class));

        return retList;
    }
}

3. 测试

@SpringBootTest
public class UserTest {

    @Autowired
    private UserService userService;


    @Test
    @DisplayName("user：add")
    public void test1(){
        User user = new User();
        user.setId("pid666");
        user.setAge(38);
        user.setName("张三9");
        user.setBirthday(LocalDate.of(2001,1,15));
        userService.addUser(user);
    }

    @Test
    @DisplayName("user：udpate")
    public void test2(){
        Map<String,Object> params = new HashMap<>();
        params.put("id","1");
        params.put("name","太祖长拳");
        params.put("birthday",LocalDate.of(2020,7,7));
        userService.updateUser(params,"tb_user","id");
    }

    @Test
    @DisplayName("user：delete")
    public void test3(){
        String tableName = "tb_user";
        String idName = "id";
        userService.deleteUser(null,tableName,idName);
        userService.deleteUser(Arrays.asList("pid666"),tableName,idName);

    }

    @Test
    @DisplayName("user：query")
    public void test4(){
        System.out.println("------------------");
        List<User> list = userService.queryList(Arrays.asList("1","3","4"));
        Optional.ofNullable(list).orElse(new ArrayList<>())
                .stream().forEach(System.out::println);
    }

    @Test
    @DisplayName("user：query by BeanPropertyRowMapper")
    public void test5(){
        System.out.println("------------------");
        List<User> list = userService.queryList(Arrays.asList("1","3","4"));
        Optional.ofNullable(list).orElse(new ArrayList<>())
                .stream().forEach(System.out::println);
    }

}

代码链接：https://gitee.com/johnny/learn-demo/tree/master/springboot-demo/boot01-07-jdbctemplate