JPA SQL 的复杂查询createNamedQuery

@NamedNativeQueries({ @NamedNativeQuery( name = "getNativeNutShellInfo", //需要调用的name query = "{call cp_syslogin_web(?,?,?)}", //存储过程,返回的List字段对应的值@EntityResult此处填充实体的属性值 resultSetMapping = "ReturnColumnEntityList" ) }) @SqlResultSetMappings({ @SqlResultSetMapping( name = "ReturnColumnEntityList", entities = { @EntityResult(entityClass = NutShellInfo.class,fields={ //返回的List字段对应的值此处填充实体的属性值 @FieldResult(name = "aguid", column = "aguid"), @FieldResult(name = "user", column = "htkusr"), @FieldResult(name = "qty", column = "qtynow"), @FieldResult(name = "registered", column = "breg"), @FieldResult(name = "hint", column = "hint"), @FieldResult(name = "ver", column = "ver"), @FieldResult(name = "overQty", column = "boverqty"), @FieldResult(name = "timeout", column = "btimeout"), @FieldResult(name = "host", column = "htkwww") }) } ) }) @Entity public class NutShellInfo { @Id private String aguid; @Column(name = "htkusr") private String user; @Column(name = "qtynow") private double qty; @Column(name = "breg") private boolean registered; private String hint; private String ver; @Column(name = "boverqty") private boolean overQty; @Column(name = "btimeout") private boolean timeout; @Column(name = "htkwww") private String host; @Transient private String password;//这个字段不对数据库操作 public String getUser() { return user; } public void setUser(String user) { this.user = user; } public double getQty() { return qty; } public void setQty(double qty) { this.qty = qty; } public boolean isRegistered() { return registered; } public void setRegistered(boolean registered) { this.registered = registered; } public String getHint() { return hint; } public void setHint(String hint) { this.hint = hint; } public String getVer() { return ver; } public void setVer(String ver) { this.ver = ver; } public boolean isOverQty() { return overQty; } public void setOverQty(boolean overQty) { this.overQty = overQty; } public boolean isTimeout() { return timeout; } public void setTimeout(boolean timeout) { this.timeout = timeout; } public String getHost() { return host; } public void setHost(String host) { this.host = host; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } =========================dao========================= @Service public class PubBaseDaoImpl<T> implements PubBaseDao<T> { private EntityManager em = null; @PersistenceContext public void setEm(EntityManager em) { this.em = em; } @Override public List<T> qryNamedQuery(String nativeName,Object[] params) { Query q = em.createNamedQuery(nativeName); setQueryParams(q, params); return q.getResultList(); } public void setQueryParams(Query query, Object[] params) { try { if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { query.setParameter(i + 1, params[i]); } } } catch (Exception e) { e.printStackTrace(); } } =================services=================================== @Override public void qryInfo(String userId, String machineCode) { List<NutShellInfo> list = baseDao.qryNamedQuery("getNativeNutShellInfo",new Object[] { userId, machineCode, 0 }); } JPA 原生态SQL 的复杂查询之createNamedQuery,select * from查询，返回的List字段对应的填充实体 ============实体类========================== @NamedNativeQueries({ @NamedNativeQuery( name = "getNativeNutShellInfo", //需要调用的name query = "select * from NutShellInfo where aguid=? and htkusr=? and btimeout=? ", //存储过程,返回的List字段对应的值@EntityResult此处填充实体的属性值 resultSetMapping = "ReturnColumnEntityList" ) }) @SqlResultSetMappings({ @SqlResultSetMapping( name = "ReturnColumnEntityList", entities = { @EntityResult(entityClass = NutShellInfo.class,fields={ //返回的List字段对应的值此处填充实体的属性值 @FieldResult(name = "aguid", column = "id"), @FieldResult(name = "user", column = "htkusr"), @FieldResult(name = "qty", column = "qtynow"), @FieldResult(name = "registered", column = "breg"), @FieldResult(name = "hint", column = "hint"), @FieldResult(name = "ver", column = "ver"), @FieldResult(name = "overQty", column = "boverqty"), @FieldResult(name = "timeout", column = "btimeout"), @FieldResult(name = "host", column = "htkwww") }) } ) }) @Entity public class NutShellInfo { @Id private String aguid; @Column(name = "htkusr") private String user; @Column(name = "qtynow") private double qty; @Column(name = "breg") private boolean registered; private String hint; private String ver; @Column(name = "boverqty") private boolean overQty; @Column(name = "btimeout") private boolean timeout; @Column(name = "htkwww") private String host; @Transient private String password;//这个字段不对数据库操作 public String getUser() { return user; } public void setUser(String user) { this.user = user; } public double getQty() { return qty; } public void setQty(double qty) { this.qty = qty; } public boolean isRegistered() { return registered; } public void setRegistered(boolean registered) { this.registered = registered; } public String getHint() { return hint; } public void setHint(String hint) { this.hint = hint; } public String getVer() { return ver; } public void setVer(String ver) { this.ver = ver; } public boolean isOverQty() { return overQty; } public void setOverQty(boolean overQty) { this.overQty = overQty; } public boolean isTimeout() { return timeout; } public void setTimeout(boolean timeout) { this.timeout = timeout; } public String getHost() { return host; } public void setHost(String host) { this.host = host; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } =========================dao========================= @Service public class PubBaseDaoImpl<T> implements PubBaseDao<T> { private EntityManager em = null; @PersistenceContext public void setEm(EntityManager em) { this.em = em; } @Override public List<T> qryNamedQuery(String nativeName,Object[] params) { Query q = em.createNamedQuery(nativeName); setQueryParams(q, params); return q.getResultList(); } public void setQueryParams(Query query, Object[] params) { try { if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { query.setParameter(i + 1, params[i]); } } } catch (Exception e) { e.printStackTrace(); } } =================services=================================== @Override public void qryInfo(String userId, String machineCode) { List<NutShellInfo> list = baseDao.qryNamedQuery("getNativeNutShellInfo",new Object[] { userId, machineCode, 0 }); } JPA 原生态SQL 的复杂查询之createNamedQuery,select xxx,xxx from查询，返回的List字段对应的填充实体 ============实体类========================== @NamedNativeQueries({ @NamedNativeQuery( name = "getNativeNutShellInfo", //需要调用的name query = "select aguid,htkusr from NutShellInfo where aguid=? and htkusr=? and btimeout=? ", //存储过程,返回的List字段对应的值@EntityResult此处填充实体的属性值 resultSetMapping = "ReturnColumnEntityList" ) }) @SqlResultSetMappings({ @SqlResultSetMapping( name = "ReturnColumnEntityList", entities = {} columns = { @ColumnResult(name = "aguid"), @ColumnResult(name = "htkusr"), } ) }) @Entity public class NutShellInfo { @Id private String aguid; @Column(name = "htkusr") private String user; @Column(name = "qtynow") private double qty; @Column(name = "breg") private boolean registered; private String hint; private String ver; @Column(name = "boverqty") private boolean overQty; @Column(name = "btimeout") private boolean timeout; @Column(name = "htkwww") private String host; @Transient private String password;//这个字段不对数据库操作 public String getUser() { return user; } public void setUser(String user) { this.user = user; } public double getQty() { return qty; } public void setQty(double qty) { this.qty = qty; } public boolean isRegistered() { return registered; } public void setRegistered(boolean registered) { this.registered = registered; } public String getHint() { return hint; } public void setHint(String hint) { this.hint = hint; } public String getVer() { return ver; } public void setVer(String ver) { this.ver = ver; } public boolean isOverQty() { return overQty; } public void setOverQty(boolean overQty) { this.overQty = overQty; } public boolean isTimeout() { return timeout; } public void setTimeout(boolean timeout) { this.timeout = timeout; } public String getHost() { return host; } public void setHost(String host) { this.host = host; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } =========================dao========================= @Service public class PubBaseDaoImpl<T> implements PubBaseDao<T> { private EntityManager em = null; @PersistenceContext public void setEm(EntityManager em) { this.em = em; } @Override public List<T> qryNamedQuery(String nativeName,Object[] params) { Query q = em.createNamedQuery(nativeName); setQueryParams(q, params); return q.getResultList(); } public void setQueryParams(Query query, Object[] params) { try { if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { query.setParameter(i + 1, params[i]); } } } catch (Exception e) { e.printStackTrace(); } } =================services=================================== @Override public void qryInfo(String userId, String machineCode) { List<NutShellInfo> list = baseDao.qryNamedQuery("getNativeNutShellInfo",new Object[] { userId, machineCode, 0 }); }

 

转载于:https://www.cnblogs.com/zhanghongqiang/p/4113107.html