List of usage examples for java.sql PreparedStatement setTimestamp
void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;
java.sql.Timestamp value. From source file:edu.ku.brc.specify.toycode.mexconabio.BuildTags.java
/** * //from w w w . j a va 2s. c om */ public void process() throws SQLException { int dupAgents = 0; int dupLocality = 0; int unknown = 0; boolean doAll = false; BasicSQLUtils.setDBConnection(dbConn); boolean doTrim = false; if (doTrim || doAll) { String trimNamesSQL = "UPDATE tagger SET first=TRIM(first),last=TRIM(last),company=TRIM(company),address1=TRIM(address1),address2=TRIM(address2),city=TRIM(city),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL); String removeQuote = "UPDATE tagger SET first=SUBSTRING_INDEX(first, '\"', -1),last=SUBSTRING_INDEX(last, '\"', -1),company=SUBSTRING_INDEX(company, '\"', -1),address1=SUBSTRING_INDEX(address1, '\"', -1)," + "address2=SUBSTRING_INDEX(address2, '\"', -1),city=SUBSTRING_INDEX(city, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote); String trimNamesSQL2 = "UPDATE tag SET city=TRIM(city),county=TRIM(county),state=TRIM(state)"; BasicSQLUtils.update(srcDBConn2, trimNamesSQL2); String removeQuote2 = "UPDATE tag SET city=SUBSTRING_INDEX(city, '\"', -1), county=SUBSTRING_INDEX(county, '\"', -1), state=SUBSTRING_INDEX(state, '\"', -1)"; BasicSQLUtils.update(srcDBConn2, removeQuote2); } IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); IdHashMapper agentMapper; Division division = (Division) session.get(Division.class, 2); initialPrepareStatements(); BasicSQLUtils.update(srcDBConn, "UPDATE tag SET `Date` = null WHERE Date = '0000-00-00'"); //IdMapperMgr.setSkippingOldTableCheck(true); boolean doAgents = false; if (doAgents || doAll) { agentMapper = new IdTableMapper("agent", "AgentID", false, false); String sql = "SELECT first, last, company, address1, address2, city, state, country, zip, phone, fax, enail, tnum FROM tagger ORDER BY tnum"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Agents..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; while (rs.next()) { String first = rs.getString(1); String last = rs.getString(2); String company = rs.getString(3); String addr1 = rs.getString(4); String addr2 = rs.getString(5); String city = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); String zip = rs.getString(9); String phone = rs.getString(10); String fax = rs.getString(11); String email = rs.getString(12); Integer oldId = rs.getInt(13); if (oldId == null) { log.error("Null primary Id: " + last + " " + first); continue; } Agent agent = getAgent(first, last, city, state); Integer agentId = null; if (agent == null) { agent = new Agent(); agent.initialize(); agent.setFirstName(first); agent.setLastName(last); agent.setEmail(email); agent.setRemarks(company); agent.setDivision(division); Address addr = new Address(); addr.initialize(); addr.setAddress(addr1); addr.setAddress2(addr2); addr.setCity(city); addr.setState(state); addr.setCountry(country); addr.setPostalCode(zip); addr.setPhone1(phone); addr.setFax(fax); agent.getAddresses().add(addr); addr.setAgent(agent); Transaction trans = null; try { trans = session.beginTransaction(); session.saveOrUpdate(agent); session.saveOrUpdate(addr); trans.commit(); agentId = agent.getId(); } catch (Exception ex) { ex.printStackTrace(); try { if (trans != null) trans.rollback(); } catch (Exception ex2) { ex2.printStackTrace(); } } } else { agentId = agent.getId(); dupAgents++; //System.out.println("Found Agent: "+first+", "+last); } agentMapper.put(oldId, agentId); cnt++; if (cnt % 500 == 0) { System.out.println("Agents: " + cnt); } if (cnt % 400 == 0) { HibernateUtil.closeSession(); session = HibernateUtil.getCurrentSession(); hibSession = new HibernateDataProviderSession(session); } } rs.close(); stmt.close(); division = (Division) session.get(Division.class, 2); } else { //agentMapper = idMapperMgr.addTableMapper("agent", "AgentID", false); IdHashMapper.setEnableDelete(false); agentMapper = new IdTableMapper("agent", "AgentID", null, false, false); } System.out.println("Duplicated Agent: " + dupAgents); boolean doTags = true; if (doTags || doAll) { HashMap<String, Integer> localityHash = new HashMap<String, Integer>(); HashMap<Integer, String> geoFullNameHash = new HashMap<Integer, String>(); int divId = 2; int dspId = 3; int colId = 4; String sql = "SELECT t.tagid, t.`date`, t.wild, t.gender, t.city, t.county, t.state, t.country, t.zip, t.observations, t.lat, t.lon, t.sunangle, p.tnum " + "FROM tag AS t Inner Join page AS p ON t.page = p.page "; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, Method, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); if (tag != null && tag.startsWith("ERR")) continue; Date date = rs.getDate(2); String wild = rs.getString(3); String gender = rs.getString(4); String city = rs.getString(5); String county = rs.getString(6); String state = rs.getString(7); String country = rs.getString(8); //String zip = rs.getString(9); String obs = rs.getString(10); double lat = rs.getDouble(11); double lon = rs.getDouble(12); double angle = rs.getDouble(13); Integer taggerId = rs.getInt(14); String locName = null; String fullName = null; Integer locId = null; Integer geoId = getGeography(country, state, county); if (geoId != null) { //locName = localityHash.get(geoId); fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); } else { unknown++; fullName = "Unknown"; locName = buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setDouble(1, lat); lcStmt.setDouble(2, lon); lcStmt.setByte(3, (byte) 0); lcStmt.setString(4, Double.toString(lat)); lcStmt.setString(5, Double.toString(lon)); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setDouble(8, angle); lcStmt.setString(9, locName); lcStmt.setObject(10, geoId); lcStmt.setTimestamp(11, ts); lcStmt.setInt(12, 1); lcStmt.setInt(13, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); localityHash.put(locName, locId); } else { dupLocality++; } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setString(2, wild); ceStmt.setInt(3, dspId); ceStmt.setInt(4, locId); ceStmt.setTimestamp(5, ts); ceStmt.setInt(6, 1); ceStmt.setInt(7, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, obs); coStmt.setInt(5, colId); coStmt.setInt(6, colId); coStmt.setInt(7, ceId); coStmt.setTimestamp(8, ts); coStmt.setInt(9, 1); coStmt.setInt(10, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); //Integer coltrId = null; if (taggerId != null) { Integer agentId = agentMapper.get(taggerId); //System.out.println(agentId); if (agentId != null) { // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); //coltrId = BasicSQLUtils.getInsertedId(clStmt); //BasicSQLUtils.getInsertedId(clStmt); } else { log.debug("Couldn't find Agent in DB for tagger id (tnum): " + taggerId + " AgentID:: " + agentId); } } else { log.debug("Couldn't find Mapped Id for tagger id (tnum): " + taggerId); } cnt++; if (cnt % 1000 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); System.out.println("Duplicated Agent: " + dupAgents); System.out.println("Duplicated Localities: " + dupLocality); System.out.println("Unknown Localities: " + unknown); System.out.println("Localities: " + BasicSQLUtils.getCountAsInt(dbConn, "SELECT COUNT(*) FROM locality")); } }
From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java
/** * Sets a specific value on a prepared statement */// w ww .j a va2s. c o m protected void setStatementValue(PreparedStatement s, MetaField f, int index, Object value) throws SQLException { int j = index; switch (f.getType()) { case MetaField.BOOLEAN: { if (value == null) { s.setNull(j, Types.BIT); } else if (value instanceof Boolean) { s.setBoolean(j, ((Boolean) value).booleanValue()); } else { s.setBoolean(j, Boolean.valueOf(value.toString()).booleanValue()); } } break; case MetaField.BYTE: { if (value == null) { s.setNull(j, Types.TINYINT); } else if (value instanceof Byte) { s.setByte(j, ((Byte) value).byteValue()); } else { s.setByte(j, Byte.valueOf(value.toString()).byteValue()); } } break; case MetaField.SHORT: { if (value == null) { s.setNull(j, Types.SMALLINT); } else if (value instanceof Short) { s.setShort(j, ((Short) value).shortValue()); } else { s.setShort(j, Short.valueOf(value.toString()).shortValue()); } } break; case MetaField.INT: { if (value == null) { s.setNull(j, Types.INTEGER); } else if (value instanceof Integer) { s.setInt(j, ((Integer) value).intValue()); } else { s.setInt(j, Integer.valueOf(value.toString()).intValue()); } } break; case MetaField.DATE: // NOTE DATE IS TREATED AS LONG! { if (value == null) { s.setNull(j, Types.TIMESTAMP); } else if (value instanceof java.util.Date) { s.setTimestamp(j, new Timestamp(((java.util.Date) value).getTime())); } else { s.setTimestamp(j, new Timestamp(Long.valueOf(value.toString()).longValue())); } } break; case MetaField.LONG: { if (value == null) { s.setNull(j, Types.BIGINT); } else if (value instanceof Long) { s.setLong(j, ((Long) value).longValue()); } else { s.setLong(j, Long.valueOf(value.toString()).longValue()); } } break; // WARNING: This should not be a valid key case MetaField.FLOAT: { if (value == null) { s.setNull(j, Types.FLOAT); } else if (value instanceof Float) { s.setFloat(j, ((Float) value).floatValue()); } else { s.setFloat(j, Float.valueOf(value.toString()).floatValue()); } } break; // WARNING: This should not be a valid key case MetaField.DOUBLE: { if (value == null) { s.setNull(j, Types.DOUBLE); } else if (value instanceof Double) { s.setDouble(j, ((Double) value).doubleValue()); } else { s.setDouble(j, Double.valueOf(value.toString()).doubleValue()); } } break; case MetaField.STRING: if (value == null) { s.setNull(j, Types.VARCHAR); } else { s.setString(j, value.toString()); } break; case MetaField.OBJECT: //if ( value == null ) // s.setNull( j, Types.BLOB ); //else s.setObject(j, value); break; } }
From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java
@Override public List<FreeBusy> getFreeBusy(ObmDomain domain, FreeBusyRequest fbr) { String fb = "SELECT e.event_id, e.event_date, e.event_duration, event_allday" + ", e.event_repeatkind, e.event_repeatdays, e.event_repeatfrequence, e.event_endrepeat " + "FROM Event e " + "INNER JOIN EventLink att ON att.eventlink_event_id=e.event_id " + "LEFT JOIN EventLink organizer ON organizer.eventlink_event_id=e.event_id " + "AND organizer.eventlink_entity_id=? " + "WHERE att.eventlink_entity_id = ? " + "AND event_type=? " + "AND ((event_repeatkind != 'none' AND (event_endrepeat IS NULL OR event_endrepeat >= ?)) " + "OR (event_date >= ? AND event_date <= ?) ) " + "AND att.eventlink_state!=? " + "AND event_opacity=? " + "AND (event_privacy!=? OR organizer.eventlink_entity_id IS NOT NULL)"; PreparedStatement ps = null; ResultSet rs = null;// ww w . j a v a 2 s . c om Connection con = null; List<FreeBusy> ret = new LinkedList<FreeBusy>(); try { con = obmHelper.getConnection(); for (Attendee att : fbr.getAttendees()) { logger.info("freebusy " + att.getEmail() + " dstart: " + fbr.getStart() + " dend: " + fbr.getEnd()); ObmUser attendee = userDao.findUser(att.getEmail(), domain); ObmUser organizer = userDao.findUser(fbr.getOwner(), domain); Calendar cal = getGMTCalendar(); if (attendee != null) { ps = null; rs = null; try { ps = con.prepareStatement(fb); int idx = 1; ps.setInt(idx++, organizer.getEntityId().getId()); ps.setInt(idx++, attendee.getEntityId().getId()); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, EventType.VEVENT.toString())); ps.setTimestamp(idx++, new Timestamp(fbr.getStart().getTime())); ps.setTimestamp(idx++, new Timestamp(fbr.getStart().getTime())); ps.setTimestamp(idx++, new Timestamp(fbr.getEnd().getTime())); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VPARTSTAT, State.DECLINED.toString())); ps.setObject(idx++, obmHelper.getDBCP().getJdbcObject(ObmHelper.VOPACITY, EventOpacity.OPAQUE.toString())); ps.setInt(idx++, EventPrivacy.CONFIDENTIAL.toInteger()); rs = ps.executeQuery(); FreeBusy freebusy = new FreeBusy(); freebusy.setStart(fbr.getStart()); freebusy.setEnd(fbr.getEnd()); freebusy.setOwner(fbr.getOwner()); freebusy.setUid(fbr.getUid()); freebusy.setAtt(att); while (rs.next()) { EventRecurrence er = eventRecurrenceFromCursor(cal, rs); cal.setTimeInMillis(rs.getTimestamp("event_date").getTime()); if (er == null || RecurrenceKind.none.equals(er.getKind())) { cal.setTimeInMillis(rs.getTimestamp("event_date").getTime()); freebusy.addFreeBusyInterval(getFreeBusyInterval(cal.getTime(), rs)); } else { Set<Date> extDate = getAllDateEventException(con, new EventObmId(rs.getInt("event_id"))); List<Date> recurDates = ical4jHelper.dateInInterval(er, cal.getTime(), fbr.getStart(), fbr.getEnd(), extDate); for (Date rd : recurDates) { FreeBusyInterval line = new FreeBusyInterval(); cal.setTimeInMillis(rd.getTime()); line.setStart(cal.getTime()); line.setAllDay(rs.getBoolean("event_allday")); line.setDuration(rs.getInt("event_duration")); freebusy.addFreeBusyInterval(line); } } } ret.add(freebusy); logger.info("freebusy found " + freebusy.getFreeBusyIntervals().size() + " events."); } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { obmHelper.cleanup(null, ps, rs); } } else { logger.info("User " + att.getEmail() + " doesn't exist"); } } } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { obmHelper.cleanup(con, null, null); } return ret; }
From source file:edu.ku.brc.specify.toycode.RegPivot.java
/** * @param newTblName/*from w ww .ja v a 2 s . co m*/ * @param stmt * @param pStmt * @param fillSQL * @param secInx * @param dbFieldTypes * @param dbFieldNames * @param inxToName * @return * @throws SQLException */ private int fillTrackTableX(final String newTblName, final Statement stmt, final PreparedStatement pStmt, final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes, final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException { System.out.println("Filling Track Table."); int instCnt = 0; HashMap<String, Object> nameToVals = new HashMap<String, Object>(); System.out.println(fillSQL); String prevId = null; ResultSet rs = stmt.executeQuery(fillSQL); ResultSetMetaData rsmd = rs.getMetaData(); HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>(); for (int c = 1; c <= rsmd.getColumnCount(); c++) { nameToIndex.put(rsmd.getColumnName(c), c); System.out.println(c + " - " + rsmd.getColumnName(c)); } while (rs.next()) { String id = rs.getString(1); if (prevId == null) prevId = id; if (!prevId.equals(id)) { for (int i = 1; i < secInx; i++) { //System.out.println("Put: "+dbFieldNames.get(i-1)+" "+dbFieldTypes.get(i-1));//+" = "+rs.getObject(i)); if (dbFieldTypes.get(i - 1) == java.sql.Types.TIMESTAMP) { try { String ts = rs.getString(i); if (StringUtils.isNotEmpty(ts) && ts.equals("0000-00-00 00:00:00")) { //nameToVals.put(dbFieldNames.get(i-1), null); continue; } } catch (Exception ex) { nameToVals.put(dbFieldNames.get(i - 1), null);//"2000-01-01 00:00:00"); continue; } } nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i)); } for (int i = 0; i < dbFieldNames.size(); i++) { int fInx = i + 1; String name = inxToName.get(i); Object value = nameToVals.get(name); pStmt.setObject(fInx, null); int typ = dbFieldTypes.get(i); if (value != null) { switch (typ) { case java.sql.Types.INTEGER: if (value instanceof Integer) { pStmt.setInt(fInx, (Integer) value); } break; case java.sql.Types.VARCHAR: if (value instanceof String) { pStmt.setString(fInx, (String) value); } break; case java.sql.Types.TIMESTAMP: { if (value instanceof Timestamp) { pStmt.setTimestamp(fInx, (Timestamp) value); } break; } } } else { pStmt.setObject(fInx, null); } } pStmt.executeUpdate(); prevId = id; nameToVals.clear(); } String name = rs.getString(secInx); name = StringUtils.replace(name, "(", "_"); name = StringUtils.replace(name, ")", "_"); Integer intVal = (Integer) rs.getObject(secInx + 1); String strVal = (String) rs.getObject(secInx + 2); nameToVals.put(name, strVal != null ? strVal : intVal); } String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP"; BasicSQLUtils.update(connection, alterSQL); alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup"; BasicSQLUtils.update(connection, alterSQL); alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country"; BasicSQLUtils.update(connection, alterSQL); return instCnt; }
From source file:edu.ku.brc.specify.conversion.SpecifyDBConverter.java
/** * @param newDBConn//from ww w.j ava 2 s. c o m * @throws SQLException */ private void updateVersionInfo(final Connection newDBConn) throws SQLException { String appVerStr = null; String schemaVersion = null; Integer spverId = null; Integer recVerNum = 1; try { System.setProperty(SchemaUpdateService.factoryName, "edu.ku.brc.specify.dbsupport.SpecifySchemaUpdateService"); // needed for updating the schema schemaVersion = SchemaUpdateService.getInstance().getDBSchemaVersionFromXML(); } catch (Exception ex) { ex.printStackTrace(); } Vector<Object[]> rows = BasicSQLUtils.query(newDBConn, "SELECT AppVersion, SchemaVersion, SpVersionID, Version FROM spversion"); if (rows.size() == 1) { Object[] row = (Object[]) rows.get(0); appVerStr = row[0].toString(); schemaVersion = row[1].toString(); spverId = (Integer) row[2]; recVerNum = (Integer) row[3]; } if (appVerStr != null) { appVerStr = UIHelper.getInstall4JInstallString(); if (appVerStr == null) { do { appVerStr = JOptionPane.showInputDialog("Enter Specify App version:"); } while (StringUtils.isEmpty(appVerStr)); } PreparedStatement pStmt = newDBConn.prepareStatement( "UPDATE spversion SET AppVersion=?, SchemaVersion=?, Version=? WHERE SpVersionID = ?"); pStmt.setString(1, appVerStr); pStmt.setString(2, SchemaUpdateService.getInstance().getDBSchemaVersionFromXML()); pStmt.setInt(3, ++recVerNum); pStmt.setInt(4, spverId); if (pStmt.executeUpdate() != 1) { throw new RuntimeException("Problem updating SpVersion"); } } else { appVerStr = UIHelper.getInstall4JInstallString(); if (appVerStr == null) { do { appVerStr = JOptionPane.showInputDialog("Enter Specify App version:"); } while (StringUtils.isEmpty(appVerStr)); } PreparedStatement pStmt = newDBConn.prepareStatement( "INSERT INTO spversion (AppVersion, SchemaVersion, Version, TimestampCreated) VALUES(?,?,?,?)"); pStmt.setString(1, appVerStr); pStmt.setString(2, schemaVersion); pStmt.setInt(3, 0); pStmt.setTimestamp(4, new Timestamp(Calendar.getInstance().getTime().getTime())); if (pStmt.executeUpdate() != 1) { throw new RuntimeException("Problem inserting SpVersion"); } } }
From source file:com.webpagebytes.cms.local.WPBLocalDataStoreDao.java
private int buildStatementForInsertUpdate(Object obj, Set<String> ignoreFields, PreparedStatement preparedStatement, Connection connection) throws SQLException, WPBSerializerException { Class<? extends Object> kind = obj.getClass(); Field[] fields = kind.getDeclaredFields(); int fieldIndex = 0; for (int i = 0; i < fields.length; i++) { Field field = fields[i];//www . j a v a2s.co m field.setAccessible(true); boolean storeField = (field.getAnnotation(WPBAdminFieldKey.class) != null) || (field.getAnnotation(WPBAdminFieldStore.class) != null) || (field.getAnnotation(WPBAdminFieldTextStore.class) != null); if (storeField) { String fieldName = field.getName(); if (ignoreFields != null && ignoreFields.contains(fieldName)) { continue; } fieldIndex = fieldIndex + 1; Object value = null; try { PropertyDescriptor pd = new PropertyDescriptor(fieldName, kind); value = pd.getReadMethod().invoke(obj); } catch (Exception e) { throw new WPBSerializerException("Cannot get property value", e); } if (field.getType() == Long.class) { Long valueLong = (Long) value; if (valueLong != null) { preparedStatement.setLong(fieldIndex, valueLong); } else { preparedStatement.setNull(fieldIndex, Types.BIGINT); } } else if (field.getType() == String.class) { String valueString = (String) value; if (field.getAnnotation(WPBAdminFieldStore.class) != null || field.getAnnotation(WPBAdminFieldKey.class) != null) { if (valueString != null) { preparedStatement.setString(fieldIndex, valueString); } else { preparedStatement.setNull(fieldIndex, Types.VARCHAR); } } else if (field.getAnnotation(WPBAdminFieldTextStore.class) != null) { if (valueString != null) { Clob clob = connection.createClob(); clob.setString(1, valueString); preparedStatement.setClob(fieldIndex, clob); } else { preparedStatement.setNull(fieldIndex, Types.CLOB); } } } else if (field.getType() == Integer.class) { Integer valueInt = (Integer) value; if (valueInt != null) { preparedStatement.setInt(fieldIndex, valueInt); } else { preparedStatement.setNull(fieldIndex, Types.INTEGER); } } else if (field.getType() == Date.class) { Date date = (Date) value; if (date != null) { java.sql.Timestamp sqlDate = new java.sql.Timestamp(date.getTime()); preparedStatement.setTimestamp(fieldIndex, sqlDate); } else { preparedStatement.setNull(fieldIndex, Types.DATE); } } } } return fieldIndex; }
From source file:gemlite.core.internal.db.AsyncEventHelper.java
/** * Set column value at given index in a prepared statement. The implementation * tries using the matching underlying type to minimize any data type * conversions, and avoid creating wrapper Java objects (e.g. {@link Integer} * for primitive int).//from w w w .j a v a2 s . c om * * @param type * the SQL type of the column as specified by JDBC {@link Types} * class * @param ps * the prepared statement where the column value has to be set * @param row * the source row as a {@link ResultSet} from where the value has to * be extracted * @param rowPosition * the 1-based position of the column in the provided * <code>row</code> * @param paramIndex * the 1-based position of the column in the target prepared * statement (provided <code>ps</code> argument) * @param sync * the {@link DBSynchronizer} object, if any; it is used to store * whether the current driver is JDBC4 compliant to enable performing * BLOB/CLOB operations {@link PreparedStatement#setBinaryStream}, * {@link PreparedStatement#setCharacterStream} * * @throws SQLException * in case of an exception in setting parameters */ public final void setColumnInPrepStatement(String type, Object val, PreparedStatement ps, final DBSynchronizer sync, int paramIndex) throws SQLException { switch (type) { case JavaTypes.STRING: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.VARCHAR); else { final String realVal = (String) val; ps.setString(paramIndex, realVal); } break; case JavaTypes.INT1: case JavaTypes.INT2: case JavaTypes.INT3: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.INTEGER); else { final int realVal = (int) val; ps.setInt(paramIndex, realVal); } break; case JavaTypes.DOUBLE1: case JavaTypes.DOUBLE2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DOUBLE); else { final double realVal = (double) val; ps.setDouble(paramIndex, realVal); } break; case JavaTypes.FLOAT1: case JavaTypes.FLOAT2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.FLOAT); else { final float realVal = (float) val; ps.setDouble(paramIndex, realVal); } break; case JavaTypes.BOOLEAN1: case JavaTypes.BOOLEAN2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.BOOLEAN); else { final boolean realVal = (boolean) val; ps.setBoolean(paramIndex, realVal); } break; case JavaTypes.DATE_SQL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DATE); else { final Date realVal = (Date) val; ps.setDate(paramIndex, realVal); } break; case JavaTypes.DATE_UTIL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DATE); else { final java.util.Date realVal = (java.util.Date) val; ps.setDate(paramIndex, new Date(realVal.getTime())); } break; case JavaTypes.BIGDECIMAL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DECIMAL); else { final BigDecimal realVal = (BigDecimal) val; ps.setBigDecimal(paramIndex, realVal); } break; case JavaTypes.TIME: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.TIME); else { final Time realVal = (Time) val; ps.setTime(paramIndex, realVal); } break; case JavaTypes.TIMESTAMP: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.TIMESTAMP); else { final Timestamp realVal = (Timestamp) val; ps.setTimestamp(paramIndex, realVal); } break; case JavaTypes.OBJECT: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.JAVA_OBJECT); else { final Object realVal = (Object) val; ps.setObject(paramIndex, realVal); } break; default: throw new UnsupportedOperationException("java.sql.Type = " + type + " not supported"); } }
From source file:com.funambol.foundation.items.dao.DataBaseFileDataObjectMetadataDAO.java
/** * Updates metadata fields when FileDataObject body changes, like crc, size, * localname etc and the properties associated to it. * * @param fdow the wrapper with the new body file * @throws DAOException if an error occurs *///from ww w . j av a 2 s . co m public void updateItemWhenBodyChanges(FileDataObjectWrapper fdow) throws DAOException { Connection con = null; PreparedStatement ps = null; try { Long fdoId = Long.valueOf(fdow.getId()); FileDataObject fdo = fdow.getFileDataObject(); Timestamp currentTime = new Timestamp(System.currentTimeMillis()); StringBuilder updateQuery = new StringBuilder(); updateQuery.append(SQL_UPDATE_FNBL_FILE_DATA_OBJECT_BEGIN); updateQuery.append(SQL_FIELD_LAST_UPDATE).append(SQL_EQUALS_QUESTIONMARK_COMMA); String localName = fdow.getLocalName(); if (localName != null) { updateQuery.append(SQL_FIELD_LOCAL_NAME).append(SQL_EQUALS_QUESTIONMARK_COMMA); } updateQuery.append(SQL_FIELD_UPLOAD_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); Long crc = Long.valueOf(fdow.getFileDataObject().getCrc()); updateQuery.append(SQL_FIELD_CRC).append(SQL_EQUALS_QUESTIONMARK_COMMA); Long size = fdo.getSize(); if (size != null) { updateQuery.append(SQL_FIELD_SIZE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Long sizeOnStorage = fdow.getSizeOnStorage(); if (sizeOnStorage != null) { updateQuery.append(SQL_FIELD_SIZE_ON_STORAGE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } // set always created and modified dates updateQuery.append(SQL_FIELD_CREATED).append(SQL_EQUALS_QUESTIONMARK_COMMA); updateQuery.append(SQL_FIELD_MODIFIED).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (updateQuery.charAt(updateQuery.length() - 2) == ',') { updateQuery.deleteCharAt(updateQuery.length() - 2); } updateQuery.append(SQL_UPDATE_FNBL_FILE_DATA_OBJECT_END); // Looks up the data source when the first connection is created con = getUserDataSource().getRoutedConnection(userId); ps = con.prepareStatement(updateQuery.toString()); int k = 1; Timestamp lastUpdate = (fdow.getLastUpdate() == null) ? currentTime : fdow.getLastUpdate(); ps.setLong(k++, lastUpdate.getTime()); if (localName != null) { ps.setString(k++, StringUtils.left(localName, SQL_LOCAL_NAME_DIM)); } ps.setString(k++, "" + fdo.getUploadStatus()); ps.setLong(k++, crc); if (size != null) { ps.setLong(k++, size); } if (sizeOnStorage != null) { ps.setLong(k++, sizeOnStorage); } MediaUtils.setFDODates(fdo, fdo.getCreated(), fdo.getModified()); Timestamp created = timestamp(fdo.getCreated()); if (created != null) { ps.setTimestamp(k++, created); } else { ps.setTimestamp(k++, currentTime); } Timestamp modified = timestamp(fdo.getModified()); if (modified != null) { ps.setTimestamp(k++, modified); } else { ps.setTimestamp(k++, currentTime); } ps.setLong(k++, fdoId); ps.setString(k++, userId); ps.setString(k++, sourceURI); ps.executeUpdate(); DBTools.close(con, ps, null); // delete and add the properties associated to the new FDO removeAllProperties(fdow.getId()); addProperties(fdow); } catch (Exception e) { throw new DAOException("Error updating file data object and its properties.", e); } finally { DBTools.close(con, ps, null); } }
From source file:com.concursive.connect.web.modules.login.dao.User.java
/** * Description of the Method//from ww w . java 2s.c o m * * @param db The database connection * @param ipAddress The ip address requesting the user to be added * @param prefs The application prefs * @return true if the record was added successfully * @throws SQLException Database exception */ public boolean insert(Connection db, String ipAddress, ApplicationPrefs prefs) throws SQLException { boolean commit = db.getAutoCommit(); try { if (commit) { db.setAutoCommit(false); } // Insert the user PreparedStatement pst = db.prepareStatement("INSERT INTO users " + "(instance_id, group_id, department_id, first_name, last_name, username, password, temporary_password, " + "company, email, enteredby, modifiedby, enabled, start_page, access_personal, access_enterprise, " + "access_admin, access_inbox, access_resources, expiration, registered, " + "account_size, access_invite, access_add_projects, terms, timezone, currency, language" + (entered != null ? ", entered" : "") + (modified != null ? ", modified" : "") + ") " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" + (entered != null ? ", ?" : "") + (modified != null ? ", ?" : "") + ") "); int i = 0; DatabaseUtils.setInt(pst, ++i, instanceId); pst.setInt(++i, groupId); pst.setInt(++i, departmentId); pst.setString(++i, firstName); pst.setString(++i, lastName); pst.setString(++i, username); pst.setString(++i, password); pst.setString(++i, temporaryPassword); pst.setString(++i, company); pst.setString(++i, email); pst.setInt(++i, enteredBy); pst.setInt(++i, modifiedBy); pst.setBoolean(++i, enabled); pst.setInt(++i, startPage); pst.setBoolean(++i, true); pst.setBoolean(++i, true); pst.setBoolean(++i, accessAdmin); pst.setBoolean(++i, false); pst.setBoolean(++i, false); DatabaseUtils.setTimestamp(pst, ++i, expiration); pst.setBoolean(++i, registered); DatabaseUtils.setInt(pst, ++i, accountSize); pst.setBoolean(++i, accessInvite); pst.setBoolean(++i, accessAddProjects); pst.setBoolean(++i, terms); pst.setString(++i, timeZone); pst.setString(++i, currency); pst.setString(++i, language); if (entered != null) { pst.setTimestamp(++i, entered); } if (modified != null) { pst.setTimestamp(++i, modified); } pst.execute(); pst.close(); id = DatabaseUtils.getCurrVal(db, "users_user_id_seq", -1); // Record the IP if (ipAddress != null) { pst = db.prepareStatement("INSERT INTO user_log (user_id, ip_address) VALUES (?, ?)"); pst.setInt(1, id); pst.setString(2, ipAddress); pst.execute(); pst.close(); } if (!isApiRestore()) { // Insert a corresponding user profile project UserUtils.addUserProfile(db, this, prefs); if (profileProjectId == -1) { LOG.error("profileProjectId did not get assigned!"); } } if (commit) { db.commit(); } } catch (Exception e) { LOG.error("adding user", e); if (commit) { db.rollback(); } throw new SQLException(e.getMessage()); } finally { if (commit) { db.setAutoCommit(true); } } return true; }
From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java
private PreparedStatement createEventUpdateStatement(Connection con, AccessToken at, Event ev, int sequence) throws SQLException { PreparedStatement ps; String upQ = "UPDATE Event SET event_userupdate=?, " + "event_type=?, event_timezone=?, event_opacity=?, " + "event_title=?, event_location=?, " + "event_category1_id=?, event_priority=?, " + "event_privacy=?, event_date=?, event_duration=?, " + "event_allday=?, event_repeatkind=?, " + "event_repeatfrequence=?, event_repeatdays=?, " + "event_endrepeat=?, event_completed=?, " + "event_url=?, event_description=?, event_origin=?, " + "event_sequence=? " + "WHERE event_id=?"; ps = con.prepareStatement(upQ);// w w w.j a v a 2 s. c o m try { ps.setInt(1, at.getObmId()); ps.setObject(2, obmHelper.getDBCP().getJdbcObject(ObmHelper.VCOMPONENT, ev.getType().toString())); ps.setString(3, ev.getTimezoneName() != null ? ev.getTimezoneName() : "Europe/Paris"); ps.setObject(4, obmHelper.getDBCP().getJdbcObject(ObmHelper.VOPACITY, ev.getOpacity().toString())); ps.setString(5, ev.getTitle()); ps.setString(6, ev.getLocation()); Integer cat = catIdFromString(con, ev.getCategory(), at.getDomain().getId()); if (cat != null) { ps.setInt(7, cat); } else { ps.setNull(7, Types.INTEGER); } ps.setInt(8, ev.getPriority()); // do not allow making a private event become public from sync // ps.setInt(9, old.getPrivacy() != 1 ? ev.getPrivacy() : old // .getPrivacy()); ps.setInt(9, ev.getPrivacy().toInteger()); ps.setTimestamp(10, new Timestamp(ev.getStartDate().getTime())); ps.setInt(11, ev.getDuration()); ps.setBoolean(12, ev.isAllday()); EventRecurrence er = ev.getRecurrence(); ps.setString(13, er.getKind().toString()); ps.setInt(14, er.getFrequence()); ps.setString(15, new RecurrenceDaysSerializer().serialize(er.getDays())); if (er.getEnd() != null) { ps.setTimestamp(16, new Timestamp(er.getEnd().getTime())); } else { ps.setNull(16, Types.TIMESTAMP); } ps.setNull(17, Types.TIMESTAMP); ps.setNull(18, Types.VARCHAR); ps.setString(19, ev.getDescription()); ps.setString(20, at.getOrigin()); ps.setInt(21, sequence); ps.setInt(22, ev.getObmId().getObmId()); return ps; } catch (SQLException e) { ps.close(); throw e; } catch (RuntimeException e) { ps.close(); throw e; } }