Example usage for org.hibernate.criterion Restrictions sqlRestriction

List of usage examples for org.hibernate.criterion Restrictions sqlRestriction

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.criterion.*
  5. Restrictions
  6. sqlRestriction

Introduction

In this page you can find the example usage for org.hibernate.criterion Restrictions sqlRestriction.

Prototype

public static Criterion sqlRestriction(String sql)

Source Link

Document

Apply a constraint expressed in SQL with no JDBC parameters.

Usage

From source file:com.viettel.vsaadmin.database.DAOHibernate.DepartmentDAOHE.java

License:Open Source License

/**
 *
 * @return//from  www  .  j  ava  2  s  . com
 */
public List<Department> getParentDeptForTree(List<Long> listDeptId, String... type) {
    Criterion[] criterion = new Criterion[4];
    criterion[0] = Restrictions.isNull("parentId");
    criterion[1] = Restrictions.in("deptId", listDeptId);
    criterion[2] = Restrictions.eq("status", (short) 1);
    criterion[3] = Restrictions
            .sqlRestriction(" 1 = 1 order by nlssort(lower({alias}.DEPT_NAME),'nls_sort = Vietnamese') ");
    return this.findByCriteria(0, -1, criterion);
}

From source file:com.viettel.vsaadmin.database.DAOHibernate.DepartmentDAOHE.java

License:Open Source License

/**
 *
 * @return/*from  w  w w .  jav  a 2 s .c o m*/
 */
public List<Department> getDeptListByParentId(Long parentId, Boolean isCheckStatus) {

    Criterion[] criterion = new Criterion[3];
    criterion[0] = Restrictions.eq("parentId", parentId);
    if (isCheckStatus) {
        criterion[1] = Restrictions.eq("status", 1L);
    } else {
        criterion[1] = Restrictions.isNotNull("deptId");
    }
    //            criterion[1] = Restrictions.ne(idField, entityId);
    //criteria.addOrder(Order.asc("name"));
    criterion[2] = Restrictions
            .sqlRestriction(" 1 = 1 order by nlssort(lower({alias}.DEPT_NAME),'nls_sort = Vietnamese') ");
    return this.findByCriteria(0, -1, criterion);
}

From source file:com.viettel.vsaadmin.database.DAOHibernate.UsersDAOHE.java

License:Open Source License

/**
 * lay danh sach user cua don vi// w w  w .ja  v  a  2 s . co m
 *
 * @param deptId
 * @return
 */
public List getListUserOfDept(Long deptId) throws Exception {
    Criterion[] criterion = new Criterion[2];
    criterion[0] = Restrictions.eq("deptId", deptId);
    criterion[1] = Restrictions.sqlRestriction(" 1 = 1 order by {alias}.USER_NAME ");
    return this.findByCriteria(0, -1, criterion);
}

From source file:com.wavemaker.runtime.data.task.SearchTask.java

License:Open Source License

protected Criteria getCritieria(Session session, Object rootSearchObject, final QueryOptions options,
        String dbName) {//from  w  w w .  j  a v  a 2s.c  om

    final DataServiceMetaData metaData = getMetaData(dbName);

    Criteria rootCriteria = null;

    final Map<String, Criteria> criterias = new ImmutableEntryMap<String, Criteria>();

    if (rootSearchObject instanceof Class) {
        rootCriteria = initCriteriasMap(criterias, (Class<?>) rootSearchObject, session);
    } else {

        rootCriteria = initCriteriasMap(criterias, rootSearchObject.getClass(), session);

        addExample(rootSearchObject, rootCriteria, options);

        handleIdInSearch(rootSearchObject, rootCriteria, options, metaData);

        ObjectGraphTraversal.ObjectVisitor ov = new ObjectGraphTraversal.ObjectVisitor() {

            @Override
            public void cycle(Object o, Context ctx) {
            }

            @Override
            public void visit(Object queryInstance, Context ctx) {

                String propertyName = ctx.getProperties().get(0);

                if (DataServiceLoggers.taskLogger.isDebugEnabled()) {
                    DataServiceLoggers.taskLogger.debug("Adding criteria on property " + propertyName);
                }

                Criteria parentCriteria = (Criteria) ctx.getValues().get(1);

                if (DataServiceLoggers.taskLogger.isDebugEnabled()) {
                    DataServiceLoggers.taskLogger.debug("Parent criteria is " + parentCriteria);
                }

                Criteria c = parentCriteria.createCriteria(propertyName);

                String k = ObjectUtils.toString(ctx.getProperties(), ServiceConstants.PROPERTY_SEP);

                criterias.put(k, c);

                ctx.getValues().set(0, c);

                addExample(queryInstance, c, options);
                handleIdInSearch(queryInstance, c, options, metaData);
            }
        };

        ObjectGraphTraversal tr = DataServiceUtils.getRelatedTraversal(ov, getObjectAccess(), metaData, false);

        tr.traverse(rootSearchObject, rootCriteria);
    }

    if (addOrder()) {
        applyOrderBy(options, criterias);
    }

    for (String s : options.getSqlRestrictions()) {
        rootCriteria.add(Restrictions.sqlRestriction(s));
    }

    return rootCriteria;
}

