# demo-jpa

**Repository Path**: cugjack/demo-jpa

## Basic Information

- **Project Name**: demo-jpa
- **Description**: 测试JPA的各项API和配置
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2023-05-13
- **Last Updated**: 2023-05-13

## Categories & Tags

**Categories**: Uncategorized

**Tags**: demo

## README

# JPA
## 引入方式
```
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.44</version>
</dependency>
```

## 创建实体类
```
@Data
@Table(name = "electron_url")
@Entity
public class ElectronUrl {

    @Id
    private int id;
    
    private String url;

    @Column(name = "title")
    private String title;
}
```

## 创建dao
```
public interface ElectronUrlDao extends JpaRepository<ElectronUrl, Integer> {
    @Query(value = "SELECT * FROM electron_url WHERE url LIKE CONCAT('%',:ip,'%')", nativeQuery = true)
    List<ElectronUrl> findByUrlLike(@Param("ip") String ip);
}
```
## 使用
### 1.使用JPA原生的函数
#### findAll
(1) findAll不带参数
查询出所有的记录
(2) findAll(Sort var1) 排序
```
public List<ElectronUrl> findAllBySort() {
    // 按照某个字段倒排
    Sort sort = new Sort(Sort.Direction.DESC, "id");
    return electronUrlDao.findAll(sort);
}
```
(3) Page<T> findAll(Pageable var1) 分页
```
 public List<ElectronUrl> findAllByPage() {
    // page 默认从0开始，1 则表示第二页
    Pageable pageable = PageRequest.of(1, 2);
    Page<ElectronUrl> page = electronUrlDao.findAll(pageable);
    int totalPages = page.getTotalPages();
    long totalElements = page.getTotalElements();
    List<ElectronUrl> electronUrlList = page.getContent();
    int number = page.getNumber();
    int size = page.getSize();
    log.info("totalPages:{}", totalPages);
    log.info("totalElements:{}", totalElements);
    log.info("number:{}", number);
    log.info("size:{}", size);
    log.info("electronUrlList:{}", electronUrlList);
    return electronUrlList;
}
```
### save
插入比较简单，没什么好记录的
### delete
删除就更简单了
### update
待完善
## 使用原生SQL语句
### 冒号
```
@Query(value = "SELECT * FROM electron_url WHERE title = :title", nativeQuery = true)
List<ElectronUrl> findByTitleSql(String title);
```
### 问号
```
@Query(value = "SELECT * FROM electron_url WHERE id = ?1", nativeQuery = true)
List<ElectronUrl> findByIdSql(Integer id);
```
### spEL表达式
```
@Query(value = "SELECT * FROM electron_url WHERE id = :#{#electronUrl.id} OR title = :#{#electronUrl.title}", nativeQuery = true)
List<ElectronUrl> findByEntitySql(ElectronUrl electronUrl);
```
### 带引号
带引号的时候，是无法直接使用冒号或者问号的，需要使用函数 CONCAT

例如，使用 `like` 的时候
```
@Query(value = "SELECT * FROM electron_url WHERE url LIKE CONCAT('%',:ip,'%')", nativeQuery = true)
List<ElectronUrl> findByUrlLike(@Param("ip") String ip);
```
### SQL 中的 IN
IN函数需要使用集合，所以参数就传入集合
```
@Query(value = "SELECT * FROM electron_url WHERE id in (?1)", nativeQuery = true)
List<ElectronUrl> findByIdsSql(List<Integer> idList);
```