Spring-boot-jdbc

1. SQL

   DROP TABLE IF EXISTS `t_user`;
   CREATE TABLE `t_user`
   (
       `user_id`     int(11) primary key NOT NULL AUTO_INCREMENT,
       `username`    varchar(255)        NOT NULL,
       `password`    varchar(255)        NOT NULL,
       `nickname`    varchar(255)        NOT NULL,
       `email`       varchar(100)        NOT NULL,
       `create_time` datetime            NOT NULL,
       `update_time` datetime            NOT NULL,
       `is_deleted`  int(1)              NOT NULL
   ) ENGINE = Innodb
     DEFAULT CHARSET = utf8;
   

2. POM.xml

   <?xml version="1.0" encoding="UTF-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <parent>
       <artifactId>springboot</artifactId>
       <groupId>com.shawn</groupId>
       <version>1.0-SNAPSHOT</version>
     </parent>
     <modelVersion>4.0.0</modelVersion>
   
     <artifactId>spring-boot-jdbc-demo</artifactId>
   
     <dependencies>
       <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter</artifactId>
       </dependency>
       <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-jdbc</artifactId>
       </dependency>
       <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
       </dependency>
       <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
       </dependency>
     </dependencies>
   
   </project>
   

3. 实体类

   @Data
   @NoArgsConstructor
   @AllArgsConstructor
   @Builder
   public class User {
     private int userId;
     private String username;
     private String password;
     private String nickname;
     private String email;
     private Date createTime;
     private Date updateTime;
     private boolean isDeleted;
   }
   

