Example usage for org.hibernate.criterion Projections projectionList

List of usage examples for org.hibernate.criterion Projections projectionList

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.criterion.*
  5. Projections
  6. projectionList

Introduction

In this page you can find the example usage for org.hibernate.criterion Projections projectionList.

Prototype

public static ProjectionList projectionList()

Source Link

Document

Create a new projection list.

Usage

From source file:com.floreantpos.model.dao.TicketDAO.java

License:Open Source License

public TicketSummary getOpenTicketSummary() {
    Session session = null;//from  w w w.  j a va2s  .c o m
    TicketSummary ticketSummary = new TicketSummary();
    try {
        session = getSession();
        Criteria criteria = session.createCriteria(Ticket.class);
        criteria.add(Restrictions.eq(Ticket.PROP_CLOSED, Boolean.FALSE));
        criteria.add(Restrictions.eq(Ticket.PROP_VOIDED, Boolean.FALSE));
        criteria.add(Restrictions.eq(Ticket.PROP_REFUNDED, Boolean.FALSE));
        criteria.add(Restrictions.eq(Ticket.PROP_DRAWER_RESETTED, Boolean.FALSE));

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.count(Ticket.PROP_ID));
        projectionList.add(Projections.sum(Ticket.PROP_TOTAL_AMOUNT));
        criteria.setProjection(projectionList);

        List list = criteria.list();
        if (list.size() > 0) {
            Object[] o = (Object[]) list.get(0);
            ticketSummary.setTotalTicket(((Integer) o[0]).intValue());
            ticketSummary.setTotalPrice(o[1] == null ? 0 : ((Double) o[1]).doubleValue());
        }
        return ticketSummary;
    } finally {
        closeSession(session);
    }
}

From source file:com.floreantpos.model.dao.TicketDAO.java

License:Open Source License

public TicketSummary getClosedTicketSummary(Terminal terminal) {

    Session session = null;//  www.  j ava 2s.c  om
    TicketSummary ticketSummary = new TicketSummary();
    try {
        session = getSession();
        Criteria criteria = session.createCriteria(Ticket.class);
        criteria.add(Restrictions.eq(Ticket.PROP_CLOSED, Boolean.TRUE));
        criteria.add(Restrictions.eq(Ticket.PROP_VOIDED, Boolean.FALSE));
        criteria.add(Restrictions.eq(Ticket.PROP_REFUNDED, Boolean.FALSE));
        criteria.add(Restrictions.eq(Ticket.PROP_DRAWER_RESETTED, Boolean.FALSE));
        criteria.add(Restrictions.eq(Ticket.PROP_TERMINAL, terminal));

        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.count(Ticket.PROP_ID));
        projectionList.add(Projections.sum(Ticket.PROP_TOTAL_AMOUNT));
        criteria.setProjection(projectionList);

        List list = criteria.list();
        if (list.size() > 0) {
            Object[] o = (Object[]) list.get(0);
            ticketSummary.setTotalTicket(((Integer) o[0]).intValue());
            ticketSummary.setTotalPrice(o[1] == null ? 0 : ((Double) o[1]).doubleValue());
        }
        return ticketSummary;
    } finally {
        closeSession(session);
    }
}

From source file:com.floreantpos.model.dao.TicketDAO.java

License:Open Source License

public List<Ticket> getTicketsWithSpecificFields(String... fields) {
    Session session = null;// w  w w  .  j  av a 2  s.  co  m
    Criteria criteria = null;
    User currentUser = Application.getCurrentUser();
    boolean filterUser = !currentUser.isAdministrator() || !currentUser.isManager();
    try {
        session = createNewSession();
        criteria = session.createCriteria(Ticket.class);
        ProjectionList projectionList = Projections.projectionList();
        for (String field : fields) {
            projectionList.add(Projections.property(field));
        }
        criteria.add(Restrictions.eq(Ticket.PROP_CLOSED, Boolean.FALSE));
        if (filterUser) {
            criteria.createAlias(Ticket.PROP_OWNER, "u");
            criteria.add(Restrictions.eq("u.userId", currentUser.getUserId()));
        }
        ResultTransformer transformer = new ResultTransformer() {

            public Object transformTuple(Object[] row, String[] arg1) {
                Ticket ticket = new Ticket();
                ticket.setId(Integer.valueOf("" + row[0]));
                ticket.setDueAmount(Double.valueOf("" + row[1]));
                return ticket;
            }

            public List transformList(List arg0) {
                return arg0;
            }
        };
        criteria.setProjection(projectionList).setResultTransformer(transformer);
        return criteria.list();
    } finally {
        closeSession(session);
    }
}

