# 1.JdbcTemplate

## 一、配置数据源：

1.嵌入式数据库的支持：Spring Boot 可以自动配置 H2, HSQL and Derby 数据库，不需要提供任何的链接 URLs，只需要加入相应的 jar 包，Spring boot 可以自动发现装配

```markup
<!-- 数据库 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
```

1. 配置mysql

application.properties文件中，可以参考多环境配置根据不同环境配置不同的库

```
spring.datasource.url=jdbc:mysql://localhost/demodb?useUnicode=true&characterEncoding=utf-8

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
```

注：

1.可以不指定 driver-class-name，spring boot 会自动识别 url。

2.数据连接池默认使用 tomcat-jdbc

连接池的配置： spring.datasource.tomcat.\*

３.创建demodb数据库,并创建表

```sql
CREATE TABLE `demo_user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';
```

二、 JdbcTemplate 模板使用

基础代码：

```java
// 自动注册
@Autowired
private JdbcTemplate jdbcTemplate;
```

具体流程实现：

![](https://3562539340-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LfnT314x6hQ7gJbPKxx%2F-LfnTB6gvTwJcyb-RbKo%2F-LfnTJJowiSNTavCTH2e%2F1.1.png?generation=1558862982072602\&alt=media)

选中的即为所需要实现的文件

1.实体类 /bean/DemoUser.java

生成get set toSttring

```java
package com.demotm.example.bean;

import java.util.Date;

public class DemoUser {

    private int id;
    private String name;
    private Date createTime;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "DemoUser{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", createTime=" + createTime +
                '}';
    }
}
```

2.接口

dao/DemoUserDao.java

```java
package com.demotm.example.dao;

import com.demotm.example.bean.DemoUser;

public interface DemoUserDao {
    int insert(DemoUser demoUser);
    int deleteById(int id);
    int updateById(DemoUser demoUser);
    DemoUser selectById(int id);
}
```

3.实现类代码

dao/impl/DemoUserDaoImpl.java

```java
package com.demotm.example.dao.impl;

import com.demotm.example.bean.DemoUser;
import com.demotm.example.dao.DemoUserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;

@Repository
public class DemoUserDaoImpl implements DemoUserDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(DemoUser demoUser) {
        String sql = "insert into demo_user (name, create_time) values (?, ?)";
        return jdbcTemplate.update(sql, demoUser.getName(), demoUser.getCreateTime());
    }


    @Override
    public int deleteById(int id) {

        String sql = "delete from demo_user where id=?";
        return jdbcTemplate.update(sql, id);

    }


    @Override
    public int updateById(DemoUser demoUser) {
        String sql = "update demo_user set name=?, create_time=? where id=?";
        return jdbcTemplate.update(sql, demoUser.getName(), demoUser.getCreateTime(), demoUser.getId());

    }

    @Override
    public DemoUser selectById(int id) {

        String sql = "select * from demo_user where id=?";
        return jdbcTemplate.queryForObject(sql, new RowMapper<DemoUser>() {
            @Override
            public DemoUser mapRow(ResultSet rs, int rowNum) throws SQLException {
                DemoUser demoUser = new DemoUser();
                demoUser.setId(rs.getInt("id"));
                demoUser.setName(rs.getString("name"));
                demoUser.setCreateTime(rs.getDate("create_time"));
                return demoUser;
            }}, id);
    }
}
```

测试类代码

DemotmApplicationTests

```java
package com.demotm.example;

import com.demotm.example.bean.DemoUser;
import com.demotm.example.dao.DemoUserDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Date;


@RunWith(SpringRunner.class)
@SpringBootTest
public class Test1ApplicationTests {

    @Autowired
    private DemoUserDao demoUserDao;

    @Test
    public void insert() {

        DemoUser demoUser = new DemoUser();
        demoUser.setName("测试");
        demoUser.setCreateTime(new Date());
        int result = demoUserDao.insert(demoUser);
        System.out.println(result);

    }

