List of usage examples for javax.sql.rowset CachedRowSet getString
String getString(int columnIndex) throws SQLException;
ResultSet object as a String in the Java programming language. From source file:CachedRS.java
public static void main(String[] args) throws Exception { FileInputStream fis = new FileInputStream(CRS_FILE_LOC); ObjectInputStream in = new ObjectInputStream(fis); CachedRowSet crs = (CachedRowSet) in.readObject(); fis.close();//from w ww .j a v a 2 s. c o m in.close(); Class.forName("oracle.jdbc.driver.OracleDriver"); crs.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL"); crs.setUsername("yourName"); crs.setPassword("mypwd"); String sql = "SELECT SSN, Name, Salary, Hiredate FROM Employees WHERE SSN=?"; crs.setCommand(sql); crs.setInt(1, 111111111); crs.execute(); FileOutputStream fos = new FileOutputStream(CRS_FILE_LOC); ObjectOutputStream out = new ObjectOutputStream(fos); out.writeObject(crs); out.close(); crs.close(); fis = new FileInputStream(CRS_FILE_LOC); in = new ObjectInputStream(fis); crs = (CachedRowSet) in.readObject(); fis.close(); in.close(); while (crs.next()) { System.out.print("SSN: " + crs.getInt("ssn")); System.out.print(", Name: " + crs.getString("name")); System.out.print(", Salary: $" + crs.getDouble("salary")); System.out.print(", HireDate: " + crs.getDate("hiredate")); } crs.close(); }
From source file:com.oracle.tutorial.jdbc.FilteredRowSetSample.java
private void viewFilteredRowSet(FilteredRowSet frs) throws SQLException { if (frs == null) { return;// www. java2s .co m } CachedRowSet crs = (CachedRowSet) frs; while (crs.next()) { if (crs == null) { break; } System.out.println(crs.getInt("STORE_ID") + ", " + crs.getString("CITY") + ", " + crs.getInt("COFFEE") + ", " + crs.getInt("MERCH") + ", " + crs.getInt("TOTAL")); } }
From source file:entity.Chart.java
private void init() { try {// ww w. j av a2 s . c o m if (this.reportID < 0) { this.reportName = "Maj_Runs"; this.sql = "exec sp_getMaj_Runs @BotID = " + Math.abs(reportID); this.server = SQLHelper.LOCAL_IP; this.database = SQLHelper.DB_QA_DATA; this.reportType = "tablechart"; this.comments = ""; this.startRow = 1; this.referQueryID = -1; this.groupClause = null; return; } HashMap<String, Object> params = new HashMap<String, Object>(); params.put("node", reportID); SqlCommand command = new SqlCommand(SQLHelper.LOCAL_IP, SQLHelper.DB_QA, CommandType.StoredProcedure, "sp_getQuery", params); HashMap<String, Object> cells = SQLHelper.executeCommand(command, new MapRowHandler()); this.reportName = (String) cells.get("name"); this.sql = (String) cells.get("queryText"); this.server = (String) cells.get("server"); if (this.server.equals("127.0.0.1") || this.server.equals("localhost")) { this.server = SQLHelper.LOCAL_IP; } this.database = (String) cells.get("database"); this.reportType = (String) cells.get("queryType"); this.comments = (String) cells.get("comments"); this.startRow = (Integer) cells.get("startRow"); this.referQueryID = (Integer) cells.get("ReferQueryID"); this.groupClause = (String) cells.get("GroupClause"); this.totalColumnName = (String) cells.get("TotalItemColumn"); //set report parameters command = new SqlCommand(SQLHelper.LOCAL_IP, SQLHelper.DB_QA, CommandType.Text, "SELECT * FROM Qa_Query_Params WHERE ReportID = " + reportID); CachedRowSet rowSet = SQLHelper.executeCommand(command, new CachedRowSetResultHandler()); rParams = new QueryParameter[rowSet.size()]; int i = 0; while (rowSet.next()) { rParams[i] = new QueryParameter(); rParams[i].name = rowSet.getString("Name"); rParams[i].value = rowSet.getString("DefaultValue"); rParams[i].type = rowSet.getString("DataType"); i++; } } catch (Exception ex) { throw new RuntimeException(ex); } }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.ColumnStructureServiceImpl.java
public ColumnRequest getColumnMetadata(OrdsPhysicalDatabase database, String tableName, String columnName, boolean staging) throws Exception { String server = database.getDatabaseServer(); String databaseName = database.getDbConsumedName(); ArrayList<String> fields = new ArrayList<String>(); fields.add("column_name"); fields.add("data_type"); // fields.add("character_maximum_length"); // fields.add("numeric_precision"); // fields.add("numeric_scale"); fields.add("column_default"); fields.add("is_nullable"); // fields.add("ordinal_position"); String command = String.format( "select %s from INFORMATION_SCHEMA.COLUMNS where table_name = ? and column_name = ? ORDER BY ordinal_position ASC", StringUtils.join(fields.iterator(), ",")); CachedRowSet results = this.runJDBCQuery(command, createParameterList(tableName, columnName), server, databaseName);//from www .ja v a 2 s . c o m if (!results.next()) { return null; } ColumnRequest column = new ColumnRequest(); column.setNewname(results.getString("column_name")); column.setDatatype(results.getString("data_type")); column.setDefaultvalue(results.getString("column_default")); column.setNullable(results.getString("is_nullable").equals("YES")); boolean autoIncrement = false; // Parse the default value to an interface-friendly // format, and identify if the field is auto-incremented if (column.getDefaultvalue() != null) { if (column.getDefaultvalue().equals("''::text")) { // CSV column.setDefaultvalue(""); } else if (column.getDefaultvalue().matches("nextval\\('[A-Za-z0-9_\"]+'::regclass\\)")) { column.setDefaultvalue(""); autoIncrement = true; } else if (column.getDefaultvalue().startsWith("NULL::")) { column.setDefaultvalue(null); } } column.setAutoincrement(autoIncrement); return column; }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.ColumnStructureServiceImpl.java
public void updateColumn(OrdsPhysicalDatabase database, String tableName, String columnName, ColumnRequest request, boolean staging) throws Exception { String databaseName = database.getDbConsumedName(); if (staging) { databaseName = this.calculateStagingName(databaseName); }/* w w w .j ava 2 s.com*/ String server = database.getDatabaseServer(); if (!this.checkColumnExists(columnName, tableName, databaseName, server)) { throw new NotFoundException(String.format("Column name %s does not exist", columnName)); } log.debug("doQuery"); String newName = request.getNewname(); Boolean nullable = request.isNullable(); String datatype = request.getDatatype(); String defaultValue = request.getDefaultvalue(); Boolean autoinc = request.isAutoincrement(); String sequenceName = ""; String query; String message = ""; // Check that some new metadata has been specified if ((newName == null || newName.isEmpty()) && nullable == null && (datatype == null || datatype.isEmpty()) && defaultValue == null && autoinc == null) { log.error("Null values set in column request"); throw new BadParameterException("Null values in column request"); } if (autoinc != null) { // If the auto-incrament status is being changed if (autoinc) { // If we're "enabling" autoincrement, check that a // default value hasn't been set as well. if (defaultValue != null && !defaultValue.isEmpty()) { log.error("Specified a default for an auto-increment field"); throw new BadParameterException("You cannot specify a default for an auto-increment field"); } // Get the data type and any current sequence for the // column. String command = "SELECT data_type, pg_get_serial_sequence(?, ?) AS sequence" + " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ?" + " AND column_name = ?"; List<Object> parameters = this.createParameterList(tableName, columnName, tableName, columnName); CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName); String currentDatatype = ""; while (results.next()) { currentDatatype = results.getString("data_type"); sequenceName = results.getString("sequence"); } if (sequenceName != null && !sequenceName.isEmpty()) { message = String.format("Cannot add auto-increment to field %s: it is already auto-incremented", columnName); log.error(message); throw new BadParameterException(message); } if (!integerTypes.contains(currentDatatype.toLowerCase()) && (datatype == null || datatype.isEmpty() || !integerTypes.contains(datatype.toLowerCase()))) { if (datatype != null && !integerTypes.contains(datatype)) { message = String.format("Attempted to create sequence for column %s with datatype %s", columnName, datatype); } else { message = String.format("Attempted to create sequence for column %s with datatype %s", columnName, currentDatatype); } throw new BadParameterException("Only integer fields can have auto-increment enabled"); } } else { // If we're "disabling" autoincrement, check that there's // an existing sequence to remove. String command = "SELECT pg_get_serial_sequence(?, ?) AS sequence"; List<Object> parameters = this.createParameterList(tableName, columnName); CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName); if (!results.first()) { log.error("Attempt to remove autoincrement where non is set"); throw new BadParameterException("Auto-increment is not set so cannot be removed"); } else { sequenceName = results.getString("sequence"); } } } // If a new name for the column is specified, check that a column // with that name doesn't already exist in the table if (newName != null && !newName.isEmpty() && checkColumnExists(newName, tableName, databaseName, server)) { log.error("Attempted to rename column %s to existing name %s in table %s", columnName, newName, tableName); message = String.format("Cannot rename field to %s: field with that name already exists in table %s", newName, tableName); throw new BadParameterException(message); } // Validation all done, now perform the specified operations // If we're changing the nullability, create an execute the // approprate ALTER TABLE query if (nullable != null) { String operation; if (nullable) { operation = "DROP"; message += String.format("Field %s no longer nullable", columnName) + "\n"; } else { operation = "SET"; message += String.format("Field %s now nullable", columnName) + "\n"; } query = String.format("ALTER TABLE %s ALTER %s %s NOT NULL;", quote_ident(tableName), quote_ident(columnName), operation); this.runJDBCQuery(query, null, server, databaseName); } ArrayList<String> statements = new ArrayList<String>(); if (datatype != null && !datatype.isEmpty()) { // If the data type is being altered get the existing data type String convertedCol = quote_ident(columnName); String command = "SELECT data_type" + " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ?" + " AND column_name = ?"; List<Object> parameters = this.createParameterList(tableName, columnName); CachedRowSet results = this.runJDBCQuery(command, parameters, server, databaseName); String fromDataType = ""; while (results.next()) { fromDataType = results.getString("data_type"); } if (fromDataType.equals("integer") || fromDataType.equals("bigint")) { if (datatype.toLowerCase().contains("date") || datatype.toLowerCase().contains("time")) { convertedCol = String.format("to_timestamp(%s)", quote_ident(columnName)); } } else if (fromDataType.contains("date") || fromDataType.contains("time")) { if (datatype.toLowerCase().equals("integer") || datatype.toLowerCase().equals("bigint")) { convertedCol = String.format("extract(epoch from %s)", quote_ident(columnName)); } } datatype = SqlDesignerTranslations.convertDatatypeForPostgres(request.getDatatype()); statements.add(String.format("ALTER TABLE %1$s ALTER %2$s TYPE %3$s USING CAST(%4$s AS %3$s)", quote_ident(tableName), quote_ident(columnName), datatype, convertedCol)); } if (autoinc != null) { // If we're changing the autoincrement status if (autoinc) { // If we're enabline autoincrement, create a new sequence // and attach it to the column, then alter the default // value of the column to the next value in the sequence sequenceName = generateSequenceName(tableName, columnName); statements.add(String.format("CREATE SEQUENCE %s", quote_ident(sequenceName))); statements.add(String.format("ALTER SEQUENCE %s OWNED BY %s.%s", quote_ident(sequenceName), quote_ident(tableName), quote_ident(columnName))); statements.add(String.format("ALTER TABLE %s ALTER %s SET DEFAULT nextval('%s')", quote_ident(tableName), quote_ident(columnName), sequenceName)); // message += String.format(emd.getMessage("Rst066").getText(), // columnName); } else { // If we're disabling autoincrement, remove the default // value and drop the sequence. statements.add(String.format("ALTER TABLE %s ALTER %s DROP DEFAULT", quote_ident(tableName), quote_ident(columnName))); statements.add(String.format("DROP SEQUENCE %s", sequenceName)); // message += String.format(emd.getMessage("Rst067").getText(), // columnName); } } // Always do defaultValue after autoinc, in case we're removing // an autoinc and adding a default. if (defaultValue != null) { if (defaultValue.isEmpty()) { statements.add(String.format("ALTER TABLE %s ALTER %s DROP DEFAULT", quote_ident(tableName), quote_ident(columnName))); // message += String.format(emd.getMessage("Rst023").getText(), // columnName)+"\n"; } else { statements.add(String.format("ALTER TABLE %s ALTER %s SET DEFAULT %s", quote_ident(tableName), quote_ident(columnName), quote_literal(defaultValue))); // message += String.format(emd.getMessage("Rst024").getText(), // columnName, // defaultValue)+"\n"; } } // Always do the rename last so that everything else works. if (newName != null && !newName.isEmpty()) { statements.add(String.format("ALTER TABLE %s RENAME %s to %s;", quote_ident(tableName), quote_ident(columnName), quote_ident(newName))); // message += String.format(emd.getMessage("Rst019").getText(), // columnName, newName)+"\n"; } // run em in 1 go this.runSQLStatements(statements, server, databaseName); }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java
private String[] getPostgresVersionArray(String server) throws Exception { CachedRowSet results = this.runJDBCQuery("SELECT version()", null, server, null); results.next();/*from w w w. j av a 2 s .c o m*/ String version = results.getString(1); String[] versionArray = null; String[] tempVersionArray = null; tempVersionArray = version.split(" "); version = tempVersionArray[1]; versionArray = version.split("\\."); return versionArray; }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java
protected String tableComment(String databaseName, String databaseServer, String tableName) throws Exception { log.debug("tableComment"); // obj_description gives the comment for an object, but needs the unique // oid for that object. To find the oid for a table from its name, we // need to to cast the table ident to regclass then to oid. 'pg_class' // is the catalog the table belongs to. String identifier = String.format("\'public.%s\'", quote_ident(tableName)); String query = String.format("SELECT obj_description(%s::regclass::oid, 'pg_class') as comment", identifier);/*from w ww . j a v a 2 s . c o m*/ String comment = ""; CachedRowSet result = this.runJDBCQuery(query, null, databaseServer, databaseName); if (result == null) { return comment; } try { while (result.next()) { comment = result.getString("comment"); } } finally { result.close(); } return comment; }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java
protected String columnComment(String databaseName, String databaseServer, String tableName, String columnName) throws Exception { log.debug("columnComment"); // col_description gives the comment stored for the given column. // As with obj_description, we need the oid of the table which we // acheieve/*ww w . j a v a 2 s .co m*/ // by casting to regclass then to oid. We also need the column number // within the table, which we get from the name by using a subquery to // look it up in pg_attribute (Again requiring the table oid). String query = "SELECT col_description(quote_ident(?)::regclass::oid, (SELECT attnum FROM pg_attribute WHERE attrelid = quote_ident(?)::regclass::oid AND attname = ?)) as comment"; ArrayList<Object> parameters = new ArrayList<Object>(); parameters.add(tableName); parameters.add(tableName); parameters.add(columnName); String comment = ""; CachedRowSet result = this.runJDBCQuery(query, parameters, databaseServer, databaseName); if (result == null) { return comment; } try { while (result.next()) { comment = result.getString("comment"); } } finally { result.close(); } return comment; }
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java
@SuppressWarnings({ "rawtypes", "unchecked" })
public List<HashMap<String, String>> getForeignKeysFromPostgres(String databaseName, String databaseServer,
String table) throws Exception {
log.debug("getForeignKeysFromPostgres");
String query = "SELECT " + "tc.constraint_name, tc.table_name, kcu.column_name, "
+ "ccu.table_name AS foreign_table_name, " + "ccu.column_name AS foreign_column_name "
+ "FROM information_schema.table_constraints tc " + "JOIN information_schema.key_column_usage kcu "
+ "ON tc.constraint_name = kcu.constraint_name "
+ "JOIN information_schema.constraint_column_usage ccu "
+ "ON ccu.constraint_name = tc.constraint_name " + "WHERE " + "constraint_type = 'FOREIGN KEY' "
+ "AND tc.table_name = ?";
List foreignKeys = new ArrayList();
HashMap<String, String> foreignKey;
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(table);//from w w w . j a v a 2s . co m
CachedRowSet rs = this.runJDBCQuery(query, parameters, databaseServer, databaseName);
// List<Object[]> results = this.runSQLQuery(query, null, null, null);
while (rs.next()) {
foreignKey = new HashMap();
foreignKey.put("constraintName", rs.getString("constraint_name"));
foreignKey.put("tableName", rs.getString("table_name"));
foreignKey.put("columnName", rs.getString("column_name"));
foreignKey.put("foreignTableName", rs.getString("foreign_table_name"));
foreignKey.put("foreignColumnName", rs.getString("foreign_column_name"));
foreignKeys.add(foreignKey);
}
return foreignKeys;
}
From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java
protected List<HashMap<String, Object>> getIndexesFromPostgres(String databaseName, String databaseServer, String table) throws Exception { String query = "SELECT " + "i.relname as indexname, " + "idx.indrelid::regclass as tablename, " + "ARRAY( " + "SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) " + "FROM generate_subscripts(idx.indkey, 1) as k " + "ORDER BY k " + ") as colnames, " + "indisunique as isunique, " + "indisprimary as isprimary " + "FROM " + "pg_index as idx " + "JOIN pg_class as i " + "ON i.oid = idx.indexrelid " + "WHERE CAST(idx.indrelid::regclass as text) = quote_ident(?)"; List<HashMap<String, Object>> indexes = new ArrayList<HashMap<String, Object>>(); HashMap<String, Object> index; String type;/* www . j av a 2 s . c om*/ ArrayList<Object> parameters = new ArrayList<Object>(); parameters.add(table); CachedRowSet rs = this.runJDBCQuery(query, parameters, databaseServer, databaseName); // List<Object[]> results = this.runSQLQuery(command, null, null, null); while (rs.next()) { index = new HashMap<String, Object>(); index.put("name", rs.getString("indexname")); ArrayList<String> columns = new ArrayList<String>(); Array sqlArray = rs.getArray("colnames"); Object[] cols = (Object[]) sqlArray.getArray(); // ResultSet columnSet = sqlArray.getResultSet(); for (Object column : cols) { // // PG may store the index columns as quoted identifiers, in which case we need // to unquote them to return via the API // columns.add(unquote(column.toString())); } index.put("columns", columns); if (rs.getBoolean("isprimary")) { type = "PRIMARY"; } else if (rs.getBoolean("isunique")) { type = "UNIQUE"; } else { type = "INDEX"; } index.put("type", type); indexes.add(index); } return indexes; }