From source file:com.floreantpos.report.service.ReportService.java

License:Open Source License

public MenuUsageReport getMenuUsageReport(Date fromDate, Date toDate) {
    GenericDAO dao = new GenericDAO();
    MenuUsageReport report = new MenuUsageReport();
    Session session = null;/*w w w  .j  a  v a  2s . co m*/

    try {

        session = dao.getSession();

        Criteria criteria = session.createCriteria(MenuCategory.class);
        List<MenuCategory> categories = criteria.list();
        MenuCategory miscCategory = new MenuCategory();
        miscCategory.setName(com.floreantpos.POSConstants.MISC_BUTTON_TEXT);
        categories.add(miscCategory);

        for (MenuCategory category : categories) {
            criteria = session.createCriteria(TicketItem.class, "item"); //$NON-NLS-1$
            criteria.createCriteria("ticket", "t"); //$NON-NLS-1$ //$NON-NLS-2$
            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.sum(TicketItem.PROP_ITEM_COUNT));
            projectionList.add(Projections.sum(TicketItem.PROP_SUBTOTAL_AMOUNT));
            projectionList.add(Projections.sum(TicketItem.PROP_DISCOUNT_AMOUNT));
            criteria.setProjection(projectionList);
            criteria.add(Restrictions.eq("item." + TicketItem.PROP_CATEGORY_NAME, category.getName())); //$NON-NLS-1$
            criteria.add(Restrictions.ge("t." + Ticket.PROP_CREATE_DATE, fromDate)); //$NON-NLS-1$
            criteria.add(Restrictions.le("t." + Ticket.PROP_CREATE_DATE, toDate)); //$NON-NLS-1$
            criteria.add(Restrictions.eq("t." + Ticket.PROP_PAID, Boolean.TRUE)); //$NON-NLS-1$

            List datas = criteria.list();
            if (datas.size() > 0) {
                Object[] objects = (Object[]) datas.get(0);

                MenuUsageReportData data = new MenuUsageReportData();
                data.setCategoryName(category.getName());

                if (objects.length > 0 && objects[0] != null)
                    data.setCount(((Number) objects[0]).intValue());

                if (objects.length > 1 && objects[1] != null)
                    data.setGrossSales(((Number) objects[1]).doubleValue());

                if (objects.length > 2 && objects[2] != null)
                    data.setDiscount(((Number) objects[2]).doubleValue());

                data.calculate();
                report.addReportData(data);
            }
        }

        return report;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

From source file:com.floreantpos.report.service.ReportService.java

License:Open Source License

public ServerProductivityReport getServerProductivityReport(Date fromDate, Date toDate) {
    GenericDAO dao = new GenericDAO();
    ServerProductivityReport report = new ServerProductivityReport();
    Session session = null;//from   ww  w . j  a  v  a2s . c  o m

    try {

        session = dao.getSession();

        Criteria criteria = session.createCriteria(User.class);
        //criteria.add(Restrictions.eq(User.PROP_USER_TYPE, User.USER_TYPE_SERVER));
        List<User> servers = criteria.list();

        criteria = session.createCriteria(MenuCategory.class);
        List<MenuCategory> categories = criteria.list();
        MenuCategory miscCategory = new MenuCategory();
        miscCategory.setName(com.floreantpos.POSConstants.MISC_BUTTON_TEXT);
        categories.add(miscCategory);

        for (User server : servers) {
            ServerProductivityReportData data = new ServerProductivityReportData();
            data.setServerName(server.getUserId() + "/" + server.toString()); //$NON-NLS-1$
            criteria = session.createCriteria(Ticket.class);
            criteria.add(Restrictions.eq(Ticket.PROP_OWNER, server));
            criteria.add(Restrictions.eq(Ticket.PROP_PAID, Boolean.TRUE));
            criteria.add(Restrictions.eq(Ticket.PROP_VOIDED, Boolean.FALSE)); //$NON-NLS-1$
            criteria.add(Restrictions.eq(Ticket.PROP_REFUNDED, Boolean.FALSE)); //$NON-NLS-1$
            criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, fromDate));
            criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, toDate));

            ProjectionList projectionList = Projections.projectionList();
            projectionList.add(Projections.rowCount());
            projectionList.add(Projections.sum(Ticket.PROP_NUMBER_OF_GUESTS));
            projectionList.add(Projections.sum(TicketItem.PROP_TOTAL_AMOUNT));

            criteria.setProjection(projectionList);

            Object[] o = (Object[]) criteria.uniqueResult();
            int totalCheckCount = 0;
            double totalServerSale = 0;
            if (o != null) {
                if (o.length > 0 && o[0] != null) {
                    int i = ((Number) o[0]).intValue();
                    data.setTotalCheckCount(totalCheckCount = i);
                }
                if (o.length > 1 && o[1] != null) {
                    int i = ((Number) o[1]).intValue();
                    data.setTotalGuestCount(i);
                }
                if (o.length > 2 && o[2] != null) {
                    totalServerSale = ((Number) o[2]).doubleValue();
                    data.setTotalSales(totalServerSale);
                }
            }

            data.calculate();
            report.addReportData(data);

            for (MenuCategory category : categories) {
                data = new ServerProductivityReportData();
                data.setServerName(server.getUserId() + "/" + server.toString()); //$NON-NLS-1$

                criteria = session.createCriteria(TicketItem.class, "item"); //$NON-NLS-1$
                criteria.createCriteria(TicketItem.PROP_TICKET, "t"); //$NON-NLS-1$

                projectionList = Projections.projectionList();
                criteria.setProjection(projectionList);
                projectionList.add(Projections.sum(TicketItem.PROP_ITEM_COUNT));
                projectionList.add(Projections.sum(TicketItem.PROP_SUBTOTAL_AMOUNT));
                projectionList.add(Projections.sum("t." + Ticket.PROP_DISCOUNT_AMOUNT)); //$NON-NLS-1$
                projectionList.add(Projections.rowCount());

                criteria.add(Restrictions.eq("item." + TicketItem.PROP_CATEGORY_NAME, category.getName())); //$NON-NLS-1$
                criteria.add(Restrictions.ge("t." + Ticket.PROP_CREATE_DATE, fromDate)); //$NON-NLS-1$
                criteria.add(Restrictions.le("t." + Ticket.PROP_CREATE_DATE, toDate)); //$NON-NLS-1$
                criteria.add(Restrictions.eq("t." + Ticket.PROP_OWNER, server)); //$NON-NLS-1$
                criteria.add(Restrictions.eq("t." + Ticket.PROP_PAID, Boolean.TRUE)); //$NON-NLS-1$
                criteria.add(Restrictions.eq("t." + Ticket.PROP_VOIDED, Boolean.FALSE)); //$NON-NLS-1$
                criteria.add(Restrictions.eq("t." + Ticket.PROP_REFUNDED, Boolean.FALSE)); //$NON-NLS-1$

                List datas = criteria.list();
                if (datas.size() > 0) {
                    Object[] objects = (Object[]) datas.get(0);

                    data.setCategoryName(category.getName());
                    data.setTotalCheckCount(totalCheckCount);
                    if (objects.length > 0 && objects[0] != null) {
                        int i = ((Number) objects[0]).intValue();
                        data.setCheckCount(i);
                    }

                    if (objects.length > 1 && objects[1] != null) {
                        double d = ((Number) objects[1]).doubleValue();
                        data.setGrossSales(d);
                    }

                    if (objects.length > 2 && objects[2] != null) {
                        double d = ((Number) objects[2]).doubleValue();
                        if (d > 0)
                            data.setSalesDiscount(d);
                    }
                    data.setAllocation((data.getGrossSales() / totalServerSale) * 100.0);
                    data.calculate();
                    report.addReportData(data);
                }
            }
        }
        return report;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

