gu.sql2java.observer.TriggerInstaller Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of sql2java-observer Show documentation
Show all versions of sql2java-observer Show documentation
table listener base row observer UDF
package gu.sql2java.observer;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import gu.sql2java.IDataSourceConfig;
import gu.sql2java.SimpleLog;
import gu.sql2java.exception.RuntimeDaoException;
import static com.google.common.base.Preconditions.checkNotNull;
import static com.google.common.base.Preconditions.checkArgument;
import static gu.sql2java.observer.JDBCUtility.parseSchemaFromJDBCURL;
import static gu.sql2java.observer.JDBCUtility.createConnection;
/**
* 依赖 com.gitee.l0km:sql2java-velocity
* ro_notify UDF及触发器安装 需要 {@code msql:mysql-connector-java} 支持
*
* @author guyadong
*
*/
public class TriggerInstaller {
private boolean debug = false;
public TriggerInstaller(){
this(false);
}
public TriggerInstaller(boolean debug) {
this.debug = debug;
}
/**
* 生成触发器创建SQL脚本，并通过JDBC接口执行SQL
*
* @param triggerGenerator
* 为{@code null}使用默认构造对象
* @param schematable
* 表名(schema+tablename)
* @param url JDBC URL
* @param user user name for connection
* @param password password for connection
* @param install
* 为true生成安装脚本,否则生成删除脚本
*/
private void generateTriggerAndRun(TriggerGenerator triggerGenerator, String schematable, String url, String user,
String password, boolean install) {
SimpleLog.log("{} trigger for {}",install ? "install": "remove",schematable);
Properties properties = new Properties();
properties.put("delimiter", false);
String sql;
Connection connection = null;
try {
if (null == triggerGenerator) {
try {
connection = createConnection(url,user,password,null);
triggerGenerator = new TriggerGenerator(connection);
} catch (SQLException e) {
throw new RuntimeDaoException(e);
}
}
sql = triggerGenerator.generateTriggerSQL(checkNotNull(schematable, "schematable is null"), properties, install);
JDBCUtility.runMultiSQL(sql, url, user, password, debug);
} catch (SQLException e) {
throw new RuntimeDaoException(e);
} finally {
if(null != connection){
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeDaoException(e);
}
}
}
}
/**
* 生成触发器创建SQL脚本，并通过JDBC接口执行SQL,安装触发器
*
* @param triggerGenerator 触发器生成对象
* @param schematable
* 表名(schema+tablename)
* @param url JDBC URL
* @param user user name for connection
* @param password password for connection
* @throws SQLException
*/
public void installTrigger(TriggerGenerator triggerGenerator, String schematable, String url, String user,
String password) throws SQLException {
generateTriggerAndRun(checkNotNull(triggerGenerator,"triggerGenerator is null"), schematable, url, user, password, true);
}
/**
* 生成触发器创建SQL脚本，并通过JDBC接口执行SQL,安装触发器
* @param schematable
* 表名(schema+tablename)
* @param url
* @param user
* @param password
* @param dbprefix
* 数据库名,为{@code null}使用默认值'mysql'-(目前只支持'mysql')
*
* @throws SQLException
*/
public void installTrigger( String schematable,String url, String user, String password,
String dbprefix) throws SQLException {
Connection connection = null;
try {
connection = createConnection(url,user,password,null);
TriggerGenerator triggerGenerator = new TriggerGenerator(connection).setDbprefix(dbprefix);
generateTriggerAndRun(triggerGenerator, schematable, url, user, password, true);
} finally {
if(null != connection){
connection.close();
}
}
}
/**
* 生成触发器创建SQL脚本，并通过JDBC接口执行SQL,安装触发器
* @param tablename
* 表名
* @param config
* 数据库配置对象
*/
public void installTrigger(String tablename, IDataSourceConfig config) {
checkArgument(null != config, "config is null");
String schema = parseSchemaFromJDBCURL(config.getJdbcUrl());
generateTriggerAndRun(new TriggerGenerator(), schema + "." + tablename, config.getJdbcUrl(), config.getJdbcUsername(),
config.getJdbcPassword(), true);
}
/**
* 通过JDBC接口执行SQL删除触发器
*
* @param schematable
* 表名([$dbhostname/]$schema.$tablename),
* {@code dbhostname}为数据库主机名，当有多个数据库主机连接同一个消息系统时用于在消息频道名中区分不同的数据库主机,可为空
* @param url JDBC URL
* @param user user name for connection
* @param password password for connection
* @param dbprefix
* 数据库名,为{@code null}使用默认值'mysql'-(目前只支持'mysql')
*/
public void removeTrigger(String schematable, String url, String user, String password, String dbprefix) {
TriggerGenerator triggerGenerator = new TriggerGenerator().setDbprefix(dbprefix);
generateTriggerAndRun(triggerGenerator, schematable, url, user, password, false);
}
/**
* 通过JDBC接口执行SQL删除触发器
*
* @param tablename
* 表名
* @param config
* 数据库配置对象
*/
public void removeTrigger(String tablename, IDataSourceConfig config) {
checkArgument(null != config, "config is null");
String schema = parseSchemaFromJDBCURL(config.getJdbcUrl());
generateTriggerAndRun(null, schema + "." + tablename, config.getJdbcUrl(), config.getJdbcUsername(),
config.getJdbcPassword(), false);
}
private void generateUDFAndRun(String url, String user, String password, boolean install) {
try {
SimpleLog.log("{} ro_nofity UDF",install ? "install": "remove");
String drop = "DROP FUNCTION IF EXISTS ro_notify;\n" + "DROP FUNCTION IF EXISTS ro_config;\n";
String create = "CREATE FUNCTION ro_notify RETURNS INTEGER SONAME 'mysql_observer';\n"
+ "CREATE FUNCTION ro_config RETURNS INTEGER SONAME 'mysql_observer';\n";
String sql = install ? drop + create : drop;
JDBCUtility.runMultiSQL(sql, url, user, password, debug);
} catch (SQLException e) {
throw new RuntimeDaoException(e);
}
}
/**
* 安装ro_notify UDF函数
*
* @param url JDBC URL
* @param user user name for connection
* @param password password for connection
*/
public void installUDF(String url, String user, String password) {
generateUDFAndRun(url, user, password, true);
}
/**
* 安装ro_notify UDF函数
*
* @param config
*/
public void installUDF(IDataSourceConfig config) {
checkArgument(null != config, "config is null");
generateUDFAndRun(config.getJdbcUrl(), config.getJdbcUsername(), config.getJdbcPassword(), true);
}
/**
* 删除ro_notify UDF函数
*
* @param url JDBC URL
* @param user user name for connection
* @param password password for connection
*/
public void removeUDF(String url, String user, String password) {
generateUDFAndRun(url, user, password, false);
}
/**
* 删除ro_notify UDF函数
* @param config
*/
public void removeUDF(IDataSourceConfig config) {
checkArgument(null != config, "config is null");
generateUDFAndRun(config.getJdbcUrl(), config.getJdbcUsername(), config.getJdbcPassword(), false);
}
}