Example usage for org.hibernate Session createSQLQuery

Introduction

In this page you can find the example usage for org.hibernate Session createSQLQuery.

Prototype

@Override
    NativeQuery createSQLQuery(String queryString);

Source Link

Usage

From source file:com.formkiq.core.dao.UserDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override/*from w  ww. j  av  a2  s .c  o m*/
public void deleteUserAccessToken(final String email) {
    String sql = "delete from oauth_access_token where " + " user_name=:user";

    Session session = getEntityManager().unwrap(Session.class);

    session.createSQLQuery(sql).setParameter("user", email).executeUpdate();
}

---

From source file:com.formkiq.core.dao.UserDaoImpl.java

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from  w w w.java 2 s .c o  m*/
public UserNotificationListDTO findNotifications(final User user, final String token) {

    int offset = Strings.getOffset(token);
    int max = Strings.getMaxResults(token, DEFAULT_MAX_RESULTS);

    StringBuilder sql = new StringBuilder(
            "select user_notifications_id as uuid, " + "data->'workflow'->>'label1' as \"workflow.label1\", "
                    + "data->'workflow'->>'label2' as \"workflow.label2\", "
                    + "data->'workflow'->>'label3' as \"workflow.label3\", " + "method as method, "
                    + "inserted_date as inserteddate " + "from user_notifications " + "where user_id=:user "
                    + "order by inserted_date, method desc ");

    sql.append(" OFFSET " + offset + " FETCH FIRST " + (max + 1) + " ROWS ONLY");

    Session session = getEntityManager().unwrap(Session.class);

    List<Map<String, Object>> list = session.createSQLQuery(sql.toString())
            .addScalar("uuid", StringType.INSTANCE).addScalar("workflow.label1", StringType.INSTANCE)
            .addScalar("workflow.label2", StringType.INSTANCE).addScalar("workflow.label3", StringType.INSTANCE)
            .addScalar("method", EnumCustomType.transform(NotificationMethod.class))
            .addScalar("inserteddate", TimestampType.INSTANCE)
            .setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE)
            .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();

    UserNotificationListDTO dto = new UserNotificationListDTO();

    List<UserNotificationDTO> unlist = transformMapListToObject(list, UserNotificationDTO.class);

    List<UserNotificationDTO> tr = updatePagination(dto, offset, max, unlist);

    dto.setNotifications(tr);

    return dto;
}

---

From source file:com.formkiq.core.dao.UserDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override/*w  w w .  j  av  a 2  s  .co  m*/
public UserDTO findUserDTO(final String email) {

    String sql = "select u.user_id as userid, u.email as email, " + " u.password as password, "
            + " u.status as status, u.role as role, " + " u.lastlogin_date as lastlogin, "
            + " u.last_user_agent as lastuseragent, " + " u.login_token as logintoken " + " from Users u"
            + " where u.email=:email";

    Session session = getEntityManager().unwrap(Session.class);

    UserDTO dto = (UserDTO) session.createSQLQuery(sql).addScalar("userid", StringType.INSTANCE)
            .addScalar("email", StringType.INSTANCE).addScalar("password", StringType.INSTANCE)
            .addScalar("status", StringType.INSTANCE).addScalar("role", StringType.INSTANCE)
            .addScalar("lastlogin", TimestampType.INSTANCE).addScalar("lastuseragent", StringType.INSTANCE)
            .addScalar("logintoken", StringType.INSTANCE).setParameter("email", email)
            .setResultTransformer(new AliasToBeanResultTransformer(UserDTO.class)).uniqueResult();

    if (dto != null) {

        List<FolderDTO> folders = this.folderDao.findFoldersDTO(email, FolderStatus.ACTIVE);
        dto.setFolders(folders);

        UUID userid = dto.getUUID();
        Collection<UserSetting> settings = findUserSettings(userid);

        Map<String, String> map = new HashMap<>();
        for (UserSetting us : settings) {
            map.put(us.getSetting().name(), us.getValue());
        }
        dto.setSettings(map);
    }

    return dto;
}

---

