@Bean
public DataSource getDataSource() throws SQLException {
DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
dataSourceBuilder.driverClassName("org.h2.Driver");
dataSourceBuilder.url("jdbc:h2:mem:test;database_to_upper=false;");
dataSourceBuilder.username("sa");
dataSourceBuilder.password("").build();
DataSource dataSource = dataSourceBuilder.build();
Connection connection = null;
try {
ClassPathResource classPathResource = new ClassPathResource("test.sql");
connection = dataSource.getConnection();
ScriptRunner runner = new ScriptRunner(connection);
runner.setErrorLogWriter(null);
runner.setLogWriter(null);
// 执行SQL脚本
runner.runScript(new InputStreamReader(classPathResource.getInputStream(), StandardCharsets.UTF_8));
PreparedStatement preparedStatement2 = connection.prepareStatement("select * from `p_event`");
ResultSet resultSet2 = preparedStatement2.executeQuery();
while (resultSet2.next()) {
int id = resultSet2.getInt("id");
System.out.println("id:>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"+id );
}
PreparedStatement preparedStatement = connection.prepareStatement("select * from `p_event_record_detail`");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
System.out.println("id:>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"+id );
}
// 关闭连接
connection.close();
// 若成功，打印提示信息
System.out.println("====== SUCCESS ======");
} catch (Exception e) {
log.error(e.getMessage());
} finally {
if (connection != null) {
connection.close();
}
}
return dataSource;
}
如上代码：
读取完脚本之后，先查询了`p_event`表，查询结果正常。但是在查询`p_event_record_detail`表时，总是抛出异常，异常信息为找不到该表。
最终排查结果：
db2的sql脚本语法中：注释（comment）的内容中不能包含英文字符分号(;)。
正确写法：
`fail_rollback` tinyint(5) NOT NULL DEFAULT 1 COMMENT ‘同步事件回滚标记：0：回滚；1：不回滚‘
错误写法：(注意sql中的分号，此处为英文符号)
`fail_rollback` tinyint(5) NOT NULL DEFAULT 1 COMMENT ‘同步事件回滚标记：0：回滚;1：不回滚‘