Beispiel #1
1
  public static void viewAd(String id) {

    Ad ad = Ad.findById(Long.parseLong(id));
    List<Category> cats = Category.find("categorytype_id=?1 order by id", "1").fetch();

    EntityManager entityManager = play.db.jpa.JPA.em();
    List<BigInteger> bCounts =
        entityManager
            .createNativeQuery(
                "select count(*) as maxCount from Ad as a group by category_id order by maxCount")
            .getResultList();
    int min = bCounts.get(0).intValue();
    int max = bCounts.get(bCounts.size() - 1).intValue();
    bCounts =
        entityManager
            .createNativeQuery(
                "select count(*) as maxCount from Ad as a group by category_id order by category_id ")
            .getResultList();
    List<String> fonts = new ArrayList<String>();
    for (int i = 0; i < bCounts.size(); i++) {
      BigInteger count = bCounts.get(i);
      int x = Ads.getFontSize(count.intValue(), min, max);
      fonts.add(String.valueOf(x));
    }

    render(ad, fonts, min, max, cats);
  }
Beispiel #2
1
 @Override
 public Long countbfjlBeanByCondition(bfjlBean bfjlBean) {
   String strHql =
       "select  count(t.bfjl_id)  from T_BFJL t ,t_khgl t1 ,t_bfjl t2 ,t_employee t3 where t.khgl_id =t1.khgl_id and  t.bfjl_id =t2.bfjl_id and  t.employee_id =t3.employee_id";
   Query query = entityManager.createNativeQuery(strHql);
   return Long.parseLong(query.getSingleResult().toString());
 }
Beispiel #3
0
  @SuppressWarnings("unchecked")
  public List<ReportTask> findTODOTasks() {
    String sql =
        "select * from report_tasks where status in ('%s', '%s') and ( gen_count IS NULL or gen_count <= %d )";
    sql = String.format(sql, Status.planned, Status.inprogress, MAX_GENERATION_COUNT);
    Query query = manager.createNativeQuery(sql, ReportTask.class);
    query.setHint(QueryHints.HINT_CACHE_MODE, CacheMode.IGNORE);
    List<?> result = query.getResultList();
    List<ReportTask> tasks = (List<ReportTask>) result;

    StringBuffer idSbs = new StringBuffer();
    // fill project identifier
    if (!tasks.isEmpty()) {
      List<Long> pId = new ArrayList<Long>();
      for (ReportTask task : tasks) {
        pId.add(task.getProjectId());
      }
      Map<Long, String> projects = new HashMap<Long, String>();
      sql = "select id, name from projects where id in ( %s )";
      query = manager.createNativeQuery(String.format(sql, StringUtils.join(pId, ',')));
      query.setHint(QueryHints.HINT_CACHE_MODE, CacheMode.IGNORE);
      result = query.getResultList();
      for (Object o : result) {
        Object[] objs = (Object[]) o;
        projects.put(((Number) objs[0]).longValue(), objs[1].toString());
      }
      for (ReportTask t : tasks) {
        t.setProjectName(projects.get(t.getProjectId()));
        idSbs.append(t.getId().toString()).append(",");
      }
    }

    logger.info("Find " + tasks.size() + " tasks! :: " + idSbs);
    return tasks;
  }
  @PostConstruct
  public void startup() throws Exception {
    Query q = entityManager.createNativeQuery("DELETE from ADDRESS");
    q.executeUpdate();
    q = entityManager.createNativeQuery("DELETE from PERSON");
    q.executeUpdate();
    entityManager.flush();

    p = new Person();
    p.setFirstName("Shane");
    p.setLastName("Bryzak");
    p.setDateOfBirth(df.parse("1901-01-01"));
    p.setAddresses(new ArrayList<Address>());

    a = new Address();
    a.setPerson(p);
    a.setStreetNo(100);
    a.setStreetName("Main");
    a.setSuburb("Pleasantville");
    a.setPostCode("32123");
    a.setCountry("Australia");
    p.getAddresses().add(a);

    a = new Address();
    a.setPerson(p);
    a.setStreetNo(57);
    a.setStreetName("1st Avenue");
    a.setSuburb("Pittsville");
    a.setPostCode("32411");
    a.setCountry("Australia");
    p.getAddresses().add(a);
    entityManager.persist(p);
    entityManager.flush();

    p = new Person();
    p.setFirstName("Jozef");
    p.setLastName("Hartinger");
    p.setDateOfBirth(df.parse("1901-01-01"));
    p.setAddresses(new ArrayList<Address>());

    a = new Address();
    a.setPerson(p);
    a.setStreetNo(99);
    a.setStreetName("Purkynova");
    a.setSuburb("Kralovo pole");
    a.setPostCode("60200");
    a.setCountry("Czech republic");
    p.getAddresses().add(a);
    entityManager.persist(p);
    entityManager.flush();
  }
 @Override
 public List<Request> findEntitiesWithAvailableStatus(boolean all, int maxResults, int firstResult)
     throws Exception {
   EntityManager entityManager = getEntityManager();
   RequestDAO rdao = new RequestDAO();
   Long resultIdLong;
   Request cr;
   Query nquery =
       entityManager.createNativeQuery(nativeQuerySequenceFindRequestWithAvailableProducts);
   try {
     if (!all) {
       nquery.setMaxResults(maxResults);
       nquery.setFirstResult(firstResult);
     }
     List resultListId = nquery.getResultList();
     List<Request> resultList = new ArrayList<Request>();
     for (Object elem : resultListId) {
       resultIdLong = Long.parseLong(elem.toString());
       cr = rdao.findById(resultIdLong);
       resultList.add(cr);
     }
     for (Object elem : resultList) {}
     return resultList;
   } catch (NullPointerException e) {
     LogBean.getLogger().debug(FINDMSG + " " + java.util.Calendar.getInstance().getTime());
     return null;
   }
 }
  /** Creates a query. */
  @Override
  public Query createNativeQuery(String sql, String map) {
    EntityManager em = getCurrent();

    if (em != null) {
      return em.createNativeQuery(sql, map);
    }

    em = createEntityManager();

    try {
      return em.createNativeQuery(sql, map);
    } finally {
      freeEntityManager(em);
    }
  }