From source file:com.floreantpos.report.service.ReportService.java

License:Open Source License

private void calculateDrawerPullAmount(Session session, SalesBalanceReport report, Date fromDate, Date toDate,
        User user) {/*ww w .  j  a v a  2 s.  co m*/
    Criteria criteria = session.createCriteria(DrawerPullReport.class);
    criteria.add(Restrictions.ge(DrawerPullReport.PROP_REPORT_TIME, fromDate));
    criteria.add(Restrictions.le(DrawerPullReport.PROP_REPORT_TIME, toDate));

    if (user != null) {
        criteria.add(Restrictions.eq(DrawerPullReport.PROP_ASSIGNED_USER, user));
    }

    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.sum(DrawerPullReport.PROP_DRAWER_ACCOUNTABLE));
    projectionList.add(Projections.sum(DrawerPullReport.PROP_BEGIN_CASH));
    criteria.setProjection(projectionList);

    Object[] o = (Object[]) criteria.uniqueResult();
    if (o.length > 0 && o[0] instanceof Number) {
        double amount = ((Number) o[0]).doubleValue();
        report.setDrawerPullsAmount(amount);
    }
    if (o.length > 1 && o[1] instanceof Number) {
        double amount = ((Number) o[1]).doubleValue();
        report.setDrawerPullsAmount(report.getDrawerPullsAmount() - amount);
    }
}

