Agfdhyk

In JAVA after execution of the query I am counting the result set size in common database utils file before returning the data to monitor queries which are selecting more than 1000 rows. I would like to do the same in springs for both criteria and specification queries. How to achieve this?

UPDATE:

Logic I am following in java while executing query to send mail when there is a query extracting more than 2500 results.

Vector result = new Vector();

Vector row;

int colIndex;

int rowCount = 0;

int colCount;

ResultSet rst = null;

try {

 rst = executeQuery(xQuery);

 colCount = rst.getMetaData().getColumnCount();

 while (rst.next()) {

  row = new Vector();

  for (colIndex = 1; colIndex <= colCount; colIndex++) {

   if (rst.getString(colIndex) != null && rst.getString(colIndex) != "")

    row.add(colIndex - 1, rst.getString(colIndex));

   else

    row.add(colIndex - 1, " ");

  }

  result.add(rowCount++, row);

 }

 try {

  if (result.size() > 2500) {



   Mailer(dbConn.getConnection().getMetaData().getURL(), xQuery, result.size());

  }

 } catch (Exception e) {



 }



} catch (Exception e) {

 throw e;

} finally {

 if (rst != null) rst.close();

 rst = null;

}

return result;

If I get your question right, you need to be able to use hibernate Criteria to determine if the Resultset returned has more than 2500 rows. This can be achieved by obtaining a list from the Criteria and then determining the size of the List. For Example, say you are querying a purchase Orders table:

public static List<PurchaseOrders> getPurchaseOrderDetails() {

        List<PurchaseOrders> orders = null;

        Session session = HibernateUtil.getSessionFactory().openSession();

        Transaction tx = null;

        try {

            tx = session.beginTransaction();

            Criteria cr = session.createCriteria(PurchaseOrders.class);

            cr.add(Restrictions.eq("delFlg", "N"));

            ////add whatever other filters here

            orders = cr.list();

            tx.commit();

        } catch (HibernateException asd) {

            log.debug(asd.getMessage());

            if (tx != null) {

                tx.rollback();

            }

        } finally {

            session.close();

        }

        return orders;

    }

This is an equivalent of :

select * from purchase_orders where del_flg = 'N';

Note that PurchaseOrders is your jpa class having the database object mapping. Since you already have a List from here, you can always check the size.

public static void sendAlert() {

        List<PurchaseOrders> orders = getPurchaseOrderDetails();

        int size = orders.size();

        if(size > 2500){

            ///Initiate alerts

        }

    }

I hope this helps.

UPDATE
If you need to cover all your queries in one method, you will need to write a generic method:

public static <T> List<T> getDetails(Class<T> c, Map<String, ?> params) {

        List<T> details = null;

        Session session = HibernateUtil.getSessionFactory().openSession();

        Transaction tx = null;

        try {

            tx = session.beginTransaction();

            Criteria cr = session.createCriteria(c);

            params.entrySet().forEach((entry) -> {

                cr.add(Restrictions.eq(entry.getKey(), entry.getValue()));

            });

            details = cr.list();

            tx.commit();

        } catch (HibernateException asd) {

            log.debug(asd.getMessage());

            if (tx != null) {

                tx.rollback();

            }

        } finally {

            session.close();

        }

        return details;

    }

and then fill in the parameters for your different queries and tables.