Spring Boot 2精髓 - (5) 数据库访问

SpringSpring Boot精髓 阅读量

spring boot

JDBCTemplate是Spring 自带的，在JDBC的基础上做了一定封装

BeetlSQL是笔者研发的，除了封装了 JDBC操作，还带有 SQL 管理、跨数据库平台支持等企业功能

以SQL为核心的数据库访问更为灵活，更能适应大型的互联网和企业应用，学习门槛较低

以对象方式访问数据库更适合较为简单的系统或者工具类系统，学习门槛刚开始较低，但因为 ORM(Object Relational Mapping)持久化上下文等概念过于复杂

配置数据源

数据库连接池 HikariCP

1
2
3
4
5
<dependency>
    <groupid>com.zaxxer</groupid>
    <artifactid>HikariCP</artifactid>
    <version>2.6.l</version>
</dependency>

MySQL

1
2
3
4
5
<dependency>
    <groupid>mysql</groupid>
    <artifactid>mysql-connector-java</artifactid>
    <version>6.0.5</version>
</dependency>

在配置文件中配置连接数据库的基本信息以供 HikariCP 使用 :

1
2
3
4
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/orm?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

JavaConfig来创建一个数据源:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Configuration
public class DataSourceConfig {

    @Bean(name = "dataSource")
    public DataSource datasource(Environment env) {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(env.getProperty("spring.datasource.url"));
        ds.setUsername(env.getProperty("spring.datasource.username"));
        ds.setPassword(env.getProperty("spring.datasource.password"));
        ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        return ds;
    }

}

Spring JDBC Template

Spring提供了JdbcTemplate对数据库访问技术 JDBC 做了一定封装，包括管理数据库连接，简单查询结果映射成 Java 对象，复杂的结果集通过实现RowMapper接口来映射到 Java 对象。

在 Spring Boot中, 只要配置好数据源 DataSource， 就能自动使用 JdbcTemplate

1
2
3
4
5
@Repository
public class UserDao {
    @Autowired
    JdbcTemplate jdbcTempalte;
}

@Repository 通常用在同存储相关的类上

查询

一个简单的返回总数的查询:

1
int rowCount = jdbcTempalte.queryForObject("select count(1) from user", Integer.class);

一 个带参数绑定的查询:

1
2
String sql = "select count(1) from user where department_id=?";
Integer rowCount = jdbcTempalte.queryForObject(sql, Integer.class, 1);

如果期望返回POJO实例，JdbcTemplate需要一个RowMapper，将查询结果集ResultSet映射成一个对象:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public User findUserById(Long userId) {
    String sql = "select * from user where id=?";
    User user = jdbcTempalte.queryForObject(sql, new RowMapper<User>() {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setDepartmentId(rs.getInt("department_id"));

        return user;
        }
    }, userId);
    return user;
}

通常RowMapper可以被其他查询复用，因此最好的办法是在 DAO 中创建一个内部类

1
2
3
4
5
6
7
8
9
static class UserRowMapper implements RowMapper<User> {
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setDepartmentId(rs.getInt("department_id"));
        return user;
    }
}

JdbcTemplate查询如果期望返回的是列表:

1
2
3
4
5
public List<User> getUserByDepartmentId(Long departmenetId) {
    String sql = "select * from user where department_id=? ";
    List<User> user = jdbcTempalte.query(sql, new UserRowMapper(), 1);
    return user;
}

对于大多数DAO框架来说，完成上述的查询会非常简单，不需要写 SOL，不需要写RowMapper，
比如后面要介绍的 BeetlSOL，以及 Spring Data 都是这种情况 。
以 BeetlSOL 为例，通过sqlManager可以直接完成上面的两个例子:

//根据主键查询
User user= sqlManager.unique(User.class , userid); //查询部门的所有用户
User template= new User();
template.setDepartmentid(departmentid);
List list= sqlManager.template(template);

大多数 DAO 框架都会自动生成 SOL，以及完成 ResultSet 到 POJO 的自动映射。

修改

JdbcTempalte 提供update方法来实现SQL的修改语旬，包括新增、修改、删除、执行存储 过程等

1
2
3
4
public void updateInfo(User user) {
    String sql = "update user set name=? and departmet_id=? where id = ?";
    jdbcTempalte.update(sql, user.getName(), user.getDepartmentId(), user.getId());
}

数据库插入:

1
2
3
4
5
6
7
8
9
10
11
12
13
public Integer insertUser(final User user) {
    final String sql = "insert into user (name, departmet_id ) values (?,?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTempalte.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
            ps.setString(1, user.getName());
            ps.setInt(2, user.getDepartmentId());
            return ps;
        }
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

keyHolder 包含了自增长的结果

JdbcTemplate 增强

JdbcTemplate，对SQL中的参数只支持传统的“?”占位符

