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:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void update(final Nummeraanduiding origineel, final Nummeraanduiding mutation) throws DAOException { try {/*from w w w .ja va2 s . c om*/ jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("update bag_nummeraanduiding set" + " aanduiding_record_inactief = ?," + " aanduiding_record_correctie = ?," + " huisnummer = ?," + " officieel = ?," + " huisletter = ?," + " huisnummertoevoeging = ?," + " postcode = ?," + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?," + " type_adresseerbaar_object = ?," + " bron_documentdatum = ?," + " bron_documentnummer = ?," + " nummeraanduiding_status = ?," + " bag_woonplaats_id = ?," + " bag_openbare_ruimte_id = ?" + " where bag_nummeraanduiding_id = ?" + " and aanduiding_record_correctie = ?" + " and begindatum_tijdvak_geldigheid = ?"); ps.setInt(1, mutation.getAanduidingRecordInactief().ordinal()); ps.setLong(2, mutation.getAanduidingRecordCorrectie()); ps.setInt(3, mutation.getHuisnummer()); ps.setInt(4, mutation.getOfficieel().ordinal()); if (mutation.getHuisletter() == null) ps.setNull(5, Types.INTEGER); else ps.setString(5, mutation.getHuisletter()); if (mutation.getHuisnummertoevoeging() == null) ps.setNull(6, Types.VARCHAR); else ps.setString(6, mutation.getHuisnummertoevoeging()); if (mutation.getPostcode() == null) ps.setNull(7, Types.VARCHAR); else ps.setString(7, mutation.getPostcode()); if (mutation.getEinddatumTijdvakGeldigheid() == null) ps.setNull(8, Types.TIMESTAMP); else ps.setTimestamp(8, new Timestamp(mutation.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(9, mutation.getInOnderzoek().ordinal()); ps.setInt(10, mutation.getTypeAdresseerbaarObject().ordinal()); ps.setDate(11, new Date(mutation.getDocumentdatum().getTime())); ps.setString(12, mutation.getDocumentnummer()); ps.setInt(13, mutation.getNummeraanduidingStatus().ordinal()); if (mutation.getGerelateerdeWoonplaats() == null) ps.setNull(14, Types.INTEGER); else ps.setLong(14, mutation.getGerelateerdeWoonplaats()); ps.setLong(15, mutation.getGerelateerdeOpenbareRuimte()); ps.setLong(16, origineel.getIdentificatie()); ps.setLong(17, origineel.getAanduidingRecordCorrectie()); ps.setTimestamp(18, new Timestamp(origineel.getBegindatumTijdvakGeldigheid().getTime())); return ps; } }); } catch (DataAccessException e) { throw new DAOException("Error updating nummeraanduiding: " + origineel.getIdentificatie(), e); } }
From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void update(final Nummeraanduiding nummeraanduiding) throws DAOException { try {/*from w w w . j a v a2s . co m*/ jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("update bag_nummeraanduiding set" + " aanduiding_record_inactief = ?," + " huisnummer = ?," + " officieel = ?," + " huisletter = ?," + " huisnummertoevoeging = ?," + " postcode = ?," + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?," + " type_adresseerbaar_object = ?," + " bron_documentdatum = ?," + " bron_documentnummer = ?," + " nummeraanduiding_status = ?," + " bag_woonplaats_id = ?," + " bag_openbare_ruimte_id = ?" + " where bag_nummeraanduiding_id = ?" + " and aanduiding_record_correctie = ?" + " and begindatum_tijdvak_geldigheid = ?"); ps.setInt(1, nummeraanduiding.getAanduidingRecordInactief().ordinal()); ps.setInt(2, nummeraanduiding.getHuisnummer()); ps.setInt(3, nummeraanduiding.getOfficieel().ordinal()); if (nummeraanduiding.getHuisletter() == null) ps.setNull(4, Types.INTEGER); else ps.setString(4, nummeraanduiding.getHuisletter()); if (nummeraanduiding.getHuisnummertoevoeging() == null) ps.setNull(5, Types.VARCHAR); else ps.setString(5, nummeraanduiding.getHuisnummertoevoeging()); if (nummeraanduiding.getPostcode() == null) ps.setNull(6, Types.VARCHAR); else ps.setString(6, nummeraanduiding.getPostcode()); if (nummeraanduiding.getEinddatumTijdvakGeldigheid() == null) ps.setNull(7, Types.TIMESTAMP); else ps.setTimestamp(7, new Timestamp(nummeraanduiding.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(8, nummeraanduiding.getInOnderzoek().ordinal()); ps.setInt(9, nummeraanduiding.getTypeAdresseerbaarObject().ordinal()); ps.setDate(10, new Date(nummeraanduiding.getDocumentdatum().getTime())); ps.setString(11, nummeraanduiding.getDocumentnummer()); ps.setInt(12, nummeraanduiding.getNummeraanduidingStatus().ordinal()); if (nummeraanduiding.getGerelateerdeWoonplaats() == null) ps.setNull(13, Types.INTEGER); else ps.setLong(13, nummeraanduiding.getGerelateerdeWoonplaats()); ps.setLong(14, nummeraanduiding.getGerelateerdeOpenbareRuimte()); ps.setLong(15, nummeraanduiding.getIdentificatie()); ps.setLong(16, nummeraanduiding.getAanduidingRecordCorrectie()); ps.setTimestamp(17, new Timestamp(nummeraanduiding.getBegindatumTijdvakGeldigheid().getTime())); return ps; } }); } catch (DataAccessException e) { throw new DAOException("Error updating nummeraanduiding: " + nummeraanduiding.getIdentificatie(), e); } }
From source file:org.gbif.portal.dao.impl.jdbc.RawOccurrenceRecordDAOImpl.java
/** * @see org.gbif.portal.dao.RawOccurrenceRecordDAO#updateOrCreate(org.gbif.portal.model.RawOccurrenceRecord) *//* w ww . j ava 2 s . c om*/ public long updateOrCreate(final RawOccurrenceRecord rawOccurrenceRecord) { if (rawOccurrenceRecord.getId() <= 0) { return create(rawOccurrenceRecord); } else { getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement(RawOccurrenceRecordDAOImpl.UPDATE_SQL); ps.setLong(1, rawOccurrenceRecord.getDataProviderId()); ps.setLong(2, rawOccurrenceRecord.getDataResourceId()); ps.setLong(3, rawOccurrenceRecord.getResourceAccessPointId()); ps.setString(4, rawOccurrenceRecord.getInstitutionCode()); ps.setString(5, rawOccurrenceRecord.getCollectionCode()); ps.setString(6, rawOccurrenceRecord.getCatalogueNumber()); ps.setString(7, StringUtils.trimToNull(rawOccurrenceRecord.getScientificName())); ps.setString(8, StringUtils.trimToNull(rawOccurrenceRecord.getAuthor())); ps.setString(9, StringUtils.trimToNull(rawOccurrenceRecord.getRank())); ps.setString(10, StringUtils.trimToNull(rawOccurrenceRecord.getKingdom())); ps.setString(11, StringUtils.trimToNull(rawOccurrenceRecord.getPhylum())); ps.setString(12, StringUtils.trimToNull(rawOccurrenceRecord.getKlass())); ps.setString(13, StringUtils.trimToNull(rawOccurrenceRecord.getOrder())); ps.setString(14, StringUtils.trimToNull(rawOccurrenceRecord.getFamily())); ps.setString(15, StringUtils.trimToNull(rawOccurrenceRecord.getGenus())); ps.setString(16, StringUtils.trimToNull(rawOccurrenceRecord.getSpecies())); ps.setString(17, StringUtils.trimToNull(rawOccurrenceRecord.getSubspecies())); ps.setString(18, StringUtils.trimToNull(rawOccurrenceRecord.getLatitude())); ps.setString(19, StringUtils.trimToNull(rawOccurrenceRecord.getLongitude())); ps.setString(20, StringUtils.trimToNull(rawOccurrenceRecord.getLatLongPrecision())); ps.setString(21, StringUtils.trimToNull(rawOccurrenceRecord.getMinAltitude())); ps.setString(22, StringUtils.trimToNull(rawOccurrenceRecord.getMaxAltitude())); ps.setString(23, StringUtils.trimToNull(rawOccurrenceRecord.getAltitudePrecision())); ps.setString(24, StringUtils.trimToNull(rawOccurrenceRecord.getMinDepth())); ps.setString(25, StringUtils.trimToNull(rawOccurrenceRecord.getMaxDepth())); ps.setString(26, StringUtils.trimToNull(rawOccurrenceRecord.getDepthPrecision())); ps.setString(27, StringUtils.trimToNull(rawOccurrenceRecord.getContinentOrOcean())); ps.setString(28, StringUtils.trimToNull(rawOccurrenceRecord.getCountry())); ps.setString(29, StringUtils.trimToNull(rawOccurrenceRecord.getStateOrProvince())); ps.setString(30, StringUtils.trimToNull(rawOccurrenceRecord.getCounty())); ps.setString(31, StringUtils.trimToNull(rawOccurrenceRecord.getCollectorName())); ps.setString(32, StringUtils.trimToNull(rawOccurrenceRecord.getLocality())); ps.setString(33, StringUtils.trimToNull(rawOccurrenceRecord.getYear())); ps.setString(34, StringUtils.trimToNull(rawOccurrenceRecord.getMonth())); ps.setString(35, StringUtils.trimToNull(rawOccurrenceRecord.getDay())); ps.setString(36, StringUtils.trimToNull(rawOccurrenceRecord.getBasisOfRecord())); ps.setString(37, StringUtils.trimToNull(rawOccurrenceRecord.getIdentifierName())); ps.setDate(38, createSQLDate(rawOccurrenceRecord.getDateIdentified())); ps.setString(39, StringUtils.trimToNull(rawOccurrenceRecord.getUnitQualifier())); ps.setTimestamp(40, new Timestamp(System.currentTimeMillis())); ps.setLong(41, rawOccurrenceRecord.getId()); return ps; } }); return rawOccurrenceRecord.getId(); } }
From source file:com.globalsight.everest.edit.offline.upload.UploadApi.java
private void editIssues(List<IssuesVo> issuesVos) { IssueImpl issue = null;/*w w w. ja v a2s.c o m*/ Session session = HibernateUtil.getSession(); Transaction tx = HibernateUtil.getTransaction(); Connection conn = null; PreparedStatement stmt = null; try { conn = DbUtil.getConnection(); conn.setAutoCommit(false); String sqlUpdate = "update ISSUE_HISTORY set DESCRIPTION= ? ," + "REPORTED_DATE = ? " + " Where REPORTED_BY = ? and REPORTED_DATE = ?"; stmt = conn.prepareStatement(sqlUpdate); int batchUpdate = 0; for (IssuesVo vo : issuesVos) { issue = (IssueImpl) session.get(IssueImpl.class, new Long(vo.id)); issue.setTitle(vo.title); issue.setPriority(vo.priority); issue.setStatus(vo.commentStatus); issue.setCategory(vo.failureType); issue.setOverwrite(false); issue.setShare(false); IssueHistoryImpl ih = (IssueHistoryImpl) issue.getHistory().get(0); Date date = ih.dateReportedAsDate(); Date currentDate = Calendar.getInstance().getTime(); ih.dateReported(Calendar.getInstance().getTime()); ih.setComment(vo.comment); session.saveOrUpdate(ih); stmt.setString(1, vo.comment); stmt.setDate(2, new java.sql.Date(currentDate.getTime())); stmt.setString(3, vo.userId); stmt.setDate(4, new java.sql.Date(date.getTime())); batchUpdate++; if (batchUpdate > DbUtil.BATCH_INSERT_UNIT) { stmt.executeBatch(); batchUpdate = 0; } } if (batchUpdate > 0) { stmt.executeBatch(); } HibernateUtil.commit(tx); } catch (Exception ex) { HibernateUtil.rollback(tx); CATEGORY.error("Failed to edit issue.", ex); } finally { // session.close(); DbUtil.silentClose(stmt); if (conn != null) { try { conn.commit(); } catch (SQLException e) { CATEGORY.error(e); } DbUtil.silentReturnConnection(conn); } } }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildTags.java
/** * // w w w.j av a 2s . c o m */ 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:nl.ordina.bag.etl.dao.AbstractBAGDAO.java
@Override public void insert(final Nummeraanduiding nummeraanduiding) throws DAOException { try {/* w w w .j a va 2 s .c o m*/ jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection .prepareStatement("insert into bag_nummeraanduiding (" + "bag_nummeraanduiding_id," + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "huisnummer," + "officieel," + "huisletter," + "huisnummertoevoeging," + "postcode," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "type_adresseerbaar_object," + "bron_documentdatum," + "bron_documentnummer," + "nummeraanduiding_status," + "bag_woonplaats_id," + "bag_openbare_ruimte_id" + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps.setLong(1, nummeraanduiding.getIdentificatie()); ps.setInt(2, nummeraanduiding.getAanduidingRecordInactief().ordinal()); ps.setLong(3, nummeraanduiding.getAanduidingRecordCorrectie()); ps.setInt(4, nummeraanduiding.getHuisnummer()); ps.setInt(5, nummeraanduiding.getOfficieel().ordinal()); if (nummeraanduiding.getHuisletter() == null) ps.setNull(6, Types.VARCHAR); else ps.setString(6, nummeraanduiding.getHuisletter()); if (nummeraanduiding.getHuisnummertoevoeging() == null) ps.setNull(7, Types.VARCHAR); else ps.setString(7, nummeraanduiding.getHuisnummertoevoeging()); if (nummeraanduiding.getPostcode() == null) ps.setNull(8, Types.VARCHAR); else ps.setString(8, nummeraanduiding.getPostcode()); ps.setTimestamp(9, new Timestamp(nummeraanduiding.getBegindatumTijdvakGeldigheid().getTime())); if (nummeraanduiding.getEinddatumTijdvakGeldigheid() == null) ps.setNull(10, Types.TIMESTAMP); else ps.setTimestamp(10, new Timestamp(nummeraanduiding.getEinddatumTijdvakGeldigheid().getTime())); ps.setInt(11, nummeraanduiding.getInOnderzoek().ordinal()); ps.setInt(12, nummeraanduiding.getTypeAdresseerbaarObject().ordinal()); ps.setDate(13, new Date(nummeraanduiding.getDocumentdatum().getTime())); ps.setString(14, nummeraanduiding.getDocumentnummer()); ps.setInt(15, nummeraanduiding.getNummeraanduidingStatus().ordinal()); if (nummeraanduiding.getGerelateerdeWoonplaats() == null) ps.setNull(16, Types.INTEGER); else ps.setLong(16, nummeraanduiding.getGerelateerdeWoonplaats()); ps.setLong(17, nummeraanduiding.getGerelateerdeOpenbareRuimte()); return ps; } }); } catch (DataAccessException e) { throw new DAOException("Error inserting nummeraanduiding: " + nummeraanduiding.getIdentificatie(), e); } }
From source file:sopho.Ofeloumenoi.AddOfeloumenoiController.java
@FXML public void Save(ActionEvent event) { if (barcode.getText().isEmpty() || onoma.getText().isEmpty() || eponimo.getText().isEmpty() || patronimo.getText().isEmpty()) { //checking if the user has filled the required fields sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? . ? ? Barcode, , ? ? ? ?", "error"); cm.showAndWait();//from w w w.j a va 2 s . com } else if (!NumberUtils.isNumber(barcode.getText()) && !barcode.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " barcode ? ? ??. ? ? .", "error"); cm.showAndWait(); } else if (!NumberUtils.isNumber(eisodima.getText()) && !eisodima.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ??. ? ? .", "error"); cm.showAndWait(); } else {//the user has filled the required fields. We can proceed. sopho.DBClass db = new sopho.DBClass(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; String teknaDB = ""; //we create a var to push data to db. for (int i = 0; i < tekna.getItems().size(); i++) {//we are converting the table rows to a single comma separated string to push it to the database in a single entry. tableManager tbl = (tableManager) tekna.getItems().get(i); if (!tbl.getEtos().equals("? ")) { //we are checking if the user has actually entered a number teknaDB += tbl.getEtos() + ","; //we have to call getEtos from the tableManager class to get the actual value. We add the value to teknaDB and seperate with comma. arithmosTeknon++; } } if (arithmosTeknon > 0) {// we need to catch the case that the user has not added any data to the table. teknaDB = teknaDB.substring(0, teknaDB.length() - 1); // we have to remove the last comma. } conn = db.ConnectDB(); //Now we will check if the user has already registered this ofeloumenos String sql = "SELECT * FROM ofeloumenoi WHERE barcode =?"; try { pst = conn.prepareStatement(sql); pst.setString(1, barcode.getText()); System.out.println("the query is:" + pst.toString()); rs = pst.executeQuery(); rs.last(); //i go to the last line of the result to find out the number of the line if (rs.getRow() > 0) {// ofeloumenos is already registered to the database sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", "? ? barcode. ? ? . Barcode:" + rs.getString("barcode") + " : " + rs.getString("eponimo") + " : " + rs.getString("onoma") + " ?: " + rs.getString("patronimo"), "error"); cm.showAndWait(); } else { // we can push the data to database... sql = "INSERT INTO ofeloumenoi (barcode, eponimo, onoma, patronimo, mitronimo, imGennisis, dieuthinsi, dimos, tilefono, anergos, epaggelma, eisodima, eksartiseis, photoID, afm, tautotita, ethnikotita, metanastis, roma, oikKatastasi, hasTekna, arithmosTeknon, ilikiesTeknon, politeknos, monogoneiki, mellousaMama, amea, asfForeas, xronios, pathisi, anoTon60, monaxikos, emfiliVia, spoudastis, anenergos, loipa, registerDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; pst = conn.prepareStatement(sql); //now we will set the values to the sql statement pst.setString(1, barcode.getText()); pst.setString(2, eponimo.getText()); pst.setString(3, onoma.getText()); pst.setString(4, patronimo.getText()); pst.setString(5, mitronimo.getText()); //now we have to convert the imGennisis to a suitable format to be able to push it to the database if (imGennisis.getValue() != null) { Date date = Date .from(imGennisis.getValue().atStartOfDay(ZoneId.systemDefault()).toInstant()); java.sql.Date sqlDate = new java.sql.Date(date.getTime()); pst.setDate(6, sqlDate); } else { pst.setDate(6, null); } pst.setString(7, dieuthinsi.getText()); pst.setString(8, dimos.getText()); pst.setString(9, tilefono.getText()); pst.setInt(10, anergos.isSelected() ? 1 : 0); //set 1 if selected and 0 if not. We will use this method for all the checkboxes. pst.setString(11, epaggelma.getText()); pst.setString(12, eisodima.getText()); pst.setString(13, eksartiseis.getText()); pst.setString(14, PhotoID); pst.setString(15, afm.getText()); pst.setString(16, tautotita.getText()); pst.setString(17, ethnikotita.getText()); pst.setInt(18, metanastis.isSelected() ? 1 : 0); pst.setInt(19, roma.isSelected() ? 1 : 0); pst.setInt(20, (int) oikKatastasi.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(21, arithmosTeknon > 0 ? 1 : 0); //checking number of tekna. if >0 has tekna gets 1 pst.setInt(22, arithmosTeknon); pst.setString(23, teknaDB); //here we use the converted to comma separated values variable in order to save the tableView data using only one field in database. pst.setInt(24, politeknos.isSelected() ? 1 : 0); pst.setInt(25, monogoneiki.isSelected() ? 1 : 0); pst.setInt(26, mellousaMama.isSelected() ? 1 : 0); pst.setInt(27, amea.isSelected() ? 1 : 0); pst.setInt(28, (int) asfForeas.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(29, xronios.isSelected() ? 1 : 0); pst.setString(30, pathisi.getText()); pst.setInt(31, monaxiko.isSelected() ? 1 : 0); pst.setInt(32, anoTon60.isSelected() ? 1 : 0); pst.setInt(33, emfiliVia.isSelected() ? 1 : 0); pst.setInt(34, spoudastis.isSelected() ? 1 : 0); pst.setInt(35, anenergos.isSelected() ? 1 : 0); pst.setString(36, loipa.getText()); //insert today's date as registerDate LocalDate now = LocalDate.now(); java.sql.Date sqlToday = java.sql.Date.valueOf(now); pst.setDate(37, sqlToday); System.out.println("the query is:" + pst.toString()); int linesAffected = pst.executeUpdate(); //checking if the data were inserted to the database successfully if (linesAffected > 0) { Stage stage = (Stage) barcode.getScene().getWindow(); try { sl.StageLoad("/sopho/Ofeloumenoi/AddMore.fxml", stage, false, true); //resizable false, utility true } catch (IOException ex) { Logger.getLogger(AddOfeloumenoiController.class.getName()).log(Level.SEVERE, null, ex); } } else {//problem inserting data... sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ? . ? ...", "error"); cm.showAndWait(); } } } catch (SQLException e) { System.out.println( "? ? ? !" + e); } } }
From source file:edu.ku.brc.specify.toycode.mexconabio.MexConvToSQLNew.java
public void convert(final String tableName, final String fileName) { String str = ""; int fldLen = 0; int inx = 0;/*from w w w .ja v a 2s. c o m*/ Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/mex?characterEncoding=UTF-8&autoReconnect=true", "root", "root"); stmt = conn.createStatement(); int[] fieldLengths = null; BasicSQLUtils.deleteAllRecordsFromTable(conn, tableName, SERVERTYPE.MySQL); Vector<Integer> types = new Vector<Integer>(); Vector<String> names = new Vector<String>(); String selectStr = null; String prepareStr = null; try { prepareStr = FileUtils.readFileToString(new File("prepare_stmt.txt")); selectStr = FileUtils.readFileToString(new File("select.txt")); } catch (IOException e) { e.printStackTrace(); } int idInx = selectStr.indexOf("ID,"); if (idInx == 0) { selectStr = selectStr.substring(3); } File file = new File("/Users/rods/Documents/" + fileName); SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); //SimpleDateFormat stf = new SimpleDateFormat("k:mm:ss"); int rowCnt = 0; try { System.out.println(prepareStr); PreparedStatement pStmt = conn.prepareStatement(prepareStr); BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF8")); str = in.readLine(); String[] fieldNames = StringUtils.split(str, ","); //String[] fieldNamesDB = StringUtils.split(selectStr, ","); String sql = "SELECT " + selectStr + " FROM " + tableName; System.out.println(sql); ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); fieldLengths = new int[rsmd.getColumnCount()]; for (int i = 1; i <= rsmd.getColumnCount(); i++) { fieldLengths[i - 1] = rsmd.getPrecision(i); types.add(rsmd.getColumnType(i)); names.add(rsmd.getColumnName(i)); System.out.println((i > 1 ? fieldNames[i - 2] : "ID") + " / " + rsmd.getColumnName(i) + " - " + rsmd.getPrecision(i)); } int numCols = rsmd.getColumnCount(); rs.close(); System.out.println("Number of Fields: " + numCols); str = in.readLine(); while (str != null) { //System.err.println(str); str = StringUtils.replace(str.substring(1, str.length() - 1), "\",\"", "|"); Vector<String> fields = split(str); if (fields.size() != numCols) { System.out.println("numCols: " + numCols + " != " + fields.size() + "fields.size()"); continue; } int col = 1; inx = 0; for (String fld : fields) { String field = fld.trim(); //if (field.length() > 1) //{ // field = field.substring(1, field.length()-1); //} //if (inx > 204) break; fldLen = field.length(); pStmt.setObject(col, null); switch (types.get(inx)) { case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: case java.sql.Types.LONGNVARCHAR: { if (field.length() > 0) { if (field.length() <= fieldLengths[inx]) { pStmt.setString(col, field); } else { System.err.println(String.format("The data for `%s` (%d) is too big %d f[%s]", names.get(inx), fieldLengths[inx], field.length(), field)); pStmt.setString(col, null); } } else { pStmt.setString(col, null); } } break; case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: { if (StringUtils.isNotEmpty(field)) { if (StringUtils.isNumeric(field)) { pStmt.setDouble(col, field.length() > 0 ? Double.parseDouble(field) : null); } else { System.err.println(col + " Bad Number[" + field + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case java.sql.Types.INTEGER: { if (StringUtils.isNotEmpty(field)) { if (StringUtils.isNumeric(field)) { pStmt.setInt(col, field.length() > 0 ? Integer.parseInt(field) : null); } else { System.err.println(col + " Bad Number[" + field + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case java.sql.Types.TIME: { Time time = null; try { time = Time.valueOf(field); } catch (Exception ex) { } pStmt.setTime(col, time); } break; case java.sql.Types.DATE: { try { if (StringUtils.isNotEmpty(field)) { if (StringUtils.contains(field, "/")) { field = StringUtils.replace(field, "/", "-"); } else if (StringUtils.contains(field, " ")) { field = StringUtils.replace(field, " ", "-"); } pStmt.setDate(col, field.length() > 0 ? new java.sql.Date(sdf.parse(field).getTime()) : null); } else { pStmt.setDate(col, null); } } catch (Exception ex) { System.err.println(col + " Bad Date[" + field + "]\n" + str); pStmt.setDate(col, null); } } break; default: { System.err.println("Error - " + types.get(inx)); } } inx++; col++; } pStmt.execute(); str = in.readLine(); rowCnt++; } in.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (Exception e) { System.err.println("Row: " + rowCnt); System.err.println(str); System.err.println(inx + " " + fieldLengths[inx] + " - Field Len: " + fldLen); e.printStackTrace(); } /*BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file)); while (bis.available() > 0) { int bytesRead = bis.read(bytes); if (bytesRead > 0) { System.arraycopy(bytes, bytesRead, buffer, bufEndInx, bytesRead); bufEndInx += bytesRead; int inx = 0; while (inx < bufEndInx) { if (buffer[inx] != '\n') { String line = } inx++; } } }*/ } catch (Exception ex) { ex.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }
From source file:org.apache.synapse.mediators.db.AbstractDBMediator.java
/** * Return a Prepared statement for the given Statement object, which is ready to be executed * * @param stmnt SQL stataement to be executed * @param con The connection to be used * @param msgCtx Current message context * @return a PreparedStatement/*from ww w .j a v a 2 s . c om*/ * @throws SQLException on error */ protected PreparedStatement getPreparedStatement(Statement stmnt, Connection con, MessageContext msgCtx) throws SQLException { SynapseLog synLog = getLog(msgCtx); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Getting a connection from DataSource " + getDSName() + " and preparing statement : " + stmnt.getRawStatement()); } if (con == null) { String msg = "Connection from DataSource " + getDSName() + " is null."; log.error(msg); throw new SynapseException(msg); } if (dataSource instanceof BasicDataSource) { BasicDataSource basicDataSource = (BasicDataSource) dataSource; int numActive = basicDataSource.getNumActive(); int numIdle = basicDataSource.getNumIdle(); String connectionId = Integer.toHexString(con.hashCode()); DBPoolView dbPoolView = getDbPoolView(); if (dbPoolView != null) { dbPoolView.setNumActive(numActive); dbPoolView.setNumIdle(numIdle); dbPoolView.updateConnectionUsage(connectionId); } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("[ DB Connection : " + con + " ]"); synLog.traceOrDebug("[ DB Connection instance identifier : " + connectionId + " ]"); synLog.traceOrDebug("[ Number of Active Connection : " + numActive + " ]"); synLog.traceOrDebug("[ Number of Idle Connection : " + numIdle + " ]"); } } PreparedStatement ps = con.prepareStatement(stmnt.getRawStatement()); // set parameters if any List<Statement.Parameter> params = stmnt.getParameters(); int column = 1; for (Statement.Parameter param : params) { if (param == null) { continue; } String value = (param.getPropertyName() != null ? param.getPropertyName() : param.getXpath().stringValueOf(msgCtx)); if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Setting as parameter : " + column + " value : " + value + " as JDBC Type : " + param.getType() + "(see java.sql.Types for valid " + "types)"); } switch (param.getType()) { // according to J2SE 1.5 /docs/guide/jdbc/getstart/mapping.html case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: { if (value != null && value.length() != 0) { ps.setString(column++, value); } else { ps.setString(column++, null); } break; } case Types.NUMERIC: case Types.DECIMAL: { if (value != null && value.length() != 0) { ps.setBigDecimal(column++, new BigDecimal(value)); } else { ps.setBigDecimal(column++, null); } break; } case Types.BIT: { if (value != null && value.length() != 0) { ps.setBoolean(column++, Boolean.parseBoolean(value)); } else { ps.setNull(column++, Types.BIT); } break; } case Types.TINYINT: { if (value != null && value.length() != 0) { ps.setByte(column++, Byte.parseByte(value)); } else { ps.setNull(column++, Types.TINYINT); } break; } case Types.SMALLINT: { if (value != null && value.length() != 0) { ps.setShort(column++, Short.parseShort(value)); } else { ps.setNull(column++, Types.SMALLINT); } break; } case Types.INTEGER: { if (value != null && value.length() != 0) { ps.setInt(column++, Integer.parseInt(value)); } else { ps.setNull(column++, Types.INTEGER); } break; } case Types.BIGINT: { if (value != null && value.length() != 0) { ps.setLong(column++, Long.parseLong(value)); } else { ps.setNull(column++, Types.BIGINT); } break; } case Types.REAL: { if (value != null && value.length() != 0) { ps.setFloat(column++, Float.parseFloat(value)); } else { ps.setNull(column++, Types.REAL); } break; } case Types.FLOAT: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.FLOAT); } break; } case Types.DOUBLE: { if (value != null && value.length() != 0) { ps.setDouble(column++, Double.parseDouble(value)); } else { ps.setNull(column++, Types.DOUBLE); } break; } // skip BINARY, VARBINARY and LONGVARBINARY case Types.DATE: { if (value != null && value.length() != 0) { ps.setDate(column++, Date.valueOf(value)); } else { ps.setNull(column++, Types.DATE); } break; } case Types.TIME: { if (value != null && value.length() != 0) { ps.setTime(column++, Time.valueOf(value)); } else { ps.setNull(column++, Types.TIME); } break; } case Types.TIMESTAMP: { if (value != null && value.length() != 0) { ps.setTimestamp(column++, Timestamp.valueOf(value)); } else { ps.setNull(column++, Types.TIMESTAMP); } break; } // skip CLOB, BLOB, ARRAY, DISTINCT, STRUCT, REF, JAVA_OBJECT default: { String msg = "Trying to set an un-supported JDBC Type : " + param.getType() + " against column : " + column + " and statement : " + stmnt.getRawStatement() + " used by a DB mediator against DataSource : " + getDSName() + " (see java.sql.Types for valid type values)"; handleException(msg, msgCtx); } } } if (synLog.isTraceOrDebugEnabled()) { synLog.traceOrDebug("Successfully prepared statement : " + stmnt.getRawStatement() + " against DataSource : " + getDSName()); } return ps; }
From source file:org.apache.wiki.auth.user.JDBCUserDatabase.java
/** * @see org.apache.wiki.auth.user.UserDatabase#save(org.apache.wiki.auth.user.UserProfile) *///from ww w . j av a 2 s.com public void save(UserProfile profile) throws WikiSecurityException { String initialRole = "Authenticated"; // Figure out which prepared statement to use & execute it String loginName = profile.getLoginName(); PreparedStatement ps = null; UserProfile existingProfile = null; try { existingProfile = findByLoginName(loginName); } catch (NoSuchPrincipalException e) { // Existing profile will be null } // Get a clean password from the passed profile. // Blank password is the same as null, which means we re-use the // existing one. String password = profile.getPassword(); String existingPassword = (existingProfile == null) ? null : existingProfile.getPassword(); if (NOTHING.equals(password)) { password = null; } if (password == null) { password = existingPassword; } // If password changed, hash it before we save if (!StringUtils.equals(password, existingPassword)) { password = getHash(password); } Connection conn = null; try { // Open the database connection conn = m_ds.getConnection(); if (m_supportsCommits) { conn.setAutoCommit(false); } Timestamp ts = new Timestamp(System.currentTimeMillis()); Date modDate = new Date(ts.getTime()); java.sql.Date lockExpiry = profile.getLockExpiry() == null ? null : new java.sql.Date(profile.getLockExpiry().getTime()); if (existingProfile == null) { // User is new: insert new user record ps = conn.prepareStatement(m_insertProfile); ps.setString(1, profile.getUid()); ps.setString(2, profile.getEmail()); ps.setString(3, profile.getFullname()); ps.setString(4, password); ps.setString(5, profile.getWikiName()); ps.setTimestamp(6, ts); ps.setString(7, profile.getLoginName()); try { ps.setString(8, Serializer.serializeToBase64(profile.getAttributes())); } catch (IOException e) { throw new WikiSecurityException( "Could not save user profile attribute. Reason: " + e.getMessage(), e); } ps.setTimestamp(9, ts); ps.execute(); ps.close(); // Insert new role record ps = conn.prepareStatement(m_findRoles); ps.setString(1, profile.getLoginName()); ResultSet rs = ps.executeQuery(); int roles = 0; while (rs.next()) { roles++; } ps.close(); if (roles == 0) { ps = conn.prepareStatement(m_insertRole); ps.setString(1, profile.getLoginName()); ps.setString(2, initialRole); ps.execute(); ps.close(); } // Set the profile creation time profile.setCreated(modDate); } else { // User exists: modify existing record ps = conn.prepareStatement(m_updateProfile); ps.setString(1, profile.getUid()); ps.setString(2, profile.getEmail()); ps.setString(3, profile.getFullname()); ps.setString(4, password); ps.setString(5, profile.getWikiName()); ps.setTimestamp(6, ts); ps.setString(7, profile.getLoginName()); try { ps.setString(8, Serializer.serializeToBase64(profile.getAttributes())); } catch (IOException e) { throw new WikiSecurityException( "Could not save user profile attribute. Reason: " + e.getMessage(), e); } ps.setDate(9, lockExpiry); ps.setString(10, profile.getLoginName()); ps.execute(); ps.close(); } // Set the profile mod time profile.setLastModified(modDate); // Commit and close connection if (m_supportsCommits) { conn.commit(); } } catch (SQLException e) { throw new WikiSecurityException(e.getMessage(), e); } finally { try { if (conn != null) conn.close(); } catch (Exception e) { } } }