    @Test
    public void delete() {
        int result = demoUserDao.deleteById(1);
        System.out.println(result);

    }

    @Test
    public void update() {

        DemoUser demoUser = new DemoUser();
        demoUser.setId(2);
        demoUser.setName("测试 2");
        demoUser.setCreateTime(new Date());
        int result = demoUserDao.updateById(demoUser);
        System.out.println(result);
    }

    @Test
    public void select() {
        DemoUser result = demoUserDao.selectById(2);
        System.out.println(result);
    }
}
```

## 三、 封装 spring jdbc，带分页

![](https://3562539340-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LfnT314x6hQ7gJbPKxx%2F-LfnTB6gvTwJcyb-RbKo%2F-LfnTJJtRCQWlyD-zKi2%2F1.2.png?generation=1558862982066188\&alt=media)

创建工具类

* JdbcDaoImpl　对jdbc代码再次进行封装
* Page　分页
* Sql　

JdbcDaoImpl.java

```java
package com.demotm.example.util.base;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.Assert;

/**
 * spirng jdbc 基本支撑类
 *
 *
 */
public abstract class JdbcDaoImpl {

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    /**
     * 获取当前事务最后一次更新的主键值
     */
    public Long getLastId() {
        return jdbcTemplate.queryForObject("select last_insert_id() as id", Long.class);
    }

    /**
     * 获取对象信息
     */
    public <T> T queryForObject(String sql, Class<T> clazz, Object... args) {
        Assert.hasText(sql, "sql 语句不能为空");
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<T>(clazz), args);
    }

    /**
     * 获取对象集合信息
     */
    public <T> List<T> queryForObjectList(String sql, Class<T> clazz, Object... args) {
        Assert.hasText(sql, "sql 语句不能为空");
        return jdbcTemplate.query(sql, args, new BeanPropertyRowMapper<T>(clazz));
    }

    /**
     * 分页，jdbcTemplate 不支持like
     */
    public Page<Map<String, Object>> queryForPage(String sql, int pageCurrent, int pageSize, Object... args) {
        Assert.hasText(sql, "sql 语句不能为空");
        Assert.isTrue(pageCurrent >= 1, "pageNo 必须大于等于1");
        String sqlCount = Sql.countSql(sql);
        int count = jdbcTemplate.queryForObject(sqlCount, Integer.class, args);
        pageCurrent = Sql.checkPageCurrent(count, pageSize, pageCurrent);
        pageSize = Sql.checkPageSize(pageSize);
        int totalPage = Sql.countTotalPage(count, pageSize);
        String sqlList = sql + Sql.limitSql(count, pageCurrent, pageSize);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sqlList, args);
        return new Page<Map<String, Object>>(count, totalPage, pageCurrent, pageSize, list);
    }

    /**
     * 分页，jdbcTemplate 不支持like
     */
    public <T> Page<T> queryForPage(String sql, int pageCurrent, int pageSize, Class<T> clazz, Object... args) {
        Assert.hasText(sql, "sql 语句不能为空");
        Assert.isTrue(pageCurrent >= 1, "pageNo 必须大于等于1");
        Assert.isTrue(clazz != null, "clazz 不能为空");
        String sqlCount = Sql.countSql(sql);
        int count = jdbcTemplate.queryForObject(sqlCount, Integer.class, args);
        pageCurrent = Sql.checkPageCurrent(count, pageSize, pageCurrent);
        pageSize = Sql.checkPageSize(pageSize);
        int totalPage = Sql.countTotalPage(count, pageSize);
        String sqlList = sql + Sql.limitSql(count, pageCurrent, pageSize);
        List<T> list = jdbcTemplate.query(sqlList, new BeanPropertyRowMapper<T>(clazz), args);
        return new Page<T>(count, totalPage, pageCurrent, pageSize, list);
    }

}
```

Page.java

```java
package com.demotm.example.util.base;

import java.io.Serializable;
import java.util.List;

/**
 * 数据分页组件
 *
 *
 */
