org.springframework.jdbc.BadSqlGrammarException Java Examples

@Test
public void testCustomErrorCodeTranslation() {

	SQLExceptionTranslator sext = new SQLErrorCodeSQLExceptionTranslator(ERROR_CODES);

	SQLException dataIntegrityViolationEx = SQLExceptionSubclassFactory.newSQLDataException("", "", 1);
	DataAccessException daeex = sext.translate("task", "SQL", dataIntegrityViolationEx);
	assertEquals(dataIntegrityViolationEx, daeex.getCause());
	assertTrue(daeex instanceof BadSqlGrammarException);

	SQLException dataAccessResourceEx = SQLExceptionSubclassFactory.newSQLDataException("", "", 2);
	DataAccessException darex = sext.translate("task", "SQL", dataAccessResourceEx);
	assertEquals(dataIntegrityViolationEx, daeex.getCause());
	assertTrue(darex instanceof TransientDataAccessResourceException);

}
 

@Test
public void createAndDestroy() throws Exception {
	ClassPathXmlApplicationContext context = context("jdbc-destroy-config.xml");
	try {
		DataSource dataSource = context.getBean(DataSource.class);
		JdbcTemplate template = new JdbcTemplate(dataSource);
		assertNumRowsInTestTable(template, 1);
		context.getBean(DataSourceInitializer.class).destroy();
		// Table has been dropped
		assertThatExceptionOfType(BadSqlGrammarException.class).isThrownBy(() ->
				assertNumRowsInTestTable(template, 1));
	}
	finally {
		context.close();
	}
}
 

@Test
@SuppressWarnings("resource")
public void customErrorCodeTranslation() {
	new ClassPathXmlApplicationContext("test-custom-translators-context.xml",
			CustomSQLExceptionTranslatorRegistrarTests.class);

	SQLErrorCodes codes = SQLErrorCodesFactory.getInstance().getErrorCodes("H2");
	SQLErrorCodeSQLExceptionTranslator sext = new SQLErrorCodeSQLExceptionTranslator();
	sext.setSqlErrorCodes(codes);

	DataAccessException exFor4200 = sext.doTranslate("", "", new SQLException("Ouch", "42000", 42000));
	assertNotNull("Should have been translated", exFor4200);
	assertTrue("Should have been instance of BadSqlGrammarException",
		BadSqlGrammarException.class.isAssignableFrom(exFor4200.getClass()));

	DataAccessException exFor2 = sext.doTranslate("", "", new SQLException("Ouch", "42000", 2));
	assertNotNull("Should have been translated", exFor2);
	assertTrue("Should have been instance of TransientDataAccessResourceException",
		TransientDataAccessResourceException.class.isAssignableFrom(exFor2.getClass()));

	DataAccessException exFor3 = sext.doTranslate("", "", new SQLException("Ouch", "42000", 3));
	assertNull("Should not have been translated", exFor3);
}
 

@Test
public void testNoSuchStoredProcedure() throws Exception {
	final String NO_SUCH_PROC = "x";
	SQLException sqlException = new SQLException("Syntax error or access violation exception", "42000");
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getUserName()).willReturn("me");
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
	given(callableStatement.execute()).willThrow(sqlException);
	given(connection.prepareCall("{call " + NO_SUCH_PROC + "()}")).willReturn(callableStatement);
	SimpleJdbcCall sproc = new SimpleJdbcCall(dataSource).withProcedureName(NO_SUCH_PROC);
	thrown.expect(BadSqlGrammarException.class);
	thrown.expect(exceptionCause(sameInstance(sqlException)));
	try {
		sproc.execute();
	}
	finally {
		verify(callableStatement).close();
		verify(connection, atLeastOnce()).close();
	}
}
 

@Test
public void createAndDestroyNestedWithHsql() throws Exception {
	ClassPathXmlApplicationContext context = context("jdbc-destroy-nested-config.xml");
	try {
		DataSource dataSource = context.getBean(DataSource.class);
		JdbcTemplate template = new JdbcTemplate(dataSource);
		assertNumRowsInTestTable(template, 1);
		context.getBean(EmbeddedDatabaseFactoryBean.class).destroy();
		// Table has been dropped
		assertThatExceptionOfType(BadSqlGrammarException.class).isThrownBy(() ->
				assertNumRowsInTestTable(template, 1));
	}
	finally {
		context.close();
	}
}
 

