List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "UPDATE" on the given tableName with attribute values * and where clause./* w w w .j a va 2 s .c o m*/ * * @param tableName * @param attributeNameValue * @param whereClause * @return * @throws SQLException */ public int updateRow(String tableName, Map attributeNameValue, String whereClause, String dbType) throws SQLException { StringBuffer stmt = new StringBuffer(); PreparedStatement prepStmt = null; int rowsUpdated = 0; Object attribute = null; Iterator itr = null; String[] key = new String[attributeNameValue.size()]; int count = 0; stmt.append("UPDATE " + tablePrefix_ + tableName.trim() + " SET "); itr = attributeNameValue.keySet().iterator(); while (itr.hasNext()) { key[count] = (String) itr.next(); stmt.append(key[count++] + " = ?,"); } /* * for (int i = 0; i < attributeNames.size(); i++) { * stmt.append(attributeNames.get(i) + " = ?,"); } */ stmt = stmt.deleteCharAt(stmt.length() - 1); if (whereClause != null && !"".equals(whereClause)) { stmt.append(" WHERE "); stmt.append(whereClause); } // stmt = stmt.deleteCharAt(stmt.length()); log.debug("************ UPDATE QUERY ************"); log.debug(stmt.toString()); log.debug("**************************************"); try { String statement = new GenericSQLModifier(dbType, false).modifySQL(stmt.toString()); prepStmt = sqlConnection_.prepareStatement(statement); itr = attributeNameValue.keySet().iterator(); for (count = 0; count < key.length; count++) { attribute = attributeNameValue.get(key[count]); if (attribute instanceof String) { prepStmt.setString(count + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(count + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(count + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(count + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(count + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(count + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(count + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(count + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(count + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(count + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(count + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(count + 1, (Timestamp) attribute); } } rowsUpdated = prepStmt.executeUpdate(); } catch (Exception e) { log.error("Exception @ updateRow: " + e.getMessage()); } finally { prepStmt.close(); } return rowsUpdated; }
From source file:org.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java
/** * This method bind values to prepared statement. * * @param type data Type//from w ww. ja v a 2s . co m * @param value String value * @param ordinalPosition Ordinal Position * @param sqlStatement Statement * @throws SQLException * @throws ParseException * @throws ODataServiceFault */ private void bindValuesToPreparedStatement(int type, String value, int ordinalPosition, PreparedStatement sqlStatement) throws SQLException, ParseException, ODataServiceFault { byte[] data; try { switch (type) { case Types.INTEGER: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setInt(ordinalPosition, ConverterUtil.convertToInt(value)); } break; case Types.TINYINT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setByte(ordinalPosition, ConverterUtil.convertToByte(value)); } break; case Types.SMALLINT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setShort(ordinalPosition, ConverterUtil.convertToShort(value)); } break; case Types.DOUBLE: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setDouble(ordinalPosition, ConverterUtil.convertToDouble(value)); } break; case Types.VARCHAR: /* fall through */ case Types.CHAR: /* fall through */ case Types.LONGVARCHAR: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setString(ordinalPosition, value); } break; case Types.CLOB: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setClob(ordinalPosition, new BufferedReader(new StringReader(value)), value.length()); } break; case Types.BOOLEAN: /* fall through */ case Types.BIT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setBoolean(ordinalPosition, ConverterUtil.convertToBoolean(value)); } break; case Types.BLOB: /* fall through */ case Types.LONGVARBINARY: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { data = this.getBytesFromBase64String(value); sqlStatement.setBlob(ordinalPosition, new ByteArrayInputStream(data), data.length); } break; case Types.BINARY: /* fall through */ case Types.VARBINARY: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { data = this.getBytesFromBase64String(value); sqlStatement.setBinaryStream(ordinalPosition, new ByteArrayInputStream(data), data.length); } break; case Types.DATE: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setDate(ordinalPosition, DBUtils.getDate(value)); } break; case Types.DECIMAL: /* fall through */ case Types.NUMERIC: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setBigDecimal(ordinalPosition, ConverterUtil.convertToBigDecimal(value)); } break; case Types.FLOAT: /* fall through */ case Types.REAL: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setFloat(ordinalPosition, ConverterUtil.convertToFloat(value)); } break; case Types.TIME: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setTime(ordinalPosition, DBUtils.getTime(value)); } break; case Types.LONGNVARCHAR: /* fall through */ case Types.NCHAR: /* fall through */ case Types.NVARCHAR: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setNString(ordinalPosition, value); } break; case Types.NCLOB: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setNClob(ordinalPosition, new BufferedReader(new StringReader(value)), value.length()); } break; case Types.BIGINT: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setLong(ordinalPosition, ConverterUtil.convertToLong(value)); } break; case Types.TIMESTAMP: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setTimestamp(ordinalPosition, DBUtils.getTimestamp(value)); } break; default: if (value == null) { sqlStatement.setNull(ordinalPosition, type); } else { sqlStatement.setString(ordinalPosition, value); } break; } } catch (DataServiceFault e) { throw new ODataServiceFault(e, "Error occurred while binding values. :" + e.getMessage()); } }
From source file:org.apache.tajo.catalog.store.AbstractDBStore.java
@Override public List<PartitionDescProto> getPartitionsByAlgebra(PartitionsByAlgebraProto request) throws UndefinedDatabaseException, UndefinedTableException, UndefinedPartitionMethodException, UnsupportedException {// w w w .j a va 2s . c om Connection conn = null; PreparedStatement pstmt = null; ResultSet res = null; int currentIndex = 1; String selectStatement = null; Pair<String, List<PartitionFilterSet>> pair = null; List<PartitionDescProto> partitions = new ArrayList<>(); List<PartitionFilterSet> filterSets = null; int databaseId = getDatabaseId(request.getDatabaseName()); int tableId = getTableId(databaseId, request.getDatabaseName(), request.getTableName()); if (!existPartitionMethod(request.getDatabaseName(), request.getTableName())) { throw new UndefinedPartitionMethodException(request.getTableName()); } try { TableDescProto tableDesc = getTable(request.getDatabaseName(), request.getTableName()); pair = getSelectStatementAndPartitionFilterSet(tableDesc.getTableName(), tableDesc.getPartition().getExpressionSchema().getFieldsList(), request.getAlgebra()); selectStatement = pair.getFirst(); filterSets = pair.getSecond(); conn = getConnection(); pstmt = conn.prepareStatement(selectStatement); // Set table id by force because first parameter of all direct sql is table id pstmt.setInt(currentIndex, tableId); currentIndex++; for (PartitionFilterSet filter : filterSets) { // Set table id by force because all filters have table id as first parameter. pstmt.setInt(currentIndex, tableId); currentIndex++; for (Pair<Type, Object> parameter : filter.getParameters()) { switch (parameter.getFirst()) { case BOOLEAN: pstmt.setBoolean(currentIndex, (Boolean) parameter.getSecond()); break; case INT8: pstmt.setLong(currentIndex, (Long) parameter.getSecond()); break; case FLOAT8: pstmt.setDouble(currentIndex, (Double) parameter.getSecond()); break; case DATE: pstmt.setDate(currentIndex, (Date) parameter.getSecond()); break; case TIMESTAMP: pstmt.setTimestamp(currentIndex, (Timestamp) parameter.getSecond()); break; case TIME: pstmt.setTime(currentIndex, (Time) parameter.getSecond()); break; default: pstmt.setString(currentIndex, (String) parameter.getSecond()); break; } currentIndex++; } } res = pstmt.executeQuery(); while (res.next()) { PartitionDescProto.Builder builder = PartitionDescProto.newBuilder(); builder.setId(res.getInt(COL_PARTITIONS_PK)); builder.setPartitionName(res.getString("PARTITION_NAME")); builder.setPath(res.getString("PATH")); builder.setNumBytes(res.getLong(COL_PARTITION_BYTES)); partitions.add(builder.build()); } } catch (SQLException se) { throw new TajoInternalError(se); } finally { CatalogUtil.closeQuietly(pstmt, res); } return partitions; }
From source file:org.getobjects.eoaccess.EOAdaptorChannel.java
protected PreparedStatement _prepareStatementWithBinds(final String _sql, final List<Map<String, Object>> _binds) { boolean isDebugOn = log.isDebugEnabled(); if (_sql == null || _sql.length() == 0) return null; final PreparedStatement stmt = this._createPreparedStatement(_sql); if (stmt == null) return null; if (_binds == null) { if (isDebugOn) log.debug("statement to prepare has no binds .."); return stmt; /* hm, statement has no binds */ }// w ww. j av a 2 s . c o m /* fill in parameters */ if (isDebugOn) log.debug("prepare binds: " + _binds); try { /* Fill statement with bindg values */ for (int i = 0; i < _binds.size(); i++) { /* a dictionary with such keys: * BindVariableAttributeKey - the EOAttribute of the value * BindVariableValueKey - the actual value */ final Map<String, Object> bind = _binds.get(i); final EOAttribute attribute = (EOAttribute) bind.get(EOSQLExpression.BindVariableAttributeKey); final Object value = bind.get(EOSQLExpression.BindVariableValueKey); int sqlType = this.sqlTypeForValue(value, attribute); if (isDebugOn) { log.debug(" bind attribute: " + attribute); log.debug(" value: " + value + " / " + (value != null ? value.getClass() : "[NULL]")); log.debug(" type: " + sqlType); } if (value == null) stmt.setNull(i + 1, sqlType); else { switch (sqlType) { case java.sql.Types.NULL: stmt.setNull(i + 1, java.sql.Types.VARCHAR); // CRAP break; // TODO: customize value processing for types case java.sql.Types.VARCHAR: case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: case java.sql.Types.INTEGER: case java.sql.Types.BIGINT: case java.sql.Types.BOOLEAN: default: if (value instanceof String) stmt.setString(i + 1, (String) value); else if (value instanceof Boolean) stmt.setBoolean(i + 1, (Boolean) value); else if (value instanceof Integer) stmt.setInt(i + 1, (Integer) value); else if (value instanceof Double) stmt.setDouble(i + 1, (Double) value); else if (value instanceof BigDecimal) stmt.setBigDecimal(i + 1, (BigDecimal) value); else if (value instanceof Long) stmt.setLong(i + 1, (Long) value); else if (value instanceof java.util.Date) { // TBD: shouldn't we use setDate with a proper Calendar? stmt.setTimestamp(i + 1, new java.sql.Timestamp(((Date) value).getTime())); } else if (value instanceof java.util.Calendar) { // TBD: shouldn't we use setDate with a proper Calendar? final Date vd = ((Calendar) value).getTime(); stmt.setTimestamp(i + 1, new java.sql.Timestamp(vd.getTime())); } else if (value instanceof java.sql.Date) { /* Note: this is just the DATE component, no TIME */ stmt.setDate(i + 1, (java.sql.Date) value); } else if (value instanceof byte[]) stmt.setBytes(i + 1, (byte[]) value); else if (value instanceof EOQualifierVariable) { log.error("detected unresolved qualifier variable: " + value); this._releaseResources(stmt, null); return null; } else { log.warn("using String column for value: " + value + " (" + value.getClass() + ")"); } } } } } catch (NullPointerException e) { this.lastException = e; log.error("could not apply binds to prepared statement (null ptr): " + _sql, e); this._releaseResources(stmt, null); return null; } catch (SQLException e) { this.lastException = e; log.error("could not apply binds to prepared statement: " + _sql, e); this._releaseResources(stmt, null); return null; } return stmt; }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Set the given value as a parameter to the statement. */// w w w. j ava 2 s .c om public void setDate(PreparedStatement stmnt, int idx, java.sql.Date val, Calendar cal, Column col) throws SQLException { if (cal == null) stmnt.setDate(idx, val); else stmnt.setDate(idx, val, cal); }
From source file:org.openmrs.module.reporting.report.util.SqlUtils.java
/** * Binds the given paramMap to the query by replacing all named parameters (e.g. :paramName) * with their corresponding values in the parameter map. TODO copied from * HibernateCohortQueryDAO/*from w w w. j a v a2 s .c o m*/ * * @param connection * @param query * @param paramMap * @throws SQLException */ @SuppressWarnings("unchecked") public static PreparedStatement prepareStatement(Connection connection, String query, Map<String, Object> paramMap) throws SQLException { PreparedStatement statement; if (!isSelectQuery(query)) { throw new IllegalDatabaseAccessException(); } boolean containParams = query.indexOf(":") > 0; if (containParams) { // the first process is replacing the :paramName with ? // implementation taken from: http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html?page=2 Map<String, List<Integer>> params = new HashMap<String, List<Integer>>(); StringBuffer parsedQuery = new StringBuffer(); int index = 1; for (int i = 0; i < query.length(); i++) { // we can use charAt here, but we might need to append "(?, ?, ?)" when the where parameter is a list // http://stackoverflow.com/questions/178479/alternatives-for-java-sql-preparedstatement-in-clause-issue // http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 String s = query.substring(i, i + 1); if (StringUtils.equals(s, ":") && i + 1 < query.length() && Character.isJavaIdentifierStart(query.charAt(i + 1))) { // we already make sure that (i + 1) is a valid character, now check the next one after (i + 1) int j = i + 2; while (j < query.length() && Character.isJavaIdentifierPart(query.charAt(j))) j++; String name = query.substring(i + 1, j); Object paramValue = paramMap.get(name); // are we dealing with collection or not int size = 1; if (paramValue != null) if (Cohort.class.isAssignableFrom(paramValue.getClass())) size = ((Cohort) paramValue).getSize(); else if (Collection.class.isAssignableFrom(paramValue.getClass())) size = ((Collection<?>) paramValue).size(); // skip until the end of the param name i += name.length(); String[] sqlParams = new String[size]; for (int k = 0; k < sqlParams.length; k++) { sqlParams[k] = "?"; // record the location of the parameter in the sql statemet List<Integer> indexList = params.get(name); if (indexList == null) { indexList = new LinkedList<Integer>(); params.put(name, indexList); } indexList.add(new Integer(index)); index++; } s = StringUtils.join(sqlParams, ","); // for the "IN" query, we need to add bracket if (size > 1) s = "(" + s + ")"; } parsedQuery.append(s); } // the query string contains parameters, re-create the prepared statement with the new parsed query string statement = connection.prepareStatement(parsedQuery.toString()); // Iterate over parameters and bind them to the Query object for (String paramName : paramMap.keySet()) { Object paramValue = paramMap.get(paramName); // Indicates whether we should bind this parameter in the query // Make sure parameter value is not null if (paramValue == null) { // TODO Should try to convert 'columnName = null' to 'columnName IS NULL' throw new ParameterException("Cannot bind an empty value to parameter " + paramName + ". " + "Please provide a real value or use the 'IS NULL' constraint in your query (e.g. 'table.columnName IS NULL')."); } int i = 0; List<Integer> positions = params.get(paramName); if (positions != null) { // Cohort (needs to be first, otherwise it will resolve as OpenmrsObject) if (Cohort.class.isAssignableFrom(paramValue.getClass())) { Cohort cohort = (Cohort) paramValue; for (Integer patientId : cohort.getMemberIds()) { statement.setInt(positions.get(i++), patientId); } } // OpenmrsObject (e.g. Location) else if (OpenmrsObject.class.isAssignableFrom(paramValue.getClass())) { for (Integer position : positions) { statement.setInt(position, ((OpenmrsObject) paramValue).getId()); } } // List<OpenmrsObject> (e.g. List<Location>) else if (List.class.isAssignableFrom(paramValue.getClass())) { // If first element in the list is an OpenmrsObject if (OpenmrsObject.class.isAssignableFrom(((List<?>) paramValue).get(0).getClass())) { List<Integer> openmrsObjectIds = SqlUtils .openmrsObjectIdListHelper((List<OpenmrsObject>) paramValue); for (Integer openmrsObjectId : openmrsObjectIds) { statement.setInt(positions.get(i++), openmrsObjectId); } } // a List of Strings, Integers? else { List<String> strings = SqlUtils.objectListHelper((List<Object>) paramValue); for (String string : strings) { statement.setString(positions.get(i++), string); } } } // java.util.Date and subclasses else if (paramValue instanceof Date) { for (Integer position : positions) { statement.setDate(position, new java.sql.Date(((Date) paramValue).getTime())); } } else if (paramValue instanceof Integer || paramValue instanceof Long) { for (Integer position : positions) { statement.setLong(position, (Integer) paramValue); } } else if (paramValue instanceof Boolean) { for (Integer position : positions) { statement.setBoolean(position, (Boolean) paramValue); } } // String, et al (this might break since this is a catch all for all other classes) else { for (Integer position : positions) { statement.setString(position, new String(paramValue.toString())); } } } } } else statement = connection.prepareStatement(query); return statement; }
From source file:org.apache.cocoon.util.JDBCTypeConversions.java
/** * Set the Statement column so that the results are mapped correctly. * * @param statement the prepared statement * @param position the position of the column * @param value the value of the column/*w w w .j a va2 s . co m*/ */ public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject) throws Exception { if (value instanceof String) { value = ((String) value).trim(); } if (typeObject == null) { throw new SQLException("Can't set column because the type is unrecognized"); } if (value == null) { /** If the value is null, set the column value null and return **/ statement.setNull(position, typeObject.intValue()); return; } if ("".equals(value)) { switch (typeObject.intValue()) { case Types.CHAR: case Types.CLOB: case Types.VARCHAR: /** If the value is an empty string and the column is a string type, we can continue **/ break; default: /** If the value is an empty string and the column is something else, we treat it as a null value **/ statement.setNull(position, typeObject.intValue()); return; } } File file = null; int length = -1; InputStream asciiStream = null; //System.out.println("========================================================================"); //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue()); switch (typeObject.intValue()) { case Types.CLOB: //System.out.println("CLOB"); Clob clob = null; if (value instanceof Clob) { clob = (Clob) value; } else if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; clob = new ClobHelper(asciiStream, length); } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new ByteArrayInputStream(asciiText.getBytes()); length = asciiText.length(); clob = new ClobHelper(asciiStream, length); } statement.setClob(position, clob); break; case Types.CHAR: // simple large object, e.g. Informix's TEXT //System.out.println("CHAR"); if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes())); length = asciiText.length(); } statement.setAsciiStream(position, asciiStream, length); break; case Types.BIGINT: //System.out.println("BIGINT"); BigDecimal bd = null; if (value instanceof BigDecimal) { bd = (BigDecimal) value; } else if (value instanceof Number) { bd = BigDecimal.valueOf(((Number) value).longValue()); } else { bd = new BigDecimal(value.toString()); } statement.setBigDecimal(position, bd); break; case Types.TINYINT: //System.out.println("TINYINT"); Byte b = null; if (value instanceof Byte) { b = (Byte) value; } else if (value instanceof Number) { b = new Byte(((Number) value).byteValue()); } else { b = new Byte(value.toString()); } statement.setByte(position, b.byteValue()); break; case Types.DATE: //System.out.println("DATE"); Date d = null; if (value instanceof Date) { d = (Date) value; } else if (value instanceof java.util.Date) { d = new Date(((java.util.Date) value).getTime()); } else if (value instanceof Calendar) { d = new Date(((Calendar) value).getTime().getTime()); } else { d = Date.valueOf(value.toString()); } statement.setDate(position, d); break; case Types.DOUBLE: //System.out.println("DOUBLE"); double db; if (value instanceof Number) { db = (((Number) value).doubleValue()); } else { db = Double.parseDouble(value.toString()); } statement.setDouble(position, db); break; case Types.FLOAT: //System.out.println("FLOAT"); float f; if (value instanceof Number) { f = (((Number) value).floatValue()); } else { f = Float.parseFloat(value.toString()); } statement.setFloat(position, f); break; case Types.NUMERIC: //System.out.println("NUMERIC"); long l; if (value instanceof Number) { l = (((Number) value).longValue()); } else { l = Long.parseLong(value.toString()); } statement.setLong(position, l); break; case Types.SMALLINT: //System.out.println("SMALLINT"); Short s = null; if (value instanceof Short) { s = (Short) value; } else if (value instanceof Number) { s = new Short(((Number) value).shortValue()); } else { s = new Short(value.toString()); } statement.setShort(position, s.shortValue()); break; case Types.TIME: //System.out.println("TIME"); Time t = null; if (value instanceof Time) { t = (Time) value; } else if (value instanceof java.util.Date) { t = new Time(((java.util.Date) value).getTime()); } else { t = Time.valueOf(value.toString()); } statement.setTime(position, t); break; case Types.TIMESTAMP: //System.out.println("TIMESTAMP"); Timestamp ts = null; if (value instanceof Time) { ts = (Timestamp) value; } else if (value instanceof java.util.Date) { ts = new Timestamp(((java.util.Date) value).getTime()); } else { ts = Timestamp.valueOf(value.toString()); } statement.setTimestamp(position, ts); break; case Types.ARRAY: //System.out.println("ARRAY"); statement.setArray(position, (Array) value); // no way to convert string to array break; case Types.STRUCT: //System.out.println("STRUCT"); case Types.OTHER: //System.out.println("OTHER"); statement.setObject(position, value); break; case Types.LONGVARBINARY: //System.out.println("LONGVARBINARY"); statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime())); break; case Types.VARCHAR: //System.out.println("VARCHAR"); statement.setString(position, value.toString()); break; case Types.BLOB: //System.out.println("BLOB"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else { Blob blob = null; if (value instanceof Blob) { blob = (Blob) value; } else if (value instanceof File) { file = (File) value; blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof String) { file = new File((String) value); blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof Part) { Part anyFile = (Part) value; blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize()); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); statement.setBlob(position, blob); } break; case Types.VARBINARY: //System.out.println("VARBINARY"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else if (value instanceof Part) { statement.setBinaryStream(position, ((Part) value).getInputStream(), ((Part) value).getSize()); } else { if (value instanceof File) { file = (File) value; } else if (value instanceof String) { file = new File((String) value); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); FileInputStream input = new FileInputStream(file); statement.setBinaryStream(position, input, (int) file.length()); } break; case Types.INTEGER: //System.out.println("INTEGER"); Integer i = null; if (value instanceof Integer) { i = (Integer) value; } else if (value instanceof Number) { i = new Integer(((Number) value).intValue()); } else { i = new Integer(value.toString()); } statement.setInt(position, i.intValue()); break; case Types.BIT: //System.out.println("BIT"); Boolean bo = null; if (value instanceof Boolean) { bo = (Boolean) value; } else if (value instanceof Number) { bo = BooleanUtils.toBooleanObject(((Number) value).intValue() == 1); } else { bo = BooleanUtils.toBooleanObject(value.toString()); } statement.setBoolean(position, bo.booleanValue()); break; default: //System.out.println("default"); throw new SQLException("Impossible exception - invalid type "); } //System.out.println("========================================================================"); }
From source file:org.openmrs.util.databasechange.MigrateConceptReferenceTermChangeSet.java
/** * @see liquibase.change.custom.CustomTaskChange#execute(liquibase.database.Database) *///from w w w.ja va 2 s. c om @Override public void execute(Database database) throws CustomChangeException { final JdbcConnection connection = (JdbcConnection) database.getConnection(); Boolean prevAutoCommit = null; PreparedStatement selectTypes = null; PreparedStatement batchUpdateMap = null; PreparedStatement selectMap = null; PreparedStatement updateMapTerm = null; PreparedStatement insertTerm = null; PreparedStatement updateMapType = null; try { prevAutoCommit = connection.getAutoCommit(); connection.setAutoCommit(false); //Prepare a list of types and their ids. Map<String, Integer> typesToIds = new HashMap<String, Integer>(); selectTypes = connection.prepareStatement("select * from concept_map_type"); selectTypes.execute(); ResultSet selectTypeResult = selectTypes.getResultSet(); while (selectTypeResult.next()) { typesToIds.put(selectTypeResult.getString("name").trim().toUpperCase(), selectTypeResult.getInt("concept_map_type_id")); } selectTypes.close(); //The FK on concept_reference_term_id is not yet created so we are safe to copy over IDs. //The trims are done to be able to compare properly. batchUpdateMap = connection.prepareStatement( "update concept_reference_map set" + " concept_reference_term_id = concept_map_id," + " source_code = trim(source_code), comment = trim(comment)"); batchUpdateMap.execute(); batchUpdateMap.close(); //Preparing statements for use in the loop. updateMapTerm = connection.prepareStatement( "update concept_reference_map set" + " concept_reference_term_id = ? where concept_map_id = ?"); insertTerm = connection.prepareStatement("insert into concept_reference_term" + " (concept_reference_term_id, uuid, concept_source_id, code, creator, date_created, description)" + " values (?, ?, ?, ?, ?, ?, ?)"); updateMapType = connection.prepareStatement( "update concept_reference_map set" + " concept_map_type_id = ? where concept_map_id = ?"); int prevSource = -1; String prevSourceCode = null; String prevComment = null; int prevInsertedTerm = -1; //In addition to source and source_code we order by UUID to always insert the same term if run on different systems. selectMap = connection.prepareStatement( "select * from concept_reference_map" + " order by source, source_code, uuid"); selectMap.execute(); final ResultSet selectMapResult = selectMap.getResultSet(); while (selectMapResult.next()) { final int conceptMapId = selectMapResult.getInt("concept_map_id"); final int source = selectMapResult.getInt("source"); final String sourceCode = selectMapResult.getString("source_code"); final String comment = selectMapResult.getString("comment"); final int creator = selectMapResult.getInt("creator"); final Date dateCreated = selectMapResult.getDate("date_created"); final String uuid = selectMapResult.getString("uuid"); final Integer mapTypeId = determineMapTypeId(comment, typesToIds); final int updatedMapTypeId = (mapTypeId == null) ? typesToIds.get(DEFAULT_CONCEPT_MAP_TYPE) : mapTypeId; updateMapType.setInt(1, updatedMapTypeId); updateMapType.setInt(2, conceptMapId); updateMapType.execute(); if (updateMapType.getUpdateCount() != 1) { throw new CustomChangeException("Failed to set map type: " + mapTypeId + " for map: " + conceptMapId + ", updated rows: " + updateMapType.getUpdateCount()); } if (source == prevSource && (sourceCode == prevSourceCode || (sourceCode != null && sourceCode.equals(prevSourceCode)))) { if (mapTypeId == null && comment != null && !comment.equals(prevComment)) { log.warn("Lost comment '" + comment + "' for map " + conceptMapId + ". Preserved comment " + prevComment); } //We need to use the last inserted term. updateMapTerm.setInt(1, prevInsertedTerm); updateMapTerm.setInt(2, conceptMapId); updateMapTerm.execute(); if (updateMapTerm.getUpdateCount() != 1) { throw new CustomChangeException( "Failed to set reference term: " + prevInsertedTerm + " for map: " + conceptMapId + ", updated rows: " + updateMapTerm.getUpdateCount()); } } else { insertTerm.setInt(1, conceptMapId); //We need to guaranty that UUIDs are always the same when run on different systems. insertTerm.setString(2, UUID.nameUUIDFromBytes(uuid.getBytes()).toString()); insertTerm.setInt(3, source); insertTerm.setString(4, sourceCode); insertTerm.setInt(5, creator); insertTerm.setDate(6, dateCreated); if (mapTypeId == null) { insertTerm.setString(7, comment); } else { insertTerm.setString(7, null); } insertTerm.execute(); prevInsertedTerm = conceptMapId; } prevSource = source; prevSourceCode = sourceCode; prevComment = comment; } selectMap.close(); updateMapType.close(); updateMapTerm.close(); insertTerm.close(); connection.commit(); } catch (Exception e) { try { if (connection != null) { connection.rollback(); } } catch (Exception ex) { log.error("Failed to rollback", ex); } throw new CustomChangeException(e); } finally { closeStatementQuietly(selectTypes); closeStatementQuietly(batchUpdateMap); closeStatementQuietly(selectMap); closeStatementQuietly(updateMapTerm); closeStatementQuietly(insertTerm); closeStatementQuietly(updateMapType); if (connection != null && prevAutoCommit != null) { try { connection.setAutoCommit(prevAutoCommit); } catch (DatabaseException e) { log.error("Failed to reset auto commit", e); } } } }
From source file:org.wso2.ws.dataservice.DBUtils.java
public static PreparedStatement getProcessedPreparedStatement(HashMap inputs, HashMap params, HashMap paramOrder, HashMap originalParamNames, HashMap paramTypes, Connection conn, String sqlStatement, String callee, String serviceName) throws AxisFault { String paramName = null;/*from w ww .j a v a 2 s .c o m*/ String originalParamName = null; String sqlType = null; String value = null; String paramType = null; log.debug("[" + serviceName + "] Processing prepared statement for SQL " + sqlStatement); Set paramNames = params.keySet(); Object pramNameArray[] = paramNames.toArray(); try { PreparedStatement sqlQuery = null; if ("SQL".equals(callee)) { sqlQuery = conn.prepareStatement(sqlStatement); //SQL expects parameters, but not params set in config file if (sqlStatement.indexOf("?") > -1 && pramNameArray.length == 0) { throw new AxisFault( "[" + serviceName + "] SQL : " + sqlStatement + " expects one or more parameters. " + "But none is mentioned in the configuration file."); } } else if ("STORED-PROCEDURE".equals(callee)) { sqlQuery = conn.prepareCall(sqlStatement); } for (int i = 0; i < pramNameArray.length; i++) { paramName = (String) paramOrder.get(new Integer(i + 1)); originalParamName = (String) originalParamNames.get(new Integer(i + 1)); sqlType = (String) params.get(paramName); paramType = (String) paramTypes.get(paramName); value = (String) inputs.get(paramName); log.debug("[" + serviceName + "] Param name : " + paramName + " SQL Type : " + sqlType + " Value : " + value); if ("IN".equals(paramType) || "INOUT".equals(paramType)) { if (value == null || value.trim().length() == 0) { log.error("[" + serviceName + "] Empty value found for parameter : " + originalParamName); throw new AxisFault( "[" + serviceName + "] Empty value found for parameter : " + originalParamName); } } //work-a-round for setting NULL if ("NULL".equalsIgnoreCase(value)) { value = null; } //TODO : work-a-round for setting space if (sqlType == null) { // Defaults to string if ("IN".equals(paramType)) { sqlQuery.setString(i + 1, value); } else if ("INOUT".equals(paramType)) { sqlQuery.setString(i + 1, value); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } } else if (DBConstants.DataTypes.INTEGER.equals(sqlType)) { if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setInt(i + 1, Integer.parseInt(value)); } else { ((CallableStatement) sqlQuery).setInt(i + 1, Integer.parseInt(value)); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setInt(i + 1, Integer.parseInt(value)); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.INTEGER); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.INTEGER); } } else if (DBConstants.DataTypes.STRING.equals(sqlType)) { if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setString(i + 1, value); } else { ((CallableStatement) sqlQuery).setString(i + 1, value); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setString(i + 1, value); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.VARCHAR); } } else if (DBConstants.DataTypes.DOUBLE.equals(sqlType)) { if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setDouble(i + 1, Double.parseDouble(value)); } else { ((CallableStatement) sqlQuery).setDouble(i + 1, Double.parseDouble(value)); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setDouble(i + 1, Double.parseDouble(value)); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DOUBLE); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DOUBLE); } } else if (DBConstants.DataTypes.DATE.equals(sqlType)) { try { //Only yyyy-MM-dd part is needed String modifiedValue = value.substring(0, 10); if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setDate(i + 1, Date.valueOf(modifiedValue)); } else { ((CallableStatement) sqlQuery).setDate(i + 1, Date.valueOf(modifiedValue)); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setDate(i + 1, Date.valueOf(modifiedValue)); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DATE); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DATE); } } catch (IllegalArgumentException e) { log.error("Incorrect date format(" + value + ") for parameter : " + paramName, e); throw new AxisFault("Incorrect date format for parameter : " + paramName + ".Date should be in yyyy-mm-dd format.", e); } } else if (DBConstants.DataTypes.TIMESTAMP.equals(sqlType)) { Timestamp timestamp = getTimestamp(value, paramName); if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setTimestamp(i + 1, timestamp); } else { ((CallableStatement) sqlQuery).setTimestamp(i + 1, timestamp); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setTimestamp(i + 1, timestamp); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIMESTAMP); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIMESTAMP); } } else if (DBConstants.DataTypes.TIME.equals(sqlType)) { Time time = getTime(value, paramName); if ("IN".equals(paramType)) { if ("SQL".equals(callee)) { sqlQuery.setTime(i + 1, time); } else { ((CallableStatement) sqlQuery).setTime(i + 1, time); } } else if ("INOUT".equals(paramType)) { ((CallableStatement) sqlQuery).setTime(i + 1, time); ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIME); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.TIME); } } else { log.error("[" + serviceName + "] Unsupported data type : " + sqlType + " as input parameter."); throw new AxisFault("[" + serviceName + "] Found Unsupported data type : " + sqlType + " as input parameter."); } } return sqlQuery; } catch (NumberFormatException e) { log.error("[" + serviceName + "] Incorrect value found for parameter : " + originalParamName, e); throw new AxisFault("[" + serviceName + "] Incorrect value found for parameter : " + originalParamName, e); } catch (SQLException e) { log.error("[" + serviceName + "] Error occurred while preparing prepared statement for sql : " + sqlStatement, e); throw new AxisFault("[" + serviceName + "] Error occurred while preparing prepared statement for sql : " + sqlStatement, e); } }
From source file:com.emr.utilities.CSVLoader.java
/** * Parse CSV file using OpenCSV library and load in * given database table. /* ww w . j a v a 2 s . co m*/ * @param csvFile {@link String} Input CSV file * @param tableName {@link String} Database table name to import data * @param truncateBeforeLoad {@link boolean} Truncate the table before inserting * new records. * @param destinationColumns {@link String[]} Array containing the destination columns */ public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad, String[] destinationColumns, List columnsToBeMapped) throws Exception { CSVReader csvReader = null; if (null == this.connection) { throw new Exception("Not a valid connection."); } try { csvReader = new CSVReader(new FileReader(csvFile), this.seprator); } catch (Exception e) { String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e); JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace, "File Error", JOptionPane.ERROR_MESSAGE); throw new Exception("Error occured while executing file. " + stacktrace); } String[] headerRow = csvReader.readNext(); if (null == headerRow) { throw new FileNotFoundException( "No columns defined in given CSV file." + "Please check the CSV file format."); } //Get indices of columns to be mapped List mapColumnsIndices = new ArrayList(); for (Object o : columnsToBeMapped) { String column = (String) o; column = column.substring(column.lastIndexOf(".") + 1, column.length()); int i; for (i = 0; i < headerRow.length; i++) { if (headerRow[i].equals(column)) { mapColumnsIndices.add(i); } } } String questionmarks = StringUtils.repeat("?,", headerRow.length); questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1); String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName); query = query.replaceFirst(KEYS_REGEX, StringUtils.join(destinationColumns, ",")); query = query.replaceFirst(VALUES_REGEX, questionmarks); String log_query = query.substring(0, query.indexOf("VALUES(")); String[] nextLine; Connection con = null; PreparedStatement ps = null; PreparedStatement ps2 = null; PreparedStatement reader = null; ResultSet rs = null; try { con = this.connection; con.setAutoCommit(false); ps = con.prepareStatement(query); File file = new File("sqlite/db"); if (!file.exists()) { file.createNewFile(); } db = new SQLiteConnection(file); db.open(true); //if destination table==person, also add an entry in the table person_identifier //get column indices for the person_id and uuid columns int person_id_column_index = -1; int uuid_column_index = -1; int maxLength = 100; int firstname_index = -1; int middlename_index = -1; int lastname_index = -1; int clanname_index = -1; int othername_index = -1; if (tableName.equals("person")) { int i; ps2 = con.prepareStatement( "insert ignore into person_identifier(person_id,identifier_type_id,identifier) values(?,?,?)"); for (i = 0; i < headerRow.length; i++) { if (headerRow[i].equals("person_id")) { person_id_column_index = i; } if (headerRow[i].equals("uuid")) { uuid_column_index = i; } /*if(headerRow[i].equals("first_name")){ System.out.println("Found firstname index: " + i); firstname_index=i; } if(headerRow[i].equals("middle_name")){ System.out.println("Found firstname index: " + i); middlename_index=i; } if(headerRow[i].equals("last_name")){ System.out.println("Found firstname index: " + i); lastname_index=i; } if(headerRow[i].equals("clan_name")){ System.out.println("Found firstname index: " + i); clanname_index=i; } if(headerRow[i].equals("other_name")){ System.out.println("Found firstname index: " + i); othername_index=i; }*/ } } if (truncateBeforeLoad) { //delete data from table before loading csv try (Statement stmnt = con.createStatement()) { stmnt.execute("DELETE FROM " + tableName); stmnt.close(); } } if (tableName.equals("person")) { try (Statement stmt2 = con.createStatement()) { stmt2.execute( "ALTER TABLE person CHANGE COLUMN first_name first_name VARCHAR(50) NULL DEFAULT NULL AFTER person_guid,CHANGE COLUMN middle_name middle_name VARCHAR(50) NULL DEFAULT NULL AFTER first_name,CHANGE COLUMN last_name last_name VARCHAR(50) NULL DEFAULT NULL AFTER middle_name;"); stmt2.close(); } } final int batchSize = 1000; int count = 0; Date date = null; while ((nextLine = csvReader.readNext()) != null) { if (null != nextLine) { int index = 1; int person_id = -1; String uuid = ""; int identifier_type_id = 3; if (tableName.equals("person")) { reader = con.prepareStatement( "select identifier_type_id from identifier_type where identifier_type_name='UUID'"); rs = reader.executeQuery(); if (!rs.isBeforeFirst()) { //no uuid row //insert it Integer numero = 0; Statement stmt = con.createStatement(); numero = stmt.executeUpdate( "insert into identifier_type(identifier_type_id,identifier_type_name) values(50,'UUID')", Statement.RETURN_GENERATED_KEYS); ResultSet rs2 = stmt.getGeneratedKeys(); if (rs2.next()) { identifier_type_id = rs2.getInt(1); } rs2.close(); stmt.close(); } else { while (rs.next()) { identifier_type_id = rs.getInt("identifier_type_id"); } } } int counter = 1; String temp_log = log_query + "VALUES("; //string to be logged for (String string : nextLine) { //if current index is in the list of columns to be mapped, we apply that mapping for (Object o : mapColumnsIndices) { int i = (int) o; if (index == (i + 1)) { //apply mapping to this column string = applyDataMapping(string); } } if (tableName.equals("person")) { //get person_id and uuid if (index == (person_id_column_index + 1)) { person_id = Integer.parseInt(string); } if (index == (uuid_column_index + 1)) { uuid = string; } } //check if string is a date if (string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4} \\d{2}:\\d{2}:\\d{2}") || string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4}")) { java.sql.Date dt = formatDate(string); temp_log = temp_log + "'" + dt.toString() + "'"; ps.setDate(index++, dt); } else { if ("".equals(string)) { temp_log = temp_log + "''"; ps.setNull(index++, Types.NULL); } else { temp_log = temp_log + "'" + string + "'"; ps.setString(index++, string); } } if (counter < headerRow.length) { temp_log = temp_log + ","; } else { temp_log = temp_log + ");"; System.out.println(temp_log); } counter++; } if (tableName.equals("person")) { if (!"".equals(uuid) && person_id != -1) { ps2.setInt(1, person_id); ps2.setInt(2, identifier_type_id); ps2.setString(3, uuid); ps2.addBatch(); } } ps.addBatch(); } if (++count % batchSize == 0) { ps.executeBatch(); if (tableName.equals("person")) { ps2.executeBatch(); } } } ps.executeBatch(); // insert remaining records if (tableName.equals("person")) { ps2.executeBatch(); } con.commit(); } catch (Exception e) { if (con != null) con.rollback(); if (db != null) db.dispose(); String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e); JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace, "File Error", JOptionPane.ERROR_MESSAGE); throw new Exception("Error occured while executing file. " + stacktrace); } finally { if (null != reader) reader.close(); if (null != ps) ps.close(); if (null != ps2) ps2.close(); if (null != con) con.close(); csvReader.close(); } }