SpringBoot 学习——使用 JdbcTemplate 访问数据库

参考博客：

[1] Spring Boot干货系列：（八）数据存储篇-SQL关系型数据库之JdbcTemplate的使用

[2] 一起来学 SpringBoot 2.x | 第五篇：使用 JdbcTemplate 访问数据库

添加依赖

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

数据源配置

在application.properties中添加如下配置。值得注意的是，SpringBoot默认会自动配置DataSource，它将优先采用HikariCP连接池，如果没有该依赖的情况则选取tomcat-jdbc，如果前两者都不可用最后选取Commons DBCP2。通过spring.datasource.type属性可以指定其它种类的连接池。

spring.datasource.url = jdbc:mysql://localhost:3306/spring?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root0
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
#spring.datasource.type
#更多细微的配置可以通过下列前缀进行调整
#spring.datasource.hikari
#spring.datasource.tomcat
#spring.datasource.dbcp2

开始使用JdbcTemplate

Spring的JdbcTemplate是自动配置的，你可以直接使用@Autowired来注入到你自己的bean中来使用。

实体对象：

public class LearnResource {
    private Long id;
    private String author;
    private String title;
    private String url;

    //get & set 方法
    //...
}

Controller层：

@Controller
@RequestMapping("/learn")
public class LearnController {
    @Autowired
    private LearnService learnService;

    @RequestMapping("t")
    public String learn(){
        return "learn-resource";
    }

    @RequestMapping(value = "queryLearnList",method = RequestMethod.POST,produces="application/json;charset=UTF-8")
    @ResponseBody
    public void queryLearnList(HttpServletRequest request ,HttpServletResponse response){
        String page = request.getParameter("page"); // 取得当前页数,注意这是jqgrid自身的参数
        String rows = request.getParameter("rows"); // 取得每页显示行数，,注意这是jqgrid自身的参数
        String author = request.getParameter("author");
        String title = request.getParameter("title");
        Map<String,Object> params = new HashMap<String,Object>();
        params.put("page", page);
        params.put("rows", rows);
        params.put("author", author);
        params.put("title", title);
        Page pageObj =learnService.queryLearnResourceList(params);
        List<Map<String, Object>> learnList=pageObj.getResultList();
        JSONObject jo=new JSONObject();
        jo.put("rows", learnList);
        jo.put("total", pageObj.getTotalPages());
        jo.put("records", pageObj.getTotalRows());
        ServletUtil.createSuccessResponse(200, jo, response);
    }

    /**
     * 新添教程
     * @param request
     * @param response
     */
    @RequestMapping(value = "add",method = RequestMethod.POST)
    public void addLearn(HttpServletRequest request , HttpServletResponse response){
        JSONObject result=new JSONObject();
        String author = request.getParameter("author");
        String title = request.getParameter("title");
        String url = request.getParameter("url");
        if(author.isEmpty()){
            result.put("message","作者不能为空!");
            result.put("flag",false);
            ServletUtil.createSuccessResponse(200, result, response);
            return;
        }
        if(title.isEmpty()){
            result.put("message","教程名称不能为空!");
            result.put("flag",false);
            ServletUtil.createSuccessResponse(200, result, response);
            return;
        }
        if(url.isEmpty()){
            result.put("message","地址不能为空!");
            result.put("flag",false);
            ServletUtil.createSuccessResponse(200, result, response);
            return;
        }
        LearnResource learnResource = new LearnResource();
        learnResource.setAuthor(author);
        learnResource.setTitle(title);
        learnResource.setUrl(url);
        int index=learnService.add(learnResource);
        System.out.println("结果="+index);
        if(index>0){
            result.put("message","教程信息添加成功!");
            result.put("flag",true);
        }else{
            result.put("message","教程信息添加失败!");
            result.put("flag",false);
        }
        ServletUtil.createSuccessResponse(200, result, response);
    }

    /**
     * 删除教程
     * @param request
     * @param response
     */
    @RequestMapping(value="delete",method = RequestMethod.POST)
    @ResponseBody
    public void deleteUser(HttpServletRequest request ,HttpServletResponse response){
        String id = request.getParameter("id");
        System.out.println("id==="+id);
        JSONObject result = new JSONObject();
        //删除操作
        int index = learnService.delete(id);
        if(index>0){
            result.put("message","教程信息删除成功!");
            result.put("flag",true);
        }else{
            result.put("message","教程信息删除失败!");
            result.put("flag",false);
        }
        ServletUtil.createSuccessResponse(200, result, response);
    }
}

Service层 接口和实现：

public interface LearnService {
    int add(LearnResource learnResource);
    int delete(String id);
    Page queryLearnResourceList(Map<String,Object> params);

}

@Service
public class LearnServiceImpl implements LearnService {
    @Autowired
    LearnDao learnDao;
    @Override
    public int add(LearnResource learnResource) {
        return this.learnDao.add(learnResource);
    }

    @Override
    public int delete(String id) {
        return this.learnDao.delete(id);
    }

    @Override
    public Page queryLearnResourceList(Map<String,Object> params) {
        return this.learnDao.queryLearnResourceList(params);
    }
}

Dao层 接口和实现：

public interface LearnDao {
    int add(LearnResource learnResource);
    int delete(String id);
    Page queryLearnResourceList(Map<String,Object> params);
}

@Repository
public class LearnDaoImpl implements LearnDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int add(LearnResource learnResource) {
        return jdbcTemplate.update("insert into learn_resource(author,title,url) values(?, ?, ?)",learnResource.getAuthor(),learnResource.getTitle(),learnResource.getUrl());
    }

    @Override
    public int delete(String id) {
        return jdbcTemplate.update("delete from learn_resource where id = ?", id);
    }

    @Override
    public Page queryLearnResourceList(Map<String,Object> params) {
        StringBuffer sql =new StringBuffer();
        sql.append("select * from learn_resource where 1=1");
        if(!StringUtil.isNull((String)params.get("author"))){
            sql.append(" and author like '%").append((String)params.get("author")).append("%'");
        }
        if(!StringUtil.isNull((String)params.get("title"))){
            sql.append(" and title like '%").append((String)params.get("title")).append("%'");
        }
        Page page = new Page(sql.toString(), Integer.parseInt(params.get("page").toString()), Integer.parseInt(params.get("rows").toString()), jdbcTemplate);
        return page;
    }
}

上面介绍的JdbcTemplate只是最基本的几个操作，更多其他数据访问操作的使用请参考：JdbcTemplate API

结果展示：