From source file:com.yahoo.elide.datastores.hibernate3.filter.CriterionFilterOperation.java

License:Apache License

@Override
public Criterion apply(FilterPredicate filterPredicate) {
    List<FilterPredicate.PathElement> path = filterPredicate.getPath();

    /* If the predicate refers to a nested association, the restriction should be 'alias.fieldName' */
    String alias;/*from w  ww  .j  a  va2 s  . c o  m*/
    if (path.size() > 1) {
        alias = getAlias(path);
        alias = alias + "." + path.get(path.size() - 1).getFieldName();
        /* If the predicate refers to the root entity, the restriction should be 'fieldName' */
    } else {
        alias = path.get(0).getFieldName();
    }

    switch (filterPredicate.getOperator()) {
    case IN:
        if (filterPredicate.getValues().isEmpty()) {
            return Restrictions.sqlRestriction("(false)");
        }
        return Restrictions.in(alias, filterPredicate.getValues());
    case NOT:
        if (filterPredicate.getValues().isEmpty()) {
            return Restrictions.sqlRestriction("(true)");
        }
        return Restrictions.not(Restrictions.in(alias, filterPredicate.getValues()));
    case PREFIX:
        return Restrictions.like(alias,
                filterPredicate.getStringValueEscaped(SPECIAL_CHARACTER, ESCAPE_CHARACTER)
                        + MATCHALL_CHARACTER);
    case PREFIX_CASE_INSENSITIVE:
        return Restrictions.ilike(alias,
                filterPredicate.getStringValueEscaped(SPECIAL_CHARACTER, ESCAPE_CHARACTER)
                        + MATCHALL_CHARACTER);
    case POSTFIX:
        return Restrictions.like(alias, MATCHALL_CHARACTER
                + filterPredicate.getStringValueEscaped(SPECIAL_CHARACTER, ESCAPE_CHARACTER));
    case POSTFIX_CASE_INSENSITIVE:
        return Restrictions.ilike(alias, MATCHALL_CHARACTER
                + filterPredicate.getStringValueEscaped(SPECIAL_CHARACTER, ESCAPE_CHARACTER));
    case INFIX:
        return Restrictions.like(alias,
                MATCHALL_CHARACTER + filterPredicate.getStringValueEscaped(SPECIAL_CHARACTER, ESCAPE_CHARACTER)
                        + MATCHALL_CHARACTER);
    case INFIX_CASE_INSENSITIVE:
        return Restrictions.ilike(alias,
                MATCHALL_CHARACTER + filterPredicate.getStringValueEscaped(SPECIAL_CHARACTER, ESCAPE_CHARACTER)
                        + MATCHALL_CHARACTER);
    case ISNULL:
        return Restrictions.isNull(alias);
    case NOTNULL:
        return Restrictions.isNotNull(alias);
    case LT:
        return Restrictions.lt(alias, filterPredicate.getValues().get(0));
    case LE:
        return Restrictions.le(alias, filterPredicate.getValues().get(0));
    case GT:
        return Restrictions.gt(alias, filterPredicate.getValues().get(0));
    case GE:
        return Restrictions.ge(alias, filterPredicate.getValues().get(0));
    case TRUE:
        return Restrictions.sqlRestriction("(true)");
    case FALSE:
        return Restrictions.sqlRestriction("(false)");
    default:
        throw new InvalidPredicateException("Operator not implemented: " + filterPredicate.getOperator());
    }
}

