前言

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的可以看我历史文章

效果图如下

用postman测试接口

测试增删改差等接口如下图

源码

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

springboot-jdbc

历史文章

SpringBoot实战之入门

springboot实战之文章汇总

springboot实战之读取配置文件

springboot实战之整合jsp模版引擎

springboot实战之整合freemarker模版引擎

springboot实战之注册自定义Servlet

springboot实战之注册filter和listener

springboot实战之注册interceptor

springboot实战之整合slf4j日志系统

springboot实战之整合CommandLineRunner

springboot实战之整合restful工具swagger2