前言
写博客总结,最近公司进新人,写了个内部文档顺便整理了一下jap的数据操作demo
正文
第一种方式:
根据客户名称查询客户,使用jpql的形式查询,配置jpql语句,使用的@Query注解
@Query(value="from Customer where custName = ?1")
public Customer findJpql(String custName);
第二种方式:
使用sql的形式查询,
Query:配置sql查询
value:sql语句
nativeQuery:查询方式
true:sql查询
false:jpql查询
@Query(value="select * from cst_customer where cust_name like ?1",nativeQuery = true)
public List<Object [] > findSql(String name);
第三种方式:
方法名的约定:
findBy: 查询
对象种的属性名称(首字母大写): 查询条件
例如:数据库中的字段 cust_name 方法名:findByCustName
默认情况: 默认使用的是等于的方式查询
列举两种种特殊情况:
1.findBy + 属性名称 + “查询方式(Like | isnull)”
方法名:findByCustNameLike
2.多条件查询
findBy + 属性名 + “查询方式” + “多条件的连接符(and|or)” + 属性名 + “查询方式”
第四种方式:
使用字符串进行拼接的方式,一定要注意引用的EntityManager的类
package com.demo.dao.imp;
import com.demo.dao.IFindByStr;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.persistence.EntityManager;
import java.math.BigInteger;
import java.util.List;
@Service
public class FindByStr implements IFindByStr {
@Autowired
private EntityManager entityManager;
@Override
public List findStr(){
//查询语句string拼接结果
StringBuffer sql = new StringBuffer("SELECT * " +"FROM cst_customer " +"where 1=1 ");
//转换数据格式
String sqlStr = sql.toString();
//字符串传入到Query中
javax.persistence.Query query = entityManager.createNativeQuery(sqlStr);
//查询数据库
List list = query.getResultList();
return list;
}
}
下边就是贴的代码了
整体的结构是这样的,两个接口,一个实体类,一个实现类
package com.demo.dao;
import java.util.List;
public interface IFindByStr {
//字符串拼接sql
public List findStr();
}
package com.demo.dao;
import com.demo.domain.Customer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import java.math.BigInteger;
import java.util.List;
public interface CustomerDao extends JpaRepository<Customer,Long> ,JpaSpecificationExecutor<Customer> {
@Query(value="from Customer where custName = ?1")
public Customer findJpql(String custName);
/**
* sql :update cst_customer set cust_name = ? where cust_id = ?
* jpql : update Customer set custName = ? where custId = ?
*/
@Query(value = " update Customer set custName = ?2 where custId = ?1 ")
@Modifying
public void updateCustomer(long custId, String custName);
@Query(value="select * from cst_customer where cust_name like ?1",nativeQuery = true)
public List<Object [] > findSql(String name);
public Customer findByCustName(String custName);
public List<Customer> findByCustNameLike(String custName);
//使用客户名称模糊匹配和客户所属行业精准匹配的查询
public Customer findByCustNameLikeAndCustIndustry(String custName, String custIndustry);
}
package com.demo.domain;
import javax.persistence.*;
@Entity
@Table(name="cst_customer")
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="cust_id")
private Long custId;
@Column(name="cust_address")
private String custAddress;
@Column(name="cust_industry")
private String custIndustry;
@Column(name="cust_level")
private String custLevel;
@Column(name="cust_name")
private String custName;
@Column(name="cust_phone")
private String custPhone;
@Column(name="cust_source")
private String custSource;
public Long getCustId() {
return custId;
}
public void setCustId(Long custId) {
this.custId = custId;
}
public String getCustAddress() {
return custAddress;
}
public void setCustAddress(String custAddress) {
this.custAddress = custAddress;
}
public String getCustIndustry() {
return custIndustry;
}
public void setCustIndustry(String custIndustry) {
this.custIndustry = custIndustry;
}
public String getCustLevel() {
return custLevel;
}
public void setCustLevel(String custLevel) {
this.custLevel = custLevel;
}
public String getCustName() {
return custName;
}
public void setCustName(String custName) {
this.custName = custName;
}
public String getCustPhone() {
return custPhone;
}
public void setCustPhone(String custPhone) {
this.custPhone = custPhone;
}
public String getCustSource() {
return custSource;
}
public void setCustSource(String custSource) {
this.custSource = custSource;
}
@Override
public String toString() {
return "Customer{" +
"custId=" + custId +
", custAddress='" + custAddress + '\'' +
", custIndustry='" + custIndustry + '\'' +
", custLevel='" + custLevel + '\'' +
", custName='" + custName + '\'' +
", custPhone='" + custPhone + '\'' +
", custSource='" + custSource + '\'' +
'}';
}
}
结束
常用的大概就这四种方式,当然还有其他的一些方法,不妨大家自己去写一写。四种方式的优略都是什么呢?欢迎大家积极评价讨论。