源代码

001package gu.sql2java.generator;
002
003import java.lang.reflect.Method;
004import java.sql.Connection;
005import java.sql.DatabaseMetaData;
006import java.sql.Driver;
007import java.sql.DriverManager;
008import java.sql.ResultSet;
009import java.sql.SQLException;
010import java.util.ArrayList;
011import java.util.Collections;
012import java.util.Hashtable;
013import java.util.Iterator;
014import java.util.List;
015import java.util.StringTokenizer;
016import java.util.TreeMap;
017import java.util.Vector;
018
019import com.google.common.base.Function;
020import com.google.common.base.Joiner;
021import com.google.common.base.Predicate;
022import com.google.common.base.Strings;
023import com.google.common.base.Throwables;
024import com.google.common.collect.Collections2;
025import com.google.common.collect.ImmutableList;
026import com.google.common.collect.Iterators;
027import com.google.common.collect.Lists;
028
029import gu.sql2java.generator.CodeWriter;
030import gu.sql2java.generator.Column;
031import gu.sql2java.generator.Index;
032import gu.sql2java.generator.IndexColumn;
033import gu.sql2java.generator.Procedure;
034import gu.sql2java.generator.Table;
035
036import static com.google.common.base.Preconditions.checkState;
037import static gu.sql2java.generator.GeneratorConfig.*;
038
039import net.gdface.utils.InterfaceDecorator;
040
041public class Database {
042        private String[] tableTypes;
043        private Connection pConnection;
044        private DatabaseMetaData meta;
045        private Vector<Table> tables;
046        private Hashtable<String,Table> tableHash;
047        private String engine;
048        private String driver;
049        private String url;
050        private String username;
051        private String password;
052        private String catalog;
053        private String schema;
054        private String tablenamepattern;
055        /**
056         * 所有表名的共同前缀
057         */
058        private String samePrefix = "";
059        public void setDriver(String driver) {
060                this.driver = driver;
061        }
062
063        public void setUrl(String url) {
064                this.url = url;
065        }
066
067        public void setUsername(String username) {
068                this.username = username;
069        }
070
071        public void setPassword(String password) {
072                this.password = password;
073        }
074
075        public void setCatalog(String catalog) {
076                this.catalog = catalog;
077        }
078
079        public void setTableNamePattern(String tablenamepattern) {
080                this.tablenamepattern = tablenamepattern;
081        }
082
083        public void setTableTypes(String[] tt) {
084                this.tableTypes = tt;
085        }
086
087        public String getEngine() {
088                return this.engine;
089        }
090
091        public String getDriver() {
092                return this.driver;
093        }
094
095        public String getUrl() {
096                return this.url;
097        }
098
099        public String getUsername() {
100                return this.username;
101        }
102
103        public String getPassword() {
104                return this.password;
105        }
106
107        public String getCatalog() {
108                return this.catalog;
109        }
110
111        public String getSchema() {
112                return this.schema;
113        }
114
115        public String getTableNamePattern() {
116                return this.tablenamepattern;
117        }
118
119        public String[] getTableTypes() {
120                return this.tableTypes;
121        }
122
123        public void setSchema(String schema) {
124                this.schema = "null".equalsIgnoreCase(schema) ? null : schema;
125        }
126
127        public Table[] getRelationTable(Table table) {
128                Vector<Table> vector = new Vector<Table>();
129                for (int iIndex = 0; iIndex < this.tables.size(); ++iIndex) {
130                        Table tempTable = (Table) this.tables.get(iIndex);
131                        if (table.equals((Object) tempTable) || !tempTable.isRelationTable() || !tempTable.relationConnectsTo(table)
132                                        || vector.contains((Object) tempTable))
133                                continue;
134                        vector.add(tempTable);
135                }
136                return vector.toArray(new Table[vector.size()]);
137        }
138
139        public void load() throws SQLException, ClassNotFoundException {
140                
141                try {
142                        // 加载并注册JDBC驱动
143                        Class<?> driverClass = Class.forName(this.driver,true,CONFIG.getClassloader());
144                        DriverManager.registerDriver(
145                                        new InterfaceDecorator<Driver,Driver>(Driver.class,(Driver)driverClass.newInstance())
146                                                .proxyInstance());
147                } catch (ReflectiveOperationException e) {
148                        throw new RuntimeException(e);
149                }
150                System.out.println("Connecting to " + this.username + " on " + this.url + " ...");
151                this.pConnection = DriverManager.getConnection(this.url, this.username, this.password);
152                System.out.println("    Connected.");
153                try {
154                        // for oracle connection,set 'remarksReporting' flag to true for read comments
155                        // 连接为 oracle JDBC 时,调用 setRemarksReporting 设置'remarksReporting'为true,用于读取表中的注释内容
156                        Class<? extends Connection> connClass = pConnection.getClass();
157                        if(connClass.getName().equals("oracle.jdbc.driver.OracleConnection")){
158                                Method method = connClass.getMethod("setRemarksReporting", boolean.class);      
159                                if(null != method){
160                                        method.invoke(pConnection, true);
161                                }
162                        }                       
163                } catch (Exception e) {
164                        Throwables.throwIfUnchecked(e);
165                        throw new RuntimeException(e);
166                }
167                this.meta = this.pConnection.getMetaData();
168                this.engine = this.meta.getDatabaseProductName();
169                System.out.println("    Database server :" + this.engine + ".");
170                this.engine = new StringTokenizer(this.engine).nextToken();
171                this.tables = new Vector<Table>();
172                this.tableHash = new Hashtable<String,Table>();
173                this.loadTables();
174                this.initSamePrefix();
175                this.loadColumns();
176                this.loadPrimaryKeys();
177                this.loadImportedKeys();
178                this.loadIndexes();
179                this.loadProcedures();
180                this.sortElements();
181                this.pConnection.close();
182        }
183
184        public Table[] getTables() {
185                return this.tables.toArray(new Table[this.tables.size()]);
186        }
187
188        private void addTable(Table t) {
189                this.tables.addElement(t);
190                this.tableHash.put(t.getName(), t);
191        }
192
193        public Table getTable(String name) {
194                return (Table) this.tableHash.get(name);
195        }
196
197        private void loadTables() throws SQLException {
198                System.out.println("Loading table list according to pattern " + this.tablenamepattern + " ...");
199                StringTokenizer st = new StringTokenizer(this.tablenamepattern, ",; \t");
200                while (st.hasMoreTokens()) {
201                        String pattern = st.nextToken().trim();
202                        String tableSchema = this.schema;
203                        int index = pattern.indexOf(46);
204                        if (index > 0) {
205                                tableSchema = pattern.substring(0, index);
206                                pattern = pattern.substring(index + 1);
207                        }
208                        ResultSet resultSet = this.meta.getTables(this.catalog, tableSchema, pattern, this.tableTypes);
209                        while (resultSet.next()) {
210                                Table table = new Table();
211                                table.setCatalog(resultSet.getString("TABLE_CAT"));
212                                table.setSchema(resultSet.getString("TABLE_SCHEM"));
213                                table.setName(resultSet.getString("TABLE_NAME"));
214                                table.setType(resultSet.getString("TABLE_TYPE"));
215                                table.setRemarks(resultSet.getString("REMARKS"));
216                                table.setDatabase(this);
217                                if (!CodeWriter.authorizeProcess((String) table.getName(), (String) "tables.include",
218                                                (String) "tables.exclude"))
219                                        continue;
220                                this.addTable(table);
221                                System.out.println("    table " + table.getName() + " found");
222                        }
223                        resultSet.close();
224                }
225        }
226
227        private void loadColumns() throws SQLException {
228                System.out.println("Loading columns ...");
229                Iterator<Table> it = this.tables.iterator();
230                while (it.hasNext()) {
231                        Table table = (Table) it.next();
232                        Column c = null;
233                        ResultSet resultSet = this.meta.getColumns(table.getCatalog(), table.getSchema(), table.getName(), "%");
234                        while (resultSet.next()) {
235                                c = new Column();
236                                c.setDatabase(this);
237                                c.setCatalog(resultSet.getString("TABLE_CAT"));
238                                c.setSchema(resultSet.getString("TABLE_SCHEM"));
239                                c.setTableName(resultSet.getString("TABLE_NAME"));
240                                c.setName(resultSet.getString("COLUMN_NAME"));
241                                c.setType(resultSet.getShort("DATA_TYPE"));
242                                c.setTypeName(resultSet.getString("TYPE_NAME"));
243                                c.setSize(resultSet.getInt("COLUMN_SIZE"));
244                                c.setDecimalDigits(resultSet.getInt("DECIMAL_DIGITS"));
245                                c.setRadix(resultSet.getInt("NUM_PREC_RADIX"));
246                                c.setNullable(resultSet.getInt("NULLABLE"));
247                                c.setRemarks(resultSet.getString("REMARKS"));
248                                c.setDefaultValue(resultSet.getString("COLUMN_DEF"));
249                                c.setOrdinalPosition(resultSet.getInt("ORDINAL_POSITION"));
250                                c.setAutoincrement(resultSet.getString("IS_AUTOINCREMENT"));
251                                System.out.printf("        %s %s %s default value: %s\n", c.getFullName(),c.getTypeName(),c.isAutoincrement()?"AUTOINCREMENT":"",c.getOriginalDefaultValue());
252                                table.addColumn(c);
253                                if(c.isAutoincrement())
254                                        table.setAutoincrement(c);
255                        }
256                        resultSet.close();
257                        System.out.println("    " + table.getName() + " found " + table.countColumns() + " columns");
258                }
259        }
260
261        private void loadPrimaryKeys() throws SQLException {
262                System.out.println("Database::loadPrimaryKeys");
263                Iterator<Table> it = this.tables.iterator();
264                while (it.hasNext()) {
265                        Column col;
266                        Table table = (Table) it.next();
267                        TreeMap<String, Column> map = new TreeMap<String, Column>();
268                        ResultSet pResultSet = this.meta.getPrimaryKeys(table.getCatalog(), table.getSchema(), table.getName());
269                        while (pResultSet.next()) {
270                                String colName = pResultSet.getString("COLUMN_NAME");
271                                short seq = pResultSet.getShort("KEY_SEQ");
272                                System.out.println("Found primary key (seq,name) (" + seq + "," + colName + ") for table '"
273                                                + table.getName() + "'");
274                                col = table.getColumn(colName);
275                                if (col == null)
276                                        continue;
277                                map.put(String.valueOf(seq), col);
278                        }
279                        pResultSet.close();
280                        int size = map.size();
281                        for (int k = 1; k <= size; ++k) {
282                                col = (Column) map.get(String.valueOf(k));
283                                table.addPrimaryKey(col);
284                        }
285                }
286        }
287
288        private void loadImportedKeys() throws SQLException {
289                System.out.println("Loading imported keys ...");
290                Iterator<Table> it = this.tables.iterator();
291                while (it.hasNext()) {
292                        ResultSet resultSet;
293                        Table table = (Table) it.next();
294                        try {
295                                resultSet = this.meta.getImportedKeys(table.getCatalog(), table.getSchema(), table.getName());
296                        } catch (SQLException sqle) {
297                                System.out.println("    Error while loading imported keys for table " + table.getName());
298                                continue;
299                        }
300                        while (resultSet.next()) {
301                                String tabName = resultSet.getString("FKTABLE_NAME");
302                                String colName = resultSet.getString("FKCOLUMN_NAME");
303                                String foreignTabName = resultSet.getString("PKTABLE_NAME");
304                                String foreignColName = resultSet.getString("PKCOLUMN_NAME");
305                                String foreignKeyName = resultSet.getString("FK_NAME");
306                                short updateRule = resultSet.getShort("UPDATE_RULE");
307                                short deleteRule = resultSet.getShort("DELETE_RULE");
308                                if(Strings.isNullOrEmpty(foreignKeyName)){
309                                        Vector<Column> primaryKeys = this.getTable(tabName).getPrimaryKeysAsList();
310                                        checkState(!primaryKeys.isEmpty());
311                                        // make a fake name
312                                        String combinName = Joiner.on('_').join(Iterators.transform(primaryKeys.iterator(), new Function<Column,String>(){
313                                                @Override
314                                                public String apply(Column input) {
315                                                        return input.getName();
316                                                }}));
317                                        foreignKeyName="fk_"+ tabName + "_" +combinName;
318                                        System.out.println("WARN: FK_NAME return empty,the generated code  may be incorrected.");
319                                }
320                                        
321                                short seq = resultSet.getShort("KEY_SEQ");
322                                Column col = this.getTable(tabName).getColumn(colName);
323                                Table foreignTable = this.getTable(foreignTabName);
324                                if (null == foreignTable)
325                                        continue;
326                                Column foreignCol = foreignTable.getColumn(foreignColName);
327                                col.addForeignKey(foreignCol, foreignKeyName, seq, 
328                                                                Table.ForeignKeyRule.values()[updateRule],
329                                                                Table.ForeignKeyRule.values()[deleteRule]);
330                                foreignCol.addImportedKey(col);
331                                System.out.println("    " + col.getFullName() + " -> " + foreignCol.getFullName() + " found seq:"+ seq+" foreign key name:"+ foreignKeyName);
332                                System.out.println("    UPDATE_RULE:" + Table.ForeignKeyRule.values()[updateRule].name() 
333                                                         + "    DELETE_RULE:" + Table.ForeignKeyRule.values()[deleteRule].name());
334                        }
335                        resultSet.close();
336                }
337        }
338
339        private void loadIndexes() throws SQLException {
340                System.out.println("Loading indexes ...");
341                Iterator<Table> it = this.tables.iterator();
342                while (it.hasNext()) {
343                        Table table = (Table) it.next();
344                        ResultSet resultSet = null;
345                        try {
346                                resultSet = this.meta.getIndexInfo(table.getCatalog(), table.getSchema(), table.getName(), false, true);
347                        } catch (SQLException sqle) {
348                                System.out.println("    Error while loading indexes for table " + table.getName());
349                                continue;
350                        }
351                        String currentName = "";
352                        Index index = null;
353                        while (resultSet.next()) {
354                                Column col;
355                                String colName = resultSet.getString("COLUMN_NAME");
356                                String indName = resultSet.getString("INDEX_NAME");
357                                if (null == indName || null == colName || (col = table.getColumn(colName)).isPrimaryKey())
358                                        continue;
359                                if (!currentName.equals(indName)) {
360                                        index = new Index(indName, table);
361                                        index.setUnique(!resultSet.getBoolean("NON_UNIQUE"));
362                                        currentName = indName;
363                                }
364                                System.out.println(
365                                                "  Found interesting index " + indName + (index.isUnique()?"(UNIQUE)": "") + " on " + colName + " for table " + table.getName() );
366
367                                IndexColumn column = new IndexColumn();
368                                column.setName(resultSet.getString("COLUMN_NAME"));
369                                column.setOrdinalPosition((int) resultSet.getShort("ORDINAL_POSITION"));
370                                column.setSortSequence(resultSet.getString("ASC_OR_DESC"));
371                                column.setFilterCondition(resultSet.getString("FILTER_CONDITION"));
372                                column.setType(col.getType());
373                                column.setRemarks(col.getRemarks());
374                                column.setTableName(col.getTableName());
375                                column.setDatabase(this);
376                                index.addIndexColumn(column);
377                        }
378                        resultSet.close();
379                }
380        }
381
382        private void loadProcedures() throws SQLException {
383                System.out.println("Loading procedures ...");
384                Iterator<Table> it = this.tables.iterator();
385                while (it.hasNext()) {
386                        Table table = (Table) it.next();
387                        String procedurePattern = "%" + table.getName() + "%";
388                        ResultSet resultSet = null;
389                        try {
390                                resultSet = this.meta.getProcedures(table.getCatalog(), table.getSchema(), procedurePattern);
391                        } catch (SQLException sqle) {
392                                System.out.println("    Error while loading procedures for table " + table.getName());
393                                continue;
394                        }
395                        while (resultSet.next()) {
396                                String spName = resultSet.getString("PROCEDURE_NAME");
397                                String spRemarks = resultSet.getString("REMARKS");
398                                Procedure procedure = new Procedure();
399                                procedure.setName(spName);
400                                procedure.setRemarks(spRemarks);
401                                procedure.setReturnType("void");
402                                table.addProcedure(procedure);
403                                System.out.println("    Found procedure " + spName + " for table " + table.getName());
404                                ResultSet rs = this.meta.getProcedureColumns(this.catalog, this.schema, spName, null);
405                                while (rs.next()) {
406                                        String colName = rs.getString("COLUMN_NAME");
407                                        short columnType = rs.getShort("COLUMN_TYPE");
408                                        if (DatabaseMetaData.procedureColumnUnknown == columnType) {
409                                                System.err.println("    Column " + colName + " of unknown type in procedure " + spName);
410                                                continue;
411                                        }
412                                        Column c = new Column();
413                                        c.setType(rs.getShort("DATA_TYPE"));
414                                        if (5 == columnType) {
415                                                procedure.setReturnType(c.getJavaType());
416                                                continue;
417                                        }
418                                        c.setDatabase(this);
419                                        c.setCatalog(rs.getString("PROCEDURE_CAT"));
420                                        c.setSchema(rs.getString("PROCEDURE_SCHEM"));
421                                        c.setTableName(rs.getString("PROCEDURE_NAME"));
422                                        c.setName(colName);
423                                        c.setSize(rs.getInt("LENGTH"));
424                                        c.setDecimalDigits(rs.getInt("SCALE"));
425                                        c.setRadix(rs.getInt("RADIX"));
426                                        c.setNullable(rs.getInt("NULLABLE"));
427                                        c.setRemarks(rs.getString("REMARKS"));
428                                        switch (columnType) {
429                                                case DatabaseMetaData.procedureColumnIn : {
430                                                        procedure.addInColumn(c);
431                                                        continue ;
432                                                }
433                                                case DatabaseMetaData.procedureColumnInOut : {
434                                                        procedure.addInOutColumn(c);
435                                                        continue ;
436                                                }
437                                                case DatabaseMetaData.procedureColumnOut : {
438                                                        procedure.addOutColumn(c);
439                                                        continue ;
440                                                }
441                                        }
442                                        procedure.setReturnType("List");
443                                }
444                                rs.close();
445                        }
446                        resultSet.close();
447                }
448        }
449
450        public String[] getAllPackages() {
451                Vector<String> vector = new Vector<String>();
452                for (int iIndex = 0; iIndex < this.tables.size(); ++iIndex) {
453                        Table table = (Table) this.tables.get(iIndex);
454                        if (vector.contains(table.getPackage()))
455                                continue;
456                        vector.add(table.getPackage());
457                }
458                return vector.toArray(new String[vector.size()]);
459        }
460        /**
461         * sort foreign keys and Import keys of all column
462         */
463        private void sortElements(){
464                for(Table table:this.tables){
465                        for(Column column:table.getColumns()){
466                                Collections.sort(column.getForeignKeys());
467                                Collections.sort(column.getImportedKeys());
468                        }
469                }
470        }
471        
472        public String getSamePrefix()  {                
473                return this.samePrefix;
474        }       
475        /**
476         * 计算所有表名的共同前缀
477         */
478        private void initSamePrefix()  {
479                int index=-1;
480                if(0==this.tables.size())return;
481                String first=this.tables.get(0).getName();
482                try{
483                        for(int i=0;i<first.length();++i){
484                                for(int j=1;j<this.tables.size();++j){
485                                        String c=this.tables.get(j).getName();
486                                        if(c.charAt(i)!=first.charAt(i))
487                                                throw new IndexOutOfBoundsException();
488                                }
489                                index=i;
490                        }
491                }catch(IndexOutOfBoundsException e){                    
492                }
493                this.samePrefix= index<0?"":first.substring(0, index+1);
494                System.out.printf("samePrefix = [%s]\n", this.samePrefix);
495        }
496
497        public List<Table> getJunctionTables() {
498                Table[] tabs = this.getTables();
499                ArrayList<Table> tables = new ArrayList<Table>(tabs.length);
500                for (int i = 0; i < tabs.length; ++i) {
501                        if (!tabs[i].isJunctionTable())
502                                continue;
503                        tables.add(tabs[i]);
504                }
505                return tables;
506        }
507        public List<Table> getTablesWithPk(){
508                return Lists.newArrayList(Collections2.filter(ImmutableList.copyOf(getTables()), new Predicate<Table>(){
509                        @Override
510                        public boolean apply(Table input) {
511                                return input.hasPrimaryKey();
512                        }}));
513        }
514        /**
515         * 返回主键字段数目为指定值的表
516         * @return
517         */
518        public List<Table> getTablesWithPk(final Integer pkLimit){
519                return Lists.newArrayList(Collections2.filter(ImmutableList.copyOf(getTables()), new Predicate<Table>(){
520                        @Override
521                        public boolean apply(Table input) {
522                                return input.countPrimaryKeys() == pkLimit;
523                        }}));
524        }
525}