spring boot jdbcTemplete操作关系型数据库

一、 配置数据源:

嵌入式数据库的支持: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>

mysql

spring.datasource.url=jdbc:mysql://localhost/spring_boot_demo?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.*

二、JdbcTemplate 模板
// 自动注册
@Autowired
private JdbcTemplate jdbcTemplate;
三、 脚本
 CREATE TABLE `roncoo_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='用户表';
四、实体类
/**
* 实体类
*
* @author wujing */
        public class RoncooUser {
         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 "RoncooUser [id=" + id + ", name=" + name + ", createTime=" + createTime }
}
五、 接口
int insert(RoncooUser roncooUser);
int deleteById(int id);
int updateById(RoncooUser roncooUser);
RoncooUser selectById(int id);
六、实现类代码
 @Autowired
         private JdbcTemplate jdbcTemplate;
         @Override
         public int insert(RoncooUser roncooUser) {
              String sql = "insert into roncoo_user (name, create_time) values (?, ?)";
return jdbcTemplate.update(sql, roncooUser.getCreateTime());
}
     @Override
     public int deleteById(int id) {
String sql = "delete from roncoo_user where id=?";
          return jdbcTemplate.update(sql, id);
     }
     @Override
     public int updateById(RoncooUser roncooUser) {
roncooUser.getName(),
String sql = "update roncoo_user set name=?, create_time=? where id=?";
return jdbcTemplate.update(sql, roncooUser.getCreateTime(), roncooUser.getId());
}
     @Override
     public RoncooUser selectById(int id) {
roncooUser.getName(),
String sql = "select * from roncoo_user where id=?";
return jdbcTemplate.queryForObject(sql, new RowMapper<RoncooUser>() {
     @Override
     public RoncooUser mapRow(ResultSet rs, int rowNum) throws SQLException {
         RoncooUser roncooUser = new RoncooUser();
         roncooUser.setId(rs.getInt("id"));
         roncooUser.setName(rs.getString("name"));
         roncooUser.setCreateTime(rs.getDate("create_time"));
         return roncooUser;
}

七、测试类代码

@Autowired
private RoncooUserDao roncooUserDao;
@Test
 public void insert() {
RoncooUser roncooUser = new RoncooUser(); roncooUser.setName("测试"); roncooUser.setCreateTime(new Date());
int result = roncooUserDao.insert(roncooUser); System.out.println(result);
}
@Test
public void delete() {
    int result = roncooUserDao.deleteById(1);
    System.out.println(result);
 }
 @Test
 public void update() {
  RoncooUser roncooUser = new RoncooUser(); 
  roncooUser.setId(2);
  roncooUser.setName("测试 2"); roncooUser.setCreateTime(new Date());
  int result = roncooUserDao.updateById(roncooUser); 
  System.out.println(result);
}
@Test
public void select() {
  RoncooUser result = roncooUserDao.selectById(2); 
  System.out.println(result);
}

打印 sql 语句,添加如下

<logger name="org.springframework.jdbc.core.JdbcTemplate" level="debug"/>
八、封装 spring jdbc,带分页
/**
* 获取当前事务最后一次更新的主键值 */
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),
return new Page<T>(count, totalPage, pageCurrent, pageSize, list);

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

// 分页测试
@Test
public void queryForPage(){
Page<RoncooUser> result = roncooUserDao.queryForPage(1,
20, "测试"); System.out.println(result.getList());
}

实现类
@Override
public Page<RoncooUser> queryForPage(int pageCurrent, int pageSize, String name){
// 确定参数
/*String sql = "select * from roncoo_user where name=?"; return queryForPage(sql.toString(), pageCurrent,
pageSize, RoncooUser.class, name);*/
// 若name可能为空,则要进行判定,如下
/*StringBuffer sql = new StringBuffer("select * from roncoo_user where 1");
args); }

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 roncoo_user where 1");
if(!StringUtils.isNullOrEmpty(name)){
// Sql.checkSql 的作用是防止sql注入
sql.append(" and name like '%").append(Sql.checkSql(name)).append("%' ");
}
return queryForPage(sql.toString(), pageCurrent, pageSize, RoncooUser.class);
}}

page.java

/**
 * 数据分页组件
 * 
 * @author wujing
 */
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

/**
 * sql工具类
 * 
 * @author wujing
 */
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();
    }
}
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,968评论 6赞 482
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,601评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 153,220评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,416评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,425评论 5赞 374
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,144评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,432评论 3赞 401
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,088评论 0赞 261
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,586评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,028评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,137评论 1赞 334
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,783评论 4赞 324
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,343评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,333评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,559评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,595评论 2赞 355
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,901评论 2赞 345

推荐阅读更多精彩内容

  • Spring boot参考指南
    Spring Boot 参考指南 介绍 转载自:https://www.gitbook.com/book/qbgb...
    毛宇鹏阅读 46,748评论 6赞 342
  • Spring Cloud
    Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具（例如配置管理，服务发现，断路器，智...
    卡卡罗2017阅读 134,601评论 18赞 139
  • 32/70 20170119 小控班 升职
    我脑子里反复回放起来 lean in里的一句话 提拔男的因为他的潜力 提拔女的因为她做过什么 在得知他人靠裙带关系...
    estilada阅读 154评论 0赞 0
  • 好帖子收集
    1 block相关 :链接 2 组件化开发:链接 3常用的宏:链接 4中文拼音排序:链接 5OC拷贝:链接 6完全...
    Link913阅读 457评论 0赞 0
  • 父亲的脾气
    我们一家人，不用说，就父亲很容易发脾气。他生气的时候我们不敢说话，每次都会默默的坐在那儿。小时候村子穷，而我们又是...
    陪月亮摘星星阅读 521评论 31赞 26