Springboot整合JdbcTemplate，使用Entity进行CRUD

JdbcTemplate的使用: JdbcTemplate配置多个数据源.

1.实体类Entity

import lombok.Data;

@Data
public class Student {
    private int stu_id;
    private String stu_name;
    private String stu_native;
    private String stu_nation;
    private int stu_score;
}

2.使用Entity进行insert

    @Autowired
    @Qualifier("jdbcTemplateTwo")
    private JdbcTemplate jdbcTemplateTwo;
    
    @Test
    void test1(){
        String create = "insert into  student values (?,?,?,?,?)";
        Student student = new Student();
        student.setStu_id(16);
        student.setStu_name("abc");
        student.setStu_nation("def");
        student.setStu_native("hdg");
        student.setStu_score(5);

        jdbcTemplateTwo.update(create, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1,student.getStu_id());
                ps.setNString(2,student.getStu_name());
                ps.setNString(3,student.getStu_nation());
                ps.setNString(4,student.getStu_native());
                ps.setInt(5,student.getStu_score());
            }
        });
    }

• 返回最后插入的id

    public int mapperInsertDpmLog(String sql,DPMLogEntity dpmLogEntity) {
        KeyHolder keyHolder = new GeneratedKeyHolder();

         new JdbcTemplateUtils().createJdbcTemplate().update(
                new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                        java.sql.PreparedStatement ps = conn.prepareStatement(sql);
                        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                        ps.setInt(1, dpmLogEntity.getSystemId());
                        ps.setInt(2,dpmLogEntity.getTopicId());
                        ps.setString(3,dpmLogEntity.getTaskType() );
                        ps.setInt(4,dpmLogEntity.getSource());
                        ps.setInt(5,dpmLogEntity.getTarget());
                        ps.setString(6,dpmLogEntity.getStatus());
                        ps.setTimestamp(7,dpmLogEntity.getLogDt());
                        return ps;
                    }
            }, keyHolder
        );
        return keyHolder.getKey().intValue();
    }

3.使用Entity进行update

    @Autowired
    @Qualifier("jdbcTemplateTwo")
    private JdbcTemplate jdbcTemplateTwo;
    
    @Test
    void test2(){
        String create = "update student set stu_name = ?,stu_native = ? ,stu_nation =?,stu_score = ? where stu_id = 16";
        Student student = new Student();
        student.setStu_name("Tom");
        student.setStu_nation("China");
        student.setStu_native("guangdong");
        student.setStu_score(5);

        jdbcTemplateTwo.update(create, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setNString(1,student.getStu_name());
                ps.setNString(2,student.getStu_nation());
                ps.setNString(3,student.getStu_native());
                ps.setInt(4,student.getStu_score());
            }
        });
    }

4.查询，封装到List

    @Autowired
    @Qualifier("jdbcTemplateTwo")
    private JdbcTemplate jdbcTemplateTwo;
    
    @Test
    void test3(){
        String select = "select * from student";

        jdbcTemplateTwo.query(select, new StudentMapper()); 
    }

    private class StudentMapper implements RowMapper<Student> {
        @Override
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student stu = new Student();
            stu.setStu_id(resultSet.getInt("stu_id"));
            stu.setStu_name(resultSet.getNString("stu_name"));
            stu.setStu_native(resultSet.getNString("stu_native"));
            stu.setStu_nation(resultSet.getNString("stu_nation"));
            stu.setStu_score(resultSet.getInt("stu_score"));
            return stu;
        }
    }
        

5.代码结构

• 所有的sql都放到dao层
• 所有jdbcTemplate执行的方法都方法MapperService
• controller 调用service ，service调用dao的sql和mapperService的jdbcTemplate

-------------利用反射给Entity属性赋值-------------------
@RestController
@RequestMapping(value = "/data-pipeline")
public class DataPipelineController {

   @Autowired
   private DataPipelineService dataPipelineService;

