compile(
            //hibernate
            'org.springframework.boot:spring-boot-starter-data-jpa',
            'org.hibernate:hibernate-java8',
            'mysql:mysql-connector-java',
            //springmvc
            'org.springframework.boot:spring-boot-starter-web',
            "com.fasterxml.jackson.datatype:jackson-datatype-jsr310",
            "com.kingboy:common:$commonVersion"
    )

server:
  port: 8080
spring:
  application:
    name: kingboy-springboot-data
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql:///jpa?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
    schema: classpath:shema.sql
    data: classpath:data.sql
  jpa:
    hibernate:
      ddl-auto: none

CREATE TABLE IF NOT EXISTS `user` (
  `id` bigint(20) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `birth` datetime DEFAULT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `realname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DELETE FROM `user`;
INSERT INTO `user`
VALUES
        ('1', '56', '2016-09-12 12:12:00', 'king123', '小明', 'boy'),
        ('2', '12', '1993-08-12 07:12:00', 'king123', '小南', 'kingboy'),
        ('3', '11', '1992-05-12 23:12:00', 'king123', '小孩', 'boyking'),
        ('4', '2', '2006-12-13 20:12:00', 'king123', '金子', 'kiboy'),
        ('5', '66', '2012-09-12 05:12:00', 'king123', '哈嘍小金', 'baby'),
        ('6', '12', '2000-02-13 06:12:00', 'king123', 'Baby', 'xiaohong'),
        ('7', '24', '2017-12-12 11:12:00', 'king123', '小金', 'king');

@Entity
public class User {
    @Id
    private Long id;
    private String username;
    private String realname;
    private String password;
    private Integer age;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm")
    private LocalDateTime birth;
    //Setter Getter ...
}

public interface UserRepository extends JpaRepository<User, Long> {
    
}

public interface UserRepository extends JpaRepository<User, Long> {
    /**
     * 查詢用戶名為username的用戶
     * @param username
     * @return
     */
    List<User> findByUsername(String username);
    /**
     * 查詢用戶名為username的用戶,帶分頁
     * @param username
     * @return
     */
    List<User> findByUsername(String username, Pageable pageable);
    /**
     * 查詢用戶名為username并且真實(shí)姓名為realname的用戶
     * @param username
     * @param realname
     */
    List<User> findByUsernameAndRealname(String username, String realname);
    /**
     * 查詢用戶名為username或者姓名為realname的用戶
     */
    List<User> findByUsernameOrRealname(String username, String realname);
    /**
     * 查詢用戶名不是username的所有用戶
     * @param username
     * @return
     */
    List<User> findByUsernameNot(String username);
    /**
     * 查詢年齡段為ageFrom到ageTo的用戶
     * @param ageFrom
     * @param ageTo
     * @return
     */
    List<User> findByAgeBetween(Integer ageFrom, Integer ageTo);
    /**
     * 查詢生日小于birthTo的用戶
     */
    List<User> findByBirthLessThan(LocalDateTime birthTo);
    /**
     * 查詢生日段大于birthFrom的用戶
     * @param birthFrom
     * @return
     */
    List<User> findByBirthGreaterThan(LocalDateTime birthFrom);
    /**
     * 查詢年齡小于或等于ageTo的用戶
     */
    List<User> findByAgeBefore(Integer ageTo);
    /**
     * 查詢年齡大于或等于ageFrom的用戶
     * @param ageFrom
     * @return
     */
    List<User> findByAgeAfter(Integer ageFrom);
    /**
     * 用戶名模糊查詢
     * @param username
     * @return
     */
    List<User> findByUsernameLike(String username);
    /**
     * 查詢以start開頭的用戶
     * @param start
     * @return
     */
    List<User> findByUsernameStartingWith(String start);
    /**
     * 查詢以end結(jié)尾的用戶
     * @return
     */
    List<User> findByUsernameEndingWith(String end);
    /**
     * 查詢用戶名包含word的用戶
     * @param word
     * @return
     */
    List<User> findByUsernameContaining(String word);
    /**
     * 查詢名字屬于usernames中的用戶
     * @param usernames
     * @return
     */
    List<User> findByUsernameIn(Collection<String> usernames);
    /**
     * 查詢名字不屬于usernames中的用戶
     * @param usernames
     * @return
     */
    List<User> findByUsernameNotIn(Collection<String> usernames);
    /**
     *最后來個(gè)復(fù)雜點(diǎn)的：查詢年齡小于ageTo,姓名以start開頭，id大于idTo的用戶，并且按照年齡倒序
     * @return
     */
    List<User> findByAgeBeforeAndUsernameStartingWithAndIdGreaterThanOrderByAgeDesc(Integer ageTo, String start, Long idTo);
}

public interface BookRepository extends ElasticsearchRepository<Book, String> {
       /**
        * 使用@Query和hql進(jìn)行查詢
        * @param name
        * @return
        */
       @Query("from User where username = :name")
       List<User> findByNameHQL(@Param(value = "name") String name, Pageable pageable);
   
       /**
        * 使用原生sql進(jìn)行查詢
        * @param name
        * @return
        */
       @Query(value = "select * from user where username = :name", nativeQuery = true)
       List<User> findByNameNative(@Param(value = "name") String name);
}