# springboot_jpa

**Repository Path**: CYSpringBoot/springboot_jpa

## Basic Information

- **Project Name**: springboot_jpa
- **Description**: 使用jpa访问数据库
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2019-06-26
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# springboot_jpa

## <span id='mysql'>搭建环境介绍</span>

A. 安装MySQL

>  1. 本地安装MySQL数据库 
>
>  2. 使用Docker安装MySQL数据库(熟悉Docker命令，很简单)

B. SpringBoot Jpa配置的Maven依赖，在Web(springboot_hello)服务的基础上
```xml
    <!-- mysql依赖 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <!-- jpa 依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
```

C. MySQL配置

```properties
# 服务器端口
server.port=8888
# 链接mysql jdbc驱动
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
# mysql地址
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false
# 数据库用户名
spring.datasource.username = root
# 数据库密码
spring.datasource.password = 123456

# 显示Hibernate操作数据库SQL语句
spring.jpa.show-sql=true
```

## <span id='createDao'>操作数据库Dao</span>
> [Dao] -> [UserDao.java]  UserDao.java 代码如下:
```java
package com.lc4y.springboot_jpa.Dao;

import com.lc4y.springboot_jpa.Entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

// Jpa 提供基础功能 JpaRepository 操作数据库
/*
    List<T> findAll();

    List<T> findAll(Sort var1);

    List<T> findAllById(Iterable<ID> var1);

    <S extends T> List<S> saveAll(Iterable<S> var1);

    void flush();

    <S extends T> S saveAndFlush(S var1);

    void deleteInBatch(Iterable<T> var1);

    void deleteAllInBatch();

    T getOne(ID var1);

    <S extends T> List<S> findAll(Example<S> var1);

    <S extends T> List<S> findAll(Example<S> var1, Sort var2);
 */

// <User,Integer>  < Entity(实体) , Key(数据库表主键) >
public interface UserDao extends JpaRepository <User,Integer> {

    // 自定义SQL语句查询对象
    @Query(value = "select * from user where user_name = :userName", nativeQuery = true)
    public User findUserByName(@Param("userName") String userName);
}

```

## <span id='createDao'>实体类</span>
> [Entity] -> [User.java]  User.java 代码如下:
```java
package com.lc4y.springboot_jpa.Entity;

import javax.persistence.*;

// @Entity 该类映射有数据库表
@Entity
public class User {

    // @Id 标记该字段为主键
    @Id
    // @GeneratedValue 标记改字段自增
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer userId;
    
    // 用户名
    private String userName;
    
    // 用户密码
    private String userPassword;

    // 用户年龄
    private String userAge;

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserAge() {
        return userAge;
    }

    public void setUserAge(String userAge) {
        this.userAge = userAge;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }
}

```

## <span id='createDao'>控制器操作数据库</span>
> [Controller] -> [UserController.java]  UserController.java 代码如下:
```java
package com.lc4y.springboot_jpa.Controller;

import com.lc4y.springboot_jpa.Dao.UserDao;
import com.lc4y.springboot_jpa.Entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;

@RestController
public class UserController {

    @Autowired
    private UserDao userDao;

    // 添加用户信息
    // @PostMapping POST请求
    // @RequestBody 说明参数是JSON数据
    @PostMapping(value = "/addUser")
    public String addUser(@RequestBody User user){
       User insertUser = userDao.save(user);

       if (insertUser != null) {
           return "添加成功";
       }
       
       return "添加失败";
    }

    // 根据用户ID设置用户数据
    @PostMapping(value = "/updateUser")
    public String updateUser(Integer userId, @RequestBody User user){
        // 通过jpa提供的接口方法，获取用户信息
        Optional<User> userOptional = userDao.findById(userId);

        if (userOptional.isPresent()) {
            // 获取用户信息
            User findUser = userOptional.get();

            // 获取用户信息成功, 更新用户数据
            findUser.setUserName(user.getUserName());
            findUser.setUserAge(user.getUserAge());
            findUser.setUserPassword(user.getUserPassword());

            User updateUser = userDao.save(findUser);

            if(updateUser != null) {
                return "更新成功";
            }

            return "更新失败";
        }

        return "更新失败";
    }

    // 获取所有用户信息
    @RequestMapping(value = "/getAllUsers")
    public List<User> getAllUsers() {
        // 通过jpa提供的接口方法，获取所有用户信息
        List<User> userList = userDao.findAll();

        return userList;
    }

    // 根据ID获取用户信息
    @RequestMapping(value = "/getUserInfoByID")
    public User getUserInfoByID(Integer userId) {
        // 通过jpa提供的接口方法，获取用户信息
        Optional<User> userOptional = userDao.findById(userId);

        if (userOptional.isPresent()) {
            // 获取用户信息
            User user = userOptional.get();

            return user;
        }

        return null;
    }

    // 根据userName获取用户信息
    @RequestMapping(value = "/getUserInfoByName")
    public User getUserInfoByName(String userName) {
        // 通过jpa提供的接口方法，获取用户信息
        User user = userDao.findUserByName(userName);

        if (user != null) {

            return user;
        }

        return null;
    }

    // 根据ID删除用户信息
    @RequestMapping(value = "/deleteUserByID")
    public String deleteUserByID(Integer userId) {
        // 通过jpa提供的接口方法，获取用户信息
        userDao.deleteById(userId);

        return "删除成功";
    }
}

```

## <span id='xxx'>创建数据库表</span>
> 创建数据库表SQL语句

```sql
CREATE TABLE IF NOT EXISTS `user`(
   `user_id` INT(11) NOT NULL AUTO_INCREMENT,
   `user_name` VARCHAR(40) NOT NULL,
   `user_password` VARCHAR(255) NOT NULL,
   `user_age` INT(4) NOT NULL,
   PRIMARY KEY ( `user_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
![table](https://images.gitee.com/uploads/images/2019/0626/160043_202472a9_4776933.png)
## <span id='xxx'>测试接口</span>
> 1.添加用户
> ![addUser](https://images.gitee.com/uploads/images/2019/0626/160139_14d041fe_4776933.png)
> 注意: 此处选择数据格式为JSON 所以PostMan请求之前要在Header中增加 Content-Type: application/json
> ![mysqlAddUser](https://images.gitee.com/uploads/images/2019/0626/160355_c5884362_4776933.png)
>
> 2.更新用户信息
> ![updateUser](https://images.gitee.com/uploads/images/2019/0626/160709_ae2c3a01_4776933.png)
> ![mysqlUpdateUser](https://images.gitee.com/uploads/images/2019/0626/160959_e4e8c4e3_4776933.png)
>
> 3.删除用户信息
> ![deleteUser](https://images.gitee.com/uploads/images/2019/0626/161507_980bd32a_4776933.png)
> ![mysqlDeleteUser](https://images.gitee.com/uploads/images/2019/0626/161927_55e87b82_4776933.png)
> 
> 4.获取所有用户信息
> ![getAllUsers](https://images.gitee.com/uploads/images/2019/0626/162057_4af6f77c_4776933.png)
>
> 5.自定义SQL语句查询用户信息
> ![customQuery](https://images.gitee.com/uploads/images/2019/0626/181436_93718e69_4776933.png)

## <span id='createDao'>完成</span>
> 待续...