List of usage examples for java.sql Types CHAR
int CHAR
To view the source code for java.sql Types CHAR.
Click Source Link
The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type CHAR.
From source file:org.jumpmind.vaadin.ui.common.CommonUiUtils.java
public static Table putResultsInTable(final ResultSet rs, int maxResultSize, final boolean showRowNumbers, String... excludeValues) throws SQLException { final Table table = createTable(); table.setImmediate(true);// www.ja v a2s . co m table.setSortEnabled(true); table.setSelectable(true); table.setMultiSelect(true); table.setColumnReorderingAllowed(true); table.setColumnReorderingAllowed(true); table.setColumnCollapsingAllowed(true); final ResultSetMetaData meta = rs.getMetaData(); int columnCount = meta.getColumnCount(); table.addContainerProperty("#", Integer.class, null); Set<String> columnNames = new HashSet<String>(); Set<Integer> skipColumnIndexes = new HashSet<Integer>(); int[] types = new int[columnCount]; for (int i = 1; i <= columnCount; i++) { String realColumnName = meta.getColumnName(i); String columnName = realColumnName; if (!Arrays.asList(excludeValues).contains(columnName)) { int index = 1; while (columnNames.contains(columnName)) { columnName = realColumnName + "_" + index++; } columnNames.add(columnName); Class<?> typeClass = Object.class; int type = meta.getColumnType(i); types[i - 1] = type; switch (type) { case Types.FLOAT: case Types.DOUBLE: case Types.NUMERIC: case Types.REAL: case Types.DECIMAL: typeClass = BigDecimal.class; break; case Types.TINYINT: case Types.SMALLINT: case Types.BIGINT: case Types.INTEGER: typeClass = Long.class; break; case Types.VARCHAR: case Types.CHAR: case Types.NVARCHAR: case Types.NCHAR: case Types.CLOB: typeClass = String.class; default: break; } table.addContainerProperty(i, typeClass, null); table.setColumnHeader(i, columnName); } else { skipColumnIndexes.add(i - 1); } } int rowNumber = 1; while (rs.next() && rowNumber <= maxResultSize) { Object[] row = new Object[columnNames.size() + 1]; row[0] = new Integer(rowNumber); int rowIndex = 1; for (int i = 0; i < columnCount; i++) { if (!skipColumnIndexes.contains(i)) { Object o = getObject(rs, i + 1); int type = types[i]; switch (type) { case Types.FLOAT: case Types.DOUBLE: case Types.REAL: case Types.NUMERIC: case Types.DECIMAL: if (o == null) { o = new BigDecimal(-1); } if (!(o instanceof BigDecimal)) { o = new BigDecimal(castToNumber(o.toString())); } break; case Types.TINYINT: case Types.SMALLINT: case Types.BIGINT: case Types.INTEGER: if (o == null) { o = new Long(-1); } if (!(o instanceof Long)) { o = new Long(castToNumber(o.toString())); } break; default: break; } row[rowIndex] = o == null ? NULL_TEXT : o; rowIndex++; } } table.addItem(row, rowNumber); rowNumber++; } if (rowNumber < 100) { table.setColumnWidth("#", 18); } else if (rowNumber < 1000) { table.setColumnWidth("#", 25); } else { table.setColumnWidth("#", 30); } if (!showRowNumbers) { table.setColumnCollapsed("#", true); } return table; }
From source file:org.jumpmind.symmetric.service.impl.OutgoingBatchService.java
public void updateOutgoingBatch(ISqlTransaction transaction, OutgoingBatch outgoingBatch) { outgoingBatch.setLastUpdatedTime(new Date()); outgoingBatch.setLastUpdatedHostName(clusterService.getServerId()); transaction.prepareAndExecute(getSql("updateOutgoingBatchSql"), new Object[] { outgoingBatch.getStatus().name(), outgoingBatch.getLoadId(), outgoingBatch.isExtractJobFlag() ? 1 : 0, outgoingBatch.isLoadFlag() ? 1 : 0, outgoingBatch.isErrorFlag() ? 1 : 0, outgoingBatch.getByteCount(), outgoingBatch.getExtractCount(), outgoingBatch.getSentCount(), outgoingBatch.getLoadCount(), outgoingBatch.getDataEventCount(), outgoingBatch.getReloadEventCount(), outgoingBatch.getInsertEventCount(), outgoingBatch.getUpdateEventCount(), outgoingBatch.getDeleteEventCount(), outgoingBatch.getOtherEventCount(), outgoingBatch.getIgnoreCount(), outgoingBatch.getRouterMillis(), outgoingBatch.getNetworkMillis(), outgoingBatch.getFilterMillis(), outgoingBatch.getLoadMillis(), outgoingBatch.getExtractMillis(), outgoingBatch.getSqlState(), outgoingBatch.getSqlCode(), FormatUtils.abbreviateForLogging(outgoingBatch.getSqlMessage()), outgoingBatch.getFailedDataId(), outgoingBatch.getLastUpdatedHostName(), outgoingBatch.getLastUpdatedTime(), outgoingBatch.getBatchId(), outgoingBatch.getNodeId() }, new int[] { Types.CHAR, Types.BIGINT, Types.NUMERIC, Types.NUMERIC, Types.NUMERIC, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.BIGINT, Types.VARCHAR, Types.NUMERIC, Types.VARCHAR, Types.BIGINT, Types.VARCHAR, Types.TIMESTAMP, symmetricDialect.getSqlTypeForIds(), Types.VARCHAR }); }
From source file:org.kuali.core.db.torque.KualiTorqueJDBCTransformTask.java
/** * Generates an XML database schema from JDBC metadata. * /* w w w . j a v a2 s . c om*/ * @throws Exception * a generic exception. */ public void generateXML() throws Exception { // Load the database Driver. Class.forName(dbDriver); log("DB driver sucessfuly instantiated"); Connection con = null; try { // Attempt to connect to a database. Properties p = new Properties(); p.setProperty("user", dbUser); p.setProperty("password", dbPassword); p.setProperty("oracle.jdbc.mapDateToTimestamp", "false"); // workaround for change in 11g JDBC driver con = DriverManager.getConnection(dbUrl, p); log("DB connection established"); Platform platform = PlatformFactory.getPlatformFor(dbType); // Get the database Metadata. DatabaseMetaData dbMetaData = con.getMetaData(); databaseNode = doc.createElement("database"); databaseNode.setAttribute("name", "kuali"); // JHK added naming method databaseNode.setAttribute("defaultJavaNamingMethod", "nochange"); if (processTables) { List<String> tableList = platform.getTableNames(dbMetaData, dbSchema); // ensure all are upper case before exporting tableList = upperCaseList(tableList); // ensure sorting is consistent (not DB-dependent) Collections.sort(tableList); for (String curTable : tableList) { if (!tableNameRegexPattern.matcher(curTable).matches()) { log("Skipping table: " + curTable); continue; } if (StringUtils.isNotBlank(tableNameExcludeRegex) && tableNameExcludeRegexPattern.matcher(curTable).matches()) { log("Skipping table: " + curTable); continue; } log("Processing table: " + curTable); Element table = doc.createElement("table"); table.setAttribute("name", curTable.toUpperCase()); // Add Columns. // TableMap tblMap = dbMap.getTable(curTable); List columns = getColumns(dbMetaData, curTable); List<String> primKeys = platform.getPrimaryKeys(dbMetaData, dbSchema, curTable); Map<String, Object[]> foreignKeys = getForeignKeys(dbMetaData, curTable); // Set the primary keys. primaryKeys = new HashMap<String, String>(); for (int k = 0; k < primKeys.size(); k++) { String curPrimaryKey = (String) primKeys.get(k); primaryKeys.put(curPrimaryKey, curPrimaryKey); } for (int j = 0; j < columns.size(); j++) { List col = (List) columns.get(j); String name = (String) col.get(0); Integer jdbcType = ((Integer) col.get(1)); int size = ((Integer) col.get(2)).intValue(); int scale = ((Integer) col.get(5)).intValue(); // From DatabaseMetaData.java // // Indicates column might not allow NULL values. Huh? // Might? Boy, that's a definitive answer. /* int columnNoNulls = 0; */ // Indicates column definitely allows NULL values. /* int columnNullable = 1; */ // Indicates NULLABILITY of column is unknown. /* int columnNullableUnknown = 2; */ Integer nullType = (Integer) col.get(3); String defValue = (String) col.get(4); Element column = doc.createElement("column"); column.setAttribute("name", name); ; column.setAttribute("type", platform.getTorqueColumnType(jdbcType)); // TypeMap.getTorqueType( type ).getName() ); if (size > 0 && (jdbcType.intValue() == Types.CHAR || jdbcType.intValue() == Types.VARCHAR || jdbcType.intValue() == Types.DECIMAL || jdbcType.intValue() == Types.NUMERIC)) { column.setAttribute("size", String.valueOf(size)); } if (scale > 0 && (jdbcType.intValue() == Types.DECIMAL || jdbcType.intValue() == Types.NUMERIC)) { column.setAttribute("scale", String.valueOf(scale)); } if (primaryKeys.containsKey(name)) { column.setAttribute("primaryKey", "true"); // JHK: protect MySQL from excessively long column in the PK //System.out.println( curTable + "." + name + " / " + size ); if (column.getAttribute("size") != null && size > 765) { log("updating column " + curTable + "." + name + " length from " + size + " to 255"); column.setAttribute("size", "255"); } } else { if (nullType.intValue() == DatabaseMetaData.columnNoNulls) { column.setAttribute("required", "true"); } } if (StringUtils.isNotEmpty(defValue)) { defValue = platform.getColumnDefaultValue(platform.getTorqueColumnType(jdbcType), defValue); if (StringUtils.isNotEmpty(defValue)) { column.setAttribute("default", defValue); } } table.appendChild(column); } List<String> foreignKeyNames = new ArrayList<String>(foreignKeys.keySet()); Collections.sort(foreignKeyNames); // Foreign keys for this table. for (String fkName : foreignKeyNames) { Element fk = doc.createElement("foreign-key"); fk.setAttribute("name", fkName.toUpperCase()); Object[] forKey = foreignKeys.get(fkName); String foreignKeyTable = (String) forKey[0]; List refs = (List) forKey[1]; fk.setAttribute("foreignTable", foreignKeyTable.toUpperCase()); String onDelete = (String) forKey[2]; // gmcgrego - just adding onDelete if it's cascade so as not to affect kfs behavior if (onDelete == "cascade") { fk.setAttribute("onDelete", onDelete); } for (int m = 0; m < refs.size(); m++) { Element ref = doc.createElement("reference"); String[] refData = (String[]) refs.get(m); ref.setAttribute("local", refData[0]); ref.setAttribute("foreign", refData[1]); fk.appendChild(ref); } table.appendChild(fk); } List<TableIndex> indexes = getIndexes(dbMetaData, curTable); Collections.sort(indexes, new Comparator<TableIndex>() { public int compare(TableIndex o1, TableIndex o2) { return o1.name.compareTo(o2.name); } }); for (TableIndex idx : indexes) { if (foreignKeyNames.contains(idx.name)) { log(idx.name + " is also a foreign key, skipping"); continue; } String tagName = idx.unique ? "unique" : "index"; Element index = doc.createElement(tagName); index.setAttribute("name", idx.name.toUpperCase()); for (String colName : idx.columns) { Element col = doc.createElement(tagName + "-column"); col.setAttribute("name", colName); index.appendChild(col); } table.appendChild(index); } databaseNode.appendChild(table); } } if (processViews) { log("Getting view list..."); List<String> viewNames = platform.getViewNames(dbMetaData, dbSchema); log("Found " + viewNames.size() + " views."); viewNames = upperCaseList(viewNames); Collections.sort(viewNames); for (String viewName : viewNames) { if (!tableNameRegexPattern.matcher(viewName).matches()) { log("Skipping view: " + viewName); continue; } Element view = doc.createElement("view"); view.setAttribute("name", viewName.toUpperCase()); /* * <view name="" viewdefinition="" /> * */ String definition = platform.getViewDefinition(dbMetaData.getConnection(), dbSchema, viewName); definition = definition.replaceAll("\0", ""); view.setAttribute("viewdefinition", definition); databaseNode.appendChild(view); } } if (processSequences) { log("Getting sequence list..."); List<String> sequenceNames = platform.getSequenceNames(dbMetaData, dbSchema); log("Found " + sequenceNames.size() + " sequences."); sequenceNames = upperCaseList(sequenceNames); Collections.sort(sequenceNames); for (String sequenceName : sequenceNames) { if (!tableNameRegexPattern.matcher(sequenceName).matches()) { log("Skipping sequence: " + sequenceName); continue; } Element sequence = doc.createElement("sequence"); sequence.setAttribute("name", sequenceName.toUpperCase()); /* * <view name="" nextval="" /> * */ Long nextVal = platform.getSequenceNextVal(dbMetaData.getConnection(), dbSchema, sequenceName); sequence.setAttribute("nextval", nextVal.toString()); databaseNode.appendChild(sequence); } doc.appendChild(databaseNode); } } finally { if (con != null) { con.close(); con = null; } } }
From source file:com.flexive.core.storage.GenericDivisionExporter.java
/** * Dump a generic table to XML/* w w w.ja v a 2 s. c o m*/ * * @param tableName name of the table * @param stmt an open statement * @param out output stream * @param sb an available and valid StringBuilder * @param xmlTag name of the xml tag to write per row * @param idColumn (optional) id column to sort results * @param onlyBinaries process binary fields (else these will be ignored) * @throws SQLException on errors * @throws IOException on errors */ private void dumpTable(String tableName, Statement stmt, OutputStream out, StringBuilder sb, String xmlTag, String idColumn, boolean onlyBinaries) throws SQLException, IOException { ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName + (StringUtils.isEmpty(idColumn) ? "" : " ORDER BY " + idColumn + " ASC")); final ResultSetMetaData md = rs.getMetaData(); String value, att; boolean hasSubTags; while (rs.next()) { hasSubTags = false; if (!onlyBinaries) { sb.setLength(0); sb.append(" <").append(xmlTag); } for (int i = 1; i <= md.getColumnCount(); i++) { value = null; att = md.getColumnName(i).toLowerCase(); switch (md.getColumnType(i)) { case java.sql.Types.DECIMAL: case java.sql.Types.NUMERIC: case java.sql.Types.BIGINT: if (!onlyBinaries) { value = String.valueOf(rs.getBigDecimal(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.INTEGER: case java.sql.Types.SMALLINT: case java.sql.Types.TINYINT: if (!onlyBinaries) { value = String.valueOf(rs.getLong(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (!onlyBinaries) { value = String.valueOf(rs.getDouble(i)); if (rs.wasNull()) value = null; } break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (!onlyBinaries) { final Timestamp ts = rs.getTimestamp(i); if (rs.wasNull()) value = null; else value = FxFormatUtils.getDateTimeFormat().format(ts); } break; case java.sql.Types.BIT: case java.sql.Types.CHAR: case java.sql.Types.BOOLEAN: if (!onlyBinaries) { value = rs.getBoolean(i) ? "1" : "0"; if (rs.wasNull()) value = null; } break; case java.sql.Types.CLOB: case java.sql.Types.BLOB: case java.sql.Types.LONGVARBINARY: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARBINARY: case java.sql.Types.VARCHAR: case java.sql.Types.BINARY: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: hasSubTags = true; break; default: LOG.warn("Unhandled type [" + md.getColumnType(i) + "] for [" + tableName + "." + att + "]"); } if (value != null && !onlyBinaries) sb.append(' ').append(att).append("=\"").append(value).append("\""); } if (hasSubTags) { if (!onlyBinaries) sb.append(">\n"); for (int i = 1; i <= md.getColumnCount(); i++) { switch (md.getColumnType(i)) { case java.sql.Types.VARBINARY: case java.sql.Types.LONGVARBINARY: case java.sql.Types.BLOB: case java.sql.Types.BINARY: if (idColumn == null) throw new IllegalArgumentException("Id column required to process binaries!"); String binFile = FOLDER_BINARY + "/BIN_" + String.valueOf(rs.getLong(idColumn)) + "_" + i + ".blob"; att = md.getColumnName(i).toLowerCase(); if (onlyBinaries) { if (!(out instanceof ZipOutputStream)) throw new IllegalArgumentException( "out has to be a ZipOutputStream to store binaries!"); ZipOutputStream zip = (ZipOutputStream) out; InputStream in = rs.getBinaryStream(i); if (rs.wasNull()) break; ZipEntry ze = new ZipEntry(binFile); zip.putNextEntry(ze); byte[] buffer = new byte[4096]; int read; while ((read = in.read(buffer)) != -1) zip.write(buffer, 0, read); in.close(); zip.closeEntry(); zip.flush(); } else { InputStream in = rs.getBinaryStream(i); //need to fetch to see if it is empty if (rs.wasNull()) break; in.close(); sb.append(" <").append(att).append(">").append(binFile).append("</").append(att) .append(">\n"); } break; case java.sql.Types.CLOB: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: if (!onlyBinaries) { value = rs.getString(i); if (rs.wasNull()) break; att = md.getColumnName(i).toLowerCase(); sb.append(" <").append(att).append('>'); escape(sb, value); sb.append("</").append(att).append(">\n"); } break; } } if (!onlyBinaries) sb.append(" </").append(xmlTag).append(">\n"); } else { if (!onlyBinaries) sb.append("/>\n"); } if (!onlyBinaries) write(out, sb); } }
From source file:it.greenvulcano.gvesb.datahandling.dbo.utils.ExtendedRowSetBuilder.java
public int build(Document doc, String id, ResultSet rs, Set<Integer> keyField, Map<String, FieldFormatter> fieldNameToFormatter, Map<String, FieldFormatter> fieldIdToFormatter) throws Exception { if (rs == null) { return 0; }// ww w. j av a2 s. c o m int rowCounter = 0; Element docRoot = doc.getDocumentElement(); ResultSetMetaData metadata = rs.getMetaData(); buildFormatterAndNamesArray(metadata, fieldNameToFormatter, fieldIdToFormatter); boolean noKey = ((keyField == null) || keyField.isEmpty()); boolean isKeyCol = false; boolean isNull = false; Element data = null; Element row = null; Element col = null; Text text = null; String textVal = null; String precKey = null; String colKey = null; Map<String, Element> keyCols = new TreeMap<String, Element>(); while (rs.next()) { if (rowCounter % 10 == 0) { ThreadUtils.checkInterrupted(getClass().getSimpleName(), name, logger); } row = parser.createElementNS(doc, AbstractDBO.ROW_NAME, NS); parser.setAttribute(row, AbstractDBO.ID_NAME, id); for (int j = 1; j <= metadata.getColumnCount(); j++) { FieldFormatter fF = fFormatters[j]; String colName = colNames[j]; isKeyCol = (!noKey && keyField.contains(new Integer(j))); isNull = false; col = parser.createElementNS(doc, colName, NS); if (isKeyCol) { parser.setAttribute(col, AbstractDBO.ID_NAME, String.valueOf(j)); } switch (metadata.getColumnType(j)) { case Types.DATE: case Types.TIME: case Types.TIMESTAMP: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.TIMESTAMP_TYPE); Timestamp dateVal = rs.getTimestamp(j); isNull = dateVal == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { parser.setAttribute(col, AbstractDBO.FORMAT_NAME, AbstractDBO.DEFAULT_DATE_FORMAT); textVal = ""; } else { if (fF != null) { parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getDateFormat()); textVal = fF.formatDate(dateVal); } else { parser.setAttribute(col, AbstractDBO.FORMAT_NAME, AbstractDBO.DEFAULT_DATE_FORMAT); textVal = dateFormatter.format(dateVal); } } } break; case Types.DOUBLE: case Types.FLOAT: case Types.REAL: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE); float numVal = rs.getFloat(j); parser.setAttribute(col, AbstractDBO.NULL_NAME, "false"); if (fF != null) { parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getNumberFormat()); parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, fF.getGroupSeparator()); parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, fF.getDecSeparator()); textVal = fF.formatNumber(numVal); } else { parser.setAttribute(col, AbstractDBO.FORMAT_NAME, numberFormat); parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, groupSeparator); parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, decSeparator); textVal = numberFormatter.format(numVal); } } break; case Types.BIGINT: case Types.INTEGER: case Types.NUMERIC: case Types.SMALLINT: case Types.TINYINT: { BigDecimal bigdecimal = rs.getBigDecimal(j); isNull = bigdecimal == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { if (metadata.getScale(j) > 0) { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE); } else { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE); } textVal = ""; } else { if (fF != null) { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE); parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getNumberFormat()); parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, fF.getGroupSeparator()); parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, fF.getDecSeparator()); textVal = fF.formatNumber(bigdecimal); } else if (metadata.getScale(j) > 0) { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE); parser.setAttribute(col, AbstractDBO.FORMAT_NAME, numberFormat); parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, groupSeparator); parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, decSeparator); textVal = numberFormatter.format(bigdecimal); } else { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE); textVal = bigdecimal.toString(); } } } break; case Types.NCHAR: case Types.NVARCHAR: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NSTRING_TYPE); textVal = rs.getNString(j); isNull = textVal == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { textVal = ""; } } break; case Types.CHAR: case Types.VARCHAR: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.STRING_TYPE); textVal = rs.getString(j); isNull = textVal == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { textVal = ""; } } break; case Types.NCLOB: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_NSTRING_TYPE); NClob clob = rs.getNClob(j); isNull = clob == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { textVal = ""; } else { Reader is = clob.getCharacterStream(); StringWriter str = new StringWriter(); IOUtils.copy(is, str); is.close(); textVal = str.toString(); } } break; case Types.CLOB: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_STRING_TYPE); Clob clob = rs.getClob(j); isNull = clob == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { textVal = ""; } else { Reader is = clob.getCharacterStream(); StringWriter str = new StringWriter(); IOUtils.copy(is, str); is.close(); textVal = str.toString(); } } break; case Types.BLOB: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BASE64_TYPE); Blob blob = rs.getBlob(j); isNull = blob == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { textVal = ""; } else { InputStream is = blob.getBinaryStream(); ByteArrayOutputStream baos = new ByteArrayOutputStream(); IOUtils.copy(is, baos); is.close(); try { byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length()); textVal = Base64.getEncoder().encodeToString(buffer); } catch (SQLFeatureNotSupportedException exc) { textVal = Base64.getEncoder().encodeToString(baos.toByteArray()); } } } break; default: { parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DEFAULT_TYPE); textVal = rs.getString(j); isNull = textVal == null; parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull)); if (isNull) { textVal = ""; } } } if (textVal != null) { text = doc.createTextNode(textVal); col.appendChild(text); } if (isKeyCol) { if (textVal != null) { if (colKey == null) { colKey = textVal; } else { colKey += "##" + textVal; } keyCols.put(String.valueOf(j), col); } } else { row.appendChild(col); } } if (noKey) { if (data == null) { data = parser.createElementNS(doc, AbstractDBO.DATA_NAME, NS); parser.setAttribute(data, AbstractDBO.ID_NAME, id); } } else if ((colKey != null) && !colKey.equals(precKey)) { if (data != null) { docRoot.appendChild(data); } data = parser.createElementNS(doc, AbstractDBO.DATA_NAME, NS); parser.setAttribute(data, AbstractDBO.ID_NAME, id); Element key = parser.createElementNS(doc, AbstractDBO.KEY_NAME, NS); data.appendChild(key); for (Entry<String, Element> keyColsEntry : keyCols.entrySet()) { key.appendChild(keyColsEntry.getValue()); } keyCols.clear(); precKey = colKey; } colKey = null; data.appendChild(row); rowCounter++; } if (data != null) { docRoot.appendChild(data); } return rowCounter; }
From source file:com.squid.core.domain.operators.ExtendedType.java
/** * compute a type "order" that can be used to compare types and promote types. * The order is a couple (x,y), where x represent a family type (string,date,numbers) and y an order in that family * A type should be promoted to the higher order. * @return/*www .j a va 2s . com*/ */ public int[] computeTypeOrder() { switch (getDataType()) { case Types.BIT: return new int[] { NUMBER_ORDER, 0 }; case Types.BOOLEAN: return new int[] { NUMBER_ORDER, 1 }; case Types.TINYINT: return new int[] { NUMBER_ORDER, 2 }; case Types.SMALLINT: return new int[] { NUMBER_ORDER, 3 }; case Types.INTEGER: return new int[] { NUMBER_ORDER, 4 }; case Types.BIGINT: return new int[] { NUMBER_ORDER, 5 }; /////////////////////////// case Types.REAL: return new int[] { NUMBER_ORDER, 6 }; case Types.DOUBLE: case Types.FLOAT: return new int[] { NUMBER_ORDER, 7 }; case Types.DECIMAL: return new int[] { NUMBER_ORDER, 8 }; case Types.NUMERIC: return new int[] { NUMBER_ORDER, 9 }; ////////////////////////// case Types.CHAR: return new int[] { STRING_ORDER, 0 }; case Types.VARCHAR: return new int[] { STRING_ORDER, 1 }; case Types.LONGVARCHAR: return new int[] { STRING_ORDER, 2 }; case Types.CLOB: return new int[] { STRING_ORDER, 3 }; /////////////////////////// case Types.TIME: return new int[] { DATE_ORDER, 1 }; case Types.DATE: return new int[] { DATE_ORDER, 2 }; case Types.TIMESTAMP: return new int[] { DATE_ORDER, 3 }; case CustomTypes.INTERVAL: return new int[] { DATE_ORDER, 4 }; /////////////////////////// default: return new int[] { UNKNOWN_ORDER, 0 }; } }
From source file:org.jfree.data.jdbc.JDBCCategoryDataset.java
/** * Populates the dataset by executing the supplied query against the * existing database connection. If no connection exists then no action * is taken.//w w w . j a va 2 s . c o m * <p> * The results from the query are extracted and cached locally, thus * applying an upper limit on how many rows can be retrieved successfully. * * @param con the connection. * @param query the query. * * @throws SQLException if there is a problem executing the query. */ public void executeQuery(Connection con, String query) throws SQLException { Statement statement = null; ResultSet resultSet = null; try { statement = con.createStatement(); resultSet = statement.executeQuery(query); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); if (columnCount < 2) { throw new SQLException("JDBCCategoryDataset.executeQuery() : insufficient columns " + "returned from the database."); } // Remove any previous old data int i = getRowCount(); while (--i >= 0) { removeRow(i); } while (resultSet.next()) { // first column contains the row key... Comparable rowKey = resultSet.getString(1); for (int column = 2; column <= columnCount; column++) { Comparable columnKey = metaData.getColumnName(column); int columnType = metaData.getColumnType(column); switch (columnType) { case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.FLOAT: case Types.DOUBLE: case Types.DECIMAL: case Types.NUMERIC: case Types.REAL: { Number value = (Number) resultSet.getObject(column); if (this.transpose) { setValue(value, columnKey, rowKey); } else { setValue(value, rowKey, columnKey); } break; } case Types.DATE: case Types.TIME: case Types.TIMESTAMP: { Date date = (Date) resultSet.getObject(column); Number value = new Long(date.getTime()); if (this.transpose) { setValue(value, columnKey, rowKey); } else { setValue(value, rowKey, columnKey); } break; } case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: { String string = (String) resultSet.getObject(column); try { Number value = Double.valueOf(string); if (this.transpose) { setValue(value, columnKey, rowKey); } else { setValue(value, rowKey, columnKey); } } catch (NumberFormatException e) { // suppress (value defaults to null) } break; } default: // not a value, can't use it (defaults to null) break; } } } fireDatasetChanged(new DatasetChangeInfo()); //TODO: fill in real change info } finally { if (resultSet != null) { try { resultSet.close(); } catch (Exception e) { // report this? } } if (statement != null) { try { statement.close(); } catch (Exception e) { // report this? } } } }
From source file:br.bookmark.db.util.ResultSetUtils.java
/** * Map JDBC objects to Java equivalents. * Used by getBean() and getBeans()./*from w ww .ja v a 2s.co m*/ * <p> * Some types not supported. * Many not work with all drivers. * <p> * Makes binary conversions of BIGINT, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, * REAL, SMALLINT, TIME, TIMESTAMP, TINYINT. * Makes Sting conversions of CHAR, CLOB, VARCHAR, LONGVARCHAR, BLOB, LONGVARBINARY, * VARBINARY. * <p> * DECIMAL, INTEGER, SMALLINT, TIMESTAMP, CHAR, VARCHAR tested with MySQL and Poolman. * Others not guaranteed. * @param classeDestino * @throws NoSuchFieldException * @throws SecurityException */ private static void putEntry(Map properties, ResultSetMetaData metaData, ResultSet resultSet, int i, Class classeDestino) throws Exception { /* In a perfect universe, this would be enough properties.put( metaData.getColumnName(i), resultSet.getObject(i)); But only String, Timestamp, and Integer seem to get through that way. */ String columnName = metaData.getColumnName(i); // Testa se uma FK /*Field[] fields = classeDestino.getDeclaredFields(); for (int j = 0; j < fields.length; j++) { if (fields[j].getAnnotation(DBFK.class) != null) { properties.put(columnName, resultSet.getString(i)); } }*/ //System.out.println(i+"-"+metaData.getColumnType(i)); switch (metaData.getColumnType(i)) { // http://java.sun.com/j2se/1.3.0/docs/api/java/sql/Types.html case Types.BIGINT: properties.put(columnName, new Long(resultSet.getLong(i))); break; case Types.DATE: properties.put(columnName, resultSet.getDate(i)); break; case Types.DECIMAL: case Types.DOUBLE: properties.put(columnName, new Double(resultSet.getDouble(i))); break; case Types.FLOAT: properties.put(columnName, new Float(resultSet.getFloat(i))); break; case Types.INTEGER: int valor = 0; try { // Se o campo esta vazio d erro valor = resultSet.getInt(i); } catch (SQLException e) { } properties.put(columnName, new Integer(valor)); break; case Types.REAL: properties.put(columnName, new Double(resultSet.getString(i))); break; case Types.SMALLINT: properties.put(columnName, new Short(resultSet.getShort(i))); break; case Types.TIME: properties.put(columnName, resultSet.getTime(i)); break; case Types.TIMESTAMP: properties.put(columnName, resultSet.getTimestamp(i)); break; // :FIXME: Throws java.lang.ClassCastException: java.lang.Integer // :FIXME: with Poolman and MySQL unless use getString. case Types.TINYINT: properties.put(columnName, new Byte(resultSet.getString(i))); break; case Types.CHAR: case Types.CLOB: case Types.VARCHAR: case Types.LONGVARCHAR: // :FIXME: Handle binaries differently? case Types.BLOB: case Types.LONGVARBINARY: case Types.VARBINARY: properties.put(columnName, resultSet.getString(i)); break; /* :FIXME: Add handlers for ARRAY BINARY BIT DISTINCT JAVA_OBJECT NULL NUMERIC OTHER REF STRUCT */ // Otherwise, pass as *String property to be converted default: properties.put(columnName + "String", resultSet.getString(i)); break; } // end switch }
From source file:org.trafodion.rest.util.JdbcT2Util.java
private synchronized JSONArray convertResultSetToJSON(java.sql.ResultSet rs) throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Begin convertResultSetToJSON"); JSONArray json = new JSONArray(); try {//from ww w. ja v a 2 s . c o m java.sql.ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getLong(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.CHAR || rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { //prevent obj.put from removing null key value from JSONObject String s = rs.getString(column_name); if (s == null) obj.put(column_name, new String("")); else obj.put(column_name, rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, rs.getTimestamp(column_name)); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.put(obj); } //end while } catch (SQLException e) { e.printStackTrace(); if (LOG.isDebugEnabled()) LOG.error(e.getMessage()); throw e; } catch (Exception e) { e.printStackTrace(); if (LOG.isDebugEnabled()) LOG.error(e.getMessage()); throw e; } if (LOG.isDebugEnabled()) LOG.debug("End convertResultSetToJSON"); return json; }
From source file:org.apache.synapse.mediators.db.AbstractDBMediator.java
/** * Return a Prepared statement for the given Statement object, which is ready to be executed * * @param stmnt SQL stataement to be executed * @param con The connection to be used * @param msgCtx Current message context * @return a PreparedStatement//from ww w. j a v a 2 s.c om * @throws SQLException on error */ protected PreparedStatement getPreparedStatement(Statement stmnt, Connection con, MessageContext msgCtx) throws SQLException { SynapseLog synLog = getLog(msgCtx); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Getting a connection from DataSource " + getDSName() + " and preparing statement : " + stmnt.getRawStatement()); } if (con == null) { String msg = "Connection from DataSource " + getDSName() + " is null."; log.error(msg); throw new SynapseException(msg); } if (dataSource instanceof BasicDataSource) { BasicDataSource basicDataSource = (BasicDataSource) dataSource; int numActive = basicDataSource.getNumActive(); int numIdle = basicDataSource.getNumIdle(); String connectionId = Integer.toHexString(con.hashCode()); DBPoolView dbPoolView = getDbPoolView(); if (dbPoolView != null) { dbPoolView.setNumActive(numActive); dbPoolView.setNumIdle(numIdle); dbPoolView.updateConnectionUsage(connectionId); } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("[ DB Connection : " + con + " ]"); synLog.traceOrDebug("[ DB Connection instance identifier : " + connectionId + " ]"); synLog.traceOrDebug("[ Number of Active Connection : " + numActive + " ]"); synLog.traceOrDebug("[ Number of Idle Connection : " + numIdle + " ]"); } } PreparedStatement ps = con.prepareStatement(stmnt.getRawStatement()); // set parameters if any List<Statement.Parameter> params = stmnt.getParameters(); int column = 1; for (Statement.Parameter param : params) { if (param == null) { continue; } String value = (param.getPropertyName() != null ? param.getPropertyName() : param.getXpath().stringValueOf(msgCtx)); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Setting as parameter : " + column + " value : " + value + " as JDBC Type : " + param.getType() + "(see java.sql.Types for valid " + "types)"); } switch (param.getType()) { // according to J2SE 1.5 /docs/guide/jdbc/getstart/mapping.html case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: { if (value != null && value.length() != 0) { ps.setString(column++, value); } else { ps.setString(column++, null); } break; } case Types.NUMERIC: case Types.DECIMAL: { if (value != null && value.length() != 0) { ps.setBigDecimal(column++, new BigDecimal(value)); } else { ps.setBigDecimal(column++, null); } break; } case Types.BIT: { if (value != null && value.length() != 0) { ps.setBoolean(column++, Boolean.parseBoolean(value)); } else { ps.setNull(column++, Types.BIT); } break; } case Types.TINYINT: { if (value != null && value.length() != 0) { ps.setByte(column++, Byte.parseByte(value)); } else { ps.setNull(column++, Types.TINYINT); } break; } case Types.SMALLINT: { if (value != null && value.length() != 0) { ps.setShort(column++, Short.parseShort(value)); } else { ps.setNull(column++, Types.SMALLINT); } break; } case Types.INTEGER: { if (value != null && value.length() != 0) { ps.setInt(column++, Integer.parseInt(value)); } else { ps.setNull(column++, Types.INTEGER); } break; } case Types.BIGINT: { if (value != null && value.length() != 0) { ps.setLong(column++, Long.parseLong(value)); } else { ps.setNull(column++, Types.BIGINT); } break; } case Types.REAL: { if (value != null && value.length() != 0) { ps.setFloat(column++, Float.parseFloat(value)); } else { ps.setNull(column++, Types.REAL); } break; } case Types.FLOAT: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.FLOAT); } break; } case Types.DOUBLE: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.DOUBLE); } break; } // skip BINARY, VARBINARY and LONGVARBINARY case Types.DATE: { if (value != null && value.length() != 0) { ps.setDate(column++, Date.valueOf(value)); } else { ps.setNull(column++, Types.DATE); } break; } case Types.TIME: { if (value != null && value.length() != 0) { ps.setTime(column++, Time.valueOf(value)); } else { ps.setNull(column++, Types.TIME); } break; } case Types.TIMESTAMP: { if (value != null && value.length() != 0) { ps.setTimestamp(column++, Timestamp.valueOf(value)); } else { ps.setNull(column++, Types.TIMESTAMP); } break; } // skip CLOB, BLOB, ARRAY, DISTINCT, STRUCT, REF, JAVA_OBJECT default: { String msg = "Trying to set an un-supported JDBC Type : " + param.getType() + " against column : " + column + " and statement : " + stmnt.getRawStatement() + " used by a DB mediator against DataSource : " + getDSName() + " (see java.sql.Types for valid type values)"; handleException(msg, msgCtx); } } } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Successfully prepared statement : " + stmnt.getRawStatement() + " against DataSource : " + getDSName()); } return ps; }