SpringBoot实战之10 整合jdbc进行crud操作

阳光穿透心脏的1/2处 2022-06-04 06:47 242阅读 0赞

## 前言 ##

springboot整合数据源之jdbc，web项目操作数据库是常有之事。本篇介绍springboot如何用jdbc连接mariadb数据库。

## code实现 ##

### 导包 ###

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mariadb.jdbc</groupId>
        <artifactId>mariadb-java-client</artifactId>
    </dependency>
    <dependency>
        <groupId>com.hsy.springboot</groupId>
        <artifactId>springboot-rpc-api</artifactId>
    </dependency>
    <dependency>
        <groupId>com.hsy.java</groupId>
        <artifactId>java-exception</artifactId>
    </dependency>
    <dependency>
        <groupId>com.hsy.java</groupId>
        <artifactId>java-util</artifactId>
    </dependency>
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger2</artifactId>
    </dependency>
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger-ui</artifactId>
    </dependency>

### 新建数据模型实体类 ###

Exercise.java

public class TExerciseZone { 
        private Long id;
        private Integer code;
        private String name;
        private Long parentId;
        private Integer sortId;
        private String remark;
    
        //省略setter，getter等方法
    }

### dao层简要实现 ###

@Repository("exerciseDao")
    public class TExerciseZoneDaoImpl implements ITExerciseZoneDao{ 
        private static final Logger _logger = LoggerFactory.getLogger(TExerciseZoneDaoImpl.class) ;
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Override
        public int insert(TExerciseZone exerciseZone) {
            try{
                return jdbcTemplate.update(insertSql,
                        exerciseZone.getCode(),exerciseZone.getName(),exerciseZone.getParentId(), exerciseZone.getSortId(),exerciseZone.getRemark());
            }catch(Exception e){
                _logger.error("出错信息:{}",e);
                throw new DBHandleException(DBEnum.DB_INSERT_RESULT_ERROR,e) ;
            }
        }
    
        @Override
        public int[] batchInsert(List<Object[]> batchArgs) {
            try{
                return jdbcTemplate.batchUpdate(insertSql,batchArgs);
            }catch(Exception e){
                throw new DBHandleException(DBEnum.DB_INSERT_RESULT_ERROR,e) ;
            }
        }
        @Override
        public int delete(Long id) {
            try{
                return jdbcTemplate.update(deleteSql,id);
            }catch(Exception e){
                throw new DBHandleException(DBEnum.DB_DELETE_RESULT_ERROR,e) ;
            }
        }
    
        @Override
        public int[] batchDelete(List<Long[]> ids) {
            try{
                List<Object[]> oids = null ;
                ids.forEach(id -> {
                    Object[] oid = null ;
                    for(int i=0;i<id.length;i++){
                        oid[i] = id[i] ;
                    }
                    oids.add(oid) ;
                });
                return jdbcTemplate.batchUpdate(deleteSql,oids) ;
            }catch (Exception e){
    
            }
            return new int[0];
        }
    
        @Override
        public int update(TExerciseZone exerciseZone) {
            try{
                return jdbcTemplate.update(updateSql,exerciseZone.getCode(),exerciseZone.getName(),exerciseZone.getParentId(),
                        exerciseZone.getSortId(),exerciseZone.getRemark(),exerciseZone.getId());
            }catch(Exception e){
                throw new DBHandleException(DBEnum.DB_UPDATE_RESULT_ERROR,e) ;
            }
        }
    
        @Override
        public int[] batchUpdate(List<Object[]> batchArgs) {
            try {
                return jdbcTemplate.batchUpdate(updateSql,batchArgs);
            } catch (DataAccessException e) {
                _logger.error("反生异常：{}",e);
                throw new DBHandleException(DBEnum.DB_UPDATE_RESULT_ERROR,e);
            }
        }
    
        @Override
        public TExerciseZone select(Long id) {
            TExerciseZone queryExerciseZone = jdbcTemplate.queryForObject(selectSql,new Object[]{id},new BeanPropertyRowMapper<>(TExerciseZone.class)) ;
            if(null!=queryExerciseZone){
                return queryExerciseZone;
            }
            return new TExerciseZone() ;
        }
    
        @Override
        public List<TExerciseZone> selectAll(Integer offset, Integer limit) {
            List<TExerciseZone> list = null;
            try {
                if(null!=offset&&null!=limit){
                    list = jdbcTemplate.query(selectAllSql + "limit ?,?",new Object[]{offset,limit},new BeanPropertyRowMapper<>(TExerciseZone.class));
                }else{
                    list = jdbcTemplate.query(selectAllSql,new Object[]{},new BeanPropertyRowMapper<>(TExerciseZone.class));
                }
            } catch (DataAccessException e) {
                _logger.error("捕获异常：",e);
                throw new DBHandleException(DBEnum.DB_SELECT_IS_NULL,e);
            }
            if(null!=list&&list.size()>0){
                return list ;
            }
            return null;
        }
    }

### service层简要实现 ###

