package com.ydlclass.jpa.ydljpa.domain;

import lombok.Data;

import javax.persistence.*;

/**
 * @Created by IT李老师
 * 公主号 “元动力课堂”
 * 个人微 itlils
 */
@Data
@Entity //这是实体类
@Table(name = "user") //对应哪张表
public class User {

    @Id //这是主键
    @Column(name = "id")//数据库中的id,对应属性中的id
    @GeneratedValue(strategy = GenerationType.IDENTITY) //
    int id;

    @Column(name = "username")//数据库中的username,对应属性中的username
    String username;

    @Column(name = "password")//数据库中的password,对应属性中的password
    String password;

}

@Repository
public interface UserRepository extends JpaRepository<User,Integer> {
}

package com.ydlclass.jpa.ydljpa;

import com.ydlclass.jpa.ydljpa.dao.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class YdljpaApplicationTests {

    @Autowired
    UserRepository userRepository;

    @Test
    public void testQuery(){
        userRepository.findById(1).ifPresent(System.out::println);
    }

}

@Test
public void testAdd(){
    User user=new User();
    //user.setId(2);
    user.setUsername("itnanls");
    user.setPassword("ydl666");

    User saveUser = userRepository.save(user); //新增 返回的实体中带着实体id
    System.out.println(saveUser);
}

@Test
public void testDel(){
    userRepository.deleteById(3);
}

@Test
public void testPageable(){
    userRepository.findAll(PageRequest.of(0,2)).forEach(System.out::println);
}

//根据username查询
List<User> findAllByUsername(String str);

@Query("select 表别名 from 表名(实际为类名) 别名 where 别名.属性='itlils'")
public List<User> findUsers();

@Query("select u from User u")//从实体类，查询，而不是表
List<User> findAllUser();

@Query("select u from User u where u.username='itlils'")//从实体类，查询，而不是表。where不是列名，而是属性名
List<User> findAllUserByUsername();

@Query("select u.id from User u")//从实体类，查询，而不是表
List<Integer> findAllUser2();

@Query("select count(u) from User u")//从实体类，查询，而不是表
List<Integer> findAllUser4();

//修改数据 一定加上@Modifying 注解
@Transactional
@Modifying
@Query("update User set username=?1 where id=?2")
int updateUsernameById2(String username,Integer id);

//修改数据 一定加上@Modifying 注解
@Transactional
@Modifying
@Query(value = "update user u set u.username=?1 where u.id=?2",nativeQuery = true)
int updateUsernameById3(String username,Integer id);

@Data
@Entity   //表示这个类是一个实体类
@Table(name = "account")    //对应的数据库中表名称
public class Account {

    @GeneratedValue(strategy = GenerationType.IDENTITY)   //生成策略，这里配置为自增
    @Column(name = "id")    //对应表中id这一列
    @Id     //此属性为主键
    int id;

    @Column(name = "username")   //对应表中username这一列
    String username;

    @Column(name = "password")   //对应表中password这一列
    String password;

    //一对一
    @JoinColumn(name = "detail_id")
    @OneToOne//声明为一对一关系
    AccountDetail detail;//对象类型,也可以理解这里写哪个实体类,外键就指向哪个实体类的主键
}

@Data
@Entity
@Table(name = "account_details")
public class AccountDetail {
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)//还是设置一个自增主键
    @Id
    int id;

    @Column(name = "address")
    String address;

    @Column(name = "email")
    String email;

    @Column(name = "phone")
    String phone;

    @Column(name = "real_name")
    String realName;
}

@JoinColumn(name = "detail_id")
@OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL) //设置关联操作为ALL
AccountDetail detail;

@Autowired
AccountRepository accountRepository;

@Test
public void testFind11() {
    Account account=new Account();
    account.setUsername("itlils");
    account.setPassword("ydl666");

    AccountDetail accountDetail=new AccountDetail();
    accountDetail.setPhone("13000000000");
    accountDetail.setRealName("itlils");
    accountDetail.setAddress("cn");
    accountDetail.setEmail("123@qq.com");

    account.setDetail(accountDetail);

    Account save = accountRepository.save(account);

    System.out.println("插入之后account id为："+save.getId()+"|||accountDetail id"+save.getDetail().getId());

}

@Test
public void testFind12() {
    accountRepository.deleteById(1);
}

package com.ydlclass.jpa.ydljpa.domain;

import lombok.Data;

import javax.persistence.*;
import java.util.List;

/**
 * @Created by IT李老师
 * 公主号 “元动力课堂”
 * 个人微 itlils
 */

@Data
@Entity   //表示这个类是一个实体类
@Table(name = "author")    //对应的数据库中表名称
public class Author {
    @Column(name = "id")
    @Id // 主键
    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自增长策略
    private Long id; //id


