public int getCount(String hql) {
session = HibernateUtil.currentSession();
tx = session.beginTransaction();
Query q = session.createQuery(hql);
tx.commit();
return Integer.parseInt(q.list().get(0).toString());
}
public List<User> getAllData() {
String hql = "from Syncpush u where 1=1 ";
List<User> results = null;
org.hibernate.Query query = sessionFactory.getCurrentSession().createQuery(hql);
results = (List<User>) query.list();
return results;
}
@Override
public List<Sys_Picture> findPicturesByOrginalID(String orginalID) {
// TODO Auto-generated method stub
// 获取集团
Doc_Company company =
(Doc_Company)
getSession()
.createQuery( //
"from Doc_Company c where c.originalID=?") //
.setParameter(0, orginalID) //
.uniqueResult();
Set<Doc_Factory> setfac = company.getDoc_factorys();
List<Sys_Picture> listpics = new ArrayList<>();
for (Doc_Factory factory : setfac) {
Query query2 =
getSession()
.createQuery(
"from Sys_Picture p where p.doc_factory.id='"
+ factory.getId()
+ "' "); // 带条件的查询语句
listpics.addAll(query2.list());
}
return listpics;
}
@SuppressWarnings("unchecked")
@Override
public List<T> findAll() {
Session session = sessionFactory.getCurrentSession();
Query query = session.createQuery("FROM " + this.persistentClass.getName());
return (List<T>) query.list();
}
public void deleteDonViTinh(int dvtId) {
session.beginTransaction();
String sql = "update DonViTinh set daXoa = 1 where dvtId = " + dvtId;
Query query = session.createQuery(sql);
query.executeUpdate();
session.getTransaction().commit();
}
public IKategorie getKategorie(String kategorieName) throws DatabaseEntryNotFoundException {
IKategorie kategorie = null;
try {
Session session = HibernateHelper.getSession();
Query query =
session.createQuery("from " + getTable() + " k where k.bezeichnung = :kategorieName");
query.setString("kategorieName", kategorieName);
@SuppressWarnings("rawtypes")
List results = query.list();
if (results.size() == 1) {
kategorie = (IKategorie) results.get(0);
}
if (results.size() == 0) {
throw new DatabaseEntryNotFoundException();
}
} catch (HibernateException e) {
e.printStackTrace();
}
return kategorie;
}
/** 加载指定ID集合文件; */
@SuppressWarnings("unchecked")
public ArrayList<File> loadFileByIds(String ids) {
Query q =
sessionFactory.getCurrentSession().createQuery("from File f where f.id in (" + ids + ")");
ArrayList<File> files = (ArrayList<File>) q.list();
return files;
}
@Override
public Member memberLogin(String login, String password) throws Exception {
Session session = HibernateUtil.getSession();
Transaction tx = null;
Member member = null;
try {
String hql = "SELECT a FROM Member AS a WHERE a.login=:login AND a.password=:password";
Query query = session.createQuery(hql);
query.setString("login", login.trim());
query.setString("password", password.trim());
query.setMaxResults(1);
tx = session.beginTransaction();
member = (Member) query.uniqueResult();
if (member != null && ("y").equals(member.getActivated().toLowerCase())) {
member.setLoginTimes(Integer.valueOf(member.getLoginTimes() + 1));
member.setLastLogin(new Date());
session.update(member);
}
tx.commit();
} catch (Exception ex) {
if (tx != null) tx.rollback();
logger.info("In class MemberServiceImpl:memberLogin()\n");
ex.printStackTrace();
} finally {
HibernateUtil.closeSession();
}
return member;
}
private String getFolio(Almacen almacen) {
Query query =
currentSession()
.createQuery(
"select f from Folio f where f.nombre = :nombre and f.almacen.id = :almacenId");
query.setString("nombre", "FACTURA");
query.setLong("almacenId", almacen.getId());
query.setLockOptions(LockOptions.UPGRADE);
Folio folio = (Folio) query.uniqueResult();
if (folio == null) {
folio = new Folio("FACTURA");
folio.setAlmacen(almacen);
currentSession().save(folio);
return getFolio(almacen);
}
folio.setValor(folio.getValor() + 1);
java.text.NumberFormat nf = java.text.DecimalFormat.getInstance();
nf.setGroupingUsed(false);
nf.setMinimumIntegerDigits(9);
nf.setMaximumIntegerDigits(9);
nf.setMaximumFractionDigits(0);
StringBuilder sb = new StringBuilder();
sb.append("FA-");
sb.append(almacen.getEmpresa().getOrganizacion().getCodigo());
sb.append(almacen.getEmpresa().getCodigo());
sb.append(almacen.getCodigo());
sb.append(nf.format(folio.getValor()));
return sb.toString();
}
public List find(String id) {
String hql = "from Course c where c.uid=?";
Query query = baseDaoImpl.getSession().createQuery(hql);
query.setString(0, id);
List list = query.list();
return list;
}
protected long getRowCountFromDataBase() {
long rowCount = 0;
SessionFactory factory = FLRSessionFactory.getFactory();
Session session = null;
try {
session = factory.openSession();
session = setFilter(session);
String queryString =
"SELECT COUNT(*) FROM FLRLohnartstundenfaktor AS lohnartstundenfaktor"
+ buildWhereClause();
Query query = session.createQuery(queryString);
List<?> rowCountResult = query.list();
if (rowCountResult != null && rowCountResult.size() > 0) {
rowCount = ((Long) rowCountResult.get(0)).longValue();
}
} catch (Exception e) {
throw new EJBExceptionLP(EJBExceptionLP.FEHLER_FLR, e);
} finally {
if (session != null) {
try {
session.close();
} catch (HibernateException he) {
throw new EJBExceptionLP(EJBExceptionLP.FEHLER, he);
}
}
}
return rowCount;
}
@SuppressWarnings("unchecked")
@Override
public List<CollectSources> getAllCollectSources() {
String hql = "from CollectSources";
Query query = sessionFactory.getCurrentSession().createQuery(hql);
return query.list();
}
public ServiceProduct getServiceProductBySpId(String productId) {
// ServiceProduct sp=new ServiceProduct();
List list = null;
Session session = getSession();
try {
String sql =
"select sp.csp_id from service_product sp,product p where p.id = sp.product_id and p.MOBILE_PRODUCT = 1";
Query query = session.createSQLQuery(sql);
list = query.list();
} catch (DataAccessResourceFailureException e) {
logger.error("Hibernate错误:" + e.getMessage());
e
.printStackTrace(); // To change body of catch statement use File | Settings | File
// Templates.
} catch (IllegalStateException e) {
logger.error("Hibernate错误:" + e.getMessage());
e
.printStackTrace(); // To change body of catch statement use File | Settings | File
// Templates.
} catch (HibernateException e) {
logger.error("Hibernate错误:" + e.getMessage());
e.printStackTrace();
} finally {
session.close();
}
// return list==null||list.size() == 0?null:list.get(0);
return null;
}
@Override
public Salary findSalaryById(Integer id) {
Query query = getSession().createQuery("from Salary as s where s.id = ?");
query.setString(0, id.toString());
Salary s = (Salary) query.uniqueResult();
return s;
}
public int getCountOfSubscribers(int forumId, int userId) {
// TODO Auto-generated method stub
Session session = this.sessionFactory.openSession();
Transaction tx = null;
int countOfSubscribers = 0;
try {
tx = session.beginTransaction();
Query query = null;
if (userId == 0) {
query = session.createQuery("select count(*) from Subscription where forumId = :forumId");
query.setParameter("forumId", forumId);
} else {
query =
session.createQuery(
"select count(*) from Subscription where forumId = :forumId and userId = :userId");
query.setParameter("forumId", forumId);
query.setParameter("userId", userId);
}
Long count = (Long) query.uniqueResult();
countOfSubscribers = count.intValue();
// System.out.println("No of subscribers.."+countOfSubscribers);
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return countOfSubscribers;
}
/**
* @param code
* @param parentCode
* @param description
* @return list of EgPartytype filtered by optional conditions
*/
public List<EgPartytype> getPartyTypeDetailFilterBy(
final String code, final String parentCode, final String description) {
final StringBuffer qryStr = new StringBuffer();
qryStr.append(
"select distinct ptype From EgPartytype ptype where ptype.createdby is not null ");
Query qry = getCurrentSession().createQuery(qryStr.toString());
if (code != null && !code.equals("")) {
qryStr.append(" and (upper(ptype.code) like :code)");
qry = getCurrentSession().createQuery(qryStr.toString());
}
if (parentCode != null && !parentCode.equals("")) {
qryStr.append(" and (upper(ptype.egPartytype.code) like :parentCode)");
qry = getCurrentSession().createQuery(qryStr.toString());
}
if (description != null && !description.equals("")) {
qryStr.append(" and (upper(ptype.description) like :description)");
qry = getCurrentSession().createQuery(qryStr.toString());
}
if (code != null && !code.equals("")) {
qry.setString("code", "%" + code.toUpperCase().trim() + "%");
}
if (parentCode != null && !parentCode.equals("")) {
qry.setString("parentCode", "%" + parentCode.toUpperCase().trim() + "%");
}
if (description != null && !description.equals("")) {
qry.setString("description", "%" + description.toUpperCase().trim() + "%");
}
return qry.list();
}
@Override
public List<Forum> getForumsOfaUser(int userId) {
// TODO Auto-generated method stub
List<Forum> forums = new ArrayList<Forum>();
Session session = sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query = session.createQuery("from Subscription where userId = :userId");
query.setString("userId", String.valueOf(userId));
List<Subscription> userSubscribedGroups = query.list();
for (Subscription subsc : userSubscribedGroups) {
query = session.createQuery("from Forum where forumId = :forumId");
query.setParameter("forumId", subsc.getForumId());
// add this to a list variable query.uniqueResult();
forums.add((Forum) query.uniqueResult());
}
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return forums;
}
public List queryAll() throws Exception {
String hql = "FROM Student";
Query q = this.session.createQuery(hql);
List all = q.list();
this.session.close();
return all;
}
// User defined finders/Buscadores definidos por el usuario
public static Size findById(int id) throws javax.ejb.ObjectNotFoundException {
if (XavaPreferences.getInstance().isJPAPersistence()) {
javax.persistence.Query query =
org.openxava.jpa.XPersistence.getManager()
.createQuery("from Size as o where o.id = :arg0");
query.setParameter("arg0", new Integer(id));
try {
return (Size) query.getSingleResult();
} catch (Exception ex) {
// In this way in order to work with Java pre 5
if (ex.getClass().getName().equals("javax.persistence.NoResultException")) {
throw new javax.ejb.ObjectNotFoundException(
XavaResources.getString("object_not_found", "Size"));
} else {
ex.printStackTrace();
throw new RuntimeException(ex.getMessage());
}
}
} else {
org.hibernate.Query query =
org.openxava.hibernate.XHibernate.getSession()
.createQuery("from Size as o where o.id = :arg0");
query.setParameter("arg0", new Integer(id));
Size r = (Size) query.uniqueResult();
if (r == null) {
throw new javax.ejb.ObjectNotFoundException(
XavaResources.getString("object_not_found", "Size"));
}
return r;
}
}
public User getUser(String username, String psd) {
Session session = sessionFactory.getCurrentSession();
Query q = session.createQuery(" from User u where username= :username and psd = :psd");
q.setParameter("username", username);
q.setParameter("psd", psd);
return (User) q.uniqueResult();
}
/** 加载指定ID文件; */
@SuppressWarnings("unchecked")
public File loadFileById(String id) {
int idF = Integer.parseInt(id);
Query q = sessionFactory.getCurrentSession().createQuery("from File f where f.id =" + idF);
ArrayList<File> files = (ArrayList<File>) q.list();
return files.get(0);
}
@Override
public int countProgramInstances(ProgramInstanceQueryParams params) {
String hql = buildProgramInstanceHql(params);
Query query = getQuery(hql);
return ((Number) query.iterate().next()).intValue();
}
/**
* SQL 分页查询
*
* @param page
* @param sqlString
* @param resultClass
* @param parameter
* @return
*/
@SuppressWarnings("unchecked")
public <E> Page<E> findBySql(
Page<E> page, String sqlString, Parameter parameter, Class<?> resultClass) {
// get count
if (!page.isDisabled() && !page.isNotCount()) {
String countSqlString = "select count(*) " + removeSelect(removeOrders(sqlString));
// page.setCount(Long.valueOf(createSqlQuery(countSqlString,
// parameter).uniqueResult().toString()));
Query query = createSqlQuery(countSqlString, parameter);
List<Object> list = query.list();
if (list.size() > 0) {
page.setCount(Long.valueOf(list.get(0).toString()));
} else {
page.setCount(list.size());
}
if (page.getCount() < 1) {
return page;
}
}
// order by
String sql = sqlString;
if (StringUtils.isNotBlank(page.getOrderBy())) {
sql += " order by " + page.getOrderBy();
}
SQLQuery query = createSqlQuery(sql, parameter);
// set page
if (!page.isDisabled()) {
query.setFirstResult(page.getFirstResult());
query.setMaxResults(page.getMaxResults());
}
setResultTransformer(query, resultClass);
page.setList(query.list());
return page;
}
public Long countPedidosRealizadosByUsuario(Usuario usuario) {
final Query query =
createQuery("select count(p) from Pedido p " + "where p.usuario.id = :idusuario");
query.setParameter("idusuario", usuario.getId());
return ((Long) query.iterate().next());
}
public void deleteDonViTinhTen(String dvtTen) {
session.beginTransaction();
String sql = "update DonViTinh set daXoa = 1 where dvtTen = '" + dvtTen + "'";
Query query = session.createQuery(sql);
query.executeUpdate();
session.getTransaction().commit();
}
@Override
public List<Reply> getRepliesToPost(int postId) {
// TODO Auto-generated method stub
Session session = sessionFactory.openSession();
Transaction tx = null;
List<Reply> replyArr = new ArrayList<Reply>();
try {
tx = session.beginTransaction();
Query query =
session.createQuery("from Reply where postId = :postId order by createdDate desc");
query.setParameter("postId", postId);
replyArr = query.list();
/* for(Reply reply : replyArr){
System.out.println("Reply - id----"+reply.getReplyId());
System.out.println("Reply - Description----"+reply.getDescription());
System.out.println("Reply - Post id----"+reply.getPostId());
}*/
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return replyArr;
}
@Override
public void update_PictureLog(Sys_Picture picture) {
// TODO Auto-generated method stub
Query query = getSession().createQuery("update Sys_Picture t set t.flag = '" + 0 + "' ");
query.executeUpdate();
this.getSession().update(picture);
}
@Override
public void deleteSubscription(int userId, int forumId) {
// TODO Auto-generated method stub
Session session = this.sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query =
session.createQuery(
"delete from Subscription where userId = :userId and forumId = :forumId");
query.setParameter("userId", userId);
query.setParameter("forumId", forumId);
int result = query.executeUpdate();
System.out.println("Execute query..");
if (result > 0) {
tx.commit();
// System.out.println("Subscription Removed..");
} else {
// System.out.println("Subscription does not exist");
tx.rollback();
}
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
}
@Override
public Map<Vendor, List<Notebook>> getNotebooksStorePresent() {
Session session = factory.openSession();
try {
Query query = session.createQuery("select n from Store s join s.notebook n");
List results = query.list();
Map<Vendor, List<Notebook>> resMap = new HashMap<>();
for (Iterator iter = results.iterator(); iter.hasNext(); ) {
Notebook notebook = (Notebook) iter.next();
Vendor key = notebook.getVendor();
if (resMap.containsKey(key)) {
List<Notebook> notebooks = resMap.get(key);
notebooks.add(notebook);
resMap.replace(key, notebooks);
} else {
List<Notebook> notebooks = new ArrayList<>();
notebooks.add(notebook);
resMap.put(key, notebooks);
}
}
return resMap;
} finally {
if (session != null) {
session.close();
}
}
}
@Transactional(readOnly = true, isolation = Isolation.READ_COMMITTED)
@Override
public String getUserMenu(User user) {
String loginName = user.getLoginName(); // 获得登录用户的logiName
StringBuilder resultStr = new StringBuilder(); // 定义最终返回的 json 格式的字符串
// 根据登录的用户名loginName，获取该用户对应的顶级菜单
String topMenuSql =
"select a.id,a.name,a.icon,a.url from wxw_sys_menu a where a.id in " //
+ " (select md.menuId from wxw_menu_dept md where md.deptId in " //
+ " (select u.departmentId from wxw_user u where loginName=:loginName))" //
+ " order by orderNum asc";
logger.debug("topMenuSql:" + topMenuSql);
Query sqlQuery =
getSession()
.createSQLQuery(topMenuSql) //
.setParameter("loginName", loginName) //
.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
List resultList = sqlQuery.list();
if (!(resultList == null || resultList.size() == 0)) {
for (int i = 0; i < resultList.size(); i++) {
// 这里返回的 resultList 本身就类似于 Map，所以强转不会粗问题~~
Map resultMap = (Map) resultList.get(i);
// logger.debug("menu_icon:"+resultMap.get("menu_icon"));
if (0 == i) {
resultStr.append("{'menus':[");
}
resultStr.append("{'id':'" + resultMap.get("id") + "',");
resultStr.append("'icon':'" + resultMap.get("icon") + "',");
resultStr.append("'name':'" + resultMap.get("name") + "',");
resultStr.append("'menus':");
String subMenuSql =
"select a.id,a.name,a.icon,a.url from wxw_sys_menu a " //
+ " where a.level = :level and a.parentId = :pId";
List resultSubMenuList =
getSession()
.createSQLQuery(subMenuSql) //
.setParameter("level", 2) //
.setParameter("pId", resultMap.get("id")) //
.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) //
.list();
resultStr.append(JSONArray.fromObject(resultSubMenuList));
if (i != (resultList.size() - 1)) {
resultStr.append("},");
}
if (i == (resultList.size() - 1)) {
resultStr.append("}]}");
}
}
}
logger.debug("jsonMenus:" + resultStr);
return resultStr.toString();
}