@Test
public void createAndDestroyNestedWithH2() throws Exception {
	ClassPathXmlApplicationContext context = context("jdbc-destroy-nested-config-h2.xml");
	try {
		DataSource dataSource = context.getBean(DataSource.class);
		JdbcTemplate template = new JdbcTemplate(dataSource);
		assertNumRowsInTestTable(template, 1);
		context.getBean(EmbeddedDatabaseFactoryBean.class).destroy();
		 // Table has been dropped
		assertThatExceptionOfType(BadSqlGrammarException.class).isThrownBy(() ->
				assertNumRowsInTestTable(template, 1));
	}
	finally {
		context.close();
	}
}
 

@Test
public void testSQLErrorCodeTranslation() throws Exception {
	final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	mockDatabaseMetaData(false);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	assertThatExceptionOfType(BadSqlGrammarException.class).isThrownBy(() ->
			this.template.query(sql, (RowCallbackHandler) rs -> {
				throw sqlException;
			}))
		.withCause(sqlException);
	verify(this.resultSet).close();
	verify(this.preparedStatement).close();
	verify(this.connection, atLeastOnce()).close();
}
 

@Test
public void testSQLErrorCodeTranslationWithSpecifiedDbName() throws Exception {
	final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	JdbcTemplate template = new JdbcTemplate();
	template.setDataSource(this.dataSource);
	template.setDatabaseProductName("MySQL");
	template.afterPropertiesSet();

	assertThatExceptionOfType(BadSqlGrammarException.class).isThrownBy(() ->
			template.query(sql, (RowCallbackHandler) rs -> {
				throw sqlException;
			}))
		.withCause(sqlException);
	verify(this.resultSet).close();
	verify(this.preparedStatement).close();
	verify(this.connection).close();
}
 

/**
 * Test that we see an SQLException translated using Error Code.
 * If we provide the SQLExceptionTranslator, we shouldn't use a connection
 * to get the metadata
 */
@Test
public void testUseCustomSQLErrorCodeTranslator() throws Exception {
	// Bad SQL state
	final SQLException sqlException = new SQLException("I have a known problem", "07000", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	JdbcTemplate template = new JdbcTemplate();
	template.setDataSource(this.dataSource);
	// Set custom exception translator
	template.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
	template.afterPropertiesSet();

	assertThatExceptionOfType(BadSqlGrammarException.class).isThrownBy(() ->
			template.query(sql, (RowCallbackHandler) rs -> {
				throw sqlException;
			}))
		.withCause(sqlException);
	verify(this.resultSet).close();
	verify(this.preparedStatement).close();
	verify(this.connection).close();
}
 

@Test
public void testNoSuchStoredProcedure() throws Exception {
	final String NO_SUCH_PROC = "x";
	SQLException sqlException = new SQLException("Syntax error or access violation exception", "42000");
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getUserName()).willReturn("me");
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
	given(callableStatement.execute()).willThrow(sqlException);
	given(connection.prepareCall("{call " + NO_SUCH_PROC + "()}")).willReturn(callableStatement);
	SimpleJdbcCall sproc = new SimpleJdbcCall(dataSource).withProcedureName(NO_SUCH_PROC);
	try {
		assertThatExceptionOfType(BadSqlGrammarException.class).isThrownBy(() ->
				sproc.execute())
			.withCause(sqlException);
	}
	finally {
		verify(callableStatement).close();
		verify(connection, atLeastOnce()).close();
	}
}
 

@Test
public void testSQLErrorCodeTranslation() throws Exception {
	final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	mockDatabaseMetaData(false);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	this.thrown.expect(BadSqlGrammarException.class);
	this.thrown.expect(exceptionCause(sameInstance(sqlException)));
	try {
		this.template.query(sql, (RowCallbackHandler) rs -> {
			throw sqlException;
		});
		fail("Should have thrown BadSqlGrammarException");
	}
	finally {
		verify(this.resultSet).close();
		verify(this.preparedStatement).close();
		verify(this.connection, atLeastOnce()).close();
	}
}
 

