List of usage examples for java.sql Types NUMERIC
int NUMERIC
To view the source code for java.sql Types NUMERIC.
Click Source Link
The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type NUMERIC.
From source file:org.apache.empire.db.codegen.CodeGenParser.java
/** * converts a SQL DataType to a EmpireDataType */// w w w. j av a 2 s .c om private DataType getEmpireDataType(int sqlType) { DataType empireType = DataType.UNKNOWN; switch (sqlType) { case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: case Types.BIGINT: empireType = DataType.INTEGER; break; case Types.VARCHAR: empireType = DataType.TEXT; break; case Types.DATE: empireType = DataType.DATE; break; case Types.TIMESTAMP: case Types.TIME: empireType = DataType.DATETIME; break; case Types.CHAR: empireType = DataType.CHAR; break; case Types.DOUBLE: case Types.FLOAT: case Types.REAL: empireType = DataType.FLOAT; break; case Types.DECIMAL: case Types.NUMERIC: empireType = DataType.DECIMAL; break; case Types.BIT: case Types.BOOLEAN: empireType = DataType.BOOL; break; case Types.CLOB: case Types.LONGVARCHAR: empireType = DataType.CLOB; break; case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: case Types.BLOB: empireType = DataType.BLOB; break; default: empireType = DataType.UNKNOWN; log.warn("SQL column type " + sqlType + " not supported."); } log.debug("Mapping date type " + String.valueOf(sqlType) + " to " + empireType); return empireType; }
From source file:org.apache.ddlutils.io.RoundtripTestBase.java
/** * Asserts that the two given columns are equal. * /*from w ww.j ava 2s. co m*/ * @param expected The expected column * @param actual The actual column */ protected void assertEquals(Column expected, Column actual) { if (_useDelimitedIdentifiers) { assertEquals("Column names do not match.", getPlatform().getSqlBuilder().shortenName(expected.getName(), getSqlBuilder().getMaxColumnNameLength()), getPlatform().getSqlBuilder().shortenName(actual.getName(), getSqlBuilder().getMaxColumnNameLength())); } else { assertEquals("Column names do not match (ignoring case).", getPlatform().getSqlBuilder().shortenName(expected.getName().toUpperCase(), getSqlBuilder().getMaxColumnNameLength()), getPlatform().getSqlBuilder().shortenName(actual.getName().toUpperCase(), getSqlBuilder().getMaxColumnNameLength())); } assertEquals("Primary key status not the same for column " + actual.getName() + ".", expected.isPrimaryKey(), actual.isPrimaryKey()); assertEquals("Required status not the same for column " + actual.getName() + ".", expected.isRequired(), actual.isRequired()); if (getPlatformInfo().getIdentityStatusReadingSupported()) { // we're only comparing this if the platform can actually read the // auto-increment status back from an existing database assertEquals("Auto-increment status not the same for column " + actual.getName() + ".", expected.isAutoIncrement(), actual.isAutoIncrement()); } assertEquals("Type not the same for column " + actual.getName() + ".", expected.getType(), actual.getType()); assertEquals("Type code not the same for column " + actual.getName() + ".", expected.getTypeCode(), actual.getTypeCode()); assertEquals("Parsed default values do not match for column " + actual.getName() + ".", expected.getParsedDefaultValue(), actual.getParsedDefaultValue()); // comparing the size makes only sense for types where it is relevant if ((expected.getTypeCode() == Types.NUMERIC) || (expected.getTypeCode() == Types.DECIMAL)) { assertEquals("Precision not the same for column " + actual.getName() + ".", expected.getSizeAsInt(), actual.getSizeAsInt()); assertEquals("Scale not the same for column " + actual.getName() + ".", expected.getScale(), actual.getScale()); } else if ((expected.getTypeCode() == Types.CHAR) || (expected.getTypeCode() == Types.VARCHAR) || (expected.getTypeCode() == Types.BINARY) || (expected.getTypeCode() == Types.VARBINARY)) { assertEquals("Size not the same for column " + actual.getName() + ".", expected.getSize(), actual.getSize()); } }
From source file:org.nuxeo.ecm.core.storage.sql.db.H2Fulltext.java
protected static String asString(Object data, int type) throws SQLException { if (data == null) { return ""; }/* www . ja v a 2s. c o m*/ switch (type) { case Types.BIT: case Types.BOOLEAN: case Types.INTEGER: case Types.BIGINT: case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.NUMERIC: case Types.REAL: case Types.SMALLINT: case Types.TINYINT: case Types.DATE: case Types.TIME: case Types.TIMESTAMP: case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: return data.toString(); case Types.CLOB: try { if (data instanceof Clob) { data = ((Clob) data).getCharacterStream(); } return IOUtils.readStringAndClose((Reader) data, -1); } catch (IOException e) { throw DbException.convert(e); } case Types.VARBINARY: case Types.LONGVARBINARY: case Types.BINARY: case Types.JAVA_OBJECT: case Types.OTHER: case Types.BLOB: case Types.STRUCT: case Types.REF: case Types.NULL: case Types.ARRAY: case Types.DATALINK: case Types.DISTINCT: throw new SQLException("Unsupported column data type: " + type); default: return ""; } }
From source file:org.moqui.impl.entity.EntityJavaUtil.java
public static void setPreparedStatementValue(PreparedStatement ps, int index, Object value, FieldInfo fi, boolean useBinaryTypeForBlob, EntityFacade efi) throws EntityException { try {/*from w ww. j a v a 2 s.com*/ // allow setting, and searching for, String values for all types; JDBC driver should handle this okay if (value instanceof CharSequence) { ps.setString(index, value.toString()); } else { switch (fi.typeValue) { case 1: if (value != null) { ps.setString(index, value.toString()); } else { ps.setNull(index, Types.VARCHAR); } break; case 2: if (value != null) { Class valClass = value.getClass(); if (valClass == Timestamp.class) { ps.setTimestamp(index, (Timestamp) value, efi.getCalendarForTzLc()); } else if (valClass == java.sql.Date.class) { ps.setDate(index, (java.sql.Date) value, efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setTimestamp(index, new Timestamp(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date-time (Timestamp) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.TIMESTAMP); } break; case 3: Time tm = (Time) value; // logger.warn("=================== setting time tm=${tm} tm long=${tm.getTime()}, cal=${cal}") if (value != null) { ps.setTime(index, tm, efi.getCalendarForTzLc()); } else { ps.setNull(index, Types.TIME); } break; case 4: if (value != null) { Class valClass = value.getClass(); if (valClass == java.sql.Date.class) { java.sql.Date dt = (java.sql.Date) value; // logger.warn("=================== setting date dt=${dt} dt long=${dt.getTime()}, cal=${cal}") ps.setDate(index, dt, efi.getCalendarForTzLc()); } else if (valClass == Timestamp.class) { ps.setDate(index, new java.sql.Date(((Timestamp) value).getTime()), efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setDate(index, new java.sql.Date(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.DATE); } break; case 5: if (value != null) { ps.setInt(index, ((Number) value).intValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 6: if (value != null) { ps.setLong(index, ((Number) value).longValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 7: if (value != null) { ps.setFloat(index, ((Number) value).floatValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 8: if (value != null) { ps.setDouble(index, ((Number) value).doubleValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 9: if (value != null) { Class valClass = value.getClass(); // most common cases BigDecimal, Double, Float; then allow any Number if (valClass == BigDecimal.class) { ps.setBigDecimal(index, (BigDecimal) value); } else if (valClass == Double.class) { ps.setDouble(index, (Double) value); } else if (valClass == Float.class) { ps.setFloat(index, (Float) value); } else if (value instanceof Number) { ps.setDouble(index, ((Number) value).doubleValue()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for number-decimal (BigDecimal) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.NUMERIC); } break; case 10: if (value != null) { ps.setBoolean(index, (Boolean) value); } else { ps.setNull(index, Types.BOOLEAN); } break; case 11: if (value != null) { try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(os); oos.writeObject(value); oos.close(); byte[] buf = os.toByteArray(); os.close(); ByteArrayInputStream is = new ByteArrayInputStream(buf); ps.setBinaryStream(index, is, buf.length); is.close(); } catch (IOException ex) { throw new EntityException( "Error setting serialized object, for field " + fi.entityName + "." + fi.name, ex); } } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } break; case 12: if (value instanceof byte[]) { ps.setBytes(index, (byte[]) value); /* } else if (value instanceof ArrayList) { ArrayList valueAl = (ArrayList) value; byte[] theBytes = new byte[valueAl.size()]; valueAl.toArray(theBytes); ps.setBytes(index, theBytes); */ } else if (value instanceof ByteBuffer) { ByteBuffer valueBb = (ByteBuffer) value; ps.setBytes(index, valueBb.array()); } else if (value instanceof Blob) { Blob valueBlob = (Blob) value; // calling setBytes instead of setBlob // ps.setBlob(index, (Blob) value) // Blob blb = value ps.setBytes(index, valueBlob.getBytes(1, (int) valueBlob.length())); } else { if (value != null) { throw new IllegalArgumentException("Type not supported for BLOB field: " + value.getClass().getName() + ", for field " + fi.entityName + "." + fi.name); } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } } break; case 13: if (value != null) { ps.setClob(index, (Clob) value); } else { ps.setNull(index, Types.CLOB); } break; case 14: if (value != null) { ps.setTimestamp(index, (Timestamp) value); } else { ps.setNull(index, Types.TIMESTAMP); } break; // TODO: is this the best way to do collections and such? case 15: if (value != null) { ps.setObject(index, value, Types.JAVA_OBJECT); } else { ps.setNull(index, Types.JAVA_OBJECT); } break; } } } catch (SQLException sqle) { throw new EntityException("SQL Exception while setting value [" + value + "](" + (value != null ? value.getClass().getName() : "null") + "), type " + fi.type + ", for field " + fi.entityName + "." + fi.name + ": " + sqle.toString(), sqle); } catch (Exception e) { throw new EntityException( "Error while setting value for field " + fi.entityName + "." + fi.name + ": " + e.toString(), e); } }
From source file:com.glaf.core.jdbc.QueryHelper.java
/** * @param conn/* w w w .jav a2 s . co m*/ * ? * @param sqlExecutor * ? * @param start * 0 * @param pageSize * ? * @return */ @SuppressWarnings("unchecked") public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor, int start, int pageSize) { if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) { throw new RuntimeException(" SQL statement illegal "); } List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(); String sql = sqlExecutor.getSql(); PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; boolean supportsPhysicalPage = false; try { Dialect dialect = DBConfiguration.getDatabaseDialect(conn); if (dialect != null && dialect.supportsPhysicalPage()) { supportsPhysicalPage = true; sql = dialect.getLimitString(sql, start, pageSize); logger.debug("sql=" + sqlExecutor.getSql()); logger.debug(">>sql=" + sql); } psmt = conn.prepareStatement(sql); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); logger.debug(">>values=" + values); } rs = psmt.executeQuery(); if (conf.getBoolean("useMyBatisResultHandler", false)) { resultList = this.getResults(rs); } else { rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setIndex(i); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); if (column.getScale() == 0 && sqlType == Types.NUMERIC) { column.setJavaType("Long"); } column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase())); columns.add(column); } if (!supportsPhysicalPage) { logger.debug("---------------------skipRows:" + start); this.skipRows(rs, start, pageSize); } logger.debug("---------------------columns:" + columns.size()); logger.debug("---------------------start:" + start); logger.debug("---------------------pageSize:" + pageSize); // int index = 0; while (rs.next()) { // index++; // logger.debug("---------------------row index:" + index); Map<String, Object> rowMap = new HashMap<String, Object>(); Iterator<ColumnDefinition> iterator = columns.iterator(); while (iterator.hasNext()) { ColumnDefinition column = iterator.next(); String columnLabel = column.getColumnLabel(); String columnName = column.getColumnName(); if (StringUtils.isEmpty(columnName)) { columnName = column.getColumnLabel(); } columnName = columnName.toLowerCase(); String javaType = column.getJavaType(); if ("String".equals(javaType)) { String value = rs.getString(column.getIndex()); if (value != null) { value = value.trim(); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } else if ("Integer".equals(javaType)) { try { Integer value = rs.getInt(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("integer:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.intValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.intValue()); } } else if ("Long".equals(javaType)) { try { Long value = rs.getLong(column.getIndex()); rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("long:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.longValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.longValue()); } } else if ("Double".equals(javaType)) { try { Double d = rs.getDouble(column.getIndex()); rowMap.put(columnName, d); rowMap.put(columnLabel, rowMap.get(columnName)); } catch (Exception e) { String str = rs.getString(column.getIndex()); logger.error("double:" + str); str = StringTools.replace(str, "$", ""); str = StringTools.replace(str, "", ""); str = StringTools.replace(str, ",", ""); NumberFormat fmt = NumberFormat.getInstance(); Number num = fmt.parse(str); rowMap.put(columnName, num.doubleValue()); rowMap.put(columnLabel, rowMap.get(columnName)); logger.debug("?:" + num.doubleValue()); } } else if ("Boolean".equals(javaType)) { rowMap.put(columnName, rs.getBoolean(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Date".equals(javaType)) { rowMap.put(columnName, rs.getTimestamp(column.getIndex())); rowMap.put(columnLabel, rowMap.get(columnName)); } else if ("Blob".equals(javaType)) { } else { Object value = rs.getObject(column.getIndex()); if (value != null) { if (value instanceof String) { value = (String) value.toString().trim(); } rowMap.put(columnName, value); rowMap.put(columnLabel, rowMap.get(columnName)); } } } resultList.add(rowMap); } } logger.debug(">resultList size = " + resultList.size()); return resultList; } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(psmt); JdbcUtils.close(rs); } }
From source file:com.netspective.axiom.sql.StoredProcedureParameter.java
/** * Extract the OUT parameter values from the callable statment and * assign them to the value of the parameter. *///www.j a va2s . c o m public void extract(ConnectionContext cc, CallableStatement stmt) throws SQLException { if (getType().getValueIndex() == StoredProcedureParameter.Type.IN) return; int index = this.getIndex(); QueryParameterType paramType = getSqlType(); int jdbcType = paramType.getJdbcType(); String identifier = paramType.getIdentifier(); // result sets are special if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER)) { ResultSet rs = (ResultSet) stmt.getObject(index); QueryResultSet qrs = new QueryResultSet(getParent().getProcedure(), cc, rs); value.getValue(cc).setValue(qrs); return; } switch (jdbcType) { case Types.VARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case Types.INTEGER: value.getValue(cc).setValue(new Integer(stmt.getInt(index))); break; case Types.DOUBLE: value.getValue(cc).setValue(new Double(stmt.getDouble(index))); break; case Types.CLOB: Clob clob = stmt.getClob(index); value.getValue(cc).setTextValue(clob.getSubString(1, (int) clob.length())); break; case java.sql.Types.ARRAY: Array array = stmt.getArray(index); value.getValue(cc).setValue(array); break; case java.sql.Types.BIGINT: long bigint = stmt.getLong(index); value.getValue(cc).setValue(new Long(bigint)); break; case java.sql.Types.BINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.BIT: boolean bit = stmt.getBoolean(index); value.getValue(cc).setValue(new Boolean(bit)); case java.sql.Types.BLOB: value.getValue(cc).setValue(stmt.getBlob(index)); break; case java.sql.Types.CHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case java.sql.Types.DATE: value.getValue(cc).setValue(stmt.getDate(index)); break; case java.sql.Types.DECIMAL: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.DISTINCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.FLOAT: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; case java.sql.Types.JAVA_OBJECT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.LONGVARBINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.LONGVARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; //case java.sql.Types.NULL: // value.getValue(cc).setValue(null); // break; case java.sql.Types.NUMERIC: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.OTHER: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.REAL: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; //case java.sql.Types.REF: // Ref ref = stmt.getRef(index); // break; case java.sql.Types.SMALLINT: short sh = stmt.getShort(index); value.getValue(cc).setValue(new Short(sh)); break; case java.sql.Types.STRUCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.TIME: value.getValue(cc).setValue(stmt.getTime(index)); break; case java.sql.Types.TIMESTAMP: value.getValue(cc).setValue(stmt.getTimestamp(index)); break; case java.sql.Types.TINYINT: byte b = stmt.getByte(index); value.getValue(cc).setValue(new Byte(b)); break; case java.sql.Types.VARBINARY: value.getValue(cc).setValue(stmt.getBytes(index)); break; default: throw new RuntimeException( "Unknown JDBC Type set for stored procedure parameter '" + this.getName() + "'."); } }
From source file:ca.sqlpower.architect.swingui.TestPlayPen.java
/** * This tests that copying and pasting a new table into * the play pen works. This also confirms that the new table * copied is not the source of the columns. *///from www. j a va 2s . c o m public void testPasteNewTable() throws Exception { SQLTable table = new SQLTable(null, "NewTable", "Remarks", "TABLE", true); table.addColumn(new SQLColumn(table, "NewCol1", Types.VARCHAR, 50, 0)); table.addColumn(new SQLColumn(table, "NewCol2", Types.NUMERIC, 50, 0)); DuplicateProperties duplicateProperties = ASUtils.createDuplicateProperties(pp.getSession(), table); duplicateProperties.setDefaultTransferStyle(TransferStyles.COPY); pp.importTableCopy(table, new Point(0, 0), duplicateProperties); assertEquals(1, pp.getTables().size()); SQLTable copy = pp.getTables().get(0); assertEquals("NewTable", copy.getName()); assertEquals(2, copy.getColumns().size()); assertTrue(copy.getColumnByName("NewCol1") != null); assertTrue(copy.getColumnByName("NewCol2") != null); assertNull(copy.getColumnByName("NewCol1").getSourceColumn()); assertNull(copy.getColumnByName("NewCol2").getSourceColumn()); }
From source file:architecture.ee.web.community.page.dao.jdbc.JdbcPageDao.java
private void updatePageVersion(Page page, int prevVersionId) { Date now = Calendar.getInstance().getTime(); if (page.getPageState() == PageState.PUBLISHED) { getExtendedJdbcTemplate().update( getBoundSql("ARCHITECTURE_COMMUNITY.UPDATE_PAGE_STATE_TO_ARCHIVED").getSql(), new SqlParameterValue(Types.NUMERIC, page.getPageId()), new SqlParameterValue(Types.NUMERIC, page.getVersionId())); }//from w w w .j av a2 s . c o m if (page.getVersionId() > 0) { page.setModifiedDate(now); long modifierId = page.getUser().getUserId() <= 0L ? page.getUser().getUserId() : page.getUser().getUserId(); // update page version getExtendedJdbcTemplate().update(getBoundSql("ARCHITECTURE_COMMUNITY.UPDATE_PAGE_VERSION").getSql(), new SqlParameterValue(Types.VARCHAR, page.getPageState().name().toLowerCase()), new SqlParameterValue(Types.VARCHAR, page.getTitle()), new SqlParameterValue(Types.VARCHAR, page.getSummary()), new SqlParameterValue(Types.NUMERIC, modifierId), new SqlParameterValue(Types.DATE, page.getModifiedDate()), new SqlParameterValue(Types.NUMERIC, page.getPageId()), new SqlParameterValue(Types.NUMERIC, page.getVersionId())); } }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result/*from w w w .j a va 2 s. c om*/ */ public SqlStatementResult execute(DatabaseExecutable databaseExecutable) throws SQLException { if (!prepared()) { return statementResult; } ProcedureParameter[] param = databaseExecutable.getParametersArray(); Arrays.sort(param, new ProcedureParameterSorter()); String procQuery = null; boolean hasOut = false; boolean hasParameters = (param != null && param.length > 0); List<ProcedureParameter> outs = null; List<ProcedureParameter> ins = null; if (hasParameters) { // split the params into ins and outs outs = new ArrayList<ProcedureParameter>(); ins = new ArrayList<ProcedureParameter>(); int type = -1; for (int i = 0; i < param.length; i++) { type = param[i].getType(); if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { // add to the ins list ins.add(param[i]); } else if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { // add to the outs list outs.add(param[i]); } } char QUESTION_MARK = '?'; String COMMA = ", "; // init the string buffer StringBuilder sb = new StringBuilder("{ "); if (!outs.isEmpty()) { // build the out params place holders for (int i = 0, n = outs.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" = "); } sb.append(" call "); if (databaseExecutable.supportCatalogOrSchemaInFunctionOrProcedureCalls()) { String namePrefix = null; if (databaseExecutable.supportCatalogInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getCatalogName(); } if (databaseExecutable.supportSchemaInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getSchemaName(); } if (namePrefix != null) { sb.append(namePrefix).append('.'); } } sb.append(databaseExecutable.getName()).append("( "); // build the ins params place holders for (int i = 0, n = ins.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" ) }"); // determine if we have out params hasOut = !(outs.isEmpty()); procQuery = sb.toString(); } else { StringBuilder sb = new StringBuilder(); sb.append("{ call "); if (databaseExecutable.getSchemaName() != null) { sb.append(databaseExecutable.getSchemaName()).append('.'); } sb.append(databaseExecutable.getName()).append("( ) }"); procQuery = sb.toString(); } //Log.debug(procQuery); // null value literal String NULL = "null"; // clear any warnings conn.clearWarnings(); Log.info("Executing: " + procQuery); CallableStatement cstmnt = null; try { // prepare the statement cstmnt = conn.prepareCall(procQuery); stmnt = cstmnt; } catch (SQLException e) { handleException(e); statementResult.setSqlException(e); return statementResult; } // check if we are passing parameters if (hasParameters) { // the parameter index counter int index = 1; // the java.sql.Type value int dataType = -1; // the parameter input value String value = null; // register the out params for (int i = 0, n = outs.size(); i < n; i++) { //Log.debug("setting out at index: " + index); cstmnt.registerOutParameter(index, outs.get(i).getDataType()); index++; } try { // register the in params for (int i = 0, n = ins.size(); i < n; i++) { ProcedureParameter procedureParameter = ins.get(i); value = procedureParameter.getValue(); dataType = procedureParameter.getDataType(); // try infer a type if OTHER if (dataType == Types.OTHER) { // checking only for bit/bool for now if (isTrueFalse(value)) { dataType = Types.BOOLEAN; } else if (isBit(value)) { dataType = Types.BIT; value = value.substring(2, value.length() - 1); } } if (MiscUtils.isNull(value) || value.equalsIgnoreCase(NULL)) { cstmnt.setNull(index, dataType); } else { switch (dataType) { case Types.TINYINT: byte _byte = Byte.valueOf(value).byteValue(); cstmnt.setShort(index, _byte); break; case Types.SMALLINT: short _short = Short.valueOf(value).shortValue(); cstmnt.setShort(index, _short); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: cstmnt.setString(index, value); break; case Types.BIT: case Types.BOOLEAN: boolean _boolean = false; if (NumberUtils.isNumber(value)) { int number = Integer.valueOf(value); if (number > 0) { _boolean = true; } } else { _boolean = Boolean.valueOf(value).booleanValue(); } cstmnt.setBoolean(index, _boolean); break; case Types.BIGINT: long _long = Long.valueOf(value).longValue(); cstmnt.setLong(index, _long); break; case Types.INTEGER: int _int = Integer.valueOf(value).intValue(); cstmnt.setInt(index, _int); break; case Types.REAL: float _float = Float.valueOf(value).floatValue(); cstmnt.setFloat(index, _float); break; case Types.NUMERIC: case Types.DECIMAL: cstmnt.setBigDecimal(index, new BigDecimal(value)); break; /* case Types.DATE: case Types.TIMESTAMP: case Types.TIME: cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value)); */ case Types.FLOAT: case Types.DOUBLE: double _double = Double.valueOf(value).doubleValue(); cstmnt.setDouble(index, _double); break; default: cstmnt.setObject(index, value); } } // increment the index index++; } } catch (Exception e) { statementResult.setOtherErrorMessage(e.getClass().getName() + ": " + e.getMessage()); return statementResult; } } /* test creating function for postgres: CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT; */ try { cstmnt.clearWarnings(); boolean hasResultSet = cstmnt.execute(); Map<String, Object> results = new HashMap<String, Object>(); if (hasOut) { // incrementing index int index = 1; // return value from each registered out String returnValue = null; for (int i = 0; i < param.length; i++) { int type = param[i].getType(); int dataType = param[i].getDataType(); if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { switch (dataType) { case Types.TINYINT: returnValue = Byte.toString(cstmnt.getByte(index)); break; case Types.SMALLINT: returnValue = Short.toString(cstmnt.getShort(index)); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: returnValue = cstmnt.getString(index); break; case Types.BIT: case Types.BOOLEAN: returnValue = Boolean.toString(cstmnt.getBoolean(index)); break; case Types.INTEGER: returnValue = Integer.toString(cstmnt.getInt(index)); break; case Types.BIGINT: returnValue = Long.toString(cstmnt.getLong(index)); break; case Types.REAL: returnValue = Float.toString(cstmnt.getFloat(index)); break; case Types.NUMERIC: case Types.DECIMAL: returnValue = cstmnt.getBigDecimal(index).toString(); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: returnValue = cstmnt.getDate(index).toString(); break; case Types.FLOAT: case Types.DOUBLE: returnValue = Double.toString(cstmnt.getDouble(index)); break; } if (returnValue == null) { returnValue = "NULL"; } results.put(param[i].getName(), returnValue); index++; } } } if (!hasResultSet) { statementResult.setUpdateCount(cstmnt.getUpdateCount()); } else { statementResult.setResultSet(cstmnt.getResultSet()); } useCount++; statementResult.setOtherResult(results); } catch (SQLException e) { statementResult.setSqlException(e); } catch (Exception e) { statementResult.setMessage(e.getMessage()); } return statementResult; }
From source file:com.streamsets.pipeline.lib.jdbc.multithread.TableContextUtil.java
public static String generateNextPartitionOffset(TableContext tableContext, String column, String offset) { final String partitionSize = tableContext.getOffsetColumnToPartitionOffsetAdjustments().get(column); switch (tableContext.getOffsetColumnToType().get(column)) { case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: final int int1 = Integer.parseInt(offset); final int int2 = Integer.parseInt(partitionSize); return String.valueOf(int1 + int2); case Types.TIMESTAMP: final Timestamp timestamp1 = getTimestampForOffsetValue(offset); final long timestampAdj = Long.parseLong(partitionSize); final Timestamp timestamp2 = Timestamp.from(timestamp1.toInstant().plusMillis(timestampAdj)); return getOffsetValueForTimestamp(timestamp2); case Types.BIGINT: // TIME, DATE are represented as long (epoch) case Types.TIME: case Types.DATE: final long long1 = Long.parseLong(offset); final long long2 = Long.parseLong(partitionSize); return String.valueOf(long1 + long2); case Types.FLOAT: case Types.REAL: final float float1 = Float.parseFloat(offset); final float float2 = Float.parseFloat(partitionSize); return String.valueOf(float1 + float2); case Types.DOUBLE: final double double1 = Double.parseDouble(offset); final double double2 = Double.parseDouble(partitionSize); return String.valueOf(double1 + double2); case Types.NUMERIC: case Types.DECIMAL: final BigDecimal decimal1 = new BigDecimal(offset); final BigDecimal decimal2 = new BigDecimal(partitionSize); return decimal1.add(decimal2).toString(); }//from w w w . ja va 2 s. co m return null; }