From source file:Contabilidad.RCuentas.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    // TODO add your handling code here:
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {//from  w  w  w.j a  va  2 s  .  c  o  m
        valores = lista.getSelectedValues();
        session.beginTransaction();
        if (cb.getSelectedItem().toString().compareTo("FACTURA") == 0) {
            Criteria consulta = session.createCriteria(Factura.class);
            if (valores.length > 0)
                consulta.add(Restrictions.in("estatus", valores));
            String campo = "fecha";
            if (cb_fecha.getSelectedItem().toString().compareToIgnoreCase("Estatus") == 0)
                campo = "f_estatus";
            if (t_fecha3.getText().compareTo("AAAA-MM-DD") != 0) {
                consulta.add(Restrictions.sqlRestriction(
                        "STR_TO_DATE(" + campo + ", '%Y-%m-%d') >= '" + t_fecha3.getText() + "'"));
            }
            if (t_fecha4.getText().compareTo("AAAA-MM-DD") != 0) {
                consulta.add(Restrictions.sqlRestriction(
                        "STR_TO_DATE(" + campo + ", '%Y-%m-%d') <= '" + t_fecha4.getText() + "'"));
            }
            Factura[] facturas = (Factura[]) consulta.list().toArray(new Factura[0]);
            model = (DefaultTableModel) t_datos.getModel();
            if (facturas.length > 0) {
                model.getDataVector().removeAllElements();
                for (int x = 0; x < facturas.length; x++) {
                    Factura actor = (Factura) facturas[x];
                    Object[] renglon = new Object[11];

                    //"ID", "FECHA", "RFC", "R. SOCIAL", "F. FISCAL", "SERIE", "FOLIO", "MONTO", "ESTATUS", "F. ESTATUS", "NOTAS"
                    renglon[0] = "" + actor.getIdFactura();
                    if (actor.getFechaFiscal() != null)
                        renglon[1] = actor.getFechaFiscal();
                    else
                        renglon[1] = "";
                    renglon[2] = actor.getRfcReceptor();
                    renglon[3] = actor.getNombreReceptor();
                    renglon[4] = actor.getFFiscal();
                    renglon[5] = actor.getSerie();
                    renglon[6] = actor.getFolio();
                    Concepto[] ren = (Concepto[]) actor.getConceptos().toArray(new Concepto[0]);
                    BigDecimal total = new BigDecimal("0.0");
                    for (int w = 0; w < actor.getConceptos().size(); w++) {
                        BigDecimal cantidad = new BigDecimal("" + ren[w].getCantidad());
                        BigDecimal precio = new BigDecimal("" + ren[w].getPrecio());
                        BigDecimal descuento = new BigDecimal("" + ren[w].getDescuento());
                        descuento = descuento.divide(new BigDecimal("100"));
                        BigDecimal subtotal = cantidad.multiply(precio).setScale(2, BigDecimal.ROUND_HALF_UP);
                        BigDecimal resta = subtotal.multiply(descuento).setScale(2, BigDecimal.ROUND_HALF_UP);
                        total = total.add(subtotal.subtract(resta)).setScale(2, BigDecimal.ROUND_HALF_UP);
                    }
                    BigDecimal iva = new BigDecimal("" + actor.getIva());
                    iva = iva.divide(new BigDecimal("100"));
                    total = total.add(total.multiply(iva));
                    //Double.parseDouble(total.setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                    renglon[7] = total.setScale(2, RoundingMode.HALF_UP).doubleValue();

                    renglon[8] = actor.getEstatus();
                    if (actor.getfEstatus() != null)
                        renglon[9] = actor.getfEstatus().toString();
                    else
                        renglon[9] = "";
                    renglon[10] = actor.getTexto();
                    model.addRow(renglon);
                }
            } else
                model.getDataVector().removeAllElements();
        } else {
            Criteria consulta = session.createCriteria(Nota.class);
            if (valores.length > 0)
                consulta.add(Restrictions.in("estatus", valores));
            String campo = "fecha";
            if (cb_fecha.getSelectedItem().toString().compareToIgnoreCase("Estatus") == 0)
                campo = "f_estatus";
            if (t_fecha3.getText().compareTo("AAAA-MM-DD") != 0) {
                consulta.add(Restrictions.sqlRestriction(
                        "STR_TO_DATE(" + campo + ", '%Y-%m-%d') >= '" + t_fecha3.getText() + "'"));
            }
            if (t_fecha4.getText().compareTo("AAAA-MM-DD") != 0) {
                consulta.add(Restrictions.sqlRestriction(
                        "STR_TO_DATE(" + campo + ", '%Y-%m-%d') <= '" + t_fecha4.getText() + "'"));
            }
            Nota[] nota = (Nota[]) consulta.list().toArray(new Nota[0]);
            model = (DefaultTableModel) t_datos.getModel();
            if (nota.length > 0) {
                model.getDataVector().removeAllElements();
                for (int x = 0; x < nota.length; x++) {
                    Nota actor = (Nota) nota[x];
                    Object[] renglon = new Object[11];

                    //"ID", "FECHA", "RFC", "R. SOCIAL", "F. FISCAL", "SERIE", "FOLIO", "MONTO", "ESTATUS", "F. ESTATUS", "NOTAS"
                    renglon[0] = "" + actor.getIdNota();
                    if (actor.getFechaFiscal() != null)
                        renglon[1] = actor.getFechaFiscal();
                    else
                        renglon[1] = "";
                    renglon[2] = actor.getRfcReceptor();
                    renglon[3] = actor.getNombreReceptor();
                    renglon[4] = actor.getFFiscal();
                    renglon[5] = actor.getSerie();
                    renglon[6] = actor.getFolio();
                    Concepto[] ren = (Concepto[]) actor.getConceptos().toArray(new Concepto[0]);
                    BigDecimal total = new BigDecimal("0.0");
                    for (int w = 0; w < actor.getConceptos().size(); w++) {
                        BigDecimal cantidad = new BigDecimal("" + ren[w].getCantidad());
                        BigDecimal precio = new BigDecimal("" + ren[w].getPrecio());
                        BigDecimal descuento = new BigDecimal("" + ren[w].getDescuento());
                        descuento = descuento.divide(new BigDecimal("100"));
                        BigDecimal subtotal = cantidad.multiply(precio).setScale(2, BigDecimal.ROUND_HALF_UP);
                        BigDecimal resta = subtotal.multiply(descuento).setScale(2, BigDecimal.ROUND_HALF_UP);
                        total = total.add(subtotal.subtract(resta)).setScale(2, BigDecimal.ROUND_HALF_UP);
                    }
                    BigDecimal iva = new BigDecimal("" + actor.getIva());
                    iva = iva.divide(new BigDecimal("100"));
                    total = total.add(total.multiply(iva));
                    //Double.parseDouble(total.setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                    renglon[7] = total.setScale(2, RoundingMode.HALF_UP).doubleValue();

                    renglon[8] = actor.getEstatus();
                    if (actor.getfEstatus() != null)
                        renglon[9] = actor.getfEstatus().toString();
                    else
                        renglon[9] = "";
                    renglon[10] = actor.getTexto();
                    model.addRow(renglon);
                }
            } else
                model.getDataVector().removeAllElements();
        }
        t_datos.revalidate();
    } catch (Exception e) {
        e.printStackTrace();
    }
    if (session != null)
        if (session.isOpen())
            session.close();
}