   @PostMapping(value = "/createGlossary")
   public void createGlossary(@RequestBody Map<String, Object> createMap) throws Exception {

       GlossaryEntity glossary = new GlossaryEntity();

       Set<String> keys = createMap.keySet();
       for (String key : keys) {
           Field[] fields = glossary.getClass().getDeclaredFields();
           for (Field field : fields) {

               if (key.equals(field.getName())) {
                   field.setAccessible(true);
                   field.set(glossary, createMap.get(key));
               }
           }
       }

       dataPipelineService.createGlossary(glossary);
   }

-----因为不知道修改了哪些属性，所以用反射-----------------
   @PostMapping("/updateGlossary")
   public void updateGlossary(@RequestBody Map<String, Object> updateMap) throws  Exception {

       int data_group_id = (int) updateMap.get("data_group_id");
       GlossaryEntity glossary = dataPipelineService.getGlossaryById(data_group_id);

       Set<String> keys = updateMap.keySet();
       for (String key : keys) {
           Field[] fields = glossary.getClass().getDeclaredFields();
           for (Field field : fields) {

               if (key.equals(field.getName())) {
                   field.setAccessible(true);
                   field.set(glossary, updateMap.get(key));
               }
           }
       }
       dataPipelineService.updateGlossary(data_group_id,glossary);
   }


   @RequestMapping(value = "/getLinkage")
   public List<Linkage> getLinkageByTermId(int termId) {
       List<Linkage> linkageList = dataPipelineService.getLinkageByTermId(termId);
       return linkageList;
   }

@Service
public class DataPipelineService {

    @Autowired
    @Qualifier("jdbcTemplateOne")
    private JdbcTemplate jdbcTemplateOne;

    @Autowired
    private DataPipelineDao dataPipelineDao;
    @Autowired
    private DataMapperService dataMapperService;

    public int createGlossary(GlossaryEntity glossary) {

        String queryLastId = "select max(data_group_id)  from xxxx";
        int lastId = jdbcTemplateOne.queryForObject(queryLastId, Integer.class).intValue();

        glossary.setDataGroupId(lastId + 1);
        return dataMapperService.insertGlossary(dataPipelineDao.createGlossary(), glossary);
    }

    public int updateGlossary(int id,GlossaryEntity glossary) {

        return dataMapperService.updateGlossary(dataPipelineDao.updateGlossary(id), glossary);
    }

    public GlossaryEntity getGlossaryById(int id) {

        String queryGlossary = dataPipelineDao.queryGlossaryById(id);
        return dataMapperService.getGlossary(queryGlossary);
    }

    public List<Linkage> getLinkageByTermId(int id) {
        String queryLinkage = dataPipelineDao.queryLinkageByTerm(id);
       return dataMapperService.getCommonList(queryLinkage);
    }

@Service
public class DataMapperService {

    @Autowired
    @Qualifier("jdbcTemplateOne")
    private JdbcTemplate jdbcTemplateOne;

    @Autowired
    @Resource(name = "jdbcTemplateTwo")
    private JdbcTemplate jdbcTemplateTwo;

    public List<CommonData> getCommonList(String sql) {
        return jdbcTemplateOne.query(sql, new CommonMapper());
    }

    public int insertGlossary(String sql, GlossaryEntity glossary) {
        return jdbcTemplateOne.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setLong(1, glossary.getDataGroupId());
                ps.setNString(2, glossary.getHasExtendedText());
                -----省略-------
            }
        });
    }

    public int updateGlossary(String sql, GlossaryEntity glossary) {
        return jdbcTemplateOne.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setNString(1, glossary.getHasExtendedText());
                ---省略----
            }
        });
    }

    public GlossaryEntity getGlossary(String sql) {
        return jdbcTemplateOne.queryForObject(sql,GlossaryEntity.class);
    }

    public List<Linkage> getLinkageList(String sql) {
        return jdbcTemplateOne.query(sql, new LinkageMapper());
    }

@Component
public class DataPipelineDao {

    public String createGlossary() {
        String createGlossary = "insert into MMT_DATA_GROUP VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        return createGlossary;
    }