public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
        return new SQLExceptionConversionDelegate() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}
 

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}
 

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}
 

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
        return new SQLExceptionConverter() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}
 

public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
        return new SQLExceptionConversionDelegate() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}
 

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}
 

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}
 

public static String extractExceptionMessage(Exception e) {
    if (e instanceof UndeclaredThrowableException) {
        Throwable cause = ((UndeclaredThrowableException) e).getUndeclaredThrowable().getCause();
        if (cause instanceof PersistenceException) {
            cause = ((PersistenceException) cause).getCause();
            if (cause instanceof DataException) {
                return ((DataException) cause).getSQLException().getMessage();
                
            }else if(cause instanceof SQLGrammarException) {
                return ((SQLGrammarException) cause).getSQLException().getMessage();
            }
        }
    }
    return e.getMessage();
}
 

@Override
public JDBCException convert(SQLException sqlException, String message, String sql) {
	if ( SQLClientInfoException.class.isInstance( sqlException )
			|| SQLInvalidAuthorizationSpecException.class.isInstance( sqlException )
			|| SQLNonTransientConnectionException.class.isInstance( sqlException )
			|| SQLTransientConnectionException.class.isInstance( sqlException ) ) {
		return new JDBCConnectionException( message, sqlException, sql );
	}
	else if ( DataTruncation.class.isInstance( sqlException ) ||
			SQLDataException.class.isInstance( sqlException ) ) {
		throw new DataException( message, sqlException, sql );
	}
	else if ( SQLIntegrityConstraintViolationException.class.isInstance( sqlException ) ) {
		return new ConstraintViolationException(
				message,
				sqlException,
				sql,
				getConversionContext().getViolatedConstraintNameExtracter().extractConstraintName( sqlException )
		);
	}
	else if ( SQLSyntaxErrorException.class.isInstance( sqlException ) ) {
		return new SQLGrammarException( message, sqlException, sql );
	}
	else if ( SQLTimeoutException.class.isInstance( sqlException ) ) {
		return new QueryTimeoutException( message, sqlException, sql );
	}
	else if ( SQLTransactionRollbackException.class.isInstance( sqlException ) ) {
		// Not 100% sure this is completely accurate.  The JavaDocs for SQLTransactionRollbackException state that
		// it indicates sql states starting with '40' and that those usually indicate that:
		//		<quote>
		//			the current statement was automatically rolled back by the database because of deadlock or
		// 			other transaction serialization failures.
		//		</quote>
		return new LockAcquisitionException( message, sqlException, sql );
	}

	return null; // allow other delegates the chance to look
}
 

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
        return new SQLExceptionConverter() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}
 

public void testBadGrammar() throws Exception {
	SQLExceptionConverter converter = getDialect().buildSQLExceptionConverter();

	Session session = openSession();
	Connection connection = session.connection();

       // prepare/execute a query against a non-existent table
	PreparedStatement ps = null;
	try {
		ps = connection.prepareStatement("SELECT user_id, user_name FROM tbl_no_there");
		ps.executeQuery();

		fail("SQL compilation should have failed");
	}
	catch( SQLException sqle ) {
		assertEquals( "Bad conversion [" + sqle.getMessage() + "]", SQLGrammarException.class, converter.convert(sqle, null, null).getClass() );
	}
	finally {
		if ( ps != null ) {
			try {
				ps.close();
			}
			catch( Throwable ignore ) {
				// ignore...
			}
		}
	}

	session.close();
}
 

public void execute() {
    // we need to do because of this class is app scoped
    Session session = sf.openSession();
    session.beginTransaction();
    try {
		update(session);
	} catch (SQLGrammarException ex) {
		ignoreIfTableDidNotExist(ex);
	}
    session.getTransaction().commit();
    session.close();
}
 

private void ignoreIfTableDidNotExist(SQLGrammarException ex) {
	if(ex.getCause().getMessage().contains(".question' doesn't exist")) {
		// ignore if its the first time we are running and the table still does not exist
		LOGGER.warn("Unable to run the mysql query to update the recent tags", ex);
	} else {
		// nasty catch and retrow, sorry.
		throw ex;
	}
}
 

@Test
void givenPeopleAndLowerCaseNamingStrategy_whenQueryPersonQuotedUpperCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"PERSON\"");

    // Expected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndSpringNamingStrategy_whenQueryPersonQuotedLowerCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"person\"");

    // Unexpected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@ParameterizedTest
