JAP多表查询
多表查询在Spring Data JPA 中有两种实现方式,第一种是创建结果集的接口来接收多表联接查询后的结果,第二种是利用JPA的关联映射来实现。
- 数据库表及关系
crm数据库,包含sys_user用户表和sys_role角色表
CREATE TABLE `sys_role` (
`role_id` bigint(20) NOT NULL AUTO_INCREMENT,
`role_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`role_desc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`role_flag` int(11) DEFAULT NULL,
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `sys_user` (
`usr_id` bigint(20) NOT NULL AUTO_INCREMENT,
`usr_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`usr_password` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`usr_role_id` bigint(20) DEFAULT NULL,
`usr_flag` int(11) DEFAULT NULL,
PRIMARY KEY (`usr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
存在主外键的关系
多表联接查询
首先创建一个 包pojo实体类Role
package com.aiweiyi.qingjing.demo.pojo;
import lombok.Data;
import javax.persistence.*;
import java.io.Serializable;
@Table(name = "sys_role")
@Entity
@Data
public class Role implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "role_id")
private Long roleId;
@Column(name = "role_name")
private String roleName;
@Column(name = "role_desc")
private String roleDesc;
@Column(name = "role_flag")
private Integer roleFlag;
}
然后在Vo包下创建一个接口UserInfo,用于提供getter方法,包含用户数据和角色名称(roleName)
package com.aiweiyi.qingjing.demo.vo;
public interface UserInfo {
public Long getUsrId();
public String getUsrName();
public String getUsrPassword();
public Long getUsrRoleId();
public Integer getUsrFlag();
//角色名称
public String getRoleName();
}
-
在运行中Spring会给接口UsrInfo自动生产一个代理类来接收返回的结果,代替了代码中的get**形式来获取
-
在UserRepository中添加查询方法,返回类型是UserInfo中添加查询方法,返回类型设置为UserInfo:
@Query("select u.usrId as usrId,u.usrName as usrName,u.usrPassword" +
" as usrPassword,u.usrRoleId as usrRoleId,u.usrFlag as usrFlag ,r.roleName as roleName" +
" from User u,Role r where u.usrRoleId=r.roleId and u.usrId=?1")
public UserInfo getUserInfo(Long usrId);
测试验证:
@Test
public void testUserInfo(){
UserInfo userInfo = userRepository.getUserInfo(1L);
System.out.println("usrName:"+userInfo.getUsrName());
System.out.println("roleName:"+userInfo.getRoleName());
}
关系映射
-
在软件开发中,类鱼类之间的关系就是关联关系,而且是有方向(目的性)的,我们User(用户表)与角色表(Role)之间存在主外键的关联关系,而一个用户只能属于一个角色,一个角色能有多个用户。
-
可能说的有点不懂,通俗的来说如:
-
User 和 Role 之间,介绍映射多对一单向关联映射
-
User 和 Role 之间,介绍映射一对多单向关联映射
单向多对一关联
通过主外键的映射关系,建立多对一的单向关联关系
首先创建一个User实体
package com.aiweiyi.qingjing.demo.pojo;
import lombok.Data;
import javax.persistence.*;
import java.io.Serializable;
@Data//get set方法
@Entity//标注对应
@Table(name = "sys_user")//对应数据库表名
public class User implements Serializable {
@Id//id
@GeneratedValue(strategy = GenerationType.IDENTITY)//主键的一些类型,如自增等功能
@Column(name = "usr_id")//对应的列名
private Long usrId;
@Column(name = "usr_name")
private String usrName;
@Column(name = "usr_password")
private String usrPassword;
@ManyToOne(targetEntity = Role.class)
@JoinColumn(name = "usr_role_id")
private Role role;
@Column(name = "usr_flag")
private Integer usrFlag;
}
- @ManyToOne:注解映射多对一关联关系,targetEntity 属性标识关联实体类型。
- @JoinColumn:注解映射关联的外键字段,则生成一张新表维护两个对象之间的关系。
创建UserRepository
public interface UserRepository extends JpaRepository<User,Long>, JpaSpecificationExecutor {
}
测试基本的CRUD(添加,删除,修改,查询操作)
@Test
public void testGet(){
User user = userRepository.findById(1L).get();
System.out.println("usrName:"+user.getUsrName());
System.out.println("roleName:"+user.getRole().getRoleName());
}
- 从输出的SQL语句可以发现,执行查询User,关联了Role数据,并将Role相关数据自自动封装在User对象的role属性中。
双向一对多关联
我们通过Role(用户类)类到User(用户类)类的双向一对多关联。
首先修改Role实体类,添加关联的User对象集合。
package com.aiweiyi.qingjing.demo.pojo;
import lombok.Data;
import javax.persistence.*;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
@Table(name = "sys_role")
@Entity
@Data
public class Role implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "role_id")
private Long roleId;
@Column(name = "role_name")
private String roleName;
@Column(name = "role_desc")
private String roleDesc;
@Column(name = "role_flag")
private Integer roleFlag;
@OneToMany(targetEntity = User.class, fetch = FetchType.EAGER, cascade = CascadeType.PERSIST, mappedBy = "role")
private Set<User> users = new HashSet<User>();
}
-
添加了Setusers属性,使用Set集合可避免重复对象的问题,用于表示一对多关联,在该属性上我们添加了@OneToMany注解,映射一对多关联关系。
-
targetEntity:属性表示关联的实体类型
-
fetch:属性表示加载,FetchType值有:LAZY、EAGER,LAZY表示懒加载俗称延迟加载,EAGER表示立即加载,马上加载,该@OneToMany注解默认是懒加载,LAZY。
-
cascade:表示级联操作,取值有PERSIST、REMOVE、ALL等等属性,PERSIST:表示添加、修改操作 ,REMOVE:表示删除或移除的操作,ALL:表示全部的级联操作,不建议使用!
-
mappedBy:表示外键的意思,用于属性来设置对象之间的关系维护方(关系维护通过操作外键完成),如果没有指定该mappedBy属性,则默认对象均由自己维护关系(外键),若@OneToMany注解中指定mappedBy属性,那么属性值为多方对象中关联的一方属性名,并且此时一方实体中不能添加@JoinColumn主键。
创建RoleRepository
package com.aiweiyi.qingjing.demo.repository;
import com.aiweiyi.qingjing.demo.pojo.Role;
import org.springframework.data.jpa.repository.JpaRepository;
public interface RoleRepository extends JpaRepository<Role,Long> {
}
测试验证:
@Test
public void testGet(){
Role role=roleRepository.findById(1L).get();
System.out.println("roleName:"+role.getRoleName());
System.out.println("users.size:"+role.getUsers().size());
}
- 测试级联操作:级联新增
@Test
public void testAdd() {
Role role = new Role("测试用户", "演示级联新增就是和用户", 1);
User user1 = new User("测试用户1", "123456",role, 1);
User user2 = new User("测试用户2", "123456", role,1);
role.getUsers().add(user1);
role.getUsers().add(user2);
roleRepository.save(role);
}
测试级联删除:
@Test
public void testDel(){
Role role =roleRepository.getOne(13L);
roleRepository.delete(role);
}
MyBatis
MyBatis是一款标准的ORM框架,被广泛的应用于各企业开发中,MyBatis最早是Apache的一个开源项目iBatis,2010年这个项目由Apache Software Foundation迁移到了Google Code,并改名为MyBatis,2013年11月又迁移到Github,MyBatis支持普通SQL语句。
优点:
- SQL被统一提取出来,便于统一管理和优化
- SQL和代码解耦,将业务逻辑和数据访问逻辑分离,使系统的设计更清晰、更易维护、更易单元测试
- 提供映射标签,支持对象与数据库的ORM字段关系映射
- 提供对象关系映射标签,支持对象关系组件维护
- 灵活书写动态SQL,支持各种条件来动态生成不同的SQL
缺点:
- 编写SQL语句时工作量很大,尤其是字段多、关联表多时,更是如此
- SQL语句依赖于数据库,导致数据库移植性差
Spring Boot集成MyBatis
依赖包:
- mybatis-spring-boot-starter是MyBatis帮助我们快速集成Spring Boot提供的一个组件有以下优点:
- 构建独立的应用
- 几乎可以零配置
- 需要很少的XML配置
首先我们创建一个项目:
添加关键依赖包:
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
application.properties配置:
#配置tomcat的端口号默认是8080
server.port=8080
#配置数据源相关新
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/crm?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
#配置JPA(Hibernate)相关信息
#spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
#配置mybatis
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
mybatis.type-aliases-package=com.aiweiyi.qingjing.demo.pojo
启动类:
在启动类中添加队Mapper包扫描@MapperScan,Spring Boot启动的时候会自动加载包路径下的Mapper。
package com.aiweiyi.qingjing.demo;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.aiweiyi.qingjing.demo.mapper")//扫描包
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
编码:
resources/mybatis目录下添加mybatis-config.xml,配置一些全局配置
mybatis-config.xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 框架的全局设置-->
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="autoMappingBehavior" value="FULL"/>
</settings>
</configuration>
实体类:User.java
package com.aiweiyi.qingjing.demo.pojo;
import java.io.Serializable;
public class User implements Serializable {
private Long usrId;
private String usrName;
private String usrPassword;
private Long usrRoleId;
private Integer usrFlag;
public User() {
}
public User( String usrName, String usrPassword, Long usrRoleId, Integer usrFlag) {
this.usrName = usrName;
this.usrPassword = usrPassword;
this.usrRoleId = usrRoleId;
this.usrFlag = usrFlag;
}
public Long getUsrId() {
return usrId;
}
public void setUsrId(Long usrId) {
this.usrId = usrId;
}
public String getUsrName() {
return usrName;
}
public void setUsrName(String usrName) {
this.usrName = usrName;
}
public String getUsrPassword() {
return usrPassword;
}
public void setUsrPassword(String usrPassword) {
this.usrPassword = usrPassword;
}
public Long getUsrRoleId() {
return usrRoleId;
}
public void setUsrRoleId(Long usrRoleId) {
this.usrRoleId = usrRoleId;
}
public Integer getUsrFlag() {
return usrFlag;
}
public void setUsrFlag(Integer usrFlag) {
this.usrFlag = usrFlag;
}
@Override
public String toString() {
return "User{" +
"usrId=" + usrId +
", usrName='" + usrName + '\'' +
", usrPassword='" + usrPassword + '\'' +
", usrRoleId=" + usrRoleId +
", usrFlag=" + usrFlag +
'}';
}
}
编写Mapper接口:
package com.aiweiyi.qingjing.demo.mapper;
import com.aiweiyi.qingjing.demo.pojo.User;
import java.util.List;
public interface UserMapper {
public void insert(User user);
public void delete(Long id);
public void update(User user);
public User get(Long id);
public List<User> findAll();
}
resources/mybatis/mapper目录下创建并编写UserMapper.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aiweiyi.qingjing.demo.mapper.UserMapper">
<resultMap id="UserResultMap" type="User">
<id column="usr_id" property="usrId"/>
<result column="usr_name" property="usrName"/>
<result column="usr_password" property="usrPassword"/>
<result column="usr_role_id" property="usrRoleId"/>
<result column="usr_flag" property="usrFlag"/>
</resultMap>
<sql id="column">
usr_id,usr_name,usr_password,usr_role_id,usr_flag
</sql>
//新增
<insert id="insert" parameterType="User">
INSERT INTO sys_user
(usr_name,usr_password,usr_role_id,usr_flag)
VALUES
(
#{usrName},
#{usrPassword},
#{usrRoleId},
#{usrFlag}
);
</insert>
//删除
<delete id="delete" parameterType="java.lang.Long">
DELETE FROM sys_user
WHERE
usr_id = #{usrId} ;
</delete>
//修改
<update id="update" parameterType="User">
UPDATE sys_user
<set>
<if test="usrName!=null">usr_name =#{usrName},</if>
<if test="usrPassword!=null">usr_password =#{usrPassword},</if>
<if test="usrRoleId!=null">usr_role_id =#{usrRoleId},</if>
<if test="usrFlag!=null">usr_flag =#{usrFlag},</if>
</set>
where
usr_id=#{usrId}
</update>
//根据Id查询
<select id="get" parameterType="java.lang.Long" resultMap="UserResultMap">
select
<include refid="column"/>
FROM sys_user
where usr_id=#{usrId}
</select>
//查询所有
<select id="findAll" resultMap="UserResultMap">
select
<include refid="column"/>
FROM sys_user
</select>
</mapper>
测试:
package com.aiweiyi.qingjing.demo;
import com.aiweiyi.qingjing.demo.mapper.UserMapper;
import com.aiweiyi.qingjing.demo.pojo.User;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DemoUserMapperApplicationTests {
@Resource
private UserMapper userMapper;
@Test
public void testInsert()throws Exception{
userMapper.insert(new User("ktjiaoyu","123456",8L,1));
// 新增
}
@Test
public void testGet(){
User user =userMapper.get(24L);
System.out.println("usrName:"+user.getUsrName());
//根据id查询
}
@Test
public void testFindAll(){
//查询所有
List<User>getAll=userMapper.findAll();
System.out.println(getAll.toString());
}
@Test
public void testUserDelete(){
//删除
userMapper.delete(16L);
}
@Test
public void testUserUpdate(){
//修改
User user= userMapper.get(24L);
// new User("ktjiaoyu1","123456",8L,1)
user.setUsrName("ktjiaoyu1");
user.setUsrFlag(1);
user.setUsrPassword("123456");
user.setUsrRoleId(8L);
userMapper.update(user);
System.out.println(user.toString());
}
}
测试效果:
因为没有在配置文件中加入日志的配置,所以没有相关的sql提示
#输出日志
logging.level.root=warn
logging.level.com.aiweiyi.qingjing.demo.mapper=trace
logging.pattern.console=%p%m%n
测试效果:
- logging.level.root=warn 将其他日志输出级别设置为warn。
- logging.level.com.aiweiyi.qingjing.demo.mapper=trace 指定mapper包下的操作日志级别为trace,比debug更加的详细。
- logging.pattern.console=%p%m%n日志输出格式,不输出日期时间,再次执行测试,控制台输出。
这样Spring Boot和MyBatis基于XML配置版集成完成
注解配置版集成
自动Java1.5开始引入了注解,注解便被广泛应用在各种开源软件中,使用
注解大大降低了系统中的配置项,让变成变得更加优雅,MyBatis是一个XML驱动的框架,注解提供了一种简单的方式来实现简单映射语句,而不会引入大量的开销。
注解版的开发方式XML版本相同,只有在构建SQL方面有所区别
application.properties配置:
#配置tomcat的端口号默认是8080
server.port=8080
#配置数据源相关新
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/crm?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
#配置JPA(Hibernate)相关信息
#spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
#配置mybatis
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.type-aliases-package=com.aiweiyi.qingjing.demo.pojo
#输出日志
logging.level.root=warn
logging.level.com.aiweiyi.qingjing.demo.mapper=trace
logging.pattern.console=%p%m%n
使用注解版就不需要使用Mapper.xml的文件了
UserMapper接口:
package com.aiweiyi.qingjing.demo.mapper;
import com.aiweiyi.qingjing.demo.pojo.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
@Insert("INSERT INTO sys_user(usr_name,usr_password,usr_role_id,usr_flag)" +
"VALUES(#{usrName},#{usrPassword},#{usrRoleId},#{usrFlag})")
public void insert(User user);
@Delete("DELETE FROM sys_user WHERE usr_id = #{usrId} ")
public void delete(Long id);
@Update("<script>" +
"UPDATE sys_user" +
"<set>" +
"<if test=\"usrName!=null\">usr_name =#{usrName},</if>" +
"<if test=\"usrPassword!=null\">usr_password =#{usrPassword},</if>" +
"<if test=\"usrRoleId!=null\">usr_role_id =#{usrRoleId},</if>" +
"<if test=\"usrFlag!=null\">usr_flag =#{usrFlag},</if>" +
"</set>" +
"where usr_id=#{usrId}" +
"</script>")
public void update(User user);
@Select(" select usr_id,usr_name,usr_password,usr_role_id,usr_flag FROM sys_user where usr_id=#{usrId}")
@Results({
@Result(column = "usr_id", property = "usrId"),
@Result(column = "usr_name", property = "usrName"),
@Result(column = "usr_password", property = "usrPassword"),
@Result(column = "usr_role_id", property = "usrRoleId"),
@Result(column = "usr_flag", property = "usrFlag")
})
public User get(Long id);
@Select("select usr_id,usr_name,usr_password,usr_role_id,usr_flag from sys_user")
@Results({
@Result(column = "usr_id", property = "usrId"),
@Result(column = "usr_name", property = "usrName"),
@Result(column = "usr_password", property = "usrPassword"),
@Result(column = "usr_role_id", property = "usrRoleId"),
@Result(column = "usr_flag", property = "usrFlag")
})
public List<User> findAll();
}
测试效果:
结果都是一样的,我个人觉得使用Mapper.xml的配置文件的话比较好一些!!!
仅做参考
总结一下:使用JPA的确方便了很多,不用我们像以前一样关联一个数据库使用内连接或者是子查询,通过注解的方法来标注,的确方便了很多,然后我们使用了MyBatis集成了Spring Boot的,也跟以前一样,不过少去了很多的配置文件,挺好的,还有注解配置版集成,把Mapper.xml的代码写在了UserMapper.java接口中我觉得不适于一些中、大型的项目,但是我个人建议的话使用Mapper.xml配置文件话比较好一些,个人建议仅做参考,每个人的习惯和风格都各不相同
希望能够帮助我的真果粒们!
谢谢大家的支持