From source file:com.floreantpos.report.service.ReportService.java

License:Open Source License

public SalesDetailedReport getSalesDetailedReport(Date fromDate, Date toDate) {
    GenericDAO dao = new GenericDAO();
    SalesDetailedReport report = new SalesDetailedReport();
    Session session = null;//from   w  w  w.j a  va2s  .  c o  m

    report.setFromDate(fromDate);
    report.setToDate(toDate);
    report.setReportTime(new Date());
    try {

        session = dao.getSession();

        Criteria criteria = session.createCriteria(DrawerPullReport.class);
        criteria.add(Restrictions.ge(DrawerPullReport.PROP_REPORT_TIME, fromDate));
        criteria.add(Restrictions.le(DrawerPullReport.PROP_REPORT_TIME, toDate));
        List list = criteria.list();
        for (Iterator iter = list.iterator(); iter.hasNext();) {
            DrawerPullReport drawerPullReport = (DrawerPullReport) iter.next();
            DrawerPullData data = new DrawerPullData();
            data.setDrawerPullId(drawerPullReport.getId());
            data.setTicketCount(drawerPullReport.getTicketCount());
            data.setIdealAmount(drawerPullReport.getDrawerAccountable());
            data.setActualAmount(drawerPullReport.getCashToDeposit());
            data.setVarinceAmount(
                    drawerPullReport.getDrawerAccountable() - drawerPullReport.getCashToDeposit());
            report.addDrawerPullData(data);
        }

        criteria = session.createCriteria(CreditCardTransaction.class);
        criteria.add(Restrictions.ge(CreditCardTransaction.PROP_TRANSACTION_TIME, fromDate));
        criteria.add(Restrictions.le(CreditCardTransaction.PROP_TRANSACTION_TIME, toDate));
        list = criteria.list();

        for (Iterator iter = list.iterator(); iter.hasNext();) {
            CreditCardTransaction t = (CreditCardTransaction) iter.next();
            report.addCreditCardData(t);
        }

        criteria = session.createCriteria(DebitCardTransaction.class);
        criteria.add(Restrictions.ge(DebitCardTransaction.PROP_TRANSACTION_TIME, fromDate));
        criteria.add(Restrictions.le(DebitCardTransaction.PROP_TRANSACTION_TIME, toDate));
        list = criteria.list();

        for (Iterator iter = list.iterator(); iter.hasNext();) {
            DebitCardTransaction t = (DebitCardTransaction) iter.next();
            report.addCreditCardData(t);
        }

        criteria = session.createCriteria(GiftCertificateTransaction.class);
        criteria.add(Restrictions.ge(GiftCertificateTransaction.PROP_TRANSACTION_TIME, fromDate));
        criteria.add(Restrictions.le(GiftCertificateTransaction.PROP_TRANSACTION_TIME, toDate));
        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.rowCount());
        projectionList.add(Projections.sum(GiftCertificateTransaction.PROP_AMOUNT));
        criteria.setProjection(projectionList);
        Object[] object = (Object[]) criteria.uniqueResult();
        if (object != null && object.length > 0 && object[0] instanceof Number) {
            report.setGiftCertReturnCount(((Number) object[0]).intValue());
        }
        if (object != null && object.length > 1 && object[1] instanceof Number) {
            report.setGiftCertReturnAmount(((Number) object[1]).doubleValue());
        }

        criteria = session.createCriteria(GiftCertificateTransaction.class);
        criteria.add(Restrictions.ge(GiftCertificateTransaction.PROP_TRANSACTION_TIME, fromDate));
        criteria.add(Restrictions.le(GiftCertificateTransaction.PROP_TRANSACTION_TIME, toDate));
        criteria.add(
                Restrictions.gt(GiftCertificateTransaction.PROP_GIFT_CERT_CASH_BACK_AMOUNT, Double.valueOf(0)));
        projectionList = Projections.projectionList();
        projectionList.add(Projections.rowCount());
        projectionList.add(Projections.sum(GiftCertificateTransaction.PROP_GIFT_CERT_CASH_BACK_AMOUNT));
        criteria.setProjection(projectionList);
        object = (Object[]) criteria.uniqueResult();
        if (object != null && object.length > 0 && object[0] instanceof Number) {
            report.setGiftCertChangeCount(((Number) object[0]).intValue());
        }
        if (object != null && object.length > 1 && object[1] instanceof Number) {
            report.setGiftCertChangeAmount(((Number) object[1]).doubleValue());
        }

        criteria = session.createCriteria(Ticket.class);
        criteria.createAlias(Ticket.PROP_GRATUITY, "g"); //$NON-NLS-1$
        criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, fromDate));
        criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, toDate));
        criteria.add(Restrictions.gt("g." + Gratuity.PROP_AMOUNT, Double.valueOf(0))); //$NON-NLS-1$
        projectionList = Projections.projectionList();
        projectionList.add(Projections.rowCount());
        projectionList.add(Projections.sum("g." + Gratuity.PROP_AMOUNT)); //$NON-NLS-1$
        criteria.setProjection(projectionList);
        object = (Object[]) criteria.uniqueResult();
        if (object != null && object.length > 0 && object[0] instanceof Number) {
            report.setTipsCount(((Number) object[0]).intValue());
        }
        if (object != null && object.length > 1 && object[1] instanceof Number) {
            report.setChargedTips(((Number) object[1]).doubleValue());
        }

        criteria = session.createCriteria(Ticket.class);
        criteria.createAlias(Ticket.PROP_GRATUITY, "g"); //$NON-NLS-1$
        criteria.add(Restrictions.ge(Ticket.PROP_CREATE_DATE, fromDate));
        criteria.add(Restrictions.le(Ticket.PROP_CREATE_DATE, toDate));
        criteria.add(Restrictions.gt("g." + Gratuity.PROP_AMOUNT, Double.valueOf(0))); //$NON-NLS-1$
        criteria.add(Restrictions.gt("g." + Gratuity.PROP_PAID, Boolean.TRUE)); //$NON-NLS-1$
        projectionList = Projections.projectionList();
        projectionList.add(Projections.sum("g." + Gratuity.PROP_AMOUNT)); //$NON-NLS-1$
        criteria.setProjection(projectionList);
        object = (Object[]) criteria.uniqueResult();
        if (object != null && object.length > 0 && object[0] instanceof Number) {
            report.setTipsPaid(((Number) object[0]).doubleValue());
        }

        return report;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

