Java DatabaseMetaData.getColumns方法代码示例

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#61332 - Check if "LIKE" or "=" is sent
 * to server in I__S query when no wildcards are supplied
 * for schema parameter.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug61332() throws Exception {
    Properties props = new Properties();
    props.setProperty("useInformationSchema", "true");
    props.setProperty("statementInterceptors", StatementInterceptorBug61332.class.getName());

    createDatabase("dbbug61332");
    Connection testConn = getConnectionWithProps(props);

    if (versionMeetsMinimum(5, 0, 7)) {
        try {
            createTable("dbbug61332.bug61332", "(c1 char(1))");
            DatabaseMetaData metaData = testConn.getMetaData();

            this.rs = metaData.getColumns("dbbug61332", null, "bug61332", null);
            this.rs.next();
        } finally {
        }
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests whether or not unsigned columns are reported correctly in
 * DBMD.getColumns
 * 
 * @throws Exception
 */
public void testGetColumnsUnsigned() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetUnsignedCols");
        this.stmt.executeUpdate("CREATE TABLE testGetUnsignedCols (field1 BIGINT, field2 BIGINT UNSIGNED)");

        DatabaseMetaData dbmd = this.conn.getMetaData();

        this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testGetUnsignedCols", "%");

        assertTrue(this.rs.next());
        // This row doesn't have 'unsigned' attribute
        assertTrue(this.rs.next());
        assertTrue(this.rs.getString(6).toLowerCase().indexOf("unsigned") != -1);
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetUnsignedCols");
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests the implementation of Information Schema for columns.
 */
public void testGetColumnsUsingInfoSchema() throws Exception {
    if (versionMeetsMinimum(5, 0, 7)) {
        createTable("t1", "(c1 char(1))");
        Properties props = new Properties();
        props.put("useInformationSchema", "true");
        Connection conn1 = null;
        try {
            conn1 = getConnectionWithProps(props);
            DatabaseMetaData metaData = conn1.getMetaData();
            this.rs = metaData.getColumns(null, null, "t1", null);
            this.rs.next();
            assertEquals("t1", this.rs.getString("TABLE_NAME"));
            assertEquals("c1", this.rs.getString("COLUMN_NAME"));
            assertEquals("CHAR", this.rs.getString("TYPE_NAME"));
            assertEquals("1", this.rs.getString("COLUMN_SIZE"));
        } finally {
            if (conn1 != null) {
                conn1.close();
            }
        }
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public void testBug31187() throws Exception {
    createTable("testBug31187", "(field1 int)");

    Connection nullCatConn = getConnectionWithProps("nullCatalogMeansCurrent=false");
    DatabaseMetaData dbmd = nullCatConn.getMetaData();
    ResultSet dbTblCols = dbmd.getColumns(null, null, "testBug31187", "%");

    boolean found = false;

    while (dbTblCols.next()) {
        String catalog = dbTblCols.getString("TABLE_CAT");
        String table = dbTblCols.getString("TABLE_NAME");
        boolean useLowerCaseTableNames = dbmd.storesLowerCaseIdentifiers();

        if (catalog.equals(nullCatConn.getCatalog())
                && (((useLowerCaseTableNames && "testBug31187".equalsIgnoreCase(table)) || "testBug31187".equals(table)))) {
            found = true;
        }
    }

    assertTrue("Didn't find any columns for table named 'testBug31187' in database " + this.conn.getCatalog(), found);
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
private void checkMetadataForBug22613(Connection c) throws Exception {
    String maxValue = "a,bc,def,ghij";
    String maxValue2 = "1,2,3,4,1585,ONE,TWO,Y,N,THREE";

    DatabaseMetaData meta = c.getMetaData();
    this.rs = meta.getColumns(null, this.conn.getCatalog(), "bug22613", "s");
    this.rs.first();

    assertEquals(maxValue.length(), this.rs.getInt("COLUMN_SIZE"));

    this.rs = meta.getColumns(null, this.conn.getCatalog(), "bug22613", "s2");
    this.rs.first();

    assertEquals(maxValue2.length(), this.rs.getInt("COLUMN_SIZE"));

    this.rs = meta.getColumns(null, c.getCatalog(), "bug22613", "t");
    this.rs.first();

    assertEquals(4, this.rs.getInt("COLUMN_SIZE"));
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests bug reported by OpenOffice team with getColumns and LONGBLOB
 * 
 * @throws Exception
 *             if any errors occur
 */
public void testGetColumns() throws Exception {
    try {
        this.stmt.execute("CREATE TABLE IF NOT EXISTS longblob_regress(field_1 longblob)");

        DatabaseMetaData dbmd = this.conn.getMetaData();
        ResultSet dbmdRs = null;

        try {
            dbmdRs = dbmd.getColumns("", "", "longblob_regress", "%");

            while (dbmdRs.next()) {
                dbmdRs.getInt(7);
            }
        } finally {
            if (dbmdRs != null) {
                try {
                    dbmdRs.close();
                } catch (SQLException ex) {
                }
            }
        }
    } finally {
        this.stmt.execute("DROP TABLE IF EXISTS longblob_regress");
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Fixed BUG#27915 - DatabaseMetaData.getColumns() doesn't contain SCOPE_*
 * or IS_AUTOINCREMENT columns.
 * 
 * @throws Exception
 */
public void testBug27915() throws Exception {
    createTable("testBug27915", "(field1 int not null primary key auto_increment, field2 int)");
    DatabaseMetaData dbmd = this.conn.getMetaData();

    this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testBug27915", "%");
    this.rs.next();

    checkBug27915();

    if (versionMeetsMinimum(5, 0)) {
        this.rs = getConnectionWithProps("useInformationSchema=true").getMetaData().getColumns(this.conn.getCatalog(), null, "testBug27915", "%");
        this.rs.next();

        checkBug27915();
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
private String getColumnRemarks(String tableName,String columnName,DatabaseMetaData databaseMetaData) throws SQLException{
	String remarks=null;
	ResultSet rs=null;
	try{
		rs=databaseMetaData.getColumns(null, null, tableName,"%");
		while(rs.next()){
			String colName=rs.getString("COLUMN_NAME");
			if(columnName.equals(colName)){
				remarks=rs.getString("REMARKS");
				break;
			}
		}
	}finally{
		if(rs!=null)rs.close();
	}
	return remarks;
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public void testQuotedGunk() throws Exception {
    createTable("testQuotedGunk", "(field1 int)");

    String quotedCatalog = "`" + this.conn.getCatalog() + "`";
    String unquotedCatalog = this.conn.getCatalog();

    DatabaseMetaData dbmd = this.conn.getMetaData();
    this.rs = dbmd.getTables(quotedCatalog, null, "testQuotedGunk", new String[] { "TABLE" });
    assertTrue(this.rs.next());
    this.rs = dbmd.getTables(unquotedCatalog, null, "testQuotedGunk", new String[] { "TABLE" });
    assertTrue(this.rs.next());
    this.rs = dbmd.getColumns(quotedCatalog, null, "testQuotedGunk", "field1");
    assertTrue(this.rs.next());
    this.rs = dbmd.getColumns(unquotedCatalog, null, "testQuotedGunk", "field1");
    assertTrue(this.rs.next());

}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#1673, where DatabaseMetaData.getColumns() is not
 * returning correct column ordinal info for non '%' column name patterns.
 * 
 * @throws Exception
 *             if the test fails for any reason
 */
public void testFixForBug1673() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1673");
        this.stmt.executeUpdate("CREATE TABLE testBug1673 (field_1 INT, field_2 INT)");

        DatabaseMetaData dbmd = this.conn.getMetaData();

        int ordinalPosOfCol2Full = 0;

        this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testBug1673", null);

        while (this.rs.next()) {
            if (this.rs.getString(4).equals("field_2")) {
                ordinalPosOfCol2Full = this.rs.getInt(17);
            }
        }

        int ordinalPosOfCol2Scoped = 0;

        this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testBug1673", "field_2");

        while (this.rs.next()) {
            if (this.rs.getString(4).equals("field_2")) {
                ordinalPosOfCol2Scoped = this.rs.getInt(17);
            }
        }

        assertTrue("Ordinal position in full column list of '" + ordinalPosOfCol2Full + "' != ordinal position in pattern search, '"
                + ordinalPosOfCol2Scoped + "'.",
                (ordinalPosOfCol2Full != 0) && (ordinalPosOfCol2Scoped != 0) && (ordinalPosOfCol2Scoped == ordinalPosOfCol2Full));
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1673");
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public void getColumns(DatabaseMetaData metaData, String databaseName, String schemaName, String tableName) throws SQLException {
	List<Column> columns = new ArrayList<>();

	try (ResultSet result = metaData.getColumns(databaseName, schemaName, tableName, null)) {
		while (result.next()) {
			Column column = new Column(tableName, result.getString("COLUMN_NAME"), this);
			column.setDataType(result.getInt("DATA_TYPE"));
			// We map some of unique MSSQL and Oracle data types to common data types to simplify subsequent analysis.
			// The mapping should be done in the vendor specific classes.
			String typeName = result.getString("TYPE_NAME");
			if (typeName.contains("VARCHAR")) { // Covers NVARCHAR and NVARCHAR2. By default these would be mapped to OTHER.
				column.setDataType(12);
			}
			if ("NCHAR".equals(typeName)) { // Covers NCHAR. By default this would be mapped to OTHER.
				column.setDataType(1);
			}
			column.setDataTypeName(JDBCType.valueOf(column.getDataType()).toString());
			column.setNullable(result.getBoolean("NULLABLE"));
			column.setColumnSize(result.getInt("COLUMN_SIZE"));
			column.setDecimalDigits(result.getInt("DECIMAL_DIGITS"));
			column.setHasDefault(result.getString("COLUMN_DEF") != null);
			column.setOrdinalPosition(result.getInt("ORDINAL_POSITION"));
			column.setAutoincrement("YES".equals(result.getString("IS_AUTOINCREMENT")));
			columns.add(column);
		}
	}

	columnList = setColumnCounts(columns);
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
private void checkColumns(List<Column> col) {
    try {
	    DatabaseMetaData metadata = connection.getMetaData();
		ResultSet resultSet = metadata.getColumns(null, null, getTableName(), null);
		List<Column>missing = new ArrayList<Column>(col);
		while(resultSet.next()) {
			String name = resultSet.getString("COLUMN_NAME");
			Column current = getColumn(missing, name);
			if(current == null)continue;
			int id = resultSet.getInt("ORDINAL_POSITION");
			if(weak)id++;
			current.setId(id);
			missing.remove(current);
			String type = resultSet.getString("TYPE_NAME");/*
			if(current.getType() == Type.DOUBLE) {
				Integer size = resultSet.getInt("COLUMN_SIZE");
				Integer decimal = resultSet.getInt("DECIMAL_DIGITS");
				if(size != null && size != 0 && decimal != null)
					type += ("(" + size + "," + decimal + ")");
			}*/
			//System.out.println(resultSet.getString("COLUMN_NAME")+ " => " + type + " :: " + resultSet.getInt("ORDINAL_POSITION"));
			//System.out.println(type + "  " + typeToString(current.getType()));
			if(!type.equalsIgnoreCase(typeToString(current.getType())))
				ErrorLogger.addError("SQLite ALTER TABLE don't exist, can't edit " + current.getName());
		}
		if(!missing.isEmpty()) {
			Alter a = alter();
			for(Column c : missing)
				a.add(c);
			a.execute();
			//if(!missing.isEmpty())
				//checkColumns(missing);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public  String getJSONStrDataBase(String dbName,String CommonNodeNameKey,String TablesChildrenNodeNamekey,String columnsChildrenNodeNameKey){
	StringBuilder dbStr = new StringBuilder();
	StringBuilder tableStr = new StringBuilder();
	StringBuilder columnStr = new StringBuilder();
	
	ResultSet tableRS = null;
	ResultSet columnRS = null;
	
	boolean tableFlag = false;
	boolean columnFlag = false;
	
	if(con != null){
		try {
			DatabaseMetaData dbMetaData = con.getMetaData();
			dbStr.append("{\""+CommonNodeNameKey+"\":");
			dbStr.append("\""+dbName+"\",\""+TablesChildrenNodeNamekey+"\":[");
			
			tableRS = dbMetaData.getTables(dbName, null, null, new String[]{"TABLE","VIEW"});
			while(tableRS.next()){
				tableFlag = true;
				tableStr.append("{\""+CommonNodeNameKey+"\":");
				tableStr.append("\""+tableRS.getString("TABLE_NAME")+"\",\""+columnsChildrenNodeNameKey+"\":[");
			
				columnRS = dbMetaData.getColumns(dbName, null, tableRS.getString("TABLE_NAME"), null);
				while(columnRS.next()){
					columnFlag = true;
					columnStr.append("{\""+CommonNodeNameKey+"\":");
					columnStr.append("\""+columnRS.getString("COLUMN_NAME")+"\""+",");
					columnStr.append("\"type\":");
					columnStr.append("\""+columnRS.getString("TYPE_NAME")+"\""+",");
					columnStr.append("\"size\":");
					columnStr.append(columnRS.getString("COLUMN_SIZE"));
					columnStr.append("},");
				}
				if(columnFlag){
					columnFlag = false;
					columnStr = columnStr.replace(columnStr.length()-1, columnStr.length(), "");
					tableStr.append(columnStr);
					columnStr = new StringBuilder();
				}
				tableStr.append("]");
				tableStr.append("},");
			}
			dbStr.append(tableStr);
			if(tableFlag)
				dbStr = dbStr.replace(dbStr.length()-1, dbStr.length(), "");
			dbStr.append("]}");	
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(columnRS,null,null);
			close(tableRS,null,null);
		}
	}
	return dbStr.toString();
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#23304 - DBMD using "show" and DBMD using
 * information_schema do not return results consistent with eachother.
 * 
 * (note this fix only addresses the inconsistencies, not the issue that the
 * driver is treating schemas differently than some users expect.
 * 
 * We will revisit this behavior when there is full support for schemas in
 * MySQL).
 * 
 * @throws Exception
 */
public void testBug23304() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    Connection connShow = null;
    Connection connInfoSchema = null;

    ResultSet rsShow = null;
    ResultSet rsInfoSchema = null;

    try {
        Properties noInfoSchemaProps = new Properties();
        noInfoSchemaProps.setProperty("useInformationSchema", "false");

        Properties infoSchemaProps = new Properties();
        infoSchemaProps.setProperty("useInformationSchema", "true");
        infoSchemaProps.setProperty("dumpQueriesOnException", "true");

        connShow = getConnectionWithProps(noInfoSchemaProps);
        connInfoSchema = getConnectionWithProps(infoSchemaProps);

        DatabaseMetaData dbmdUsingShow = connShow.getMetaData();
        DatabaseMetaData dbmdUsingInfoSchema = connInfoSchema.getMetaData();

        assertNotSame(dbmdUsingShow.getClass(), dbmdUsingInfoSchema.getClass());

        rsShow = dbmdUsingShow.getSchemas();
        rsInfoSchema = dbmdUsingInfoSchema.getSchemas();

        compareResultSets(rsShow, rsInfoSchema);

        /*
         * rsShow = dbmdUsingShow.getTables(connShow.getCatalog(), null,
         * "%", new String[] {"TABLE", "VIEW"}); rsInfoSchema =
         * dbmdUsingInfoSchema.getTables(connInfoSchema.getCatalog(), null,
         * "%", new String[] {"TABLE", "VIEW"});
         * 
         * compareResultSets(rsShow, rsInfoSchema);
         * 
         * rsShow = dbmdUsingShow.getTables(null, null, "%", new String[]
         * {"TABLE", "VIEW"}); rsInfoSchema =
         * dbmdUsingInfoSchema.getTables(null, null, "%", new String[]
         * {"TABLE", "VIEW"});
         * 
         * compareResultSets(rsShow, rsInfoSchema);
         */

        createTable("t_testBug23304",
                "(field1 int primary key not null, field2 tinyint, field3 mediumint, field4 mediumint, field5 bigint, field6 float, field7 double, field8 decimal, field9 char(32), field10 varchar(32), field11 blob, field12 mediumblob, field13 longblob, field14 text, field15 mediumtext, field16 longtext, field17 date, field18 time, field19 datetime, field20 timestamp)");

        rsShow = dbmdUsingShow.getColumns(connShow.getCatalog(), null, "t_testBug23304", "%");
        rsInfoSchema = dbmdUsingInfoSchema.getColumns(connInfoSchema.getCatalog(), null, "t_testBug23304", "%");

        compareResultSets(rsShow, rsInfoSchema);
    } finally {
        if (rsShow != null) {
            rsShow.close();
        }

        if (rsInfoSchema != null) {
            rsInfoSchema.close();
        }
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#
 * 
 * @throws Exception
 *             if an error occurs
 */
public void testGetColumnsBug1099() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetColumnsBug1099");

        DatabaseMetaData dbmd = this.conn.getMetaData();

        this.rs = dbmd.getTypeInfo();

        StringBuilder types = new StringBuilder();

        HashMap<String, String> alreadyDoneTypes = new HashMap<String, String>();

        while (this.rs.next()) {
            String typeName = this.rs.getString("TYPE_NAME");
            //String createParams = this.rs.getString("CREATE_PARAMS");

            if ((typeName.indexOf("BINARY") == -1) && !typeName.equals("LONG VARCHAR")) {
                if (!alreadyDoneTypes.containsKey(typeName)) {
                    alreadyDoneTypes.put(typeName, null);

                    if (types.length() != 0) {
                        types.append(", \n");
                    }

                    int typeNameLength = typeName.length();
                    StringBuilder safeTypeName = new StringBuilder(typeNameLength);

                    for (int i = 0; i < typeNameLength; i++) {
                        char c = typeName.charAt(i);

                        if (Character.isWhitespace(c)) {
                            safeTypeName.append("_");
                        } else {
                            safeTypeName.append(c);
                        }
                    }

                    types.append(safeTypeName.toString());
                    types.append("Column ");
                    types.append(typeName);

                    if (typeName.indexOf("CHAR") != -1) {
                        types.append(" (1)");
                    } else if (typeName.equalsIgnoreCase("enum") || typeName.equalsIgnoreCase("set")) {
                        types.append("('a', 'b', 'c')");
                    }
                }
            }
        }

        this.stmt.executeUpdate("CREATE TABLE testGetColumnsBug1099(" + types.toString() + ")");

        dbmd.getColumns(null, this.conn.getCatalog(), "testGetColumnsBug1099", "%");
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetColumnsBug1099");
    }
}