jdbctemplate,jpa,mybatis,mybatis-plus,BeetlSQL
谢海阳
spring-boot-demo-orm-jdbctemplate
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-boot-demo-orm-jdbctemplate</artifactId>
<version>1.0.0-SNAPSHOT</version>
<packaging>jar</packaging>
<parent>
<groupId>com.xkcoding</groupId>
<artifactId>spring-boot-demo</artifactId>
<version>1.0.0-SNAPSHOT</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<finalName>spring-boot-demo-orm-jdbctemplate</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
BaseDao.java
@Slf4j
public class BaseDao<T, P> {
private JdbcTemplate jdbcTemplate;
private Class<T> clazz;
@SuppressWarnings(value = "unchecked")
public BaseDao(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
clazz = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
}
/**
* 通用插入，自增列需要添加 {@link Pk} 注解
*
* @param t 对象
* @param ignoreNull 是否忽略 null 值
* @return 操作的行数
*/
protected Integer insert(T t, Boolean ignoreNull) {
String table = getTableName(t);
List<Field> filterField = getField(t, ignoreNull);
List<String> columnList = getColumns(filterField);
String columns = StrUtil.join(Const.SEPARATOR_COMMA, columnList);
// 构造占位符
String params = StrUtil.repeatAndJoin("?", columnList.size(), Const.SEPARATOR_COMMA);
// 构造值
Object[] values = filterField.stream().map(field -> ReflectUtil.getFieldValue(t, field)).toArray();
String sql = StrUtil.format("INSERT INTO {table} ({columns}) VALUES ({params})", Dict.create().set("table", table).set("columns", columns).set("params", params));
log.debug("【执行SQL】SQL：{}", sql);
log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(values));
return jdbcTemplate.update(sql, values);
}
/**
* 通用根据主键删除
*
* @param pk 主键
* @return 影响行数
*/
protected Integer deleteById(P pk) {
String tableName = getTableName();
String sql = StrUtil.format("DELETE FROM {table} where id = ?", Dict.create().set("table", tableName));
log.debug("【执行SQL】SQL：{}", sql);
log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(pk));
return jdbcTemplate.update(sql, pk);
}
/**
* 通用根据主键更新，自增列需要添加 {@link Pk} 注解
*
* @param t 对象
* @param pk 主键
* @param ignoreNull 是否忽略 null 值
* @return 操作的行数
*/
protected Integer updateById(T t, P pk, Boolean ignoreNull) {
String tableName = getTableName(t);
List<Field> filterField = getField(t, ignoreNull);
List<String> columnList = getColumns(filterField);
List<String> columns = columnList.stream().map(s -> StrUtil.appendIfMissing(s, " = ?")).collect(Collectors.toList());
String params = StrUtil.join(Const.SEPARATOR_COMMA, columns);
// 构造值
List<Object> valueList = filterField.stream().map(field -> ReflectUtil.getFieldValue(t, field)).collect(Collectors.toList());
valueList.add(pk);
Object[] values = ArrayUtil.toArray(valueList, Object.class);
String sql = StrUtil.format("UPDATE {table} SET {params} where id = ?", Dict.create().set("table", tableName).set("params", params));
log.debug("【执行SQL】SQL：{}", sql);
log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(values));
return jdbcTemplate.update(sql, values);
}
/**
* 通用根据主键查询单条记录
*
* @param pk 主键
* @return 单条记录
*/
public T findOneById(P pk) {
String tableName = getTableName();
String sql = StrUtil.format("SELECT * FROM {table} where id = ?", Dict.create().set("table", tableName));
RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(clazz);
log.debug("【执行SQL】SQL：{}", sql);
log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(pk));
return jdbcTemplate.queryForObject(sql, new Object[]{pk}, rowMapper);
}
/**
* 根据对象查询
*
* @param t 查询条件
* @return 对象列表
*/
public List<T> findByExample(T t) {
String tableName = getTableName(t);
List<Field> filterField = getField(t, true);
List<String> columnList = getColumns(filterField);
List<String> columns = columnList.stream().map(s -> " and " + s + " = ? ").collect(Collectors.toList());
String where = StrUtil.join(" ", columns);
// 构造值
Object[] values = filterField.stream().map(field -> ReflectUtil.getFieldValue(t, field)).toArray();
String sql = StrUtil.format("SELECT * FROM {table} where 1=1 {where}", Dict.create().set("table", tableName).set("where", StrUtil.isBlank(where) ? "" : where));
log.debug("【执行SQL】SQL：{}", sql);
log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(values));
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, values);
List<T> ret = CollUtil.newArrayList();
maps.forEach(map -> ret.add(BeanUtil.fillBeanWithMap(map, ReflectUtil.newInstance(clazz), true, false)));
return ret;
}
/**
* 获取表名
*
* @param t 对象
* @return 表名
*/
private String getTableName(T t) {
Table tableAnnotation = t.getClass().getAnnotation(Table.class);
if (ObjectUtil.isNotNull(tableAnnotation)) {
return StrUtil.format("`{}`", tableAnnotation.name());
} else {
return StrUtil.format("`{}`", t.getClass().getName().toLowerCase());
}
}
/**
* 获取表名
*
* @return 表名
*/
private String getTableName() {
Table tableAnnotation = clazz.getAnnotation(Table.class);
if (ObjectUtil.isNotNull(tableAnnotation)) {
return StrUtil.format("`{}`", tableAnnotation.name());
} else {
return StrUtil.format("`{}`", clazz.getName().toLowerCase());
}
}
/**
* 获取列
*
* @param fieldList 字段列表
* @return 列信息列表
*/
private List<String> getColumns(List<Field> fieldList) {
// 构造列
List<String> columnList = CollUtil.newArrayList();
for (Field field : fieldList) {
Column columnAnnotation = field.getAnnotation(Column.class);
String columnName;
if (ObjectUtil.isNotNull(columnAnnotation)) {
columnName = columnAnnotation.name();
} else {
columnName = field.getName();
}
columnList.add(StrUtil.format("`{}`", columnName));
}
return columnList;
}
/**
* 获取字段列表 {@code 过滤数据库中不存在的字段，以及自增列}
*
* @param t 对象
* @param ignoreNull 是否忽略空值
* @return 字段列表
*/
private List<Field> getField(T t, Boolean ignoreNull) {
// 获取所有字段，包含父类中的字段
Field[] fields = ReflectUtil.getFields(t.getClass());
// 过滤数据库中不存在的字段，以及自增列
List<Field> filterField;
Stream<Field> fieldStream = CollUtil.toList(fields).stream().filter(field -> ObjectUtil.isNull(field.getAnnotation(Ignore.class)) || ObjectUtil.isNull(field.getAnnotation(Pk.class)));
// 是否过滤字段值为null的字段
if (ignoreNull) {
filterField = fieldStream.filter(field -> ObjectUtil.isNotNull(ReflectUtil.getFieldValue(t, field))).collect(Collectors.toList());
} else {
filterField = fieldStream.collect(Collectors.toList());
}
return filterField;
}
}
application.yml
server:
port: 8080
servlet:
context-path: /demo
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/spring-boot-demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
initialization-mode: always
continue-on-error: true
schema:
- "classpath:db/schema.sql"
data:
- "classpath:db/data.sql"
hikari:
minimum-idle: 5
connection-test-query: SELECT 1 FROM DUAL
maximum-pool-size: 20
auto-commit: true
idle-timeout: 30000
pool-name: SpringBootDemoHikariCP
max-lifetime: 60000
connection-timeout: 30000
logging:
level:
com.xkcoding: debug
spring-boot-demo-orm-jpa
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-boot-demo-orm-jpa</artifactId>
<version>1.0.0-SNAPSHOT</version>
<packaging>jar</packaging>
<parent>
<groupId>com.xkcoding</groupId>
<artifactId>spring-boot-demo</artifactId>
<version>1.0.0-SNAPSHOT</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<finalName>spring-boot-demo-orm-jpa</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
JpaConfig.java
@Configuration
@EnableTransactionManagement
@EnableJpaAuditing
@EnableJpaRepositories(basePackages = "com.xkcoding.orm.jpa.repository", transactionManagerRef = "jpaTransactionManager")
public class JpaConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
HibernateJpaVendorAdapter japVendor = new HibernateJpaVendorAdapter();
japVendor.setGenerateDdl(false);
LocalContainerEntityManagerFactoryBean entityManagerFactory = new LocalContainerEntityManagerFactoryBean();
entityManagerFactory.setDataSource(dataSource());
entityManagerFactory.setJpaVendorAdapter(japVendor);
entityManagerFactory.setPackagesToScan("com.xkcoding.orm.jpa.entity");
return entityManagerFactory;
}
@Bean
public PlatformTransactionManager jpaTransactionManager(EntityManagerFactory entityManagerFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(entityManagerFactory);
return transactionManager;
}
}
User.java
@EqualsAndHashCode(callSuper = true)
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@Entity
@Table(name = "orm_user")
@ToString(callSuper = true)
public class User extends AbstractAuditModel {
private String name;
private String password;
private String salt;
private String email;
@Column(name = "phone_number")
private String phoneNumber;
/**
* 状态，-1：逻辑删除，0：禁用，1：启用
*/
private Integer status;
/**
* 上次登录时间
*/
@Column(name = "last_login_time")
private Date lastLoginTime;
/**
* 关联部门表
* 1、关系维护端，负责多对多关系的绑定和解除
* 2、@JoinTable注解的name属性指定关联表的名字，joinColumns指定外键的名字，关联到关系维护端(User)
* 3、inverseJoinColumns指定外键的名字，要关联的关系被维护端(Department)
* 4、其实可以不使用@JoinTable注解，默认生成的关联表名称为主表表名+下划线+从表表名，
* 即表名为user_department
* 关联到主表的外键名：主表名+下划线+主表中的主键列名,即user_id,这里使用referencedColumnName指定
* 关联到从表的外键名：主表中用于关联的属性名+下划线+从表的主键列名,department_id
* 主表就是关系维护端对应的表，从表就是关系被维护端对应的表
*/
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = "orm_user_dept", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "dept_id", referencedColumnName = "id"))
private Collection<Department> departmentList;
}
Department.java
@EqualsAndHashCode(callSuper = true)
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "orm_department")
@ToString(callSuper = true)
public class Department extends AbstractAuditModel {
/**
* 部门名
*/
@Column(name = "name", columnDefinition = "varchar(255) not null")
private String name;
/**
* 上级部门id
*/
@ManyToOne(cascade = {CascadeType.REFRESH}, optional = true)
@JoinColumn(name = "superior", referencedColumnName = "id")
private Department superior;
/**
* 所属层级
*/
@Column(name = "levels", columnDefinition = "int not null default 0")
private Integer levels;
/**
* 排序
*/
@Column(name = "order_no", columnDefinition = "int not null default 0")
private Integer orderNo;
/**
* 子部门集合
*/
@OneToMany(cascade = {CascadeType.REFRESH, CascadeType.REMOVE}, fetch = FetchType.EAGER, mappedBy = "superior")
private Collection<Department> children;
/**
* 部门下用户集合
*/
@ManyToMany(mappedBy = "departmentList")
private Collection<User> userList;
}
AbstractAuditModel.java
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@Data
public abstract class AbstractAuditModel implements Serializable {
/**
* 主键
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/**
* 创建时间
*/
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "create_time", nullable = false, updatable = false)
@CreatedDate
private Date createTime;
/**
* 上次更新时间
*/
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "last_update_time", nullable = false)
@LastModifiedDate
private Date lastUpdateTime;
}
UserDao.java
@Repository
public interface UserDao extends JpaRepository<User, Long> {
}
DepartmentDao.java
@Repository
public interface DepartmentDao extends JpaRepository<Department, Long> {
/**
* 根据层级查询部门
*
* @param level 层级
* @return 部门列表
*/
List<Department> findDepartmentsByLevels(Integer level);
}
application.yml
server:
port: 8080
servlet:
context-path: /demo
spring:
datasource:
jdbc-url: jdbc:mysql://127.0.0.1:3306/spring-boot-demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
initialization-mode: always
continue-on-error: true
schema:
- "classpath:db/schema.sql"
data:
- "classpath:db/data.sql"
hikari:
minimum-idle: 5
connection-test-query: SELECT 1 FROM DUAL
maximum-pool-size: 20
auto-commit: true
idle-timeout: 30000
pool-name: SpringBootDemoHikariCP
max-lifetime: 60000
connection-timeout: 30000
jpa:
show-sql: true
hibernate:
ddl-auto: validate
properties:
hibernate:
dialect: org.hibernate.dialect.MySQL57InnoDBDialect
open-in-view: true
logging:
level:
com.xkcoding: debug
org.hibernate.SQL: debug
org.hibernate.type: trace
UserDaoTest.java
@Slf4j
public class UserDaoTest extends SpringBootDemoOrmJpaApplicationTests {
@Autowired
private UserDao userDao;
/**
* 测试保存
*/
@Test
public void testSave() {
String salt = IdUtil.fastSimpleUUID();
User testSave3 = User.builder().name("testSave3").password(SecureUtil.md5("123456" + salt)).salt(salt).email("testSave3@xkcoding.com").phoneNumber("17300000003").status(1).lastLoginTime(new DateTime()).build();
userDao.save(testSave3);
Assert.assertNotNull(testSave3.getId());
Optional<User> byId = userDao.findById(testSave3.getId());
Assert.assertTrue(byId.isPresent());
log.debug("【byId】= {}", byId.get());
}
/**
* 测试删除
*/
@Test
public void testDelete() {
long count = userDao.count();
userDao.deleteById(1L);
long left = userDao.count();
Assert.assertEquals(count - 1, left);
}
/**
* 测试修改
*/
@Test
public void testUpdate() {
userDao.findById(1L).ifPresent(user -> {
user.setName("JPA修改名字");
userDao.save(user);
});
Assert.assertEquals("JPA修改名字", userDao.findById(1L).get().getName());
}
/**
* 测试查询单个
*/
@Test
public void testQueryOne() {
Optional<User> byId = userDao.findById(1L);
Assert.assertTrue(byId.isPresent());
log.debug("【byId】= {}", byId.get());
}
/**
* 测试查询所有
*/
@Test
public void testQueryAll() {
List<User> users = userDao.findAll();
Assert.assertNotEquals(0, users.size());
log.debug("【users】= {}", users);
}
/**
* 测试分页排序查询
*/
@Test
public void testQueryPage() {
// 初始化数据
initData();
// JPA分页的时候起始页是页码减1
Integer currentPage = 0;
Integer pageSize = 5;
Sort sort = Sort.by(Sort.Direction.DESC, "id");
PageRequest pageRequest = PageRequest.of(currentPage, pageSize, sort);
Page<User> userPage = userDao.findAll(pageRequest);
Assert.assertEquals(5, userPage.getSize());
Assert.assertEquals(userDao.count(), userPage.getTotalElements());
log.debug("【id】= {}", userPage.getContent().stream().map(User::getId).collect(Collectors.toList()));
}
/**
* 初始化10条数据
*/
private void initData() {
List<User> userList = Lists.newArrayList();
for (int i = 0; i < 10; i++) {
String salt = IdUtil.fastSimpleUUID();
int index = 3 + i;
User user = User.builder().name("testSave" + index).password(SecureUtil.md5("123456" + salt)).salt(salt).email("testSave" + index + "@xkcoding.com").phoneNumber("1730000000" + index).status(1).lastLoginTime(new DateTime()).build();
userList.add(user);
}
userDao.saveAll(userList);
}
}
DepartmentDaoTest.java
/**
* <p>
* jpa 测试类
* </p>
*
* @author 76peter
* @date Created in 2018-11-07 14:09
*/
@Slf4j
public class DepartmentDaoTest extends SpringBootDemoOrmJpaApplicationTests {
@Autowired
private DepartmentDao departmentDao;
@Autowired
private UserDao userDao;
/**
* 测试保存 ,根节点
*/
@Test
@Transactional
public void testSave() {
Collection<Department> departmentList = departmentDao.findDepartmentsByLevels(0);
if (departmentList.size() == 0) {
Department testSave1 = Department.builder().name("testSave1").orderNo(0).levels(0).superior(null).build();
Department testSave1_1 = Department.builder().name("testSave1_1").orderNo(0).levels(1).superior(testSave1).build();
Department testSave1_2 = Department.builder().name("testSave1_2").orderNo(0).levels(1).superior(testSave1).build();
Department testSave1_1_1 = Department.builder().name("testSave1_1_1").orderNo(0).levels(2).superior(testSave1_1).build();
departmentList.add(testSave1);
departmentList.add(testSave1_1);
departmentList.add(testSave1_2);
departmentList.add(testSave1_1_1);
departmentDao.saveAll(departmentList);
Collection<Department> deptall = departmentDao.findAll();
log.debug("【部门】= {}", JSONArray.toJSONString((List) deptall));
}
userDao.findById(1L).ifPresent(user -> {
user.setName("添加部门");
Department dept = departmentDao.findById(2L).get();
user.setDepartmentList(departmentList);
userDao.save(user);
});
log.debug("用户部门={}", JSONUtil.toJsonStr(userDao.findById(1L).get().getDepartmentList()));
departmentDao.findById(2L).ifPresent(dept -> {
Collection<User> userlist = dept.getUserList();
//关联关系由user维护中间表，department userlist不会发生变化，可以增加查询方法来处理 重写getUserList方法
log.debug("部门下用户={}", JSONUtil.toJsonStr(userlist));
});
userDao.findById(1L).ifPresent(user -> {
user.setName("清空部门");
user.setDepartmentList(null);
userDao.save(user);
});
log.debug("用户部门={}", userDao.findById(1L).get().getDepartmentList());
}
}
参考
- Spring Data JPA 官方文档：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
spring-boot-demo-orm-mybatis
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-boot-demo-orm-mybatis</artifactId>
<version>1.0.0-SNAPSHOT</version>
<packaging>jar</packaging>
<parent>
<groupId>com.xkcoding</groupId>
<artifactId>spring-boot-demo</artifactId>
<version>1.0.0-SNAPSHOT</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<mybatis.version>1.3.2</mybatis.version>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<finalName>spring-boot-demo-orm-mybatis</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
SpringBootDemoOrmMybatisApplication.java
@MapperScan(basePackages = {"com.xkcoding.orm.mybatis.mapper"})
@SpringBootApplication
public class SpringBootDemoOrmMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootDemoOrmMybatisApplication.class, args);
}
}
application.yml
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/spring-boot-demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
initialization-mode: always
continue-on-error: true
schema:
- "classpath:db/schema.sql"
data:
- "classpath:db/data.sql"
hikari:
minimum-idle: 5
connection-test-query: SELECT 1 FROM DUAL
maximum-pool-size: 20
auto-commit: true
idle-timeout: 30000
pool-name: SpringBootDemoHikariCP
max-lifetime: 60000
connection-timeout: 30000
logging:
level:
com.xkcoding: debug
com.xkcoding.orm.mybatis.mapper: trace
mybatis:
configuration:
# 下划线转驼峰
map-underscore-to-camel-case: true
mapper-locations: classpath:mappers/*.xml
type-aliases-package: com.xkcoding.orm.mybatis.entity
UserMapper.java
@Mapper
@Component
public interface UserMapper {
@Select("SELECT * FROM orm_user")
List<User> selectAllUser();
@Select("SELECT * FROM orm_user WHERE id = #{id}")
User selectUserById(@Param("id") Long id);
int saveUser(@Param("user") User user);
int deleteById(@Param("id") Long id);
}
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xkcoding.orm.mybatis.mapper.UserMapper">
<insert id="saveUser">
INSERT INTO `orm_user` (`name`,
`password`,
`salt`,
`email`,
`phone_number`,
`status`,
`create_time`,
`last_login_time`,
`last_update_time`)
VALUES (#{user.name},
#{user.password},
#{user.salt},
#{user.email},
#{user.phoneNumber},
#{user.status},
#{user.createTime},
#{user.lastLoginTime},
#{user.lastUpdateTime})
</insert>
<delete id="deleteById">
DELETE
FROM `orm_user`
WHERE `id` = #{id}
</delete>
</mapper>
UserMapperTest.java
@Slf4j
public class UserMapperTest extends SpringBootDemoOrmMybatisApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void selectAllUser() {
List<User> userList = userMapper.selectAllUser();
Assert.assertTrue(CollUtil.isNotEmpty(userList));
log.debug("【userList】= {}", userList);
}
@Test
public void selectUserById() {
User user = userMapper.selectUserById(1L);
Assert.assertNotNull(user);
log.debug("【user】= {}", user);
}
@Test
public void saveUser() {
String salt = IdUtil.fastSimpleUUID();
User user = User.builder().name("testSave3").password(SecureUtil.md5("123456" + salt)).salt(salt).email("testSave3@xkcoding.com").phoneNumber("17300000003").status(1).lastLoginTime(new DateTime()).createTime(new DateTime()).lastUpdateTime(new DateTime()).build();
int i = userMapper.saveUser(user);
Assert.assertEquals(1, i);
}
@Test
public void deleteById() {
int i = userMapper.deleteById(1L);
Assert.assertEquals(1, i);
}
}
参考
- Mybatis官方文档：http://www.mybatis.org/mybatis-3/zh/index.html
- Mybatis官方脚手架文档：http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/
- Mybatis整合Spring Boot官方demo：https://github.com/mybatis/spring-boot-starter/tree/master/mybatis-spring-boot-samples
spring-boot-demo-orm-mybatis-plus
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-boot-demo-orm-mybatis-plus</artifactId>
<version>1.0.0-SNAPSHOT</version>
<packaging>jar</packaging>
<parent>
<groupId>com.xkcoding</groupId>
<artifactId>spring-boot-demo</artifactId>
<version>1.0.0-SNAPSHOT</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<mybatis.plus.version>3.0.5</mybatis.plus.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis.plus.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<finalName>spring-boot-demo-orm-mybatis-plus</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
MybatisPlusConfig.java
@Configuration
@MapperScan(basePackages = {"com.xkcoding.orm.mybatis.plus.mapper"})
@EnableTransactionManagement
public class MybatisPlusConfig {
/**
* 性能分析拦截器，不建议生产使用
*/
@Bean
public PerformanceInterceptor performanceInterceptor(){
return new PerformanceInterceptor();
}
/**
* 分页插件
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
CommonFieldHandler.java
package com.xkcoding.orm.mybatis.plus.config;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;
@Slf4j
@Component
public class CommonFieldHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
log.info("start insert fill ....");
this.setFieldValByName("createTime", new Date(), metaObject);
this.setFieldValByName("lastUpdateTime", new Date(), metaObject);
}
@Override
public void updateFill(MetaObject metaObject) {
log.info("start update fill ....");
this.setFieldValByName("lastUpdateTime", new Date(), metaObject);
}
}
application.yml
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/spring-boot-demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
initialization-mode: always
continue-on-error: true
schema:
- "classpath:db/schema.sql"
data:
- "classpath:db/data.sql"
hikari:
minimum-idle: 5
connection-test-query: SELECT 1 FROM DUAL
maximum-pool-size: 20
auto-commit: true
idle-timeout: 30000
pool-name: SpringBootDemoHikariCP
max-lifetime: 60000
connection-timeout: 30000
logging:
level:
com.xkcoding: debug
com.xkcoding.orm.mybatis.plus.mapper: trace
mybatis-plus:
mapper-locations: classpath:mappers/*.xml
#实体扫描，多个package用逗号或者分号分隔
typeAliasesPackage: com.xkcoding.orm.mybatis.plus.entity
global-config:
# 数据库相关配置
db-config:
#主键类型 AUTO:"数据库ID自增", INPUT:"用户输入ID",ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
id-type: auto
#字段策略 IGNORED:"忽略判断",NOT_NULL:"非 NULL 判断"),NOT_EMPTY:"非空判断"
field-strategy: not_empty
#驼峰下划线转换
table-underline: true
#是否开启大写命名，默认不开启
#capital-mode: true
#逻辑删除配置
#logic-delete-value: 1
#logic-not-delete-value: 0
db-type: mysql
#刷新mapper 调试神器
refresh: true
# 原生配置
configuration:
map-underscore-to-camel-case: true
cache-enabled: true
UserMapper.java
@Component
public interface UserMapper extends BaseMapper<User> {
}
UserService.java
public interface UserService extends IService<User> {
}
UserServiceImpl.java
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
UserServiceTest.java
@Slf4j
public class UserServiceTest extends SpringBootDemoOrmMybatisPlusApplicationTests {
@Autowired
private UserService userService;
@Test
public void testSave() {
String salt = IdUtil.fastSimpleUUID();
User testSave3 = User.builder().name("testSave3").password(SecureUtil.md5("123456" + salt)).salt(salt).email("testSave3@xkcoding.com").phoneNumber("17300000003").status(1).lastLoginTime(new DateTime()).build();
boolean save = userService.save(testSave3);
Assert.assertTrue(save);
log.debug("【测试id回显#testSave3.getId()】= {}", testSave3.getId());
}
/**
* 测试Mybatis-Plus 批量新增
*/
@Test
public void testSaveList() {
List<User> userList = Lists.newArrayList();
for (int i = 4; i < 14; i++) {
String salt = IdUtil.fastSimpleUUID();
User user = User.builder().name("testSave" + i).password(SecureUtil.md5("123456" + salt)).salt(salt).email("testSave" + i + "@xkcoding.com").phoneNumber("1730000000" + i).status(1).lastLoginTime(new DateTime()).build();
userList.add(user);
}
boolean batch = userService.saveBatch(userList);
Assert.assertTrue(batch);
List<Long> ids = userList.stream().map(User::getId).collect(Collectors.toList());
log.debug("【userList#ids】= {}", ids);
}
/**
* 测试Mybatis-Plus 删除
*/
@Test
public void testDelete() {
boolean remove = userService.removeById(1L);
Assert.assertTrue(remove);
User byId = userService.getById(1L);
Assert.assertNull(byId);
}
/**
* 测试Mybatis-Plus 修改
*/
@Test
public void testUpdate() {
User user = userService.getById(1L);
Assert.assertNotNull(user);
user.setName("MybatisPlus修改名字");
boolean b = userService.updateById(user);
Assert.assertTrue(b);
User update = userService.getById(1L);
Assert.assertEquals("MybatisPlus修改名字", update.getName());
log.debug("【update】= {}", update);
}
/**
* 测试Mybatis-Plus 查询单个
*/
@Test
public void testQueryOne() {
User user = userService.getById(1L);
Assert.assertNotNull(user);
log.debug("【user】= {}", user);
}
/**
* 测试Mybatis-Plus 查询全部
*/
@Test
public void testQueryAll() {
List<User> list = userService.list(new QueryWrapper<>());
Assert.assertTrue(CollUtil.isNotEmpty(list));
log.debug("【list】= {}", list);
}
/**
* 测试Mybatis-Plus 分页排序查询
*/
@Test
public void testQueryByPageAndSort() {
initData();
int count = userService.count(new QueryWrapper<>());
Page<User> userPage = new Page<>(1, 5);
userPage.setDesc("id");
IPage<User> page = userService.page(userPage, new QueryWrapper<>());
Assert.assertEquals(5, page.getSize());
Assert.assertEquals(count, page.getTotal());
log.debug("【page.getRecords()】= {}", page.getRecords());
}
/**
* 测试Mybatis-Plus 自定义查询
*/
@Test
public void testQueryByCondition() {
initData();
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like("name", "Save1").or().eq("phone_number", "17300000001").orderByDesc("id");
int count = userService.count(wrapper);
Page<User> userPage = new Page<>(1, 3);
IPage<User> page = userService.page(userPage, wrapper);
Assert.assertEquals(3, page.getSize());
Assert.assertEquals(count, page.getTotal());
log.debug("【page.getRecords()】= {}", page.getRecords());
}
/**
* 初始化数据
*/
private void initData() {
testSaveList();
}
}
ActiveRecord 模式
- Role.java
@Data
@TableName("orm_role")
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true)
public class Role extends Model<Role> {
/**
* 主键
*/
private Long id;
/**
* 角色名
*/
private String name;
/**
* 主键值，ActiveRecord 模式这个必须有，否则 xxById 的方法都将失效！
* 即使使用 ActiveRecord 不会用到 RoleMapper，RoleMapper 这个接口也必须创建
*/
@Override
protected Serializable pkVal() {
return this.id;
}
}
- RoleMapper.java
public interface RoleMapper extends BaseMapper<Role> {
}
- ActiveRecordTest.java
@Slf4j
public class ActiveRecordTest extends SpringBootDemoOrmMybatisPlusApplicationTests {
/**
* 测试 ActiveRecord 插入数据
*/
@Test
public void testActiveRecordInsert() {
Role role = new Role();
role.setName("VIP");
Assert.assertTrue(role.insert());
// 成功直接拿会写的 ID
log.debug("【role】= {}", role);
}
/**
* 测试 ActiveRecord 更新数据
*/
@Test
public void testActiveRecordUpdate() {
Assert.assertTrue(new Role().setId(1L).setName("管理员-1").updateById());
Assert.assertTrue(new Role().update(new UpdateWrapper<Role>().lambda().set(Role::getName, "普通用户-1").eq(Role::getId, 2)));
}
/**
* 测试 ActiveRecord 查询数据
*/
@Test
public void testActiveRecordSelect() {
Assert.assertEquals("管理员", new Role().setId(1L).selectById().getName());
Role role = new Role().selectOne(new QueryWrapper<Role>().lambda().eq(Role::getId, 2));
Assert.assertEquals("普通用户", role.getName());
List<Role> roles = new Role().selectAll();
Assert.assertTrue(roles.size() > 0);
log.debug("【roles】= {}", roles);
}
/**
* 测试 ActiveRecord 删除数据
*/
@Test
public void testActiveRecordDelete() {
Assert.assertTrue(new Role().setId(1L).deleteById());
Assert.assertTrue(new Role().delete(new QueryWrapper<Role>().lambda().eq(Role::getName, "普通用户")));
}
}
参考
- mybatis-plus官方文档：http://mp.baomidou.com/
spring-boot-demo-orm-beetlsql
整合beetl sql快捷操作数据库，使用的是beetl官方提供的beetl-framework-starter集成。集成过程不是十分顺利，没有其他的orm框架集成的便捷。
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-boot-demo-orm-beetlsql</artifactId>
<version>1.0.0-SNAPSHOT</version>
<packaging>jar</packaging>
<parent>
<groupId>com.xkcoding</groupId>
<artifactId>spring-boot-demo</artifactId>
<version>1.0.0-SNAPSHOT</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<ibeetl.version>1.1.68.RELEASE</ibeetl.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetl-framework-starter</artifactId>
<version>${ibeetl.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<finalName>spring-boot-demo-orm-beetlsql</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.yml
注意下方注释的地方，不能解开注释，并且需要通过JavaConfig的方式手动配置数据源，
否则，会导致beetl启动失败，因此，初始化数据库的数据，只能手动在数据库使用 resources/db 下的建表语句和数据库初始化数据。
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/spring-boot-demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
#### beetlsql starter不能开启下面选项
# type: com.zaxxer.hikari.HikariDataSource
# initialization-mode: always
# continue-on-error: true
# schema:
# - "classpath:db/schema.sql"
# data:
# - "classpath:db/data.sql"
# hikari:
# minimum-idle: 5
# connection-test-query: SELECT 1 FROM DUAL
# maximum-pool-size: 20
# auto-commit: true
# idle-timeout: 30000
# pool-name: SpringBootDemoHikariCP
# max-lifetime: 60000
# connection-timeout: 30000
logging:
level:
com.xkcoding: debug
com.xkcoding.orm.beetlsql: trace
beetl:
enabled: false
beetlsql:
enabled: true
sqlPath: /sql
daoSuffix: Dao
basePackage: com.xkcoding.orm.beetlsql.dao
dbStyle: org.beetl.sql.core.db.MySqlStyle
nameConversion: org.beetl.sql.core.UnderlinedNameConversion
beet-beetlsql:
dev: true
BeetlConfig.java
@Configuration
public class BeetlConfig {
/**
* Beetl需要显示的配置数据源，方可启动项目，大坑，切记！
*/
@Bean(name = "datasource")
public DataSource getDataSource(Environment env){
HikariDataSource dataSource = new HikariDataSource();
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setJdbcUrl(env.getProperty("spring.datasource.url"));
dataSource.setUsername(env.getProperty("spring.datasource.username"));
dataSource.setPassword(env.getProperty("spring.datasource.password"));
return dataSource;
}
}
UserDao.java
@Component
public interface UserDao extends BaseMapper<User> {}
UserServiceImpl.java
@Service
@Slf4j
public class UserServiceImpl implements UserService {
private final UserDao userDao;
@Autowired
public UserServiceImpl(UserDao userDao) {
this.userDao = userDao;
}
@Override
public User saveUser(User user) {
userDao.insert(user, true);
return user;
}
@Override
public void saveUserList(List<User> users) {
userDao.insertBatch(users);
}
@Override
public void deleteUser(Long id) {
userDao.deleteById(id);
}
@Override
public User updateUser(User user) {
if (ObjectUtil.isNull(user)) {
throw new RuntimeException("用户id不能为null");
}
userDao.updateTemplateById(user);
return userDao.single(user.getId());
}
@Override
public User getUser(Long id) {
return userDao.single(id);
}
@Override
public List<User> getUserList() {
return userDao.all();
}
@Override
public PageQuery<User> getUserByPage(Integer currentPage, Integer pageSize) {
return userDao.createLambdaQuery().page(currentPage, pageSize);
}
}
UserServiceTest.java
@Slf4j
public class UserServiceTest extends SpringBootDemoOrmBeetlsqlApplicationTests {
@Autowired
private UserService userService;
@Test
public void saveUser() {
String salt = IdUtil.fastSimpleUUID();
User user = User.builder().name("testSave3").password(SecureUtil.md5("123456" + salt)).salt(salt).email("testSave3@xkcoding.com").phoneNumber("17300000003").status(1).lastLoginTime(new DateTime()).createTime(new DateTime()).lastUpdateTime(new DateTime()).build();
user = userService.saveUser(user);
Assert.assertTrue(ObjectUtil.isNotNull(user.getId()));
log.debug("【user】= {}", user);
}
@Test
public void saveUserList() {
List<User> users = Lists.newArrayList();
for (int i = 5; i < 15; i++) {
String salt = IdUtil.fastSimpleUUID();
User user = User.builder().name("testSave" + i).password(SecureUtil.md5("123456" + salt)).salt(salt).email("testSave" + i + "@xkcoding.com").phoneNumber("1730000000" + i).status(1).lastLoginTime(new DateTime()).createTime(new DateTime()).lastUpdateTime(new DateTime()).build();
users.add(user);
}
userService.saveUserList(users);
Assert.assertTrue(userService.getUserList().size() > 2);
}
@Test
public void deleteUser() {
userService.deleteUser(1L);
User user = userService.getUser(1L);
Assert.assertTrue(ObjectUtil.isNull(user));
}
@Test
public void updateUser() {
User user = userService.getUser(2L);
user.setName("beetlSql 修改后的名字");
User update = userService.updateUser(user);
Assert.assertEquals("beetlSql 修改后的名字", update.getName());
log.debug("【update】= {}", update);
}
@Test
public void getUser() {
User user = userService.getUser(1L);
Assert.assertNotNull(user);
log.debug("【user】= {}", user);
}
@Test
public void getUserList() {
List<User> userList = userService.getUserList();
Assert.assertTrue(CollUtil.isNotEmpty(userList));
log.debug("【userList】= {}", userList);
}
@Test
public void getUserByPage() {
List<User> userList = userService.getUserList();
PageQuery<User> userByPage = userService.getUserByPage(1, 5);
Assert.assertEquals(5, userByPage.getList().size());
Assert.assertEquals(userList.size(), userByPage.getTotalRow());
log.debug("【userByPage】= {}", JSONUtil.toJsonStr(userByPage));
}
}
参考
- BeetlSQL官方文档：http://ibeetl.com/guide/#beetlsql
- 开源项目：https://gitee.com/yangkb/springboot-beetl-beetlsql
- 博客：https://blog.csdn.net/flystarfly/article/details/82752597
类似资料：