From source file:com.gisgraphy.hibernate.projection.SpatialProjectionTest.java

License:Open Source License

@SuppressWarnings("unchecked")
@Test//from  w  w  w.j a  v  a  2  s  .  co  m
public void testdistance_pointToLine() {
    LineString shape = GeolocHelper.createLineString("LINESTRING (6.9416088 50.9154239,6.9410001 50.9154734)");

    OpenStreetMap streetOSM = GisgraphyTestHelper.createOpenStreetMapForPeterMartinStreet();
    streetOSM.setShape(shape);
    openStreetMapDao.save(streetOSM);
    assertNotNull(openStreetMapDao.get(streetOSM.getId()));

    final Point p1 = GeolocHelper.createPoint(6.9412748F, 50.9155829F);

    HibernateCallback hibernateCallback = new HibernateCallback() {

        public Object doInHibernate(Session session) throws PersistenceException {

            Criteria testCriteria = session.createCriteria(OpenStreetMap.class);
            ProjectionList projection = Projections.projectionList()
                    .add(Projections.property("name").as("name")).add(SpatialProjection
                            .distance_pointToLine(p1, OpenStreetMap.SHAPE_COLUMN_NAME).as("distance"))
                    .add(Projections.property("shape").as("shape"));
            // remove the from point
            testCriteria.setProjection(projection);
            testCriteria.setResultTransformer(Transformers.aliasToBean(_OpenstreetmapDTO.class));

            List<_OpenstreetmapDTO> results = testCriteria.list();
            return results;
        }
    };

    List<_OpenstreetmapDTO> streets = (List<_OpenstreetmapDTO>) testDao.testCallback(hibernateCallback);
    assertEquals(1, streets.size());
    Double calculatedDist = 14.76D;
    Double retrieveDistance = streets.get(0).getDistance();
    double percent = (Math.abs(calculatedDist - retrieveDistance) * 100)
            / Math.min(retrieveDistance, calculatedDist);
    assertTrue("There is more than one percent of error beetween the calculated distance (" + calculatedDist
            + ") and the retrieved one (" + retrieveDistance + ")", percent < 1);

}

