package foundation.persist;
|
|
import java.math.BigDecimal;
|
import java.sql.DatabaseMetaData;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.ResultSetMetaData;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import org.apache.logging.log4j.LogManager;
|
import org.apache.logging.log4j.Logger;
|
|
import foundation.data.entity.Entity;
|
import foundation.data.entity.EntitySet;
|
import foundation.data.entity.EntityTree;
|
import foundation.data.getter.EntityGetter;
|
import foundation.data.getter.EntitySetGetter;
|
import foundation.data.getter.EntityTreeGetter;
|
import foundation.data.getter.ObjectLoader;
|
import foundation.data.getter.ValueLoader;
|
import foundation.data.object.Sequence;
|
import foundation.data.object.SequenceValue;
|
import foundation.persist.source.ConnectionAgent;
|
import foundation.persist.source.DataSourceManager;
|
import foundation.persist.source.NamedDataSource;
|
import foundation.server.config.DBaseType;
|
|
|
public class SQLRunner {
|
|
protected static Logger logger;
|
protected static ThreadLocal<ConnectionAgent> activeConn;
|
|
static {
|
logger = LogManager.getLogger(SQLRunner.class);
|
activeConn = new ThreadLocal<ConnectionAgent>();
|
}
|
|
public static synchronized String beginTrans() throws SQLException {
|
ConnectionAgent conn = activeConn.get();
|
|
if (conn == null) {
|
conn = DataSourceManager.getConnection();
|
conn.setAutoCommit(false);
|
|
activeConn.set(conn);
|
return conn.getToken();
|
}
|
|
return "none";
|
}
|
|
public static void commit(String token) throws Exception {
|
ConnectionAgent conn = activeConn.get();
|
|
if (conn == null) {
|
return;
|
}
|
|
if ((token != null) && !token.equals(conn.getToken())) {
|
return;
|
}
|
|
try {
|
conn.commit();
|
}
|
finally {
|
try {
|
conn.close();
|
}
|
catch (Exception e) {
|
}
|
|
activeConn.remove();
|
}
|
}
|
|
public static void commit() throws Exception {
|
commit(null);
|
}
|
|
public static void rollback(String token) throws Exception {
|
ConnectionAgent conn = activeConn.get();
|
|
if (conn == null) {
|
return;
|
}
|
|
if ((token != null) && !token.equals(conn.getToken())) {
|
return;
|
}
|
|
try {
|
conn.rollback();
|
}
|
finally {
|
try {
|
conn.close();
|
}
|
catch (Exception e) {
|
}
|
|
activeConn.remove();
|
}
|
}
|
|
public static void rollback() throws Exception {
|
rollback(null);
|
}
|
|
public static int execSQL(NamedSQL namedSQL) throws Exception {
|
return execSQL(null, namedSQL);
|
}
|
|
public static int execSQL(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
Statement stmt = null; int result = 0;
|
|
//1. 根据数据库类型生成特定的SQL
|
String sql = namedSQL.toString();
|
logger.debug(sql);
|
|
try {
|
//2. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
//3. 执行SQL
|
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
result = stmt.executeUpdate(sql);
|
}
|
catch (SQLException e) {
|
onError(sql, e);
|
throw e;
|
}
|
finally {
|
try {
|
if (stmt != null) {
|
stmt.close();
|
}
|
} catch (SQLException e) {
|
} finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
|
return result;
|
}
|
|
public static void getData(NamedSQL namedSQL, ILoadable loadable, Object ...args) throws Exception {
|
getData(null, namedSQL, loadable, args);
|
}
|
|
public static void getData(IStepLoadable loadable, Object ...args) throws Exception {
|
getData(null, loadable, args);
|
}
|
|
public static void getData(NamedDataSource dataSource, NamedSQL namedSQL, ILoadable loadable, Object ...args) throws Exception {
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
Statement stmt = null; ResultSet rslt = null;
|
|
//1. 根据数据库类型生成特定的SQL
|
String sql = namedSQL.toString();
|
logger.debug(sql);
|
|
try {
|
//2. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
//3. 执行SQL
|
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
rslt = stmt.executeQuery(sql);
|
|
loadable.load(rslt, args);
|
}
|
catch (SQLException e) {
|
logger.error("getDataFromDB error:" + e.getMessage());
|
throw e;
|
}
|
finally {
|
try {
|
if (rslt != null) {
|
rslt.close();
|
}
|
} catch (SQLException e) {
|
}
|
finally {
|
try {
|
if (stmt != null) {
|
stmt.close();
|
}
|
} catch (SQLException e) {
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
}
|
}
|
|
public static void getData(NamedDataSource dataSource, IStepLoadable loadable, Object ...args) throws Exception {
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
Statement stmt = null; ResultSet rslt = null;
|
|
try {
|
//1. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
//3. 执行SQL
|
while (loadable.hasNextForLoad()) {
|
//1. 根据数据库类型生成特定的SQL
|
NamedSQL stepSQL = loadable.getStepSQL();
|
String sql = stepSQL.toString();
|
logger.debug(sql);
|
|
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
try {
|
try {
|
rslt = stmt.executeQuery(sql);
|
loadable.loadResultSet(rslt, args);
|
}
|
finally {
|
if (rslt != null) {
|
rslt.close();
|
}
|
}
|
}
|
finally {
|
if (stmt != null) {
|
stmt.close();
|
}
|
}
|
}
|
}
|
catch (SQLException e) {
|
logger.error("getDataFromDB error:" + e.getMessage());
|
throw e;
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
|
public static long getSequence(NamedDataSource dataSource, Sequence sequence, SequenceValue value) throws Exception {
|
long result = 0;
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
|
try {
|
//1. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
result = sequence.exec(conn.getConn(), value);
|
}
|
catch (SQLException e) {
|
logger.error("getDataFromDB error:" + e.getMessage());
|
throw e;
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
|
return result;
|
}
|
|
public static void saveData(NamedSQL namedSQL1, NamedSQL namedSQL2, IDoubleSavable savable, Object ...args) throws Exception {
|
saveData(null, namedSQL1, namedSQL2, savable, args);
|
}
|
|
public static void saveData(NamedSQL namedSQL, ISavable savable, Object ...args) throws Exception {
|
saveData(null, namedSQL, savable, args);
|
}
|
|
public static void saveData(NamedDataSource dataSource, NamedSQL namedSQL, ISavable savable, Object ...args) throws Exception {
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
PreparedStatement stmt = null; ResultSet rslt = null;
|
|
//1. 根据数据库类型生成特定的SQL
|
String sql = namedSQL.toString();
|
logger.debug(sql);
|
|
try {
|
//2. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
//3. 执行SQL
|
stmt = conn.prepareStatement(sql);
|
savable.saveData(stmt, args);
|
}
|
catch (SQLException e) {
|
throw e;
|
}
|
finally {
|
try {
|
if (rslt != null) {
|
rslt.close();
|
}
|
} catch (SQLException e) {
|
}
|
finally {
|
try {
|
if (stmt != null) {
|
stmt.close();
|
}
|
} catch (SQLException e) {
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
}
|
}
|
|
public static void saveData(NamedDataSource dataSource, NamedSQL namedSQL1, NamedSQL namedSQL2, IDoubleSavable savable, Object ...args) throws Exception {
|
PreparedStatement stmt1 = null, stmt2 = null;
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
|
//1. 根据数据库类型生成特定的SQL
|
String sql1 = namedSQL1.toString();
|
logger.debug(sql1);
|
|
String sql2 = namedSQL2.toString();
|
logger.debug(sql2);
|
|
try {
|
//2. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
//3. 执行SQL
|
stmt1 = conn.prepareStatement(sql1);
|
stmt2 = conn.prepareStatement(sql2);
|
|
savable.save(stmt1, stmt2, args);
|
}
|
catch (SQLException e) {
|
logger.error("getDataFromDB error:" + e.getMessage());
|
throw e;
|
} finally {
|
try {
|
if (stmt1 != null) {
|
stmt1.close();
|
}
|
} catch (SQLException e) {
|
}
|
finally {
|
try {
|
if (stmt2 != null) {
|
stmt2.close();
|
}
|
} catch (SQLException e) {
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
}
|
}
|
|
public static void saveData(NamedSQL namedSQL, IStepSavable stepSavable, Object ...args) throws Exception {
|
saveData(null, namedSQL, stepSavable, args);
|
}
|
|
public static void saveData(NamedDataSource dataSource, NamedSQL namedSQL, IStepSavable stepSavable, Object ...args) throws Exception {
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
PreparedStatement stmt = null;
|
|
//1. 根据数据库类型生成特定的SQL
|
String sql = namedSQL.toString();
|
logger.debug(sql);
|
|
try {
|
//2. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
//3. 执行SQL
|
while (stepSavable.hasNextForSave()) {
|
stmt = conn.prepareStatement(sql);
|
|
try {
|
stepSavable.save(stmt, args);
|
}
|
finally {
|
if (stmt != null) {
|
stmt.close();
|
}
|
}
|
}
|
}
|
catch (SQLException e) {
|
logger.debug(sql);
|
logger.error("getDataFromDB error:" + e.getMessage());
|
throw e;
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
|
public static EntitySet getEntitySet(NamedSQL namedSQL) throws Exception {
|
EntitySet result = null;
|
|
EntitySetGetter entitySetLoader = new EntitySetGetter(null);
|
getData(namedSQL, entitySetLoader);
|
result = entitySetLoader.getDataSet();
|
|
return result;
|
}
|
|
public static EntitySet getEntitySet(NamedSQL namedSQL, EntitySetGetter entitySetLoader) throws Exception {
|
return getEntitySet(null, namedSQL, entitySetLoader);
|
}
|
|
public static EntitySet getEntitySet(NamedDataSource dataSource, NamedSQL namedSQL, EntitySetGetter entitySetLoader) throws Exception {
|
EntitySet result = null;
|
|
getData(dataSource, namedSQL, entitySetLoader);
|
result = entitySetLoader.getDataSet();
|
|
return result;
|
}
|
|
public static EntityTree getEntityTree(NamedSQL namedSQL) throws Exception {
|
EntityTree result = null;
|
|
EntityTreeGetter entityTreeLoader = new EntityTreeGetter(null);
|
getData(namedSQL, entityTreeLoader);
|
result = entityTreeLoader.getEntityTree();
|
|
return result;
|
}
|
|
public static EntityTree getEntityTree(NamedDataSource dataSource, NamedSQL namedSQL, EntityTreeGetter entityTreeLoader) throws Exception {
|
EntityTree result = null;
|
|
getData(dataSource, namedSQL, entityTreeLoader);
|
result = entityTreeLoader.getEntityTree();
|
|
return result;
|
}
|
|
public static Entity getEntity(NamedSQL namedSQL) throws Exception {
|
return getEntity(namedSQL, new EntityGetter());
|
}
|
|
public static Entity getEntity(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
|
return getEntity(dataSource, namedSQL, new EntityGetter());
|
}
|
|
public static Entity getEntity(NamedSQL namedSQL, EntityGetter entityLoader) throws Exception {
|
return getEntity(null, namedSQL, entityLoader);
|
}
|
|
public static Entity getEntity(NamedDataSource dataSource, NamedSQL namedSQL, EntityGetter entityLoader) throws Exception {
|
Entity result = null;
|
|
getData(dataSource, namedSQL, entityLoader);
|
result = entityLoader.getEntity();
|
|
return result;
|
}
|
|
public static Object getObject(NamedDataSource dataSource, NamedSQL namedSQL, ObjectLoader objectLoader) throws Exception {
|
Object result = null;
|
|
getData(dataSource, namedSQL, objectLoader);
|
result = objectLoader.getObject();
|
|
return result;
|
}
|
|
public static String getString(NamedSQL namedSQL) throws Exception {
|
return getString(null, namedSQL);
|
}
|
|
public static String getString(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
|
String result = null;
|
|
ValueLoader valueLoader = new ValueLoader();
|
getData(dataSource, namedSQL, valueLoader);
|
result = valueLoader.getString();
|
|
return result;
|
}
|
|
public static int getInteger(NamedSQL namedSQL) throws Exception {
|
return getInteger(null, namedSQL);
|
}
|
|
public static int getInteger(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
|
int result = 0;
|
|
ValueLoader valueLoader = new ValueLoader();
|
getData(dataSource, namedSQL, valueLoader);
|
result = valueLoader.getInt();
|
|
return result;
|
}
|
|
public static BigDecimal getBigDecimal(NamedSQL namedSQL) throws Exception {
|
return getBigDecimal(null, namedSQL);
|
}
|
|
public static BigDecimal getBigDecimal(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
|
BigDecimal result = null;
|
|
ValueLoader valueLoader = new ValueLoader();
|
getData(dataSource, namedSQL, valueLoader);
|
result = valueLoader.getBigDecimal();
|
|
return result;
|
}
|
|
public static Date getDate(NamedSQL namedSQL) throws Exception {
|
return getDate(null, namedSQL);
|
}
|
|
public static Date getDate(NamedDataSource dataSource, NamedSQL namedSQL) throws Exception {
|
Date result = null;
|
|
ValueLoader valueLoader = new ValueLoader();
|
getData(dataSource, namedSQL, valueLoader);
|
result = valueLoader.getDate();
|
|
return result;
|
}
|
|
public static void getTableMetaData(NamedDataSource dataSource, IMetaDataLoader loader, NamedSQL sql) throws Exception {
|
getFieldsBySQL(dataSource, loader, sql);
|
}
|
|
public static List<String> getTableList() throws SQLException {
|
return getTableList(null);
|
}
|
|
public static List<String> getTableList(NamedDataSource dataSource) throws SQLException {
|
List<String> result = new ArrayList<String>();
|
boolean closeFlag = true; ConnectionAgent conn = null; ResultSet rslt = null;
|
|
try {
|
//1. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
DatabaseMetaData metaData = conn.getMetaData();
|
rslt = metaData.getTables(null, null, null, new String[]{"TABLE"});
|
|
while(rslt.next()){
|
String tableName = rslt.getString("TABLE_NAME");
|
result.add(tableName);
|
}
|
}
|
catch (Exception e) {
|
}
|
finally {
|
try {
|
if (rslt != null) {
|
rslt.close();
|
}
|
}
|
catch (SQLException e) {
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
|
return result;
|
}
|
|
public static void getFieldsBySQL(NamedDataSource dataSource, IMetaDataLoader loader, NamedSQL namedSQL) throws Exception {
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
Statement stmt = null; ResultSet rslt = null;
|
|
//1. 根据数据库类型生成特定的SQL
|
String sql = namedSQL.toString();
|
logger.debug(sql);
|
|
try {
|
//2. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
//3. 执行SQL
|
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
|
|
rslt = stmt.executeQuery(sql);
|
ResultSetMetaData result = rslt.getMetaData();
|
loader.load(result);
|
}
|
catch (Exception e) {
|
onError(sql, e);
|
throw e;
|
}
|
finally {
|
try {
|
if (rslt != null) {
|
rslt.close();
|
}
|
}
|
catch (SQLException e) {
|
}
|
finally {
|
try {
|
if (stmt != null) {
|
stmt.close();
|
}
|
}
|
catch (SQLException e) {
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
}
|
}
|
|
public static void getFieldsByMeta(NamedDataSource dataSource, IMetaDataLoader loader, String tableName) throws Exception {
|
boolean closeFlag = true; ConnectionAgent conn = null; ResultSet rslt = null;
|
|
try {
|
//1. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
DatabaseMetaData databaseMetaData = conn.getMetaData();
|
rslt = databaseMetaData.getColumns(null, "%", tableName, null);
|
|
loader.load(rslt);
|
}
|
catch (Exception e) {
|
throw e;
|
}
|
finally {
|
try {
|
if (rslt != null) {
|
rslt.close();
|
}
|
}
|
catch (SQLException e) {
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
}
|
|
public static boolean isTableExists(String tableName) throws Exception {
|
if (tableName == null) {
|
return false;
|
}
|
|
return isTableExists(null, tableName);
|
}
|
|
public static boolean isTableExists(NamedDataSource dataSource, String tableName) throws Exception {
|
String sql = null; Statement stmt = null; ResultSet rslt = null;
|
boolean closeFlag = true; ConnectionAgent conn = null;
|
|
try {
|
//1. 获取 connection
|
if (dataSource != null) {
|
conn = activeConn.get();
|
|
if ((conn != null) && dataSource.keyEquals(conn.getSourceKey())) {
|
closeFlag = false;
|
}
|
else {
|
conn = dataSource.getConnectionAgent();
|
}
|
}
|
else {
|
conn = activeConn.get();
|
closeFlag = conn == null;
|
|
if (conn == null) {
|
NamedDataSource defaultSource = DataSourceManager.getMain();
|
conn = defaultSource.getConnectionAgent();
|
}
|
}
|
|
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
|
|
DBaseType type = conn.getDBaseType();
|
tableName = tableName.toLowerCase();
|
|
if (type.isSQLServer()) {
|
sql = "select * from sys.all_objects where object_id = OBJECT_ID(N'" + tableName + "') and type = 'U'";
|
}
|
else if (type.isMySQL()) {
|
sql = "select * from information_schema.tables where table_name = '" + tableName + "' and table_type = 'BASE TABLE'";
|
}
|
else if (type.isOracle()){
|
sql = "select 1 from user_tables where table_name ='" + tableName.toUpperCase() + "'";
|
}
|
else {
|
throw new Exception("not support data base");
|
}
|
|
rslt = stmt.executeQuery(sql);
|
|
if (rslt.next()) {
|
return true;
|
}
|
|
return false;
|
}
|
catch (Exception e) {
|
onError(sql, e);
|
throw e;
|
}
|
finally {
|
try {
|
if (rslt != null) {
|
rslt.close();
|
}
|
}
|
catch (SQLException e) {
|
}
|
finally {
|
try {
|
if (stmt != null) {
|
stmt.close();
|
}
|
}
|
catch (SQLException e) {
|
}
|
finally {
|
if (closeFlag && conn != null) {
|
conn.close();
|
}
|
}
|
}
|
}
|
}
|
|
public static void formatOracleTables() throws SQLException {
|
List<String> tableList = new ArrayList<String>();
|
ResultSet rslt = null; String tableName = null;
|
|
ConnectionAgent conn = DataSourceManager.getConnection();
|
|
try {
|
//1. 获取表列表
|
DatabaseMetaData metaData = conn.getMetaData();
|
rslt = metaData.getTables(null, "C##GRAND", null, new String[]{"TABLE"});
|
|
while(rslt.next()){
|
tableName = rslt.getString("TABLE_NAME");
|
tableList.add(tableName);
|
}
|
|
//2. 依次修改每个表
|
for (String table: tableList) {
|
formatOracleOneTable(conn, table);
|
}
|
}
|
catch (Exception e) {
|
}
|
finally {
|
conn.close();
|
}
|
}
|
|
private static void formatOracleOneTable(ConnectionAgent conn, String tableName) throws SQLException {
|
if (tableName == null) {
|
return;
|
}
|
|
String fieldName = null;
|
|
try {
|
Statement stmt = null; ResultSet rslt = null;
|
String upperTable = tableName.toUpperCase();
|
|
//1. 修改表名
|
if (!tableName.equals(upperTable)) {
|
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
|
stmt.executeUpdate("ALTER TABLE \"" + tableName + "\" RENAME TO " + tableName);
|
}
|
|
//2. 修改字段
|
if (stmt == null) {
|
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
|
}
|
|
rslt = stmt.executeQuery("select * from " + tableName);
|
ResultSetMetaData resultMeta = rslt.getMetaData();
|
|
int max = resultMeta.getColumnCount();
|
String upperField;
|
|
Statement updateStmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
|
|
for (int i = 1; i <= max; i++) {
|
fieldName = resultMeta.getColumnLabel(i);
|
upperField = fieldName.toUpperCase();
|
|
if (fieldName.equals(upperField)) {
|
continue;
|
}
|
|
updateStmt.executeUpdate("ALTER TABLE " + tableName + " RENAME COLUMN \"" + fieldName + "\" TO " + fieldName);
|
}
|
}
|
catch (Exception e) {
|
logger.error("format table:{}, field:{} error", tableName, fieldName);
|
e.printStackTrace();
|
}
|
}
|
|
protected static void onError(String sql, Exception e) {
|
logger.error("executor exec error: " + e.getMessage());
|
}
|
}
|
