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支持的关键字概述以及包含该关键字的方法实际上转换为的内容。
| 关键词 | 方法名 | JPQL代码段 |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
例子
//查询 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); }
