1.JdbcTemplate

一、配置数据源：

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

<!-- 数据库 -->
<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数据库,并创建表

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 模板使用

基础代码：

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

具体流程实现：

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

1.实体类 /bean/DemoUser.java

生成get set toSttring

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

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

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

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，带分页

创建工具类

• JdbcDaoImpl　对jdbc代码再次进行封装

• Page　分页

• Sql　

JdbcDaoImpl.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

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

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 加入分页接口

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，使用模板，并加入分页函数

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);
    }

}

测试类中加入分页测试

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语句

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