    @Column(nullable = false, length = 20,name = "name")
    private String name;//姓名

    @OneToMany(mappedBy = "author",cascade=CascadeType.ALL,fetch=FetchType.LAZY)
    //级联保存、更新、删除、刷新;延迟加载。当删除用户，会级联删除该用户的所有文章
    //拥有mappedBy注解的实体类为关系被维护端
    //mappedBy="author"中的author是Article中的author属性
    private List<Article> articleList;//文章列表
}

package com.ydlclass.jpa.ydljpa.domain;

import lombok.Data;

import javax.persistence.*;
/**
 * @Created by IT李老师
 * 公主号 “元动力课堂”
 * 个人微 itlils
 */
@Data
@Entity   //表示这个类是一个实体类
@Table(name = "article")    //对应的数据库中表名称
public class Article {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自增长策略
    @Column(name = "id", nullable = false)
    private Long id;


    @Column(nullable = false, length = 50) // 映射为字段，值不能为空
    private String title;

    @Lob  // 大对象，映射 MySQL 的 Long Text 类型
    @Basic(fetch = FetchType.LAZY) // 懒加载
    @Column(nullable = false) // 映射为字段，值不能为空
    private String content;//文章全文内容

    @ManyToOne(cascade={CascadeType.MERGE,CascadeType.REFRESH},optional=false)//可选属性optional=false,表示author不能为空。删除文章，不影响用户
    @JoinColumn(name="author_id")//设置在article表中的关联字段(外键)
    private Author author;//所属作者
}

@Autowired
AuthorRepository authorRepository;
@Autowired
ArticleRepository articleRepository;
@Test
public void testFind13() {
    Author author=new Author();
    author.setName("itlils");
    Author author1 = authorRepository.save(author);


    Article article1=new Article();
    article1.setTitle("1");
    article1.setContent("123");
    article1.setAuthor(author1);
    articleRepository.save(article1);


    Article article2=new Article();
    article2.setTitle("2");
    article2.setContent("22222");
    article2.setAuthor(author1);
    articleRepository.save(article2);


}

@Test
public void testFind14() {
    articleRepository.deleteById(2L);
}

package com.ydlclass.jpa.ydljpa.domain;

import lombok.Data;

import javax.persistence.*;
import java.util.List;

/**
 * @Created by IT李老师
 * 公主号 “元动力课堂”
 * 个人微 itlils
 */
@Data
@Entity //这是实体类
@Table(name = "users") //对应哪张表
public class Users {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    @Column(nullable = false, length = 20, unique = true)
    private String username; // 用户账号，用户登录时的唯一标识


    @Column(length = 100)
    private String password; // 登录时密码

    @ManyToMany
    @JoinTable(name = "user_authority",joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "authority_id"))
    //1、关系维护端，负责多对多关系的绑定和解除
    //2、@JoinTable注解的name属性指定关联表的名字，joinColumns指定外键的名字，关联到关系维护端(User)
    //3、inverseJoinColumns指定外键的名字，要关联的关系被维护端(Authority)
    //4、其实可以不使用@JoinTable注解，默认生成的关联表名称为主表表名+下划线+从表表名，
    //即表名为user_authority
    //关联到主表的外键名：主表名+下划线+主表中的主键列名,即user_id
    //关联到从表的外键名：主表中用于关联的属性名+下划线+从表的主键列名,即authority_id
    //主表就是关系维护端对应的表，从表就是关系被维护端对应的表
    private List<Authority> authorityList;

}

package com.ydlclass.jpa.ydljpa.domain;

import lombok.Data;

import javax.persistence.*;
import java.util.List;

/**
 * @Created by IT李老师
 * 公主号 “元动力课堂”
 * 个人微 itlils
 */
@Data
@Entity //这是实体类
@Table(name = "authority") //对应哪张表
public class Authority {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name; //权限名

    @ManyToMany(mappedBy = "authorityList")
    private List<Users> userList;
}

@Autowired
private UsersRepository usersRepository;
@Autowired
private AuthorityRepository authorityRepository;
@Test
public void testFind16() {
    Authority authority = new Authority();
    authority.setId(1);
    authority.setName("ROLE_ADMIN");
    authorityRepository.save(authority);
}

@Test
public void testFind17() {
    Users users = new Users();
    users.setUsername("itlils");
    users.setPassword("123456");
    Authority authority = authorityRepository.findById(1).get();
    List<Authority> authorityList = new ArrayList<>();
    authorityList.add(authority);
    users.setAuthorityList(authorityList);
    usersRepository.save(users);
}

@Test
public void testFind18() {
    usersRepository.deleteById(1L);
}