4. Dao

   @Repository
   public class UserDao {
   
     @Autowired private JdbcTemplate jdbcTemplate;
   
     @Autowired private SimpleJdbcInsert jdbcInsert;
   
     @Autowired private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
   
     private static final String INSERT_SQL =
         "insert into t_user (username, password, nickname, email, create_time, update_time, is_deleted) values (?,?,?,?,?,?,?)";
   
     private static final String SELECT_SQL = "select * from t_user where is_deleted = false";
   
     private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
   
     public void insertWithJbdcTemplate(User user) {
       jdbcTemplate.update(
           INSERT_SQL,
           user.getUsername(),
           user.getPassword(),
           user.getNickname(),
           user.getEmail(),
           user.getCreateTime(),
           user.getUpdateTime(),
           user.isDeleted());
     }
   
     public void batchInsertWithBatchPreparedStatementSetter(List<User> users) {
       jdbcTemplate.batchUpdate(
           INSERT_SQL,
           new BatchPreparedStatementSetter() {
             @Override
             public void setValues(PreparedStatement ps, int i) throws SQLException {
               User user = users.get(i);
               ps.setString(1, user.getUsername());
               ps.setString(2, user.getPassword());
               ps.setString(3, user.getNickname());
               ps.setString(4, user.getEmail());
               ps.setDate(5, new java.sql.Date(user.getCreateTime().getTime()));
               ps.setDate(6, new java.sql.Date(user.getUpdateTime().getTime()));
               ps.setBoolean(7, user.isDeleted());
             }
   
             @Override
             public int getBatchSize() {
               return users.size();
             }
           });
     }
   
     public void batchInsertWithList(List<User> users) {
       List<Object[]> arrList = new ArrayList<>();
       for (User user : users) {
         List<Object> objs = new ArrayList<>();
         objs.add(user.getUsername());
         objs.add(user.getPassword());
         objs.add(user.getNickname());
         objs.add(user.getEmail());
         objs.add(user.getCreateTime());
         objs.add(user.getUpdateTime());
         objs.add(user.isDeleted());
         arrList.add(objs.toArray());
       }
       jdbcTemplate.batchUpdate(INSERT_SQL, arrList);
     }
   
     public void batchInsertWithNamedParams(List<User> users) {
       String sql =
           "insert into t_user (username, password, nickname, email, create_time, update_time) values (:username, :password, :nickname, :email, :createTime, :updateTime)";
       namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(users));
     }
   
     public int insertReturnKey(User user) {
       Number key = jdbcInsert.withTableName("t_user").executeAndReturnKey(toMap(user));
       return key.intValue();
     }
   
     public List<User> selectAll() {
       return jdbcTemplate.query(
           SELECT_SQL,
           (rs, rowNum) ->
               User.builder()
                   .userId(rs.getInt(1))
                   .username(rs.getString(2))
                   .password(rs.getString(3))
                   .nickname(rs.getString(4))
                   .email(rs.getString(5))
                   .createTime(rs.getDate(6))
                   .updateTime(rs.getDate(7))
                   .isDeleted(rs.getBoolean(8))
                   .build());
     }
   
     public User selectById(int id) {
       List<User> users =
           jdbcTemplate.query(
               SELECT_SQL + " and user_id = ? ",
               new Object[] {id},
               (rs, rowNum) ->
                   User.builder()
                       .userId(rs.getInt(1))
                       .username(rs.getString(2))
                       .password(rs.getString(3))
                       .nickname(rs.getString(4))
                       .email(rs.getString(5))
                       .createTime(rs.getDate(6))
                       .updateTime(rs.getDate(7))
                       .isDeleted(rs.getBoolean(8))
                       .build());
       return CollectionUtils.isEmpty(users) ? null : users.get(0);
     }
   
     public List<User> selectByExample(User user) {
       Map<String, List<Object>> listMap = getFieldValue(user, true);
       List<Object> cols = listMap.get("cols");
       List<Object> values = listMap.get("values");
       StringBuilder builder = new StringBuilder("select * from t_user where 1 = 1");
       for (int i = 0; i < cols.size(); i++) {
         String col = cols.get(i).toString();
         if ("userId".equals(col)) {
           values.remove(i);
           continue;
         }
         if ("isDeleted".equals(col)) {
           builder.append(" and is_deleted = ? ");
           continue;
         }
         if ("createTime".equals(col)) {
           builder.append(" and create_time = ? ");
           continue;
         }
         if ("updateTime".equals(col)) {
           builder.append(" and update_time = ? ");
           continue;
         }
         builder.append(" and " + col + " = ? ");
       }
       return jdbcTemplate.query(
           builder.toString(),
           values.toArray(),
           (rs, rowNum) ->
               User.builder()
                   .userId(rs.getInt(1))
                   .username(rs.getString(2))
                   .password(rs.getString(3))
                   .nickname(rs.getString(4))
                   .email(rs.getString(5))
                   .createTime(rs.getDate(6))
                   .updateTime(rs.getDate(7))
                   .isDeleted(rs.getBoolean(8))
                   .build());
     }
   
     public void update(User user) {
       StringBuilder builder = new StringBuilder("update t_user set user_id = user_id");
       int userId = user.getUserId();
       final String whereSql = "where user_id = ? ";
       Map<String, List<Object>> listMap = getFieldValue(user, true);
       List<Object> cols = listMap.get("cols");
       List<Object> values = listMap.get("values");
       for (int i = 0; i < cols.size(); i++) {
         String col = cols.get(i).toString();
         if ("userId".equals(col)) {
           values.remove(i);
           continue;
         }
         if ("isDeleted".equals(col)) {
           builder.append(" , is_deleted = ? ");
           continue;
         }
         if ("createTime".equals(col)) {
           builder.append(" , create_time = ? ");
           continue;
         }
         if ("updateTime".equals(col)) {
           builder.append(" , update_time = ? ");
           continue;
         }
         builder.append(", " + col + " = ? ");
       }
       builder.append(whereSql);
       values.add(userId);
       jdbcTemplate.update(builder.toString(), values.toArray());
     }
   
     public void delete(int id) {
       String sql = "delete from t_user where user_id = ?";
       jdbcTemplate.update(sql, id);
     }
   
     private Map<String, Object> toMap(User user) {
       Map<String, Object> map = new HashMap<>();
       map.put("is_deleted", user.isDeleted());
       String username = user.getUsername();
       if (StringUtils.hasText(username)) {
         map.put("username", username);
       }
       String password = user.getPassword();
       if (StringUtils.hasText(password)) {
         map.put("password", password);
       }
       String nickname = user.getNickname();
       if (StringUtils.hasText(nickname)) {
         map.put("nickname", nickname);
       }
       String email = user.getEmail();
       if (StringUtils.hasText(email)) {
         map.put("email", email);
       }
       Date createTime = user.getCreateTime();
       if (null != createTime) {
         map.put("create_time", createTime);
       }
       Date updateTime = user.getUpdateTime();
       if (null != updateTime) {
         map.put("update_time", updateTime);
       }
       int id = user.getUserId();
       if (id != 0) {
         map.put("user_id", id);
       }
       return map;
     }
   
     private Map<String, List<Object>> getFieldValue(User user, boolean ignoreEmpty) {
       Map<String, List<Object>> map = new HashMap<>();
       List<Object> cols = new ArrayList<>();
       List<Object> values = new ArrayList<>();
       try {
         Field[] fields = user.getClass().getDeclaredFields();
         for (Field field : fields) {
           field.setAccessible(true);
           Object o = field.get(user);
           if (!ignoreEmpty || null != o) {
             cols.add(field.getName());
             values.add(o);
           }
         }
       } catch (IllegalAccessException e) {
         e.printStackTrace();
       }
       map.put("cols", cols);
       map.put("values", values);
       return map;
     }
   }
   