From source file:com.gisgraphy.hibernate.projection.SpatialProjectionTest.java

License:Open Source License

@SuppressWarnings("unchecked")
@Test/* ww  w  .ja va2 s .co m*/
@Ignore
public void testDistance_sphere() {
    final City p1 = GisgraphyTestHelper.createCity("paris", 48.86667F, 2.3333F, 1L);
    City p2 = GisgraphyTestHelper.createCity("bordeaux", 44.83333F, -0.56667F, 3L);

    this.cityDao.save(p1);
    this.cityDao.save(p2);

    HibernateCallback hibernateCallback = new HibernateCallback() {

        public Object doInHibernate(Session session) throws PersistenceException {

            Criteria testCriteria = session.createCriteria(City.class);
            ProjectionList projection = Projections.projectionList()
                    .add(Projections.property("name").as("name")).add(SpatialProjection
                            .distance_sphere(p1.getLocation(), GisFeature.LOCATION_COLUMN_NAME).as("distance"));
            // remove the from point
            testCriteria.add(Restrictions.ne("id", p1.getId())).setProjection(projection);
            testCriteria.setResultTransformer(Transformers.aliasToBean(_CityDTO.class));

            List<_CityDTO> results = testCriteria.list();
            return results;
        }
    };

    List<_CityDTO> cities = (List<_CityDTO>) testDao.testCallback(hibernateCallback);
    assertEquals(1, cities.size());
    assertEquals("bordeaux", cities.get(0).getName());
    Double calculatedDist = p1.distanceTo(p2.getLocation());
    Double retrieveDistance = cities.get(0).getDistance();
    double percent = (Math.abs(calculatedDist - retrieveDistance) * 100)
            / Math.min(retrieveDistance, calculatedDist);
    assertTrue("There is more than one percent of error beetween the calculated distance (" + calculatedDist
            + ") and the retrieved one (" + retrieveDistance + ")", percent < 1);

}

From source file:com.gisgraphy.hibernate.projection.SpatialProjectionTest.java

License:Open Source License

@SuppressWarnings("unchecked")
@Test//  w w w .j  a  v  a2s.co  m
@Ignore
public void testDistance() {
    float x1 = -2f;
    float y1 = 2f;
    float x2 = 2f;
    float y2 = 2f;
    Point locationParis = GeolocHelper.createPoint(x1, y1);
    //locationParis.setSRID(-1);
    Point locationbordeaux = GeolocHelper.createPoint(x2, y2);
    //locationbordeaux.setSRID(-1);

    final City p1 = GisgraphyTestHelper.createCity("paris", 0F, 0F, 1L);
    p1.setLocation(locationParis);
    City p2 = GisgraphyTestHelper.createCity("bordeaux", 0F, 0F, 3L);
    p2.setLocation(locationbordeaux);

    this.cityDao.save(p1);
    this.cityDao.save(p2);

    HibernateCallback hibernateCallback = new HibernateCallback() {

        public Object doInHibernate(Session session) throws PersistenceException {

            Criteria testCriteria = session.createCriteria(City.class);
            ProjectionList projection = Projections.projectionList()
                    .add(Projections.property("name").as("name")).add(SpatialProjection
                            .distance(p1.getLocation(), GisFeature.LOCATION_COLUMN_NAME).as("distance"));
            // remove the from point
            testCriteria.add(Restrictions.ne("id", p1.getId())).setProjection(projection);
            testCriteria.setResultTransformer(Transformers.aliasToBean(_CityDTO.class));

            List<_CityDTO> results = testCriteria.list();
            return results;
        }
    };

    List<_CityDTO> cities = (List<_CityDTO>) testDao.testCallback(hibernateCallback);
    assertEquals(1, cities.size());
    assertEquals("bordeaux", cities.get(0).getName());
    Double calculatedDist = Math.sqrt(Math.pow(x2 - x1, 2) + Math.pow(y2 - y1, 2));//cartesian distance
    Double retrieveDistance = cities.get(0).getDistance();
    double percent = (Math.abs(calculatedDist - retrieveDistance) * 100)
            / Math.min(retrieveDistance, calculatedDist);
    assertTrue("There is more than one percent of error beetween the calculated distance (" + calculatedDist
            + ") and the retrieved one (" + retrieveDistance + ")", percent < 1);
}