public interface OrderRepository extends JpaRepository<MyOrder, Long>, JpaSpecificationExecutor {
//根据方法名生产sql，开头要由findBy开头，驼峰法必须
List findByCode(String code);
List findByTotalGreaterThanEqual(BigDecimal total);
List findByCodeAndTotalGreaterThanEqual(String code, BigDecimal total);

//JPQL语句， 用的都是类名和属性名
@Query("from MyOrder where code like %?1%")
List<MyOrder> selectOrderByCode(String code);
@Query("from MyOrder where code like %?1% and total > ?2") //?+数字 标记参数对应关系
List<MyOrder> selectOrderByCodeAndTotal(String code, BigDecimal total);

//原始sql nativeQuery 指定为true
@Query(value = "select * from my_order where code like %?1% and total > ?2", nativeQuery=true)
List<MyOrder> selectOrderByCodeAndTotalBySql(String code, BigDecimal total);

//param 注解标识参数
@Query(value = "select * from my_order where code like %:code% and total > :total", nativeQuery=true)
List<MyOrder> selectOrderByCodeAndTotalByParam(@Param("code") String code, @Param("total") BigDecimal total);

//修改方法
@Modifying
@Query("update MyOrder set total=?2 where id=?1")
int updateTotalById(Long id, BigDecimal total);

//删除方法
@Modifying
@Query("delete from MyOrder where id=:id")
int deleteOrderById(@Param("id") Long id);

}

public interface CustomerRepository extends JpaRepository<Customer, Long> {
}

@SpringBootTest
public class JPATest {
@Resource
private CustomerRepository customerRepository;

@Resource
private OrderRepository orderRepository;

@Test
public void testJPA() {
    Customer customer = new Customer("xiao","wang");

    customerRepository.save(customer);
}

@Test
public void insertOrder() {
    MyOrder order1 = new MyOrder();
    order1.setCode("0001");
    order1.setCId(1L);
    order1.setTotal(new BigDecimal("100"));
    orderRepository.save(order1);

    MyOrder order2 = new MyOrder();
    order2.setCode("0002");
    order2.setCId(1L);
    order2.setTotal(new BigDecimal("101"));
    orderRepository.save(order2);

    MyOrder order3 = new MyOrder();
    order3.setCode("0003");
    order3.setCId(1L);
    order3.setTotal(new BigDecimal("103"));
    orderRepository.save(order3);
}

@Test
public void testOrderItemSort() {
    Sort sort = Sort.by(Sort.Direction.DESC, "id");
    List<MyOrder> orders = orderRepository.findAll(sort);

    orders.forEach(item ->{
        System.out.println(item.getId() + "------" + item.getCode());
    });
}

@Test
public void testOrderPage() {
    PageRequest pageRequest = PageRequest.of(0,2, Sort.Direction.DESC,"id");
    Page<MyOrder> orders = orderRepository.findAll(pageRequest);


    orders.get().forEach(item -> {
        System.out.println(item.getCode() + "-----" +   orders.getTotalPages());
    });
}

@Test
public void testOrderMutilCondition() {
    MyOrder order1 = new MyOrder();
    order1.setCode("000");
    order1.setCId(1L);
    order1.setTotal(new BigDecimal("100"));
    List<MyOrder> orders = orderRepository.findAll(new Specification<MyOrder>() {
        @Override
        public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
            //获取条件对象
            Predicate predicate = criteriaBuilder.conjunction();
            //判断查询对象是否为空
            //like
            if(!ObjectUtils.isEmpty(order1.getCode())) {
                predicate.getExpressions().add(criteriaBuilder.like(root.get("code"),"%"+order1.getCode()+"%"));
            }
            //more than
            if(!ObjectUtils.isEmpty(order1.getTotal())) {
                predicate.getExpressions().add(criteriaBuilder.ge(root.get("total"),101));
            }

            return predicate;
        }
      }
    );

    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });

}

@Test
public void testFindByCode() {
    List<MyOrder> orders = orderRepository.findByCode("0002");
    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });
}

@Test
public void testFindByTotalGreaterThanEqual() {
    List<MyOrder> orders = orderRepository.findByTotalGreaterThanEqual(new BigDecimal("101"));
    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });
}

@Test
public void testFindByCodeAndTotalGreaterThanEqual() {
    List<MyOrder> orders = orderRepository.findByCodeAndTotalGreaterThanEqual("0003",new BigDecimal("101"));
    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });
}

@Test
public void testFindOrderByCodeWithQueryAnnotation() {
    List<MyOrder> orders = orderRepository.selectOrderByCode("000");
    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });
}

@Test
public void testSelectOrderByCodeAndTotal() {
    List<MyOrder> orders = orderRepository.selectOrderByCodeAndTotal("000", new BigDecimal("101"));
    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });
}

@Test
public void testSelectOrderByCodeAndTotalBySql() {
    List<MyOrder> orders = orderRepository.selectOrderByCodeAndTotalBySql("000", new BigDecimal("101"));
    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });
}

@Test
public void testSelectOrderByCodeAndTotalByParam() {
    List<MyOrder> orders = orderRepository.selectOrderByCodeAndTotalByParam("000", new BigDecimal("101"));
    orders.forEach(item -> {
        System.out.println(item.getCode() + "-----" +   item.getTotal());
    });
}

@Test
@Transactional
public void testUpdateTotalById() {
    int result = orderRepository.updateTotalById(5L, new BigDecimal("99"));
    System.out.println("result-----" +   result);
}

@Test
@Transactional
public void testDeleteOrderById() {
    int result = orderRepository.deleteOrderById(5L);
    System.out.println("result-----" +   result);
}

}

@Entity //与数据库表对应
@Data
@AllArgsConstructor
@Table(name = “customer”) //对应的表名，与类名一致时候可以不写
@NamedQuery(name=“Customer.findByFirstName”,query = “select c from Customer c where c.firstName = ?1”)
public class Customer {
@Id//标记为主键字段
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = “s_customer_id_seq”)
@SequenceGenerator(sequenceName = “customer_seq”, name=“s_customer_id_seq”, allocationSize = 1)
private Long id;

@Column(name="first_name",columnDefinition = "varchar(20)",nullable = false) //驼峰命名法数据库中，会自动生产带下划线的字段
private String firstName;
private String lastName;

@Transient  //表明字段不添加到数据库表中
private int gender;

//一对多，一个客户对应多个订单，关联的字段是订单里的cId字段
@OneToMany
@JoinColumn(name = "cId")//表示对应子表的关联外键，如果不使用这个注解则需要创建中间表
private List<MyOrder> myOrders;

public Customer(String firstName, String lastName) {
    this.firstName = firstName;
    this.lastName = lastName;
}


@Override
public String toString() {
    return String.format(
            "Customer[id=%d, firstName='%s', lastName='%s']",
            id, firstName, lastName);
}

}

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MyOrder {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = “s_order_id_seq”)
@SequenceGenerator(sequenceName = “order_seq”, name=“s_order_id_seq”, allocationSize = 1)
private Long id;
private String code;
private Long cId;
private BigDecimal total;

//实体映射重复列必须设置：insertable = false,updatable = false
//@OneToOne
//@JoinColumn(name = "id", insertable = false, updatable = false)
//private Customer customer;

@Override
public String toString() {
    return "MyOrder{" +
            "id=" + id +
            ", code='" + code + '\'' +
            ", cId=" + cId +
            ", total=" + total +
           // ", customer=" + customer +
            '}';
}

}