设置mybatis的配置文件路径,默认是在resources的目录
mybatis.config-location=classpath:mybatis/mybatis-config.xml
设置mybatis下面的mapper映射文件路径,默认是在resources的目录
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
设置项目中实体类包路径;
mybatis.type-aliases-package=com.study.springbootmybatis.entity
设置缓存失效
spring.thymeleaf.cache=false
展示sql语句
logging.level.com.study.springbootmybatis.repository.UsersDao=debug
@SpringBootTest
class SpringbootMybatisApplicationTests {
@Resource
UsersDao usersDao;
@Test
void contextLoads() {
System.out.println(usersDao.findAll());
}
}
public interface UsersDao {
非注解,在mapper中配置
List<Users> findAll();
Users findById(int id);
注解
@Select("select id,username,password,name from users where name like #{name}")
Users findByName(String name);
}
在properties中删除相关xml配置,删除实体类路径。
在资源中删除xml文件
在dao接口中删除非注解方法。
这里是演示一对多中的多
@Select("select * from Product where uid=#{uid}")
Product findByUid(int uid);
演示一对多中的1,这里是根据id来查users对象,那么我们可以结合ProductRepository来一次性把对应的product也查出来
property指的是在Users实体类中一对多中的多的属性名,column代表的是把users表中的那个字段作为后面查询的条件,many指的是查询是一对多还是一对一
@Select("select * from Users where id=#{id}")
@Results({
@Result(
property = "products",column = "id",many = @Many(select = "com.study.springbootmybatis.repository.ProductRepository.findByUid")
)
})
Users findUsersById(int id);
Spring Data是为了简化构建基于Spring框架应用的数据访问技术。包括对关系数据库、非关系数据库、Map-Reduce框架、云数据服务等访问支持。它为我们提供使用统一的API标准来对数据访问层进行操作,这套标准包含了CRUD(创建、获取、更新、删除)、查询、排序和分页的相关操作。
注:jdbc是数据访问规范
spring:
jpa:
show-sql: true //支持sql输出
database: mysql //数据库类型
hibernate:
ddl-auto: update //自动生成开启,让表数据会自动跟随entity类的变化而变化
properties:
hibernate:
format_sql: true //format一下SQL进行输出
database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
public interface UserRepository extends JpaRepository<TUser, Integer> {
TUser findTuserByUsernameLike(String username);
}
关键词 | 样例 | JPQL代码段 |
---|---|---|
And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
Is,Equals | findByFirstname,findByFirstnameIs,findByFirstnameEquals | … where x.firstname = ?1 |
Between | findByStartDateBetween | … where x.startDate between ?1 and ?2 |
LessThan | findByAgeLessThan | … where x.age < ?1 |
LessThanEqual | findByAgeLessThanEqual | … where x.age <= ?1 |
GreaterThan | findByAgeGreaterThan | … where x.age > ?1 |
GreaterThanEqual findByAgeGreaterThanEqual | … where x.age >= ?1 | |
After | findByStartDateAfter | … where x.startDate > ?1 |
Before | findByStartDateBefore | … where x.startDate < ?1 |
IsNull | findByAgeIsNull | … where x.age is null |
IsNotNull,NotNull | findByAge(Is)NotNull | … where x.age not null |
Like | findByFirstnameLike | … where x.firstname like ?1 |
NotLike | findByFirstnameNotLike | … where x.firstname not like ?1 |
StartingWith | findByFirstnameStartingWith | … where x.firstname like ?1(附加参数绑定%) |
EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1(与前置绑定的参数%) |
Containing | findByFirstnameContaining | … where x.firstname like ?1(包含参数绑定%) |
OrderBy | findByAgeOrderByLastnameDesc | … where x.age = ?1 order by x.lastname desc |
Not | findByLastnameNot | … where x.lastname <> ?1 |
In | findByAgeIn(Collection ages) | … where x.age in ?1 |
NotIn | findByAgeNotIn(Collection ages) | … where x.age not in ?1 |
TRUE | findByActiveTrue() | … where x.active = true |
FALSE | findByActiveFalse() | … where x.active = false |
IgnoreCase | findByFirstnameIgnoreCase | … where UPPER(x.firstame) = UPPER(?1) |
1、查询出年龄小于等于22岁的人;
List<Person> findAllByPageIsLessThanEqual(Integer age);
2、查询出年龄在20-22岁之间并且性别是男的人
List<Person> findAllByPageBetweenAndPsexEquals(Integer lowage,Integer highage,String sex);
3、查询出已经结婚并且性别是男的人
List<Person> findAllByGetmarriedIsTrueAndPsexEquals(String psex);
@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Person {
@Id
@GenericGenerator(name = "myuuid",strategy = "uuid") 主键生成策略
@GeneratedValue(generator = "myuuid")
private String pid;
@Column(unique = true)
private String pname;
@Column
private String psex;
@Column
private Integer page;
@Column
private boolean getmarried;
}
@Transactional
@Modifying
/示例1 @Query(value = "delete from Person where pname like ?1") 1代表匹配第一个参数
void deleteByName(String pname);/
或者这样↓ 推荐使用这种
/示例2 @Query(value = "delete from Person where pname like :pname")
void deleteByName(@Param("pname")String pname); /
示例3 @Query(value = "delete from Person where pname like %:pname%")
void deleteByName(@Param("pname")String pname);
默认是HQL语句,如果打sql语句需要多一个变量
/@Query(value = "select * from person where page between 20 and 22 and psex='女',",nativeQuery = true)/
@Query(value = "select p from Person p where p.page between 20 and 22 and p.psex='女'")
List<Person> findPerson();
使用SPEL表达式来完成person表的修改操作
@Modifying
@Transactional
@Query(value = "update person set pname=:#{#person.pname},psex=:#{#person.psex},page=:#{#person.page} where pid=:#{#person.pid}",nativeQuery = true)
void updatePerson(@Param("person")Person person);
利用idea右侧数据库工具连接上数据库
利用Jpa生成实体类
联表查询-根据书名来查该书籍的拥有者
@Query(value = "select p from Person p inner join Book b on p.pid=b.pid where b.bname=:bname")
Person findPersonByBname(@Param("bname") String bname);
联表查询-根据用户id来查询person和book (通过数据接口的方式)
@Query(value = "select p.pid as pid, p.pname as pname, p.psex as psex, p.getmarried as getmarried," +
"b.bid as bid,b.bname as bname, b.bprice as bprice from Person p inner join Book b on p.pid =b.pid " +
"where p.pid=:pid")
List<PersonInfo> findAllInfo(@Param("pid") String pid);
联表查询-根据用户id来查询person和book (通过简单集合的方式)
@Query(value = "select p.pid as pid, p.pname as pname, p.psex as psex, p.getmarried as getmarried," +
"b.bid as bid,b.bname as bname, b.bprice as bprice from Person p inner join Book b on p.pid =b.pid " +
"where p.pid=:pid")
List<Object> findAllInfo1(@Param("pid") String pid);
联表查询-根据用户id来查询person和book (通过嵌套集合的方式)
@Query(value = "select p.pid as pid, p.pname as pname, p.psex as psex, p.getmarried as getmarried," +
"b.bid as bid,b.bname as bname, b.bprice as bprice from Person p inner join Book b on p.pid =b.pid " +
"where p.pid=:pid")
List<Map<String, Object>> findAllInfo2(@Param("pid") String pid);
上述示例用到的数据接口
public interface PersonInfo { //数据接口
String getPid();
String getPname();
String getPsex();
String getPage();
String getGetmarried();
Integer getBid();
String getBname();
double getBprice();
}
数据接口的方式
List<PersonInfo> allInfo = personRepository.findAllInfo("40280981727e7c0401727e7c11210002");
for (PersonInfo info : allInfo) {
System.out.println(info.getPid()+","+ info.getPname() +","+ info.getPsex() +","+ info.getPage() +
","+ info.getGetmarried() +","+ info.getBid() +","+ info.getBname() +","+ info.getBprice() );
}
简单集合的方式
List<Object> allInfo1 = personRepository.findAllInfo1("40280981727e7c0401727e7c11210002");
Object[] o =(Object[]) allInfo1.get(0);
System.out.println(Arrays.toString(o));/
嵌套集合的方式 List<Map>
System.out.println(personRepository.findAllInfo2("40280981727e7c0401727e7c11210002"));
注: 多表SQL操作注意事项
- 使用数据接口的方式来接受查询的字段时要注意,必须要为要查询的字段名起别名,否则会无法取到
操作总结:
Pageable分页是SpringDataJPA默认提供的分页功能,直接继承JpaRepository就获得了该分页功能;
First | Previous | 1 | 2 | 3 | 4 | 5 | 6 | Next | Last |
---|
本文地址:https://blog.csdn.net/Mr_ZHOUZHOU/article/details/107385903
如对本文有疑问, 点击进行留言回复!!
如何在IDEA中对 hashCode()和 equals() 利用快捷键快速进行方法重写
springboot集成普罗米修斯(Prometheus)的方法
Tomcat启动springboot项目war包报错:启动子级时出错的问题
如何利用Spring的@Import扩展点与spring进行无缝整合
网友评论