public class Page<T> implements Serializable {
    private static final long serialVersionUID = -5764853545343945831L;

    /**
     * 默认每页记录数(20)
     */
    public static final int DEFAULT_PAGE_SIZE = 20;

    /**
     * 最大每页记录数(1000)
     */
    public static final int MAX_PAGE_SIZE = 1000;

    /**
     * 当前分页的数据集
     */
    private List<T> list;

    /**
     * 总记录数
     */
    private int totalCount;

    /**
     * 总页数
     */
    private int totalPage;

    /**
     * 当前页
     */
    private int pageCurrent;

    /**
     * 每页记录数
     */
    private int pageSize;

    /**
     * 排序字段
     */
    private String orderField;

    /**
     * 排序方式：asc or desc
     */
    private String orderDirection;

    /**
     * 默认构造函数
     */
    public Page() {
    }

    /**
     * 构造函数
     *
     * @param totalCount
     *            总记录数
     * @param totalPage
     *            总页数
     * @param pageCurrent
     * @param pageSize
     * @param list
     */
    public Page(int totalCount, int totalPage, int pageCurrent, int pageSize, List<T> list) {
        this.totalCount = totalCount;
        this.totalPage = totalPage;
        this.pageCurrent = pageCurrent;
        this.pageSize = pageSize;
        this.list = list;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getPageCurrent() {
        return pageCurrent;
    }

    public void setPageCurrent(int pageCurrent) {
        this.pageCurrent = pageCurrent;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public String getOrderField() {
        return orderField;
    }

    public void setOrderField(String orderField) {
        this.orderField = orderField;
    }

    public String getOrderDirection() {
        return orderDirection;
    }

    public void setOrderDirection(String orderDirection) {
        this.orderDirection = orderDirection;
    }

}
```

Sql.java

```java
package com.demotm.example.util.base;

import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
 * sql工具类
 *
 *
 */
public class Sql {
    private Sql() {
    }

    /**
     * 检测sql，防止sql注入
     *
     * @param sql
     *            sql
     * @return 正常返回sql；异常返回""
     */
    public static String checkSql(String sql) {
        String inj_str = "'|and|exec|insert|select|delete|update|count|*|%|chr|mid|master|truncate|char|declare|;|or|-|+|,";
        String inj_stra[] = inj_str.split("\\|");
        for (int i = 0; i < inj_stra.length; i++) {
            if (sql.indexOf(inj_stra[i]) >= 0) {
                return "";
            }
        }
        return sql;
    }

    /**
     * 计算总页数
     *
     * @param totalCount
     *            总记录数.
     * @param pageSize
     *            每页记录数.
     * @return totalPage 总页数.
     */
    public static int countTotalPage(final int totalCount, final int pageSize) {
        if (totalCount % pageSize == 0) {
            return totalCount / pageSize; // 刚好整除
        } else {
            return totalCount / pageSize + 1; // 不能整除则总页数为：商 + 1
        }
    }

    /**
     * 校验当前页数pageCurrent<br/>
     * 1、先根据总记录数totalCount和每页记录数pageSize，计算出总页数totalPage<br/>
     * 2、判断页面提交过来的当前页数pageCurrent是否大于总页数totalPage，大于则返回totalPage<br/>
     * 3、判断pageCurrent是否小于1，小于则返回1<br/>
     * 4、其它则直接返回pageCurrent
     *
     * @param totalCount
     *            要分页的总记录数
     * @param pageSize
     *            每页记录数大小
     * @param pageCurrent
     *            输入的当前页数
     * @return pageCurrent
     */
    public static int checkPageCurrent(int totalCount, int pageSize, int pageCurrent) {
        int totalPage = countTotalPage(totalCount, pageSize); // 最大页数
        if (pageCurrent > totalPage) {
            // 如果页面提交过来的页数大于总页数，则将当前页设为总页数
            // 此时要求totalPage要大于获等于1
            if (totalPage < 1) {
                return 1;
            }
            return totalPage;
        } else if (pageCurrent < 1) {
            return 1; // 当前页不能小于1（避免页面输入不正确值）
        } else {
            return pageCurrent;
        }
    }

    /**
     * 校验页面输入的每页记录数pageSize是否合法<br/>
     * 1、当页面输入的每页记录数pageSize大于允许的最大每页记录数MAX_PAGE_SIZE时，返回MAX_PAGE_SIZE
     * 2、如果pageSize小于1，则返回默认的每页记录数DEFAULT_PAGE_SIZE
     *
     * @param pageSize
     *            页面输入的每页记录数
     * @return checkPageSize
     */
    public static int checkPageSize(int pageSize) {
        if (pageSize > Page.MAX_PAGE_SIZE) {
            return Page.MAX_PAGE_SIZE;
        } else if (pageSize < 1) {
            return Page.DEFAULT_PAGE_SIZE;
        } else {
            return pageSize;
        }
    }

    /**
     * 计算当前分页的开始记录的索引
     *
     * @param pageCurrent
     *            当前第几页
     * @param pageSize
     *            每页记录数
     * @return 当前页开始记录号
     */
    public static int countOffset(final int pageCurrent, final int pageSize) {
        return (pageCurrent - 1) * pageSize;
    }

    /**
     * 根据总记录数，对页面传来的分页参数进行校验，并返分页的SQL语句
     *
     * @param pageCurrent
     *            当前页
     * @param pageSize
     *            每页记录数
     * @param pageBean
     *            DWZ分页查询参数
     * @return limitSql
     */
    public static String limitSql(int totalCount, int pageCurrent, int pageSize) {
        // 校验当前页数
        pageCurrent = checkPageCurrent(totalCount, pageSize, pageCurrent);
        pageSize = checkPageSize(pageSize); // 校验每页记录数
        return " limit " + countOffset(pageCurrent, pageSize) + "," + pageSize;
    }

    /**
     * 根据分页查询的SQL语句，获取统计总记录数的语句
     *
     * @param sql
     *            分页查询的SQL
     * @return countSql
     */
    public static String countSql(String sql) {
        String countSql = sql.substring(sql.toLowerCase().indexOf("from")); // 去除第一个from前的内容
        return "select count(*) " + removeOrderBy(countSql);
    }

    /**
     * 移除SQL语句中的的order by子句（用于分页前获取总记录数，不需要排序）
     *
     * @param sql
     *            原始SQL
     * @return 去除order by子句后的内容
     */
    private static String removeOrderBy(String sql) {
        Pattern pat = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
        Matcher mc = pat.matcher(sql);
        StringBuffer strBuf = new StringBuffer();
        while (mc.find()) {
            mc.appendReplacement(strBuf, "");
        }
        mc.appendTail(strBuf);
        return strBuf.toString();
    }
}
```

改造之前的代码，并加入分页

dao/DemoUserDao.java 加入分页接口

```java
package com.demotm.example.dao;

import com.demotm.example.bean.DemoUser;
import com.demotm.example.util.base.Page;

public interface DemoUserDao {
    int insert(DemoUser demoUser);
    int deleteById(int id);
    int updateById(DemoUser demoUser);
    DemoUser selectById(int id);
    Page<DemoUser> queryForPage(int pageCurrent, int pageSize, String name);

}
```

dao/impl/DemoUserDaoImpl.java

继承JdbcDaoImpl，使用模板，并加入分页函数

```java
package com.demotm.example.dao.impl;

import com.demotm.example.bean.DemoUser;
import com.demotm.example.dao.DemoUserDao;
import com.demotm.example.util.base.JdbcDaoImpl;
import com.demotm.example.util.base.Page;
import com.demotm.example.util.base.Sql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

@Repository
public class DemoUserDaoImpl extends JdbcDaoImpl implements DemoUserDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(DemoUser demoUser) {
        String sql = "insert into demo_user (name, create_time) values (?, ?)";
        return jdbcTemplate.update(sql, demoUser.getName(), demoUser.getCreateTime());
    }


    @Override
    public int deleteById(int id) {

        String sql = "delete from demo_user where id=?";
        return jdbcTemplate.update(sql, id);

    }


    @Override
    public int updateById(DemoUser demoUser) {
        String sql = "update demo_user set name=?, create_time=? where id=?";
        return jdbcTemplate.update(sql, demoUser.getName(), demoUser.getCreateTime(), demoUser.getId());

    }

    @Override
    public DemoUser selectById(int id) {

        String sql = "select * from demo_user where id=?";
        /*return jdbcTemplate.queryForObject(sql, new RowMapper<DemoUser>() {
        @Override
        public DemoUser mapRow(ResultSet rs, int rowNum) throws SQLException {
            DemoUser demoUser = new DemoUser();
            demoUser.setId(rs.getInt("id"));
            demoUser.setName(rs.getString("name"));
            demoUser.setCreateTime(rs.getDate("create_time"));
            return demoUser;
        }}, id);*/
        return queryForObject(sql, DemoUser.class, id);
    }

    @Override
    public Page<DemoUser> queryForPage(int pageCurrent, int pageSize, String name){
        // 确定参数
        /*String sql = "select * from demo_user where name=?";
        return queryForPage(sql.toString(), pageCurrent, pageSize, RoncooUser.class, name);*/

        // 若name可能为空，则要进行判定，如下
        /*StringBuffer sql = new StringBuffer("select * from demo_user where 1");
        if(!StringUtils.isNullOrEmpty(name)){
            // Sql.checkSql 的作用是防止sql注入
            sql.append(" and name = '").append(Sql.checkSql(name)).append("' ");
        }
        return queryForPage(sql.toString(), pageCurrent, pageSize, RoncooUser.class);*/

        // 若要like查询，如下
        StringBuffer sql = new StringBuffer("select * from demo_user where 1");
        if(!StringUtils.isEmpty(name)){
            // Sql.checkSql 的作用是防止sql注入
            sql.append(" and name like '%").append(Sql.checkSql(name)).append("%' ");
        }
        return queryForPage(sql.toString(), pageCurrent, pageSize, DemoUser.class);
    }

}
```

测试类中加入分页测试

```java
package com.demotm.example;

import com.demotm.example.bean.DemoUser;
import com.demotm.example.dao.DemoUserDao;
import com.demotm.example.util.base.Page;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Date;

@RunWith(SpringRunner.class)
@SpringBootTest
public class DemotmApplicationTests {

