List of usage examples for java.sql Types BOOLEAN
int BOOLEAN
To view the source code for java.sql Types BOOLEAN.
Click Source Link
BOOLEAN. From source file:com.feedzai.commons.sql.abstraction.engine.AbstractDatabaseEngine.java
/** * Maps the database type to {@link DbColumnType}. If there's no mapping a {@link DbColumnType#UNMAPPED} is returned. * * @param type The SQL type from {@link java.sql.Types}. * @return The {@link DbColumnType}./*from ww w . j a va2s .co m*/ */ protected DbColumnType toPdbType(final int type) { switch (type) { case Types.ARRAY: return DbColumnType.UNMAPPED; case Types.BIGINT: return DbColumnType.LONG; case Types.BINARY: return DbColumnType.BLOB; case Types.BIT: return DbColumnType.BOOLEAN; case Types.BLOB: return DbColumnType.BLOB; case Types.BOOLEAN: return DbColumnType.BOOLEAN; case Types.CHAR: return DbColumnType.STRING; case Types.CLOB: return DbColumnType.STRING; case Types.DATALINK: return DbColumnType.UNMAPPED; case Types.DATE: return DbColumnType.UNMAPPED; case Types.DECIMAL: return DbColumnType.DOUBLE; case Types.DISTINCT: return DbColumnType.UNMAPPED; case Types.DOUBLE: return DbColumnType.DOUBLE; case Types.FLOAT: return DbColumnType.DOUBLE; case Types.INTEGER: return DbColumnType.INT; case Types.JAVA_OBJECT: return DbColumnType.BLOB; case Types.LONGNVARCHAR: return DbColumnType.STRING; case Types.LONGVARBINARY: return DbColumnType.BLOB; case Types.LONGVARCHAR: return DbColumnType.STRING; case Types.NCHAR: return DbColumnType.STRING; case Types.NCLOB: return DbColumnType.STRING; case Types.NULL: return DbColumnType.UNMAPPED; case Types.NUMERIC: return DbColumnType.DOUBLE; case Types.NVARCHAR: return DbColumnType.STRING; case Types.OTHER: return DbColumnType.UNMAPPED; case Types.REAL: return DbColumnType.DOUBLE; case Types.REF: return DbColumnType.UNMAPPED; case Types.ROWID: return DbColumnType.STRING; case Types.SMALLINT: return DbColumnType.INT; case Types.SQLXML: return DbColumnType.STRING; case Types.STRUCT: return DbColumnType.UNMAPPED; case Types.TIME: return DbColumnType.UNMAPPED; case Types.TIMESTAMP: return DbColumnType.LONG; case Types.TINYINT: return DbColumnType.INT; case Types.VARBINARY: return DbColumnType.BLOB; case Types.VARCHAR: return DbColumnType.STRING; default: return DbColumnType.UNMAPPED; } }
From source file:helma.objectmodel.db.NodeManager.java
/** * Create a new Node from a ResultSet./*from w ww. ja va2s.c om*/ */ public Node createNode(DbMapping dbm, ResultSet rs, DbColumn[] columns, int offset) throws SQLException, IOException, ClassNotFoundException { HashMap propBuffer = new HashMap(); String id = null; String name = null; String protoName = dbm.getTypeName(); DbMapping dbmap = dbm; Node node = new Node(safe); for (int i = 0; i < columns.length; i++) { int columnNumber = i + 1 + offset; // set prototype? if (columns[i].isPrototypeField()) { String protoId = rs.getString(columnNumber); protoName = dbm.getPrototypeName(protoId); if (protoName != null) { dbmap = getDbMapping(protoName); if (dbmap == null) { // invalid prototype name! app.logError("No prototype defined for prototype mapping \"" + protoName + "\" - Using default prototype \"" + dbm.getTypeName() + "\"."); dbmap = dbm; protoName = dbmap.getTypeName(); } } } // set id? if (columns[i].isIdField()) { id = rs.getString(columnNumber); // if id == null, the object doesn't actually exist - return null if (id == null) { return null; } } // set name? if (columns[i].isNameField()) { name = rs.getString(columnNumber); } Property newprop = new Property(node); switch (columns[i].getType()) { case Types.BIT: case Types.BOOLEAN: newprop.setBooleanValue(rs.getBoolean(columnNumber)); break; case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: newprop.setIntegerValue(rs.getLong(columnNumber)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: newprop.setFloatValue(rs.getDouble(columnNumber)); break; case Types.DECIMAL: case Types.NUMERIC: BigDecimal num = rs.getBigDecimal(columnNumber); if (num == null) { break; } if (num.scale() > 0) { newprop.setFloatValue(num.doubleValue()); } else { newprop.setIntegerValue(num.longValue()); } break; case Types.VARBINARY: case Types.BINARY: newprop.setJavaObjectValue(rs.getBytes(columnNumber)); break; case Types.BLOB: case Types.LONGVARBINARY: { InputStream in = rs.getBinaryStream(columnNumber); if (in == null) { break; } ByteArrayOutputStream bout = new ByteArrayOutputStream(); byte[] buffer = new byte[2048]; int read; while ((read = in.read(buffer)) > -1) { bout.write(buffer, 0, read); } newprop.setJavaObjectValue(bout.toByteArray()); } break; case Types.LONGVARCHAR: try { newprop.setStringValue(rs.getString(columnNumber)); } catch (SQLException x) { Reader in = rs.getCharacterStream(columnNumber); if (in == null) { newprop.setStringValue(null); break; } StringBuffer out = new StringBuffer(); char[] buffer = new char[2048]; int read; while ((read = in.read(buffer)) > -1) { out.append(buffer, 0, read); } newprop.setStringValue(out.toString()); } break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: newprop.setStringValue(rs.getString(columnNumber)); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: newprop.setDateValue(rs.getTimestamp(columnNumber)); break; case Types.NULL: newprop.setStringValue(null); break; case Types.CLOB: Clob cl = rs.getClob(columnNumber); if (cl == null) { newprop.setStringValue(null); break; } char[] c = new char[(int) cl.length()]; Reader isr = cl.getCharacterStream(); isr.read(c); newprop.setStringValue(String.copyValueOf(c)); break; default: newprop.setStringValue(rs.getString(columnNumber)); break; } if (rs.wasNull()) { newprop.setStringValue(null); } propBuffer.put(columns[i].getName(), newprop); // mark property as clean, since it's fresh from the db newprop.dirty = false; } if (id == null) { return null; } else { Transactor tx = Transactor.getInstance(); if (tx != null) { // Check if the node is already registered with the transactor - // it may be in the process of being DELETED, but do return the // new node if the old one has been marked as INVALID. DbKey key = new DbKey(dbmap, id); Node dirtyNode = tx.getDirtyNode(key); if (dirtyNode != null && dirtyNode.getState() != Node.INVALID) { return dirtyNode; } } } Hashtable propMap = new Hashtable(); DbColumn[] columns2 = dbmap.getColumns(); for (int i = 0; i < columns2.length; i++) { Relation rel = columns2[i].getRelation(); if (rel != null && rel.isPrimitiveOrReference()) { Property prop = (Property) propBuffer.get(columns2[i].getName()); if (prop == null) { continue; } prop.setName(rel.propName); // if the property is a pointer to another node, change the property type to NODE if (rel.isReference() && rel.usesPrimaryKey()) { // FIXME: References to anything other than the primary key are not supported prop.convertToNodeReference(rel); } propMap.put(rel.propName, prop); } } node.init(dbmap, id, name, protoName, propMap); return node; }
From source file:org.dspace.storage.rdbms.MockDatabaseManager.java
@Mock private static void loadParameters(PreparedStatement statement, Collection<ColumnInfo> columns, TableRow row) throws SQLException { int count = 0; for (ColumnInfo info : columns) { count++;//from w w w .j a va2s.c o m String column = info.getName(); int jdbctype = info.getType(); if (row.isColumnNull(column)) { statement.setNull(count, jdbctype); } else { switch (jdbctype) { case Types.BIT: case Types.BOOLEAN: statement.setBoolean(count, row.getBooleanColumn(column)); break; case Types.INTEGER: if (isOracle) { statement.setLong(count, row.getLongColumn(column)); } else { statement.setInt(count, row.getIntColumn(column)); } break; case Types.NUMERIC: case Types.DECIMAL: statement.setLong(count, row.getLongColumn(column)); // FIXME should be BigDecimal if TableRow supported that break; case Types.BIGINT: statement.setLong(count, row.getLongColumn(column)); break; case Types.CLOB: if (isOracle) { // Support CLOBs in place of TEXT columns in Oracle statement.setString(count, row.getStringColumn(column)); } else { throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype); } break; case Types.VARCHAR: statement.setString(count, row.getStringColumn(column)); break; case Types.DATE: statement.setDate(count, new java.sql.Date(row.getDateColumn(column).getTime())); break; case Types.TIME: statement.setTime(count, new Time(row.getDateColumn(column).getTime())); break; case Types.TIMESTAMP: statement.setTimestamp(count, new Timestamp(row.getDateColumn(column).getTime())); break; default: throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype); } } } }
From source file:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java
/** * @see org.xml.sax.ContentHandler#endElement(java.lang.String, * java.lang.String, java.lang.String) *///from w w w . ja va2s. c om @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (ROW_NAME.equals(localName)) { if (!currCriticalError) { executeStatement(); } else { rowDisc++; // aggiunta DiscardCause al dhr... String msg = currentXSLMessage; dhr.addDiscardCause(new DiscardCause(rowCounter, msg)); resultMessage.append("Data error on row ").append(rowCounter).append(": ").append(msg); resultMessage.append("SQL Statement Informations:\n").append(sqlStatementInfo); resultMessage.append("Record parameters:\n").append(dumpCurrentRowFields()); resultStatus = STATUS_PARTIAL; } } else if (COL_NAME.equals(localName)) { CallableStatement cs = (CallableStatement) sqlStatementInfo.getStatement(); try { if (!outOnly) { colDataExpecting = false; String text = textBuffer.toString(); if ((currentUUID != null) && (currentUUID.trim().length() > 0) && (text.length() == 0)) { text = uuids.get(currentUUID); if (text == null) { text = currentUUID; } } if (TIMESTAMP_TYPE.equals(currType) || DATE_TYPE.equals(currType) || TIME_TYPE.equals(currType)) { if (text.equals("")) { if (TIMESTAMP_TYPE.equals(currType)) setNull(cs, Types.TIMESTAMP); else if (DATE_TYPE.equals(currType)) setNull(cs, Types.DATE); else setNull(cs, Types.TIME); currentRowFields.add(null); } else { dateFormatter.applyPattern(currDateFormat); Date formattedDate = dateFormatter.parse(text); if (TIMESTAMP_TYPE.equals(currType)) { Timestamp ts = new Timestamp(formattedDate.getTime()); setTimestamp(cs, ts); currentRowFields.add(ts); } else if (DATE_TYPE.equals(currType)) { java.sql.Date d = new java.sql.Date(formattedDate.getTime()); setDate(cs, d); currentRowFields.add(d); } else { java.sql.Time t = new java.sql.Time(formattedDate.getTime()); setTime(cs, t); currentRowFields.add(t); } } } else if (INTEGER_TYPE.equals(currType) || SMALLINT_TYPE.equals(currType) || BIGINT_TYPE.equals(currType)) { if (text.equals("")) { if (INTEGER_TYPE.equals(currType)) setNull(cs, Types.INTEGER); else if (SMALLINT_TYPE.equals(currType)) setNull(cs, Types.SMALLINT); else setNull(cs, Types.BIGINT); currentRowFields.add(null); } else { if (INTEGER_TYPE.equals(currType)) setInt(cs, Integer.parseInt(text, 10)); else if (SMALLINT_TYPE.equals(currType)) setShort(cs, Short.parseShort(text, 10)); else setLong(cs, Long.parseLong(text, 10)); currentRowFields.add(text); } } else if (FLOAT_TYPE.equals(currType) || DOUBLE_TYPE.equals(currType) || DECIMAL_TYPE.equals(currType) || NUMERIC_TYPE.equals(currType)) { if (text.equals("")) { if (DECIMAL_TYPE.equals(currType) || NUMERIC_TYPE.equals(currType)) setNull(cs, Types.NUMERIC); else if (FLOAT_TYPE.equals(currType)) setNull(cs, Types.FLOAT); else setNull(cs, Types.DOUBLE); currentRowFields.add(null); } else { DecimalFormatSymbols dfs = numberFormatter.getDecimalFormatSymbols(); dfs.setDecimalSeparator(currDecSeparator.charAt(0)); dfs.setGroupingSeparator(currGroupSeparator.charAt(0)); numberFormatter.setDecimalFormatSymbols(dfs); numberFormatter.applyPattern(currNumberFormat); boolean isBigDecimal = numberFormatter.isParseBigDecimal(); try { numberFormatter.setParseBigDecimal(true); BigDecimal formattedNumber = (BigDecimal) numberFormatter.parse(text); if (DECIMAL_TYPE.equals(currType) || NUMERIC_TYPE.equals(currType)) { setBigDecimal(cs, formattedNumber); currentRowFields.add(formattedNumber); } else if (FLOAT_TYPE.equals(currType)) { setFloat(cs, formattedNumber.floatValue()); currentRowFields.add(formattedNumber.floatValue()); } else { setDouble(cs, formattedNumber.doubleValue()); currentRowFields.add(formattedNumber.doubleValue()); } } finally { numberFormatter.setParseBigDecimal(isBigDecimal); } } } else if (LONG_STRING_TYPE.equals(currType) || LONG_NSTRING_TYPE.equals(currType)) { if (text.equals("")) { if (LONG_STRING_TYPE.equals(currType)) setNull(cs, Types.CLOB); else setNull(cs, Types.NCLOB); currentRowFields.add(null); } else { if (LONG_STRING_TYPE.equals(currType)) { setCharacterStream(cs, new StringReader(text)); currentRowFields.add(text); } else { setNCharacterStream(cs, new StringReader(text)); currentRowFields.add(text); } } } else if (BASE64_TYPE.equals(currType)) { if (text.equals("")) { setNull(cs, Types.BLOB); currentRowFields.add(null); } else { byte[] data = text.getBytes(); data = Base64.getDecoder().decode(data); ByteArrayInputStream bais = new ByteArrayInputStream(data); setBinaryStream(cs, bais, data.length); currentRowFields.add(text); } } else if (BINARY_TYPE.equals(currType)) { if (text.equals("")) { setNull(cs, Types.BLOB); currentRowFields.add(null); } else { byte[] data = text.getBytes(); ByteArrayInputStream bais = new ByteArrayInputStream(data); setBinaryStream(cs, bais, data.length); currentRowFields.add(text); } } else if (BOOLEAN_TYPE.equals(currType)) { if (text.equals("")) { setNull(cs, Types.BOOLEAN); currentRowFields.add(null); } else { setBoolean(cs, TextUtils.parseBoolean(text)); currentRowFields.add(text); } } else if (XML_TYPE.equals(currType)) { if (text.equals("")) { setNull(cs, Types.SQLXML); currentRowFields.add(null); } else { SQLXML xml = cs.getConnection().createSQLXML(); xml.setString(text); setSQLXML(cs, xml); currentRowFields.add(text); } } else if (NSTRING_TYPE.equals(currType)) { if (text.equals("")) { setNull(cs, Types.NVARCHAR); currentRowFields.add(null); } else { setNString(cs, text); currentRowFields.add(text); } } else { if (text.equals("")) { setNull(cs, Types.VARCHAR); currentRowFields.add(null); } else { setString(cs, text); currentRowFields.add(text); } } } else { currentRowFields.add(currentUUID); } } catch (ParseException exc) { throw new SAXException(exc); } catch (SQLException exc) { OracleExceptionHandler.handleSQLException(exc); throw new SAXException(exc); } } }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testResultSetMetaData() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, " + "c1*2, sentences(null, null, null) as b, c17, c18, c20, c21, c22, c23 from " + dataTypeTableName + " limit 1"); ResultSetMetaData meta = res.getMetaData(); ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null); assertEquals(20, meta.getColumnCount()); assertTrue(colRS.next());//www .java 2s . co m assertEquals("c1", meta.getColumnName(1)); assertEquals(Types.INTEGER, meta.getColumnType(1)); assertEquals("int", meta.getColumnTypeName(1)); assertEquals(11, meta.getColumnDisplaySize(1)); assertEquals(10, meta.getPrecision(1)); assertEquals(0, meta.getScale(1)); assertEquals("c1", colRS.getString("COLUMN_NAME")); assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE")); assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c2", meta.getColumnName(2)); assertEquals("boolean", meta.getColumnTypeName(2)); assertEquals(Types.BOOLEAN, meta.getColumnType(2)); assertEquals(1, meta.getColumnDisplaySize(2)); assertEquals(1, meta.getPrecision(2)); assertEquals(0, meta.getScale(2)); assertEquals("c2", colRS.getString("COLUMN_NAME")); assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE")); assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c3", meta.getColumnName(3)); assertEquals(Types.DOUBLE, meta.getColumnType(3)); assertEquals("double", meta.getColumnTypeName(3)); assertEquals(25, meta.getColumnDisplaySize(3)); assertEquals(15, meta.getPrecision(3)); assertEquals(15, meta.getScale(3)); assertEquals("c3", colRS.getString("COLUMN_NAME")); assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE")); assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c4", meta.getColumnName(4)); assertEquals(Types.VARCHAR, meta.getColumnType(4)); assertEquals("string", meta.getColumnTypeName(4)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(4)); assertEquals(0, meta.getScale(4)); assertEquals("c4", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("a", meta.getColumnName(5)); assertEquals(Types.ARRAY, meta.getColumnType(5)); assertEquals("array", meta.getColumnTypeName(5)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(5)); assertEquals(0, meta.getScale(5)); assertEquals("c5", colRS.getString("COLUMN_NAME")); assertEquals(Types.ARRAY, colRS.getInt("DATA_TYPE")); assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c6", meta.getColumnName(6)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(6)); assertEquals("map", meta.getColumnTypeName(6)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(6)); assertEquals(0, meta.getScale(6)); assertEquals("c6", colRS.getString("COLUMN_NAME")); assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c7", meta.getColumnName(7)); assertEquals(Types.JAVA_OBJECT, meta.getColumnType(7)); assertEquals("map", meta.getColumnTypeName(7)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(7)); assertEquals(0, meta.getScale(7)); assertEquals("c7", colRS.getString("COLUMN_NAME")); assertEquals(Types.JAVA_OBJECT, colRS.getInt("DATA_TYPE")); assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c8", meta.getColumnName(8)); assertEquals(Types.STRUCT, meta.getColumnType(8)); assertEquals("struct", meta.getColumnTypeName(8)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(8)); assertEquals(0, meta.getScale(8)); assertEquals("c8", colRS.getString("COLUMN_NAME")); assertEquals(Types.STRUCT, colRS.getInt("DATA_TYPE")); assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase()); assertTrue(colRS.next()); assertEquals("c9", meta.getColumnName(9)); assertEquals(Types.TINYINT, meta.getColumnType(9)); assertEquals("tinyint", meta.getColumnTypeName(9)); assertEquals(4, meta.getColumnDisplaySize(9)); assertEquals(3, meta.getPrecision(9)); assertEquals(0, meta.getScale(9)); assertEquals("c9", colRS.getString("COLUMN_NAME")); assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE")); assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c10", meta.getColumnName(10)); assertEquals(Types.SMALLINT, meta.getColumnType(10)); assertEquals("smallint", meta.getColumnTypeName(10)); assertEquals(6, meta.getColumnDisplaySize(10)); assertEquals(5, meta.getPrecision(10)); assertEquals(0, meta.getScale(10)); assertEquals("c10", colRS.getString("COLUMN_NAME")); assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE")); assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c11", meta.getColumnName(11)); assertEquals(Types.FLOAT, meta.getColumnType(11)); assertEquals("float", meta.getColumnTypeName(11)); assertEquals(24, meta.getColumnDisplaySize(11)); assertEquals(7, meta.getPrecision(11)); assertEquals(7, meta.getScale(11)); assertEquals("c11", colRS.getString("COLUMN_NAME")); assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE")); assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c12", meta.getColumnName(12)); assertEquals(Types.BIGINT, meta.getColumnType(12)); assertEquals("bigint", meta.getColumnTypeName(12)); assertEquals(20, meta.getColumnDisplaySize(12)); assertEquals(19, meta.getPrecision(12)); assertEquals(0, meta.getScale(12)); assertEquals("c12", colRS.getString("COLUMN_NAME")); assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE")); assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS")); assertEquals("_c12", meta.getColumnName(13)); assertEquals(Types.INTEGER, meta.getColumnType(13)); assertEquals("int", meta.getColumnTypeName(13)); assertEquals(11, meta.getColumnDisplaySize(13)); assertEquals(10, meta.getPrecision(13)); assertEquals(0, meta.getScale(13)); assertEquals("b", meta.getColumnName(14)); assertEquals(Types.ARRAY, meta.getColumnType(14)); assertEquals("array", meta.getColumnTypeName(14)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(14)); assertEquals(0, meta.getScale(14)); // Move the result of getColumns() forward to match the columns of the query assertTrue(colRS.next()); // c13 assertTrue(colRS.next()); // c14 assertTrue(colRS.next()); // c15 assertTrue(colRS.next()); // c16 assertTrue(colRS.next()); // c17 assertEquals("c17", meta.getColumnName(15)); assertEquals(Types.TIMESTAMP, meta.getColumnType(15)); assertEquals("timestamp", meta.getColumnTypeName(15)); assertEquals(29, meta.getColumnDisplaySize(15)); assertEquals(29, meta.getPrecision(15)); assertEquals(9, meta.getScale(15)); assertEquals("c17", colRS.getString("COLUMN_NAME")); assertEquals(Types.TIMESTAMP, colRS.getInt("DATA_TYPE")); assertEquals("timestamp", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(15), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(15), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c18", meta.getColumnName(16)); assertEquals(Types.DECIMAL, meta.getColumnType(16)); assertEquals("decimal", meta.getColumnTypeName(16)); assertEquals(18, meta.getColumnDisplaySize(16)); assertEquals(16, meta.getPrecision(16)); assertEquals(7, meta.getScale(16)); assertEquals("c18", colRS.getString("COLUMN_NAME")); assertEquals(Types.DECIMAL, colRS.getInt("DATA_TYPE")); assertEquals("decimal", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(16), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(16), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); // skip c19, since not selected by query assertTrue(colRS.next()); assertEquals("c20", meta.getColumnName(17)); assertEquals(Types.DATE, meta.getColumnType(17)); assertEquals("date", meta.getColumnTypeName(17)); assertEquals(10, meta.getColumnDisplaySize(17)); assertEquals(10, meta.getPrecision(17)); assertEquals(0, meta.getScale(17)); assertEquals("c20", colRS.getString("COLUMN_NAME")); assertEquals(Types.DATE, colRS.getInt("DATA_TYPE")); assertEquals("date", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(17), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(17), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c21", meta.getColumnName(18)); assertEquals(Types.VARCHAR, meta.getColumnType(18)); assertEquals("varchar", meta.getColumnTypeName(18)); // varchar columns should have correct display size/precision assertEquals(20, meta.getColumnDisplaySize(18)); assertEquals(20, meta.getPrecision(18)); assertEquals(0, meta.getScale(18)); assertEquals("c21", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("varchar", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(18), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(18), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c22", meta.getColumnName(19)); assertEquals(Types.CHAR, meta.getColumnType(19)); assertEquals("char", meta.getColumnTypeName(19)); // char columns should have correct display size/precision assertEquals(15, meta.getColumnDisplaySize(19)); assertEquals(15, meta.getPrecision(19)); assertEquals(0, meta.getScale(19)); assertEquals("c23", meta.getColumnName(20)); assertEquals(Types.BINARY, meta.getColumnType(20)); assertEquals("binary", meta.getColumnTypeName(20)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(20)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(20)); assertEquals(0, meta.getScale(20)); assertEquals("c22", colRS.getString("COLUMN_NAME")); assertEquals(Types.CHAR, colRS.getInt("DATA_TYPE")); assertEquals("char", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(19), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(19), colRS.getInt("DECIMAL_DIGITS")); for (int i = 1; i <= meta.getColumnCount(); i++) { assertFalse(meta.isAutoIncrement(i)); assertFalse(meta.isCurrency(i)); assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i)); } }
From source file:com.gtwm.pb.model.manageData.DataManagement.java
public int importCSV(HttpServletRequest request, TableInfo table, boolean updateExistingRecords, BaseField recordIdentifierField, boolean generateRowIds, char separator, char quotechar, int numHeaderLines, boolean useRelationDisplayValues, boolean importSequenceValues, boolean requireExactRelationMatches, boolean trim, boolean merge, List<FileItem> multipartItems, String csvContent) throws SQLException, InputRecordException, IOException, CantDoThatException, ObjectNotFoundException, DisallowedException, CodingErrorException { if (!FileUpload.isMultipartContent(new ServletRequestContext(request))) { if (csvContent == null) { throw new CantDoThatException( "To import CSV content, a file must be uploaded (form posted as multi-part) or csv_content specified"); }// w ww .ja v a2 s . c o m } int numImportedRecords = 0; // get field set to import into. LinkedHashSet to ensure order is // retained so the right values are imported into the right fields LinkedHashSet<BaseField> fields = new LinkedHashSet<BaseField>(table.getFields()); // if row IDs aren't included in the data to import, remove ID from the // field set BaseField primaryKey = table.getPrimaryKey(); if (recordIdentifierField == null) { recordIdentifierField = primaryKey; } if (generateRowIds || (updateExistingRecords && !recordIdentifierField.equals(primaryKey))) { fields.remove(primaryKey); } Map<RelationField, Map<String, String>> relationLookups = new HashMap<RelationField, Map<String, String>>(); // Remove fields which shouldn't be modified during the import // For serial fields, if we need to set serial values explicitly, this // will have to be dealt with later for (BaseField field : table.getFields()) { if (field instanceof SequenceField && (!field.equals(primaryKey)) && (!importSequenceValues)) { fields.remove(field); } else if (field.getHidden()) { if (field.getFieldName().equals(HiddenFields.VIEW_COUNT.getFieldName()) || field.getFieldName().equals(HiddenFields.COMMENTS_FEED.getFieldName())) { fields.remove(field); } else if (updateExistingRecords) { if (field.getFieldName().equals(HiddenFields.DATE_CREATED.getFieldName()) || field.getFieldName().equals(HiddenFields.CREATED_BY.getFieldName())) { fields.remove(field); } } } else if (!field.getFieldCategory().savesData()) { fields.remove(field); } // Also, if importing relations by display value, look up // display/internal value mappings if (useRelationDisplayValues && field instanceof RelationField) { Map<String, String> displayToInternalValue = ((RelationFieldDefn) field).getItems(true, false); relationLookups.put((RelationField) field, displayToInternalValue); } } // Prepare SQL String insertSQLCode = null; String updateSQLCode = null; String logCreationSQLCode = null; // If updating, we'll need a record ID value. Depending on what the // identifier field is, this could be one of a couple of different types String recordIdentifierString = null; Integer recordIdentifierInteger = null; int recordIdentifierFieldNum = 0; DatabaseFieldType identifierFieldDbType = null; if (updateExistingRecords) { identifierFieldDbType = recordIdentifierField.getDbType(); if (!identifierFieldDbType.equals(DatabaseFieldType.VARCHAR) && !identifierFieldDbType.equals(DatabaseFieldType.INTEGER) && !identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { throw new CantDoThatException("The record identifier field has to be text or a whole number, " + recordIdentifierField + " is a " + identifierFieldDbType); } updateSQLCode = "UPDATE " + table.getInternalTableName() + " SET "; int fieldNum = 0; for (BaseField field : fields) { fieldNum += 1; if (merge) { // Update database only if there's a non-null value from the // spreadsheet updateSQLCode += field.getInternalFieldName() + " = COALESCE(?," + field.getInternalFieldName() + "), "; } else { updateSQLCode += field.getInternalFieldName() + " = ?, "; } if (field.equals(recordIdentifierField)) { recordIdentifierFieldNum = fieldNum; } } if (recordIdentifierFieldNum == 0) { throw new CantDoThatException("Can't find the field specified as record identifier (" + recordIdentifierField + ") in the list of table fields " + fields + " in table " + table); } updateSQLCode = updateSQLCode.substring(0, updateSQLCode.length() - 2); updateSQLCode += " WHERE " + recordIdentifierField.getInternalFieldName() + "=?"; logCreationSQLCode = "UPDATE " + table.getInternalTableName() + " SET " + table.getField(HiddenFields.DATE_CREATED.getFieldName()).getInternalFieldName() + "=?, " + table.getField(HiddenFields.CREATED_BY.getFieldName()).getInternalFieldName() + "=? WHERE " + primaryKey.getInternalFieldName() + "=?"; } insertSQLCode = "INSERT INTO " + table.getInternalTableName() + "("; String placeholders = ""; for (BaseField field : fields) { insertSQLCode += field.getInternalFieldName() + ", "; placeholders += "?, "; } placeholders = placeholders.substring(0, placeholders.length() - 2); insertSQLCode = insertSQLCode.substring(0, insertSQLCode.length() - 2) + ") VALUES (" + placeholders + ")"; // Find content to import Reader inputStreamReader = null; if (csvContent != null) { inputStreamReader = new StringReader(csvContent); } else { for (FileItem item : multipartItems) { // if item is a file if (!item.isFormField()) { if (item.getName().toLowerCase().endsWith(".xls")) { throw new CantDoThatException( "You need to upload as a CSV to import, Excel files can't be imported directly"); } inputStreamReader = new InputStreamReader(item.getInputStream()); break; } } } if (inputStreamReader == null) { throw new CantDoThatException("No file uploaded"); } CSVReader csvReader = new CSVReader(inputStreamReader, separator, quotechar, numHeaderLines); // returns a list of String arrays List<String[]> csvLines = (List<String[]>) csvReader.readAll(); // do db inserts Connection conn = null; PreparedStatement statement = null; // backupInsertStatement is for when an update returns 0 rows affected, // i.e. there's no matching row. In this case, do an insert PreparedStatement backupInsertStatement = null; PreparedStatement logCreationStatement = null; // These two variables used in exception handling int importLine = 0; BaseField fieldImported = null; Timestamp importTime = new Timestamp(System.currentTimeMillis()); AppUserInfo loggedInUser = authManager.getUserByUserName(request, request.getRemoteUser()); String fullname = loggedInUser.getForename() + " " + loggedInUser.getSurname() + " (" + loggedInUser.getUserName() + ")"; try { conn = this.dataSource.getConnection(); conn.setAutoCommit(false); if (updateExistingRecords) { statement = conn.prepareStatement(updateSQLCode); backupInsertStatement = conn.prepareStatement(insertSQLCode); logCreationStatement = conn.prepareStatement(logCreationSQLCode); } else { statement = conn.prepareStatement(insertSQLCode); } CSVLINE: for (String[] csvLineArray : csvLines) { // convert to an object rather than a primitive array - // easier to work with List<String> lineValues = Arrays.asList(csvLineArray); importLine++; // skip blank lines if (lineValues.size() == 1) { if (lineValues.get(0).length() == 0) { continue CSVLINE; } } int fieldNum = 0; for (BaseField field : fields) { fieldImported = field; fieldNum++; if (field.getHidden()) { String fieldName = field.getFieldName(); if (fieldName.equals(HiddenFields.LOCKED.getFieldName())) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else if (fieldName.equals(HiddenFields.DATE_CREATED.getFieldName()) || fieldName.equals(HiddenFields.LAST_MODIFIED.getFieldName())) { statement.setTimestamp(fieldNum, importTime); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, importTime); } } else if (fieldName.equals(HiddenFields.CREATED_BY.getFieldName()) || fieldName.equals(HiddenFields.MODIFIED_BY.getFieldName())) { statement.setString(fieldNum, fullname); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, fullname); } } } else if (fieldNum > lineValues.size()) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { String lineValue = lineValues.get(fieldNum - 1); if (lineValue != null) { if (trim) { lineValue = lineValue.trim(); } if (lineValue.equals("")) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { if ((field instanceof FileField) && (generateRowIds)) { throw new CantDoThatException( "Cannot generate row ids when importing file names. See line " + importLine + ", field '" + field.getFieldName() + "' with value '" + lineValue + "'"); } switch (field.getDbType()) { case VARCHAR: statement.setString(fieldNum, lineValue); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, lineValue); if (field.equals(recordIdentifierField)) { recordIdentifierString = lineValue; } } break; case TIMESTAMP: // deal with month and year // resolution dates exported if (lineValue.matches("^[a-zA-Z]{3}\\s\\d{2,4}$")) { lineValue = "01 " + lineValue; } else if (lineValue.matches("^\\d{2,4}")) { lineValue = "01 Jan " + lineValue; } try { Calendar calValue = CalendarParser.parse(lineValue, CalendarParser.DD_MM_YY); statement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); } } catch (CalendarParserException cpex) { throw new InputRecordException("Error importing line " + importLine + ", field " + field + ": " + cpex.getMessage(), field, cpex); } break; case FLOAT: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); statement.setDouble(fieldNum, Double.valueOf(lineValue)); if (updateExistingRecords) { backupInsertStatement.setDouble(fieldNum, Double.valueOf(lineValue)); } break; case INTEGER: if ((field instanceof RelationField) && (useRelationDisplayValues)) { // find key value for display value RelationField relationField = (RelationField) field; Map<String, String> valueKeyMap = relationLookups.get(relationField); String internalValueString = valueKeyMap.get(lineValue); if (internalValueString == null) { if (!requireExactRelationMatches) { // A very basic fuzzy matching // algorithm String potentialDisplayValue = null; String lineValueLowerCase = lineValue.toLowerCase(); FUZZYMATCH: for (Map.Entry<String, String> entry : valueKeyMap .entrySet()) { potentialDisplayValue = entry.getKey(); if (potentialDisplayValue.toLowerCase() .contains(lineValueLowerCase)) { internalValueString = entry.getValue(); break FUZZYMATCH; } } } if (internalValueString == null) { throw new CantDoThatException("Error importing line " + importLine + ", field " + relationField + ": Can't find a related '" + relationField.getRelatedTable() + "' for " + relationField.getDisplayField() + " '" + lineValue + "'. "); } } int keyValue = Integer.valueOf(internalValueString); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } else { lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } break; case SERIAL: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } break; case BOOLEAN: boolean filterValueIsTrue = Helpers.valueRepresentsBooleanTrue(lineValue); statement.setBoolean(fieldNum, filterValueIsTrue); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, filterValueIsTrue); } break; } } } else { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } } } if (updateExistingRecords) { // for potential error messages String recordIdentifierDescription = null; if (identifierFieldDbType.equals(DatabaseFieldType.INTEGER) || identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { if (recordIdentifierInteger == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = " + recordIdentifierInteger; // Set the 'WHERE recordIdentifier = ?' clause statement.setInt(fields.size() + 1, recordIdentifierInteger); } else { if (recordIdentifierString == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = '" + recordIdentifierString + "'"; // Set the 'WHERE recordIdentifier = ?' clause statement.setString(fields.size() + 1, recordIdentifierString); } int rowsAffected = statement.executeUpdate(); if (rowsAffected == 0) { // If can't find a match to update, insert a record // instead backupInsertStatement.executeUpdate(); // NB Postgres specific code to find Row ID of newly // inserted record, not cross-db compatible String newRowIdSQLCode = "SELECT currval('" + table.getInternalTableName() + "_" + primaryKey.getInternalFieldName() + "_seq')"; PreparedStatement newRowIdStatement = conn.prepareStatement(newRowIdSQLCode); ResultSet newRowIdResults = newRowIdStatement.executeQuery(); if (newRowIdResults.next()) { int newRowId = newRowIdResults.getInt(1); // Add creation metadata to the new row logCreationStatement.setTimestamp(1, importTime); logCreationStatement.setString(2, fullname); logCreationStatement.setInt(3, newRowId); int creationLogRowsAffected = logCreationStatement.executeUpdate(); if (creationLogRowsAffected == 0) { throw new SQLException( "Unable to update creation metadata of newly inserted record, using query " + logCreationStatement); } } else { newRowIdResults.close(); newRowIdStatement.close(); throw new SQLException("Row ID not found for the newly inserted record. '" + newRowIdStatement + "' didn't work"); } newRowIdResults.close(); newRowIdStatement.close(); } else if (rowsAffected > 1) { throw new InputRecordException("Error importing line " + importLine + ". The record identifier field " + recordIdentifierDescription + " should match only 1 record in the database but it actually matches " + rowsAffected, recordIdentifierField); } // reset to null for the next line recordIdentifierString = null; recordIdentifierInteger = null; } else { statement.executeUpdate(); } numImportedRecords += 1; } statement.close(); if (backupInsertStatement != null) { backupInsertStatement.close(); } if (logCreationStatement != null) { logCreationStatement.close(); } // reset the primary key ID sequence so new records can be added resetSequence((SequenceField) primaryKey, conn); // and any other sequence fields if (importSequenceValues) { for (BaseField field : table.getFields()) { if ((!field.equals(primaryKey)) && field instanceof SequenceField) { resetSequence((SequenceField) field, conn); } } } // ANALYZE the table after import if (numImportedRecords > 1000) { Statement analyzeStatement = conn.createStatement(); analyzeStatement.execute("ANALYZE " + table.getInternalTableName()); analyzeStatement.close(); } conn.commit(); } catch (SQLException sqlex) { String databaseErrorMessage = Helpers.replaceInternalNames(sqlex.getMessage(), table.getDefaultReport()); logger.warn("Import failed, statement is " + statement); logger.warn("Backup insert statement is " + backupInsertStatement); String errorMessage = "Error importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + databaseErrorMessage; throw new InputRecordException(errorMessage, fieldImported, sqlex); } catch (NumberFormatException nfex) { String causeMessage = nfex.getMessage(); causeMessage = causeMessage.replaceAll("For input string", "value"); String errorMessage = "Error parsing number when importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + causeMessage; throw new InputRecordException(errorMessage, fieldImported, nfex); } finally { if (conn != null) { conn.close(); } } this.logLastDataChangeTime(request); logLastTableDataChangeTime(table); UsageLogger usageLogger = new UsageLogger(this.dataSource); String logMessage = "" + numImportedRecords; if (updateExistingRecords) { logMessage += " records imported"; } else { logMessage += " new records imported"; } if (csvContent != null) { logMessage += " from file"; } usageLogger.logDataChange(loggedInUser, table, null, AppAction.CSV_IMPORT, -1, logMessage); UsageLogger.startLoggingThread(usageLogger); return numImportedRecords; }
From source file:helma.objectmodel.db.NodeManager.java
private void setStatementValue(PreparedStatement stmt, int stmtNumber, Property p, int columnType) throws SQLException { if (p.getValue() == null) { stmt.setNull(stmtNumber, columnType); } else {//from ww w.j a v a 2s.com switch (columnType) { case Types.BIT: case Types.BOOLEAN: stmt.setBoolean(stmtNumber, p.getBooleanValue()); break; case Types.TINYINT: case Types.BIGINT: case Types.SMALLINT: case Types.INTEGER: stmt.setLong(stmtNumber, p.getIntegerValue()); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: stmt.setDouble(stmtNumber, p.getFloatValue()); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BINARY: case Types.BLOB: Object b = p.getJavaObjectValue(); if (b instanceof byte[]) { byte[] buf = (byte[]) b; try { stmt.setBytes(stmtNumber, buf); } catch (SQLException x) { ByteArrayInputStream bout = new ByteArrayInputStream(buf); stmt.setBinaryStream(stmtNumber, bout, buf.length); } } else { throw new SQLException( "expected byte[] for binary column '" + p.getName() + "', found " + b.getClass()); } break; case Types.LONGVARCHAR: try { stmt.setString(stmtNumber, p.getStringValue()); } catch (SQLException x) { String str = p.getStringValue(); Reader r = new StringReader(str); stmt.setCharacterStream(stmtNumber, r, str.length()); } break; case Types.CLOB: String val = p.getStringValue(); Reader isr = new StringReader(val); stmt.setCharacterStream(stmtNumber, isr, val.length()); break; case Types.CHAR: case Types.VARCHAR: case Types.OTHER: stmt.setString(stmtNumber, p.getStringValue()); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: stmt.setTimestamp(stmtNumber, p.getTimestampValue()); break; case Types.NULL: stmt.setNull(stmtNumber, 0); break; default: stmt.setString(stmtNumber, p.getStringValue()); break; } } }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Returns the type name for the specific constant as defined * by {@link java.sql.Types}./* w w w . j a va 2 s. c o m*/ * * @param type the type * @return the name for the type */ public String getTypeName(int type) { switch (type) { case Types.ARRAY: return arrayTypeName; case Types.BIGINT: return bigintTypeName; case Types.BINARY: return binaryTypeName; case Types.BIT: return bitTypeName; case Types.BLOB: return blobTypeName; case Types.BOOLEAN: return booleanTypeName; case Types.CHAR: return charTypeName; case Types.CLOB: return clobTypeName; case Types.DATE: return dateTypeName; case Types.DECIMAL: return decimalTypeName; case Types.DISTINCT: return distinctTypeName; case Types.DOUBLE: return doubleTypeName; case Types.FLOAT: return floatTypeName; case Types.INTEGER: return integerTypeName; case Types.JAVA_OBJECT: return javaObjectTypeName; case Types.LONGVARBINARY: return longVarbinaryTypeName; case Types.LONGVARCHAR: return longVarcharTypeName; case Types.NULL: return nullTypeName; case Types.NUMERIC: return numericTypeName; case Types.OTHER: return otherTypeName; case Types.REAL: return realTypeName; case Types.REF: return refTypeName; case Types.SMALLINT: return smallintTypeName; case Types.STRUCT: return structTypeName; case Types.TIME: return timeTypeName; case Types.TIMESTAMP: return timestampTypeName; case Types.TINYINT: return tinyintTypeName; case Types.VARBINARY: return varbinaryTypeName; case Types.VARCHAR: return varcharTypeName; default: return otherTypeName; } }
From source file:org.nuclos.server.masterdata.ejb3.MetaDataFacadeBean.java
private String getBestJavaType(int colType) { String sType = "java.lang.String"; switch (colType) { case Types.VARCHAR: return sType; case Types.CHAR: return sType; case Types.NCHAR: return sType; case Types.NVARCHAR: return sType; case Types.LONGNVARCHAR: return sType; case Types.LONGVARCHAR: return sType; case Types.LONGVARBINARY: return sType; case Types.NUMERIC: return "java.lang.Integer"; case Types.DECIMAL: return "java.lang.Double"; case Types.BOOLEAN: return "java.lang.Integer"; case Types.DATE: return "java.util.Date"; case Types.TIME: return "java.util.Date"; case Types.TIMESTAMP: return "java.util.Date"; default://from ww w.j a v a 2s . c o m return sType; } }
From source file:edu.jhuapl.openessence.datasource.jdbc.entry.JdbcOeDataEntrySource.java
/** * Sets arguments of the proper type on a PreparedSatement * * @param pStmt prepared statement on which to set arguments * @param dimIds dimension ids that map to columns on the prepared statement * @param valueList values to set for the paramenters on the prepared statement * @param valueMap dimIds mapped to values to set for the paramenters on the prepared statement * @throws SQLException if error occurs while processing the prepared statement * @throws OeDataSourceException if error occurs converting value to it's sql type */// w w w . j a v a 2 s . co m protected void setArgumentsOnSqlType(PreparedStatement pStmt, List<String> dimIds, List<Object> valueList, Map<String, Object> valueMap) throws SQLException, OeDataSourceException { if ((valueList == null && valueMap == null) || (valueList != null && valueMap != null)) { throw new OeDataSourceException("Invalid value lists. Only one form of the list can be provided."); } int argCount = 0; Object val; boolean isValueList = (valueList != null); for (String dimId : dimIds) { Dimension dim = getEditDimension(dimId); FieldType sqlType = dim.getSqlType(); if (isValueList) { val = valueList.get(argCount); } else { val = valueMap.get(dimId); } try { argCount++; switch (sqlType) { case DATE_TIME: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlTimestampType(val), Types.TIMESTAMP); continue; case DATE: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.DATE); continue; case BOOLEAN: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.BOOLEAN); continue; case FLOAT: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.FLOAT); continue; case DOUBLE: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.DOUBLE); continue; case INTEGER: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.INTEGER); continue; case LONG: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.BIGINT); continue; case TEXT: pStmt.setObject(argCount, DataTypeConversionHelper.convert2SqlType(val), Types.VARCHAR); continue; default: throw new AssertionError("Unexpected sqlType " + sqlType + "."); } } catch (OeDataSourceException e) { throw new SQLException("Error occured converting value \"" + val + "\" to its sql type.", e); } } }