@Service(value="exerciseZoneService")
    public class TExerciseZoneServiceImpl implements ITExerciseZoneService{ 
        @Autowired private ITExerciseZoneDao exerciseDao ;
        @Override
        public boolean createTExerciseZone(Integer code, String name, Long parentId, Integer sortId, String remark) {
            TExerciseZone exerciseZone = new TExerciseZone() ;
            exerciseZone.setCode(code);
            exerciseZone.setName(name);
            exerciseZone.setParentId(parentId);
            exerciseZone.setSortId(sortId);
            exerciseZone.setRemark(remark);
            int insertCount = exerciseDao.insert(exerciseZone) ;
            if(insertCount>0){
                return true ;
            }
            return false;
        }
    
        @Override
        public boolean bachtCreateTExcrciseZone(List<TExerciseZone> list) {
            List<Object[]> objArgs = new ArrayList<>() ;
            for (int i=0;i<list.size();i++){
                Object[] objs = new Object[]{} ;
                objs[0] = list.get(i).getCode();
                objs[1] = list.get(i).getName();
                objs[2] = list.get(i).getParentId() ;
                objs[3] = list.get(i).getRemark() ;
                objs[4] = list.get(i).getSortId() ;
                objArgs.add(objs) ;
            }
            if(exerciseDao.batchInsert(objArgs).length>0){
                return true ;
            }
            return false;
        }
    
        @Override
        public boolean deleteTExerciseZone(Long id) {
            if(exerciseDao.delete(id)>0){
                return true ;
            }
            return false;
        }
    
        @Override
        public boolean batchDeleteTExerciseZone(List<Long> ids) {
            List<Long[]> objArgs = new ArrayList<>() ;
            for (int i = 0; i < ids.size(); i++) {
                Long[] objs = new Long[]{} ;
                objs[0] = ids.get(i);
                objArgs.add(objs);
            }
            if(exerciseDao.batchDelete(objArgs).length>0){
                return true ;
            }
            return false;
        }
    
        @Override
        public boolean updateTExerciseZone(TExerciseZone exerciseZone) {
            if (exerciseDao.update(exerciseZone)>0){
                return true ;
            }
            return false;
        }
    
        @Override
        public boolean batchUpdateTEerciseZone(List<TExerciseZone> list) {
            List<Object[]> objArgs = new ArrayList<>() ;
            for (int i = 0; i < list.size(); i++) {
                Object[] objs = new Object[]{} ;
                objs[0] = list.get(i).getCode();
                objs[1] = list.get(i).getName();
                objs[2] = list.get(i).getParentId() ;
                objs[3] = list.get(i).getRemark() ;
                objs[4] = list.get(i).getSortId() ;
                objs[5] = list.get(i).getId() ;
                objArgs.add(objs) ;
            }
            if(exerciseDao.batchUpdate(objArgs).length>0){
                return true ;
            }
            return false;
        }
    
        @Override
        public TExerciseZone getOne(Long id) {
            return exerciseDao.select(id) ;
        }
    
        @Override
        public List<TExerciseZone> getList(Integer offset, Integer limit) {
            return exerciseDao.selectAll(offset,limit);
        }
    }

### controller层简要实现 ###

