List of usage examples for java.sql ResultSetMetaData getColumnTypeName
String getColumnTypeName(int column) throws SQLException;
From source file:DbMetaServlet.java
private void printMeta(ResultSetMetaData metaData, String type, java.io.PrintWriter out, int colCount) throws SQLException { if (metaData == null || type == null || out == null) throw new IllegalArgumentException("Illegal args passed to printMeta()"); out.println("<tr>"); if (type.equals("table")) { out.println("<td><strong>Table name</strong></td>"); for (int i = 1; i <= colCount; ++i) { out.println("<td>" + metaData.getTableName(i) + "</td>"); }/*from w ww . j ava 2 s . com*/ } else if (type.equals("name")) { out.println("<td><strong>Column name</strong></td>"); for (int i = 1; i <= colCount; ++i) { out.println("<td>" + metaData.getColumnName(i) + "</td>"); } } else if (type.equals("index")) { out.println("<td><strong>Column index</strong></td>"); for (int i = 1; i <= colCount; ++i) { out.println("<td>" + i + "</td>"); } } else if (type.equals("column type")) { out.println("<td><strong>Column type</strong></td>"); for (int i = 1; i <= colCount; ++i) { out.println("<td>" + metaData.getColumnTypeName(i) + "</td>"); } } else if (type.equals("column display")) { out.println("<td><strong>Column display size</strong></td>"); for (int i = 1; i <= colCount; ++i) { out.println("<td>" + metaData.getColumnDisplaySize(i) + "</td>"); } } out.println("</tr>"); }
From source file:org.geoserver.taskmanager.tasks.CopyTableTaskTypeImpl.java
@Override public TaskResult run(TaskContext ctx) throws TaskException { // TODO: check for ctx.isInterruptMe() in loops and cancel task final DbSource sourcedb = (DbSource) ctx.getParameterValues().get(PARAM_SOURCE_DB_NAME); final DbSource targetdb = (DbSource) ctx.getParameterValues().get(PARAM_TARGET_DB_NAME); final DbTable table = (DbTable) ctx.getBatchContext().get(ctx.getParameterValues().get(PARAM_TABLE_NAME)); final DbTable targetTable = ctx.getParameterValues().containsKey(PARAM_TARGET_TABLE_NAME) ? (DbTable) ctx.getParameterValues().get(PARAM_TARGET_TABLE_NAME) : new DbTableImpl(targetdb, table.getTableName()); final String tempTableName = SqlUtil.qualified(SqlUtil.schema(targetTable.getTableName()), "_temp_" + UUID.randomUUID().toString().replace('-', '_')); ctx.getBatchContext().put(targetTable, new DbTableImpl(targetdb, tempTableName)); try (Connection sourceConn = sourcedb.getDataSource().getConnection()) { sourceConn.setAutoCommit(false); try (Connection destConn = targetdb.getDataSource().getConnection()) { try (Statement stmt = sourceConn.createStatement()) { stmt.setFetchSize(BATCH_SIZE); try (ResultSet rs = stmt .executeQuery("SELECT * FROM " + sourcedb.getDialect().quote(table.getTableName()))) { ResultSetMetaData rsmd = rs.getMetaData(); String tempSchema = SqlUtil.schema(tempTableName); String sqlCreateSchemaIfNotExists = tempSchema == null ? "" : targetdb.getDialect().createSchema(destConn, targetdb.getDialect().quote(tempSchema)); // create the temp table structure StringBuilder sb = new StringBuilder(sqlCreateSchemaIfNotExists); sb.append("CREATE TABLE ").append(targetdb.getDialect().quote(tempTableName)).append(" ( "); int columnCount = rsmd.getColumnCount(); for (int i = 1; i <= columnCount; i++) { String columnName = targetdb.getDialect().quote(rsmd.getColumnLabel(i)); String typeName = rsmd.getColumnTypeName(i); sb.append(columnName).append(" ").append(typeName); if (("char".equals(typeName) || "varchar".equals(typeName)) && rsmd.getColumnDisplaySize(i) > 0 && rsmd.getColumnDisplaySize(i) < Integer.MAX_VALUE) { sb.append(" (").append(rsmd.getColumnDisplaySize(i)).append(" ) "); }/*from w w w .j a v a2 s . c o m*/ switch (sourcedb.getDialect().isNullable(rsmd.isNullable(i))) { case ResultSetMetaData.columnNoNulls: sb.append(" NOT NULL"); break; case ResultSetMetaData.columnNullable: sb.append(" NULL"); break; } sb.append(", "); } String primaryKey = getPrimaryKey(sourceConn, table.getTableName()); boolean hasPrimaryKeyColumn = !primaryKey.isEmpty(); if (!hasPrimaryKeyColumn) { // create a Primary key column if none exist. sb.append(GENERATE_ID_COLUMN_NAME + " int PRIMARY KEY, "); columnCount++; } sb.setLength(sb.length() - 2); sb.append(" ); "); // creating indexes Map<String, Set<String>> indexAndColumnMap = getIndexesColumns(sourceConn, table.getTableName()); Set<String> uniqueIndexes = getUniqueIndexes(sourceConn, table.getTableName()); Set<String> spatialColumns = sourcedb.getDialect().getSpatialColumns(sourceConn, table.getTableName(), sourcedb.getSchema()); for (String indexName : indexAndColumnMap.keySet()) { Set<String> columnNames = indexAndColumnMap.get(indexName); boolean isSpatialIndex = columnNames.size() == 1 && spatialColumns.contains(columnNames.iterator().next()); sb.append(targetdb.getDialect().createIndex(tempTableName, columnNames, isSpatialIndex, uniqueIndexes.contains(indexName))); } // we are copying a view and need to create the spatial index. if (indexAndColumnMap.isEmpty() && !spatialColumns.isEmpty()) { sb.append( targetdb.getDialect().createIndex(tempTableName, spatialColumns, true, false)); } String dump = sb.toString(); LOGGER.log(Level.FINE, "creating temporary table: " + dump); try (Statement stmt2 = destConn.createStatement()) { stmt2.executeUpdate(dump); } // copy the data sb = new StringBuilder("INSERT INTO ").append(targetdb.getDialect().quote(tempTableName)) .append(" VALUES ("); for (int i = 0; i < columnCount; i++) { if (i > 0) { sb.append(","); } sb.append("?"); } sb.append(")"); LOGGER.log(Level.FINE, "inserting records: " + sb.toString()); try (PreparedStatement pstmt = destConn.prepareStatement(sb.toString())) { int batchSize = 0; int primaryKeyValue = 0; while (rs.next()) { for (int i = 1; i <= rsmd.getColumnCount(); i++) { pstmt.setObject(i, rs.getObject(i)); } // generate the primary key value if (!hasPrimaryKeyColumn) { pstmt.setObject(columnCount, primaryKeyValue); } pstmt.addBatch(); batchSize++; if (batchSize >= BATCH_SIZE) { pstmt.executeBatch(); batchSize = 0; } primaryKeyValue++; } if (batchSize > 0) { pstmt.executeBatch(); } } } } } } catch (SQLException e) { // clean-up if necessary try (Connection conn = targetdb.getDataSource().getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate("DROP TABLE IF EXISTS " + targetdb.getDialect().quote(tempTableName)); } } catch (SQLException e2) { } throw new TaskException(e); } return new TaskResult() { @Override public void commit() throws TaskException { try (Connection conn = targetdb.getDataSource().getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate( "DROP TABLE IF EXISTS " + targetdb.getDialect().quote(targetTable.getTableName())); stmt.executeUpdate("ALTER TABLE " + targetdb.getDialect().quote(tempTableName) + " RENAME TO " + targetdb.getDialect().quote(SqlUtil.notQualified(targetTable.getTableName()))); } ctx.getBatchContext().delete(targetTable); } catch (SQLException e) { throw new TaskException(e); } } @Override public void rollback() throws TaskException { try (Connection conn = targetdb.getDataSource().getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.executeUpdate("DROP TABLE " + targetdb.getDialect().quote(tempTableName) + ""); } } catch (SQLException e) { throw new TaskException(e); } } }; }
From source file:com.thinkbiganalytics.schema.QueryRunner.java
/** * Initializes the query result with the specified metadata. * * @param queryResult the query result to initialize * @param rsMetaData the result set metadata for the query * @throws SQLException if the metadata is not available *//*from w w w .j a v a2s . c om*/ private void initQueryResult(@Nonnull final DefaultQueryResult queryResult, @Nonnull final ResultSetMetaData rsMetaData) throws SQLException { final List<QueryResultColumn> columns = new ArrayList<>(); final Map<String, Integer> displayNameMap = new HashMap<>(); for (int i = 1; i <= rsMetaData.getColumnCount(); i++) { final DefaultQueryResultColumn column = new DefaultQueryResultColumn(); column.setField(rsMetaData.getColumnName(i)); String displayName = rsMetaData.getColumnLabel(i); column.setHiveColumnLabel(displayName); //remove the table name if it exists displayName = StringUtils.contains(displayName, ".") ? StringUtils.substringAfterLast(displayName, ".") : displayName; Integer count = 0; if (displayNameMap.containsKey(displayName)) { count = displayNameMap.get(displayName); count++; } displayNameMap.put(displayName, count); column.setDisplayName(displayName + "" + (count > 0 ? count : "")); column.setTableName(StringUtils.substringAfterLast(rsMetaData.getColumnName(i), ".")); column.setDataType(ParserHelper.sqlTypeToHiveType(rsMetaData.getColumnType(i))); column.setNativeDataType(rsMetaData.getColumnTypeName(i)); columns.add(column); } queryResult.setColumns(columns); }
From source file:org.openiot.gsn.storage.StorageManager.java
public DataField[] tableToStructureByString(String tableName, Connection connection) throws SQLException { StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 "); ResultSet rs = null;// ww w . ja va 2 s.com DataField[] toReturn = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); ArrayList<DataField> toReturnArr = new ArrayList<DataField>(); for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); if (colName.equalsIgnoreCase("pk")) continue; if (colName.equalsIgnoreCase("timed")) continue; int colType = structure.getColumnType(i); String colTypeName = structure.getColumnTypeName(i); int precision = structure.getPrecision(i); byte colTypeInGSN = convertLocalTypeToGSN(colType); if ((colTypeInGSN == DataTypes.VARCHAR) || (colTypeInGSN == DataTypes.CHAR)) toReturnArr.add(new DataField(colName, colTypeName, precision, colName)); else toReturnArr.add(new DataField(colName, colTypeInGSN)); } toReturn = toReturnArr.toArray(new DataField[] {}); } finally { if (rs != null) close(rs); } return toReturn; }
From source file:org.apache.bigtop.itest.hive.TestJdbc.java
@Test public void preparedStmtAndResultSet() throws SQLException { final String tableName = "bigtop_jdbc_psars_test_table"; try (Statement stmt = conn.createStatement()) { stmt.execute("drop table if exists " + tableName); stmt.execute("create table " + tableName + " (bo boolean, ti tinyint, db double, fl float, " + "i int, lo bigint, sh smallint, st varchar(32))"); }/* w ww . j a v a 2 s . co m*/ // NOTE Hive 1.2 theoretically support binary, Date & Timestamp in JDBC, but I get errors when I // try to put them in the query. try (PreparedStatement ps = conn .prepareStatement("insert into " + tableName + " values (?, ?, ?, ?, ?, ?, ?, ?)")) { ps.setBoolean(1, true); ps.setByte(2, (byte) 1); ps.setDouble(3, 3.141592654); ps.setFloat(4, 3.14f); ps.setInt(5, 3); ps.setLong(6, 10L); ps.setShort(7, (short) 20); ps.setString(8, "abc"); ps.executeUpdate(); } try (PreparedStatement ps = conn.prepareStatement("insert into " + tableName + " (i, st) " + "values(?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) { ps.setNull(1, Types.INTEGER); ps.setObject(2, "mary had a little lamb"); ps.executeUpdate(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.clearParameters(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.execute(); } try (Statement stmt = conn.createStatement()) { ResultSet rs = stmt.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int colCnt = md.getColumnCount(); LOG.debug("Column count is " + colCnt); for (int i = 1; i <= colCnt; i++) { LOG.debug("Looking at column " + i); String strrc = md.getColumnClassName(i); LOG.debug("Column class name is " + strrc); int intrc = md.getColumnDisplaySize(i); LOG.debug("Column display size is " + intrc); strrc = md.getColumnLabel(i); LOG.debug("Column label is " + strrc); strrc = md.getColumnName(i); LOG.debug("Column name is " + strrc); intrc = md.getColumnType(i); LOG.debug("Column type is " + intrc); strrc = md.getColumnTypeName(i); LOG.debug("Column type name is " + strrc); intrc = md.getPrecision(i); LOG.debug("Precision is " + intrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); boolean boolrc = md.isAutoIncrement(i); LOG.debug("Is auto increment? " + boolrc); boolrc = md.isCaseSensitive(i); LOG.debug("Is case sensitive? " + boolrc); boolrc = md.isCurrency(i); LOG.debug("Is currency? " + boolrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); intrc = md.isNullable(i); LOG.debug("Is nullable? " + intrc); boolrc = md.isReadOnly(i); LOG.debug("Is read only? " + boolrc); } while (rs.next()) { LOG.debug("bo = " + rs.getBoolean(1)); LOG.debug("bo = " + rs.getBoolean("bo")); LOG.debug("ti = " + rs.getByte(2)); LOG.debug("ti = " + rs.getByte("ti")); LOG.debug("db = " + rs.getDouble(3)); LOG.debug("db = " + rs.getDouble("db")); LOG.debug("fl = " + rs.getFloat(4)); LOG.debug("fl = " + rs.getFloat("fl")); LOG.debug("i = " + rs.getInt(5)); LOG.debug("i = " + rs.getInt("i")); LOG.debug("lo = " + rs.getLong(6)); LOG.debug("lo = " + rs.getLong("lo")); LOG.debug("sh = " + rs.getShort(7)); LOG.debug("sh = " + rs.getShort("sh")); LOG.debug("st = " + rs.getString(8)); LOG.debug("st = " + rs.getString("st")); LOG.debug("tm = " + rs.getObject(8)); LOG.debug("tm = " + rs.getObject("st")); LOG.debug("tm was null " + rs.wasNull()); } LOG.debug("bo is column " + rs.findColumn("bo")); int intrc = rs.getConcurrency(); LOG.debug("concurrency " + intrc); intrc = rs.getFetchDirection(); LOG.debug("fetch direction " + intrc); intrc = rs.getType(); LOG.debug("type " + intrc); Statement copy = rs.getStatement(); SQLWarning warning = rs.getWarnings(); while (warning != null) { LOG.debug("Found a warning: " + warning.getMessage()); warning = warning.getNextWarning(); } rs.clearWarnings(); } }
From source file:org.executequery.gui.resultset.ResultSetTableModel.java
public void createTable(ResultSet resultSet) { if (!isOpenAndValid(resultSet)) { clearData();/* w w w . j a v a2 s .c om*/ return; } try { resetMetaData(); ResultSetMetaData rsmd = resultSet.getMetaData(); columnHeaders.clear(); visibleColumnHeaders.clear(); tableData.clear(); int zeroBaseIndex = 0; int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { zeroBaseIndex = i - 1; columnHeaders.add(new ResultSetColumnHeader(zeroBaseIndex, rsmd.getColumnLabel(i), rsmd.getColumnName(i), rsmd.getColumnType(i), rsmd.getColumnTypeName(i))); } int recordCount = 0; interrupted = false; if (holdMetaData) { setMetaDataVectors(rsmd); } List<RecordDataItem> rowData; long time = System.currentTimeMillis(); while (resultSet.next()) { if (interrupted || Thread.interrupted()) { throw new InterruptedException(); } recordCount++; rowData = new ArrayList<RecordDataItem>(count); for (int i = 1; i <= count; i++) { zeroBaseIndex = i - 1; ResultSetColumnHeader header = columnHeaders.get(zeroBaseIndex); RecordDataItem value = recordDataItemFactory.create(header); try { int dataType = header.getDataType(); switch (dataType) { // some drivers (informix for example) // was noticed to return the hashcode from // getObject for -1 data types (eg. longvarchar). // force string for these - others stick with // getObject() for default value formatting case Types.CHAR: case Types.VARCHAR: value.setValue(resultSet.getString(i)); break; case Types.DATE: value.setValue(resultSet.getDate(i)); break; case Types.TIME: value.setValue(resultSet.getTime(i)); break; case Types.TIMESTAMP: value.setValue(resultSet.getTimestamp(i)); break; case Types.LONGVARCHAR: case Types.CLOB: value.setValue(resultSet.getClob(i)); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BINARY: value.setValue(resultSet.getBytes(i)); break; case Types.BLOB: value.setValue(resultSet.getBlob(i)); break; case Types.BIT: case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.FLOAT: case Types.REAL: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: case Types.NULL: case Types.OTHER: case Types.JAVA_OBJECT: case Types.DISTINCT: case Types.STRUCT: case Types.ARRAY: case Types.REF: case Types.DATALINK: case Types.BOOLEAN: case Types.ROWID: case Types.NCHAR: case Types.NVARCHAR: case Types.LONGNVARCHAR: case Types.NCLOB: case Types.SQLXML: // use getObject for all other known types value.setValue(resultSet.getObject(i)); break; default: // otherwise try as string asStringOrObject(value, resultSet, i); break; } } catch (Exception e) { try { // ... and on dump, resort to string value.setValue(resultSet.getString(i)); } catch (SQLException sqlException) { // catch-all SQLException - yes, this is hideous // noticed with invalid date formatted values in mysql value.setValue("<Error - " + sqlException.getMessage() + ">"); } } if (resultSet.wasNull()) { value.setNull(); } rowData.add(value); } tableData.add(rowData); if (recordCount == maxRecords) { break; } } if (Log.isTraceEnabled()) { Log.trace("Finished populating table model - " + recordCount + " rows - [ " + MiscUtils.formatDuration(System.currentTimeMillis() - time) + "]"); } fireTableStructureChanged(); } catch (SQLException e) { System.err.println("SQL error populating table model at: " + e.getMessage()); Log.debug("Table model error - " + e.getMessage(), e); } catch (Exception e) { if (e instanceof InterruptedException) { Log.debug("ResultSet generation interrupted.", e); } else { String message = e.getMessage(); if (StringUtils.isBlank(message)) { System.err.println("Exception populating table model."); } else { System.err.println("Exception populating table model at: " + message); } Log.debug("Table model error - ", e); } } finally { if (resultSet != null) { try { resultSet.close(); Statement statement = resultSet.getStatement(); if (statement != null) { statement.close(); } } catch (SQLException e) { } } } }
From source file:gsn.storage.StorageManager.java
public DataField[] tableToStructureByString(String tableName, Connection connection) throws SQLException { StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 "); ResultSet rs = null;/* w ww .j a va2 s .co m*/ DataField[] toReturn = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); ArrayList<DataField> toReturnArr = new ArrayList<DataField>(); for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); if (colName.equalsIgnoreCase("pk")) continue; if (colName.equalsIgnoreCase("timed")) continue; int colType = structure.getColumnType(i); String colTypeName = structure.getColumnTypeName(i); int precision = structure.getPrecision(i); byte colTypeInGSN = convertLocalTypeToGSN(colType); if (colTypeInGSN == -100) { logger.error( "The type can't be converted to GSN form - error description: virtual sensor name is: " + tableName + ", field name is: " + colName + ", query is: " + sb); } if ((colTypeInGSN == DataTypes.VARCHAR) || (colTypeInGSN == DataTypes.CHAR)) toReturnArr.add(new DataField(colName, colTypeName, precision, colName)); else toReturnArr.add(new DataField(colName, colTypeInGSN)); } toReturn = toReturnArr.toArray(new DataField[] {}); } finally { if (rs != null) close(rs); } return toReturn; }
From source file:gr.seab.r2rml.beans.Generator.java
BaseDatatype findFieldDataType(String field, ResultSet rs) {
field = field.trim();/*from ww w . j a v a 2 s .c om*/
if (verbose)
log.info("Figuring out datatype of field: " + field);
try {
ResultSetMetaData rsMeta = rs.getMetaData();
if (verbose)
log.info("Table name " + rsMeta.getTableName(1));
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
if (verbose)
log.info("Column name is " + rsMeta.getColumnName(i));
if (rsMeta.getColumnName(i).equals(field)) {
String sqlType = rsMeta.getColumnTypeName(i);
if (verbose)
log.info(
"Column " + i + " with name " + rsMeta.getColumnName(i) + " is of type " + sqlType);
return util.findDataTypeFromSql(sqlType);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
From source file:it.unibas.spicy.persistence.relational.DAORelational.java
public void loadInstance(int scenarioNo, AccessConfiguration accessConfiguration, IDataSourceProxy dataSource, DBFragmentDescription dataDescription, IConnectionFactory dataSourceDB, boolean source) throws DAOException, SQLException { Connection connection = dataSourceDB.getConnection(accessConfiguration); DatabaseMetaData databaseMetaData = null; String catalog = null;//from ww w .j a v a2 s .c o m String schemaName = accessConfiguration.getSchemaName(); Connection connectionPostgres = null; this.dataDescription = dataDescription; AccessConfiguration accessConfigurationPostgres = new AccessConfiguration(); accessConfigurationPostgres.setDriver(SpicyEngineConstants.ACCESS_CONFIGURATION_DRIVER); accessConfigurationPostgres .setUri(SpicyEngineConstants.ACCESS_CONFIGURATION_URI + SpicyEngineConstants.MAPPING_TASK_DB_NAME); accessConfigurationPostgres.setLogin(SpicyEngineConstants.ACCESS_CONFIGURATION_LOGIN); accessConfigurationPostgres.setPassword(SpicyEngineConstants.ACCESS_CONFIGURATION_PASS); connectionPostgres = dataSourceDB.getConnection(accessConfigurationPostgres); try { databaseMetaData = connection.getMetaData(); catalog = connection.getCatalog(); String[] tableTypes = new String[] { "TABLE" }; ResultSet tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, tableTypes); Statement statement = connection.createStatement(); Statement statementPostgres = connectionPostgres.createStatement(); while (tableResultSet.next()) { String tableName = tableResultSet.getString("TABLE_NAME"); if (!this.dataDescription.checkLoadTable(tableName)) { continue; } String tablePath = tableName; if (!schemaName.equals("")) { tablePath = schemaName + ".\"" + tableName + "\""; } String newTablePath = tableName; if (source) { newTablePath = SpicyEngineConstants.SOURCE_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\""; } else { newTablePath = SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\""; } ResultSet countResult = statement .executeQuery("SELECT COUNT(*) AS instancesCount FROM " + tablePath + ";"); int instancesCount = 1; while (countResult.next()) { instancesCount = countResult.getInt("instancesCount"); } ResultSet pKList = null; pKList = databaseMetaData.getPrimaryKeys(null, null, tableName); // ResultSet pKList = statement.executeQuery("SELECT c.column_name as keyname\n" + "FROM information_schema.key_column_usage AS c\n" + // "LEFT JOIN information_schema.table_constraints AS t\n" + // "ON t.constraint_name = c.constraint_name\n" + // "WHERE t.table_name = '" + tablePath + "' AND t.constraint_type = 'PRIMARY KEY';"); String pKListString = ""; while (pKList.next()) { pKListString += pKList.getString("COLUMN_NAME") + ","; } if (pKListString != "") pKListString = pKListString.substring(0, pKListString.length() - 1); int inCount = 0; String viewName = tableName + "_MIPMapView"; String orderByClause = ""; if (pKListString != "") orderByClause = " ORDER BY " + pKListString; statement.executeUpdate("DROP VIEW IF EXISTS " + viewName + ";"); statement.executeUpdate( "CREATE VIEW " + viewName + " AS SELECT * FROM " + tablePath + orderByClause + ";"); for (int i = 0; i <= ((instancesCount - 1) / BATCH_SIZE); i++) { ResultSet instancesSet = statement.executeQuery("SELECT * FROM " + viewName + " LIMIT " + BATCH_SIZE + " OFFSET " + (BATCH_SIZE * i) + ";"); ResultSetMetaData rsmd = instancesSet.getMetaData(); int columnsNumber = rsmd.getColumnCount(); String sql_insert_stmnt = ""; while (instancesSet.next()) { String tmp_sql_insert_stmnt = "("; for (int j = 1; j <= columnsNumber; j++) { String columnValue = instancesSet.getString(j); if (columnValue == null) { tmp_sql_insert_stmnt += " null,"; } else { if (isTextColumn(rsmd.getColumnTypeName(j))) { tmp_sql_insert_stmnt += "'" + columnValue.replaceAll("'", "''") + "',"; } else { tmp_sql_insert_stmnt += "" + columnValue + ","; } } } //take out the last ',' character tmp_sql_insert_stmnt = tmp_sql_insert_stmnt.substring(0, tmp_sql_insert_stmnt.length() - 1); tmp_sql_insert_stmnt += "),"; // if (!inserted.contains(tmp_sql_insert_stmnt)) { sql_insert_stmnt += tmp_sql_insert_stmnt; // inserted.add(tmp_sql_insert_stmnt); // } } if (!sql_insert_stmnt.equals("")) { //take out the last ',' character sql_insert_stmnt = sql_insert_stmnt.substring(0, sql_insert_stmnt.length() - 1); inCount += statementPostgres .executeUpdate("insert into " + newTablePath + " values " + sql_insert_stmnt + ";"); } } statement.executeUpdate("DROP VIEW IF EXISTS " + viewName + ";"); } dataSource.addAnnotation(SpicyEngineConstants.LOADED_INSTANCES_FLAG, true); } finally { if (connection != null) dataSourceDB.close(connection); if (connectionPostgres != null) dataSourceDB.close(connectionPostgres); } }
From source file:org.kawanfw.sql.servlet.sql.ResultSetWriter.java
/** * Code extracted and modified for oreilly jdbc book in french. * /* w w w . jav a2 s .c om*/ * Process the ResultSet and print it on the outPutStream <br> * - Each row is a line of a List of column values <br> * * @param resultSet * the Result Set to process and print on the output stream * @param br * the writer where to redirect the result set content, one Json * line per rs.next(); * */ public void write(ResultSet resultSet) throws SQLException, IOException { try { if (resultSet == null) { throw new SQLException("SQL Connection is null!"); } String productName = getDatabaseProductName(resultSet); isTerradata = productName.equals(SqlUtil.TERADATA) ? true : false; isPostgreSQL = productName.equals(SqlUtil.POSTGRESQL) ? true : false; ResultSetMetaData meta = resultSet.getMetaData(); int cols = meta.getColumnCount(); @SuppressWarnings("unused") int row_count = 0; List<Integer> columnTypeList = new Vector<Integer>(); List<String> columnTypeNameList = new Vector<String>(); List<String> columnNameList = new Vector<String>(); List<String> columnTableList = new Vector<String>(); // Loop on Columns for (int i = 1; i <= cols; i++) { columnTypeList.add(meta.getColumnType(i)); columnNameList.add(meta.getColumnName(i).toLowerCase()); columnTypeNameList.add(meta.getColumnTypeName(i)); if (isPostgreSQL) { columnTableList.add(PostgreSqlUtil.getTableName(resultSet, i)); } else { columnTableList.add(meta.getTableName(i)); } debug(""); debug("meta.getColumnType(" + i + ") : " + meta.getColumnType(i)); debug("meta.getColumnTypeName(" + i + "): " + meta.getColumnTypeName(i)); debug("meta.getColumnName(" + i + ") : " + meta.getColumnName(i)); debug("meta.getTableName(" + i + ") : " + meta.getTableName(i)); } // Ok, dump the column Map<String, Integer> == (Column name, column // pos starting 9) Map<String, Integer> mapColumnNames = new LinkedHashMap<String, Integer>(); for (int i = 0; i < columnNameList.size(); i++) { mapColumnNames.put(columnNameList.get(i), i); } String jsonString = JsonColPosition.toJson(mapColumnNames); debug("JsonColPosition.toJson(mapColumnNames) jsonString" + jsonString); boolean doEncryptResultSet = SqlConfiguratorCall.encryptResultSet(sqlConfigurator); // Maybe encryption asked if (doEncryptResultSet) { jsonString = JsonLineEncrypter.encrypt(jsonString, commonsConfigurator); } //br.write(jsonString + CR_LF); ServerSqlManager.writeLine(out, jsonString); // Loop result Set while (resultSet.next()) { row_count++; // The List of column values for one row List<String> values = new Vector<String>(); for (int i = 0; i < columnTypeList.size(); i++) { int columnIndex = i + 1; int columnType = columnTypeList.get(i); String columnName = columnNameList.get(i); String columnTable = columnTableList.get(i); debug(""); debug("columnIndex: " + columnIndex); debug("columnType : " + columnType); debug("columnName : " + columnName); debug("columnTable: " + columnTable); Object columnValue = null; String columnValueStr = null; if (isBinaryColumn(resultSet, columnType, columnName, columnTable)) { columnValueStr = formatBinaryColumn(resultSet, columnIndex, columnType, columnName, columnTable); } else if (isNStringColumn(columnType)) { columnValueStr = resultSet.getNString(columnIndex); columnValueStr = HtmlConverter.toHtml(columnValueStr); } else if (isClobColumn(columnType)) { columnValueStr = formatClobColumn(resultSet, columnIndex); } else if (columnType == Types.ARRAY) { columnValueStr = formatArrayColumn(resultSet, columnIndex); } else if (columnType == Types.ROWID) { columnValueStr = formatRowIdColumn(resultSet, columnIndex); } else { try { columnValue = resultSet.getObject(columnIndex); debug("columnValue: " + columnValue); } catch (Exception e) { // int intValue = resultSet.getInt(columnName); debug("Exception : " + e.toString()); debug("columnType : " + columnType); debug("columnTypeName: " + columnTypeNameList.get(i)); debug("columnName : " + columnName); throw new SQLException(columnType + "Type/TypeName/ColName " + columnTypeNameList.get(i) + " " + columnName, e); } if (resultSet.wasNull()) { columnValueStr = "NULL"; } else if (columnValue == null) { columnValueStr = null; } else { columnValueStr = columnValue.toString(); } debug("columnValueStr : " + columnValueStr); // Case we - maybe - have an URL: columnValueStr = urlFormater(resultSet, columnIndex, columnValueStr); if (isCharacterType(columnType)) { debugStringType(columnValueStr); columnValueStr = HtmlConverter.toHtml(columnValueStr); } } // Add the value to the list of values: values.add(columnValueStr); } jsonString = StringListTransport.toJson(values); debug("ResultSetLineTransport.toJson(values) jsonString" + jsonString); // Maybe encryption asked if (doEncryptResultSet) { jsonString = JsonLineEncrypter.encrypt(jsonString, commonsConfigurator); } //br.write(jsonString + CR_LF); ServerSqlManager.writeLine(out, jsonString); } // Maybe we send also the ResultSet meta data, if asked by client // side if (JoinResultSetMetaData) { //br.write(FileSplitSeparatorLine.RESULT_SET_GET_METADATA_SEP // + CR_LF); ServerSqlManager.writeLine(out, FileSplitSeparatorLine.RESULT_SET_GET_METADATA_SEP); ResultSetMetaDataWriter resultSetMetaDataWriter = new ResultSetMetaDataWriter(out, commonsConfigurator, sqlConfigurator); resultSetMetaDataWriter.write(resultSet); } } finally { // NO! resultSet.close(); // NO! IOUtils.closeQuietly(br); } }