NamedParameterJdbcTemplate继承了 JdbcTemplate,允许SQL中使用参数的名字作为占位符

1
2
@Autowired
NamedParameterJdbcTemplate namedParameterJdbcTemplate;

在SQL语句中，不再使用?，而是使用了“:”开头的参数名字，如“:deptld”

BeetlSQL介绍

功能概览

BeetlSQL是一个全功能 DAO工具，同时具有 Hibernate和 MyBatis的优点，适用于承认以SQL为中心，同时又需要工具能自动生成大量常用的 SQL的应用。

  • 开发效率
    • 无须注解，自动使用大量内置SQL，轻易完成增删改查功能，节省约50%的开发工作量
    • 数据模型支持 POJO，也支持 Map/List 这种快速模型，还支持混合模型
    • SQL模板基于Beetl 实现，更容易编写和调试 ，以及进行扩展
    • 可以针对单个表(或者视图)代码生成 POJO类和SQL模板，甚至是整个数据库，能减少代码编写工作量
    • 支持 ORM 查询功能
  • 维护性
    • SQL写在Markdown文件中，同时方便程序开发和数据库 SQL 调试
    • 可以自动将SQL文件映射为DAO接口类
    • 具备 Interceptor 功能，可以调试、性能诊断 SQL，以及扩展其他功能
  • 灵活直观地支持一对一、一对多、多对多关系映射而不引入复杂的ORMapping概念和技术
  • 其他
    • 内置支持主从数据库的开源工具
    • 持跨数据库平台，将开发者所要完成的工作减到最小，目前跨数据库支持 MySQL、PostgreSQL、Oracle、SQL Server、H2, SQLite、DB2

Maven依赖

1
2
3
4
5
<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetl-framework-starter</artifactId>
    <version>1.1.40.RELEASE</version>
</dependency>

配置 BeetlSQL

beetl-framework-starter 会读取 application.properties 如下配置:

  • beetlsql.sqlPath，默认为/sql，作为存放 SQL文件的根目录，位于/resources/sql 目录下
  • beetlsql.nameConversion, 默认是 org.beetl.sql.core.UnderlineNameConversion，能将下画线分割的数据库命名风格转化为 Java 驼峰命名风格，还有常用的 DefaultNameConversion, 数据库命名完全和 Java命名一致，以及 JPA2NameConversion，兼容 JPA 命名
  • beetl-beetlsql.dev, 默认是 true，即向控制台输出执行时的 SQL，包括参数、执行时间 及执行的位置，每次修改 SQL 文件时，自动检测 SQL 文件修改
  • beetlsql.daoSuffix，默认为 Dao。任何接口继承了 BaseMapper接口，将自动具备实体内置的CRUD功能。daoSuffix选项会在 SpringBoot 启动的时候，自动扫描以 Dao 结尾的接口，自动注册为 Spring 管理的 Dao 类，你可以 在任何地方自动注入这个 Dao类，比如在 Service类中。
  • beetlsqI.basePackage，默认为com，此选项配置 beetlsql.daoSuffix 来自动扫描 com 包及 其子包下的所有以 Dao 结尾的 Mapper 类。以本章例子而言，你可以配置com.bee.sample.ch5.dao
  • beetlsql.dbStyle，数据库风格，默认是 org.beetl.sql.core.db.MySqlStyle，对应不同的数据库，其他还有 OracleStyle、 PostgresStyle、 SqlServerStyle、 DB2Sq!Style、 SQLiteStyle、 H2Style

SQLManager

1
2
3
4
5
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    SqlManager sqlManager;
}

SQLManager 是 BeetlSQL 的核心类，提供了所有的数据访问操作:

  • 根据主键查找实体操作，可调用unique方法:
1
User user = sqlManager.unique(User.class, pk);

也可以调用 single 方法，如果未查找到，仅仅返回 null:

1
User user = sqlManager.single(User.class, pk);
  • 添加一个实体，调用 insert方法:
