jpa存儲過程out
jpa調用mysql有多返回值存儲過程詳解
@Entity
@Table(name = "USER", schema = "MOBILE", uniqueConstraints = {
@UniqueConstraint(columnNames = { "LOGINID" }),
@UniqueConstraint(columnNames = { "USERACCOUNT" }) })
@NamedNativeQuery(name = "addUser", query = "{call addUser(:pPortalID,:ploginid,:ploginpasswd,:pSelfQuiz,:pSelfAnswer,:pUserEmail,:pUserAccount,:pUserPin)}", hints = { @QueryHint(name = "org.hibernate.callable", value = "true") }, resultClass = User.class)
public class User implements java.io.Serializable {
private static final long serialVersionUID = 5325039036880950119L;
private String userid;
private String loginid;
private String passwd;
//省略若干代碼
}
Ⅱ EtityManager調用存儲過程報錯
在EJB3 中你可以調用的存儲過程有兩種
1．無返回值的存儲過程。
2．返回值為ResultSet（以select 形式返回的值）的存儲過程(注意：EJB3不能調用以OUT參數返回值的存儲過程。)
下面我們看看幾種具有代表性的存儲過程的調用方法.
調用無返回值的存儲過程
首先創建一個名為AddPerson的存儲過程，他的DDL 如下（註：本例使用的是MySql資料庫）：
CREATE PROCEDURE `AddPerson`()
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
Ⅲ JPA實用手冊，即看即用
修改查詢
@Modifying
@Query("update User u set u.firstname = ?1 where u.lastname = ?2")
int setFixedFirstnameFor(String firstname, String lastname);
使用 Sort 和 JpaSort
public interface UserRepository extends JpaRepository {
@Query("select u from User u where u.lastname like ?1%")
List findByAndSort(String lastname, Sort sort);
@Query("select u.id, LENGTH(u.firstname) as fn_len from User u where u.lastname like ?1%")
List findByAsArrayAndSort(String lastname, Sort sort);
}
repo.findByAndSort("lannister", new Sort("firstname"));
repo.findByAndSort("stark", new Sort("LENGTH(firstname)"));
repo.findByAndSort("targaryen", JpaSort.unsafe("LENGTH(firstname)"));
repo.findByAsArrayAndSort("bolton", new Sort("fn_len"));
使用已命名參數
public interface UserRepository extends JpaRepository {
@Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
User findByLastnameOrFirstname(@Param("lastname") String lastname,
@Param("firstname") String firstname);
}
原生SQL分頁
public interface UserRepository extends JpaRepository {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page findByLastname(String lastname, Pageable pageable);
}
Sort sort =newSort(Sort.Direction.DESC,"createTime");//創建時間降序排序Pageable pageable =newPageRequest(pageNumber,pageSize,sort);
使用原生SQL
public interface UserRepository extends JpaRepository {
@Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
User findByEmailAddress(String emailAddress);
}
為了消除不確定性，可以在方法名內使用下劃線「_」手動定義隔斷點。
List findByAddress_ZipCode(ZipCode zipCode);
查詢方法建立
distinct flag
ignoring case
order by
public interface PersonRepository extends Repository {
List findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname);
// Enables the distinct flag for the query List (String lastname, String firstname);
List (String lastname, String firstname);
// Enabling ignoring case for an indivial property List findByLastnameIgnoreCase(String lastname);
// Enabling ignoring case for all suitable properties List (String lastname, String firstname);
// Enabling static ORDER BY for a query List (String lastname);
List (String lastname);
}
非同步查詢結果
@Async
Future findByFirstname(String firstname);
@Async
CompletableFuture findOneByFirstname(String firstname);
@Async
ListenableFuture findOneByLastname(String lastname);
Like模糊查詢
@Query(value = "select name,author,price from Book b where b.name like %:name%")
List findByNameMatch(@Param("name") String name);
In 查詢
@Query(value = "select * from trade$seek_purchase_offer where sp_id in (:spIds) and of_enuu = :enUu", nativeQuery = true)
List getSeekPurchaseOfferList(@Param("spIds") List spIds, @Param("enUu") Long enUu);
MappedSuperClass:
映射為非實體父類，該實體父類不會生成對應的數據表
@OneToOne
@Entity
@Table(name = "costume_all_id")
public class AllId extends AbstractEntity {
private static final long serialVersionUID = 1L;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "costume_member_fk")
private Member member;// 用戶表外鍵
}
@OneToMany和@ManyToOne
@Entity
@Table(name = "costume_organization")
public class Organization extends AbstractEntity {
private static final long serialVersionUID = 1L;
@Column(nullable = false, length = 50)
private String name; // 組織名稱
@OneToMany(mappedBy = "organization")
private Set departmentSet; // 部門集合
}
@Entity
@Table(name = "costume_department")
public class Department extends AbstractEntity {
private static final long serialVersionUID = 1L;
@Column(nullable = false, length = 50)
private String name; // 部門名稱
@ManyToOne(optional = false)
private Organization organization; // 組織外鍵
@ManyToMany
private Set memberSet; // 用戶表外鍵
public Organization getOrganization() {
return organization;
}
@JsonBackReference
public void setOrganization(Organization organization) {
this.organization = organization;
}
}
@ManyToMany
Entity
@Table(name = "costume_member")
public class Member extends AbstractEntity {
private static final long serialVersionUID = 1L;
@Column(nullable = false, length = 20)
private String name;
@ManyToMany
@JoinTable(joinColumns = { @JoinColumn(name = "member_id") }, inverseJoinColumns = {
@JoinColumn(name = "department_id") }) //被控方表欄位名
private Set departmentSet; // 部門表外鍵
public Set getDepartmentSet() {
return departmentSet;
}
@JsonBackReference
public void setDepartmentSet(Set departmentSet)
{
this.departmentSet = departmentSet;
}
}
HQL通過旅店名稱查詢旅店以及城市的所有信息 直接返回實體類
/**
* 關聯查詢
*
* @return
*/
@Query(value = "select new pers.zpw.domain.CityHohel(t1.name AS cityName,t2.name AS hotelName) from TCity t1 left join THotel t2 on t1.id=t2.city where t2.name =:name")
List (@Param("name") String name);
@Data
public class CityHohel {
private String cityName;
private String hotelName;
public CityHohel(String cityName, String hotelName) {
this.cityName = cityName;
this.hotelName = hotelName;
}
}
實例2
@Entity
@Table(name="orders")
public class Order {
private String orderid;
private Float amount = 0f;
private Set items = new HashSet();
@Id
@Column(length = 12)
public String getOrderid() {
return orderid;
}
public void setOrderid(String orderid) {
this.orderid = orderid;
}
@Column(nullable = false)
public Float getAmount() {
return amount;
}
public void setAmount(Float amount) {
this.amount = amount;
}
@OneToMany(cascade = { CascadeType.REFRESH, CascadeType.PERSIST,CascadeType.MERGE, CascadeType.REMOVE },mappedBy ="order") //這里配置關系，並且確定關系維護端和被維護端。mappBy表示關系被維護端，只有關系端有權去更新外鍵。這里還有注意OneToMany默認的載入方式是賴載入。當看到設置關系中最後一個單詞是Many，那麼該載入默認為懶載入
public Set getItems() {
return items;
}
public void setItems(Set items) {
this.items = items;
}
/**
*該方法用於向order中加order項
/*
public void addOrderItem(OrderItem orderItem){
orderItem.setOrder(this);//用關系維護端來維護關系
this.items.add(orderItem);
}
}
@Entity
public class OrderItem {
private Integer id;
private String proctName;
private Float sellPrice = 0f;
private Order order;
@Id
@GeneratedValue
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@Column(length = 40, nullable = false)
public String getProctName() {
return proctName;
}
public void setProctName(String proctName) {
this.proctName = proctName;
}
@Column(nullable = false)
public Float getSellPrice() {
return sellPrice;
}
public void setSellPrice(Float sellPrice) {
this.sellPrice = sellPrice;
}
@ManyToOne(cascade = {CascadeType.MERGE,CascadeType.REFRESH }, optional = true)
@JoinColumn(name="order_id")//這里設置JoinColum設置了外鍵的名字，並且orderItem是關系維護端
public Order getOrder() {
return order;
}
public void setOrder(Order order) {
this.order = order;
}
}
緩存
org.springframework.boot
spring-boot-starter-cache
@Configuration
@EnableCaching
public class CacheConfig {
}
@Cacheable
Spring 在執行 @Cacheable 標注的方法前先查看緩存中是否有數據，如果有數據，則直接返回緩存數據；若沒有數據，執行該方法並將方法返回值放進緩存。
參數： value緩存名、 key緩存鍵值、 condition滿足緩存條件、unless否決緩存條件
@Cacheable(value = "user", key = "#id")
public User findById(final Long id) {
System.out.println("cache miss, invoke find by id, id:" + id);
for (User user : users) {
if (user.getId().equals(id)) {
return user;
}
}
return null;
}
@CachePut
和 @Cacheable 類似，但會把方法的返回值放入緩存中, 主要用於數據新增和修改方法。
@CachePut(value = "user", key = "#user.id")
public User save(User user) {
users.add(user);
return user;
}
@CacheEvict
方法執行成功後會從緩存中移除相應數據。
參數： value緩存名、 key緩存鍵值、 condition滿足緩存條件、 unless否決緩存條件、 allEntries是否移除所有數據（設置為true時會移除所有緩存）
@CacheEvict(value = "user", key = "#user.id") // 移除指定key的數據
public User delete(User user) {
users.remove(user);
return user;
}
@CacheEvict(value = "user", allEntries = true) // 移除所有數據
public void deleteAll() {
users.clear();
}
spring.cache.type=none ***設置緩存無效化
集成EhCache
net.sf.ehcache
ehcache
xsi:noNamespaceSchemaLocation="ehcache.xsd">
src\main\resources/application.properties
spring.cache.ehcache.config=classpath:ehcache.xml
如果想自定義設置一些個性化參數時，通過Java Config形式配置。
@Configuration
@EnableCaching
public class CacheConfig {
@Bean
public CacheManager cacheManager() {
return new EhCacheCacheManager(ehCacheCacheManager().getObject());
}
@Bean
public EhCacheManagerFactoryBean ehCacheCacheManager() {
EhCacheManagerFactoryBean cmfb = new EhCacheManagerFactoryBean();
cmfb.setConfigLocation(new ClassPathResource("ehcache.xml"));
cmfb.setShared(true);
return cmfb;
}
}
組合CacheManager
從多個CacheManager中輪詢得到相應的Cache。
@Configuration
@EnableCaching
public class CacheConfig {
@Bean
public CacheManager compositeCacheManager(RedisTemplate redisTemplate) {
CompositeCacheManager cacheManager = new CompositeCacheManager(new ConcurrentMapCacheManager(), new SimpleCacheManager());
cacheManager.setFallbackToNoOpCache(false);
cacheManager.afterPropertiesSet();
return cacheManager;
}
Ⅳ JPA如果調用ORACLE中的函數，跟調用存儲過程不一樣哦，我把函數換成存儲過程做了，以下是調用存儲過程的
Oracle定義存儲過程和函數主要是為了解決比較復雜的功能。是SQL語句的集合。