@Api(value = "省市区接口服务",description = "省市区crud操作")
    @RestController
    @RequestMapping("/api/rest")
    public class RestfulController extends BaseController{ 
    
        @Autowired private ITExerciseZoneService exerciseZoneService ;
    
        @ApiOperation(value = "用户注册接口",tags = "对外提供用户注册接口")
        @PostMapping(value = "/v1/reg",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public ResponseBodyBean<Boolean> reg(@RequestParam(value = "code") Integer code, @RequestParam String name,
                                          @RequestParam Long parentId, @RequestParam(required = false) Integer sortId, String remark){
            return success(exerciseZoneService.createTExerciseZone(code, name, parentId, sortId, remark));
        }
        @ApiOperation(value = "批量注册",tags = "对外提供批量注册接口")
        @PostMapping(value = "/v1/reg/batch",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public ResponseBodyBean<Boolean> batchReg(@RequestParam String json){
    
            JsonToBeanUtil.responseJsonToBeanEnhance(json, TExerciseZone.class) ;
            return null;
        }
        @ApiOperation(value = "指定id删除书籍",tags = "对外提供根据id单个删除书籍")
        //paramType 说明在路径当中
        @ApiImplicitParam(name = "id",value = "book主键ID",required = true,dataType = "Long",paramType = "path")
        @DeleteMapping(value = "/v1/delete/{id}",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public ResponseBodyBean<Boolean> delete(@PathVariable Long id){
            return success(exerciseZoneService.deleteTExerciseZone(id)) ;
        }
    
        @ApiOperation(value = "批量删除",tags = "批量删除服务")
        @ApiImplicitParam(name = "ids",value = "主键IDlist集合",required = true,dataType = "List<Long>",example = "{1,2,3,4}")
        @DeleteMapping(value = "/v1/delete/batch",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public ResponseBodyBean<Boolean> batchDelete(List<Long> ids){
            return success(exerciseZoneService.batchDeleteTExerciseZone(ids));
        }
    
        @ApiOperation(value = "指定id更新书籍",tags = "对外提供根据id单个更新书籍")
        //paramType 说明在路径当中
        @ApiImplicitParam(name = "id",value = "book主键ID",required = true,dataType = "Long",paramType = "path")
        @PutMapping(value = "/v1/update/{id}",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public ResponseBodyBean<Boolean> update(TExerciseZone exerciseZone){
            return success(exerciseZoneService.updateTExerciseZone(exerciseZone));
        }
    
        @ApiOperation(value = "批量更新",tags = "批量更新服务")
        @ApiImplicitParam(name = "list",value = "实体TExerciseZone list集合",required = true,dataType = "List<TExerciseZone>")
        @PutMapping(value = "/v1/update/batch",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public ResponseBodyBean<Boolean> batchUpdate(List<TExerciseZone> list){
            return success(exerciseZoneService.batchUpdateTEerciseZone(list));
        }
    
        @ApiOperation(value = "单个查询",tags = "查询单个书籍")
        @ApiImplicitParam(name = "id",value = "book主键ID",required = true,dataType = "Long",paramType = "path")
        @GetMapping(value = "/v1/zone/{id}",produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public ResponseBodyBean<TExerciseZone> getZone(@PathVariable Long id){
            return success(exerciseZoneService.getOne(id)) ;
        }
        @ApiOperation(value = "批量查询",tags = "批量查询服务")
        @ApiImplicitParams({
            @ApiImplicitParam(name = "offset",value = "从第几个开始查询",dataType = "Integer"),
            @ApiImplicitParam(name = "limit",value = "每页显示几个",dataType = "Integer")
        })
        @GetMapping(value = {
       "/v1/zones","/v1/zones/{offset}/{limit}"})
        public ResponseBodyBean<List<TExerciseZone>> getZones(@PathVariable(required = false) Integer offset,@PathVariable(required = false) Integer limit){
            return success(exerciseZoneService.getList(offset,limit));
        }
    }

### 配置文件 ###

aplication.properties

spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
    spring.datasource.url=jdbc:mariadb://192.168.216.129:3306/exercise?useUnicode=true&characterEncoding=UTF-8
    spring.datasource.username=root
    spring.datasource.password=root@mariadb

### 程序入口 ###

@SpringBootApplication
    @ComponentScan(basePackages = "com.hsy.springboot.jdbc")
    public class SpringBootJdbcApplication { 
        public static void main(String[] args){
            SpringApplication.run(SpringBootJdbcApplication.class,args) ;
        }
    }

## swagger接口文档 ##

用了swagger生成在线接口文档，不懂swagger的可以看我历史文章

效果图如下  
![这里写图片描述][SouthEast]

## 用postman测试接口 ##

测试增删改差等接口如下图  
![这里写图片描述][SouthEast 1]

## 源码 ##

由于不便详尽展现整个项目，再次附上源代码

[springboot-jdbc][]

## 历史文章 ##

[SpringBoot实战之入门][SpringBoot]

[springboot实战之文章汇总][springboot]

[springboot实战之读取配置文件][springboot 1]

[springboot实战之整合jsp模版引擎][springboot_jsp]

[springboot实战之整合freemarker模版引擎][springboot_freemarker]

[springboot实战之注册自定义Servlet][springboot_Servlet]

[springboot实战之注册filter和listener][springboot_filter_listener]

[springboot实战之注册interceptor][springboot_interceptor]

[springboot实战之整合slf4j日志系统][springboot_slf4j]

[springboot实战之整合CommandLineRunner][springboot_CommandLineRunner]

[springboot实战之整合restful工具swagger2][springboot_restful_swagger2]

[SouthEast]: /images/20220604/682d1e2fdb474d1096abb9c12852cd23.png
[SouthEast 1]: /images/20220604/9549bde41a944839afc251c9d8a03fcd.png
[springboot-jdbc]: https://github.com/shiyuan2he/springboot/tree/master/springboot-jdbc
[SpringBoot]: http://blog.csdn.net/u012806787/article/details/78694912
[springboot]: http://blog.csdn.net/u012806787/article/details/78686998
[springboot 1]: http://blog.csdn.net/u012806787/article/details/78583814
[springboot_jsp]: http://blog.csdn.net/u012806787/article/details/78700999
[springboot_freemarker]: http://blog.csdn.net/u012806787/article/details/78706156
[springboot_Servlet]: http://blog.csdn.net/u012806787/article/details/78710113
[springboot_filter_listener]: http://blog.csdn.net/u012806787/article/details/78715725
[springboot_interceptor]: http://blog.csdn.net/u012806787/article/details/78719429
[springboot_slf4j]: http://blog.csdn.net/u012806787/article/details/78725259
[springboot_CommandLineRunner]: http://blog.csdn.net/u012806787/article/details/78722025
[springboot_restful_swagger2]: http://blog.csdn.net/u012806787/article/details/78725676