一 项目简介
演示基于方法名的查询、基于@Query的查询、分页和排序。
二 实战
1 在docker下安装Oracle XE。
安装方法参考:
https://blog.csdn.net/chengqiuming/article/details/82555481
2 新建Spring Boot项目
2.1 添加依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!--引入oracle的依赖包-->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.2.0</version>
</dependency>
<!--包含大量的Java常用统计-->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>18.0</version>
</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>
</dependencies>
2.2 安装ojdbc6.jar到本地库
E:\springboot\SourceCode\ch8_2>mvn install:install-file -DgroupId=com.oracle "-DartifactId=ojdbc6" "-Dversion=11.2.0.2.0" "-Dpackaging=jar" "-Dfile=E:\java\ojdbc6.jar"
ojdbc6被打包到本地库
3 新建一个data.sql文件,向里面加入一些测试数据
insert into person(id,name,age,address) values(hibernate_sequence.nextval,'汪云飞',32,'合肥');
insert into person(id,name,age,address) values(hibernate_sequence.nextval,'xx',31,'北京');
insert into person(id,name,age,address) values(hibernate_sequence.nextval,'yy',30,'上海');
insert into person(id,name,age,address) values(hibernate_sequence.nextval,'zz',29,'南京');
insert into person(id,name,age,address) values(hibernate_sequence.nextval,'aa',28,'武汉');
insert into person(id,name,age,address) values(hibernate_sequence.nextval,'bb',27,'合肥');
4 配置基本属性
spring.datasource.driverClassName=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc\:oracle\:thin\:@localhost\:1521\:xe
spring.datasource.username=system
spring.datasource.password=oracle
#配置jpa
#Hibernate提供了根据实体类自动维护数据库表结构的功能
#create:启动时删除上一次生成的表,并根据实体类生成表,表中的数据会被清空
#create-drop:启动时候根据实体类生成表,sessionFactory关闭时表会被删除
#update:启动时会根据实体类生成表,当实体类属性表动时,表的结构也会更新,在初期开发阶段使用此选项
#validate:启动是验证实体类和数据表是否一致,在数据结构稳定时采用此选项
#none:不采取任何手段
spring.jpa.hibernate.ddl-auto=update
#2用来设置Hibernate操作的时候在控制台显示真实的sql语句。
spring.jpa.show-sql=true
#让控制器输出的json字符串格式更美观。
spring.jackson.serialization.indent_output=true
5 定义映射实体类
/*
没有使用@Table(实体类映射表名)、@Column(属性映射字段名)注解。这是因为我们是采用
正向工程通过实体类生成表结构,而不是逆向工程从表结构生成数据库。
@Column是用来映射属性名和字段名,不注解的时候,hibernate会自动根据属性名生成数据库表的
字段名。如属性名name映射成字段NAME;多字母属性如testName会自动映射为TEST_NAME.表名
的映射规则也是这样
*/
package com.wisely.domain;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
@Entity //指定这是一个和数据表映射的实体类
@NamedQuery(name = "Person.withNameAndAddressNamedQuery",
query = "select p from Person p where p.name=?1 and address=?2")
public class Person {
@Id //指明这个属性映射为数据库的主键
@GeneratedValue //注解默认使用主键生成方式为自增,Hibernate为我们自动生成一个名为HIBERBATE_SEQUENCE的序列。
private Long id;
private String name;
private Integer age;
private String address;
public Person() {
super();
}
public Person(Long id, String name, Integer age, String address) {
super();
this.id = id;
this.name = name;
this.age = age;
this.address = address;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
6 定义数据访问接口
package com.wisely.dao;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.wisely.domain.Person;
public interface PersonRepository extends JpaRepository<Person, Long> {
List<Person> findByAddress(String address); //使用方法名查询,接收一个name参数,返回值为列表
Person findByNameAndAddress(String name,String address); //使用方法名查询,接受name和address,返回值为单个对象
//使用@Query查询,参数按照名称绑定
@Query("select p from Person p where p.name= :name and p.address= :address")
Person withNameAndAddressQuery(@Param("name")String name,@Param("address")String address);
//使用@NamedQuery查询,该查询在实体类中有定义
Person withNameAndAddressNamedQuery(String name,String address);
}
7 控制器编写
package com.wisely.web;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.wisely.dao.PersonRepository;
import com.wisely.domain.Person;
@RestController
public class DataController {
//1 Spring Data JPA已自动为你注册bean,所以可自动注入
@Autowired
PersonRepository personRepository;
/**
* 保存
* save支持批量保存:<S extends T> Iterable<S> save(Iterable<S> entities);
*
* 删除:
* 删除支持使用id,对象以,批量删除及删除全部:
* void delete(ID id);
* void delete(T entity);
* void delete(Iterable<? extends T> entities);
* void deleteAll();
*
*/
@RequestMapping("/save")
public Person save(String name,String address,Integer age){
Person p = personRepository.save(new Person(null, name, age, address));
return p;
}
/**
* 测试findByAddress
*/
@RequestMapping("/q1")
public List<Person> q1(String address){
List<Person> people = personRepository.findByAddress(address);
return people;
}
/**
* 测试findByNameAndAddress
*/
@RequestMapping("/q2")
public Person q2(String name,String address){
Person people = personRepository.findByNameAndAddress(name, address);
return people;
}
/**
* 测试withNameAndAddressQuery
*/
@RequestMapping("/q3")
public Person q3(String name,String address){
Person p = personRepository.withNameAndAddressQuery(name, address);
return p;
}
/**
* 测试withNameAndAddressNamedQuery
*/
@RequestMapping("/q4")
public Person q4(String name,String address){
Person p = personRepository.withNameAndAddressNamedQuery(name, address);
return p;
}
/**
* 测试排序
*/
@RequestMapping("/sort")
public List<Person> sort(){
List<Person> people = personRepository.findAll(new Sort(Direction.ASC,"age"));
return people;
}
/**
* 测试分页
*/
@RequestMapping("/page")
public Page<Person> page(){
Page<Person> pagePeople = personRepository.findAll(new PageRequest(1, 2));
return pagePeople;
}
}
三 测试
1 数据表插入成功
2 浏览器输入http://localhost:8080/save?name=cakin&address=xian&age=36
3 浏览器输入http://localhost:8080/q1?address=合肥
4 浏览器输入http://localhost:8080/q2?address=合肥&name=汪云飞
5 浏览器输入 http://localhost:8080/q3?address=合肥&name=汪云飞
6 浏览器输入http://localhost:8080/sort
7 浏览器输入http://localhost:8080/page