From source file:com.formkiq.core.dao.UserDaoImpl.java

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*from ww w .ja va2 s.  c om*/
public UserListDTO findUsers(final String token, final String text) {

    int offset = Strings.getOffset(token);
    int max = Strings.getMaxResults(token, DEFAULT_MAX_RESULTS);

    StringBuilder sql = new StringBuilder("select u.user_id as userid, " + " u.email as email, "
            + " u.status as status, " + " u.role as role, " + " u.lastlogin_date as lastlogin, "
            + " u.last_user_agent as lastuseragent " + " from Users u ");

    if (StringUtils.hasText(text)) {
        sql.append("where u.email like :text ");
    }

    sql.append("order by u.email");

    sql.append(" OFFSET " + offset + " FETCH FIRST " + (max + 1) + " ROWS ONLY");

    Session session = getEntityManager().unwrap(Session.class);

    org.hibernate.Query query = session.createSQLQuery(sql.toString()).addScalar("userid", StringType.INSTANCE)
            .addScalar("email", StringType.INSTANCE).addScalar("status", StringType.INSTANCE)
            .addScalar("role", StringType.INSTANCE).addScalar("lastlogin", TimestampType.INSTANCE)
            .addScalar("lastuseragent", StringType.INSTANCE)
            .setResultTransformer(new AliasToBeanResultTransformer(UserDTO.class));

    if (StringUtils.hasText(text)) {
        query.setParameter("text", "%" + text + "%");
    }

    List<UserDTO> list = query.list();

    UserListDTO dto = new UserListDTO();

    List<UserDTO> truncated = updatePagination(dto, offset, max, list);
    dto.setUsers(truncated);

    return dto;
}

---

From source file:com.formkiq.core.dao.UserDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override/*from   w  ww. j a  v a2  s  . co  m*/
public int getAdminUserCount() {
    String sql = "select count(*) as count from users where role=:role";
    Session session = getEntityManager().unwrap(Session.class);

    Integer count = (Integer) session.createSQLQuery(sql).addScalar("count", IntegerType.INSTANCE)
            .setParameter("role", UserRole.ROLE_ADMIN.name()).uniqueResult();
    return count.intValue();
}

---

From source file:com.formkiq.core.dao.UserDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override//from   w  w  w . j ava 2s .  c  om
public void updateLastLogin(final String email, final Date date) {
    String sql = "update users set lastlogin_date=:date where email=:email";
    Session session = getEntityManager().unwrap(Session.class);

    session.createSQLQuery(sql).setParameter("date", date).setParameter("email", email).executeUpdate();
}

---

From source file:com.formkiq.core.dao.UserDaoImpl.java

License:Apache License

@SuppressWarnings("resource")
@Override//from www . java 2s. c o m
public void updateLastUserAgent(final String email, final String useragent) {

    String sql = "update users set last_user_agent=:agent " + "where email=:email";
    Session session = getEntityManager().unwrap(Session.class);

    session.createSQLQuery(sql).setParameter("agent", useragent).setParameter("email", email).executeUpdate();
}

---

From source file:com.gisgraphy.domain.repository.CityDao.java

License:Open Source License

@SuppressWarnings({ "rawtypes", "unchecked" })
public int fixPolygons() {
    return (Integer) this.getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(Session session) throws PersistenceException {
            session.flush();/*from  w ww  .  j  a  v  a2 s.c o m*/
            logger.info("will fix polygons for city ");
            String fixPolygon = "update city set shape=ST_MakePolygon(shape) where ST_GeometryType(shape)='ST_LineString'";
            Query fixPolygonQuery = session.createSQLQuery(fixPolygon);
            int nbModify = fixPolygonQuery.executeUpdate();

            return nbModify;
        }
    });

}

---

From source file:com.gisgraphy.domain.repository.GenericGisDao.java

License:Open Source License

/**
 * base method for all findNearest* //from   www. j  a  va2 s. c  o m
 * 
 * @param point
 *                The point from which we want to find GIS Object
 * @param pointId
 *                the id of the point that we don't want to be include, it
 *                is used to not include the gisFeature from which we want
 *                to find the nearest
 * @param distance
 *                distance The radius in meters
 * @param firstResult
 *                the firstResult index (for pagination), numbered from 1,
 *                if < 1 : it will not be taken into account
 * @param maxResults
 *                The Maximum number of results to retrieve (for
 *                pagination), if <= 0 : it will not be taken into acount
 * @param requiredClass
 *                the class of the object to be retireved
 * @param isMunicipality whether we should filter on city that are flag as 'municipality'.
          act as a filter, if false it doesn't filters( false doesn't mean that we return non municipality)
 * @return A List of GisFeatureDistance with the nearest elements or an
 *         emptylist (never return null), ordered by distance.<u>note</u>
 *         the specified gisFeature will not be included into results
 * @see GisFeatureDistance
 * @return a list of gisFeature (never return null but an empty list)
 */