/**
 * 捕获sql语法异常，原生的Message不适合中文环境
 *
 * @param request
 * @param e
 * @return
 */
@ExceptionHandler(BadSqlGrammarException.class)
public Result handleSQLSyntaxErrorException(HttpServletRequest request, BadSqlGrammarException e) {
    String message = e.getSQLException().getMessage();
    Map<String, String> regexMap = new HashMap<>();
    //数据表不存在
    regexMap.put("Table '(\\S+)' doesn't exist", "表($1)不存在");
    //唯一键
    regexMap.put("Duplicate entry '(\\S+)' for key '(\\S+)'", "$1已经存在了");
    for (Map.Entry<String, String> entry : regexMap.entrySet()) {
        if (message.matches(entry.getKey())) {
            message = message.replaceAll(entry.getKey(), entry.getValue());
            break;
        }
    }
    log.warn(">>> Handle SQLSyntaxErrorException, url is {}, message is {}", request.getRequestURI(), message);
    return Result.failure(CommonResultStatus.INTERNAL_SERVER_ERROR, message);
}
 

@Test
@SuppressWarnings("resource")
public void customErrorCodeTranslation() {
	new ClassPathXmlApplicationContext("test-custom-translators-context.xml",
			CustomSQLExceptionTranslatorRegistrarTests.class);

	SQLErrorCodes codes = SQLErrorCodesFactory.getInstance().getErrorCodes("H2");
	SQLErrorCodeSQLExceptionTranslator sext = new SQLErrorCodeSQLExceptionTranslator();
	sext.setSqlErrorCodes(codes);

	DataAccessException exFor4200 = sext.doTranslate("", "", new SQLException("Ouch", "42000", 42000));
	assertNotNull("Should have been translated", exFor4200);
	assertTrue("Should have been instance of BadSqlGrammarException",
		BadSqlGrammarException.class.isAssignableFrom(exFor4200.getClass()));

	DataAccessException exFor2 = sext.doTranslate("", "", new SQLException("Ouch", "42000", 2));
	assertNotNull("Should have been translated", exFor2);
	assertTrue("Should have been instance of TransientDataAccessResourceException",
		TransientDataAccessResourceException.class.isAssignableFrom(exFor2.getClass()));

	DataAccessException exFor3 = sext.doTranslate("", "", new SQLException("Ouch", "42000", 3));
	assertNull("Should not have been translated", exFor3);
}
 

@Test
public void testSQLErrorCodeTranslation() throws Exception {
	final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	mockDatabaseMetaData(false);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	this.thrown.expect(BadSqlGrammarException.class);
	this.thrown.expect(exceptionCause(sameInstance(sqlException)));
	try {
		this.template.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				throw sqlException;
			}
		});
		fail("Should have thrown BadSqlGrammarException");
	}
	finally {
		verify(this.resultSet).close();
		verify(this.preparedStatement).close();
		verify(this.connection, atLeastOnce()).close();
	}
}
 

@Test
public void testSQLErrorCodeTranslation() throws Exception {
	final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	mockDatabaseMetaData(false);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	this.thrown.expect(BadSqlGrammarException.class);
	this.thrown.expect(exceptionCause(sameInstance(sqlException)));
	try {
		this.template.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				throw sqlException;
			}
		});
		fail("Should have thrown BadSqlGrammarException");
	}
	finally {
		verify(this.resultSet).close();
		verify(this.preparedStatement).close();
		verify(this.connection, atLeastOnce()).close();
	}
}
 

