springboot JPA mysql

官方文档

https://docs.spring.io/spring-data/jpa/docs/1.11.10.RELEASE/reference/html/

常用关键字

通常，JPA的查询创建机制与Query方法中描述的一样。以下是JPA查询方法转换为的简短示例：

示例45.从方法名称创建查询

public interface UserRepository扩展了Repository <User，Long> {

　　List <User> findByEmailAddressAndLastname（String emailAddress，String lastname）;

}

我们将使用JPA标准API创建一个查询，但实质上这将转换为以下查询：select u from User u where u.emailAddress = ?1 and u.lastname = ?2。

Spring Data JPA将执行属性检查并遍历嵌套属性，如Property表达式中所述。以下是JPA支持的关键字概述以及包含该关键字的方法实际上转换为的内容。

 

表4.方法名称中支持的关键字

关键词	方法名	JPQL代码段
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstname，findByFirstnameIs，findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull	findByAgeIsNull	… where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1（附加参数绑定%）
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1（与前置绑定的参数%）
Containing	findByFirstnameContaining	… where x.firstname like ?1（包含参数绑定%）
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection<Age> ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection<Age> ages)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)

例子

//查询
public UserEntity findByUserName(String uName);

//public List<UserEntity> findByUserName(String uName);

//多条件查询
public UserEntity findByUserNameAndPassWord(String uName, String psw);

//统计
public long countByUserName(String uName);
//判断存在
public boolean existsByUserName(String uName);
//多条件判断存在
public boolean existsByUserNameAndPassWord(String uName, String psw);

//删除等效于SQL
//DELETE u FROM user_info AS u WHERE u.id=?1;
//DELET FROM user_info where id=?1;
public long deleteById(String id);
//in删除，等效于
//DELETE u FROM user_info AS u WHERE u.id IN ?1;
public int deleteByIdIn(List<String> ids)

//原生SQL
@Modifying
@Query(value = "DELETE u FROM user_info AS u WHERE u.id=?1 AND u.role not like '%root%'", nativeQuery = true)
public int deleteUser(String id);

@Modifying
@Query(value = "DELETE u FROM user_info AS u WHERE u.id IN ?1 AND u.role not like '%root%'", nativeQuery = true)
public int deleteMoreUser(List<String> ids);

起步

1.配置文件

server: 
  port: 7006
  tomcat: 
  uri-encoding :  UTF-8
spring: 
  profiles:
    include: config
  application: 
    name: ams-svc-print
  devtools:
    restart:
      enabled: true  #设置热部署启动
  datasource: 
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/ams_db?useUnicode=true&characterEncoding=utf8&useSSL=false
    username: amsuser
    password: ams2018
  jpa: 
    show-sql: true
    properties: 
      hibernate: 
        hbm2ddl: 
          auto: update  #有多个值create,none
  http: 
    multipart: 
      maxFileSize: 40Mb    #单个文件上传大小限制
      maxRequestSize: 40Mb #总上传文件大小限制
#entitymanager:
  #packagesToScan: com.entity

#服务发现
eureka: 
  client: 
    registerWithEureka: true
    fetchRegistry: true
    serviceUrl:
      defaultZone: http://admin:admin@127.0.0.1:7001/eureka/
debug: true

2.pom.xml配置，红字部分

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.test</groupId>
  <artifactId>test</artifactId>
  <version>0.0.1-SNAPSHOT</version>
   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>1.5.10.RELEASE</version>
      <relativePath/>
  </parent>

  <properties>
     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
     <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
     <java.version>1.8</java.version>
  </properties>
  
  <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-starter-parent</artifactId>
                <!--version>Dalston.SR1</version-->
                <version>Edgware.SR3</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
   </dependencyManagement>
   
  <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-devtools</artifactId>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
      </dependency>
      <!--dependency>
          <groupId>org.springframework.cloud</groupId>
          <artifactId>spring-cloud-starter-eureka</artifactId>
      </dependency-->
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-data-jpa</artifactId>
      </dependency>        
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
      </dependency>
       <!--添加文件上传进度条支持-->
      <dependency>
         <groupId>commons-io</groupId>
         <artifactId>commons-io</artifactId>
         <version>2.4</version>
      </dependency>
      <dependency>
         <groupId>commons-fileupload</groupId>
         <artifactId>commons-fileupload</artifactId>
         <version>1.3.1</version>
      </dependency>
  </dependencies>
  <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
         </plugin>
      </plugins>
  </build>