5. Service

   public interface UserService {
   
     void insert(User user);
   
     void batchInsertWithJbdcTemplate(List<User> users);
   
     void batchInsertWithNamedParams(List<User> users);
   
     void batchInsertWithBatchPreparedStatementSetter(List<User> users);
   
     int insertReturnKey(User user);
   
     List<User> findAll();
   
     User findById(int id);
   
     List<User> findByCondition(User user);
   
     void modify(User user);
   
     void remove(int id);
   }
   

   @Service
   @Slf4j
   public class UserServiceImpl implements UserService {
   
     @Autowired private UserDao dao;
   
     @Override
     public void insert(User user) {
       dao.insertWithJbdcTemplate(user);
     }
   
     @Override
     public void batchInsertWithJbdcTemplate(List<User> users) {
       dao.batchInsertWithList(users);
     }
   
     @Override
     public void batchInsertWithNamedParams(List<User> users) {
       dao.batchInsertWithNamedParams(users);
     }
   
     @Override
     public void batchInsertWithBatchPreparedStatementSetter(List<User> users) {
       dao.batchInsertWithBatchPreparedStatementSetter(users);
     }
   
     @Override
     public int insertReturnKey(User user) {
       return dao.insertReturnKey(user);
     }
   
     @Override
     public List<User> findAll() {
       return dao.selectAll();
     }
   
     @Override
     public User findById(int id) {
       return dao.selectById(id);
     }
   
     @Override
     public List<User> findByCondition(User user) {
       return dao.selectByExample(user);
     }
   
     @Override
     public void modify(User user) {
       dao.update(user);
     }
   
     @Override
     public void remove(int id) {
       dao.delete(id);
     }
   }
   

6. 启动类

   @SpringBootApplication
   public class JdbcTemplateApplication {
   
     public static void main(String[] args) {
       SpringApplication.run(JdbcTemplateApplication.class, args);
     }
   
     @Bean
     @Autowired
     public SimpleJdbcInsert simpleJdbcInsert(JdbcTemplate jdbcTemplate) {
       return new SimpleJdbcInsert(jdbcTemplate).usingGeneratedKeyColumns("id");
     }
   
     @Bean
     @Autowired
     public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
       return new NamedParameterJdbcTemplate(dataSource);
     }
   }
   

7. 配置文件

   spring.datasource.url: jdbc:mysql://127.0.0.1:3306/spring-boot-demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
   spring.datasource.username: root
   spring.datasource.password: 11111
   spring.datasource.driver-class-name: com.mysql.cj.jdbc.Driver
   

