二、单表SQL操作-使用关键字拼凑方法
回顾
public interface UserRepository extends JpaRepository<User,Integer> {
User findByUsernameLike(String username);
}
@GetMapping("/user/username/{username}")
public Object findUserByUsername(@PathVariable String username){
return userRepository.findByUsernameLike("%"+username+"%");
}
1、单表sql操作—使用关键词字拼凑的方法
关键字 | 示例 | JPQL 片段 |
---|---|---|
And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
Is,Equals | 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 (parameter bound with appended %) |
EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1 (parameter bound with prepended %) |
Containing | findByFirstnameContaining | … where x.firstname like ?1 (parameter bound wrapped in %) |
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 age) | … 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) |
2、单表sql操作—使用关键词字拼凑的方法案例
2.1、相关查询题目
●查询出年龄小于等于22岁的人;
●查询出年龄在20- 22岁并且性别是男的人;
●查询出已结婚且性别是男的人;
2.2、表结构
Person
pid varchar(32)
pname varchar(255) unique
psex varchar(255)
page int(3)
getmarried boolean
2.3、注意事项
- 实体类属性名不要出现isXxx、 getXxx的名称,会导致关键字拼凑出错
- 实体类属性名中间只要出现了大写字母,就会导致数据库的字段名有下划线隔开,比如你使
用isMarried属性名,那么实体类的字段名就会变成is_ married, 这样容易导致找不到值 - 属性名类型是boolean类型的在某些数据库中会变成bit(1)类型, 其中0为false, 1为true
src/main/resources/application.properties
#mysql的配置信息
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#支持SQL 输出
spring.jpa.show-sql=true
#format 一下 SQL 进行输出
spring.jpa.properties.hibernate.format_sql=true
#自动生成开启,让表数据会自动跟随entity类的变化而变化
#spring.jpa.properties.hibernate.hbm2ddl.auto=update
#开启自动更新,若数据库没有对应的表,则生成,若有,则检查是否需要更改
spring.jpa.hibernate.ddl-auto=update
src/main/java/com/study/springdatajpademosecond/entity/Person.java
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
@Data//geter、setter、equals、hashcode以及tostring
@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;
}
src/main/java/com/study/springdatajpademosecond/entity/PersonInfo.java
public interface PersonInfo {
String getPid();
String getPname();
String getPsex();
String getPage();
String getGetmerried();
Integer getBid();
String getBname();
double getBprice();
}
src/main/java/com/study/springdatajpademosecond/repository/PersonRepository.java
import com.study.springdatajpademosecond.entity.Person;
import com.study.springdatajpademosecond.entity.PersonInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
import java.util.Map;
public interface PersonRepository extends JpaRepository<Person,String> {
//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);
}
2.3、测试
@SpringBootTest
class SpringdataJpaDemoSecondApplicationTests {
@Resource
private PersonRepository personRepository;
@Test
void contextLoads() {
//初始化表
// initPersons();
//1、查询出年龄小于等于22岁的人;
System.out.println(personRepository.findAllByPageIsLessThanEqual(22));
System.out.println("---------------------------------------------------");
//2、查询出年龄在20-22岁之间并且性别是男的人
System.out.println(personRepository.findAllByPageBetweenAndPsexEquals(20,22,"男"));
System.out.println("---------------------------------------------------");
//3、查询出已经结婚并且性别是男的人
System.out.println(personRepository.findAllByGetmarriedIsTrueAndPsexEquals("男"));
}
// 初始化数据库 加入
private void initPersons() {
List<Person> list = new ArrayList<>();
Collections.addAll(list,
Person.builder().pname("zhangsan").psex("男").page(22).getmarried(false).build(),
Person.builder().pname("lisi").psex("女").page(21).getmarried(true).build(),
Person.builder().pname("wangwu").psex("男").page(20).getmarried(false).build(),
Person.builder().pname("zhaoliu").psex("女").page(23).getmarried(true).build(),
Person.builder().pname("sunqi").psex("男").page(25).getmarried(true).build());
personRepository.saveAll(list);
}
}
三、单表SQL操作-使用关键字拼凑方法无法解决的问题及解决方法
1、造成的原因
- 实体类的属性名与表的字段名无法映射,导致关键字找不到
- CRUD操作方式比较另类或者是你不想用关键字的写法
- 涉及到了多表操作
2、解决方法
2.1、使用sql语句来书写sql
2.2、使用hql语句来书写sql
具体看文档
3、演示使用sql语句来书写sql
3.1、 实现接口
public interface PersonRepository extends JpaRepository<Person,String> {
//4、根据pname来模糊删除一个person数据
@Transactional
@Modifying
@Query(value = "delete from Person where pname like %:pname%")
void deleteByName(@Param("pname") String pname);
//5、使用HQL或者是sql来书写一个查询语句,查询出年龄在20-22岁,性别是女的人
// @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();
//6、使用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);
}
3.2、测试
private void createSqlTest() {
// personRepository.deleteByName("si");
// System.out.println(personRepository.findPerson());
personRepository.updatePerson(Person.builder().pid("402882f870e8a2cd0170e8a2d6470002").
pname("刘德华").psex("男").page(60).build());
}
四、Spring data jpa逆向工程和多表查询
1、三种形式
VO不讲解
2、Spring data jpa逆向操作
2.1、关联数据库
idea右侧 —database—±–data source —HSQLDB
url 填写jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
然后测试
2.2、逆向生成
idea 右侧的project structure—project settigns----Modules—JPA—±-选择默认
idea 左侧的persistence —
选择entity包
然后选中book 逆向生成
这时候就能生成实体类了
3、多表查询
3.1、联表查询-根据书名来查该书籍的拥有者
//7、联表查询-根据书名来查该书籍的拥有者
@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);
//测试 7、联表查询-根据书名来查该书籍的拥有者
System.out.println(personRepository.findPersonByBname("三国演义"));
3.2、联表查询-联表查询-根据用户id来查询person和book
3.2.1、创建接口形式
1、创建接口
创建PersonInfo是为了展示person和book需要展示的部分
public interface PersonInfo {
String getPid();
String getPname();
String getPsex();
String getPage();
String getGetmerried();
Integer getBid();
String getBname();
double getBprice();
}
2、具体查询
@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);
一定要使用别名
文章来源:https://www.toymoban.com/news/detail-627192.html
List<PersonInfo> allInfo = personRepository.findAllInfo("402882f870e8a2cd0170e8a2d6470002");
for (PersonInfo info:allInfo
) {
System.out.println(info.getPid()+","+info.getPname()+","+info.getPsex()+","+info.getPage()+","+info.getGetmarried()+","+
info.getBid()+","+info.getBname()+","+info.getBprice());
}
3.2.2、通过集合形式
//使用集合来接收数据-List<Map<>> System.out.println(personRepository.findAllInfo2("402882f870e8a2cd0170e8a2d6470002"));
//通过集合来接收数据-list
List<Object> allInfo1 = personRepository.findAllInfo1("402882f870e8a2cd0170e8a2d6470002");
Object[] o = (Object[])allInfo1.get(0);
System.out.println(Arrays.toString(o));
五、Query-DSL
文章来源地址https://www.toymoban.com/news/detail-627192.html
到了这里,关于数据库访问中间件--springdata-jpa的基本使用的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!