Java/Database SQL JDBC/ResultSetMetaData — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 09:34, 1 июня 2010
Содержание
Create Custom RowSet MetaData
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.Types; import javax.sql.RowSetMetaData; import javax.sql.rowset.CachedRowSet; import javax.sql.rowset.RowSetMetaDataImpl; import com.sun.rowset.CachedRowSetImpl; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); System.out.println("Got Connection."); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar);"); st.executeUpdate("create view surveyView as (select * from survey);"); st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")"); st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")"); CachedRowSet crs = null; RowSetMetaData rsMD = new RowSetMetaDataImpl(); rsMD.setColumnCount(2); rsMD.setColumnName(1, "id"); rsMD.setColumnType(1, Types.VARCHAR); rsMD.setColumnName(2, "name"); rsMD.setColumnType(2, Types.VARCHAR); // sets the designated column"s table name, if any, to the given String. rsMD.setTableName(1, "survey"); rsMD.setTableName(2, "survey"); // use a custom made RowSetMetaData object for CachedRowSet object crs = new CachedRowSetImpl(); crs.setMetaData(rsMD); crs.moveToInsertRow(); crs.updateString(1, "1111"); crs.updateString(2, "alex"); crs.insertRow(); crs.moveToInsertRow(); crs.updateString(1, "2222"); crs.updateString(2, "jane"); crs.insertRow(); // if you want to commit changes from a CachedRowSet // object to your desired datasource, then you must // create a Connection object. // //conn = getHSQLConnection(); // moves the cursor to the remembered cursor position, usually // the current row. This method has no effect if the cursor is // not on the insert row. crs.moveToCurrentRow(); // when the method acceptChanges() is executed, the CachedRowSet // object"s writer, a RowSetWriterImpl object, is called behind the // scenes to write the changes made to the rowset to the underlying // data source. The writer is implemented to make a connection to // the data source and write updates to it. crs.acceptChanges(conn); conn.close(); } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); } public static Connection getMySqlConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/demo2s"; String username = "oost"; String password = "oost"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:caspian"; String username = "mp"; String password = "mp2"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; }
}
</source>
Get ResultSet Metadata
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
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")"); st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); System.out.println("resultSet MetaData column Count=" + numberOfColumns); for (int i = 1; i <= numberOfColumns; i++) { System.out.println("column MetaData "); System.out.println("column number " + i); // indicates the designated column"s normal maximum width in // characters System.out.println(rsMetaData.getColumnDisplaySize(i)); // gets the designated column"s suggested title // for use in printouts and displays. System.out.println(rsMetaData.getColumnLabel(i)); // get the designated column"s name. System.out.println(rsMetaData.getColumnName(i)); // get the designated column"s SQL type. System.out.println(rsMetaData.getColumnType(i)); // get the designated column"s SQL type name. System.out.println(rsMetaData.getColumnTypeName(i)); // get the designated column"s class name. System.out.println(rsMetaData.getColumnClassName(i)); // get the designated column"s table name. System.out.println(rsMetaData.getTableName(i)); // get the designated column"s number of decimal digits. System.out.println(rsMetaData.getPrecision(i)); // gets the designated column"s number of // digits to right of the decimal point. System.out.println(rsMetaData.getScale(i)); // indicates whether the designated column is // automatically numbered, thus read-only. System.out.println(rsMetaData.isAutoIncrement(i)); // indicates whether the designated column is a cash value. System.out.println(rsMetaData.isCurrency(i)); // indicates whether a write on the designated // column will succeed. System.out.println(rsMetaData.isWritable(i)); // indicates whether a write on the designated // column will definitely succeed. System.out.println(rsMetaData.isDefinitelyWritable(i)); // indicates the nullability of values // in the designated column. System.out.println(rsMetaData.isNullable(i)); // Indicates whether the designated column // is definitely not writable. System.out.println(rsMetaData.isReadOnly(i)); // Indicates whether a column"s case matters // in the designated column. System.out.println(rsMetaData.isCaseSensitive(i)); // Indicates whether a column"s case matters // in the designated column. System.out.println(rsMetaData.isSearchable(i)); // indicates whether values in the designated // column are signed numbers. System.out.println(rsMetaData.isSigned(i)); // Gets the designated column"s table"s catalog name. System.out.println(rsMetaData.getCatalogName(i)); // Gets the designated column"s table"s schema name. System.out.println(rsMetaData.getSchemaName(i)); } st.close(); conn.close(); } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); } public static Connection getMySqlConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/demo2s"; String username = "oost"; String password = "oost"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:caspian"; String username = "mp"; String password = "mp2"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; }
}
</source>
Get Table Name From ResultSet Metadata
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
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")"); st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); System.out.println("resultSet MetaData column Count=" + numberOfColumns); for (int i = 1; i <= numberOfColumns; i++) { System.out.println("column MetaData "); System.out.println("column number " + i);
// get the designated column"s table name. System.out.println(rsMetaData.getTableName(i)); } st.close(); conn.close(); } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); } public static Connection getMySqlConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/demo2s"; String username = "oost"; String password = "oost"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:caspian"; String username = "mp"; String password = "mp2"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; }
}
</source>
Get Table Schema Name From ResultSet Metadata
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
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")"); st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); System.out.println("resultSet MetaData column Count=" + numberOfColumns); for (int i = 1; i <= numberOfColumns; i++) { System.out.println("column MetaData "); System.out.println("column number " + i); // Gets the designated column"s table"s schema name. System.out.println(rsMetaData.getSchemaName(i)); } st.close(); conn.close(); } private static Connection getHSQLConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); System.out.println("Driver Loaded."); String url = "jdbc:hsqldb:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); } public static Connection getMySqlConnection() throws Exception { String driver = "org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/demo2s"; String username = "oost"; String password = "oost"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); return conn; } public static Connection getOracleConnection() throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:caspian"; String username = "mp"; String password = "mp2"; Class.forName(driver); // load Oracle driver Connection conn = DriverManager.getConnection(url, username, password); return conn; }
}
</source>
Getting the Column Names in a Result Set
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName); String serverName = "127.0.0.1"; String portNumber = "1433"; String mydatabase = serverName + ":" + portNumber; String url = "jdbc:JSQLConnect://" + mydatabase; String username = "username"; String password = "password"; Connection connection = DriverManager.getConnection(url, username, password); Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM mysql_all_table"); ResultSetMetaData rsmd = rs.getMetaData(); int numColumns = rsmd.getColumnCount(); for (int i = 1; i < numColumns + 1; i++) { String columnName = rsmd.getColumnName(i); String tableName = rsmd.getTableName(i); } }
}
</source>
ResultSet Metadata from Oracle Driver
<source lang="java">
import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; public class RSMetaData {
public static void main(String[] args) throws Exception { String driver = "oracle.jdbc.driver.OracleDriver"; Class.forName(driver).newInstance(); String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL"; Connection conn = DriverManager.getConnection(jdbcUrl, "yourName", "mypwd"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM Employees"); printColumnInfo(rs); printColumnNames(rs); processRs(rs); rs = stmt.executeQuery("SELECT * FROM Location"); printColumnInfo(rs); printColumnNames(rs); processRs(rs); conn.close(); } public static void processRs(ResultSet rs) throws SQLException { ResultSetMetaData rmd = rs.getMetaData(); while (rs.next()) { for (int col = 1; col <= rmd.getColumnCount(); col++) getData(rs, rmd.getColumnType(col), col); } } public static void printColumnNames(ResultSet rs) throws SQLException { ResultSetMetaData rmd = rs.getMetaData(); for (int col = 1; col <= rmd.getColumnCount(); col++) System.out.println(rmd.getColumnName(col) + " "); } public static void getData(ResultSet rs, int type, int colIdx) throws SQLException { switch (type) { case java.sql.Types.CHAR: case java.sql.Types.VARCHAR: System.out.println(rs.getString(colIdx)); break; case java.sql.Types.INTEGER: int i = rs.getInt(colIdx); System.out.println(i); break; case java.sql.Types.NUMERIC: BigDecimal bd = rs.getBigDecimal(colIdx); System.out.println(bd.toString()); break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: java.sql.Date d = rs.getDate(colIdx); System.out.println(d.toString()); break; } } public static void printColumnInfo(ResultSet rs) throws SQLException { ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); for (int colIdx = 1; colIdx <= cols; colIdx++) { String name = rsmd.getColumnName(colIdx); int type = rsmd.getColumnType(colIdx); String typeName = rsmd.getColumnTypeName(colIdx); System.out.println(name + ", " + type + ", " + typeName); } }
}
</source>