1、数据库连接操作
（前两天听以前的同事讲，另外一个同事因病去了，忽然觉得人好渺小，来一趟世间因为能力不行，啥也没留下。所以决定花点时间写点东西，帮一些新人入行。也以此纪念元梦兄吧。）
接前面继续已经建了五个数据库实体类，就让他回到数据库吧。
在项目中META-INF这个目录下，新建一个persistence.xml文件 ，其大体上代码如下所示，记得我们前面有个帖子已经在jboss里新建了数据源配置，忘记了的，往前翻一翻
其中配置代码如下：
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<persistence-unit name="pu" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/datasources/newsDS</jta-data-source>
<non-jta-data-source/>
<class>com.dodow.base.entity.IdEntity</class>
<class>com.dodow.base.entity.BaseEntity</class>
<class>com.dodow.test.entity.Test</class>
<class>com.dodow.common.entity.User</class>
<class>com.dodow.common.entity.Resource</class>
<class>com.dodow.common.entity.Role</class>
<class>com.dodow.common.entity.RoleResource</class>
<class>com.dodow.common.entity.UserRole</class>
<class>com.dodow.base.entity.SystemInfo</class>
<class>com.dodow.common.entity.Dict</class>
<class>com.dodow.common.entity.LaunchSysInfo</class>
<properties>
<!--
<property name="hibernate.hbm2ddl.auto" value="create"/> -->
<!-- validate 加载hibernate时，验证创建数据库表结构
create 每次加载hibernate，重新创建数据库表结构，这就是导致数据库表数据丢失的原因。
create-drop 加载hibernate时创建，退出是删除表结构
update 加载hibernate自动更新数据库结构 -->
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="false"/>
</properties>
</persistence-unit>
</persistence>
java:jboss/datasources/newsDS这个数据源名称，前面已经在jboss里配置过了，下面的注释也写清楚了，其中<!--
<property name="hibernate.hbm2ddl.auto" value="create"/> -->这一句已经被注释掉了，如果需要在数据库中按上面的配置建表，则打开这句注释即可。这样就可以自动建表了，注意建好表后按上面的方法注释掉，免得下次启去jboss的时候，又重新建表，把你的数据清空了。上面建了五个类，打去掉注释，然后启动jboss，就可以把相应的类建成表。有点啰嗦。
这里也一并说明上面的五个类都extend了BaseEntity,这个类里定义了通用的几个字段，其他业务表都需要这几个字段，比如记录什么时间创建的，什么人创建的，什么时间修改的，被谁改的，什么的，所以这几个字段集成到了BaseEntity。其相应的代码如下：
/**
*
*/
package com.dodow.base.entity;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.MappedSuperclass;
import javax.persistence.PrePersist;
import javax.persistence.PreUpdate;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;
/**
* @MappedSuperclass:一个映射的超类，它不会映射到数据库表中，为子类提供基本操作字体
*/
@MappedSuperclass
public abstract class BaseEntity extends IdEntity {
/**
*
*/
private static final long serialVersionUID = 1L;
private Date createdDatetime;
private String createdBy;
private Date updatedDatetime;
private String updatedBy;
private Boolean defunctInd=false;
/**
* TemporalType.TIMESTAMP: 2024-08-23 09:15:34.0
* TemporalType.DATE:2024-08-23
* TemporalType.TIME:09:15:34.0
*/
@Temporal(TemporalType.TIMESTAMP)
@Column(name="F_CREATED_DATETIME")
public Date getCreatedDatetime() {
return createdDatetime;
}
public void setCreatedDatetime(Date createdDatetime) {
this.createdDatetime = createdDatetime;
}
@Column(name="F_CREATED_BY")
public String getCreatedBy() {
return createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
@Temporal(TemporalType.TIMESTAMP)
@Column(name="F_UPDATEDATETIME")
public Date getUpdatedDatetime() {
return updatedDatetime;
}
public void setUpdatedDatetime(Date updatedDatetime) {
this.updatedDatetime = updatedDatetime;
}
@Column(name="F_UPDATE_BY")
public String getUpdatedBy() {
return updatedBy;
}
public void setUpdatedBy(String updatedBy) {
this.updatedBy = updatedBy;
}
@Column(name="F_DEFUNCT_IND",columnDefinition="smallint")
public Boolean isDefunctInd() {
return defunctInd;
}
@Column(name="F_DEFUNCT_IND",columnDefinition="smallint")
public Boolean getDefunctInd() {
return defunctInd;
}
public void setDefunctInd(Boolean defunctInd) {
this.defunctInd = defunctInd;
}
/**
* @transient 就是在给某个javabean上需要添加个属性，但是这个属性你又不希望给存到数据库中去，仅仅是做个临时变量，用一下
* 这个字段的生命周期仅存于调用者的内存中而不会写到磁盘里持久化
* */
@Transient
public abstract String getDisplayText();
/**
* 用于告诉编译器忽略指定的警告信息,可带的警告类型all、boxing、cast、deprecation等
* 参数说明：deprecation:抑制与使用已弃用的 API 或特性相关的警告。这有助于避免在代码中继续使用那些已不推荐使用的部分
* @PrePersist 注解用于指定在将实体对象持久化到数据库之前执行的方法。通常用于设置实体对象的初始状态或执行一些必要的操作。
*/
@SuppressWarnings("deprecation")
@PrePersist
public void initTimeStamps() {
if(createdDatetime==null) {
createdDatetime=new Date();
}
updatedDatetime=createdDatetime;
defunctInd=false;
//createdBy=JSFUtils.getSession().get("userName")!=null?JSFUtils.getSession.get().toString("userName"):null;
}
@SuppressWarnings("deprecation")
@PreUpdate
public void updateTimeStamp() {
updatedDatetime=new Date();
}
}
其中注释是瞎写的，去掉别看。这个类又扩展了IdEntity这个类，这个类定义了所有表的主键id，根据不同的数据库可以做适当调整，因为是mysql，如果你是按我前面配置的mysql，就不用改了
/**
*
*/
package com.dodow.base.entity;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.persistence.SequenceGenerator;
@MappedSuperclass
public abstract class IdEntity implements java.io.Serializable {
private static final long serialVersionUID = 1L;
private Long id;
@Id
// @GeneratedValue(strategy=GenerationType.TABLE, generator="myGen")
// @TableGenerator(name="myGen", table="ID_GEN",initialValue=5000)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SRC")
@SequenceGenerator(name="SRC", sequenceName="SRC", allocationSize=1,initialValue=19000)
// for db2 : CREATE SEQUENCE ROOT.SRC START WITH 500 INCREMENT BY 1 MAXVALUE 999999999 CYCLE NOCACHE
//@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "F_ID")
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
因为这两个类都是其他数据库实体类的父类，所以就放在com.dodow.base.entity这个包里了
2、封装数据库操作，
对外提供统一无状态的实体操作类，做为数据库操作，即封装类StatelessEntityService对，对外提供数据库操作的常用操作。
2.1、数据库的增删查改抽象类CrudEntityService，代码如下：
/**
*
*/
package com.dodow.base.service;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.dodow.base.util.Validate;
/**
*
*/
public abstract class CrudEntityService implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
protected Logger logger=LoggerFactory.getLogger(getClass());
protected EntityManager entityManager;
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
}
// ----------------------------- 基本 CRUD 操作方法 ------------------------------//
/**
* 保存新增的对象.
*/
public <T> T create(final T entity) {
Validate.notNull(entity,"entity不能为空");
entityManager.persist(entity);
logger.debug("create-create entity:{}",entity);
return entity;
}
public boolean isManaged(Object entity) {
return entityManager.contains(entity);
}
/**
* 保存修改的对象.
*/
public <T> T update(final T entity) {
Validate.notNull(entity, "entity不能为空");
entityManager.merge(entity);
logger.debug("update entity: {}", entity);
return entity;
}
/**
* 删除对象.
*
* @param entity 对象必须是session中的对象或含id属性的transient对象.
*/
public <T> void delete(final T entity) {
Validate.notNull(entity, "entity不能为空");
entityManager.remove(entity);
// logger.debug("delete entity: {}", entity);
}
/**
* 按id删除对象.
*/
public void delete(Class<?> entityClass, final Serializable id) {
Validate.notNull(id, "id不能为空");
delete(findUnique(entityClass, id));
// logger.debug("delete entity {},id is {}", entityClass.getSimpleName(), id);
}
/**
* 按id获取对象.
*/
public <T> T findUnique(Class<T> entityClass, final Serializable id) {
Validate.notNull(id, "id不能为空");
return (T) entityManager.find(entityClass, id);
}
/**
* 按条件删除对象.
*/
public void deleteByPro(final String jpql, final Map<String, ?> values) {
delete(findUnique(jpql,values));
// logger.debug("delete entity {},id is {}", entityClass.getSimpleName(), id);
}
/**
* 获取全部对象
*
* @param entityClass 实体类的类型
* @param <T>
* @return
*/
public <T> List<T> findAll(Class<T> entityClass) {
return this.findAll(entityClass, null, null);
}
/**
* 获取全部对象, 支持按属性行序.
*
* @param entityClass
* @param orderByProperty
* @param isAsc
* @param <T>
* @return
*/
public <T> List<T> findAll(Class<T> entityClass, String orderByProperty, Boolean isAsc) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = builder.createQuery(entityClass);
Root<T> entityRoot = criteriaQuery.from(entityClass);
criteriaQuery.select(entityRoot);
if (orderByProperty != null) {
if (isAsc) {
criteriaQuery.orderBy(builder.asc(entityRoot.get(orderByProperty)));
} else {
criteriaQuery.orderBy(builder.desc(entityRoot.get(orderByProperty)));
}
}
return entityManager.createQuery(criteriaQuery).getResultList();
}
// --------------------------------- 条件查询方法 -----------------------------------//
/**
* 按属性查找唯一对象, 匹配方式为相等.
*/
public <T> T findUnique(Class<T> entityClass, final String propertyName, final Object value) {
Validate.hasText(propertyName, "propertyName不能为空");
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = builder.createQuery(entityClass);
Root<T> entityRoot = criteriaQuery.from(entityClass);
criteriaQuery.select(entityRoot);
criteriaQuery.where(builder.equal(entityRoot.get(propertyName), value));
return entityManager.createQuery(criteriaQuery).getSingleResult();
}
/**
* 按JPQL查询唯一对象.
*
* @param values 数量可变的参数,按顺序绑定.
*/
public <X> X findUnique(final String jpql, final Object... values) {
return (X) createQuery(jpql, values).getSingleResult();
}
/**
* 按JPQL查询唯一对象.
*
* @param values 命名参数,按名称绑定.
*/
public <X> X findUnique(final String jpql, final Map<String, ?> values) {
return (X) createQuery(jpql, values).getSingleResult();
}
/**
* 按属性查找对象列表, 匹配方式为相等.
*/
public <T> List<T> findList(Class<T> entityClass, final String propertyName, final Object value) {
Validate.hasText(propertyName, "propertyName不能为空");
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = builder.createQuery(entityClass);
Root<T> entityRoot = criteriaQuery.from(entityClass);
criteriaQuery.select(entityRoot);
criteriaQuery.where(builder.equal(entityRoot.get(propertyName), value));
return entityManager.createQuery(criteriaQuery).getResultList();
}
/**
* 按属性查找对象列表, 匹配方式为相等.
*/
public <T> List<T> findList(Class<T> entityClass, final String propertyName, final Object value, String orderByProperty, Boolean isAsc) {
Validate.hasText(propertyName, "propertyName不能为空");
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = builder.createQuery(entityClass);
Root<T> entityRoot = criteriaQuery.from(entityClass);
criteriaQuery.select(entityRoot);
criteriaQuery.where(builder.equal(entityRoot.get(propertyName), value));
if (orderByProperty != null) {
if (isAsc) {
criteriaQuery.orderBy(builder.asc(entityRoot.get(orderByProperty)));
} else {
criteriaQuery.orderBy(builder.desc(entityRoot.get(orderByProperty)));
}
}
return entityManager.createQuery(criteriaQuery).getResultList();
}
/**
* 按JPQL查询对象列表.
*
* @param values 数量可变的参数,按顺序绑定.
*/
public <X> List<X> findList(final String jpql, final Object... values) {
return createQuery(jpql, values).getResultList();
}
/**
* 按JPQL查询对象列表.
*
* @param values 命名参数,按名称绑定.
*/
public <X> List<X> findList(final String jpql, final Map<String, ?> values) {
return createQuery(jpql, values).getResultList();
}
// ------------------------------ createQuery 方法集合 -------------------------//
/**
* 根据查询JPQL与参数列表创建Query对象.
* 与find()函数可进行更加灵活的操作.
*
* @param values 数量可变的参数,按顺序绑定.
*/
public Query createQuery(final String queryString, final Object... values) {
Validate.hasText(queryString, "queryString不能为空");
Query query = entityManager.createQuery(queryString);
if (values != null && values.length>0) {
for (int i = 1; i <= values.length; i++) {
query.setParameter(i, values[i - 1]);
}
}
return query;
}
/**
* 根据查询JPQL与参数列表创建Query对象.
* 与find()函数可进行更加灵活的操作.
*
* @param queryString jpql 查询语句
* @param values 命名参数,按名称绑定.
* @return 返回 javax.persistence.Query 对象.
*/
public Query createQuery(final String queryString, final Map<String, ?> values) {
Validate.hasText(queryString, "queryString不能为空");
Query query = entityManager.createQuery(queryString);
for (Map.Entry<String, ?> kv : values.entrySet()) {
query.setParameter(kv.getKey(), kv.getValue());
}
return query;
}
// ---------------------------- execute 及其他方法 ------------------------------ //
/**
* 执行JPQL进行批量修改/删除操作.
*
* @param values 数量可变的参数,按顺序绑定.
* @return 更新记录数.
*/
public int batchExecute(final String jpql, final Object... values) {
return createQuery(jpql, values).executeUpdate();
}
/**
* 执行JPQL进行批量修改/删除操作.
*
* @param values 命名参数,按名称绑定.
* @return 更新记录数.
*/
public int batchExecute(final String jpql, final Map<String, ?> values) {
return createQuery(jpql, values).executeUpdate();
}
}
2.2、EntityService：因为我们前台页面展示部分用的primefaces，里面专们有用于数据展示的控件，这样我们只要把分页数据返回给前台，就省去很多处理分页的麻烦的事，这也是用primefaces的原因吧。所以基于上面的基类，继续封装，当然你也可以直接用上面的类，其源代码如下：
package com.dodow.base.service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javacommon.xsqlbuilder.XsqlBuilder;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.metamodel.EntityType;
import org.primefaces.model.LazyDataModel;
import org.primefaces.model.SortOrder;
import com.dodow.base.conf.SystemConfiguration;
import com.dodow.base.entity.IdEntity;
import com.dodow.base.entity.PageInfo;
import com.dodow.base.util.MyString;
import com.dodow.base.util.ObjectUtils;
import com.dodow.base.util.PersistenceUtils;
import com.dodow.base.util.ReflectionUtils;
import com.dodow.base.util.StringUtils;
import com.dodow.base.util.Validate;
import com.dodow.base.view.PropertyFilter;
import com.dodow.base.view.PropertyFilter.PropertyType;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
/**
* JPA Service 基类.
* <p/>
* 扩展功能包括分页查询,按属性过滤条件列表查询.
* 可直接使用,也可以扩展EntityService
*
* @author
*/
public abstract class EntityService extends CrudEntityService {
private static final long serialVersionUID = 1L;
// ----------------------------------- List 查询 --------------------------------------//
/**
* <p>按 XSQL 查询，参数以 Filter(Map) 形式提供.
* xsql的写法参考：http://code.google.com/p/rapid-xsqlbuilder/，例如：
* String xsql = "select b from Book b where 1=1" + " /~ and name = {name}~/ " ;
* 位于/~ ~/之间的语句为可选部分，{name}表示变量值，当 map 中没有 name值或name为空（null或“”）时，/~ ~/之间的语句被忽略。
* filterMap 为jsf页面与Bean的传值容器，命名方法示例：EQL_id, LIKES_name等
* <p/>
* 用法例子：
* <p/>
* 页面代码：
* 书名：<h:inputText value="#{bookBean.map['EQS_name']}"/>
* 作者：<h:inputText value="#{bookBean.map['LIKES_author']}"/>
* 价格：<h:inputText value="#{bookBean.map['EQN_price']}"/>
* 页数：<h:inputText value="#{bookBean.map['EQI_pageNum']}"/>
* <p/>
* Bean类代码：
* StringBuilder sql = new StringBuilder("select name from Book where 1=1");
* sql.append(" /~ and name = {name}~/ ")
* .append(" /~ and author like {author}~/ ")
* .append(" /~ and price = {price}~/")
* .append(" /~ and pageNum = {pageNum}~/");
* <p/>
* private Map<String,Object> map = Maps.newHashMapWithExpectedSize(5);
* //set, get...
* <p/>
* List list = entityService.findXsqlList(sql.toString(), map);
* <p/>
* </p>
*
* @param xsql 基于 xsqlbuilder 样式的类SQL语句.
* @param filterMap 从页面上以Map形式传过来的属性集合.
* @return 分页的查询结果.
*/
public <X> List<X> findXsqlList(final String xsql, final Map<String, Object> filterMap) {
Validate.hasText(xsql, "xsql不能为空");
Query q = createXsqlQuery(xsql, filterMap);
return q.getResultList();
}
// ------------------------------ 计算记录条数 count ----------------------------//
/**
* 执行count查询获得本次Hql查询所能获得的对象总数.
* <p/>
* 本函数只能自动处理简单的jpql语句,复杂的jpql查询请另行编写count语句查询.
*/
protected long countHqlResult(final String jpql, final Object... values) {
String countHql = prepareCountHql(jpql);
try {
Long count = findUnique(countHql, values);
return count;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("jpql can't be auto count, jpql is:" + countHql, e);
}
}
/**
* 执行count查询获得本次Hql查询所能获得的对象总数.
* <p/>
* 本函数只能自动处理简单的jpql语句,复杂的jpql查询请另行编写count语句查询.
*/
protected Long countHqlResult(final String jpql, final Map<String, ?> values) {
String countHql = prepareCountHql(jpql);
try {
Long count = findUnique(countHql.trim(), values);
return count;
} catch (Exception e) {
throw new RuntimeException("jpql can't be auto count, jpql is:" + countHql, e);
}
}
/**
*
* @param targetClass
* @param filters
* @return
*/
protected <T> int countByPropertyFilter(final Class<T> targetClass, final List<PropertyFilter> filters) {
String countOfQuery = PersistenceUtils.buildQueryStringWithPropertyFilters(true, targetClass, filters);
Query query = entityManager.createQuery(countOfQuery);
return Integer.valueOf(String.valueOf(query.getSingleResult()));
}
/**
* select子句与order by子句会影响count查询,进行简单的排除.
*
* @param jpql JPQL 查询语句
* @return 与 jpql对应的 count 语句
*/
private String prepareCountHql(String jpql) {
boolean distinct= false;
if(jpql.toUpperCase().contains("DISTINCT")){
distinct = true;
}
String fromQl = jpql.substring(jpql.toUpperCase().indexOf("FROM"));
int pos = fromQl.toUpperCase().indexOf("ORDER BY");
if (pos != -1) {
fromQl = fromQl.substring(0, pos);
}
String[] fromQls =fromQl.split("\\s+");//多个空格
String fromQl1 ="";
if("AS".equals(fromQls[2].toUpperCase())){
fromQl1= fromQls[3];
}else{
fromQl1= fromQls[2];
}
StringBuilder countOfQuery = new StringBuilder("SELECT COUNT("+ (distinct ? "DISTINCT "+fromQl1+".id" : fromQl1+".id")+") ");
countOfQuery.append(fromQl);
return countOfQuery.toString();
}
// -------------------- LazyDataModel 动态分页 findModel() -------------------//
/**
* 按JPQL分页查询. 仅供 PrimeFaces 的 <p:dataTable> 实现分页使用。
*
* @param jpql jpql语句.
* @param values 数量可变的查询参数,按顺序绑定.
* @return 分页查询结果, 以 PrimeFaces 的LazyDataModel 形式返回.
*/
@SuppressWarnings("unchecked")
public <T extends IdEntity> LazyDataModel<T> findPage(final String jpql, final Object... values) {
PageDataModel<T> lazyModel = new PageDataModel<T>() {
@Override
public List<T> load(int first, int pageSize, String sortField,SortOrder sortOrder, Map<String, String> filters) {
// 得到总记录数
Integer count = Long.valueOf(countHqlResult(jpql, values)).intValue();
this.setRowCount(count);
// 得到查询结果
Query q = createQuery(jpql, values);
setPageParameterToQuery(q, first, pageSize);
List result = q.getResultList();
return result;
}
};
return lazyModel;
}
abstract class PageDataModel<T extends IdEntity> extends LazyDataModel<T> {
@Override
public Object getRowKey(T entity) {
return entity.getId();
}
@Override
public int getPageSize(){
return SystemConfiguration.PAGE_SIZE;
}
}
abstract class PageDataModel1<T > extends LazyDataModel<T> {
@Override
public Object getRowKey(T entity) {
return entity;
}
@Override
public int getPageSize(){
return SystemConfiguration.PAGE_SIZE;
}
}
/**
* 按JPQL分页查询. 仅供 PrimeFaces 的 <p:dataTable> 实现分页使用。
*
* @param jpql jpql语句.
* @param values 命名参数,按名称绑定.
* @return 分页查询结果, 以 PrimeFaces 的LazyDataModel 形式返回.
*/
@SuppressWarnings("unchecked")
public <T extends IdEntity> LazyDataModel<T> findPage(final String jpql, final Map<String, Object> values) {
PageDataModel<T> lazyModel = new PageDataModel<T>() {
@Override
public List<T> load(int first, int pageSize, String sortField,SortOrder sortOrder, Map<String, String> filters) {
// 得到总记录数
Integer count = Long.valueOf(countHqlResult(jpql, values)).intValue();
this.setRowCount(count);
// 得到查询结果
Query q = createQuery(jpql, values);
setPageParameterToQuery(q, first, pageSize);
List result = q.getResultList();
return result;
}
};
return lazyModel;
}
/**
* <p>按 XSQL 分页查询，参数以 Map 形式提供，仅供 PrimeFaces 的 <p:dataTable> 实现分页使用。
* xsql的写法参考：http://code.google.com/p/rapid-xsqlbuilder/，例如：
* String xsql = "select b from Book b where 1=1" + " /~ and name = {name}~/ " ;
* 位于/~ ~/之间的语句为可选部分，{name}表示变量值，当 map 中没有 name值或name为空（null或“”）时，/~ ~/之间的语句被忽略。
* xsqlFilterMap 为jsf页面与Bean的传值容器，命名方法示例：EQL_id, LIKES_name等
* <p/>
* 用法例子：
* <p/>
* 页面代码：
* 书名：<h:inputText value="#{bookBean.map['EQS_name']}"/>
* 作者：<h:inputText value="#{bookBean.map['LIKES_author']}"/>
* 价格：<h:inputText value="#{bookBean.map['EQN_price']}"/>
* 页数：<h:inputText value="#{bookBean.map['EQI_pageNum']}"/>
* <p/>
* Bean类代码：
* private Map<String,Object> map = Maps.newHashMapWithExpectedSize(5);
* // set, get ...
* <p/>
* StringBuilder sql = new StringBuilder("select name from Book where 1=1");
* sql.append(" /~ and name = {name}~/ ")
* .append(" /~ and author like {author}~/ ")
* .append(" /~ and price = {price}~/")
* .append(" /~ and pageNum = {pageNum}~/");
* <p/>
* LazyDataModel lazyModel = entityService.findModelByMap(sql.toString(), map);
* <p/>
* </p>
*
* @param xsql 基于 xsqlbuilder 样式的类SQL语句.
* @param xsqlFilterMap 从页面上以Map形式传过来的属性集合.
* @return 分页的查询结果. 以 PrimeFaces 的LazyDataModel 形式返回。
*/
@SuppressWarnings("unchecked")
public <T extends IdEntity> LazyDataModel<T> findXsqlPage(final String xsql, final Map<String, Object> xsqlFilterMap) {
//Map<String, Object> paramMap = Maps.newHashMapWithExpectedSize(5);
Map<String, Object> paramMap = new HashMap<String,Object>();
paramMap = this.buildParamMap(xsql, xsqlFilterMap);
// 构建 JPQL 语句
XsqlBuilder builder = new XsqlBuilder();
String jpql = builder.generateHql(xsql, paramMap).getXsql().toString();
return this.findPage(jpql, paramMap);
}
/**
* 按类型查询，按属性过滤条件列表分页查找对象。
* 用法例子：
* <p/>
* 页面代码：
* 书名：<h:inputText value="#{bookBean.map['EQS_name']}"/>
* 作者：<h:inputText value="#{bookBean.map['LIKES_author']}"/>
* 价格：<h:inputText value="#{bookBean.map['EQN_price']}"/>
* 页数：<h:inputText value="#{bookBean.map['EQI_pageNum']}"/>
* <p/>
* Bean类代码：
* private Map<String,Object> map = Maps.newHashMapWithExpectedSize(5);
* // set, get ...
* <p/>
* LazyDataModel lazyModel = entityService.findModelByMap(Book.class, map);
*
* @param targetClass 实体类的类型
* @param filterMap 属性过滤条件的集合，如：LIKES_name，此 name 为实体类的属性名
* @return 分页的查询结果. 以 PrimeFaces 的LazyDataModel 形式返回。
*/
public < T extends IdEntity> LazyDataModel<T> findPage(final Class<T> targetClass, final Map<String, Object> filterMap) {
List<PropertyFilter> propertyFilters = PersistenceUtils.buildPropertyFilters(filterMap);
return this.findPage(targetClass, propertyFilters);
}
public <T> LazyDataModel<T> findPage1(final Class<T> targetClass, final Map<String, Object> filterMap) {
List<PropertyFilter> propertyFilters = PersistenceUtils.buildPropertyFilters(filterMap);
return this.findPage1(targetClass, propertyFilters);
}
public <T> LazyDataModel<T> findPage(final Class<T> targetClass, final Map<String, Object> filterMap,final String[] orderFilter,final String orderType) {
List<PropertyFilter> propertyFilters = PersistenceUtils.buildPropertyFilters(filterMap);
return this.findPage(targetClass, propertyFilters,orderFilter,orderType);
}
/**
* 按类型查询，按属性过滤条件列表分页查找对象。
* 用法例子：
* <p/>
* 页面代码：
* <input type="text" id="filter_EQI_quantity" name="filter_EQI_quantity" value="#{param['filter_EQI_quantity']}"/>
* <input type="text" id="filter_LIKES_plannedStorageLoc" name="filter_LIKES_plannedStorageLoc" value="#{param['filter_LIKES_plannedStorageLoc']}"/>
* <p/>
* Bean类代码：
* HttpServletRequest request = (HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest();
* List<PropertyFilter> filters = PersistenceUtils.buildPropertyFilters(request,true);
* <p/>
* LazyDataModel lazyModel = entityService.findModelByMap(Book.class, filters);
*
* @param targetClass
* @param filters
* @param <T>
* @return
*/
@SuppressWarnings("unchecked")
public <T extends IdEntity> LazyDataModel<T> findPage(final Class<T> targetClass, final List<PropertyFilter> filters) {
PageDataModel<T> lazyModel = new PageDataModel<T>() {
@Override
public List<T> load(int first, int pageSize, String sortField,SortOrder sortOrder, Map<String, String> filter) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(targetClass);
Root<T> entity = criteriaQuery.from(targetClass);
EntityType<T> entityType = entity.getModel();
criteriaQuery.select(entity);
Predicate predicates[] = PersistenceUtils.buildPropertyFilterPredicates(targetClass, criteriaBuilder, criteriaQuery, entity, entityType, true, filters);
if (!ObjectUtils.isEmpty(predicates)) {
criteriaQuery.where(predicates);
} else {
criteriaQuery.where(criteriaBuilder.conjunction());
}
TypedQuery<T> finalCriteriaQuery = entityManager.createQuery(criteriaQuery);
// 得到总记录数
Integer count = countByPropertyFilter(targetClass, filters);
this.setRowCount(count);
int tmpStart = first > 0 ? first : 0;
int tmpMaxRows = pageSize > 0 ? pageSize : 1;
// 得到查询结果
if (tmpMaxRows >= 0) {
finalCriteriaQuery.setMaxResults(pageSize);
}
if (tmpStart >= 0) {
finalCriteriaQuery.setFirstResult(first);
}
return finalCriteriaQuery.getResultList();
}
};
lazyModel.setRowCount(1);
return lazyModel;
}
@SuppressWarnings("unchecked")
public <T> LazyDataModel<T> findPage1(final Class<T> targetClass, final List<PropertyFilter> filters) {
PageDataModel1<T> lazyModel = new PageDataModel1<T>() {
@Override
public List<T> load(int first, int pageSize, String sortField,SortOrder sortOrder, Map<String, String> filter) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(targetClass);
Root<T> entity = criteriaQuery.from(targetClass);
EntityType<T> entityType = entity.getModel();
criteriaQuery.select(entity);
Predicate predicates[] = PersistenceUtils.buildPropertyFilterPredicates(targetClass, criteriaBuilder, criteriaQuery, entity, entityType, true, filters);
if (!ObjectUtils.isEmpty(predicates)) {
criteriaQuery.where(predicates);
} else {
criteriaQuery.where(criteriaBuilder.conjunction());
}
TypedQuery<T> finalCriteriaQuery = entityManager.createQuery(criteriaQuery);
// 得到总记录数
Integer count = countByPropertyFilter(targetClass, filters);
this.setRowCount(count);
int tmpStart = first > 0 ? first : 0;
int tmpMaxRows = pageSize > 0 ? pageSize : 1;
// 得到查询结果
if (tmpMaxRows >= 0) {
finalCriteriaQuery.setMaxResults(pageSize);
}
if (tmpStart >= 0) {
finalCriteriaQuery.setFirstResult(first);
}
return finalCriteriaQuery.getResultList();
}
};
lazyModel.setRowCount(1);
return lazyModel;
}
/*
* orderType取值只能取:desc,asc
*/
@SuppressWarnings("unchecked")
public <T> LazyDataModel<T> findPage(final Class<T> targetClass, final List<PropertyFilter> filters,final String[] orderFilter,final String orderType) {
PageDataModel1<T> lazyModel = new PageDataModel1<T>() {
@Override
public List<T> load(int first, int pageSize, String sortField,SortOrder sortOrder, Map<String, String> filter) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(targetClass);
Root<T> entity = criteriaQuery.from(targetClass);
EntityType<T> entityType = entity.getModel();
criteriaQuery.select(entity);
Predicate predicates[] = PersistenceUtils.buildPropertyFilterPredicates(targetClass, criteriaBuilder, criteriaQuery, entity, entityType, true, filters);
if (!ObjectUtils.isEmpty(predicates)) {
criteriaQuery.where(predicates);
} else {
criteriaQuery.where(criteriaBuilder.conjunction());
}
List l = new ArrayList();
if(orderType.equals("desc")){
for(int i=0;i<orderFilter.length;i++){
l.add(criteriaBuilder.desc(entity.get(orderFilter[i]))) ;
}
}else if(orderType.equals("asc")){
for(int i=0;i<orderFilter.length;i++){
l.add(criteriaBuilder.asc(entity.get(orderFilter[i])));
}
}
criteriaQuery.orderBy(l);
TypedQuery<T> finalCriteriaQuery = entityManager.createQuery(criteriaQuery);
// 得到总记录数
Integer count = countByPropertyFilter(targetClass, filters);
this.setRowCount(count);
int tmpStart = first > 0 ? first : 0;
int tmpMaxRows = pageSize > 0 ? pageSize : 1;
// 得到查询结果
if (tmpMaxRows >= 0) {
finalCriteriaQuery.setMaxResults(pageSize);
}
if (tmpStart >= 0) {
finalCriteriaQuery.setFirstResult(first);
}
return finalCriteriaQuery.getResultList();
}
};
lazyModel.setRowCount(1);
return lazyModel;
}
// -------------------- 辅助方法 -------------------//
/**
* 设置分页参数到Query对象,辅助函数.
*/
protected <T> Query setPageParameterToQuery(final Query q, final int first, final int pageSize) {
Validate.isTrue(pageSize > 0, "Page Size must larger than zero");
//hibernate的firstResult的序号从0开始 primeface lazymodel first从0开始
q.setFirstResult(first);
q.setMaxResults(pageSize);
return q;
}
/**
* 根据查询XSQL与参数结合创建Query对象.
* 与 findModelByMap() 函数可进行更加灵活的操作.
*
* @param xsql 基于 xsqlbuilder 样式的类SQL语句.
* @param filterMap 参数集合，从页面上以Map形式传过来的属性集合.
* @return 返回 javax.persistence.Query 对象
*/
public Query createXsqlQuery(String xsql, Map<String, Object> filterMap) {
Map<String, Object> paramMap = Maps.newHashMapWithExpectedSize(5);
paramMap = this.buildParamMap(xsql, filterMap);
// 构建 JPQL 语句
XsqlBuilder builder = new XsqlBuilder();
String jpql = builder.generateHql(xsql, paramMap).getXsql().toString();
return createQuery(jpql, paramMap);
}
/**
* 用法参考
*
* @param xsql 基于 xsqlbuilder 样式的类SQL语句.
* @param filterMap 参数集合，从页面上以Map形式传过来的属性集合.
* @return paramMap 回调的参数列表，Map的key剔除了前缀
* @see StatelessEntityService#findXsqlPage
*/
public Map<String, Object> buildParamMap(String xsql, Map<String, Object> filterMap) {
// 得到需要动态构建的字段
List<String> avialableKeys = Lists.newArrayList();
Pattern p = Pattern.compile("\\{(.+?)\\}");
Matcher m = p.matcher(xsql);
while (m.find()) {
avialableKeys.add(m.group(1));
}
//剔除不需要的过滤属性 和 空值的属性
Map<String, Object> tmpMap = Maps.newHashMap();
for (Map.Entry<String, Object> kv : filterMap.entrySet()){
if (kv.getValue()==null || "".equals(kv.getValue())){
continue;
}
boolean hasIt = false;
for (String s : avialableKeys){
if (kv.getKey().contains(s)){
hasIt = true;
break;
}
}
if (hasIt) tmpMap.put(kv.getKey(),kv.getValue());
}
//Assert.isTrue(avialableKeys.size()== filterMap.size());
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap = this.convertMap(tmpMap);
return paramMap;
}
private Map<String,Object> convertMap(Map<String, Object> xsqlFilterMap){
Map<String, Object> paramMap = Maps.newHashMap();
for (Map.Entry<String, Object> kv : xsqlFilterMap.entrySet()){
// 获取属性的 Name (key)
String filterName = kv.getKey();
String propertyName = StringUtils.substringAfter(filterName, "_");
//分离属性的 Type
String matchTypeStr = StringUtils.substringBefore(filterName, "_");
String propertyTypeCode = StringUtils.substring(matchTypeStr, matchTypeStr.length() - 1, matchTypeStr.length());
// 如果带有 LIKE，则一定为字符串，此时，需要加上 % （目前只处理了全 Like）
if(filterName.contains("LIKE")){
if (!("S".equals(propertyTypeCode.toUpperCase()))) {
throw new IllegalArgumentException("filter name: " + filterName
+ "'LIKE' needs 'S'.");
}
paramMap.put(propertyName, "%" + kv.getValue() + "%"); // 构建 paramMap
continue;
}
// 获得属性的 Type 的 Class 类型
Class<?> propertyType = null;
try {
propertyType = Enum.valueOf(PropertyType.class, propertyTypeCode).getValue();
} catch (RuntimeException e) {
throw new IllegalArgumentException("filter name: " + filterName
+ "Not prepared in accordance with the rules, attribute value types can not be.", e);
}
// 比较属性value的类型是否给定类型相同，如果相同则不转换，不相同，则需要将value转换为propertyType指定的类型
if (kv.getValue().getClass().equals(propertyType)){
paramMap.put(propertyName, kv.getValue());
} else {
Object propertyValue = ReflectionUtils.convertStringToObject(kv.getValue().toString(), propertyType);
paramMap.put(propertyName, propertyValue);
}
}
return paramMap;
}
/**
* @Description： 自定义分页大小的分页数据
* @param entityClass 实体类
* @param strWhere 查询条件
* @param currentPage 当前页码
* @param pageSize 每页显示行数
* @return
*/
public <T> PageInfo<T> findByPage(final Class<T> targetClass,String strWhere, int currentPage, int pageSize) {
StringBuilder hql = new StringBuilder();
StringBuilder countHql = new StringBuilder();
hql.append("from " + targetClass.getName() + " as o ");
countHql.append("select count(*) from "+targetClass.getName()+" as o ");
if(!MyString.isEmptyOrNull(strWhere)){
//只含排序
if(strWhere.trim().toLowerCase().substring(0, 5).equals("order")){
hql.append(strWhere);
countHql.append(strWhere);
}else{
hql.append("where " + strWhere);
countHql.append("where " + strWhere);
}
}
//初始化Page
PageInfo<T> page = new PageInfo<T>((int)countHqlResult(countHql.toString()),pageSize);
page.setCurrentPage(currentPage);
//分页查询开始
Query query = entityManager.createQuery(hql.toString());
query.setFirstResult(page.getStartRow());
query.setMaxResults(page.getPageSize());
//把取得的实体list设置到Page 类中
page.setResult(query.getResultList());
return page;
}
}
2.3、封装上面的
package com.dodow.base.service;
import javax.annotation.PostConstruct;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
/**
* 无状态的实体操作类.
* <p/>
* 扩展功能包括分页查询,按属性过滤条件列表查询.
* 可直接使用,也可以扩展EntityService
*
* @author
*/
@Stateless
public class StatelessEntityService extends EntityService {
/**
*
*/
private static final long serialVersionUID = 1L;
@PersistenceContext(unitName = "pu")
public EntityManager entityManager;
@PostConstruct
private void initEntityManager(){
logger.info("初始化 EntityManager");
this.setEntityManager(entityManager);
}
}
类，对外提供数据库操作类，以后如没必要，就用这个类统一做数据库的相关操作。
好象上面还涉及到一个验证类，一并贴出来
Validate.java
/**
* base.util类
* <p/>
* 数据验证
*
*
* @author hp
*/
package com.dodow.base.util;
import java.util.Collection;
import java.util.Map;
/**
*
*/
public abstract class Validate extends org.apache.commons.lang.Validate {
public static void isTrue(boolean expression, RuntimeException throwIfAssertFail) {
if (!expression) {
throw throwIfAssertFail;
}
}
public static void isNull(Object object, RuntimeException throwIfAssertFail) {
if (object != null) {
throw throwIfAssertFail;
}
}
public static void notNull(Object object, RuntimeException throwIfAssertFail) {
if (object == null) {
throw throwIfAssertFail;
}
}
public static void notEmpty(Object[] array, RuntimeException throwIfAssertFail) {
if (ObjectUtils.isEmpty(array)) {
throw throwIfAssertFail;
}
}
public static void noNullElements(Object[] array, RuntimeException throwIfAssertFail) {
if (array != null) {
for (Object element : array) {
if (element == null) {
throw throwIfAssertFail;
}
}
}
}
public static void notEmpty(Collection collection, RuntimeException throwIfAssertFail) {
if (CollectionUtils.isEmpty(collection)) {
throw throwIfAssertFail;
}
}
public static void notEmpty(Map map, RuntimeException throwIfAssertFail) {
if (CollectionUtils.isEmpty(map)) {
throw throwIfAssertFail;
}
}
public static void hasLength(String text, String message) {
if (!StringUtils.hasLength(text)) {
throw new IllegalArgumentException(message);
}
}
public static void hasLength(String text) {
hasLength(text,
"this String argument must have length; it must not be null or empty");
}
public static void hasLength(String text, RuntimeException throwIfAssertFail) {
if (!StringUtils.hasLength(text)) {
throw throwIfAssertFail;
}
}
public static void hasText(String text, String message) {
if (!StringUtils.hasText(text)) {
throw new IllegalArgumentException(message);
}
}
public static void hasText(String text) {
hasText(text,
"this String argument must have text; it must not be null, empty, or blank");
}
public static void hasText(String text, RuntimeException throwIfAssertFail) {
if (!StringUtils.hasText(text)) {
throw throwIfAssertFail;
}
}
public static void doesNotContain(String textToSearch, String substring, String message) {
if (StringUtils.hasLength(textToSearch) && StringUtils.hasLength(substring) &&
textToSearch.indexOf(substring) != -1) {
throw new IllegalArgumentException(message);
}
}
public static void doesNotContain(String textToSearch, String substring) {
doesNotContain(textToSearch, substring,
"this String argument must not contain the substring [" + substring + "]");
}
public static void doesNotContain(String textToSearch, String substring, RuntimeException throwIfAssertFail) {
if (StringUtils.hasLength(textToSearch) && StringUtils.hasLength(substring)
&& textToSearch.indexOf(substring) != -1) {
throw throwIfAssertFail;
}
}
public static void isInstanceOf(Class clazz, Object obj) {
isInstanceOf(clazz, obj, "");
}
public static void isInstanceOf(Class type, Object obj, String message) {
notNull(type, "Type to check against must not be null");
if (!type.isInstance(obj)) {
throw new IllegalArgumentException(message +
"Object of class [" + (obj != null ? obj.getClass().getName() : "null") +
"] must be an instance of " + type);
}
}
public static void isInstanceOf(Class type, Object obj, RuntimeException throwIfAssertFail) {
notNull(type, "Type to check against must not be null");
if (!type.isInstance(obj)) {
throw throwIfAssertFail;
}
}
public static void isAssignable(Class superType, Class subType) {
isAssignable(superType, subType, "");
}
public static void isAssignable(Class superType, Class subType, String message) {
notNull(superType, "Type to check against must not be null");
if (subType == null || !superType.isAssignableFrom(subType)) {
throw new IllegalArgumentException(message + subType + " is not assignable to " + superType);
}
}
public static void state(boolean expression, String message) {
if (!expression) {
throw new IllegalStateException(message);
}
}
public static void state(boolean expression) {
state(expression, "this state invariant must be true");
}
}
这样底层数据操作方面的封装就完成了，按我们前面的介绍，在业务层的service层里，如需要对某个数据表做操作，大体上的代码就象这个样子，比如:LoginService 里面，要做
@EJB
private StatelessEntityService entityService;
//注入数据库操作类
//然后定义登录时，判断用户是否存在的方法，如下：
/**
* @Title:ifExistenceUser
* @Desc:登录判断，用户是否存在
* @Param:@param un
* @Param:@param pw
* @Param:@return
* @Return:Boolean
* @Throws:
*/
public Boolean ifExistenceUser(String un, String pw) {
StringBuilder jpql = new StringBuilder();
jpql.append("select us from User us ");
jpql.append(" where us.name='" + un + "' and us.password='" + pw + "'");
try {
List<User> uList1 = entityService.findList(jpql.toString());
if (uList1.size() > 0) {
return true;
}
} catch (Exception e) {
}
return false;
}通过上面的entityService就可以做相应的数据库操作，对于业务层就不用关心数据库的基本操作。后面还会花一些时间，介绍业务层里面的相关的完整实现。就到这里吧。对你有帮助的话，敬请点个赞，谢了。
扫一扫
5111
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