1
2
3
4
5
User user = new User();
user.setDepartmentID(1);
user.setCreateTime(new Date();
user.setName(name);
sqlManager.selectSample(user);

USER表的 ID是自增的，如果想获取到自增主键，可以传入true参数:

1
sqlManager.insert(user, true) ;
  • 根据主键更新实体:
1
2
3
4
5
User user= new User();
user.setid(1);
......
user.setName("NewName");
sqlManager.updateByid(user);
  • 只更新有值的属性:
1
2
3
4
User user = new User();
user.setid(l);
user.setName("NewName");
sqlManager.updateTemplateByid(user) ;

使用 SQL 文件

项目中还是有不少复杂的 SQL (有的只有五六行，有的甚至有数百行)，放在单独的 SQL 文件中更容易编写和维护，需要在/resources/sql 目录下创建 user.md 文件

1
2
3
4
5
6
7
8
9
10
selectSample
===

* 一个简单的查询例子
* 根据用户名查询用户

    select * from user where 1=1
    @if(!isEmpty(name)) {
    and name = #name#
    @}

简单说明:

  • 采用 md格式，===上面是 SQL语句在文件中的唯一标示，下面则是SQL语句
  • === 下面可以放多行注释，采用*开头
  • @和回车符号是定界符号，可以在里面写 Beetl语句，如条件判断语句
  • #是占位符，生成SQL语句的时候，占位符将输出?，并记录此位置的值，如果想直接输出值，需要用 text 函数，或者任何以 db 开头的函数，引擎则认为是直接输出文本
  • isEmpty是 Beetl 的一个函数，用来判断变量是否为空或者是否不存在
  • 文件名约定为类名，首字母小写

使用：

1
2
3
User query = new User();
query.setName("NewName");
List<User> list = sqlManager.select("user.selectSample", User.class, query);

Mapper

BeetlSQL 提供 Mapper 功能, 能将 md 文件的 sqlld 映射为方法名

BaseMapper是 BeetlSQL提供的一个内置 Dao接口，内置了多种增删改查方法，包含如下:

  • Insert 插入实体对象到数据库
  • insertTemplate，插入实体到数据库， 只插入非null属性
  • insertBatch， 批量插入实体
  • updateByld，按照主键更新实体
  • updateTemplateByld，按照主键更新实体，只更新非null的属性
  • deleteByld，按照主键删除实体，一些大型应用严格来说禁止删除，参考 BeetlSQL 官网了解如何定制 BaseMapper，不提供 deleteByld方法
  • unique，根据主键查询实体，如果没有找到，抛出Runtime异常
  • single，根据主键查询实体，如果没有找到，返回 null
  • lock, 根据主键使用数据库悲观锁， 相当于select * from table where id = ? for update
  • all， 返回所有实体
  • allCount，所有实体的个数
  • template， 查询符合模板的所有实体
  • templateOne，查询符合模板的第一个实体
  • execute，执行一个 JDBC查询， 在 Java中提供一个 SQL语句
  • executeUpdate，执行一个 JDBC 更新操作，在 Java 中提供一个 SQL 语句

使用实体

按照 NameConverson指定的命名约定对应一个普通的 JavaBean 即可

BeetlSQL 井不要求列名能和实体的属性名一一对应， SQL 操作的时候, 选取列名和属性名的 “交集”来操作

SQLManager 内置 CRUD

内置的插入 API

  • insert
  • insertTemplate
  • insertBatch

内置的更新(删除) API

  • updateByld
  • updateTemplateByld
  • updateBatchTemplateByld
  • updateByldBatch

内置的查询 API

  • all
  • allCount
  • template
  • templateOne
  • template
  • unique
  • single
  • templateCount

代码生成方法

BeetlSQL 能自动生成表对应的实体、 Dao 操作和 md 文件

md 文件里包含了相关 SQL 查询语句:

  • genPojoCodeToConsole
  • genSQLTemplateToConsole
  • genPojoCode
  • genSQLFile
  • genAll

使用sqlld

有些业务系统的数据库操作比较复杂，通常 SQL有很多行， 拼接 SQL的条件也较为复杂

BeetlSQL 支持将SQL放到 md 中，有以下好处:

  • md格式本身比较简单， 适合阅读书写
  • 与 BeetlSQL 类似的 MyBatis 采用 XML 格式来维护, md 比 XML 更合适 SQL 语句，因为没有像 XML 那样过多的格式控制，还有SQL中的 <符号是特殊符号，放在XML中必须转义
  • md 编辑器 比较丰富， 也支持导出到其他格式，方便项目所有人员阅读

md 文件命名

针对数据库中每一个表或者视图对应的实体 POJO，都可以创建一个同名且首字母小写的 文件名，放在默认 的 SQL 目录下

比如数据库表SYS_USER，对应的POJO是SysUser，可以在 SQL目录下创建sysUser.md文件

md 文件构成

调用 sqlld

  • 查询类接口

    • select
    • selectSingle
    • selectUnique
    • intValue

  • 插入方法

  • 更新方法

翻页查询

BeetJSQL 提供一个 PageQuery对象用于 SpringBoot应用的翻页查询 。它为查询提供参数、查询范围、排序

TailBean

BeetlSQL 提供 TailBean类，POJO类继承此类后，SQL查询结果集映射不到的字段将会放 到此类中，称之为混合模型

ORM 查询

其他API

  • 直接执行 SQL模板语句
  • SQLReady

BeetlSOL 的其它功能

  • SQL 模板语句常用函数和标签
  • 主键
  • BeetlSQL 常用注解
  • NameConvesion， 命名转化类
  • 悲观锁和乐观锁

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2021 朝着牛逼的道路一路狂奔 All Rights Reserved.

访客数 : | 访问量 :