@SuppressWarnings("unchecked")
protected List<GisFeatureDistance> getNearestAndDistanceFrom(final Point point, final Long pointId,
        final double distance, final int firstResult, final int maxResults, final boolean includeDistanceField,
        final Class<? extends GisFeature> requiredClass, final boolean isMunicipality) {
    Assert.notNull(point);
    return (List<GisFeatureDistance>) this.getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(Session session) throws PersistenceException {
            Criteria criteria = session.createCriteria(requiredClass);

            if (maxResults > 0) {
                criteria = criteria.setMaxResults(maxResults);
            }
            if (firstResult >= 1) {
                criteria = criteria.setFirstResult(firstResult - 1);
            }
            criteria = criteria.add(new DistanceRestriction(point, distance));
            List<String> fieldList = IntrospectionHelper.getFieldsAsList(requiredClass);
            ProjectionList projections = ProjectionBean.fieldList(fieldList, true);
            if (includeDistanceField) {
                projections.add(SpatialProjection.distance_sphere(point, GisFeature.LOCATION_COLUMN_NAME)
                        .as("distance"));
            }
            criteria.setProjection(projections);
            if (pointId != 0) {
                // remove The From Point
                criteria = criteria.add(Restrictions.not(Restrictions.idEq(pointId)));
            }
            if (includeDistanceField) {
                criteria.addOrder(new ProjectionOrder("distance"));
            }
            if (isMunicipality && (requiredClass == City.class || requiredClass == GisFeature.class)) {
                criteria.add(Restrictions.eq(City.MUNICIPALITY_FIELD_NAME, isMunicipality));
            }

            criteria.setCacheable(true);
            List<Object[]> queryResults = criteria.list();

            String[] aliasList;
            if (includeDistanceField) {
                aliasList = (String[]) ArrayUtils.add(IntrospectionHelper.getFieldsAsArray(requiredClass),
                        "distance");
            } else {
                aliasList = IntrospectionHelper.getFieldsAsArray(requiredClass);
            }
            int idPropertyIndexInAliasList = 0;
            for (int i = 0; i < aliasList.length; i++) {
                if (aliasList[i] == "id") {
                    idPropertyIndexInAliasList = i;
                    break;
                }
            }

            boolean hasZipCodesProperty = ZipCodesAware.class.isAssignableFrom(requiredClass);
            Map<Long, Set<String>> idToZipCodesMap = null;
            if (hasZipCodesProperty && queryResults.size() > 0) {
                List<Long> ids = new ArrayList<Long>();
                for (Object[] tuple : queryResults) {
                    ids.add((Long) tuple[idPropertyIndexInAliasList]);
                }
                String zipCodeQuery = "SELECT code as code,gisfeature as id FROM "
                        + ZipCode.class.getSimpleName().toLowerCase() + " zip where zip.gisfeature in (:ids)";
                Query qry = session.createSQLQuery(zipCodeQuery).addScalar("code", Hibernate.STRING)
                        .addScalar("id", Hibernate.LONG);
                qry.setCacheable(true);

                qry.setParameterList("ids", ids);
                List<Object[]> zipCodes = (List<Object[]>) qry.list();

                if (zipCodes.size() > 0) {
                    idToZipCodesMap = new HashMap<Long, Set<String>>();
                    for (Object[] zipCode : zipCodes) {
                        Long idFromZipcode = (Long) zipCode[1];
                        Set<String> zipCodesFromMap = idToZipCodesMap.get(idFromZipcode);
                        if (zipCodesFromMap == null) {
                            Set<String> zipCodesToAdd = new HashSet<String>();
                            idToZipCodesMap.put(idFromZipcode, zipCodesToAdd);
                            zipCodesFromMap = zipCodesToAdd;
                        }
                        zipCodesFromMap.add((String) zipCode[0]);
                    }
                }
            }
            List<GisFeatureDistance> results = ResultTransformerUtil.transformToGisFeatureDistance(aliasList,
                    queryResults, idToZipCodesMap, requiredClass);
            return results;
        }
    });

}

---

From source file:com.gisgraphy.domain.repository.GenericGisDao.java

License:Open Source License

public void createGISTIndexForLocationColumn() {
    this.getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(Session session) throws PersistenceException {
            session.flush();/*from ww  w .j  a v  a2  s.c om*/

            logger.info("will create GIST index for  " + persistentClass.getSimpleName());
            String locationIndexName = "locationIndex" + persistentClass.getSimpleName();
            logger.info("checking if " + locationIndexName + " exists");
            String checkingLocationIndex = "SELECT 1 FROM   pg_class c  JOIN   pg_namespace n ON n.oid = c.relnamespace WHERE  c.relname = '"
                    + locationIndexName + "'";
            Query checkingLocationIndexQuery = session.createSQLQuery(checkingLocationIndex);
            Object locationIndexExists = checkingLocationIndexQuery.uniqueResult();
            if (locationIndexExists != null) {
                logger.info("will create GIST index for  the " + OpenStreetMap.SHAPE_COLUMN_NAME + " column");
                String createIndex = "CREATE INDEX " + locationIndexName + " ON "
                        + persistentClass.getSimpleName().toLowerCase() + " USING GIST (location)";
                Query createIndexQuery = session.createSQLQuery(createIndex);
                createIndexQuery.executeUpdate();
            } else {
                logger.info("won't create GIST index for " + persistentClass.getSimpleName()
                        + " because it already exists");
            }

            return null;
        }
    });
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »