Java tutorial
/* * JEF - Copyright 2009-2010 Jiyi (mr.jiyi@gmail.com) * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package jef.database; import java.io.BufferedReader; import java.io.IOException; import java.net.URL; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Collections; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Optional; import java.util.Properties; import java.util.Set; import java.util.concurrent.ConcurrentHashMap; import java.util.regex.Pattern; import java.util.stream.Collectors; import javax.persistence.PersistenceException; import javax.sql.DataSource; import org.easyframe.enterprise.spring.TransactionMode; import jef.common.Entry; import jef.common.PairIS; import jef.common.SimpleMap; import jef.common.log.LogUtil; import jef.database.Condition.Operator; import jef.database.annotation.PartitionFunction; import jef.database.annotation.PartitionKey; import jef.database.annotation.PartitionTable; import jef.database.datasource.SimpleDataSource; import jef.database.dialect.ColumnType; import jef.database.dialect.DatabaseDialect; import jef.database.dialect.type.ColumnMapping; import jef.database.innerpool.IConnection; import jef.database.innerpool.IUserManagedPool; import jef.database.jdbc.result.ResultSetImpl; import jef.database.jdbc.result.ResultSets; import jef.database.meta.AbstractMetadata; import jef.database.meta.ColumnChange; import jef.database.meta.ColumnModification; import jef.database.meta.DbProperty; import jef.database.meta.DdlGenerator; import jef.database.meta.DdlGeneratorImpl; import jef.database.meta.Feature; import jef.database.meta.ITableMetadata; import jef.database.meta.MetaHolder; import jef.database.meta.MetadataFeature; import jef.database.meta.TableCreateSQLs; import jef.database.meta.def.UniqueConstraintDef; import jef.database.meta.object.Column; import jef.database.meta.object.Constraint; import jef.database.meta.object.ConstraintType; import jef.database.meta.object.DataType; import jef.database.meta.object.ForeignKeyItem; import jef.database.meta.object.Function; import jef.database.meta.object.Index; import jef.database.meta.object.PrimaryKey; import jef.database.meta.object.SequenceInfo; import jef.database.meta.object.TableInfo; import jef.database.query.DefaultPartitionCalculator; import jef.database.query.Func; import jef.database.support.MetadataEventListener; import jef.database.support.RDBMS; import jef.database.support.SqlLog; import jef.database.wrapper.executor.ExecutorImpl; import jef.database.wrapper.executor.ExecutorJTAImpl; import jef.database.wrapper.executor.StatementExecutor; import jef.database.wrapper.populator.ResultPopulatorImpl; import jef.database.wrapper.populator.ResultSetExtractor; import jef.database.wrapper.populator.Transformer; import jef.database.wrapper.variable.BindVariableContext; import jef.http.client.support.CommentEntry; import jef.tools.ArrayUtils; import jef.tools.Assert; import jef.tools.IOUtils; import jef.tools.JefConfiguration; import jef.tools.StringUtils; /* * constraint * ====Oracle?===== ? ????? * * ??? ???? . * * ??disableenable?? ALTER TABLE products disable CONSTRAINT fk_supplier; * * ???? * * ???? * ??????? * * alter table person add constraint FK_PERSON foreign key (schoolId) references * School(id); alter table person add constraint FK_PERSON foreign key * (parentid) references person(id); * * ==== ==== 1\Derby???? 2\Oracle Eg. COMMENT ON TABLE * EMPLOYEE IS 'Reflects first quarter 2000 reorganization' COMMENT ON COLUMN * mytable.primarykey IS 'Unique ID from Sequence SEQ_MASTER' * * 3\MySql CREATE TABLE FOO ( A COMMENT 'This col is A') COMMENT='And here * is the table comment' * * * 5\? alter table person add constraint FK_PERSON_PARENT foreign key * (parentid) references person(id); * * 6? * */ /** * ??? * <p> * ?JDBCDatabaseMetadata?????? * * @author Jiyi * */ public class DbMetaData { public final Set<String> checkedFunctions = new HashSet<String>(); private String dbkey; private ConnectInfo info; /** * shema */ private String schema; /** * ?<br> * ?? */ private int subtableInterval; /** * */ private volatile long subtableCacheExpireTime; /** * ??? */ private final Map<String, Set<String>> subtableCache = new ConcurrentHashMap<String, Set<String>>(); /** * ??? */ private MetadataFeature feature; /** * DDL */ private DdlGenerator ddlGenerator; /** * ?? */ private DataSource ds; /** * ??? */ private long dbTimeDelta; private IUserManagedPool parent; protected Connection getConnection(boolean b) throws SQLException { Connection conn; if (b && (ds instanceof SimpleDataSource) && hasRemarkFeature()) { Properties props = new Properties(); props.put("remarksReporting", "true"); conn = ((SimpleDataSource) ds).getConnectionFromDriver(props); } else { IConnection ic = parent.poll(); ic.setKey(dbkey); conn = ic; } return conn; } /** * * * @param ds * ? * @param parent * ?? * @param dbkey * ?????? */ public DbMetaData(DataSource ds, IUserManagedPool parent, String dbkey) { this.ds = ds; this.parent = parent; this.dbkey = dbkey; this.subtableInterval = JefConfiguration.getInt(DbCfg.DB_PARTITION_REFRESH, 3600) * 1000; this.subtableCacheExpireTime = System.currentTimeMillis() + subtableInterval; this.parent = parent; LogUtil.debug("init database metadata of " + ds); info = DbUtils.tryAnalyzeInfo(ds, false); Connection con = null; try { con = getConnection(false); } catch (SQLException e) { throw DbUtils.toRuntimeException(e); } try { if (info == null) { info = DbUtils.tryAnalyzeInfo(con); } DatabaseDialect profile = info.profile; Assert.notNull(profile); // SQL? this.ddlGenerator = new DdlGeneratorImpl(profile); // ??? this.feature = new MetadataFeature(con.getMetaData()); // ?.??Metadata? calcSchema(feature, profile); // calcTimeDelta(con, profile); if (Math.abs(dbTimeDelta) > 30000) { // ???30 LogUtil.warn( "The time of thie machine is [{}], and database is [{}]. Please adjust date time via any NTP server.", new Date(), getCurrentTime()); } else { LogUtil.debug("The time between database and this machine is {}ms.", this.dbTimeDelta); } } catch (SQLException e) { throw new PersistenceException(e); } finally { LogUtil.debug("finish init database metadata of " + ds); releaseConnection(con); } } private void calcSchema(MetadataFeature feature, DatabaseDialect profile) { String schema = null; if (profile.has(Feature.USER_AS_SCHEMA)) { schema = StringUtils.trimToNull(info.getUser()); } else if (profile.has(Feature.DBNAME_AS_SCHEMA)) { schema = StringUtils.trimToNull(info.getDbname()); } if (schema != null) { this.schema = feature.getDefaultCase().getObjectNameToUse(schema); } else { this.schema = profile.getDefaultSchema(); } } private void calcTimeDelta(Connection conn, DatabaseDialect dialect) { String template = dialect.getProperty(DbProperty.SELECT_EXPRESSION); String exps = dialect.getFunction(Func.current_timestamp); String sql; if (template == null) { sql = "SELECT " + exps; } else { sql = String.format(template, exps); } try { Date date = select0(conn, sql, ResultSetExtractor.GET_FIRST_TIMESTAMP, null, SqlLog.DUMMY); dbTimeDelta = date.getTime() - System.currentTimeMillis(); } catch (SQLException e) { throw DbUtils.toRuntimeException(e); } } /** * ? * * @param names * ???? * @return ? */ public List<TableInfo> getTable(String name) throws SQLException { return getDatabaseObject(ObjectType.TABLE, this.schema, getProfile().getObjectNameToUse(name), null, false); } /** * ?(?schema) * * @return ? * @throws SQLException */ public List<TableInfo> getTables(boolean needRemark) throws SQLException { return getDatabaseObject(ObjectType.TABLE, this.schema, null, null, needRemark); } /** * ?(?schema) * * @return ?. A collection of TableInfo * @throws SQLException * @see TableInfo */ public List<TableInfo> getTables() throws SQLException { return getDatabaseObject(ObjectType.TABLE, this.schema, null, null, false); } /** * ?????????? * ???? * * @return ?? */ public Date getCurrentTime() { return new Date(System.currentTimeMillis() + this.dbTimeDelta); } /** * ? * * @param name * ?? * @return ? */ public List<TableInfo> getView(String name) throws SQLException { return getDatabaseObject(ObjectType.VIEW, this.schema, getProfile().getObjectNameToUse(name), null, false); } /** * ??schema * * @param needRemark * while on Oracle driver, you have to set 'true' to fetch the * comment of view. * @return ? A collection of view info. * @throws SQLException */ public List<TableInfo> getViews(boolean needRemark) throws SQLException { return getDatabaseObject(ObjectType.VIEW, this.schema, null, null, needRemark); } /** * ??schema * * @return ? A collection of view info. * @throws SQLException */ public List<TableInfo> getViews() throws SQLException { return getDatabaseObject(ObjectType.VIEW, this.schema, null, null, false); } /** * ?? * * @return Sequence? * <ul> * <li>{@link CommentEntry#getKey()} sequence??</li> * <li>{@link CommentEntry#getValue()} sequence</li> * </ul> */ public List<SequenceInfo> getSequence(String schema, String name) throws SQLException { schema = schema == null ? this.schema : schema; return info.profile.getSequenceInfo(this, schema, name); } /** * @param type * ? "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", * "LOCAL TEMPORARY", "ALIAS", "SYNONYM". * @param schema * Schema * @param matchName * ??? * @param oper * ??nullnull? * @return /?? * @throws SQLException * @see Operator */ public List<TableInfo> getDatabaseObject(ObjectType type, String schema, String matchName, Operator oper, boolean needRemark) throws SQLException { if (schema == null) schema = this.schema; if (matchName != null) { int n = matchName.indexOf('.'); if (n > -1) { schema = matchName.substring(0, n); matchName = matchName.substring(n + 1); } } if (oper != null && oper != Operator.EQUALS) { if (StringUtils.isEmpty(matchName)) { matchName = "%"; } else if (oper == Operator.MATCH_ANY) { matchName = "%" + matchName + "%"; } else if (oper == Operator.MATCH_END) { matchName = "%" + matchName; } else if (oper == Operator.MATCH_START) { matchName = matchName + "%"; } } Connection conn = getConnection(needRemark); DatabaseMetaData databaseMetaData = conn.getMetaData(); DatabaseDialect trans = info.profile; ResultSet rs = null; try { rs = databaseMetaData.getTables(trans.getCatlog(schema), trans.getSchema(schema), matchName, type == null ? null : new String[] { type.name() }); List<TableInfo> result = new ArrayList<TableInfo>(); while (rs.next()) { TableInfo info = new TableInfo(); info.setCatalog(rs.getString("TABLE_CAT")); info.setSchema(rs.getString("TABLE_SCHEM")); info.setName(rs.getString("TABLE_NAME")); info.setType(rs.getString("TABLE_TYPE"));// "TABLE","VIEW", // "SYSTEM TABLE", // "GLOBAL // TEMPORARY","LOCAL // TEMPORARY", // "ALIAS", // "SYNONYM". info.setRemarks(rs.getString("REMARKS")); result.add(info); } return result; } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * ?schema??? * * @param types * ??{@link ObjectType}????Table * @return ?? * @throws SQLException */ public List<String> getTableNames(ObjectType... types) throws SQLException { if (types == null || types.length == 0) { types = new ObjectType[] { ObjectType.TABLE }; } Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); DatabaseDialect trans = info.profile; String[] ts = new String[types.length]; for (int i = 0; i < types.length; i++) { ts[i] = types[i].name(); } ResultSet rs = databaseMetaData.getTables(trans.getCatlog(schema), trans.getSchema(schema), null, ts); try { List<String> result = new ArrayList<String>(); while (rs.next()) { result.add(rs.getString("TABLE_NAME")); } return result; } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * ? * * @param tableName * ?? * @return ???null * @throws SQLException */ public String getExistTable(String tableName) throws SQLException { return getExists(ObjectType.TABLE, tableName); } /** * ??catalog * * @return catalog * @throws SQLException */ public String[] getCatalogs() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = databaseMetaData.getCatalogs(); try { List<String> list = ResultSets.toStringList(rs, "TABLE_CAT", 9999, this.getProfile()); return list.toArray(new String[list.size()]); } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * ?Schema * * @return ?Schema */ public String getCurrentSchema() { return schema; } /** * Schema * * @return Schema * @throws SQLException */ public String[] getSchemas() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = databaseMetaData.getSchemas(); try { List<String> list = ResultSets.toStringList(rs, "TABLE_SCHEM", 9999, this.getProfile()); return list.toArray(new String[list.size()]); } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * ? * * @param type * ?{@linkplain ObjectType } * @param objectName * ?? * @return truetrue * @throws SQLException * @see ObjectType */ public boolean exists(ObjectType type, String objectName) throws SQLException { return getExists(type, objectName) != null; } /** * ? * * @param type * ?{@linkplain ObjectType } * @param objectName * ?? * @return true???null * @throws SQLException * @see ObjectType */ public String getExists(ObjectType type, String objectName) throws SQLException { objectName = info.profile.getObjectNameToUse(objectName); String schema = null; int n = objectName.indexOf('.'); if (n > -1) { schema = objectName.substring(0, n); objectName = objectName.substring(n + 1); } if (this.getProfile().isCaseSensitive()) { String upper = objectName.toUpperCase(); String lower = objectName.toLowerCase(); if (innerExists(type, schema, objectName)) { return objectName; } if (!upper.equals(objectName) && innerExists(type, schema, upper)) { return upper; } if (!lower.equals(objectName) && innerExists(type, schema, lower)) { return lower; } return null; } else { return innerExists(type, schema, objectName) ? objectName : null; } } /** * ?schema * * @param type * ?{@linkplain ObjectType } * @param schema * schema * @param objectName * ?? * @return true?false * @throws SQLException * @see ObjectType */ public boolean existsInSchema(ObjectType type, String schema, String objectName) throws SQLException { if (schema == null) { int n = objectName.indexOf('.'); if (n > -1) { schema = objectName.substring(0, n); objectName = objectName.substring(n + 1); } } objectName = info.profile.getObjectNameToUse(objectName); schema = info.profile.getObjectNameToUse(schema); return innerExists(type, schema, objectName); } /** * * * @param tableName * ?? * @return A collection of columns. * @throws SQLException * @see Column */ public List<Column> getColumns(String tableName) throws SQLException { return getColumns(tableName, false); } /** * ?null * * @param tableName * ?? * @param column * ?? * @return null * @throws SQLException */ public Column getColumn(String tableName, String column) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); column = info.profile.getObjectNameToUse(column); Connection conn = getConnection(false); Collection<Index> indexes = getIndexes(tableName); DatabaseMetaData databaseMetaData = conn.getMetaData(); String schema = this.schema; int n = tableName.indexOf('.'); if (n > 0) {// ???schema schema = tableName.substring(0, n); tableName = tableName.substring(n + 1); } ResultSet rs = null; try { rs = databaseMetaData.getColumns(null, schema, tableName, column); Column result = null; if (rs.next()) { result = new Column(); populateColumn(result, rs, tableName, indexes); } return result; } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * * * @param tableName * ?? * @return A collection of columns. * @throws SQLException * @see Column */ public List<Column> getColumns(String tableName, boolean needRemark) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); Connection conn = getConnection(needRemark); DatabaseMetaData databaseMetaData = conn.getMetaData(); String schema = this.schema; int n = tableName.indexOf('.'); if (n > 0) {// ???schema schema = tableName.substring(0, n); tableName = tableName.substring(n + 1); } ResultSet rs = null; List<Column> list = new ArrayList<Column>(); Collection<Index> indexes = null; try { rs = databaseMetaData.getColumns(null, schema, tableName, "%"); while (rs.next()) { if (indexes == null) { // ?Oracle?getIndexInfo()??????? // ???? indexes = getIndexes(tableName); } Column column = new Column(); populateColumn(column, rs, tableName, indexes); list.add(column); } } finally { DbUtils.close(rs); releaseConnection(conn); } Collections.sort(list, (a, b) -> Integer.compare(a.getOrdinal(), b.getOrdinal())); return list; } private void populateColumn(Column column, ResultSet rs, String tableName, Collection<Index> indexes) throws SQLException { /* * Notice: Oracle???rs.getString("COLUMN_DEF")? * "Stream is already closed" Exception ??google???? * https://issues.apache.org/jira/browse/DDLUTILS-29 * getString("COLUMN_DEF")? ??? */ String defaultVal = rs.getString("COLUMN_DEF"); column.setColumnName(rs.getString("COLUMN_NAME")); column.setOrdinal(rs.getInt("ORDINAL_POSITION")); column.setColumnSize(rs.getInt("COLUMN_SIZE")); column.setDecimalDigit(rs.getInt("DECIMAL_DIGITS")); column.setDataType(rs.getString("TYPE_NAME")); column.setDataTypeCode(rs.getInt("DATA_TYPE")); column.setNullable(rs.getString("IS_NULLABLE").equalsIgnoreCase("YES")); /* * defaultVal?null?""? */ if (this.getProfile().has(Feature.EMPTY_CHAR_IS_NULL)) { // Oracle????trimToNull?default ' ' defaultVal = StringUtils.rtrim(defaultVal, '\r', '\n'); if (defaultVal.length() == 0) { defaultVal = null; } } column.setColumnDef(defaultVal); column.setRemarks(rs.getString("REMARKS"));// ???? column.setTableName(tableName); if (indexes != null) { // ??unique for (Index index : indexes) { if (index.isUnique() && index.isOnSingleColumn(column.getColumnName())) { column.setUnique(true); break; } } } } /** * * * @param type * * @return A Collection of index information. * @throws SQLException * @see Index */ public Collection<Index> getIndexes(Class<?> type) throws SQLException { ITableMetadata meta = MetaHolder.getMeta(type); String tableName = meta.getTableName(true); Optional<PrimaryKey> pk = getPrimaryKey(tableName); Collection<Index> indexes = getIndexes(tableName); for (Iterator<Index> iter = indexes.iterator(); iter.hasNext();) { Index index = iter.next(); if (!index.isUnique()) { continue; } if (isPrimaryKey(pk, index)) { iter.remove(); continue; } if (index.getColumns().size() == 1) { if (isUniqueFromColumnAnnotation(index, meta.getColumns())) { iter.remove(); continue; } } if (isUniqueFromClassAnnotation(index, meta)) { iter.remove(); continue; } } return indexes; } /** * unique?? unique index constraint * Entity????? ? * * @param meta * / Metadata * @return Collection of Index * @throws SQLException */ public Collection<Index> getUniqueConstraint(ITableMetadata meta) throws SQLException { Collection<Index> indexes = getUniqueConstraint(meta.getTableName(true)); List<Index> result = new ArrayList<Index>(); for (Index index : indexes) { // UniqueUnique? if (index.getColumns().size() == 1 && isUniqueFromColumnAnnotation(index, meta.getColumns())) { result.add(index); continue; } // ?Unique?Unique? if (isUniqueFromClassAnnotation(index, meta)) { result.add(index); } } return result; } /** * ??? * * @param index * @param meta * @return */ private boolean isUniqueFromClassAnnotation(Index index, ITableMetadata meta) { DatabaseDialect dialect = this.getProfile(); for (UniqueConstraintDef unique : meta.getUniqueDefinitions()) { List<String> columns = unique.getColumnNames(meta, dialect); if (ArrayUtils.equals(columns.toArray(new String[columns.size()]), index.getColumnNames())) { return true; } } return false; } private boolean isUniqueFromColumnAnnotation(Index index, Collection<ColumnMapping> columns) { for (ColumnMapping c : columns) { String columnName = c.getColumnName(getProfile(), false); if (index.getColumnNames()[0].equals(columnName)) { return c.get().isUnique(); } } return false; } /** * unique?? unique index constraint * ?MySQL ?? * * @param tableName * ?? * @return ? * @throws SQLException */ public Collection<Index> getUniqueConstraint(String tableName) throws SQLException { Collection<Index> indexes = getIndexes(tableName); Optional<PrimaryKey> pk = this.getPrimaryKey(tableName); for (Iterator<Index> iter = indexes.iterator(); iter.hasNext();) { Index index = iter.next(); if (!index.isUnique()) { iter.remove(); continue; } // PK if (isPrimaryKey(pk, index)) { iter.remove(); continue; } } return indexes; } private boolean isPrimaryKey(Optional<PrimaryKey> pk, Index index) { return pk.isPresent() && ArrayUtils.equals(pk.get().getColumns(), index.getColumnNames()); } /** * * * @param meta * * @return A Collection of index information. * @throws SQLException * @see Index */ public Collection<Index> getIndexes(ITableMetadata meta) throws SQLException { return getIndexes(meta.getTableName(true)); } /** * * * @param tableName * ?? * @return ? * @see Index */ public Collection<Index> getIndexes(String tableName) throws SQLException { // JDBC?? if (info.profile.has(Feature.NOT_SUPPORT_INDEX_META)) { LogUtil.warn("Current JDBC version doesn't suppoer fetch index info."); return Collections.emptyList(); } tableName = info.profile.getObjectNameToUse(tableName); String schema = this.schema; int n = tableName.indexOf('.'); if (n > -1) { schema = tableName.substring(0, n); tableName = tableName.substring(n + 1); } Connection conn = getConnection(false); ResultSet rs = null; try { DatabaseMetaData databaseMetaData = conn.getMetaData(); rs = databaseMetaData.getIndexInfo(null, schema, tableName, false, false); Map<String, Index> map = new HashMap<String, Index>(); while (rs.next()) { String indexName = rs.getString("INDEX_NAME"); String cName = rs.getString("COLUMN_NAME"); if (indexName == null || cName == null) continue; Index index = map.get(indexName); if (index == null) { index = new Index(); index.setIndexName(indexName); index.setTableName(rs.getString("TABLE_NAME")); index.setTableSchema(rs.getString("TABLE_SCHEM")); index.setIndexQualifier(rs.getString("INDEX_QUALIFIER")); index.setUnique(!rs.getBoolean("NON_UNIQUE")); index.setType(rs.getInt("TYPE")); map.put(indexName, index); } String asc = rs.getString("ASC_OR_DESC"); int order = rs.getInt("ORDINAL_POSITION"); index.addColumn(cName, asc == null ? true : asc.startsWith("A"), order); } return map.values(); } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * ?JDBC? * * @return (??JDBC) * @throws SQLException */ public String getDriverVersion() throws SQLException { Connection conn = getConnection(false); try { DatabaseMetaData databaseMetaData = conn.getMetaData(); return databaseMetaData.getDriverVersion(); } finally { releaseConnection(conn); } } /** * ??? * * @return ? * @throws SQLException */ public String getDatabaseVersion() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); String version = databaseMetaData.getDatabaseProductVersion(); releaseConnection(conn); return version; } /** * databasemetadata * * @param callback * @return * @throws SQLException */ public <T> T callDatabaseMetadata(DatabaseMetaDataCall<T> callback) throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); try { return callback.apply(databaseMetaData); } finally { releaseConnection(conn); } } @FunctionalInterface public static interface DatabaseMetaDataCall<T> { T apply(DatabaseMetaData databaseMeta) throws SQLException; } /** * @return the JDBC 'DatabaseMetaData' object * @throws SQLException * @deprecated not recommended */ public DatabaseMetaData get() throws SQLException { Connection conn = getConnection(false); return conn.getMetaData(); } /** * ?? * * @return Map<String,String> [key] is * <ul> * <li>DatabaseProductName</li> * <li>DatabaseProductVersion</li> * <li>DriverName</li> * <li>DriverVersion</li> * </ul> */ public Map<String, String> getDbVersion() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); Map<String, String> map = new SimpleMap<String, String>(); map.put("DriverName", databaseMetaData.getDriverName()); map.put("DriverVersion", databaseMetaData.getDriverVersion() + " " + databaseMetaData.getDatabaseMinorVersion()); map.put("DatabaseProductName", databaseMetaData.getDatabaseProductName()); map.put("DatabaseProductVersion", databaseMetaData.getDatabaseProductVersion() + " " + databaseMetaData.getDatabaseMinorVersion()); String otherVersionSQL = info.profile.getProperty(DbProperty.OTHER_VERSION_SQL); if (otherVersionSQL != null) { for (String sql : StringUtils.split(otherVersionSQL, ";")) { if (StringUtils.isBlank(sql)) continue; Statement st = conn.createStatement(); ResultSet rs = null; try { rs = st.executeQuery(sql); while (rs.next()) { map.put(rs.getString(1), rs.getString(2)); } } finally { DbUtils.close(rs); DbUtils.close(st); } } } releaseConnection(conn); return map; } /** * ???? * * @return Username */ public String getUserName() throws SQLException { return info.user; } /** * ??? * * @return List<DataType> ? */ public List<DataType> getSupportDataType() throws SQLException { Connection conn = getConnection(false); ResultSet rs = null; try { DatabaseMetaData databaseMetaData = conn.getMetaData(); rs = databaseMetaData.getTypeInfo(); return ResultPopulatorImpl.instance.toPlainJavaObject(new ResultSetImpl(rs, getProfile()), DATATYPE_TRANSFORMER); } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * * * @param tableName * ?? * @return Map<String,String> key=?? value=?? */ public Optional<PrimaryKey> getPrimaryKey(String tableName) throws SQLException { tableName = MetaHolder.toSchemaAdjustedName(tableName); tableName = info.profile.getObjectNameToUse(tableName); Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = null; PrimaryKey pk = null; List<PairIS> pkColumns = new ArrayList<PairIS>(); try { rs = databaseMetaData.getPrimaryKeys(null, schema, tableName); while (rs.next()) { String pkName = rs.getString("PK_NAME"); String col = rs.getString("COLUMN_NAME"); int seq = rs.getShort("KEY_SEQ"); if (pk == null) { pk = new PrimaryKey(pkName); } pkColumns.add(new PairIS(seq, col)); } if (pk == null) return Optional.empty(); } finally { DbUtils.close(rs); releaseConnection(conn); } pkColumns.sort((a, b) -> Integer.compare(a.first, b.first)); String[] columns = new String[pkColumns.size()]; for (int i = 0; i < pkColumns.size(); i++) { columns[i] = pkColumns.get(i).second; } pk.setColumns(columns); return Optional.of(pk); } /** * * * @param tableName * * @return * @throws SQLException */ public List<ForeignKeyItem> getForeignKey(String tableName) throws SQLException { return getForeignKey(schema, tableName); } /** * * * @param schema * @param tableName * @return * @throws SQLException */ public List<ForeignKeyItem> getForeignKey(String schema, String tableName) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = null; try { rs = databaseMetaData.getImportedKeys(null, schema, tableName); List<ForeignKeyItem> fks = ResultPopulatorImpl.instance .toPlainJavaObject(new ResultSetImpl(rs, this.getProfile()), FK_TRANSFORMER); return fks; } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * * * @param fromField * ? * @param refField * * @throws SQLException */ public void createForeignKey(Field fromField, Field refField) throws SQLException { createForeignKey(fromField, refField, DatabaseMetaData.importedKeyNoAction, DatabaseMetaData.importedKeyNoAction); } /** * * * <p> * ?????importedKeyRestrict * <ul> * <li>{@link DatabaseMetaData#importedKeyNoAction} - ??</li> * <li>{@link DatabaseMetaData#importedKeyCascade} - </li> * <li>{@link DatabaseMetaData#importedKeySetNull} - null</li> * <li>{@link DatabaseMetaData#importedKeyRestrict} - * ?importedKeyNoAction</li> * <li>{@link DatabaseMetaData#importedKeySetDefault} - ?</li> * </ul> * * @param fromField * ? * @param refField * * @param deleteRule * ??????? * @param updateRule * ??????? * @throws SQLException */ public void createForeignKey(Field fromField, Field refField, int deleteRule, int updateRule) throws SQLException { AbstractMetadata from = DbUtils.getTableMeta(fromField); AbstractMetadata ref = DbUtils.getTableMeta(refField); String fromTable = from.getTableName(false); String fromColumn = from.getColumnName(fromField, getProfile(), true); String refTable = ref.getTableName(false); String refColumn = ref.getColumnName(refField, getProfile(), true); ForeignKeyItem key = new ForeignKeyItem(fromTable, fromColumn, refTable, refColumn); key.setFromSchema(from.getSchema()); key.setReferenceSchema(ref.getSchema()); key.setDeleteRule(deleteRule); key.setUpdateRule(updateRule); if (!checkFK(key)) { String sql = key.toCreateSql(getProfile()); StatementExecutor executor = this.createExecutor(); try { executor.executeSql(sql); } finally { executor.close(); } } } /* * FK,true?false?Drop?false */ private boolean checkFK(ForeignKeyItem key) throws SQLException { List<ForeignKeyItem> keys = getForeignKey(key.getFromSchema(), key.getFromTable()); for (ForeignKeyItem old : keys) { if (old.getFromColumn().equalsIgnoreCase(key.getFromColumn())) { return true; } } return false; } /** * ? * * @param tableName * * @return * @throws SQLException */ public List<ForeignKeyItem> getForeignKeyReferenceTo(String tableName) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = null; try { rs = databaseMetaData.getExportedKeys(null, schema, tableName); List<ForeignKeyItem> fks = ResultPopulatorImpl.instance .toPlainJavaObject(new ResultSetImpl(rs, getProfile()), FK_TRANSFORMER); return fks; } catch (RuntimeException e) { // JDBC? LogUtil.exception(e); return Collections.emptyList(); } finally { DbUtils.close(rs); releaseConnection(conn); } } /** * ???? * * @return Map<String,String> [key] will be.. * <ul> * <li>SQLKeywords</li> * <li>TimeDateFunctions</li> * <li>NumericFunctions</li> * <li>StringFunctions</li> * <li>SystemFunctions</li> * </ul> */ public List<String> getAllBuildInFunctions() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); List<String> result = new ArrayList<String>(); result.addAll(Arrays.asList(StringUtils.split(databaseMetaData.getTimeDateFunctions(), ','))); result.addAll(Arrays.asList(StringUtils.split(databaseMetaData.getNumericFunctions(), ','))); result.addAll(Arrays.asList(StringUtils.split(databaseMetaData.getStringFunctions(), ','))); result.addAll(Arrays.asList(StringUtils.split(databaseMetaData.getSystemFunctions(), ','))); releaseConnection(conn); return result; } /** * ?? * * @return ??? * @throws SQLException */ public String getTimeDateFunctions() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); String result = databaseMetaData.getTimeDateFunctions(); releaseConnection(conn); return result; } /** * ?? * * @return ??? * @throws SQLException */ public String getNumericFunctions() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); String result = databaseMetaData.getNumericFunctions(); releaseConnection(conn); return result; } /** * ?? * * @return ? * @throws SQLException */ public String getStringFunctions() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); String result = databaseMetaData.getStringFunctions(); releaseConnection(conn); return result; } /** * ?? * * @return ??? * @throws SQLException */ public String getSystemFunctions() throws SQLException { Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); String result = databaseMetaData.getSystemFunctions(); releaseConnection(conn); return result; } /** * ? * * @param schema * schema * @param objectName * ?? * @return true?false * @throws SQLException */ public boolean existsProcdure(String schema, String objectName) throws SQLException { List<Function> func = this.innerGetProcedures(schema, objectName); return !func.isEmpty(); } /** * ???(??JDBC 4.0??) * * @param schema * schema * @param name * ?? * @return true,?false * @throws SQLException * JDBC 4.0 (JDK 6)?? * @since 1.7.1 * @author Jiyi */ public boolean existsFunction(String schema, String name) throws SQLException { List<Function> func = innerGetFunctions(schema, name); return !func.isEmpty(); } /** * ?? * * @param schema * ?schemanull?schema * @return */ public List<Function> getProcedures(String schema) throws SQLException { return innerGetProcedures(schema, null); } /** * ? * * @param schema * ?schemanull?schema * @return * @throws SQLException */ public List<Function> getFunctions(String schema) throws SQLException { return innerGetFunctions(schema, null); } /** * JDBC??? * * @return * @throws SQLException */ public String[] getSQLKeywords() throws SQLException { Connection conn = getConnection(false); try { DatabaseMetaData databaseMetaData = conn.getMetaData(); return org.apache.commons.lang.StringUtils.split(databaseMetaData.getSQLKeywords(), ','); } finally { releaseConnection(conn); } } /** * ?(??Sequence??) * * @param schema * schema?? * @param tableName * ?schema?? * @param sequenceColumnName * ??? * @param stReuse * ??{@code Statement} * @return Sequence??sequence?? * @throws SQLException */ public long getSequenceStartValue(String schema, String tableName, String sequenceColumnName) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); if (!existTable(tableName)) { return 1; } String getMaxValueSql = createGetMaxSequenceColumnValueStatement(schema, DbUtils.escapeColumn(info.profile, tableName), sequenceColumnName); Connection conn = getConnection(false); Statement st = null; ResultSet rs = null; try { if (st == null) { st = conn.createStatement(); } if (ORMConfig.getInstance().isDebugMode()) { LogUtil.show(getMaxValueSql + "|" + info.getDbname()); } rs = st.executeQuery(getMaxValueSql); long maxValue = 0; if (rs.next()) { maxValue = rs.getLong(1); } return maxValue; } catch (SQLException e) { DebugUtil.setSqlState(e, getMaxValueSql); throw e; } finally { DbUtils.close(rs); DbUtils.close(st); releaseConnection(conn); } } public boolean existTable(String tableName) throws SQLException { return getExistTable(tableName) != null; } /** * Return whether the JDBC 3.0 Savepoint feature was supported. Caches the * flag for the lifetime of this Metadata. * * @return true if current Database and the JDBC Driver supports savepoints. * @throws SQLException * if thrown by the JDBC driver */ public boolean supportsSavepoints() { return feature.supportsSavepoints(); } /** * ?? * * @param level * @return * @throws SQLException */ public boolean supportsTransactionIsolationLevel(int level) throws SQLException { Connection conn = getConnection(false); return conn.getMetaData().supportsTransactionIsolationLevel(level); } /** * ???JDBC * * @return 1234?-1 */ public int getJdbcVersion() { return feature.getJdbcVersion(); } /** * ?? * * @return * @throws SQLException */ public String[] getTableTypes() throws SQLException { return feature.getTableTypes(); } /** * ?? * * @return ? * @see ConnectInfo */ public ConnectInfo getInfo() { return info; } /** * ???? * * @return ??? */ public String getDbName() { return info.dbname; } /** * ?? * * @return * @see DatabaseDialect */ public DatabaseDialect getProfile() { return info.profile; } /** * ?,?? * <h3></h3> ?????? * ?? * <ul> * <li>USER_LOG<br> * --??</li> * <li>USER_LOG_201204<br> * --20124</li> * <li>USER_LOG_201205<br> * --20125</li> * <li>USER_LOG_201206<br> * --20126</li> * <li>USER_LOG_201207<br> * --20127</li> * <li>.....</li> * </ul> * <p> * ????? * * <h3></h3> ???schema????? * ?? * <h3></h3> jef.properties?<br> * {@code db.partition.refresh=n}<br> * n3600?1? * <p> * * @param tableMetadata * ? * @return ??() * * @throws SQLException */ public Collection<String> getSubTableNames(ITableMetadata tableMetadata) throws SQLException { Assert.notNull(tableMetadata); boolean isDefault = DbUtils.partitionUtil instanceof DefaultPartitionCalculator; if (isDefault && tableMetadata.getPartition() == null) {// JEF? return Collections.emptySet(); } String tableName = getProfile().getObjectNameToUse(tableMetadata.getTableName(true)); Set<String> result = null; // if (System.currentTimeMillis() > subtableCacheExpireTime) { subtableCache.clear(); subtableCacheExpireTime = System.currentTimeMillis() + subtableInterval; } else { result = subtableCache.get(tableName); } // ?? if (result == null) { result = calculateSubTables(tableName, tableMetadata, isDefault); subtableCache.put(tableName, result); } return result; } /** * ??create tablealter table?? * * <h3>?</h3> ALTER TABLE?? * <p> * ALTER TABLE?DDLDDL???? * {@linkplain MetadataEventListener ?} ???? * * @param meta * * @param tablename * ?? * @param event * ?????SQL??? * @throws SQLException * * @see MetadataEventListener ?? */ public void refreshTable(ITableMetadata meta, String tablename, MetadataEventListener event, boolean modifyConstraint, boolean modifyIndex) throws SQLException { tablename = info.profile.getObjectNameToUse(tablename); // modifyColumns(tablename, meta, event); // ? if (modifyConstraint) { executeDDL(calculateConstraints(meta, tablename), tablename, meta, event); } // if (modifyIndex) { executeDDL(calculateIndexes(meta, tablename), tablename, meta, event); } if (event != null) { event.onTableFinished(meta, tablename); } } private void modifyColumns(String tablename, ITableMetadata meta, MetadataEventListener event) throws SQLException { DatabaseDialect dialect = info.profile; boolean supportsChangeDelete = dialect.notHas(Feature.NOT_SUPPORT_ALTER_DROP_COLUMN); if (!supportsChangeDelete) { LogUtil.warn("Current database [{}] doesn't support alter table column.", dialect.getName()); } List<Column> columns = this.getColumns(tablename, false); if (columns.isEmpty()) {// ? boolean created = false; if (event == null || event.onTableCreate(meta, tablename)) { created = this.createTable(meta, tablename); } if (created && event != null) { event.onTableFinished(meta, tablename); } return; } // Map<Field, ColumnMapping> defined = getColumnMap(meta); // ? if (event != null) { boolean isContinue = event.onCompareColumns(tablename, columns, defined); if (!isContinue) { return; } } // List<String> delete = new ArrayList<String>(); // List<ColumnModification> changed = new ArrayList<ColumnModification>(); // for (Column c : columns) { Field field = meta.getFieldByLowerColumn(c.getColumnName().toLowerCase()); if (field == null) { if (supportsChangeDelete) { delete.add(c.getColumnName()); } continue; } compareColumns(defined.remove(field), supportsChangeDelete, changed, c); } Map<String, ColumnType> insert = new HashMap<String, ColumnType>(); for (Map.Entry<Field, ColumnMapping> e : defined.entrySet()) { String columnName = e.getValue().getColumnName(getProfile(), true); insert.put(columnName, e.getValue().get()); } // ????? if (event != null && event.onColumnsCompared(tablename, meta, insert, changed, delete) == false) { return; } executeDDL(ddlGenerator.toTableModifyClause(meta, tablename, insert, changed, delete), tablename, meta, event); } private void compareColumns(ColumnMapping type, boolean supportsChangeDelete, List<ColumnModification> changed, Column columnDb) { Assert.notNull(type);// ?? if (supportsChangeDelete) { List<ColumnChange> changes = type.get().isEqualTo(columnDb, getProfile()); if (!changes.isEmpty()) { changed.add(new ColumnModification(columnDb, changes, type.get())); } } } private void executeDDL(List<String> alterTableSQLs, String tablename, ITableMetadata meta, MetadataEventListener event) throws SQLException { StatementExecutor exe = createExecutor(); try { exe.setQueryTimeout(180);// 3 if (event != null) { event.beforeAlterTable(tablename, meta, exe, alterTableSQLs); } int n = 0; for (String sql : alterTableSQLs) { long start = System.currentTimeMillis(); boolean success = true; try { exe.executeSql(sql); } catch (SQLException e) { success = false; if (event == null || !event.onSqlExecuteError(e, tablename, sql, Collections.unmodifiableList(alterTableSQLs), n)) { throw e; } } if (success) { long cost = System.currentTimeMillis() - start; if (event != null) { event.onAlterSqlFinished(tablename, sql, Collections.unmodifiableList(alterTableSQLs), n, cost); } } n++; } } finally { exe.close(); } } private List<String> calculateConstraints(ITableMetadata meta, String tablename) throws SQLException { List<String> sqls = new ArrayList<String>(); // ? List<ColumnMapping> pkFields = meta.getPKFields(); Optional<PrimaryKey> currentPk = getPrimaryKey(tablename); String[] pkColumnsEntity = new String[pkFields.size()]; // entity for (int n = 0; n < pkFields.size(); n++) { pkColumnsEntity[n] = pkFields.get(n).getColumnName(info.profile, false); } String[] pkColumnsDB = currentPk.isPresent() ? currentPk.get().getColumns() : new String[0]; // DB if (!ArrayUtils.equals(pkColumnsEntity, pkColumnsDB)) { // FIXME ?? if (currentPk.isPresent()) { LogUtil.warn( "Primary key of table [{}] was changed from [{}] to [{}], automatically modifying is not supported yet. Please modify your table by yourself.", tablename, pkColumnsDB, pkColumnsEntity); /* * Constraint con = new Constraint(); con.setName(pk.getName()); * con.setTableName(tablename); con.setType(ConstraintType.P); * con.setColumns(Arrays.asList(pkColumnsDB)); * sqls.add(ddlGenerator.deleteConstraint(con)); */// ? } else { // Constraint con = new Constraint(); con.setTableName(tablename); con.setName(info.profile.getObjectNameToUse("PK_" + tablename)); con.setType(ConstraintType.P); con.setColumns(Arrays.asList(pkColumnsEntity)); sqls.add(ddlGenerator.addConstraint(con)); // ? } } // ?? List<Constraint> constraintsDB = info.profile.getConstraintInfo(this, meta.getSchema(), tablename, null); // DB? if (constraintsDB != null) { // null?????? constraintsDB = constraintsDB.stream().filter((e) -> e.getType() == ConstraintType.U) .collect(Collectors.toList()); List<Constraint> uniquesEntity = meta.getUniqueDefinitions().stream() .map((e) -> e.toConstraint(tablename, meta, info.profile)).collect(Collectors.toList()); sqls.addAll(this.compareConstraints(uniquesEntity, constraintsDB)); // ?SQL? } return sqls; } private List<String> calculateIndexes(ITableMetadata meta, String tablename) throws SQLException { List<String> sqls = new ArrayList<String>(); // ? List<Constraint> constraints = info.profile.getConstraintInfo(this, schema, tablename, null); Optional<PrimaryKey> pk = this.getPrimaryKey(tablename); List<ForeignKeyItem> referedKeys = getForeignKeyReferenceTo(tablename); // ? Collection<Index> indexesDB = getIndexes(tablename); List<Index> newIndexes = meta.getIndexDefinition().stream() .map(e -> Index.valueOf(e, meta, info.profile, tablename)).collect(Collectors.toList()); for (Index index : indexesDB) { if (newIndexes.remove(index)) { continue; } // ????? if (!isConstraintIndex(index, constraints, pk, referedKeys)) { sqls.add(ddlGenerator.deleteIndex(index)); } } // isDupIndex??Index?Index for (Index def : newIndexes) { sqls.add(def.toCreateSql(info.profile)); } return sqls; } // ???SQL? private List<String> compareConstraints(List<Constraint> after, List<Constraint> before) { List<String> result = new ArrayList<String>(); if (after != null && after.size() > 0) { if (before != null && before.size() > 0) { for (int i = after.size() - 1; i >= 0; i--) { Constraint conA = after.get(i); for (int j = 0; j < before.size(); j++) { Constraint conB = before.get(j); if (StringUtils.equals(conA.getSchema(), conB.getSchema()) && StringUtils.equals(conA.getName(), conB.getName())) { // ???? if (conA.equals(conB)) { // ? after.remove(i); before.remove(j); } break; } } } } } // ???? for (Constraint con : before) { result.add(ddlGenerator.deleteConstraint(con)); } // ????? for (Constraint con : after) { result.add(ddlGenerator.addConstraint(con)); } return result; } private boolean isConstraintIndex(Index index, List<Constraint> constraints, Optional<PrimaryKey> pk, List<ForeignKeyItem> foreignKeys) throws SQLException { // ? // if(index.getIndexName().equals(pk.getName()))return true; if (pk.isPresent() && ArrayUtils.equals(index.getColumnNames(), pk.get().getColumns())) { return true; } // ??????true? if (constraints == null) { return true; } // Unique?? for (Constraint c : constraints) { // Derby???Unique?? boolean isKey = (c.getType() == ConstraintType.U || c.getType() == ConstraintType.P); if (RDBMS.derby == info.profile.getName() || isKey == index.isUnique()) { if (index.getIndexName().equalsIgnoreCase(c.getName())) { return true; } // ?????Unique?????? if (ArrayUtils.equals(index.getColumnNames(), c.getColumns().toArray(new String[c.getColumns().size()]))) { return true; } } } // ?? // ????Oracle? // ????? // for(ForeignKey foreignKey:foreignKeys){ // ??? // } return false; } /** * ?truncateDDL? * * @param meta * ?? * @param tablename * ?? * @throws SQLException */ public void truncate(ITableMetadata meta, List<String> tablename) throws SQLException { StatementExecutor exe = createExecutor(); try { if (getProfile().has(Feature.NOT_SUPPORT_TRUNCATE)) { for (String table : tablename) { exe.executeSql("delete from " + table); } } else { for (String table : tablename) { exe.executeSql("truncate table " + table); } } } finally { exe.close(); } } /** * * * @param clz * CLass * @return true?false * @throws SQLException */ public <T extends IQueryableEntity> boolean createTable(Class<T> clz) throws SQLException { ITableMetadata meta = MetaHolder.getMeta(clz); return createTable(meta, meta.getTableName(true)); } /** * * * @param meta * ?? The metadata of the table. * @return Ture if the table created successful, or vv. * @throws SQLException */ public boolean createTable(ITableMetadata meta) throws SQLException { return createTable(meta, null); } /** * * * @param meta * ?? The metadata of the table. * @param tablename * ?? The name of the table. * @return truefalse<br> * Ture if the table created successful, or vv. * @throws SQLException * @see {@link ITableMetadata} */ public boolean createTable(ITableMetadata meta, String table) throws SQLException { final String tablename = StringUtils.isEmpty(table) ? meta.getTableName(true) : table; boolean created = false; if (!existTable(tablename)) { TableCreateSQLs sqls = ddlGenerator.toTableCreateClause(meta, tablename); StatementExecutor exe = createExecutor(); try { // exe.executeSql(sqls.getTableSQL()); // create sequence for (PairIS seq : sqls.getSequences()) { createSequence0(null, seq.second, 1, StringUtils.toLong(StringUtils.repeat('9', seq.first), Long.MAX_VALUE), exe, true); } exe.executeSql(sqls.getComments()); // TODO? // exe.executeSql(sqls.getOtherContraints()); // create indexes exe.executeSql(meta.getIndexDefinition().stream() .map(e -> Index.valueOf(e, meta, info.profile, tablename).toCreateSql(info.profile)) .collect(Collectors.toList())); } finally { exe.close(); } created = true; clearTableMetadataCache(meta); } if (meta.getExtendsTable() != null) { this.createTable(meta.getExtendsTable(), null); } // ? return created; } /* * ??finally DDLExecutor executor=createExecutor(); try{ * createSequence0(schema,sequenceName,start,max,executor); }finally{ * executor.close(); } */ private StatementExecutor createExecutor() { if (parent.getTransactionMode() == TransactionMode.JTA) { return new ExecutorJTAImpl(parent, dbkey, getTransactionId(), getProfile()); } else { return new ExecutorImpl(parent, dbkey, getTransactionId(), getProfile()); } } /** * Sequence * * @param schema * ?schema * @param sequenceName * ??schemaSequence?? * @param start * Sequence * @param max * Sequence * @throws SQLException */ public void createSequence(String schema, String sequenceName, long start, Long max) throws SQLException { StatementExecutor executor = createExecutor(); try { createSequence0(schema, sequenceName, start, max, executor, true); } finally { executor.close(); } } void createSequenceWithoutCheck(String schema, String sequenceName, long start, Long max) throws SQLException { StatementExecutor executor = createExecutor(); try { createSequence0(schema, sequenceName, start, max, executor, false); } finally { executor.close(); } } /** * Sequence * * @param schema * @param sequenceName * @param start * @param max * @param executor * @param check * @throws SQLException */ private void createSequence0(String schema, String sequenceName, long start, Long max, StatementExecutor executor, boolean check) throws SQLException { DatabaseDialect profile = this.getProfile(); sequenceName = profile.getObjectNameToUse(sequenceName); schema = profile.getObjectNameToUse(schema); if (check && innerExists(ObjectType.SEQUENCE, schema, sequenceName)) return; if (max == null) max = 9999999999L; long limit = profile.getPropertyLong(DbProperty.MAX_SEQUENCE_VALUE); if (limit > 0) { max = Math.min(max, limit); } long min = 1; if (min > start) min = start; if (schema != null) { sequenceName = schema + "." + sequenceName; } String sequenceSql = StringUtils.concat("create sequence ", sequenceName, " minvalue ", String.valueOf(min), " maxvalue ", String.valueOf(max), " start with ", String.valueOf(start), " increment by 1"); executor.executeSql(sequenceSql); } /** * SQL? * * @param url * the script file. * @throws SQLException */ public void executeScriptFile(URL url) throws SQLException { executeScriptFile(url, ";/"); } /** * SQL? * * @param url * the script file. * @param endChars * ? * @throws SQLException */ public void executeScriptFile(URL url, String endChars) throws SQLException { Assert.notNull(url); char[] ends = endChars.toCharArray(); StatementExecutor exe = createExecutor(); try { StringBuilder sb = new StringBuilder(); BufferedReader reader = IOUtils.getReader(url, null); String line; while ((line = reader.readLine()) != null) { line = line.trim(); if (line.length() == 0 || line.startsWith("--")) { continue; } char end = line.charAt(line.length() - 1); if (ArrayUtils.contains(ends, end)) { sb.append(line.substring(0, line.length() - 1)); String sql = sb.toString(); sb.setLength(0); exe.executeSql(sql); } else { sb.append(line).append('\n'); } } if (sb.length() > 0) { exe.executeSql(sb.toString()); } } catch (IOException e) { LogUtil.exception(e); } finally { exe.close(); } } /** * SQL?<br> * Execute the sql * * @param sql * SQL? * @param ps * ??? * @return ?? * @throws SQLException */ public final int executeSql(String sql, Object... ps) throws SQLException { StatementExecutor exe = createExecutor(); try { return exe.executeUpdate(sql, ps); } finally { exe.close(); } } /** * ?SQL? * * @param sql * SQL? * @param rst * ? * @param maxReturn * * @param objs * ??? * @return ?? * @throws SQLException */ public final <T> T selectBySql(String sql, ResultSetExtractor<T> rst, List<?> objs, boolean log) throws SQLException { Connection conn = getConnection(false); try { SqlLog debug = log ? ORMConfig.getInstance().newLogger() : SqlLog.DUMMY; return select0(conn, sql, rst, objs, debug); } finally { releaseConnection(conn); } } private <T> T select0(Connection conn, String sql, ResultSetExtractor<T> rst, List<?> objs, SqlLog debug) throws SQLException { // ???ResultSet if (!rst.autoClose()) { throw new UnsupportedOperationException(); } PreparedStatement st = null; ResultSet rs = null; DatabaseDialect profile = getProfile(); try { debug.ensureCapacity(sql.length() + 30); debug.append(sql).append(" | ", getTransactionId()); st = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); if (objs != null) { BindVariableContext context = new BindVariableContext(st, profile, debug); context.setVariables(objs); } // ? MySQLBUG limit=1 rst.apply(st); rs = st.executeQuery(); return rst.transformer(new ResultSetImpl(rs, profile)); } catch (SQLException e) { DebugUtil.setSqlState(e, sql); throw e; } finally { DbUtils.close(rs); DbUtils.close(st); debug.output(); } } /** * ?: * * @param tablename * ???Schema?? * @param constraintName * ??? * @throws SQLException */ public void dropConstraint(String tablename, String constraintName) throws SQLException { tablename = MetaHolder.toSchemaAdjustedName(tablename); StatementExecutor exe = createExecutor(); try { dropConstraint0(tablename, constraintName, exe); } finally { exe.close(); } } /** * ?? * * @param tablename * ??(?schema??) * @throws SQLException */ public void dropAllForeignKey(String tablename) throws SQLException { tablename = MetaHolder.toSchemaAdjustedName(tablename); StatementExecutor exe = createExecutor(); try { dropAllForeignKey0(tablename, null, exe); } finally { exe.close(); } } /** * ? FIXME ??UNIQUE Constraint.... * * @param tablename * ??(?schema??) * @throws SQLException */ public void dropAllConstraint(String tablename) throws SQLException { dropAllForeignKey(tablename);// dropPrimaryKey(tablename); } /** * * * @param tablename * ?schema?? * @return true if drop success. * @throws SQLException */ public boolean dropPrimaryKey(String tablename) throws SQLException { Optional<PrimaryKey> pk = getPrimaryKey(tablename); if (pk.isPresent()) { dropConstraint(tablename, pk.get().getName()); return true; } return false; } /** * * * @param index * @throws SQLException */ public boolean createIndex(Index index) throws SQLException { Collection<Index> indexs = getIndexes(index.getTableWithSchem()); for (Index old : indexs) { if (old.equals(index)) { LogUtil.warn(index + " duplicate with old index " + old.getIndexName()); return false; } // ???? if (old.getIndexName().equalsIgnoreCase(index.getIndexName())) { index.generateRandomName(); } } StatementExecutor exe = createExecutor(); try { exe.executeSql(index.toCreateSql(getProfile())); return true; } finally { exe.close(); } } public void dropIndex(String tableName, String indexName) throws SQLException { Index index = new Index(); index.setIndexName(indexName); index.setTableName(tableName); dropIndex(index); } /** * * * @param index * @throws SQLException */ public void dropIndex(Index index) throws SQLException { String pattern = getProfile().getProperty(DbProperty.DROP_INDEX_TABLE_PATTERN); String sql; if (pattern == null) { sql = "DROP INDEX " + index.getIndexName(); } else { sql = "DROP INDEX " + String.format(pattern, index.getIndexName(), index.getTableName()); } StatementExecutor exe = createExecutor(); try { exe.executeSql(sql); } finally { exe.close(); } } /** * ?? * * @param table * the name of table. * @return true if table was dropped. false if the table was not exist. * @throws SQLException * Any error while executing SQL. */ public boolean dropTable(String table) throws SQLException { table = getExistTable(table); if (table != null) { StatementExecutor exe = createExecutor(); String sql = "drop table " + DbUtils.escapeColumn(getProfile(), table); try { if (getProfile().has(Feature.DROP_CASCADE)) { sql += " cascade constraints"; } else if (getProfile().notHas(Feature.NOT_SUPPORT_FOREIGN_KEY)) { dropAllForeignKey0(table, null, exe); } exe.executeSql(sql); subtableCacheExpireTime = 0; return true; } finally { exe.close(); } } return false; } /** * * * @param table * Entity * @return true if table was dropped. false if the table was not exist. * @throws SQLException * Any error while executing SQL. */ public boolean dropTable(Class<?> table) throws SQLException { ITableMetadata meta = MetaHolder.getMeta(table); return dropTable(meta.getTableName(true)); } /** * ?????? get the datasource name of current metadata connection. * * @return name of data source */ public String getDbkey() { return dbkey; } /** * Delete the assign sequence or do nothing if the sequence not exists.. * * @param sequenceName * the name of sequence. * @return true if drop success. * @throws SQLException * */ public boolean dropSequence(String sequenceName) throws SQLException { return dropSequence(null, sequenceName); } /** * Delete the assign sequence or do nothing if the sequence not exists.. * * @param schema * @param sequenceName * @return * @throws SQLException */ public boolean dropSequence(String schema, String sequenceName) throws SQLException { String seqName = (schema == null ? sequenceName : schema + "." + sequenceName); seqName = getExists(ObjectType.SEQUENCE, seqName); if (seqName != null) { StatementExecutor exe = createExecutor(); try { exe.executeSql("drop sequence " + seqName); } finally { exe.close(); } return true; } return false; } /** * ? * <ul> * <li>{@link #TABLE}<br> * ?</li> * <li>{@link #SEQUENCE}<br> * ??</li> * <li>{@link #VIEW}<br> * ?</li> * <li>{@link #FUNCTION}<br> * </li> * <li>{@link #PROCEDURE}<br> * </li> * </ul> * <p> */ public static enum ObjectType { /** * ??? */ TABLE, /** * ? */ SEQUENCE, /** * */ VIEW, /** * */ FUNCTION, /** * */ PROCEDURE } @Override public String toString() { return this.getTransactionId(); } /* * ????() */ private Set<String> calculateSubTables(String tableName, ITableMetadata meta, boolean isDefault) throws SQLException { long start = System.currentTimeMillis(); List<Column> columns = getColumns(tableName, false); int baseColumnCount = columns.size(); if (baseColumnCount == 0) { baseColumnCount = meta.getColumns().size(); } List<TableInfo> tables; if (info.profile.isCaseSensitive()) { tables = new ArrayList<TableInfo>(); String lower = tableName.toLowerCase(); String upper = tableName.toUpperCase(); tables.addAll(getDatabaseObject(ObjectType.TABLE, this.schema, tableName, Operator.MATCH_START, false)); if (!lower.equals(tableName)) { tables.addAll(getDatabaseObject(ObjectType.TABLE, this.schema, lower, Operator.MATCH_START, false)); } if (!upper.equals(tableName)) { tables.addAll(getDatabaseObject(ObjectType.TABLE, this.schema, upper, Operator.MATCH_START, false)); } } else { tables = getDatabaseObject(ObjectType.TABLE, this.schema, tableName, Operator.MATCH_START, false); } String tableNameWithoutSchema = StringUtils.substringAfterIfExist(tableName, "."); // ? Pattern suffix; if (isDefault) { PartitionTable pt = meta.getPartition(); StringBuilder suffixRegexp = new StringBuilder(tableNameWithoutSchema); suffixRegexp.append(pt.appender()); int n = 0; for (@SuppressWarnings("rawtypes") Entry<PartitionKey, PartitionFunction> entry : meta.getEffectPartitionKeys()) { PartitionKey key = entry.getKey(); if (key.isDbName()) continue; if (n > 0) { suffixRegexp.append(pt.keySeparator());// } if (DefaultPartitionCalculator.isNumberFun(key)) { suffixRegexp.append("\\d"); } else { suffixRegexp.append("[a-zA-Z0-9]"); } if (key.length() > 0) { suffixRegexp.append("{" + key.length() + "}"); } else { suffixRegexp.append("+"); } } suffix = Pattern.compile(suffixRegexp.toString(), Pattern.CASE_INSENSITIVE); } else { suffix = Pattern.compile(tableNameWithoutSchema.concat("(_?[0-9_]{1,4})+"), Pattern.CASE_INSENSITIVE); } Set<String> result = new HashSet<String>(); String schema = meta.getSchema(); for (TableInfo entry : tables) { if (suffix.matcher(entry.getName()).matches()) { String fullTableName = schema == null ? entry.getName() : schema + "." + entry.getName(); List<Column> subColumns = getColumns(fullTableName, false); if (subColumns.size() == baseColumnCount) { result.add(fullTableName.toUpperCase()); } else { LogUtil.info("The table [" + fullTableName + "](" + subColumns.size() + ") seems like a subtable of [" + tableName + "], but their columns are not match.\n" + subColumns); } } } start = System.currentTimeMillis() - start; if (ORMConfig.getInstance().isDebugMode()) { LogUtil.debug("Scan Partition Tables for [" + tableName + "] at " + info + ", found " + result.size() + " result. cost " + start + " ms."); } return result; } private boolean innerExists(ObjectType type, String schema, String objectName) throws SQLException { if (schema == null) schema = this.getCurrentSchema();// ?schema? switch (type) { case FUNCTION: return existsFunction(schema, objectName); case PROCEDURE: return existsProcdure(schema, objectName); case SEQUENCE: List<SequenceInfo> seqs = this.getProfile().getSequenceInfo(this, schema, objectName); if (seqs != null) { return !seqs.isEmpty(); } default: break; } Connection conn = getConnection(false); DatabaseDialect trans = info.profile; DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = databaseMetaData.getTables(trans.getCatlog(schema), trans.getSchema(schema), objectName, new String[] { type.name() }); try { return rs.next(); } finally { DbUtils.close(rs); releaseConnection(conn); } } private List<Function> innerGetFunctions(String schema, String name) throws SQLException { if (schema == null) { schema = this.schema; } Connection conn = getConnection(false); DatabaseDialect profile = getProfile(); if (profile.has(Feature.NOT_SUPPORT_USER_FUNCTION)) { return Collections.emptyList(); } List<Function> result = new ArrayList<Function>(); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = null; try { rs = databaseMetaData.getFunctions(profile.getCatlog(schema), profile.getSchema(schema), name); while (rs.next()) { Function function = new Function(); function.setCatalog(rs.getString(1)); function.setSchema(rs.getString(2)); function.setName(rs.getString(3)); function.setRemarks(rs.getString(4)); function.setType(rs.getShort(5)); function.setSpecificName(rs.getString(6)); result.add(function); } } catch (java.sql.SQLFeatureNotSupportedException e) { LogUtil.warn(databaseMetaData.getDriverName() + " doesn't supprt getFunctions() defined in JDDBC 4.0."); } catch (AbstractMethodError e) { // Driver version is too old... StringBuilder sb = new StringBuilder("The driver ").append(databaseMetaData.getDriverName()); sb.append(' ').append(databaseMetaData.getDriverVersion()).append(' ') .append(databaseMetaData.getDatabaseMinorVersion()); sb.append(" not implements JDBC 4.0, please upgrade you JDBC Driver."); throw new SQLException(sb.toString()); } finally { DbUtils.close(rs); releaseConnection(conn); } return result; } private List<Function> innerGetProcedures(String schema, String procdureName) throws SQLException { if (schema == null) { schema = this.schema; } DatabaseDialect profile = getProfile(); Connection conn = getConnection(false); DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = null; try { List<Function> result = new ArrayList<Function>(); rs = databaseMetaData.getProcedures(profile.getCatlog(schema), profile.getSchema(schema), procdureName); while (rs.next()) { Function function = new Function(ObjectType.PROCEDURE); function.setCatalog(rs.getString(1)); function.setSchema(rs.getString(2)); function.setName(rs.getString(3)); function.setRemarks(rs.getString(7)); function.setType(rs.getShort(8)); function.setSpecificName(rs.getString(9)); result.add(function); } return result; } finally { DbUtils.close(rs); releaseConnection(conn); } } private Map<Field, ColumnMapping> getColumnMap(ITableMetadata meta) { Map<Field, ColumnMapping> map = new HashMap<Field, ColumnMapping>(); for (ColumnMapping mapping : meta.getColumns()) { map.put(mapping.field(), mapping); } return map; } private String getTransactionId() { if (info == null) return super.toString(); StringBuilder sb = new StringBuilder(); sb.append('[').append(info.profile.getName()).append(':').append(getDbName()).append('@') .append(Thread.currentThread().getId()).append(']'); return sb.toString(); } private void releaseConnection(Connection con) { DbUtils.closeConnection(con); } private void dropConstraint0(String tablename, String constraintName, StatementExecutor exe) throws SQLException { exe.executeSql(ddlGenerator.getDropConstraintSql(tablename, constraintName)); } /* * * * @param tablename ??(?Schema??) * * @param referenceBy truefalse: null * * @throws SQLException */ private void dropAllForeignKey0(String tablename, Boolean referenceBy, StatementExecutor exe) throws SQLException { if (referenceBy == null || referenceBy == Boolean.TRUE) { for (ForeignKeyItem fk : getForeignKeyReferenceTo(tablename)) { dropConstraint0(fk.getFromTable(), fk.getName(), exe); } } if (referenceBy == null || referenceBy == Boolean.FALSE) { for (ForeignKeyItem fk : getForeignKey(tablename)) { dropConstraint0(fk.getFromTable(), fk.getName(), exe); } } } /* * ?SQL? * * @param schema schema?? * * @param tableName ?schema?? * * @param sequenceColumnName ??? * * @return */ private static String createGetMaxSequenceColumnValueStatement(String schema, String tableName, String sequenceColumnName) { if (schema != null) { tableName = schema + "." + tableName; } return StringUtils.concat("select max(", sequenceColumnName, ") from " + tableName); } private final static Transformer FK_TRANSFORMER = new Transformer(ForeignKeyItem.class); private final static Transformer DATATYPE_TRANSFORMER = new Transformer(DataType.class); /** * ??????/??? ??? * * @param meta * @return true if the cache was flushed. */ public boolean clearTableMetadataCache(ITableMetadata meta) { String baseName = getProfile().getObjectNameToUse(meta.getTableName(true)); return subtableCache.remove(baseName) != null; } protected boolean hasRemarkFeature() { if (JefConfiguration.getBoolean(DbCfg.DB_NO_REMARK_CONNECTION, false)) { return false; } DatabaseDialect profile; if (this.info == null) { ConnectInfo info = DbUtils.tryAnalyzeInfo(ds, false); if (info == null) { Connection conn = null; try { conn = ds.getConnection(); info = DbUtils.tryAnalyzeInfo(conn); } catch (SQLException e) { return false; } finally { DbUtils.closeConnection(conn); } } profile = info.getProfile(); } else { profile = info.getProfile(); } return profile.has(Feature.REMARK_META_FETCH); } public boolean supportsSequence() { return getProfile().has(Feature.SUPPORT_SEQUENCE); } public MetadataFeature getFeature() { return feature; } public void init() { // TODO Auto-generated method stub } // @Override // protected boolean processCheck(Connection conn) { // DatabaseDialect dialect = getProfile(); // String func = dialect.getFunction(Func.current_timestamp); // String template = dialect.getProperty(DbProperty.SELECT_EXPRESSION); // String sql; // if (template == null) { // sql = "SELECT " + func; // } else { // sql = String.format(template, func); // } // try { // long current = System.currentTimeMillis(); // Date date = this.selectBySql(sql, ResultSetExtractor.GET_FIRST_TIMESTAMP, // 1, Collections.EMPTY_LIST); // current = (System.currentTimeMillis() + current) / 2;// // ???? // long delta = date.getTime() - System.currentTimeMillis(); // if (Math.abs(delta) > 60000) { // 1 // LogUtil.warn("Database time checked. It is far different from local // machine: " // + DateUtils.formatTimePeriod(delta, TimeUnit.DAY, Locale.US)); // } // this.dbTimeDelta = delta; // return true; // } catch (SQLException e) { // return false; // } // } }