Java EntityManager.createNativeQuery方法代碼示例

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static List<Object> countNbCardsByMotifsDisable(String userType) {
    EntityManager em = Card.entityManager();
    String sql = "SELECT motif_disable, COUNT(*) FROM card WHERE motif_disable IS NOT NULL GROUP BY motif_disable ORDER BY motif_disable";
    if (!userType.isEmpty()) {
        sql = "SELECT motif_disable, COUNT(*) FROM card, user_account WHERE card.user_account= user_account.id " + "AND motif_disable IS NOT NULL AND user_type =:userType GROUP BY motif_disable";
    }
    Query q = em.createNativeQuery(sql);
    if (!userType.isEmpty()) {
        q.setParameter("userType", userType);
    }
    return q.getResultList();
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public List<Notificaciones> getNotifications(boolean all, int maxResults, int firstResult, String _id) {
    EntityManager em = getEntityManager();
    List<Notificaciones> result = new ArrayList<>();
    try {
        Query q = em.createNativeQuery(String.format(SQL_NOTIFICATION, _id), Notificaciones.class);

        if (!all) {
            q.setMaxResults(maxResults);
            q.setFirstResult(firstResult);
        }
        result = q.getResultList();
        return result;
    } finally {
        em.close();
    }
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static List<Object> countNbEditedCardNotDelivered(Date date) {
    EntityManager em = Card.entityManager();
    String sql = "select CASE WHEN user_type= 'I' THEN 'Invité' WHEN user_type= 'E' THEN 'Etudiant' WHEN user_type= 'P' THEN 'Personnel' ELSE user_type END AS userType, count(*) " 
    		+ "FROM card, user_account WHERE card.user_account= user_account.id AND delivered_date is null and etat='ENABLED' " + "AND request_date>=:date "
    				+ "AND user_type NOT LIKE '' GROUP BY userType;";
    Query q = em.createNativeQuery(sql);
    q.setParameter("date", date);
    return q.getResultList();
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static List<Object> countNbLogByDay2(String action, String cases, List<String> banned) {
    String bannedReq = "";
    if (!banned.isEmpty()) {
        bannedReq = " AND remote_address NOT IN(:banned) ";
    }
    String sql = "SELECT remote_address,  to_date(to_char(log_date, 'DD-MM-YYYY'), 'DD-MM-YYYY') AS day, count(*) FROM log WHERE action = '" + action + "' " + bannedReq + "AND ret_code = 'SUCCESS' AND DATE_PART('days', now() - log_date) < 31  GROUP BY remote_address, day  order by remote_address";
    if (cases != null) {
        sql = "SELECT " + cases + ",  to_date(to_char(log_date, 'DD-MM-YYYY'), 'DD-MM-YYYY') AS day, count(*) FROM log WHERE action = '" + action + "' " + bannedReq + " AND ret_code = 'SUCCESS' AND DATE_PART('days', now() - log_date) < 31  GROUP BY remote_address, day  order by remote_address";
    }
    EntityManager em = Card.entityManager();
    Query q = em.createNativeQuery(sql);
    if (!banned.isEmpty()) {
        q.setParameter("banned", banned);
    }
    return q.getResultList();
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static Query countDeliveryByAddress(Date date) {
    EntityManager em = User.entityManager();
    String sql = "SELECT address, count(*) FROM card INNER JOIN user_account ON card.user_account=user_account.id " + "AND request_date>=:date AND delivered_date is null GROUP BY address ORDER BY count DESC";
    Query q = em.createNativeQuery(sql);
    q.setParameter("date", date);
    return q;
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static List<Object> countNbCardsByMonthYear(String userType) {
    EntityManager em = Card.entityManager();
    String sql = "SELECT CAST(DATE_PART('year', request_date) AS INTEGER) AS year, CAST(DATE_PART('month', request_date) AS INTEGER) AS month, count(*) AS count FROM card GROUP BY year, month";
    if (!userType.isEmpty()) {
        sql = "SELECT CAST(DATE_PART('year', request_date) AS INTEGER) AS year, CAST(DATE_PART('month', request_date) AS INTEGER) AS month, count(*) AS count FROM card, user_account "
        		+ "WHERE card.user_account= user_account.id " + " AND user_type =:userType GROUP BY year, month";
    }
    Query q = em.createNativeQuery(sql);
    if (!userType.isEmpty()) {
        q.setParameter("userType", userType);
    }
    return q.getResultList();
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
private Query getCallableStatementAlert(EntityManager em, Timestamp sqlTimeString, String dir) throws SQLException {
    File cvsFile = new File(dir + "alert.csv");
    Query spq = em.createNativeQuery("{CALL CSVWRITE(?, ?, ?)}");
    spq.setParameter(1, String.valueOf(cvsFile.getAbsoluteFile()));
    spq.setParameter(2, "SELECT * FROM ALERT WHERE created_timestamp <= '"+sqlTimeString+"'");
    spq.setParameter(3,"charset=UTF-8 fieldSeparator=,");
    return spq;
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
private Query getCallableStatementReleaseInfo(EntityManager em, Timestamp sqlTimeString, String dir) throws SQLException {
    File cvsFile = new File(dir + "release_info.csv");
    Query spq = em.createNativeQuery("{CALL CSVWRITE(?, ?, ?)}");
    spq.setParameter(1, String.valueOf(cvsFile.getAbsoluteFile()));
    spq.setParameter(2, "SELECT * FROM RELEASE_INFO");
    spq.setParameter(3,"charset=UTF-8 fieldSeparator=,");
    return spq;
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static List<Object> countNbRejetsByMonth(String userType) {
    String sql = "SELECT to_char(date_etat, 'MM-YYYY') tochar, count(*) FROM card WHERE etat='REJECTED' GROUP BY tochar ORDER BY to_date(to_char(date_etat, 'MM-YYYY'), 'MM-YYYY')";
    if (!userType.isEmpty()) {
        sql = "SELECT to_char(date_etat, 'MM-YYYY') tochar, count(*) FROM card, user_account WHERE card.user_account= user_account.id " + "AND etat='REJECTED' AND user_type = :userType GROUP BY tochar ORDER BY to_date(to_char(date_etat, 'MM-YYYY'), 'MM-YYYY')";
    }
    EntityManager em = Card.entityManager();

    Query q = em.createNativeQuery(sql);
    if (!userType.isEmpty()) {
        q.setParameter("userType", userType);
    }        

    return q.getResultList();
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
/**
 * Create a native query that returns object instances
 * @param em
 * @param targetClass the result class
 * @return
 */
public Query nativeQuery(EntityManager em, Class targetClass) {
	nativeQuery = true;
	Query query = em.createNativeQuery(sql(), targetClass);
	fixQuery(query);
	return query;
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
private Query getCallableStatementTask(EntityManager em, Timestamp sqlTimeString, String dir) throws SQLException {
    File cvsFile = new File(dir + "task.csv");
    Query spq = em.createNativeQuery("{CALL CSVWRITE(?, ?, ?)}");
    spq.setParameter(1, String.valueOf(cvsFile.getAbsoluteFile()));
    spq.setParameter(2, "SELECT * FROM TASK WHERE job_fk IN (SELECT ID FROM JOB WHERE completed_timestamp <= '"+sqlTimeString+"')");
    spq.setParameter(3,"charset=UTF-8 fieldSeparator=,");
    return spq;
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public int getPersonaCount() {
        EntityManager em = getEntityManager();
        try {
//            CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
//            Root<Usuario> rt = cq.from(Usuario.class);
//            cq.select(em.getCriteriaBuilder().count(rt));
//            Query q = em.createQuery(cq);
//            return ((Long) q.getSingleResult()).intValue();
            //String _user = "a";
            //String a = em.createQuery("select u.Rol from Usuarios u where Id=:ID and Pass='a'").setParameter("ID", _user);
            String pam1 = "a";
            String pam2 = "a";
            Query q = em.createNativeQuery(String.format(SQL_ISBANNED, pam1, pam2));
            //q = q.setParameter("pam1", "a");
            System.out.println(">>Uuser: " + q.toString());
            System.out.println(">>Uuser: " + q.getSingleResult());
//            List<Object[]> usuarios = q.getResultList();
//            
//            for(Object[] u : usuarios){
//                System.out.println(">>Uuser: " + u[0]);
//                System.out.println(">>Rool: " + u[1]);
//            }
            
            //System.out.println(">>>>>>>: " + a);

            return 1;
        } finally {
            em.close();
        }
    }
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
private Query getCallableStatementForJob(EntityManager em, Timestamp sqlTimeString, String dir) throws SQLException {
    File cvsFile = new File(dir + "job.csv");
    Query spq = em.createNativeQuery("{CALL CSVWRITE(?, ?, ?)}");
    spq.setParameter(1, String.valueOf(cvsFile.getAbsoluteFile()));
    spq.setParameter(2, "SELECT * FROM JOB WHERE completed_timestamp <= '" + sqlTimeString+"'");
    spq.setParameter(3,"charset=UTF-8 fieldSeparator=,");
    return spq;
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static List<Object[]> countMonthCardsByPopulationCrous(String date, String typeDate) {
    EntityManager em = User.entityManager();
    String sql = "SELECT cnous_reference_statut AS eppa, count(*) as count "
    		+ "FROM card, user_account WHERE card.user_account=user_account.id "
    		+ "AND to_char(" + typeDate + ", 'yyyy-mm-dd') like '" + date + "' GROUP BY cnous_reference_statut";

    Query q = em.createNativeQuery(sql);

    return q.getResultList();
}
 

import javax.persistence.EntityManager; //導入方法依賴的package包/類
public static List<String> findDistinctEtats() {
    EntityManager em = Card.entityManager();
    Query q = em.createNativeQuery("SELECT DISTINCT etat FROM Card");
    return q.getResultList();
}