    @Autowired
    private DemoUserDao demoUserDao;

    @Test
    public void insert() {

        DemoUser demoUser = new DemoUser();
        demoUser.setName("测试");
        demoUser.setCreateTime(new Date());
        int result = demoUserDao.insert(demoUser);
        System.out.println(result);
    }

    @Test
    public void delete() {

        int result = demoUserDao.deleteById(1);
        System.out.println(result);
    }

    @Test
    public void update() {
        DemoUser demoUser = new DemoUser();
        demoUser.setId(2);
        demoUser.setName("测试 2");
        demoUser.setCreateTime(new Date());
        int result = demoUserDao.updateById(demoUser);
        System.out.println(result);
    }

    @Test
    public void select() {
        DemoUser result = demoUserDao.selectById(2);
        System.out.println(result);
    }

    @Test
    public void select2() {
        DemoUser result = demoUserDao.selectById(7); System.out.println(result);
    }

    //    分页测试
    @Test
    public void queryForPage(){
        Page<DemoUser> result = demoUserDao.queryForPage(1, 20, "测试");

        System.out.println(result.getList());
    }
}
```

## 其他

如果使用的是logback，可以加入如下配置打印sql语句

```markup
<logger name="org.springframework.jdbc.core.JdbcTemplate" level="debug"/>
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://spring-boot.shujuwajue.com/guan-xi-xing-shu-ju-ku/1jdbctemplate.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.