@Test
public void testNoSuchStoredProcedure() throws Exception {
	final String NO_SUCH_PROC = "x";
	SQLException sqlException = new SQLException("Syntax error or access violation exception", "42000");
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getUserName()).willReturn("me");
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
	given(callableStatement.execute()).willThrow(sqlException);
	given(connection.prepareCall("{call " + NO_SUCH_PROC + "()}")).willReturn(callableStatement);
	SimpleJdbcCall sproc = new SimpleJdbcCall(dataSource).withProcedureName(NO_SUCH_PROC);
	thrown.expect(BadSqlGrammarException.class);
	thrown.expect(exceptionCause(sameInstance(sqlException)));
	try {
		sproc.execute();
	}
	finally {
		verify(callableStatement).close();
		verify(connection, atLeastOnce()).close();
	}
}
 

@Override
public String createViewTable( SqlView sqlView )
{
    dropViewTable( sqlView );

    final String sql = TYPE_CREATE_PREFIX_MAP.get( sqlView.getType() ) + statementBuilder.columnQuote( sqlView.getViewName() ) + " AS " + sqlView.getSqlQuery();

    log.debug( "Create view SQL: " + sql );

    try
    {
        jdbcTemplate.execute( sql );

        return null;
    }
    catch ( BadSqlGrammarException ex )
    {
        return ex.getCause().getMessage();
    }
}
 

@Override
public String testSqlGrammar( String sql )
{
    String viewName = SqlView.PREFIX_VIEWNAME + System.currentTimeMillis();

    sql = "CREATE VIEW " + viewName + " AS " + sql;

    log.debug( "Test view SQL: " + sql );

    try
    {
        jdbcTemplate.execute( sql );

        jdbcTemplate.execute( "DROP VIEW IF EXISTS " + viewName );
    }
    catch ( BadSqlGrammarException | UncategorizedSQLException ex )
    {
        return ex.getCause().getMessage();
    }

    return null;
}
 

@Override
public void afterPropertiesSet() throws Exception {
    Assert.state(this.dataSource != null, "DataSource must be set");
    Assert.state(this.databasePopulator != null, "DatabasePopulator must be set");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
    boolean existingData;
    try {
        existingData = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM QRTZ_SCHEDULER_STATE", Integer.class) > 0;
    } catch (BadSqlGrammarException e) {
        LOG.debug("Could not access to table QRTZ_SCHEDULER_STATE", e);
        existingData = false;
    }

    if (existingData) {
        LOG.info("Quartz tables found in the database, leaving untouched");
    } else {
        LOG.info("No Quartz tables found, creating");

        DatabasePopulatorUtils.execute(databasePopulator, this.dataSource);
    }
}
 

@Override
public ErrorLevel level(Throwable e, SyncWrapper wrapper, int maxTry) {
  /*
   * Possible reasons for DuplicateKey
   * 1. the first failed, the second succeed. Then restart, then the second will send again and cause this
   * 2. duplicate entry in binlog file: load data into db multiple time
   * 3. the data is sync to mysql but not receive response before syncer shutdown
   */
  if (e instanceof DuplicateKeyException) {
    return ErrorLevel.WARN;
  }
  if (e instanceof BadSqlGrammarException) {
    return ErrorLevel.SYNCER_BUG;
  }
  return BufferedChannel.super.level(e, wrapper, maxTry);
}
 

@Test
public void createAndDestroyNestedWithH2() throws Exception {
	ClassPathXmlApplicationContext context = context("jdbc-destroy-nested-config-h2.xml");
	try {
		DataSource dataSource = context.getBean(DataSource.class);
		JdbcTemplate template = new JdbcTemplate(dataSource);
		assertNumRowsInTestTable(template, 1);
		context.getBean(EmbeddedDatabaseFactoryBean.class).destroy();
		expected.expect(BadSqlGrammarException.class); // Table has been dropped
		assertNumRowsInTestTable(template, 1);
	}
	finally {
		context.close();
	}
}
 

@Test
public void badSqlGrammar() {
	SQLException sex = new SQLException("Message", "42001", 1);
	try {
		throw this.trans.translate("task", sql, sex);
	}
	catch (BadSqlGrammarException ex) {
		// OK
		assertTrue("SQL is correct", sql.equals(ex.getSql()));
		assertTrue("Exception matches", sex.equals(ex.getSQLException()));
	}
}
 

