gu.sql2java.generator.Database Maven / Gradle / Ivy

package gu.sql2java.generator;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;
import java.util.TreeMap;
import java.util.Vector;

import com.google.common.base.Function;
import com.google.common.base.Joiner;
import com.google.common.base.Predicate;
import com.google.common.base.Strings;
import com.google.common.base.Throwables;
import com.google.common.collect.Collections2;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.Iterators;
import com.google.common.collect.Lists;
import com.google.common.primitives.Ints;

import gu.sql2java.generator.CodeWriter;
import gu.sql2java.generator.Column;
import gu.sql2java.generator.Index;
import gu.sql2java.generator.IndexColumn;
import gu.sql2java.generator.Procedure;
import gu.sql2java.generator.Table;

import static com.google.common.base.Preconditions.checkState;
import static gu.sql2java.generator.GeneratorConfig.CONFIG;

import net.gdface.utils.InterfaceDecorator;

public class Database {
	private String[] tableTypes;
	private Connection pConnection;
	private DatabaseMetaData meta;
	private Vector tables;
	private Hashtable tableHash;
	private String engine;
	private String driver;
	private String url;
	private String username;
	private String password;
	private String catalog;
	private String schema;
	private String tablenamepattern = "%";
	/**
	 * 所有表名的共同前缀
	 */
	private String samePrefix = "";
	public void setDriver(String driver) {
		this.driver = driver;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public void setCatalog(String catalog) {
		this.catalog = catalog;
	}

	public void setTableNamePattern(String tablenamepattern) {
		if(tablenamepattern != null){
			this.tablenamepattern = tablenamepattern;
		}
	}

	public void setTableTypes(String[] tt) {
		this.tableTypes = tt;
	}

	public String getEngine() {
		return this.engine;
	}

	public String getDriver() {
		return this.driver;
	}

	public String getUrl() {
		return this.url;
	}

	public String getUsername() {
		return this.username;
	}

	public String getPassword() {
		return this.password;
	}

	public String getCatalog() {
		return this.catalog;
	}

	public String getSchema() {
		return this.schema;
	}

	public String getTableNamePattern() {
		return this.tablenamepattern;
	}

	public String[] getTableTypes() {
		return this.tableTypes;
	}

	public void setSchema(String schema) {
		this.schema = "null".equalsIgnoreCase(schema) ? null : schema;
	}

	public Table[] getRelationTable(Table table) {
		Vector
 vector = new Vector();
		for (int iIndex = 0; iIndex < this.tables.size(); ++iIndex) {
			Table tempTable = (Table) this.tables.get(iIndex);
			if (table.equals((Object) tempTable) || !tempTable.isRelationTable() || !tempTable.relationConnectsTo(table)
					|| vector.contains((Object) tempTable))
				continue;
			vector.add(tempTable);
		}
		return vector.toArray(new Table[vector.size()]);
	}

	public void load() throws SQLException, ClassNotFoundException {
		
		try {
			// 加载并注册JDBC驱动
			Class driverClass = Class.forName(this.driver,true,CONFIG.getClassloader());
			DriverManager.registerDriver(
					new InterfaceDecorator(Driver.class,(Driver)driverClass.newInstance())
						.proxyInstance());
		} catch (ReflectiveOperationException e) {
			throw new RuntimeException(e);
		}
		System.out.println("Connecting to " + this.username + " on " + this.url + " ...");
		this.pConnection = DriverManager.getConnection(this.url, this.username, this.password);
		System.out.println("    Connected.");
		try {
			// for oracle connection,set 'remarksReporting' flag to true for read comments
			// 连接为 oracle JDBC 时,调用 setRemarksReporting 设置'remarksReporting'为true,用于读取表中的注释内容
			Class connClass = pConnection.getClass();
			if(connClass.getName().equals("oracle.jdbc.driver.OracleConnection")){
				Method method = connClass.getMethod("setRemarksReporting", boolean.class);	
				if(null != method){
					method.invoke(pConnection, true);
				}
			}			
		} catch (Exception e) {
			Throwables.throwIfUnchecked(e);
			throw new RuntimeException(e);
		}
		this.meta = this.pConnection.getMetaData();
		this.engine = this.meta.getDatabaseProductName();
		System.out.println("    Database server :" + this.engine + ".");
		this.engine = new StringTokenizer(this.engine).nextToken();
		this.tables = new Vector();
		this.tableHash = new Hashtable();
		this.loadTables();
		this.initSamePrefix();
		this.loadColumns();
		this.loadPrimaryKeys();
		this.loadImportedKeys();
		this.loadIndexes();
		this.loadProcedures();
		this.sortElements();
		this.pConnection.close();
	}

	public Table[] getTables() {
		return this.tables.toArray(new Table[this.tables.size()]);
	}

	private void addTable(Table t) {
		this.tables.addElement(t);
		this.tableHash.put(t.getName(), t);
	}

	public Table getTable(String name) {
		return (Table) this.tableHash.get(name);
	}

	private void loadTables() throws SQLException {
		System.out.println("Loading table list according to pattern " + this.tablenamepattern + " ...");
		StringTokenizer st = new StringTokenizer(this.tablenamepattern, ",; \t");
		while (st.hasMoreTokens()) {
			String pattern = st.nextToken().trim();
			String tableSchema = this.schema;
			int index = pattern.indexOf(46);
			if (index > 0) {
				tableSchema = pattern.substring(0, index);
				pattern = pattern.substring(index + 1);
			}
			ResultSet resultSet = this.meta.getTables(this.catalog, tableSchema, pattern, this.tableTypes);
			while (resultSet.next()) {
				Table table = new Table();
				table.setCatalog(resultSet.getString("TABLE_CAT"));
				table.setSchema(resultSet.getString("TABLE_SCHEM"));
				table.setName(resultSet.getString("TABLE_NAME"));
				table.setType(resultSet.getString("TABLE_TYPE"));
				table.setRemarks(resultSet.getString("REMARKS"));
				table.setDatabase(this);
				if (!CodeWriter.authorizeProcess((String) table.getName(), (String) "tables.include",
						(String) "tables.exclude"))
					continue;
				this.addTable(table);
				System.out.println("    table " + table.getName() + " found");
			}
			resultSet.close();
		}
	}

	private void loadColumns() throws SQLException {
		System.out.println("Loading columns ...");
		Iterator it = this.tables.iterator();
		while (it.hasNext()) {
			Table table = (Table) it.next();
			Column c = null;
			ResultSet resultSet = this.meta.getColumns(table.getCatalog(), table.getSchema(), table.getName(), "%");
			while (resultSet.next()) {
				c = new Column();
				c.setDatabase(this);
				c.setCatalog(resultSet.getString("TABLE_CAT"));
				c.setSchema(resultSet.getString("TABLE_SCHEM"));
				c.setTableName(resultSet.getString("TABLE_NAME"));
				c.setName(resultSet.getString("COLUMN_NAME"));
				c.setType(resultSet.getShort("DATA_TYPE"));
				c.setTypeName(resultSet.getString("TYPE_NAME"));
				c.setSize(resultSet.getInt("COLUMN_SIZE"));
				c.setDecimalDigits(resultSet.getInt("DECIMAL_DIGITS"));
				c.setRadix(resultSet.getInt("NUM_PREC_RADIX"));
				c.setNullable(resultSet.getInt("NULLABLE"));
				c.setRemarks(resultSet.getString("REMARKS"));
				c.setDefaultValue(resultSet.getString("COLUMN_DEF"));
				c.setOrdinalPosition(resultSet.getInt("ORDINAL_POSITION"));
				c.setAutoincrement(resultSet.getString("IS_AUTOINCREMENT"));
				System.out.printf("        %s %s(%d) %s default value: %s\n", c.getFullName(),c.getTypeName(),c.getSize(),c.isAutoincrement()?"AUTOINCREMENT":"",c.getOriginalDefaultValue());
				table.addColumn(c);
				if(c.isAutoincrement())
					table.setAutoincrement(c);
			}
			resultSet.close();
			System.out.println("    " + table.getName() + " found " + table.countColumns() + " columns");
		}
	}

	private void loadPrimaryKeys() throws SQLException {
		System.out.println("Database::loadPrimaryKeys");
		Iterator it = this.tables.iterator();
		while (it.hasNext()) {
			Column col;
			Table table = (Table) it.next();
			TreeMap map = new TreeMap();
			ResultSet pResultSet = this.meta.getPrimaryKeys(table.getCatalog(), table.getSchema(), table.getName());
			while (pResultSet.next()) {
				String colName = pResultSet.getString("COLUMN_NAME");
				short seq = pResultSet.getShort("KEY_SEQ");
				System.out.println("Found primary key (seq,name) (" + seq + "," + colName + ") for table '"
						+ table.getName() + "'");
				col = table.getColumn(colName);
				if (col == null)
					continue;
				map.put(String.valueOf(seq), col);
			}
			pResultSet.close();
			int size = map.size();
			for (int k = 1; k <= size; ++k) {
				col = (Column) map.get(String.valueOf(k));
				table.addPrimaryKey(col);
			}
		}
	}

	private void loadImportedKeys() throws SQLException {
		System.out.println("Loading imported keys ...");
		Iterator it = this.tables.iterator();
		while (it.hasNext()) {
			ResultSet resultSet;
			Table table = (Table) it.next();
			try {
				resultSet = this.meta.getImportedKeys(table.getCatalog(), table.getSchema(), table.getName());
			} catch (SQLException sqle) {
				System.out.println("    Error while loading imported keys for table " + table.getName());
				continue;
			}
			while (resultSet.next()) {
				String tabName = resultSet.getString("FKTABLE_NAME");
				String colName = resultSet.getString("FKCOLUMN_NAME");
				String foreignTabName = resultSet.getString("PKTABLE_NAME");
				String foreignColName = resultSet.getString("PKCOLUMN_NAME");
				String foreignKeyName = resultSet.getString("FK_NAME");
				short updateRule = resultSet.getShort("UPDATE_RULE");
				short deleteRule = resultSet.getShort("DELETE_RULE");
				if(Strings.isNullOrEmpty(foreignKeyName)){
					Vector primaryKeys = this.getTable(tabName).getPrimaryKeysAsList();
					checkState(!primaryKeys.isEmpty());
					// make a fake name
					String combinName = Joiner.on('_').join(Iterators.transform(primaryKeys.iterator(), new Function(){
						@Override
						public String apply(Column input) {
							return input.getName();
						}}));
					foreignKeyName="fk_"+ tabName + "_" +combinName;
					System.out.println("WARN: FK_NAME return empty,the generated code  may be incorrected.");
				}
					
				short seq = resultSet.getShort("KEY_SEQ");
				Column col = this.getTable(tabName).getColumn(colName);
				Table foreignTable = this.getTable(foreignTabName);
				if (null == foreignTable)
					continue;
				Column foreignCol = foreignTable.getColumn(foreignColName);
				col.addForeignKey(foreignCol, foreignKeyName, seq, 
								Table.ForeignKeyRule.values()[updateRule],
								Table.ForeignKeyRule.values()[deleteRule]);
				foreignCol.addImportedKey(col);
				System.out.println("    " + col.getFullName() + " -> " + foreignCol.getFullName() + " found seq:"+ seq+" foreign key name:"+ foreignKeyName);
				System.out.println("    UPDATE_RULE:" + Table.ForeignKeyRule.values()[updateRule].name() 
						         + "    DELETE_RULE:" + Table.ForeignKeyRule.values()[deleteRule].name());
			}
			resultSet.close();
		}
	}

	private void loadIndexes() throws SQLException {
		System.out.println("Loading indexes ...");
		Iterator it = this.tables.iterator();
		while (it.hasNext()) {
			Table table = (Table) it.next();
			ResultSet resultSet = null;
			try {
				resultSet = this.meta.getIndexInfo(table.getCatalog(), table.getSchema(), table.getName(), false, true);
			} catch (SQLException sqle) {
				System.out.println("    Error while loading indexes for table " + table.getName());
				continue;
			}
			String currentName = "";
			Index index = null;
			while (resultSet.next()) {
				Column col;
				String colName = resultSet.getString("COLUMN_NAME");
				String indName = resultSet.getString("INDEX_NAME");
				if (null == indName || null == colName || (col = table.getColumn(colName)).isPrimaryKey())
					continue;
				if (!currentName.equals(indName)) {
					index = new Index(indName, table);
					index.setUnique(!resultSet.getBoolean("NON_UNIQUE"));
					currentName = indName;
				}
				System.out.println(
						"  Found interesting index " + indName + (index.isUnique()?"(UNIQUE)": "") + " on " + colName + " for table " + table.getName() );

				IndexColumn column = new IndexColumn();
				column.setName(resultSet.getString("COLUMN_NAME"));
				column.setOrdinalPosition((int) resultSet.getShort("ORDINAL_POSITION"));
				column.setSortSequence(resultSet.getString("ASC_OR_DESC"));
				column.setFilterCondition(resultSet.getString("FILTER_CONDITION"));
				column.setType(col.getType());
				column.setRemarks(col.getRemarks());
				column.setTableName(col.getTableName());
				column.setDatabase(this);
				index.addIndexColumn(column);
			}
			resultSet.close();
		}
	}

	private void loadProcedures() throws SQLException {
		System.out.println("Loading procedures ...");
		Iterator it = this.tables.iterator();
		while (it.hasNext()) {
			Table table = (Table) it.next();
			String procedurePattern = "%" + table.getName() + "%";
			ResultSet resultSet = null;
			try {
				resultSet = this.meta.getProcedures(table.getCatalog(), table.getSchema(), procedurePattern);
			} catch (SQLException sqle) {
				System.out.println("    Error while loading procedures for table " + table.getName());
				continue;
			}
			while (resultSet.next()) {
				String spName = resultSet.getString("PROCEDURE_NAME");
				String spRemarks = resultSet.getString("REMARKS");
				Procedure procedure = new Procedure();
				procedure.setName(spName);
				procedure.setRemarks(spRemarks);
				procedure.setReturnType("void");
				table.addProcedure(procedure);
				System.out.println("    Found procedure " + spName + " for table " + table.getName());
				ResultSet rs = this.meta.getProcedureColumns(this.catalog, this.schema, spName, null);
				while (rs.next()) {
					String colName = rs.getString("COLUMN_NAME");
					short columnType = rs.getShort("COLUMN_TYPE");
					if (DatabaseMetaData.procedureColumnUnknown == columnType) {
						System.err.println("    Column " + colName + " of unknown type in procedure " + spName);
						continue;
					}
					Column c = new Column();
					c.setType(rs.getShort("DATA_TYPE"));
					if (5 == columnType) {
						procedure.setReturnType(c.getJavaType());
						continue;
					}
					c.setDatabase(this);
					c.setCatalog(rs.getString("PROCEDURE_CAT"));
					c.setSchema(rs.getString("PROCEDURE_SCHEM"));
					c.setTableName(rs.getString("PROCEDURE_NAME"));
					c.setName(colName);
					c.setSize(rs.getInt("LENGTH"));
					c.setDecimalDigits(rs.getInt("SCALE"));
					c.setRadix(rs.getInt("RADIX"));
					c.setNullable(rs.getInt("NULLABLE"));
					c.setRemarks(rs.getString("REMARKS"));
					switch (columnType) {
						case DatabaseMetaData.procedureColumnIn : {
							procedure.addInColumn(c);
							continue ;
						}
						case DatabaseMetaData.procedureColumnInOut : {
							procedure.addInOutColumn(c);
							continue ;
						}
						case DatabaseMetaData.procedureColumnOut : {
							procedure.addOutColumn(c);
							continue ;
						}
					}
					procedure.setReturnType("List");
				}
				rs.close();
			}
			resultSet.close();
		}
	}

	public String[] getAllPackages() {
		Vector vector = new Vector();
		for (int iIndex = 0; iIndex < this.tables.size(); ++iIndex) {
			Table table = (Table) this.tables.get(iIndex);
			if (vector.contains(table.getPackage()))
				continue;
			vector.add(table.getPackage());
		}
		return vector.toArray(new String[vector.size()]);
	}
	/**
	 * sort foreign keys and Import keys of all column
	 */
	private void sortElements(){
		for(Table table:this.tables){
			for(Column column:table.getColumns()){
				Collections.sort(column.getForeignKeys());
				Collections.sort(column.getImportedKeys());
			}
		}
	}
	
	public String getSamePrefix()  {		
		return this.samePrefix;
	}	
	/**
	 * 计算所有表名的共同前缀
	 */
	private void initSamePrefix()  {
		int index=-1;
		if(0==this.tables.size())return;
		String first=this.tables.get(0).getName();
		/* 
		 * 返回所有表名的长度，如果表中有下划线，则只返回包含最后一个下划线的长度
		 * 以确保后续计算中得到的前缀长度不能为表名的全部长度
		 */
		List lengths = Lists.transform(this.tables, new Function(){

			@Override
			public Integer apply(Table input) {
				String name = input.getName();
				int lastUnderline = name.lastIndexOf('_') + 1;
				return (lastUnderline > 0 && lastUnderline < name.length() -1 ) ? lastUnderline: name.length();
			}});
		int minlen = Ints.min(Ints.toArray(lengths));
		if(this.tables.size()>1){
			try{
				for(int i=0;i0){
				this.samePrefix = this.tablenamepattern.substring(0, pidx);
			}else {
				this.samePrefix = "";
			}
		}		
		System.out.printf("samePrefix = [%s]\n", this.samePrefix);
	}

	public List getJunctionTables() {
		Table[] tabs = this.getTables();
		ArrayList
 tables = new ArrayList(tabs.length);
		for (int i = 0; i < tabs.length; ++i) {
			if (!tabs[i].isJunctionTable())
				continue;
			tables.add(tabs[i]);
		}
		return tables;
	}
	public List getTablesWithPk(){
		return Lists.newArrayList(Collections2.filter(ImmutableList.copyOf(getTables()), new Predicate(){
			@Override
			public boolean apply(Table input) {
				return input.hasPrimaryKey();
			}}));
	}
	/**
	 * 返回主键字段数目为指定值的表
	 */
	public List getTablesWithPk(final Integer pkLimit){
		return Lists.newArrayList(Collections2.filter(ImmutableList.copyOf(getTables()), new Predicate(){
			@Override
			public boolean apply(Table input) {
				return input.countPrimaryKeys() == pkLimit;
			}}));
	}
}



© 2015 - 2024 Weber Informatics LLC | Privacy Policy