@ValueSource(strings = {"person", "PERSON", "Person"})
void givenPeopleAndUpperCaseNamingStrategy_whenQueryPersonUnquoted_thenResult(String tableName) {
    Query query = entityManager.createNativeQuery("select * from " + tableName);

    // Unexpected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndUpperCaseNamingStrategy_whenQueryPersonQuotedLowerCase_thenResult() {
    Query query = entityManager.createNativeQuery("select * from \"person\"");

    // Expected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndUpperCaseNamingStrategy_whenQueryPersonQuotedUpperCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"PERSON\"");

    // Unexpected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndSpringNamingStrategy_whenQueryPersonQuotedUpperCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"PERSON\"");

    // Expected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndLowerCaseNamingStrategy_whenQueryPersonQuotedUpperCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"PERSON\"");

    // Expected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndUpperCaseNamingStrategy_whenQueryPersonQuotedLowerCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"person\"");

    // Expected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndLowerCaseNamingStrategy_whenQueryPersonQuotedLowerCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"person\"");

    // Unexpected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@ParameterizedTest
@ValueSource(strings = {"person", "PERSON", "Person"})
void givenPeopleAndLowerCaseNamingStrategy_whenQueryPersonUnquoted_thenException(String tableName) {
    Query query = entityManager.createNativeQuery("select * from " + tableName);

    // Unexpected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndLowerCaseNamingStrategy_whenQueryPersonQuotedUpperCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"PERSON\"");

    // Expected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
void givenPeopleAndUpperCaseNamingStrategy_whenQueryPersonQuotedLowerCase_thenException() {
    Query query = entityManager.createNativeQuery("select * from \"person\"");

    // Expected result
    assertThrows(SQLGrammarException.class, query::getResultStream);
}
 

@Test
public void givenMissingTable_whenEntitySaved_thenSQLGrammarException() {
    thrown.expect(isA(PersistenceException.class));
    thrown.expectCause(isA(SQLGrammarException.class));
    thrown
        .expectMessage("SQLGrammarException: could not prepare statement");

    Configuration cfg = getConfiguration();
    cfg.addAnnotatedClass(Product.class);

    SessionFactory sessionFactory = cfg.buildSessionFactory();
    Session session = null;
    Transaction transaction = null;
    try {

        session = sessionFactory.openSession();
        transaction = session.beginTransaction();
        Product product = new Product();
        product.setId(1);
        product.setName("Product 1");
        session.save(product);
        transaction.commit();
    } catch (Exception e) {
        rollbackTransactionQuietly(transaction);
        throw (e);
    } finally {
        closeSessionQuietly(session);
        closeSessionFactoryQuietly(sessionFactory);
    }
}
 

@Test
public void givenMissingTable_whenQueryExecuted_thenSQLGrammarException() {
    thrown.expect(isA(PersistenceException.class));
    thrown.expectCause(isA(SQLGrammarException.class));
    thrown
        .expectMessage("SQLGrammarException: could not prepare statement");

    Session session = sessionFactory.openSession();
    NativeQuery<Product> query = session.createNativeQuery(
        "select * from NON_EXISTING_TABLE", Product.class);
    query.getResultList();
}
 

private boolean getFoosByNameExists() {
    try {
        Query sqlQuery = session.createSQLQuery("CALL GetAllFoos()").addEntity(Foo.class);
        sqlQuery.list();
        return true;
    } catch (SQLGrammarException e) {
        LOGGER.error("WARNING : GetFoosByName() Procedure is may be missing ", e);
        return false;
    }
}
 

private boolean getAllFoosExists() {
    try {
        Query sqlQuery = session.createSQLQuery("CALL GetAllFoos()").addEntity(Foo.class);
        sqlQuery.list();
        return true;
    } catch (SQLGrammarException e) {
        LOGGER.error("WARNING : GetAllFoos() Procedure is may be missing ", e);
        return false;
    }
}
 

private boolean getFoosByNameExists() {
    try {
        @SuppressWarnings("unchecked")
        NativeQuery<Foo> sqlQuery = session.createSQLQuery("CALL GetAllFoos()").addEntity(Foo.class);
        sqlQuery.list();
        return true;
    } catch (SQLGrammarException e) {
        LOGGER.error("WARNING : GetFoosByName() Procedure is may be missing ", e);
        return false;
    }
}