# boot-jpa

**Repository Path**: li90hou/boot-jpa

## Basic Information

- **Project Name**: boot-jpa
- **Description**: Spring Boot 使用 Spring Data JPA 操作数据库
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2018-03-23
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 1 配置
## 1.1 Maven 依赖
```java
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
```
## 1.2配置属性文件 application.properties
```java
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://hostname:3306/dbName?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.username=youname
spring.datasource.password=youpassword

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
```
## *或使用 application.yml*
```java
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://hostname:3306/dbName?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: youname
    password: youpassword
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
```
# 2 编写类
## 2.1 实体类

```java
@Entity
@Table(name = "tb_user") 							// 数据库表名
public class User {
	@Id                                             // 主键
	@GeneratedValue(strategy = GenerationType.AUTO) // 自增
	private Long id;
	private String username;
	private String password;
	
	// setter and getter
}
```
## 2.2 Dao 层
 编写接口继承 JpaRepository 接口，此接口中提供了常用的 CRUD 方法，可以直接调用
```java
public interface UserDao extends JpaRepository<User, Long>{
	// 自定义方法
}
```
## 2.3 Service 层
注入 UserDao
```java
@Service
public class UserService {
	@Resource
	private UserDao userDao;
	
}
```
# 3 查询方式
> 增、删、改操作要使用事务(在Service 层或 Dao 层相应方法上添加 @Transactional 注解)，否则会报如下错：
> javax.persistence.TransactionRequiredException: Executing an update/delete query
## 3.1 直接调用接口 JpaRepository 的方法生成 SQL
```java
// 保存用户信息
userDao.save(user);
// 查获取所有用户信息列表
userDao.findAll();
``` 
## 3.2 使用 @Query 注解自定义生成 SQL
```java
/**
 * 在 UserDao 中添加自定义方法
 * User 为类名，将关联到 数据库表 tb_user
 */
@Query(value = "SELECT u FROM User u")
List<User> listUser();

/**
 * 对于增、删、改类型 SQL 需要添加 @Modifying 
 * ?1: 取方法参数列表中第一个参数的值，如果方法参数有多个，?1 ?2...
 */
@Modifying
@Query(value = "DELETE FROM User WHERE id = ?1")
int removeUserByMinId(Long id);

/**
 * 多个参数
 * ?1 -> id
 * ?2 -> username
 * ?3 -> newPasswd
 */
@Modifying
@Query(value = "UPDATE User SET password = ?3 WHERE id = ?1 OR username = ?2")
void updateUserPassword(Long id, String username, String newPasswd);

/**
 * 使用命名参数，通过  ":param" 的形式引用
 */
@Modifying
@Query(value = "UPDATE User SET password = :pwd WHERE id = :id OR username = :name")
void updateUserPassword2(@Param("id") Long id, 
					@Param("name") String username, 
					@Param("pwd") String newPasswd);
/**
 * 引用实体类属性
 */
@Modifying
@Query(value = "UPDATE User SET password = :#{#user.password} WHERE id = :#{#user.id} OR username = :#{#user.username}")
void updateUserPassword3(@Param("user") User user);
```
## 3.3 使用关键字创建查询生成 SQL
在 UserDao 中添加方法
```java
/**
 * 符合规则的方法将自动生成 SQL
 * <==> SELECT u FROM User u WHERE username = ?1
 */
User findByUsername(String username);
```
## *可用关键字如下表*
官方：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.sample-app.finders.strategies

|关键字 |举例 |转化为 |
| :-------- | :--------| :-- |
| 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 (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| findByAgeNotIn(Collection<Age> ages)| … where x.age in ?1|
|NotIn|findByAgeNotIn(Collection<Age> 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)|