spring boot jdbcTemplete操作关系型数据库

作者: 逗比punk | 来源:发表于2017-07-14 23:49 被阅读72次

一、配置数据源:

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

spring boot jdbcTemplete操作关系型数据库

一、配置数据源:

二、JdbcTemplate 模板

三、脚本

四、实体类

五、接口

六、实现类代码

八、封装 spring jdbc,带分页

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

spring boot jdbcTemplete操作关系型数据库

一、 配置数据源:

二、JdbcTemplate 模板

三、 脚本

四、实体类

五、 接口

六、实现类代码

八、封装 spring jdbc,带分页

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

一、配置数据源:

三、脚本

五、接口