8. Test Case

   @Component
   @Slf4j
   public class JdbcTemplateApplicationRunner implements ApplicationRunner {
   
     @Autowired private UserService service;
   
     @Override
     public void run(ApplicationArguments args) throws Exception {
       log.info("test insert");
       testInsert();
       log.info("test batch insert");
       testBatchInsert();
       log.info("test insert return key");
       int i = testInsertReturnKey();
       log.info("test find all");
       testFindAll();
       log.info("test find by id");
       testFindById(i);
       log.info("test find by condition");
       testFindByCondition();
       log.info("before update");
       testFindById(i);
       Thread.sleep(1000);
       log.info("action updating");
       testUpdate(i);
       log.info("after update");
       testFindById(i);
       log.info("test delete");
       testDelete(i);
       log.info("after delete");
       testFindById(i);
     }
   
     private void testInsert() {
       Date now = new Date();
       User user =
           User.builder()
               .username("shawn")
               .password("shawn")
               .nickname("shawn")
               .email("1111@qq.com")
               .createTime(now)
               .updateTime(now)
               .isDeleted(false)
               .build();
       service.insert(user);
     }
   
     private void testBatchInsert() throws InterruptedException {
       Date now = new Date();
       User jack =
           User.builder()
               .username("jack")
               .password("jack")
               .nickname("jack")
               .email("2222@qq.com")
               .createTime(now)
               .updateTime(now)
               .isDeleted(false)
               .build();
       service.batchInsertWithJbdcTemplate(Arrays.asList(jack));
       Thread.sleep(1000);
       User bill =
           User.builder()
               .username("bill")
               .password("bill")
               .nickname("bill")
               .email("3333@qq.com")
               .createTime(new Date())
               .updateTime(new Date())
               .isDeleted(false)
               .build();
       Thread.sleep(1000);
       User john =
           User.builder()
               .username("john")
               .password("john")
               .nickname("john")
               .email("4444@qq.com")
               .createTime(new Date())
               .updateTime(new Date())
               .isDeleted(false)
               .build();
       service.batchInsertWithBatchPreparedStatementSetter(Arrays.asList(bill, john));
       Thread.sleep(1000);
       User bob =
           User.builder()
               .username("bob")
               .password("bob")
               .nickname("bob")
               .email("5555@qq.com")
               .createTime(new Date())
               .updateTime(new Date())
               .build();
       Thread.sleep(1000);
       User jackson =
           User.builder()
               .username("jackson")
               .password("jackson")
               .nickname("jackson")
               .email("6666@qq.com")
               .createTime(new Date())
               .updateTime(new Date())
               .build();
       service.batchInsertWithNamedParams(Arrays.asList(bob, jackson));
     }
   
     private int testInsertReturnKey() {
       User johnson =
           User.builder()
               .username("johnson")
               .password("johnson")
               .nickname("johnson")
               .email("7777@qq.com")
               .createTime(new Date())
               .updateTime(new Date())
               .isDeleted(false)
               .build();
       int i = service.insertReturnKey(johnson);
       log.info("id {}", i);
       return i;
     }
   
     private void testFindAll() {
       service.findAll().forEach(user -> log.info(user.toString()));
     }
   
     private User testFindById(int id) {
       User user = service.findById(id);
       log.info(user == null ? null : user.toString());
       return user;
     }
   
     private void testFindByCondition() {
       service
           .findByCondition(User.builder().username("john").password("john").nickname("john").build())
           .forEach(user -> log.info(user.toString()));
     }
   
     private void testUpdate(int id) {
       User user = testFindById(id);
       user.setUpdateTime(new Date());
       user.setPassword("newPass");
       service.modify(user);
     }
   
     private void testDelete(int id) {
       service.remove(id);
     }
   }
   

优化：

后期可以使用注解的形式，对entity进行处理。比如指定表名，驼峰命名匹配，主键，校验等。