Beispiel #7
0
  @Override
  public List<Dish> genDishByBudgetAndCate(double budget, int cate) {
    if (budget > 0 && cate != 0) {
      EntityManager em = entityManager;
      try {
        Query q =
            em.createNativeQuery(
                "Select * From dish Where price <= ? And id_category = ?", Dish.class);
        q.setParameter(1, budget);
        q.setParameter(2, cate);
        q.setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE);
        List<Dish> tmpLstDish = q.getResultList();
        if (tmpLstDish.size() > 10 && tmpLstDish != null) {
          List<Dish> lstDish = new ArrayList<Dish>();
          Collections.shuffle(tmpLstDish);
          for (int i = 0; i < 10; i++) {
            lstDish.add(tmpLstDish.get(i));
          }
          return lstDish;
        } else {
          return tmpLstDish;
        }

      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return null;
  }
 public Object getSpecialPrice(String nombre) {
   Query q =
       em.createNativeQuery(
           "SELECT PRECIO FROM GENERIC.PUBLIC.PRECIOESPECIAL p WHERE p.NOMBRE=?1");
   q.setParameter(1, nombre);
   return q.getSingleResult();
 }
 @Override
 public void execute(Application application) {
   Query query =
       em.createNativeQuery("{call PROC_BORROW_OLD(?)}")
           .setParameter(1, application.getApplicationNo());
   query.executeUpdate();
 }
Beispiel #10
0
 public void deleteUserCard(User user, long cardId) {
   entityManager
       .createNativeQuery("DELETE FROM Card_User WHERE user = :user AND card = :cardId")
       .setParameter("user", user.getId())
       .setParameter("cardId", cardId)
       .executeUpdate();
 }
  public List<Object[]> acumuladoPuntossucursal(List<Evaluacion> eval, Dependencia dep) {
    List<Object[]> res = null;
    String cadena = eval.get(0).getId().toString();
    for (int i = 1; i < eval.size(); i++) {

      cadena += "," + eval.get(i).getId().toString();
    }

    StringBuffer s =
        new StringBuffer("select sum(puntos/c.PESO),v.ID_EVALUACION")
            .append(" from EVA.EVALUACION_EMPLEADO v join empleado e on(v.ID_EMPLEADO=e.ID) ")
            .append(
                " join dependencia d on(d.ID=e.ID_DEPENDENCIA) join evaluacion c on (v.ID_EVALUACION=c.ID) join estado_empleado s on(e.ID=s.ID_EMPLEADO) ")
            .append(" where id_evaluacion in(")
            .append(cadena)
            .append(") and s.activo=1  and d.id=")
            .append(dep.getId().toString())
            .append(" group by v.ID_EVALUACION order by v.ID_EVALUACION");
    try {
      res = em.createNativeQuery(s.toString()).getResultList();

    } catch (Exception e) {
      return null;
    }

    return res;
  }
Beispiel #12
0
  @Override
  public int insertCorte(CorteCaja cc) {
    System.out.println("+++++++++++++EJB++++++++++");
    System.out.println(cc.toString());
    Query query =
        em.createNativeQuery(
            "INSERT INTO Corte_Caja(ID_CORTE_CAJA_PK,ID_CAJA_FK,FECHA,CANT_CHEQUES_ANT,"
                + "                         MONTO_CHEQUES_ANT,SALDO_ANTERIOR,CANT_CHEQUES_NUEVOS,MONTO_CHEQUES_NUEVOS,NUEVO_SALDO,"
                + "                         COMENTARIOS,ID_USER_FK,ID_STATUS_FK,MONTO_CUENTA_ANT,MONTO_CUENTA_NUEVO) VALUES(?,?,sysdate,?,?,?,?,?,?,?,?,?,?,?)");

    query.setParameter(1, cc.getIdCorteCajaPk());
    query.setParameter(2, cc.getIdCajaFk());
    query.setParameter(3, cc.getCantChequesAnt());
    query.setParameter(4, cc.getMontoChequesAnt());
    query.setParameter(5, cc.getSaldoAnterior());
    query.setParameter(6, cc.getCantChequesNuevos());
    query.setParameter(7, cc.getMontoChequesNuevos());
    query.setParameter(8, cc.getSaldoNuevo());
    query.setParameter(9, cc.getComentarios());
    query.setParameter(10, cc.getIdUserFk());
    query.setParameter(11, cc.getIdStatusFk());
    query.setParameter(12, cc.getMontoCuentaAnterior());
    query.setParameter(13, cc.getMontoCuentaNuevo());
    return query.executeUpdate();
  }
 private Collection<GroupData> fetchGroupDataForUser(
     final User user, final boolean restrictToSuscribed) {
   final String ifConnectedColumns =
       "max(ifnull(USER_ID=:userId, false))>0, sum(ifnull(USER_ID=:userId AND LAST_VISIT<PUBLIC_MESSAGES.`DATE`, false))";
   final Query query =
       entityManager.createNativeQuery(
           "select GROUPS.ID, name, count(DISTINCT GROUP_USER.USER_ID), "
               + (user != null ? ifConnectedColumns : " 0,0")
               + " from GROUPS left join  GROUP_USER on GROUP_ID=ID "
               + " left join PUBLIC_MESSAGES on PUBLIC_MESSAGES.GROUP_ID=GROUP_USER.GROUP_ID "
               + (restrictToSuscribed ? " where GROUP_USER.USER_ID=:userId" : "")
               + " group by GROUPS.ID order by CREATION_DATE");
   if (user != null) query.setParameter("userId", user.getId());
   final List<Object[]> list = query.getResultList();
   final Collection<GroupData> result = new ArrayList<GroupData>(list.size());
   for (final Object[] o : list)
     result.add(
         new GroupData(
             ((Number) o[0]).longValue(),
             UserStringImpl.valueOf(String.valueOf(o[1])),
             ((Number) o[2]).longValue(),
             ((Number) o[3]).intValue() != 0,
             ((Number) o[4]).intValue()));
   return result;
 }
 /**
  * getManagedPagedDataByActivity
  *
  * @param firstRow
  * @param totalRows
  * @param order
  * @return list of dataverses ordered by activity
  */
 public List getManagedPagedDataByLastUpdated(int firstRow, int totalRows, String order) {
   List<VDC> list = new ArrayList();
   try {
     String queryString =
         "SELECT vdc.id, vdc.name, vdc.alias, vdc.affiliation, vdc.releasedate, "
             + "vdc.dtype, vdc.createddate, vdc.dvndescription, username, "
             + "CASE WHEN count(owner_id) is null THEN 0 ELSE count(owner_id) END AS owned_studies, "
             + "CASE WHEN max(lastupdatetime) is null THEN vdc.releasedate ELSE max(lastupdatetime) END as lastupdated, "
             + "vdc.restricted "
             + "FROM vdcuser, vdc "
             + "LEFT OUTER JOIN study on vdc.id = study.owner_id "
             + "LEFT OUTER JOIN studyfileactivity on study.id = studyfileactivity.study_id "
             + "WHERE vdc.creator_id = vdcuser.id "
             + "GROUP BY vdc.id, vdc.name, vdc.alias, vdc.affiliation, vdc.releasedate, vdc.dtype, vdc.createddate, vdc.dvndescription, username, vdc.restricted "
             + "ORDER BY "
             + "CASE WHEN max(lastupdatetime) is null THEN vdc.releasedate ELSE max(lastupdatetime) END "
             + order
             + " LIMIT "
             + totalRows
             + " OFFSET "
             + firstRow;
     Query query = em.createNativeQuery(queryString);
     list = (List<VDC>) query.getResultList();
   } catch (Exception e) {
     // do something here with the exception
     list = new ArrayList();
   } finally {
     return list;
   }
 }
  public List<Object[]> acumuladoPuntosEmpleadoPeso(List<Evaluacion> eval, Empleado emp) {
    List<Object[]> res = null;
    String cadena = eval.get(0).getId().toString();
    for (int i = 1; i < eval.size(); i++) {

      cadena += "," + eval.get(i).getId().toString();
    }

    StringBuffer s =
        new StringBuffer(
                "select (puntos/c.peso),v.ID_EVALUACION from EVA.EVALUACION_EMPLEADO v join empleado e on(v.ID_EMPLEADO=e.ID) join evaluacion c on (v.ID_EVALUACION=c.ID) ")
            .append(" where id_evaluacion in(")
            .append(cadena)
            .append(")  and e.id=")
            .append(emp.getId().toString())
            .append(" order by v.ID_EVALUACION");
    try {
      res = em.createNativeQuery(s.toString()).getResultList();

    } catch (Exception e) {
      return null;
    }

    return res;
  }
Beispiel #16
0
  public Map<Object, Object> getBannerListAndTotalCount(
      int start, ListFilterCriteria filterCriteria) {
    log.debug("BannerDao.getBannerListAndTotalCount() ");
    EntityManager entityManager = getEntityManagerForActiveEntities();
    Query bannerCountQuery =
        entityManager.createNativeQuery(
            "select count(*) from banner b where b."
                + filterCriteria.getColumn()
                + " like '%"
                + filterCriteria.getValueToSearch()
                + "%'");

    Query bannerListQuery =
        entityManager.createQuery(
            "select b from Banner b where b."
                + filterCriteria.getColumn()
                + " like '%"
                + filterCriteria.getValueToSearch()
                + "%' order by "
                + filterCriteria.getSortBy()
                + " "
                + filterCriteria.getSortOrder());
    bannerListQuery.setFirstResult(start);
    bannerListQuery.setMaxResults(filterCriteria.getItemsPerPage());

    List<Banner> bannersList = bannerListQuery.getResultList();
    Map<Object, Object> bannerDetails = new HashMap<Object, Object>();

    bannerDetails.put("list", bannersList);
    bannerDetails.put("totalCount", ((BigInteger) bannerCountQuery.getSingleResult()).intValue());
    return bannerDetails;
  }
Beispiel #17
0
 public List<MrTblPrepAndSendHistory> mrTblPrepAndSendHistory(
     String jobName, int organizeId, String processId) {
   String sqlString =
       " SELECT rownum as id, entity.*\n"
           + " FROM MR_TBL_PREP_AND_SEND_HISTORY entity\n"
           + " WHERE entity.ORGANIZE_ID = "
           + organizeId
           + "\n"
           + " AND entity.PROCESS_ID    = '"
           + processId
           + "'\n"
           + " AND entity.JOB_NAME      = '"
           + jobName
           + "'\n"
           + " AND entity.FIRM_BUCKET   =\n"
           + "  (SELECT MAX(FIRM_BUCKET)\n"
           + "  FROM MR_TBL_PREP_AND_SEND_HISTORY\n"
           + "  WHERE ORGANIZE_ID = 1\n"
           + "  AND PROCESS_ID    = 'IP'\n"
           + "  AND JOB_NAME      = '"
           + jobName
           + "')";
   Query query = em.createNativeQuery(sqlString, MrTblPrepAndSendHistory.class);
   return query.getResultList();
 }
Beispiel #18
0
 public List<String> mr1008PDataDesName(int organizeId, String processId) {
   String sqlString =
       "SELECT rownum as id, JOB_GROUP_DESC as jobGroupDesc\n"
           + "FROM\n"
           + "  (SELECT *\n"
           + "  FROM MR_TBL_DATA_TO_OTHER_CONFIG\n"
           + "  WHERE ORGANIZE_ID = 1\n"
           + "  AND PROCESS_ID    = 'IP'\n"
           + "  ORDER BY JOB_SEQ ASC\n"
           + "  ) entity\n"
           + "INNER JOIN\n"
           + "  (SELECT JOB_GROUP_NAME ,\n"
           + "    JOB_GROUP_SEQ ,\n"
           + "    JOB_GROUP_DESC\n"
           + "  FROM MR_TBL_DATA_TO_OTHER_JOB_GROUP\n"
           + "  WHERE ORGANIZE_ID = "
           + organizeId
           + "\n"
           + "  AND PROCESS_ID    = '"
           + processId
           + "'\n"
           + "  ORDER BY JOB_GROUP_SEQ ASC\n"
           + "  ) dplu\n"
           + "ON entity.JOB_GROUP_NAME = dplu.JOB_GROUP_NAME\n"
           + "GROUP BY JOB_GROUP_DESC, rownum\n"
           + "ORDER BY JOB_GROUP_DESC ASC";
   Query query = em.createNativeQuery(sqlString, String.class);
   return query.getResultList();
 }
Beispiel #19
0
  // 员工管理
  @Override
  public List<Object[]> ygglfindygglByCondition(Employee Employee, Integer start, Integer limit) {
    String strHql =
        "select t.employee_id,t.yggl_djrq ,t.employee_no,t.yggl_rybhbj,t.employee_name,t.yggl_ywxm,t1.organization_id,t1.organization_no,t1.organization_name, t2.xbsd_id,t2.xbsd_bh,t2.xbsd_mc ,t.yggl_sfzh,t3.jzzt_id,t3.jzzt_bh,t3.jzzt_mc,t.yggl_csrq,t4.hyzk_id,t4.hyzk_bh,t4.hyzk_mc,t5.xxsd_id,t5.xxsd_bh, t5.xxsd_mc ,t6.jgsd_id,t6.jgsd_bh,t6.jgsd_mc ,t7.gjsd_id,t7.gjsd_bh,t7.gjsd_mc ,t8.mzsd_id,t8.mzsd_bh,t8.mzsd_mc, t9.sdzw_id,t9.sdzw_bh,t9.sdzw_mc, t10.xsl_id,t10.xsl_bh,t10.xsl_mc,t.yggl_zzmm,t.yggl_rzrq,t.yggl_zzrq,t.yggl_syqc,t.yggl_lzrq,t11.jszc_id,t11.jszc_bh,t11.jszc_mc,t12.sdgw_id,t12.sdgw_bh, t12.sdgw_mc,t.yggl_dzyj,t.yggl_hjdh,t.yggl_hjyb,t.yggl_hjdz,t.yggl_jtdh,t.yggl_jtyb,t.yggl_jtzz,t.yggl_xdh,t.yggl_xyb,t.yggl_xzz,t.yggl_jjlxr, t.yggl_jjlxyb,t.yggl_jjlxdh,t.yggl_jjlxrdz,t13.xlsd_id,t13.xlsd_bh,t13.xlsd_mc ,t.yggl_xw,t.yggl_byyx,t.yggl_sxzy,t.yggl_wysp,t.yggl_tjrq,t.yggl_dqrq,  t.yggl_rjrq,t.yggl_hzhm,t.yggl_htqs,t.yggl_htjs,t.yggl_htqc,t.yggl_gzfa,t.yggl_jbxz,t.yggl_fxfs,t.yggl_yhkh,t.yggl_sdsksfs,t.yggl_bz  "
            + "from T_EMPLOYEE t ,t_organization t1 ,t_xbsd t2 ,t_jzzt t3 ,t_hyzk t4 , t_xxsd t5 ,t_jgsd t6 ,t_gjsd t7 ,t_mzsd t8 ,t_sdzw t9,  t_xsl t10 ,t_jszc t11 ,t_sdgw t12 ,t_xlsd  t13 where t.organization_id=t1.organization_id and  t.xbsd_id =t2.xbsd_id and   t.xbsd_id=t2.xbsd_id and  t.jzzt_id =t3.jzzt_id and t.hyzk_id=t4.hyzk_id and t.xxsd_id=t5.xxsd_id and  t.jgsd_id=t6.jgsd_id   and t.gjsd_id=t7.gjsd_id  and  t.mzsd_id=t8.mzsd_id and  t.sdzw_id=t9.sdzw_id and  t.xsl_id=t10.xsl_id and t.jszc_id=t11.jszc_id  and  t.sdgw_id=t12.sdgw_id and  t.xlsd_id =t13.xlsd_id";
    // 人员姓名
    if (null != Employee.getEmployeeName() && !"".equals(Employee.getEmployeeName().trim())) {
      strHql += " and t.employee_name LIKE('%" + Employee.getEmployeeName().trim() + "%')";
    }
    // 所属部门
    if (null != Employee.getYgglbz() && !"".equals(Employee.getYgglbz())) {
      strHql += " and t1.organization_name LIKE('%" + Employee.getYgglbz() + "%')";
    }
    if (SearchUntil.search_strHql != null) {
      strHql += SearchUntil.search_strHql;
      SearchUntil.search_strHql = "";
    }

    strHql += "  ORDER BY t.employee_id  desc";
    Query query = entityManager.createNativeQuery(strHql);

    if (null != start && null != limit) {
      if (0 == start) start = 1;
      query.setFirstResult((start.intValue() - 1) * limit);
      query.setMaxResults(limit.intValue());
    }
    List<Object[]> lstO = query.getResultList();
    return lstO;
  }
Beispiel #20
0
  /** Test of an SQL query using a result set mapping giving two entities (Login + LoginAccount). */
  public void testSQLResult() {
    if (vendorID == null) {
      return;
    }
    try {
      EntityManager em = getEM();
      EntityTransaction tx = em.getTransaction();
      try {
        tx.begin();

        LoginAccount acct = new LoginAccount(1, "Fred", "Flintstone");
        Login login = new Login("flintstone", "pwd");
        acct.setLogin(login);
        em.persist(login);
        em.persist(acct);

        tx.commit();
      } finally {
        if (tx.isActive()) {
          tx.rollback();
        }
        em.close();
      }

      em = getEM();
      tx = em.getTransaction();
      try {
        tx.begin();

        // Check the results
        List result =
            em.createNativeQuery(
                    "SELECT P.ID, P.FIRSTNAME, P.LASTNAME, P.LOGIN_ID, L.ID, L.USERNAME, L.PASSWORD "
                        + "FROM JPA_AN_LOGINACCOUNT P, JPA_AN_LOGIN L",
                    "AN_LOGIN_PLUS_ACCOUNT")
                .getResultList();
        assertEquals(1, result.size());

        Iterator iter = result.iterator();
        while (iter.hasNext()) {
          // Should be a String (type of "ID" column)
          Object[] obj = (Object[]) iter.next();
          assertEquals("Fred", ((LoginAccount) obj[0]).getFirstName());
          assertEquals("flintstone", ((Login) obj[1]).getUserName());
          assertEquals("Fred", ((LoginAccount) obj[0]).getFirstName());
          assertTrue(((LoginAccount) obj[0]).getLogin() == ((Login) obj[1]));
        }

        tx.rollback();
      } finally {
        if (tx.isActive()) {
          tx.rollback();
        }
        em.close();
      }
    } finally {
      clean(LoginAccount.class);
      clean(Login.class);
    }
  }
  @Override
  @Transactional(readOnly = true)
  public Page<UserGroupCheck> findAllByPage(Pageable pageable, Long groupId, long total) {
    // TODO use setParameter
    String sql = USER_GROUP_CHECK_QUERY;
    String orderBy = "";
    String sort = "";
    Iterator<Order> i = pageable.getSort().iterator();
    while (i.hasNext()) {
      Order order = i.next();
      orderBy = order.getProperty();
      sort = order.getDirection().name();
    }
    sql = sql.replace(ORDER_BY, orderBy);
    sql = sql.replace(SORT, sort);
    Query query = entityManager.createNativeQuery(sql);
    query.setParameter(GROUP_ID, groupId);
    query.setParameter(LIMIT, pageable.getPageSize());
    query.setParameter(OFFSET, pageable.getOffset());

    List<UserGroupCheck> result = JpaUtil.getResultList(query, UserGroupCheck.class);
    Page<UserGroupCheck> page = new PageImpl<>(result, pageable, total);

    return page;
  }
  @Override
  public List<Productos> productosLike(String par, String id) {
    /*  String jpql = "SELECT EX.productos FROM Existencias EX "
    + " WHERE EX.bodegas.idBodega = '59' AND EX.productos.idProducto LIKE :par"
    + " OR EX.productos.nombre LIKE :par";*/
    String jpql =
        "SELECT PR FROM Productos PR where PR.idProducto LIKE :par OR PR.nombre LIKE :par";

    Query query = em.createQuery(jpql);

    String jpql1 =
        "select DISTINCT pro.IdProducto, pro.Nombre from bodegaVirtualMM vb inner join Productos pro on vb.IdProducto = pro.IdProducto and vb.IdVendedor = ? where   pro.IdProducto = ? or pro.Nombre like ? ";
    //   String jpql1 =   "select DISTINCT pro.IdProducto, pro.Nombre from bodegaVirtualMM vb inner
    // join Productos pro on vb.IdProducto = pro.IdProducto and vb.IdVendedor = ? order by
    // pro.Nombre";

    Query query2 = em.createNativeQuery(jpql1, Productos.class);
    query2.setParameter(1, id);
    query2.setParameter(2, par);
    query2.setParameter(3, "%" + par + "%");
    // query2.setParameter(1,  "%"+ par +"%");
    //  query2.setParameter("par", "%"+ par +"%");
    productosLikeList = query2.getResultList();
    return productosLikeList;

    /* String jpql =   "select us.UID, us.Nombre  as 'NombreUsuario' , pm.Nombre  as 'NombrePerfil', pm.Descripcion  as 'DescripcionPerfil' ,\n" +
    " am.Nombre  as NombreApp, am.Descripcion as 'DescripcionMovil' , am.Logo from Usuarios us inner join PerfilesMoviles pm \n" +
    "on us.IdPerfilMovil = pm.IdPerfilMovil inner join AppPorPerfiles ap on us.IdPerfilMovil = ap.IdPerfilMovil\n" +
    "inner join AppMoviles am on ap.IdAppMovil = am.IdAppMovil\n" +
    "where us.UID = :par" ;*/

  }
 public void deleteMessageFor(final Long id, final User user) {
   final Query query =
       entityManager.createNativeQuery(
           "update MESSAGES SET deleted_by=:user_id where ID=:id and deleted_by IS NULL and receiver_id <> sender_id and (receiver_id=:user_id or sender_id=:user_id)");
   query.setParameter("user_id", user.getId());
   query.setParameter("id", id);
   final int updated = query.executeUpdate();
   if (updated == 0) {
     final Query query2 =
         entityManager.createNativeQuery(
             "delete from MESSAGES where ID=:id and (receiver_id=:user_id or sender_id=:user_id)");
     query2.setParameter("id", id);
     query2.setParameter("user_id", user.getId());
     query2.executeUpdate();
   }
 }
  public List<Object[]> cantidadEmpleados(List<Evaluacion> eval, Dependencia dep) {
    List<Object[]> res = null;
    String cadena = eval.get(0).getId().toString();
    for (int i = 1; i < eval.size(); i++) {

      cadena += "," + eval.get(i).getId().toString();
    }

    StringBuffer s =
        new StringBuffer("select count(v.ID_EMPLEADO),v.ID_EVALUACION")
            .append(" from EVA.EVALUACION_EMPLEADO v join empleado e on(v.ID_EMPLEADO=e.ID) ")
            .append(
                " join dependencia d on(d.ID=e.ID_DEPENDENCIA) join evaluacion c on (v.ID_EVALUACION=c.ID)")
            .append(" where id_evaluacion in(")
            .append(cadena)
            .append(")  and (d.id=")
            .append(dep.getId().toString())
            .append(" or d.ID in(select id from dependencia where ID_DEPENDENCIA_PADRE=")
            .append(dep.getId().toString())
            .append(")) group by v.ID_EVALUACION order by v.ID_EVALUACION");
    try {
      res = em.createNativeQuery(s.toString()).getResultList();

    } catch (Exception e) {
      return null;
    }

    return res;
  }
 private void separatePrivateMessagesFromWorkout(final Long id) {
   final Query query =
       entityManager.createNativeQuery(
           "UPDATE MESSAGES SET WORKOUT_ID=NULL WHERE WORKOUT_ID=:id AND RECEIVER_ID IS NOT NULL");
   query.setParameter("id", id);
   query.executeUpdate();
 }
  @SuppressWarnings("unchecked")
  @GET
  @Path("/neighborhood")
  @Produces("application/json;charset=UTF-8")
  public Map<String, List<String>> registerNeighborhoodsToNewCity(
      @QueryParam("state") String state, @QueryParam("cityName") String cityName) {
    StringBuilder query = new StringBuilder();
    query.append("(SELECT CAST(endereco.bairro AS char) AS b ");
    query.append("FROM ");
    query.append("CEP.");
    query.append(state.toLowerCase());
    query.append(" endereco ");
    query.append("WHERE endereco.cidade = :cityName) ");
    query.append("UNION ");
    query.append("(SELECT CAST(bairro.nome AS char) AS b ");
    query.append("FROM CEP2.bairros bairro ");
    query.append("JOIN CEP2.cidades cidade ON cidade.id = bairro.cidade ");
    query.append("WHERE cidade.nome = :cityName) ");
    query.append("ORDER BY b ASC ");

    List<String> neighborhoodsList =
        em.createNativeQuery(query.toString()).setParameter("cityName", cityName).getResultList();

    Map<String, List<String>> neighborhoods = new HashMap<String, List<String>>();
    neighborhoods.put("neighborhoods", neighborhoodsList);

    return neighborhoods;
  }
 private void setH2LockTimeout() {
   EntityManager manager = entityManagerFactory.createEntityManager();
   manager.getTransaction().begin();
   manager.createNativeQuery("SET DEFAULT_LOCK_TIMEOUT " + locktimeout).executeUpdate();
   manager.getTransaction().commit();
   manager.close();
 }
Beispiel #28
0
  @Override
  public List getSome() {
    // TODO Auto-generated method stub

    return em.createNativeQuery("findLastFiveTransaction", "BalanceWithCategoryType")
        .getResultList();
  }
Beispiel #29
0
  /**
   * 查询类别包含数量
   *
   * @param parentId
   * @param error
   * @return
   */
  public static List<v_news_types> queryTypeAndCount(long parentId, ErrorInfo error) {
    error.clear();

    List<v_news_types> types = new ArrayList<v_news_types>();
    StringBuffer sql = new StringBuffer("");
    sql.append(SQLTempletes.SELECT);
    sql.append(SQLTempletes.V_NEWS_TYPES);
    sql.append(" and parent_id = ? and status = true order by _order");

    try {
      // types = v_news_types.find("parent_id = ? and status = true order by _order",
      // parentId).fetch();
      EntityManager em = JPA.em();
      Query query = em.createNativeQuery(sql.toString(), v_news_types.class);
      query.setParameter(1, parentId);
      types = query.getResultList();

    } catch (Exception e) {
      e.printStackTrace();
      error.code = -1;
      error.msg = "查询类别失败";
      return null;
    }

    error.code = 0;

    return types;
  }
  public void popularity(ShopRoomTarriffCorporateRateView item) {
    ShopRoomTarriffCorporateRateView shoproomtarriffcorporaterateview = null;
    if (item != null) {
      shoproomtarriffcorporaterateview = findById(item.getId(), false);
    }

    if (shoproomtarriffcorporaterateview != null) {
      if (SObject.class.isAssignableFrom(ShopRoomTarriffCorporateRateView.class)) {
        if (shoproomtarriffcorporaterateview.getPopularity() == null) {
          shoproomtarriffcorporaterateview.setPopularity(-1);
        }
        shoproomtarriffcorporaterateview.setPopularity(
            shoproomtarriffcorporaterateview.getPopularity() + 1);

        try {
          em.createNativeQuery(
                  String.format(updatePopularityClause, tableName),
                  ShopRoomTarriffCorporateRateView.class)
              .setParameter("popularity", shoproomtarriffcorporaterateview.getPopularity())
              .setParameter("id", item.getId())
              .executeUpdate();
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }
  }