List of usage examples for java.sql DatabaseMetaData getColumns
ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException;
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); DatabaseMetaData md = conn.getMetaData(); ResultSet r = md.getColumns("", "", "yourTableName", "%"); while (r.next()) System.out.println("\t" + r.getString(4) + " : " + r.getString(6)); conn.close();/*from w ww .java 2s. com*/ }
From source file:Main.java
public static void main(String[] args) throws Exception { Class.forName(DRIVER);//from w w w .ja v a 2 s. co m Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); DatabaseMetaData metadata = connection.getMetaData(); ResultSet resultSet = metadata.getColumns(null, null, "users", null); while (resultSet.next()) { String name = resultSet.getString("COLUMN_NAME"); String type = resultSet.getString("TYPE_NAME"); int size = resultSet.getInt("COLUMN_SIZE"); System.out.println("Column name: [" + name + "]; type: [" + type + "]; size: [" + size + "]"); } connection.close(); }
From source file:Main.java
public static void main(String[] argv) throws Exception { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root"); Statement st = con.createStatement(); ResultSet rs = st.executeQuery("DESCRIBE myTable"); ResultSetMetaData md = rs.getMetaData(); int col = md.getColumnCount(); for (int i = 1; i <= col; i++) { String col_name = md.getColumnName(i); System.out.println(col_name); }/*from w w w . j a v a2 s . c o m*/ DatabaseMetaData dbm = con.getMetaData(); ResultSet rs1 = dbm.getColumns(null, "%", "myTable", "%"); while (rs1.next()) { String col_name = rs1.getString("COLUMN_NAME"); String data_type = rs1.getString("TYPE_NAME"); int data_size = rs1.getInt("COLUMN_SIZE"); int nullable = rs1.getInt("NULLABLE"); System.out.println(col_name + " " + data_type + "(" + data_size + ")"); if (nullable == 1) { System.out.println("YES"); } else { System.out.println("NO"); } } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getMySqlConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("drop table survey;"); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); ResultSet rsColumns = null;/*from ww w .ja v a2 s .c o m*/ DatabaseMetaData meta = conn.getMetaData(); rsColumns = meta.getColumns(null, null, "survey", null); while (rsColumns.next()) { System.out.println(rsColumns.getString("TYPE_NAME")); System.out.println(rsColumns.getString("COLUMN_NAME")); } st.close(); conn.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getMySqlConnection(); System.out.println("Got Connection."); ResultSet rsColumns = null;/*from w w w.ja v a2 s. com*/ DatabaseMetaData meta = conn.getMetaData(); rsColumns = meta.getColumns(null, "%", "code", "%"); while (rsColumns.next()) { String columnType = rsColumns.getString("TYPE_NAME"); String columnName = rsColumns.getString("COLUMN_NAME"); int size = rsColumns.getInt("COLUMN_SIZE"); int nullable = rsColumns.getInt("NULLABLE"); int position = rsColumns.getInt("ORDINAL_POSITION"); System.out.println("column name=" + columnName); System.out.println("type=" + columnType); System.out.println("size=" + size); if (nullable == DatabaseMetaData.columnNullable) { System.out.println("nullable is true"); } else { System.out.println("nullable is false"); } System.out.println("position" + position); } conn.close(); }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getMySqlConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("drop table survey;"); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); ResultSet rsColumns = null;//from ww w .ja v a2 s . co m DatabaseMetaData meta = conn.getMetaData(); rsColumns = meta.getColumns(null, null, "survey", null); while (rsColumns.next()) { String columnName = rsColumns.getString("COLUMN_NAME"); System.out.println("column name=" + columnName); String columnType = rsColumns.getString("TYPE_NAME"); System.out.println("type:" + columnType); int size = rsColumns.getInt("COLUMN_SIZE"); System.out.println("size:" + size); int nullable = rsColumns.getInt("NULLABLE"); if (nullable == DatabaseMetaData.columnNullable) { System.out.println("nullable true"); } else { System.out.println("nullable false"); } int position = rsColumns.getInt("ORDINAL_POSITION"); System.out.println("position:" + position); } st.close(); conn.close(); }
From source file:org.apache.hadoop.chukwa.database.MetricsAggregation.java
/** * @param args/*from w w w .j a v a 2s.c om*/ * @throws SQLException */ public static void main(String[] args) throws SQLException { mdlConfig = new DatabaseConfig(); // Connect to the database String jdbc_url = System.getenv("JDBC_URL_PREFIX") + mdlConfig.get("jdbc.host") + "/" + mdlConfig.get("jdbc.db"); if (mdlConfig.get("jdbc.user") != null) { jdbc_url = jdbc_url + "?user=" + mdlConfig.get("jdbc.user"); if (mdlConfig.get("jdbc.password") != null) { jdbc_url = jdbc_url + "&password=" + mdlConfig.get("jdbc.password"); } } try { // The newInstance() call is a work around for some // broken Java implementations org.apache.hadoop.chukwa.util.DriverManagerUtil.loadDriver().newInstance(); log.info("Initialized JDBC URL: " + jdbc_url); } catch (Exception ex) { // handle the error ex.printStackTrace(); log.error(ex, ex); } try { conn = org.apache.hadoop.chukwa.util.DriverManagerUtil.getConnection(jdbc_url); } catch (SQLException ex) { ex.printStackTrace(); log.error(ex, ex); } // get the latest timestamp for aggregation on this table // Start = latest SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); long start = System.currentTimeMillis() - (1000 * 60 * 60 * 24); long end = System.currentTimeMillis() - (1000 * 60 * 10); // retrieve metadata for cluster_system_metrics DatabaseConfig dbConf = new DatabaseConfig(); String[] tables = dbConf.findTableName("cluster_system_metrics_2018_week", start, end); for (String table : tables) { System.out.println("Table to aggregate per Ts: " + table); stmt = conn.createStatement(); rs = stmt.executeQuery( "select table_ts from aggregation_admin_table where table_name=\"" + table + "\""); if (rs.next()) { start = rs.getLong(1); } else { start = 0; } end = start + (1000 * 60 * 60 * 1); // do 1 hour aggregation max long now = System.currentTimeMillis(); now = now - (1000 * 60 * 10); // wait for 10 minutes end = Math.min(now, end); // TODO REMOVE DEBUG ONLY! end = now; System.out.println("Start Date:" + new Date(start)); System.out.println("End Date:" + new Date(end)); DatabaseMetaData dbm = conn.getMetaData(); rs = dbm.getColumns(null, null, table, null); List<String> cols = new ArrayList<String>(); while (rs.next()) { String s = rs.getString(4); // 4 is column name, 5 data type etc. System.out.println("Name: " + s); int type = rs.getInt(5); if (type == java.sql.Types.VARCHAR) { System.out.println("Type: Varchar " + type); } else { cols.add(s); System.out.println("Type: Number " + type); } } // end of while. // build insert into from select query String initTable = table.replace("cluster_", ""); StringBuilder sb0 = new StringBuilder(); StringBuilder sb = new StringBuilder(); sb0.append("insert into ").append(table).append(" ("); sb.append(" ( select "); for (int i = 0; i < cols.size(); i++) { sb0.append(cols.get(i)); sb.append("avg(").append(cols.get(i)).append(") "); if (i < cols.size() - 1) { sb0.append(","); sb.append(","); } } sb.append(" from ").append(initTable); sb.append(" where timestamp between \""); sb.append(formatter.format(start)); sb.append("\" and \"").append(formatter.format(end)); sb.append("\" group by timestamp )"); // close fields sb0.append(" )").append(sb); System.out.println(sb0.toString()); // run query conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute(sb0.toString()); // update last run stmt = conn.createStatement(); stmt.execute("insert into aggregation_admin_table set table_ts=\"" + formatter.format(end) + "\" where table_name=\"" + table + "\""); conn.commit(); } }
From source file:com.ingby.socbox.bischeck.test.JDBCtest.java
static public void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException { CommandLineParser parser = new GnuParser(); CommandLine line = null;/*from w w w . ja v a 2 s . com*/ // create the Options Options options = new Options(); options.addOption("u", "usage", false, "show usage."); options.addOption("c", "connection", true, "the connection url"); options.addOption("s", "sql", true, "the sql statement to run"); options.addOption("m", "meta", true, "get the table meta data"); options.addOption("C", "columns", true, "the number of columns to display, default 1"); options.addOption("d", "driver", true, "the driver class"); options.addOption("v", "verbose", false, "verbose outbout"); try { // parse the command line arguments line = parser.parse(options, args); } catch (org.apache.commons.cli.ParseException e) { System.out.println("Command parse error:" + e.getMessage()); HelpFormatter formatter = new HelpFormatter(); formatter.printHelp("JDBCtest", options); Util.ShellExit(1); } if (line.hasOption("verbose")) { verbose = true; } if (line.hasOption("usage")) { HelpFormatter formatter = new HelpFormatter(); formatter.printHelp("Bischeck", options); Util.ShellExit(0); } String driverclassname = null; if (!line.hasOption("driver")) { System.out.println("Driver class must be set"); Util.ShellExit(1); } else { driverclassname = line.getOptionValue("driver"); outputln("DriverClass: " + driverclassname); } String connectionname = null; if (!line.hasOption("connection")) { System.out.println("Connection url must be set"); Util.ShellExit(1); } else { connectionname = line.getOptionValue("connection"); outputln("Connection: " + connectionname); } String sql = null; String tablename = null; if (line.hasOption("sql")) { sql = line.getOptionValue("sql"); outputln("SQL: " + sql); } if (line.hasOption("meta")) { tablename = line.getOptionValue("meta"); outputln("Table: " + tablename); } int nrColumns = 1; if (line.hasOption("columns")) { nrColumns = new Integer(line.getOptionValue("columns")); } long execStart = 0l; long execEnd = 0l; long openStart = 0l; long openEnd = 0l; long metaStart = 0l; long metaEnd = 0l; Class.forName(driverclassname).newInstance(); openStart = System.currentTimeMillis(); Connection conn = DriverManager.getConnection(connectionname); openEnd = System.currentTimeMillis(); if (tablename != null) { ResultSet rsCol = null; metaStart = System.currentTimeMillis(); DatabaseMetaData md = conn.getMetaData(); metaEnd = System.currentTimeMillis(); rsCol = md.getColumns(null, null, tablename, null); if (verbose) { tabular("COLUMN_NAME"); tabular("TYPE_NAME"); tabular("COLUMN_SIZE"); tabularlast("DATA_TYPE"); outputln(""); } while (rsCol.next()) { tabular(rsCol.getString("COLUMN_NAME")); tabular(rsCol.getString("TYPE_NAME")); tabular(rsCol.getString("COLUMN_SIZE")); tabularlast(rsCol.getString("DATA_TYPE")); outputln("", true); } } if (sql != null) { Statement stat = conn.createStatement(); stat.setQueryTimeout(10); execStart = System.currentTimeMillis(); ResultSet res = stat.executeQuery(sql); ResultSetMetaData rsmd = res.getMetaData(); execEnd = System.currentTimeMillis(); if (verbose) { for (int i = 1; i < nrColumns + 1; i++) { if (i != nrColumns) tabular(rsmd.getColumnName(i)); else tabularlast(rsmd.getColumnName(i)); } outputln(""); } while (res.next()) { for (int i = 1; i < nrColumns + 1; i++) { if (i != nrColumns) tabular(res.getString(i)); else tabularlast(res.getString(i)); } outputln("", true); } stat.close(); res.close(); } conn.close(); // Print the execution times outputln("Open time: " + (openEnd - openStart) + " ms"); if (line.hasOption("meta")) { outputln("Meta time: " + (metaEnd - metaStart) + " ms"); } if (line.hasOption("sql")) { outputln("Exec time: " + (execEnd - execStart) + " ms"); } }
From source file:GetDBInfo.java
public static void main(String[] args) { Connection c = null; // The JDBC connection to the database server try {// w w w .ja va 2s . co m // Look for the properties file DB.props in the same directory as // this program. It will contain default values for the various // parameters needed to connect to a database Properties p = new Properties(); try { p.load(GetDBInfo.class.getResourceAsStream("DB.props")); } catch (Exception e) { } // Get default values from the properties file String driver = p.getProperty("driver"); // Driver class name String server = p.getProperty("server", ""); // JDBC URL for server String user = p.getProperty("user", ""); // db user name String password = p.getProperty("password", ""); // db password // These variables don't have defaults String database = null; // The db name (appended to server URL) String table = null; // The optional name of a table in the db // Parse the command-line args to override the default values above for (int i = 0; i < args.length; i++) { if (args[i].equals("-d")) driver = args[++i]; //-d <driver> else if (args[i].equals("-s")) server = args[++i];//-s <server> else if (args[i].equals("-u")) user = args[++i]; //-u <user> else if (args[i].equals("-p")) password = args[++i]; else if (database == null) database = args[i]; // <dbname> else if (table == null) table = args[i]; // <table> else throw new IllegalArgumentException("Unknown argument: " + args[i]); } // Make sure that at least a server or a database were specified. // If not, we have no idea what to connect to, and cannot continue. if ((server.length() == 0) && (database.length() == 0)) throw new IllegalArgumentException("No database specified."); // Load the db driver, if any was specified. if (driver != null) Class.forName(driver); // Now attempt to open a connection to the specified database on // the specified server, using the specified name and password c = DriverManager.getConnection(server + database, user, password); // Get the DatabaseMetaData object for the connection. This is the // object that will return us all the data we're interested in here DatabaseMetaData md = c.getMetaData(); // Display information about the server, the driver, etc. System.out.println("DBMS: " + md.getDatabaseProductName() + " " + md.getDatabaseProductVersion()); System.out.println("JDBC Driver: " + md.getDriverName() + " " + md.getDriverVersion()); System.out.println("Database: " + md.getURL()); System.out.println("User: " + md.getUserName()); // Now, if the user did not specify a table, then display a list of // all tables defined in the named database. Note that tables are // returned in a ResultSet, just like query results are. if (table == null) { System.out.println("Tables:"); ResultSet r = md.getTables("", "", "%", null); while (r.next()) System.out.println("\t" + r.getString(3)); } // Otherwise, list all columns of the specified table. // Again, information about the columns is returned in a ResultSet else { System.out.println("Columns of " + table + ": "); ResultSet r = md.getColumns("", "", table, "%"); while (r.next()) System.out.println("\t" + r.getString(4) + " : " + r.getString(6)); } } // Print an error message if anything goes wrong. catch (Exception e) { System.err.println(e); if (e instanceof SQLException) System.err.println(((SQLException) e).getSQLState()); System.err.println("Usage: java GetDBInfo [-d <driver] " + "[-s <dbserver>]\n" + "\t[-u <username>] [-p <password>] <dbname>"); } // Always remember to close the Connection object when we're done! finally { try { c.close(); } catch (Exception e) { } } }
From source file:com.alifi.jgenerator.spring.SpringGeneratorFactory.java
public static void main(String[] args) { @SuppressWarnings("unused") ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("META-INF/SpringContext.xml"); for (DataSourceMap m : DataSourceUtil.getDataSourceMap()) { DataSource ds = m.getDataSource(); try {// ww w . j a v a 2 s . c o m Connection con = ds.getConnection(); DatabaseMetaData dmd = con.getMetaData(); String catalog = con.getCatalog(); String schemaPattern = dmd.getSchemaTerm(); System.out.println("Catalog:" + con.getCatalog() + " schemaPattern:" + schemaPattern); ResultSet rs = dmd.getTables(catalog, schemaPattern, "time_zone_transition", null); Statement s = null; ResultSet xrs = null; try { s = con.createStatement(); xrs = s.executeQuery("select * from time_zone_transition"); if (xrs.next()) { p("xxxxxxxxxxxxxxxxxxxxxxxx:" + xrs.getString(1)); } } catch (SQLException e) { e.printStackTrace(); } finally { } while (rs.next()) { p("-----------------------"); p("TABLE_CAT :" + rs.getString("TABLE_CAT")); p("TABLE_SCHEM:" + rs.getString("TABLE_SCHEM")); p("TABLE_NAME :" + rs.getString("TABLE_NAME")); p("TABLE_TYPE :" + rs.getString("TABLE_TYPE")); p("REMARKS: " + rs.getString("REMARKS")); p("11111111111111111:" + rs.getMetaData().getColumnClassName(1)); // ResultSet pkeyRs = dmd.getPrimaryKeys(catalog, schemaPattern, rs.getString("TABLE_NAME")); while (pkeyRs.next()) { p("M-------------M"); p("------COLUMN_NAME:" + pkeyRs.getString("COLUMN_NAME")); p("------KEY_SEQ:" + pkeyRs.getString("KEY_SEQ")); p("------PK_NAME:" + pkeyRs.getString("PK_NAME")); } // ResultSet rss = dmd.getColumns(catalog, schemaPattern, rs.getString("TABLE_NAME"), null); int cCount = rss.getMetaData().getColumnCount(); for (int i = 1; i <= cCount; i++) { p("-=-=-=-=-=-=-=-=-=-=-=-=-=-=-="); p(" getColumnClassName:" + rss.getMetaData().getColumnClassName(i)); p(" getColumnLabel:" + rss.getMetaData().getColumnLabel(i)); p(" getColumnName:" + rss.getMetaData().getColumnName(i)); p(" getColumnTypeName:" + rss.getMetaData().getColumnTypeName(i)); p(" getColumnType:" + ColumnTypes.getType(rss.getMetaData().getColumnType(i))); } } rs = dmd.getTableTypes(); while (rs.next()) { p("========================="); p("TABLE_TYPE: " + rs.getString("TABLE_TYPE")); } // ResultSetMetaData rsmd = rs.getMetaData(); // int numberOfColumns = rsmd.getColumnCount(); // for (int i = 1; i <= numberOfColumns; i++) // p(rsmd.getColumnName(i)); } catch (SQLException e) { e.printStackTrace(); } p(m.toString()); } }