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语句的集合。