如何在控制台打印原生SQL查询？Hibernate执行代码相关问题

Hey there! Let's break down your questions and fix the code issues step by step:

1. 打印查询语句到控制台 #

This is straightforward—just add a console log right after you get the query variable. Insert this line right after line 7 in your original code:

System.out.println("Executing SQL query: " + query);

2. 调用getResultList()获取查询结果 #

Your original example uses session.doWork() to directly execute JDBC statements, which works great for non-query operations like INSERT/UPDATE/DELETE. But to fetch results, you'll want to use Hibernate's NativeQuery API instead, which lets you call getResultList() directly.

Here's the revised Example 1 with key changes marked:

public String executeSQL(String[] split){ 
    SessionFactory hibernateFactory = someService.getHibernateFactory(); 
    Session session = hibernateFactory.openSession(); 
    String message = null; 
    try { // Add outer try to ensure session closes properly
        for (int i = 0; i < split.length; i++) { 
            try{ 
                String query = split[i]; 
                // 1. Print query to console
                System.out.println("Executing SQL query: " + query); 
                
                // 2. Use NativeQuery to call getResultList()
                List<Object[]> resultList = session.createNativeQuery(query).getResultList(); 
                // Optional: Print results to console
                resultList.stream().map(Arrays::toString).forEach(System.out::println); 
                
                message = "Success! Query returned " + resultList.size() + " rows."; 
            } 
            catch(SQLGrammarException e){ // More precise exception catch
                message = e.getSQLException().getMessage(); 
            } catch(Exception e){
                message = "Unexpected error: " + e.getMessage();
            }
        } 
    } finally {
        session.close(); // Move to finally to guarantee session closure
    }
    return message; 
}

Note: I moved session.close() to a finally block to ensure the session gets closed even if an exception occurs—this is a critical resource management fix.

In JPA/Hibernate, direct SQLExceptions are wrapped in a PersistenceException (or Hibernate-specific JDBCException). You'll need to unpack the exception chain to get the underlying SQLException. Also, your original Example 2 has two resource issues: an unclosed EntityManager and an unused Session—we'll fix those too.

Here's the revised Example 2:

public String executeSQL(String[] split){ 
    SessionFactory hibernateFactory = someService.getHibernateFactory(); 
    String message = null; 
    
    for (int i = 0; i < split.length; i++) { 
        // Use try-with-resources to auto-close EntityManager
        try(EntityManager entityManager = hibernateFactory.createEntityManager()){ 
            String query = split[i]; 
            List<Object[]> resultList = entityManager.createNativeQuery(query).getResultList(); 
            resultList.stream().map(Arrays::toString).forEach(System.out::println); 
            message = "Success! Query returned " + resultList.size() + " rows."; 
        } 
        catch(javax.persistence.PersistenceException pe){ // Catch JPA's persistence exception
            // Unpack the exception chain to get the underlying SQLException
            Throwable cause = pe.getCause();
            while (cause != null && !(cause instanceof SQLException)) {
                cause = cause.getCause();
            }
            if (cause instanceof SQLException) {
                SQLException sqlEx = (SQLException) cause;
                message = "SQL Error: " + sqlEx.getMessage();
            } else {
                message = "Persistence Error: " + pe.getMessage();
            }
        } catch(Exception e){
            message = "Unexpected error: " + e.getMessage();
        }
    } 
    // Remove unused Session code
    return message; 
}

Key notes:

• try-with-resources automatically closes the EntityManager, preventing resource leaks.
• By traversing the exception chain, we extract the underlying SQLException to get the detailed error message you need for your HTML page.
• The unused Session code is removed since you're using EntityManager—no need to maintain both.

内容的提问来源于stack exchange，提问作者Brarord