</project>

 3.实体层

@Entity
@Table(name = "orderInfo", indexes = { @Index(name = "orderNoIndex", columnList = "orderNo", unique = true) })
public class OrderEntity
{
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    private String id; // id
    private String orderNo = CommonUtil.getOrderNO(); // 订单号
    private String userName; // 用户名
    private String orderState; // 订单状态，未付款， 待发货，已发货，已签收，已完成
    @Temporal(TemporalType.TIMESTAMP)
    private Date createTime = new Date(); // 下单时间
    @Temporal(TemporalType.TIMESTAMP)
    private Date finishTime; // 订单完成时间
    private String acceptUserName; // 收件人姓名
    private String acceptUserPhone; // 收件人电话
    private String acceptUserAddress; // 收件人地址
    private double totalPrice; // 订单总价
    private double totalPayPrice; // 实际支付价格
    private boolean needIvoice; // 是否开发票
    private String note; // 备注

    public String getOrderId()
    {
        return id;
    }

    public void setOrderId(String orderId)
    {
        this.id = orderId;
    }

    public String getOrderNo()
    {
        return orderNo;
    }

    public void setOrderNo(String orderNo)
    {
        this.orderNo = orderNo;
    }

    public String getUserName()
    {
        return userName;
    }

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

    public String getOrderState()
    {
        return orderState;
    }

    public void setOrderState(String orderState)
    {
        this.orderState = orderState;
    }

    public Date getCreateTime()
    {
        return createTime;
    }

    public void setCreateTime(Date createTime)
    {
        this.createTime = createTime;
    }

    public Date getFinishTime()
    {
        return finishTime;
    }

    public void setFinishTime(Date finishTime)
    {
        this.finishTime = finishTime;
    }

    public String getAcceptUserName()
    {
        return acceptUserName;
    }

    public void setAcceptUserName(String acceptUserName)
    {
        this.acceptUserName = acceptUserName;
    }

    public String getAcceptUserPhone()
    {
        return acceptUserPhone;
    }

    public void setAcceptUserPhone(String acceptUserPhone)
    {
        this.acceptUserPhone = acceptUserPhone;
    }

    public String getAcceptUserAddress()
    {
        return acceptUserAddress;
    }

    public void setAcceptUserAddress(String acceptUserAddress)
    {
        this.acceptUserAddress = acceptUserAddress;
    }

    public double getTotalPrice()
    {
        return totalPrice;
    }

    public void setTotalPrice(double totalPrice)
    {
        this.totalPrice = totalPrice;
    }

    public double getTotalPayPrice()
    {
        return totalPayPrice;
    }

    public void setTotalPayPrice(double totalPayPrice)
    {
        this.totalPayPrice = totalPayPrice;
    }

    public boolean isNeedIvoice()
    {
        return needIvoice;
    }

    public void setNeedIvoice(boolean needIvoice)
    {
        this.needIvoice = needIvoice;
    }

    public String getNote()
    {
        return note;
    }

    public void setNote(String note)
    {
        this.note = note;
    }

    public OrderEntity()
    {
        super();
    }

}

4.访问层

Repository
public interface OrderRepository extends JpaRepository<OrderEntity, String>
{
    
    public OrderEntity findByOrderNo(String orderNo);
    
    public long countByOrderNo(String orderNo);
    /**
     * 更新订单的状态
     * @param orderNO
     * @param orderState
     * @return
     */
    @Modifying
    @Query(value="update orderInfo set orderState=?2 where orderNo=?1",nativeQuery=true)
    public int updateOrderPayState(String orderNO,String orderState);
    /**
     * 更新订单的支付价格
     * @param orderNO
     * @param orderState
     * @return
     */
    @Modifying
    @Query(value="update orderInfo set payPrice=?2 where orderNo=?1",nativeQuery=true)
    public int updateOrderPayPrice(String orderNO,double payPrice);
    /**
     * 更新订单的总价
     * @param orderNO
     * @param orderState
     * @return
     */
    @Modifying
    @Query(value="update orderInfo set totalPrice=?2 where orderNo=?1",nativeQuery=true)
    public int updateOrderTotalPrice(String orderNO,double totalPrice);
    
}