From source file:controller.CategoriaEmpresaController.java

public void buscar() {
    SessionFactory sf = HibernateUtil.getSessionFactory();
    Session s = sf.openSession();/*from   w  ww .j  a v  a2s. co  m*/

    Criteria c = s.createCriteria(CategoriaEmpresa.class);
    c.addOrder(Order.desc("idCategoriaEmpresa"));
    c.add(Restrictions.sqlRestriction("limit 1"));

}

From source file:cruds.PortofolioforuserCrudImplementation.java

@Override
public ArrayList<Portofolioforuser> selectPortofolios(Category cat) {
    List<Portofolioforuser> portofolios = new ArrayList<>();
    try {/*  ww w .  j  a  v  a2  s .c  o m*/
        sc.beginTransaction();
        Criteria cr = sc.createCriteria(Portofolioforuser.class);
        cr.add(Restrictions.eq("category.categoryId", cat.getCategoryId()));
        cr.add(Restrictions.sqlRestriction("1=1 order by rand()"));
        portofolios = cr.list();
        sc.getTransaction().commit();
    } catch (HibernateException e) {
        e.printStackTrace();
    } finally {
        sc.close();
    }

    return (ArrayList<Portofolioforuser>) portofolios;
}

From source file:cruds.ProjectCrudImplementation.java

@Override
public ArrayList<Projectsforusers> selectProjects(int id) {
    List<Projectsforusers> projects = new ArrayList<>();
    Criteria criteria = sc.createCriteria(Projectsforusers.class);
    criteria.add(Restrictions.eq("users.userId", id));
    criteria.add(Restrictions.sqlRestriction("1=1 order by rand()"));
    projects = criteria.list();//  w w  w.j av a 2 s  .c om
    return (ArrayList<Projectsforusers>) projects;
}

From source file:cruds.ProjectCrudImplementation.java

@Override
public ArrayList<Projectsforusers> selectLastInterd() {
    System.out.println("in crud mona");
    List<Projectsforusers> projects = new ArrayList<>();
    Session session = SessionCreation.getSessionFactory().openSession();
    int n = 2;//from   w  w  w. jav a2 s . c om
    Criteria count = session.createCriteria(Projectsforusers.class);
    count.setProjection(Projections.rowCount());
    Long total = (Long) count.uniqueResult();
    Criteria criteria = session.createCriteria(Projectsforusers.class);
    criteria.setFirstResult((int) (total - n));
    criteria.add(Restrictions.sqlRestriction("1=1 order by rand()"));
    criteria.setMaxResults(n);
    projects = criteria.list();
    return (ArrayList<Projectsforusers>) projects;
}