@Test
public void badSqlGrammarException() {
	SQLException badSqlGrammarExceptionEx = SQLExceptionSubclassFactory.newSQLDataException("", "", 1);
	DataAccessException dae = sext.translate("task", "SQL", badSqlGrammarExceptionEx);
	assertEquals(badSqlGrammarExceptionEx, dae.getCause());
	assertThat(dae, instanceOf(BadSqlGrammarException.class));
}
 

@Test
public void badSqlGrammar() {
	SQLException sex = new SQLException("Message", "42001", 1);
	try {
		throw this.trans.translate("task", sql, sex);
	}
	catch (BadSqlGrammarException ex) {
		// OK
		assertTrue("SQL is correct", sql.equals(ex.getSql()));
		assertTrue("Exception matches", sex.equals(ex.getSQLException()));
	}
}
 

@Test
public void testNoSuchStoredProcedure() throws Exception {
	SQLException sqlException = new SQLException(
			"Syntax error or access violation exception", "42000");
	given(callableStatement.execute()).willThrow(sqlException);
	given(connection.prepareCall("{call " + NoSuchStoredProcedure.SQL + "()}")).willReturn(
			callableStatement);

	NoSuchStoredProcedure sproc = new NoSuchStoredProcedure(dataSource);
	thrown.expect(BadSqlGrammarException.class);
	sproc.execute();
}
 

@Test
public void testSQLErrorCodeTranslationWithSpecifiedDbName() throws Exception {
	final SQLException sqlException = new SQLException("I have a known problem", "99999", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	JdbcTemplate template = new JdbcTemplate();
	template.setDataSource(this.dataSource);
	template.setDatabaseProductName("MySQL");
	template.afterPropertiesSet();

	this.thrown.expect(BadSqlGrammarException.class);
	this.thrown.expect(exceptionCause(sameInstance(sqlException)));
	try {
		template.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				throw sqlException;
			}
		});
	}
	finally {
		verify(this.resultSet).close();
		verify(this.preparedStatement).close();
		verify(this.connection).close();
	}
}
 

/**
 * Test that we see an SQLException translated using Error Code.
 * If we provide the SQLExceptionTranslator, we shouldn't use a connection
 * to get the metadata
 */
@Test
public void testUseCustomSQLErrorCodeTranslator() throws Exception {
	// Bad SQL state
	final SQLException sqlException = new SQLException("I have a known problem", "07000", 1054);
	final String sql = "SELECT ID FROM CUSTOMER";

	given(this.resultSet.next()).willReturn(true);
	given(this.connection.createStatement()).willReturn(this.preparedStatement);

	JdbcTemplate template = new JdbcTemplate();
	template.setDataSource(this.dataSource);
	// Set custom exception translator
	template.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
	template.afterPropertiesSet();

	this.thrown.expect(BadSqlGrammarException.class);
	this.thrown.expect(exceptionCause(sameInstance(sqlException)));
	try {
		template.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				throw sqlException;
			}
		});
	}
	finally {
		verify(this.resultSet).close();
		verify(this.preparedStatement).close();
		verify(this.connection).close();
	}
}
 

@AfterTransaction
public void afterTransaction() {
	if ("test01".equals(testName.getMethodName())) {
		try {
			assertNumUsers(99);
			fail("Should throw a BadSqlGrammarException after test01, assuming 'drop-schema.sql' was executed");
		}
		catch (BadSqlGrammarException e) {
			/* expected */
		}
	}
}
 

/**
 * Indicates whether the given table exists and has at least one row.
 *
 * @param tableName the table name.
 */
protected boolean hasRows( String tableName )
{
    final String sql = "select * from " + tableName + " limit 1";

    try
    {
        return jdbcTemplate.queryForRowSet( sql ).next();
    }
    catch ( BadSqlGrammarException ex )
    {
        return false;
    }
}
 

@Override
public boolean viewTableExists( String viewTableName )
{
    try
    {
        jdbcTemplate.queryForRowSet( "select * from " + statementBuilder.columnQuote( viewTableName ) + " limit 1" );

        return true;
    }
    catch ( BadSqlGrammarException ex )
    {
        return false; // View does not exist
    }
}