List of usage examples for java.sql PreparedStatement setNull
void setNull(int parameterIndex, int sqlType) throws SQLException;
NULL. From source file:com.zimbra.cs.db.DbMailItem.java
public void create(UnderlyingData data) throws ServiceException { if (data.id <= 0 || data.folderId <= 0 || data.parentId == 0) { throw ServiceException.FAILURE("invalid data for DB item create", null); }//from w ww.j a va 2 s . com assert mailbox.isNewItemIdValid(data.id) : "[bug 46549] illegal id for mail item"; //temporarily for bug 46549 checkNamingConstraint(mailbox, data.folderId, data.name, data.id); DbConnection conn = mailbox.getOperationConnection(); PreparedStatement stmt = null; try { MailItem.Type type = MailItem.Type.of(data.type); stmt = conn.prepareStatement("INSERT INTO " + getMailItemTableName(mailbox) + "(" + MAILBOX_ID + " id, type, parent_id, folder_id, index_id, imap_id, date, size, locator, blob_digest, unread," + " flags, tag_names, sender, recipients, subject, name, metadata, mod_metadata, change_date," + " mod_content, uuid) VALUES (" + MAILBOX_ID_VALUE + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); int pos = 1; pos = setMailboxId(stmt, mailbox, pos); stmt.setInt(pos++, data.id); stmt.setByte(pos++, data.type); if (data.parentId <= 0) { // Messages in virtual conversations are stored with a null parent_id stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, data.parentId); } stmt.setInt(pos++, data.folderId); if (data.indexId == MailItem.IndexStatus.NO.id()) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, data.indexId); } if (data.imapId <= 0) { stmt.setNull(pos++, Types.INTEGER); } else { stmt.setInt(pos++, data.imapId); } stmt.setInt(pos++, data.date); stmt.setLong(pos++, data.size); stmt.setString(pos++, data.locator); stmt.setString(pos++, data.getBlobDigest()); switch (type) { case MESSAGE: case CHAT: case FOLDER: stmt.setInt(pos++, data.unreadCount); break; default: stmt.setNull(pos++, Types.INTEGER); break; } stmt.setInt(pos++, data.getFlags()); stmt.setString(pos++, DbTag.serializeTags(data.getTags())); stmt.setString(pos++, sender); stmt.setString(pos++, recipients); stmt.setString(pos++, data.getSubject()); stmt.setString(pos++, data.name); stmt.setString(pos++, checkMetadataLength(data.metadata)); stmt.setInt(pos++, data.modMetadata); if (data.dateChanged > 0) { stmt.setInt(pos++, data.dateChanged); } else { stmt.setNull(pos++, Types.INTEGER); } stmt.setInt(pos++, data.modContent); stmt.setString(pos++, data.uuid); int num = stmt.executeUpdate(); if (num != 1) { throw ServiceException.FAILURE("failed to create object", null); } DbTag.storeTagReferences(mailbox, data.id, type, data.getFlags(), data.unreadCount > 0); DbTag.storeTagReferences(mailbox, data.id, type, data.getTags()); } catch (SQLException e) { // catch item_id uniqueness constraint violation and return failure if (Db.errorMatches(e, Db.Error.DUPLICATE_ROW)) { throw MailServiceException.ALREADY_EXISTS(data.id, e); } else { throw ServiceException.FAILURE("Failed to create id=" + data.id + ",type=" + data.type, e); } } finally { DbPool.closeStatement(stmt); } }
From source file:com.oltpbenchmark.benchmarks.seats.SEATSLoader.java
/** * /*from w w w. j a va 2s .co m*/ * @param catalog_tbl */ public void loadTable(Table catalog_tbl, Iterable<Object[]> iterable, int batch_size) { // Special Case: Airport Locations final boolean is_airport = catalog_tbl.getName().equals(SEATSConstants.TABLENAME_AIRPORT); if (LOG.isDebugEnabled()) LOG.debug(String.format("Generating new records for table %s [batchSize=%d]", catalog_tbl.getName(), batch_size)); final List<Column> columns = catalog_tbl.getColumns(); // Check whether we have any special mappings that we need to maintain Map<Integer, Integer> code_2_id = new HashMap<Integer, Integer>(); Map<Integer, Map<String, Long>> mapping_columns = new HashMap<Integer, Map<String, Long>>(); for (int col_code_idx = 0, cnt = columns.size(); col_code_idx < cnt; col_code_idx++) { Column catalog_col = columns.get(col_code_idx); String col_name = catalog_col.getName(); // Code Column -> Id Column Mapping // Check to see whether this table has columns that we need to map their // code values to tuple ids String col_id_name = this.profile.code_columns.get(col_name); if (col_id_name != null) { Column catalog_id_col = catalog_tbl.getColumnByName(col_id_name); assert (catalog_id_col != null) : "The id column " + catalog_tbl.getName() + "." + col_id_name + " is missing"; int col_id_idx = catalog_tbl.getColumnIndex(catalog_id_col); code_2_id.put(col_code_idx, col_id_idx); } // Foreign Key Column to Code->Id Mapping // If this columns references a foreign key that is used in the Code->Id mapping // that we generating above, then we need to know when we should change the // column value from a code to the id stored in our lookup table if (this.profile.fkey_value_xref.containsKey(col_name)) { String col_fkey_name = this.profile.fkey_value_xref.get(col_name); mapping_columns.put(col_code_idx, this.profile.code_id_xref.get(col_fkey_name)); } } // FOR int row_idx = 0; int row_batch = 0; try { String insert_sql = SQLUtil.getInsertSQL(catalog_tbl); PreparedStatement insert_stmt = this.conn.prepareStatement(insert_sql); int sqlTypes[] = catalog_tbl.getColumnTypes(); for (Object tuple[] : iterable) { assert (tuple[0] != null) : "The primary key for " + catalog_tbl.getName() + " is null"; // AIRPORT if (is_airport) { // Skip any airport that does not have flights int col_code_idx = catalog_tbl.getColumnByName("AP_CODE").getIndex(); if (profile.hasFlights((String) tuple[col_code_idx]) == false) { if (LOG.isTraceEnabled()) LOG.trace(String.format("Skipping AIRPORT '%s' because it does not have any flights", tuple[col_code_idx])); continue; } // Update the row # so that it matches what we're actually loading int col_id_idx = catalog_tbl.getColumnByName("AP_ID").getIndex(); tuple[col_id_idx] = (long) (row_idx + 1); // Store Locations int col_lat_idx = catalog_tbl.getColumnByName("AP_LATITUDE").getIndex(); int col_lon_idx = catalog_tbl.getColumnByName("AP_LONGITUDE").getIndex(); Pair<Double, Double> coords = Pair.of((Double) tuple[col_lat_idx], (Double) tuple[col_lon_idx]); if (coords.first == null || coords.second == null) { LOG.error(Arrays.toString(tuple)); } assert (coords.first != null) : String.format("Unexpected null latitude for airport '%s' [%d]", tuple[col_code_idx], col_lat_idx); assert (coords.second != null) : String.format( "Unexpected null longitude for airport '%s' [%d]", tuple[col_code_idx], col_lon_idx); this.airport_locations.put(tuple[col_code_idx].toString(), coords); if (LOG.isTraceEnabled()) LOG.trace(String.format("Storing location for '%s': %s", tuple[col_code_idx], coords)); } // Code Column -> Id Column for (int col_code_idx : code_2_id.keySet()) { assert (tuple[col_code_idx] != null) : String.format( "The value of the code column at '%d' is null for %s\n%s", col_code_idx, catalog_tbl.getName(), Arrays.toString(tuple)); String code = tuple[col_code_idx].toString().trim(); if (code.length() > 0) { Column from_column = columns.get(col_code_idx); assert (from_column != null); Column to_column = columns.get(code_2_id.get(col_code_idx)); assert (to_column != null) : String.format("Invalid column %s.%s", catalog_tbl.getName(), code_2_id.get(col_code_idx)); long id = (Long) tuple[code_2_id.get(col_code_idx)]; if (LOG.isTraceEnabled()) LOG.trace(String.format("Mapping %s '%s' -> %s '%d'", from_column.fullName(), code, to_column.fullName(), id)); this.profile.code_id_xref.get(to_column.getName()).put(code, id); } } // FOR // Foreign Key Code -> Foreign Key Id for (int col_code_idx : mapping_columns.keySet()) { Column catalog_col = columns.get(col_code_idx); assert (tuple[col_code_idx] != null || catalog_col.isNullable()) : String.format( "The code %s column at '%d' is null for %s id=%s\n%s", catalog_col.fullName(), col_code_idx, catalog_tbl.getName(), tuple[0], Arrays.toString(tuple)); if (tuple[col_code_idx] != null) { String code = tuple[col_code_idx].toString(); tuple[col_code_idx] = mapping_columns.get(col_code_idx).get(code); if (LOG.isTraceEnabled()) LOG.trace(String.format("Mapped %s '%s' -> %s '%s'", catalog_col.fullName(), code, catalog_col.getForeignKey().fullName(), tuple[col_code_idx])); } } // FOR for (int i = 0; i < tuple.length; i++) { try { if (tuple[i] != null) { insert_stmt.setObject(i + 1, tuple[i]); } else { insert_stmt.setNull(i + 1, sqlTypes[i]); } } catch (SQLDataException ex) { LOG.error("INVALID " + catalog_tbl.getName() + " TUPLE: " + Arrays.toString(tuple)); throw new RuntimeException("Failed to set value for " + catalog_tbl.getColumn(i).fullName(), ex); } } // FOR insert_stmt.addBatch(); row_idx++; if (++row_batch >= batch_size) { LOG.debug(String.format("Loading %s batch [total=%d]", catalog_tbl.getName(), row_idx)); insert_stmt.executeBatch(); conn.commit(); insert_stmt.clearBatch(); row_batch = 0; } } // FOR if (row_batch > 0) { insert_stmt.executeBatch(); conn.commit(); } insert_stmt.close(); } catch (Exception ex) { throw new RuntimeException("Failed to load table " + catalog_tbl.getName(), ex); } if (is_airport) assert (this.profile.getAirportCount() == row_idx) : String.format("%d != %d", profile.getAirportCount(), row_idx); // Record the number of tuples that we loaded for this table in the profile if (catalog_tbl.getName().equals(SEATSConstants.TABLENAME_RESERVATION)) { this.profile.num_reservations = row_idx + 1; } LOG.info(String.format("Finished loading all %d tuples for %s [%d / %d]", row_idx, catalog_tbl.getName(), this.finished.incrementAndGet(), this.getCatalog().getTableCount())); return; }
From source file:edu.uga.cs.fluxbuster.db.PostgresDBInterface.java
/** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeClusters(java.util.List, java.lang.String, java.util.Date) *//* w w w. java 2s . com*/ @Override public void storeClusters(List<DomainCluster> clusters, String sensorname, Date logdate) { String logDateTable = dateFormatTable.format(logdate); Connection con = null; PreparedStatement domainsInsertStmt = null; PreparedStatement domainsSelectStmt = null; PreparedStatement clustersInsertStmt = null; PreparedStatement resolvedIPSInsertStmt = null; PreparedStatement clusterResolvedIPSInsertStmt = null; PreparedStatement clusterFeatureVectorsInsertStmt = null; try { con = this.getConnection(); domainsInsertStmt = con .prepareStatement("INSERT INTO domains_" + logDateTable + " VALUES(DEFAULT, ?, ?, ?)"); domainsSelectStmt = con .prepareStatement("SELECT domain_id FROM domains_" + logDateTable + " WHERE domain_name = ?"); clustersInsertStmt = con .prepareStatement("INSERT INTO clusters_" + logDateTable + " VALUES " + "(?, ?, ?, ?)"); resolvedIPSInsertStmt = con .prepareStatement("INSERT INTO resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, inet(?))"); clusterResolvedIPSInsertStmt = con.prepareStatement( "INSERT INTO cluster_resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, ?, inet(?))"); clusterFeatureVectorsInsertStmt = con.prepareStatement("INSERT INTO cluster_feature_vectors_" + logDateTable + "(cluster_id, sensor_name, log_date, network_cardinality, ip_diversity, " + "number_of_domains, ttl_per_domain, ip_growth_ratio, queries_per_domain, avg_last_growth_ratio_single_entry, " + "avg_last_growth_ratio_entries, avg_last_growth_prefix_ratio_entries, last_growth_ratio_cluster," + "last_growth_prefix_ratio_cluster) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); int clusterId = 1; for (DomainCluster cluster : clusters) { for (CandidateFluxDomain candidateDomain : cluster.getCandidateDomains()) { String domainName = filterChars(candidateDomain.getDomainName()); String domainNameRev = DomainNameUtils.reverseDomainName(domainName); String secondLevelDomainName = DomainNameUtils.extractEffective2LD(domainName); String secondLevelDomainNameRev = null; if (secondLevelDomainName != null) { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(secondLevelDomainName); } else { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(domainName); } domainsInsertStmt.setString(1, domainNameRev); domainsInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); domainsInsertStmt.setString(3, secondLevelDomainNameRev); executePreparedStatementNoResult(con, domainsInsertStmt); domainsSelectStmt.setString(1, domainNameRev); ResultSet rs = this.executePreparedStatementWithResult(con, domainsSelectStmt); try { if (rs.next()) { int domainId = rs.getInt(1); clustersInsertStmt.setInt(1, clusterId); clustersInsertStmt.setInt(2, domainId); clustersInsertStmt.setString(3, sensorname); clustersInsertStmt.setDate(4, new java.sql.Date(logdate.getTime())); this.executePreparedStatementNoResult(con, clustersInsertStmt); for (InetAddress resolvedIP : candidateDomain.getIps()) { resolvedIPSInsertStmt.setInt(1, domainId); resolvedIPSInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); resolvedIPSInsertStmt.setString(3, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, resolvedIPSInsertStmt); } } } catch (SQLException ex) { if (log.isErrorEnabled()) { log.error("", ex); } } finally { rs.close(); } } /*String nickname = getNicknames((List<String>)cluster.getDomains()); insertQuery = "INSERT INTO cluster_nicknames_"+ logDateTable +" VALUES" + "("+clusterId+", '"+sensorname+"', '"+logDateStr+"', '"+nickname+"')"; performInsertQuery(insertQuery, clusterNicknamesCreateQuery);*/ for (InetAddress resolvedIP : cluster.getIps()) { clusterResolvedIPSInsertStmt.setInt(1, clusterId); clusterResolvedIPSInsertStmt.setString(2, sensorname); clusterResolvedIPSInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterResolvedIPSInsertStmt.setString(4, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, clusterResolvedIPSInsertStmt); } clusterFeatureVectorsInsertStmt.setInt(1, clusterId); clusterFeatureVectorsInsertStmt.setString(2, sensorname); clusterFeatureVectorsInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterFeatureVectorsInsertStmt.setInt(4, cluster.getIps().size()); clusterFeatureVectorsInsertStmt.setDouble(5, cluster.getIpDiversity()); clusterFeatureVectorsInsertStmt.setInt(6, cluster.getDomains().size()); clusterFeatureVectorsInsertStmt.setDouble(7, cluster.getAvgTTLPerDomain()); clusterFeatureVectorsInsertStmt.setDouble(8, cluster.getIpGrowthRatio()); clusterFeatureVectorsInsertStmt.setDouble(9, cluster.getQueriesPerDomain()); Double temp = cluster.getAvgLastGrowthRatioSingleEntry(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(10, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(10, temp); } temp = cluster.getAvgLastGrowthRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(11, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(11, temp); } temp = cluster.getAvgLastGrowthPrefixRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(12, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(12, temp); } temp = cluster.getLastGrowthRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(13, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(13, temp); } temp = cluster.getLastGrowthPrefixRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(14, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(14, temp); } this.executePreparedStatementNoResult(con, clusterFeatureVectorsInsertStmt); clusterId++; } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("", e); } } finally { try { if (domainsInsertStmt != null && !domainsInsertStmt.isClosed()) { domainsInsertStmt.close(); } if (domainsSelectStmt != null && !domainsSelectStmt.isClosed()) { domainsSelectStmt.close(); } if (clustersInsertStmt != null && !clustersInsertStmt.isClosed()) { clustersInsertStmt.close(); } if (resolvedIPSInsertStmt != null && !resolvedIPSInsertStmt.isClosed()) { resolvedIPSInsertStmt.close(); } if (clusterResolvedIPSInsertStmt != null && !clusterResolvedIPSInsertStmt.isClosed()) { clusterResolvedIPSInsertStmt.close(); } if (clusterFeatureVectorsInsertStmt != null && !clusterFeatureVectorsInsertStmt.isClosed()) { clusterFeatureVectorsInsertStmt.close(); } if (con != null && !con.isClosed()) { con.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * {@inheritDoc}/* w ww. j a v a 2 s .c o m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public long createGroup(long typeId, FxGroupEdit group, String parentXPath, String assignmentAlias) throws FxApplicationException { FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.StructureManagement); Connection con = null; PreparedStatement ps = null; StringBuilder sql = new StringBuilder(2000); long newGroupId; long newAssignmentId; try { parentXPath = parentXPath.toUpperCase(); assignmentAlias = assignmentAlias.toUpperCase(); FxType type = CacheAdmin.getEnvironment().getType(typeId); FxAssignment tmp = type.getAssignment(parentXPath); if (tmp != null && tmp instanceof FxPropertyAssignment) throw new FxInvalidParameterException("ex.structure.assignment.noGroup", parentXPath); //parentXPath is valid, create the group, then assign it to root newGroupId = seq.getId(FxSystemSequencer.TYPEGROUP); con = Database.getDbConnection(); ContentStorage storage = StorageManager.getContentStorage(type.getStorageMode()); // do not allow to add mandatory groups (i.e. min multiplicity > 0) to types for which content exists if (storage.getTypeInstanceCount(con, typeId) > 0 && group.getMultiplicity().getMin() > 0) { throw new FxCreateException("ex.structure.group.creation.exisitingContentMultiplicityError", group.getName(), group.getMultiplicity().getMin()); } //create group sql.append("INSERT INTO ").append(TBL_STRUCT_GROUPS) .append("(ID,NAME,DEFMINMULT,DEFMAXMULT,MAYOVERRIDEMULT)VALUES(?,?,?,?,?)"); ps = con.prepareStatement(sql.toString()); ps.setLong(1, newGroupId); ps.setString(2, group.getName()); ps.setInt(3, group.getMultiplicity().getMin()); ps.setInt(4, group.getMultiplicity().getMax()); ps.setBoolean(5, group.mayOverrideBaseMultiplicity()); ps.executeUpdate(); ps.close(); sql.setLength(0); Database.storeFxString(new FxString[] { group.getLabel(), group.getHint() }, con, TBL_STRUCT_GROUPS, new String[] { "DESCRIPTION", "HINT" }, "ID", newGroupId); //calc new position sql.append("SELECT COALESCE(MAX(POS)+1,0) FROM ").append(TBL_STRUCT_ASSIGNMENTS) .append(" WHERE PARENTGROUP=? AND TYPEDEF=?"); ps = con.prepareStatement(sql.toString()); ps.setLong(1, (tmp == null ? FxAssignment.NO_PARENT : tmp.getId())); ps.setLong(2, typeId); ResultSet rs = ps.executeQuery(); long pos = 0; if (rs != null && rs.next()) pos = rs.getLong(1); ps.close(); storeOptions(con, TBL_STRUCT_GROUP_OPTIONS, "ID", newGroupId, null, group.getOptions()); sql.setLength(0); //create root assignment sql.append("INSERT INTO ").append(TBL_STRUCT_ASSIGNMENTS). // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 append("(ID,ATYPE,ENABLED,TYPEDEF,MINMULT,MAXMULT,DEFMULT,POS,XPATH,XALIAS,BASE,PARENTGROUP,AGROUP,GROUPMODE)" + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps = con.prepareStatement(sql.toString()); newAssignmentId = seq.getId(FxSystemSequencer.ASSIGNMENT); ps.setLong(1, newAssignmentId); ps.setInt(2, FxAssignment.TYPE_GROUP); ps.setBoolean(3, true); ps.setLong(4, typeId); ps.setInt(5, group.getMultiplicity().getMin()); ps.setInt(6, group.getMultiplicity().getMax()); if (group.getMultiplicity().isValid(group.getAssignmentDefaultMultiplicity())) { ps.setInt(7, group.getAssignmentDefaultMultiplicity()); } else { //default is min(min,1). ps.setInt(7, group.getMultiplicity().getMin() > 1 ? group.getMultiplicity().getMin() : 1); } ps.setLong(8, pos); if (parentXPath == null || "/".equals(parentXPath)) parentXPath = ""; ps.setString(9, type.getName() + XPathElement.stripType(parentXPath) + "/" + assignmentAlias); ps.setString(10, assignmentAlias); ps.setNull(11, java.sql.Types.NUMERIC); ps.setLong(12, (tmp == null ? FxAssignment.NO_PARENT : tmp.getId())); ps.setLong(13, newGroupId); ps.setInt(14, group.getAssignmentGroupMode().getId()); ps.executeUpdate(); Database.storeFxString(new FxString[] { group.getLabel(), group.getHint() }, con, TBL_STRUCT_ASSIGNMENTS, new String[] { "DESCRIPTION", "HINT" }, "ID", newAssignmentId); StructureLoader.reloadAssignments(FxContext.get().getDivisionId()); htracker.track(type, "history.assignment.createGroup", group.getName(), type.getId(), type.getName()); if (type.getId() != FxType.ROOT_ID) createInheritedAssignments(CacheAdmin.getEnvironment().getAssignment(newAssignmentId), con, sql, type.getDerivedTypes()); } catch (FxNotFoundException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (FxLoadException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (SQLException e) { final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(e); EJBUtils.rollback(ctx); if (uniqueConstraintViolation) throw new FxEntryExistsException("ex.structure.group.exists", group.getName(), (parentXPath.length() == 0 ? "/" : parentXPath)); throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(AssignmentEngineBean.class, con, ps); } return newAssignmentId; }
From source file:com.emr.utilities.CSVLoader.java
/** * Parse CSV file using OpenCSV library and load in * given database table. /* ww w .ja va2s . 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(); } }
From source file:com.gtwm.pb.model.manageData.DataManagement.java
public int importCSV(HttpServletRequest request, TableInfo table, boolean updateExistingRecords, BaseField recordIdentifierField, boolean generateRowIds, char separator, char quotechar, int numHeaderLines, boolean useRelationDisplayValues, boolean importSequenceValues, boolean requireExactRelationMatches, boolean trim, boolean merge, List<FileItem> multipartItems, String csvContent) throws SQLException, InputRecordException, IOException, CantDoThatException, ObjectNotFoundException, DisallowedException, CodingErrorException { if (!FileUpload.isMultipartContent(new ServletRequestContext(request))) { if (csvContent == null) { throw new CantDoThatException( "To import CSV content, a file must be uploaded (form posted as multi-part) or csv_content specified"); }/*from w ww . j a v a 2 s . com*/ } int numImportedRecords = 0; // get field set to import into. LinkedHashSet to ensure order is // retained so the right values are imported into the right fields LinkedHashSet<BaseField> fields = new LinkedHashSet<BaseField>(table.getFields()); // if row IDs aren't included in the data to import, remove ID from the // field set BaseField primaryKey = table.getPrimaryKey(); if (recordIdentifierField == null) { recordIdentifierField = primaryKey; } if (generateRowIds || (updateExistingRecords && !recordIdentifierField.equals(primaryKey))) { fields.remove(primaryKey); } Map<RelationField, Map<String, String>> relationLookups = new HashMap<RelationField, Map<String, String>>(); // Remove fields which shouldn't be modified during the import // For serial fields, if we need to set serial values explicitly, this // will have to be dealt with later for (BaseField field : table.getFields()) { if (field instanceof SequenceField && (!field.equals(primaryKey)) && (!importSequenceValues)) { fields.remove(field); } else if (field.getHidden()) { if (field.getFieldName().equals(HiddenFields.VIEW_COUNT.getFieldName()) || field.getFieldName().equals(HiddenFields.COMMENTS_FEED.getFieldName())) { fields.remove(field); } else if (updateExistingRecords) { if (field.getFieldName().equals(HiddenFields.DATE_CREATED.getFieldName()) || field.getFieldName().equals(HiddenFields.CREATED_BY.getFieldName())) { fields.remove(field); } } } else if (!field.getFieldCategory().savesData()) { fields.remove(field); } // Also, if importing relations by display value, look up // display/internal value mappings if (useRelationDisplayValues && field instanceof RelationField) { Map<String, String> displayToInternalValue = ((RelationFieldDefn) field).getItems(true, false); relationLookups.put((RelationField) field, displayToInternalValue); } } // Prepare SQL String insertSQLCode = null; String updateSQLCode = null; String logCreationSQLCode = null; // If updating, we'll need a record ID value. Depending on what the // identifier field is, this could be one of a couple of different types String recordIdentifierString = null; Integer recordIdentifierInteger = null; int recordIdentifierFieldNum = 0; DatabaseFieldType identifierFieldDbType = null; if (updateExistingRecords) { identifierFieldDbType = recordIdentifierField.getDbType(); if (!identifierFieldDbType.equals(DatabaseFieldType.VARCHAR) && !identifierFieldDbType.equals(DatabaseFieldType.INTEGER) && !identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { throw new CantDoThatException("The record identifier field has to be text or a whole number, " + recordIdentifierField + " is a " + identifierFieldDbType); } updateSQLCode = "UPDATE " + table.getInternalTableName() + " SET "; int fieldNum = 0; for (BaseField field : fields) { fieldNum += 1; if (merge) { // Update database only if there's a non-null value from the // spreadsheet updateSQLCode += field.getInternalFieldName() + " = COALESCE(?," + field.getInternalFieldName() + "), "; } else { updateSQLCode += field.getInternalFieldName() + " = ?, "; } if (field.equals(recordIdentifierField)) { recordIdentifierFieldNum = fieldNum; } } if (recordIdentifierFieldNum == 0) { throw new CantDoThatException("Can't find the field specified as record identifier (" + recordIdentifierField + ") in the list of table fields " + fields + " in table " + table); } updateSQLCode = updateSQLCode.substring(0, updateSQLCode.length() - 2); updateSQLCode += " WHERE " + recordIdentifierField.getInternalFieldName() + "=?"; logCreationSQLCode = "UPDATE " + table.getInternalTableName() + " SET " + table.getField(HiddenFields.DATE_CREATED.getFieldName()).getInternalFieldName() + "=?, " + table.getField(HiddenFields.CREATED_BY.getFieldName()).getInternalFieldName() + "=? WHERE " + primaryKey.getInternalFieldName() + "=?"; } insertSQLCode = "INSERT INTO " + table.getInternalTableName() + "("; String placeholders = ""; for (BaseField field : fields) { insertSQLCode += field.getInternalFieldName() + ", "; placeholders += "?, "; } placeholders = placeholders.substring(0, placeholders.length() - 2); insertSQLCode = insertSQLCode.substring(0, insertSQLCode.length() - 2) + ") VALUES (" + placeholders + ")"; // Find content to import Reader inputStreamReader = null; if (csvContent != null) { inputStreamReader = new StringReader(csvContent); } else { for (FileItem item : multipartItems) { // if item is a file if (!item.isFormField()) { if (item.getName().toLowerCase().endsWith(".xls")) { throw new CantDoThatException( "You need to upload as a CSV to import, Excel files can't be imported directly"); } inputStreamReader = new InputStreamReader(item.getInputStream()); break; } } } if (inputStreamReader == null) { throw new CantDoThatException("No file uploaded"); } CSVReader csvReader = new CSVReader(inputStreamReader, separator, quotechar, numHeaderLines); // returns a list of String arrays List<String[]> csvLines = (List<String[]>) csvReader.readAll(); // do db inserts Connection conn = null; PreparedStatement statement = null; // backupInsertStatement is for when an update returns 0 rows affected, // i.e. there's no matching row. In this case, do an insert PreparedStatement backupInsertStatement = null; PreparedStatement logCreationStatement = null; // These two variables used in exception handling int importLine = 0; BaseField fieldImported = null; Timestamp importTime = new Timestamp(System.currentTimeMillis()); AppUserInfo loggedInUser = authManager.getUserByUserName(request, request.getRemoteUser()); String fullname = loggedInUser.getForename() + " " + loggedInUser.getSurname() + " (" + loggedInUser.getUserName() + ")"; try { conn = this.dataSource.getConnection(); conn.setAutoCommit(false); if (updateExistingRecords) { statement = conn.prepareStatement(updateSQLCode); backupInsertStatement = conn.prepareStatement(insertSQLCode); logCreationStatement = conn.prepareStatement(logCreationSQLCode); } else { statement = conn.prepareStatement(insertSQLCode); } CSVLINE: for (String[] csvLineArray : csvLines) { // convert to an object rather than a primitive array - // easier to work with List<String> lineValues = Arrays.asList(csvLineArray); importLine++; // skip blank lines if (lineValues.size() == 1) { if (lineValues.get(0).length() == 0) { continue CSVLINE; } } int fieldNum = 0; for (BaseField field : fields) { fieldImported = field; fieldNum++; if (field.getHidden()) { String fieldName = field.getFieldName(); if (fieldName.equals(HiddenFields.LOCKED.getFieldName())) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else if (fieldName.equals(HiddenFields.DATE_CREATED.getFieldName()) || fieldName.equals(HiddenFields.LAST_MODIFIED.getFieldName())) { statement.setTimestamp(fieldNum, importTime); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, importTime); } } else if (fieldName.equals(HiddenFields.CREATED_BY.getFieldName()) || fieldName.equals(HiddenFields.MODIFIED_BY.getFieldName())) { statement.setString(fieldNum, fullname); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, fullname); } } } else if (fieldNum > lineValues.size()) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { String lineValue = lineValues.get(fieldNum - 1); if (lineValue != null) { if (trim) { lineValue = lineValue.trim(); } if (lineValue.equals("")) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { if ((field instanceof FileField) && (generateRowIds)) { throw new CantDoThatException( "Cannot generate row ids when importing file names. See line " + importLine + ", field '" + field.getFieldName() + "' with value '" + lineValue + "'"); } switch (field.getDbType()) { case VARCHAR: statement.setString(fieldNum, lineValue); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, lineValue); if (field.equals(recordIdentifierField)) { recordIdentifierString = lineValue; } } break; case TIMESTAMP: // deal with month and year // resolution dates exported if (lineValue.matches("^[a-zA-Z]{3}\\s\\d{2,4}$")) { lineValue = "01 " + lineValue; } else if (lineValue.matches("^\\d{2,4}")) { lineValue = "01 Jan " + lineValue; } try { Calendar calValue = CalendarParser.parse(lineValue, CalendarParser.DD_MM_YY); statement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); } } catch (CalendarParserException cpex) { throw new InputRecordException("Error importing line " + importLine + ", field " + field + ": " + cpex.getMessage(), field, cpex); } break; case FLOAT: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); statement.setDouble(fieldNum, Double.valueOf(lineValue)); if (updateExistingRecords) { backupInsertStatement.setDouble(fieldNum, Double.valueOf(lineValue)); } break; case INTEGER: if ((field instanceof RelationField) && (useRelationDisplayValues)) { // find key value for display value RelationField relationField = (RelationField) field; Map<String, String> valueKeyMap = relationLookups.get(relationField); String internalValueString = valueKeyMap.get(lineValue); if (internalValueString == null) { if (!requireExactRelationMatches) { // A very basic fuzzy matching // algorithm String potentialDisplayValue = null; String lineValueLowerCase = lineValue.toLowerCase(); FUZZYMATCH: for (Map.Entry<String, String> entry : valueKeyMap .entrySet()) { potentialDisplayValue = entry.getKey(); if (potentialDisplayValue.toLowerCase() .contains(lineValueLowerCase)) { internalValueString = entry.getValue(); break FUZZYMATCH; } } } if (internalValueString == null) { throw new CantDoThatException("Error importing line " + importLine + ", field " + relationField + ": Can't find a related '" + relationField.getRelatedTable() + "' for " + relationField.getDisplayField() + " '" + lineValue + "'. "); } } int keyValue = Integer.valueOf(internalValueString); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } else { lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } break; case SERIAL: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } break; case BOOLEAN: boolean filterValueIsTrue = Helpers.valueRepresentsBooleanTrue(lineValue); statement.setBoolean(fieldNum, filterValueIsTrue); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, filterValueIsTrue); } break; } } } else { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } } } if (updateExistingRecords) { // for potential error messages String recordIdentifierDescription = null; if (identifierFieldDbType.equals(DatabaseFieldType.INTEGER) || identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { if (recordIdentifierInteger == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = " + recordIdentifierInteger; // Set the 'WHERE recordIdentifier = ?' clause statement.setInt(fields.size() + 1, recordIdentifierInteger); } else { if (recordIdentifierString == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = '" + recordIdentifierString + "'"; // Set the 'WHERE recordIdentifier = ?' clause statement.setString(fields.size() + 1, recordIdentifierString); } int rowsAffected = statement.executeUpdate(); if (rowsAffected == 0) { // If can't find a match to update, insert a record // instead backupInsertStatement.executeUpdate(); // NB Postgres specific code to find Row ID of newly // inserted record, not cross-db compatible String newRowIdSQLCode = "SELECT currval('" + table.getInternalTableName() + "_" + primaryKey.getInternalFieldName() + "_seq')"; PreparedStatement newRowIdStatement = conn.prepareStatement(newRowIdSQLCode); ResultSet newRowIdResults = newRowIdStatement.executeQuery(); if (newRowIdResults.next()) { int newRowId = newRowIdResults.getInt(1); // Add creation metadata to the new row logCreationStatement.setTimestamp(1, importTime); logCreationStatement.setString(2, fullname); logCreationStatement.setInt(3, newRowId); int creationLogRowsAffected = logCreationStatement.executeUpdate(); if (creationLogRowsAffected == 0) { throw new SQLException( "Unable to update creation metadata of newly inserted record, using query " + logCreationStatement); } } else { newRowIdResults.close(); newRowIdStatement.close(); throw new SQLException("Row ID not found for the newly inserted record. '" + newRowIdStatement + "' didn't work"); } newRowIdResults.close(); newRowIdStatement.close(); } else if (rowsAffected > 1) { throw new InputRecordException("Error importing line " + importLine + ". The record identifier field " + recordIdentifierDescription + " should match only 1 record in the database but it actually matches " + rowsAffected, recordIdentifierField); } // reset to null for the next line recordIdentifierString = null; recordIdentifierInteger = null; } else { statement.executeUpdate(); } numImportedRecords += 1; } statement.close(); if (backupInsertStatement != null) { backupInsertStatement.close(); } if (logCreationStatement != null) { logCreationStatement.close(); } // reset the primary key ID sequence so new records can be added resetSequence((SequenceField) primaryKey, conn); // and any other sequence fields if (importSequenceValues) { for (BaseField field : table.getFields()) { if ((!field.equals(primaryKey)) && field instanceof SequenceField) { resetSequence((SequenceField) field, conn); } } } // ANALYZE the table after import if (numImportedRecords > 1000) { Statement analyzeStatement = conn.createStatement(); analyzeStatement.execute("ANALYZE " + table.getInternalTableName()); analyzeStatement.close(); } conn.commit(); } catch (SQLException sqlex) { String databaseErrorMessage = Helpers.replaceInternalNames(sqlex.getMessage(), table.getDefaultReport()); logger.warn("Import failed, statement is " + statement); logger.warn("Backup insert statement is " + backupInsertStatement); String errorMessage = "Error importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + databaseErrorMessage; throw new InputRecordException(errorMessage, fieldImported, sqlex); } catch (NumberFormatException nfex) { String causeMessage = nfex.getMessage(); causeMessage = causeMessage.replaceAll("For input string", "value"); String errorMessage = "Error parsing number when importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + causeMessage; throw new InputRecordException(errorMessage, fieldImported, nfex); } finally { if (conn != null) { conn.close(); } } this.logLastDataChangeTime(request); logLastTableDataChangeTime(table); UsageLogger usageLogger = new UsageLogger(this.dataSource); String logMessage = "" + numImportedRecords; if (updateExistingRecords) { logMessage += " records imported"; } else { logMessage += " new records imported"; } if (csvContent != null) { logMessage += " from file"; } usageLogger.logDataChange(loggedInUser, table, null, AppAction.CSV_IMPORT, -1, logMessage); UsageLogger.startLoggingThread(usageLogger); return numImportedRecords; }
From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSpreaded.java
protected long _reorganizeSpace(Connection con, SequencerEngine seq, FxTreeMode sourceMode, FxTreeMode destMode, long nodeId, boolean includeNodeId, BigInteger overrideSpacing, BigInteger overrideLeft, FxTreeNodeInfo insertParent, int insertPosition, BigInteger insertSpace, BigInteger insertBoundaries[], int depthDelta, Long destinationNode, boolean createMode, boolean createKeepIds, boolean disableSpaceOptimization) throws FxTreeException { long firstCreatedNodeId = -1; FxTreeNodeInfoSpreaded nodeInfo;//from w w w . j a v a 2 s . c om try { nodeInfo = (FxTreeNodeInfoSpreaded) getTreeNodeInfo(con, sourceMode, nodeId); } catch (Exception e) { return -1; } if (!nodeInfo.isSpaceOptimizable() && !disableSpaceOptimization) { // The Root node and cant be optimize any more ... so all we can do is fail :-/ // This should never really happen if (nodeId == ROOT_NODE) { return -1; } //System.out.println("### UP we go, depthDelta=" + depthDelta); return _reorganizeSpace(con, seq, sourceMode, destMode, nodeInfo.getParentId(), includeNodeId, overrideSpacing, overrideLeft, insertParent, insertPosition, insertSpace, insertBoundaries, depthDelta, destinationNode, createMode, createKeepIds, false); } BigInteger spacing = nodeInfo.getDefaultSpacing(); if (overrideSpacing != null && (overrideSpacing.compareTo(spacing) < 0 || overrideLeft != null)) { // override spacing unless it is greater OR overrideLeft is specified (in that case we // have to use the spacing for valid tree ranges) spacing = overrideSpacing; } else { if (spacing.compareTo(GO_UP) < 0 && !createMode && !disableSpaceOptimization) { return _reorganizeSpace(con, seq, sourceMode, destMode, nodeInfo.getParentId(), includeNodeId, overrideSpacing, overrideLeft, insertParent, insertPosition, insertSpace, insertBoundaries, depthDelta, destinationNode, createMode, createKeepIds, false); } } if (insertBoundaries != null && insertPosition == -1) { insertPosition = 0; // insertPosition cannot be negative } Statement stmt = null; PreparedStatement ps = null; ResultSet rs; BigInteger left = overrideLeft == null ? nodeInfo.getLeft() : overrideLeft; BigInteger right = null; String includeNode = includeNodeId ? "=" : ""; long counter = 0; long newId = -1; try { final long start = System.currentTimeMillis(); String createProps = createMode ? ",PARENT,REF,NAME,TEMPLATE" : ""; String sql = " SELECT ID," + StorageManager.getIfFunction( // compute total child count only when the node has children "CHILDCOUNT = 0", "0", "(SELECT COUNT(*) FROM " + getTable(sourceMode) + " WHERE LFT > NODE.LFT AND RGT < NODE.RGT)") + // 3 4 5 6 ", CHILDCOUNT, LFT AS LFTORD,RGT,DEPTH" + createProps + " FROM (SELECT ID,CHILDCOUNT,LFT,RGT,DEPTH" + createProps + " FROM " + getTable(sourceMode) + " WHERE " + "LFT>" + includeNode + nodeInfo.getLeft() + " AND LFT<" + includeNode + nodeInfo.getRight() + ") NODE " + "ORDER BY LFTORD ASC"; stmt = con.createStatement(); rs = stmt.executeQuery(sql); if (createMode) { // 1 2 3 4 5 6 7 8 ps = con.prepareStatement( "INSERT INTO " + getTable(destMode) + " (ID,PARENT,DEPTH,DIRTY,REF,TEMPLATE,LFT,RGT," + //9 10 11 "CHILDCOUNT,NAME,MODIFIED_AT) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?)"); } else { ps = con.prepareStatement("UPDATE " + getTable(sourceMode) + " SET LFT=?,RGT=?,DEPTH=? WHERE ID=?"); } long id; int total_childs; int direct_childs; BigInteger nextLeft; int lastDepth = nodeInfo.getDepth() + (includeNodeId ? 0 : 1); int depth; BigInteger _rgt; BigInteger _lft; Long ref = null; String data = null; String name = ""; Stack<Long> currentParent = null; if (createMode) { currentParent = new Stack<Long>(); currentParent.push(destinationNode); } //System.out.println("Spacing:"+SPACING); while (rs.next()) { //System.out.println("------------------"); id = rs.getLong(1); total_childs = rs.getInt(2); direct_childs = rs.getInt(3); _lft = getNodeBounds(rs, 4); _rgt = getNodeBounds(rs, 5); depth = rs.getInt(6); if (createMode) { // Reading these properties is slow, only do it when needed ref = rs.getLong(8); if (rs.wasNull()) ref = null; name = rs.getString(9); data = rs.getString(10); if (rs.wasNull()) data = null; } left = left.add(spacing).add(BigInteger.ONE); // Handle depth differences if (lastDepth - depth > 0) { BigInteger depthDifference = spacing.add(BigInteger.ONE); left = left.add(depthDifference.multiply(BigInteger.valueOf(lastDepth - depth))); } if (createMode) { if (lastDepth < depth) { currentParent.push(newId); } else if (lastDepth > depth) { for (int p = 0; p < (lastDepth - depth); p++) currentParent.pop(); } } right = left.add(spacing).add(BigInteger.ONE); // add child space if needed if (total_childs > 0) { BigInteger childSpace = spacing.multiply(BigInteger.valueOf(total_childs * 2)); childSpace = childSpace.add(BigInteger.valueOf((total_childs * 2) - 1)); right = right.add(childSpace); nextLeft = left; } else { nextLeft = right; } if (insertBoundaries != null) { // insert gap at requested position // If we're past the gap, keep adding the insert space to left/right because the added // space is never "injected" into the loop, i.e. without adding it the left/right boundaries of // nodes after the gap would be too far to the left. if (_lft.compareTo(insertBoundaries[0]) > 0) { left = left.add(insertSpace); } if (_rgt.compareTo(insertBoundaries[0]) > 0) { right = right.add(insertSpace); } } // sanity checks if (left.compareTo(right) >= 0) { throw new FxTreeException(LOG, "ex.tree.reorganize.failed", counter, left, right, "left greater than right"); } if (insertParent != null && right.compareTo((BigInteger) insertParent.getRight()) > 0) { throw new FxTreeException(LOG, "ex.tree.reorganize.failed", counter, left, right, "wrote past parent node bounds"); } // Update the node if (createMode) { newId = createKeepIds ? id : seq.getId(destMode.getSequencer()); if (firstCreatedNodeId == -1) firstCreatedNodeId = newId; // Create the main entry ps.setLong(1, newId); ps.setLong(2, currentParent.peek()); ps.setLong(3, depth + depthDelta); ps.setBoolean(4, destMode != FxTreeMode.Live); //only flag non-live tree's dirty if (ref == null) { ps.setNull(5, java.sql.Types.NUMERIC); } else { ps.setLong(5, ref); } if (data == null) { ps.setNull(6, java.sql.Types.VARCHAR); } else { ps.setString(6, data); } // System.out.println("=> id:"+newId+" left:"+left+" right:"+right); setNodeBounds(ps, 7, left); setNodeBounds(ps, 8, right); ps.setInt(9, direct_childs); ps.setString(10, name); ps.setLong(11, System.currentTimeMillis()); ps.addBatch(); } else { setNodeBounds(ps, 1, left); setNodeBounds(ps, 2, right); ps.setInt(3, depth + depthDelta); ps.setLong(4, id); ps.addBatch(); // ps.executeBatch(); // ps.clearBatch(); } // Prepare variables for the next node left = nextLeft; lastDepth = depth; counter++; // Execute batch every 10000 items to avoid out of memory if (counter % 10000 == 0) { ps.executeBatch(); ps.clearBatch(); } } rs.close(); stmt.close(); stmt = null; ps.executeBatch(); if (LOG.isDebugEnabled()) { final long time = System.currentTimeMillis() - start; LOG.debug("Tree reorganization of " + counter + " items completed in " + time + " ms (spaceLen=" + spacing + ")"); } return firstCreatedNodeId; } catch (FxApplicationException e) { throw e instanceof FxTreeException ? (FxTreeException) e : new FxTreeException(e); } catch (SQLException e) { String next = ""; if (e.getNextException() != null) next = " next:" + e.getNextException().getMessage(); if (StorageManager.isDuplicateKeyViolation(e)) throw new FxTreeException(LOG, e, "ex.tree.reorganize.duplicateKey"); throw new FxTreeException(LOG, e, "ex.tree.reorganize.failed", counter, left, right, e.getMessage() + next); } catch (Exception e) { throw new FxTreeException(e); } finally { try { if (stmt != null) stmt.close(); } catch (Throwable t) { /*ignore*/} try { if (ps != null) ps.close(); } catch (Throwable t) { /*ignore*/} } }
From source file:com.flexive.core.storage.genericSQL.GenericBinarySQLStorage.java
/** * Transfer a binary from the transit to the 'real' binary table * * @param _con open and valid connection * @param binary the binary descriptor/*from w w w . j av a 2 s. co m*/ * @param id desired id * @param version desired version * @param quality desired quality * @return descriptor of final binary * @throws FxDbException on errors looking up the sequencer */ private BinaryDescriptor binaryTransit(Connection _con, BinaryDescriptor binary, long id, int version, int quality) throws FxDbException { PreparedStatement ps = null; BinaryDescriptor created; FileInputStream fis = null; boolean dbTransit; boolean dbStorage; final long dbThreshold; final long dbPreviewThreshold; final int divisionId = FxContext.get().getDivisionId(); try { final DivisionConfigurationEngine divisionConfig = EJBLookup.getDivisionConfigurationEngine(); dbTransit = divisionConfig.get(SystemParameters.BINARY_TRANSIT_DB); if (id >= 0) { dbThreshold = divisionConfig.get(SystemParameters.BINARY_DB_THRESHOLD); dbPreviewThreshold = divisionConfig.get(SystemParameters.BINARY_DB_PREVIEW_THRESHOLD); } else { //force storage of system binaries in the database dbThreshold = -1; dbPreviewThreshold = -1; } dbStorage = dbThreshold < 0 || binary.getSize() < dbThreshold; } catch (FxApplicationException e) { throw e.asRuntimeException(); } Connection con = null; try { con = Database.getNonTXDataSource(divisionId).getConnection(); con.setAutoCommit(false); double resolution = 0.0; int width = 0; int height = 0; boolean isImage = binary.getMimeType().startsWith("image/"); if (isImage) { try { width = Integer .parseInt(defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "width"), "0")); height = Integer.parseInt( defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "height"), "0")); resolution = Double.parseDouble( defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "xResolution"), "0")); } catch (NumberFormatException e) { //ignore LOG.warn(e, e); } } created = new BinaryDescriptor(CacheAdmin.getStreamServers(), id, version, quality, System.currentTimeMillis(), binary.getName(), binary.getSize(), binary.getMetadata(), binary.getMimeType(), isImage, resolution, width, height, binary.getMd5sum()); //we can copy the blob directly into the binary table if the database is used for transit and the final binary is //stored in the filesystem final boolean copyBlob = dbTransit && dbStorage; boolean storePrev1FS = false, storePrev2FS = false, storePrev3FS = false, storePrev4FS = false; long prev1Length = -1, prev2Length = -1, prev3Length = -1, prev4Length = -1; if (dbPreviewThreshold >= 0) { //we have to check if preview should be stored on the filesystem ps = con.prepareStatement(BINARY_TRANSIT_PREVIEW_SIZES); ps.setString(1, binary.getHandle()); ResultSet rs = ps.executeQuery(); if (!rs.next()) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); rs.getLong(1); //check if previewref is null if (rs.wasNull()) { //if previews are not referenced, check thresholds storePrev1FS = (prev1Length = rs.getLong(2)) >= dbPreviewThreshold && !rs.wasNull(); storePrev2FS = (prev2Length = rs.getLong(3)) >= dbPreviewThreshold && !rs.wasNull(); storePrev3FS = (prev3Length = rs.getLong(4)) >= dbPreviewThreshold && !rs.wasNull(); storePrev4FS = (prev4Length = rs.getLong(5)) >= dbPreviewThreshold && !rs.wasNull(); } } if (ps != null) ps.close(); String previewSelect = (storePrev1FS ? ",NULL" : ",PREV1") + (storePrev2FS ? ",NULL" : ",PREV2") + (storePrev3FS ? ",NULL" : ",PREV3") + (storePrev4FS ? ",NULL" : ",PREV4"); //check if the binary is to be replaced ps = con.prepareStatement( "SELECT COUNT(*) FROM " + TBL_CONTENT_BINARY + " WHERE ID=? AND VER=? AND QUALITY=?"); ps.setLong(1, created.getId()); ps.setInt(2, created.getVersion()); //version ps.setInt(3, created.getQuality()); //quality ResultSet rsExist = ps.executeQuery(); final boolean replaceBinary = rsExist != null && rsExist.next() && rsExist.getLong(1) > 0; ps.close(); int paramIndex = 1; if (replaceBinary) { ps = con.prepareStatement(BINARY_TRANSIT_REPLACE + (copyBlob ? BINARY_TRANSIT_REPLACE_FBLOB_COPY : BINARY_TRANSIT_REPLACE_FBLOB_PARAM) + BINARY_TRANSIT_REPLACE_PARAMS); FxBinaryUtils.removeBinary(divisionId, created.getId()); } else { ps = con.prepareStatement((copyBlob ? BINARY_TRANSIT : BINARY_TRANSIT_FILESYSTEM) + previewSelect + BINARY_TRANSIT_PREVIEW_WHERE); ps.setLong(paramIndex++, created.getId()); ps.setInt(paramIndex++, created.getVersion()); //version ps.setInt(paramIndex++, created.getQuality()); //quality } File binaryTransit = null; boolean removeTransitFile = false; if (dbTransit) { //transit is handled in the database try { if (!dbStorage) { //binaries are stored on the filesystem binaryTransit = getBinaryTransitFileInfo(binary).getBinaryTransitFile(); removeTransitFile = true; //have to clean up afterwards since its a temporary file we get } } catch (FxApplicationException e) { if (e instanceof FxDbException) throw (FxDbException) e; throw new FxDbException(e); } } else { //transit file resides on the local file system binaryTransit = FxBinaryUtils.getTransitFile(divisionId, binary.getHandle()); removeTransitFile = true; // temporary transit file can be removed as well if (binaryTransit == null) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); } boolean needExplicitBlobInsert = false; if (copyBlob && replaceBinary) ps.setString(paramIndex++, binary.getHandle()); if (!copyBlob) { //we do not perform a simple blob copy operation in the database if (dbStorage) { //binary is stored in the database -> copy it from the transit file (might be a temp. file) if (blobInsertSelectAllowed()) { fis = new FileInputStream(binaryTransit); ps.setBinaryStream(paramIndex++, fis, (int) binaryTransit.length()); } else { ps.setNull(paramIndex++, Types.BINARY); needExplicitBlobInsert = true; } } else { //binary is stored on the filesystem -> move transit file to binary storage file try { if (!FxFileUtils.moveFile(binaryTransit, FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.ORIGINAL.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId()); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId(), e.getMessage()); } ps.setNull(paramIndex++, Types.BINARY); } } // int cnt = paramIndex; //copyBlob ? 4 : 5; ps.setString(paramIndex++, created.getName()); ps.setLong(paramIndex++, created.getSize()); setBigString(ps, paramIndex++, created.getMetadata()); ps.setString(paramIndex++, created.getMimeType()); if (replaceBinary) ps.setNull(paramIndex++, java.sql.Types.NUMERIC); //set preview ref to null ps.setBoolean(paramIndex++, created.isImage()); ps.setDouble(paramIndex++, created.getResolution()); ps.setInt(paramIndex++, created.getWidth()); ps.setInt(paramIndex++, created.getHeight()); ps.setString(paramIndex++, created.getMd5sum()); if (replaceBinary) { ps.setLong(paramIndex++, created.getId()); ps.setInt(paramIndex++, created.getVersion()); //version ps.setInt(paramIndex, created.getQuality()); //quality } else ps.setString(paramIndex, binary.getHandle()); ps.executeUpdate(); if (needExplicitBlobInsert) { ps.close(); ps = con.prepareStatement( "UPDATE " + TBL_CONTENT_BINARY + " SET FBLOB=? WHERE ID=? AND VER=? AND QUALITY=?"); fis = new FileInputStream(binaryTransit); ps.setBinaryStream(1, fis, (int) binaryTransit.length()); ps.setLong(2, created.getId()); ps.setInt(3, created.getVersion()); //version ps.setInt(4, created.getQuality()); //quality ps.executeUpdate(); } if (removeTransitFile && binaryTransit != null) { //transit file was a temp. file -> got to clean up FxFileUtils.removeFile(binaryTransit); } if (replaceBinary) { ps.close(); //set all preview entries to the values provided by the transit table ps = con.prepareStatement("UPDATE " + TBL_CONTENT_BINARY + " SET PREV1=NULL,PREV2=NULL,PREV3=NULL,PREV4=NULL WHERE ID=? AND VER=? AND QUALITY=?"); ps.setLong(1, created.getId()); ps.setInt(2, created.getVersion()); //version ps.setInt(3, created.getQuality()); //quality ps.executeUpdate(); ps.close(); ps = con.prepareStatement( "SELECT PREV1_WIDTH,PREV1_HEIGHT,PREV1SIZE,PREV2_WIDTH,PREV2_HEIGHT,PREV2SIZE,PREV3_WIDTH,PREV3_HEIGHT,PREV3SIZE,PREV4_WIDTH,PREV4_HEIGHT,PREV4SIZE FROM " + TBL_BINARY_TRANSIT + " WHERE BKEY=?"); ps.setString(1, binary.getHandle()); ResultSet rsPrev = ps.executeQuery(); if (rsPrev != null && rsPrev.next()) { long[] data = new long[12]; for (int d = 0; d < 12; d++) data[d] = rsPrev.getLong(d + 1); ps.close(); ps = con.prepareStatement("UPDATE " + TBL_CONTENT_BINARY + " SET PREV1_WIDTH=?,PREV1_HEIGHT=?,PREV1SIZE=?,PREV2_WIDTH=?,PREV2_HEIGHT=?,PREV2SIZE=?,PREV3_WIDTH=?,PREV3_HEIGHT=?,PREV3SIZE=?,PREV4_WIDTH=?,PREV4_HEIGHT=?,PREV4SIZE=? WHERE ID=? AND VER=? AND QUALITY=?"); for (int d = 0; d < 12; d++) ps.setLong(d + 1, data[d]); ps.setLong(13, created.getId()); ps.setInt(14, created.getVersion()); //version ps.setInt(15, created.getQuality()); //quality ps.executeUpdate(); } } //finally fetch the preview blobs from transit and store them on the filesystem if required if (storePrev1FS || storePrev2FS || storePrev3FS || storePrev4FS) { ps.close(); previewSelect = (!storePrev1FS ? ",NULL" : ",PREV1") + (!storePrev2FS ? ",NULL" : ",PREV2") + (!storePrev3FS ? ",NULL" : ",PREV3") + (!storePrev4FS ? ",NULL" : ",PREV4"); ps = con.prepareStatement("SELECT " + previewSelect.substring(1) + BINARY_TRANSIT_PREVIEW_WHERE); ps.setString(1, binary.getHandle()); ResultSet rs = ps.executeQuery(); if (!rs.next()) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); if (storePrev1FS) try { if (!FxFileUtils.copyStream2File(prev1Length, rs.getBinaryStream(1), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW1.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW1.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW1.getBlobIndex() + "]", e.getMessage()); } if (storePrev2FS) try { if (!FxFileUtils.copyStream2File(prev2Length, rs.getBinaryStream(2), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW2.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW2.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW2.getBlobIndex() + "]", e.getMessage()); } if (storePrev3FS) try { if (!FxFileUtils.copyStream2File(prev3Length, rs.getBinaryStream(3), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW3.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW3.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW3.getBlobIndex() + "]", e.getMessage()); } if (storePrev4FS) try { if (!FxFileUtils.copyStream2File(prev4Length, rs.getBinaryStream(4), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.SCREENVIEW.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.SCREENVIEW.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.SCREENVIEW.getBlobIndex() + "]", e.getMessage()); } } con.commit(); } catch (SQLException e) { throw new FxDbException(e, "ex.db.sqlError", e.getMessage()); } catch (FileNotFoundException e) { throw new FxDbException(e, "ex.content.binary.IOError", binary.getHandle()); } finally { Database.closeObjects(GenericBinarySQLStorage.class, con, ps); FxSharedUtils.close(fis); } return created; }
From source file:com.funambol.foundation.items.dao.PIMCalendarDAO.java
/** * Adds a calendar. If necessary, a new ID is generated and set in the * CalendarWrapper.//from w w w . ja v a 2 s. co m * * @param c as a CalendarWrapper object, usually without an ID set. * @throws DAOException * * @see CalendarWrapper */ public void addItem(CalendarWrapper cw) throws DAOException { if (log.isTraceEnabled()) { log.trace("PIMCalendarDAO addItem begin"); } Connection con = null; PreparedStatement ps = null; long id = 0; String allDay = null; String body = null; Short busyStatus = null; String categories = null; String companies = null; int duration = 0; Date dend = null; short importance = 0; String location = null; Short meetingStatus = null; String mileage = null; Date replyTime = null; short sensitivity = 0; Date dstart = null; String subject = null; int interval = 0; short monthOfYear = 0; short dayOfMonth = 0; String dayOfWeekMask = null; short instance = 0; String startDatePattern = null; String endDatePattern = null; Reminder reminder = null; RecurrencePattern rp = null; short recurrenceType = -1; String sId = null; int occurrences = -1; String folder = null; String dstartTimeZone = null; String dendTimeZone = null; String reminderTimeZone = null; Date completed = null; short percentComplete = -1; Timestamp lastUpdate = cw.getLastUpdate(); if (lastUpdate == null) { lastUpdate = new Timestamp(System.currentTimeMillis()); } try { sId = cw.getId(); if (sId == null || sId.length() == 0) { // ...as it should be sId = getNextID(); cw.setId(sId); } id = Long.parseLong(sId); CalendarContent c = cw.getCalendar().getCalendarContent(); rp = c.getRecurrencePattern(); boolean allDayB; if (c.getAllDay() != null && c.getAllDay().booleanValue()) { allDayB = true; allDay = "1"; } else { allDayB = false; allDay = "0"; } String sd = null; if (c.getDtStart() != null) { sd = c.getDtStart().getPropertyValueAsString(); dstart = getDateFromString(allDayB, sd, "000000"); } String ed = null; if ((sd != null && sd.length() > 0) || c.getDtEnd() != null) { ed = c.getDtEnd().getPropertyValueAsString(); // // Fix for Siemens S56 end date issue only for event // @todo: verify if is really need to do this // if (c instanceof Event) { if (ed == null || ed.length() == 0) { ed = sd; } } dend = getDateFromString(allDayB, ed, "235900"); } body = Property.stringFrom(c.getDescription()); if (c.getBusyStatus() != null) { busyStatus = new Short(c.getBusyStatus().shortValue()); } categories = Property.stringFrom(c.getCategories()); companies = Property.stringFrom(c.getOrganizer()); location = Property.stringFrom(c.getLocation()); folder = Property.stringFrom(c.getFolder()); dstartTimeZone = timeZoneFrom(c.getDtStart()); dendTimeZone = timeZoneFrom(c.getDtEnd()); reminderTimeZone = timeZoneFrom(c.getReminder()); meetingStatus = c.getMeetingStatus(); Integer mileageInteger = c.getMileage(); // NB: not an int... if (mileageInteger != null) { // ...therefore it may be null mileage = String.valueOf(mileageInteger); } if (c instanceof Event) { replyTime = getDateFromString(allDayB, // @todo or false? Property.stringFrom(((Event) c).getReplyTime()), "000000"); } try { sensitivity = Short.parseShort(Property.stringFrom(c.getAccessClass())); } catch (NumberFormatException nfe) { sensitivity = 0; // The short must go on } if ((subject = Property.stringFrom(c.getSummary())) == null && body != null) { int endOfSentence = body.indexOf('.'); if (endOfSentence == -1) { endOfSentence = body.length(); } if (endOfSentence > SQL_SUBJECT_DIM) { endOfSentence = SQL_SUBJECT_DIM; } subject = body.substring(0, endOfSentence); } String isInfinite = "0"; if (rp != null) { interval = rp.getInterval(); recurrenceType = rp.getTypeId(); monthOfYear = rp.getMonthOfYear(); dayOfMonth = rp.getDayOfMonth(); dayOfWeekMask = String.valueOf(rp.getDayOfWeekMask()); instance = rp.getInstance(); startDatePattern = rp.getStartDatePattern(); endDatePattern = rp.getEndDatePattern(); if (rp.isNoEndDate()) { isInfinite = "1"; } occurrences = rp.getOccurrences(); } if (c instanceof Task) { Task t = (Task) c; if (t.getDateCompleted() != null) { completed = getDateFromString(allDayB, ((Task) c).getDateCompleted().getPropertyValueAsString(), "000000"); } try { String complete = Property.stringFrom(t.getComplete()); if (complete != null && complete.equals("1")) { percentComplete = 100; } else { percentComplete = Short.parseShort(Property.stringFrom(t.getPercentComplete())); } if (percentComplete < 0 || percentComplete > 100) { throw new NumberFormatException("A percentage can't be " + percentComplete); } } catch (NumberFormatException nfe) { percentComplete = -1; // the short must go on } meetingStatus = getTaskStatus(t); } con = getUserDataSource().getRoutedConnection(userId); ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR); ps.setLong(1, id); ps.setString(2, userId); ps.setLong(3, lastUpdate.getTime()); ps.setString(4, String.valueOf(Def.PIM_STATE_NEW)); ps.setString(5, allDay); ps.setString(6, StringUtils.left(body, SQL_BODY_DIM)); if (busyStatus != null) { ps.setShort(7, busyStatus.shortValue()); } else { ps.setNull(7, Types.SMALLINT); } ps.setString(8, StringUtils.left(categories, SQL_CATEGORIES_DIM)); ps.setString(9, StringUtils.left(companies, SQL_COMPANIES_DIM)); ps.setInt(10, duration); if (dend != null) { ps.setTimestamp(11, new Timestamp(dend.getTime())); } else { ps.setNull(11, Types.TIMESTAMP); } if (c.getPriority() != null) { String priority = c.getPriority().getPropertyValueAsString(); if (priority != null && priority.length() > 0) { importance = Short.parseShort(priority); ps.setShort(12, importance); } else { ps.setNull(12, Types.SMALLINT); } } else { ps.setNull(12, Types.SMALLINT); } ps.setString(13, StringUtils.left(location, SQL_LOCATION_DIM)); if (meetingStatus != null) { ps.setShort(14, meetingStatus.shortValue()); } else { ps.setNull(14, Types.SMALLINT); } ps.setString(15, mileage); reminder = c.getReminder(); if (reminder != null && reminder.isActive()) { Timestamp reminderTime = getReminderTime(dstart, reminder); if (reminderTime == null) { ps.setNull(16, Types.TIMESTAMP); } else { ps.setTimestamp(16, reminderTime); } ps.setInt(17, reminder.getRepeatCount()); ps.setString(18, (reminder.isActive()) ? "1" : "0"); String soundFileValue = reminder.getSoundFile(); ps.setString(19, StringUtils.left(soundFileValue, SQL_SOUNDFILE_DIM)); ps.setInt(20, reminder.getOptions()); } else { ps.setNull(16, Types.TIMESTAMP); ps.setInt(17, 0); ps.setString(18, "0"); ps.setString(19, null); ps.setInt(20, 0); } if (replyTime != null) { ps.setTimestamp(21, new Timestamp(replyTime.getTime())); } else { ps.setNull(21, Types.TIMESTAMP); } ps.setShort(22, sensitivity); if (dstart != null) { ps.setTimestamp(23, new Timestamp(dstart.getTime())); } else { ps.setNull(23, Types.TIMESTAMP); } ps.setString(24, StringUtils.left(subject, SQL_SUBJECT_DIM)); ps.setShort(25, recurrenceType); ps.setInt(26, interval); ps.setShort(27, monthOfYear); ps.setShort(28, dayOfMonth); ps.setString(29, StringUtils.left(dayOfWeekMask, SQL_DAYOFWEEKMASK_DIM)); ps.setShort(30, instance); ps.setString(31, StringUtils.left(startDatePattern, SQL_STARTDATEPATTERN_DIM)); ps.setString(32, isInfinite); ps.setString(33, StringUtils.left(endDatePattern, SQL_ENDDATEPATTERN_DIM)); ps.setInt(34, occurrences); if (c instanceof Event) { ps.setInt(35, CALENDAR_EVENT_TYPE); ps.setNull(36, Types.TIMESTAMP); // completed ps.setNull(37, Types.SMALLINT); // percent_complete } else { ps.setInt(35, CALENDAR_TASK_TYPE); if (completed != null) { ps.setTimestamp(36, new Timestamp(completed.getTime())); } else { ps.setNull(36, Types.TIMESTAMP); } if (percentComplete != -1) { ps.setShort(37, percentComplete); } else { ps.setNull(37, Types.SMALLINT); } } ps.setString(38, StringUtils.left(folder, SQL_FOLDER_DIM)); ps.setString(39, StringUtils.left(dstartTimeZone, SQL_TIME_ZONE_DIM)); ps.setString(40, StringUtils.left(dendTimeZone, SQL_TIME_ZONE_DIM)); ps.setString(41, StringUtils.left(reminderTimeZone, SQL_TIME_ZONE_DIM)); ps.executeUpdate(); DBTools.close(null, ps, null); if (recurrenceType != -1) { List<ExceptionToRecurrenceRule> exceptions = rp.getExceptions(); for (ExceptionToRecurrenceRule etrr : exceptions) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR_EXCEPTION); ps.setLong(1, id); ps.setString(2, (etrr.isAddition() ? "1" : "0")); ps.setTimestamp(3, new Timestamp(getDateFromString(allDayB, etrr.getDate(), "000000").getTime())); ps.executeUpdate(); DBTools.close(null, ps, null); } } } catch (Exception e) { throw new DAOException("Error adding a calendar item: " + e.getMessage(), e); } finally { DBTools.close(con, ps, null); } }
From source file:com.funambol.foundation.items.dao.PIMContactDAO.java
/** * Adds a contact. If necessary, a new ID is generated and set in the * ContactWrapper.//from ww w .ja va 2 s .c om * * @param cw as a ContactWrapper object, usually without an ID set. * @throws DAOException * * @see ContactWrapper */ public void addItem(ContactWrapper cw) throws DAOException { if (log.isTraceEnabled()) { log.trace("Storing a contact item..."); } Connection con = null; PreparedStatement ps = null; long id = 0; int type = 0; PersonalDetail personalDetail = null; BusinessDetail businessDetail = null; Address homeAddressBook = null; Address workAddressBook = null; Address otherAddressBook = null; Name name = null; Phone phone = null; Email email = null; WebPage webPage = null; List<WebPage> webPages = new ArrayList<WebPage>(); List<Email> emails = new ArrayList<Email>(); List<Phone> phones = new ArrayList<Phone>(); List<String[]> labels = new ArrayList<String[]>(); String webPageType = null; Short importance = null; Short sensitivity = null; String mileage = null; String subject = null; String folder = null; String anniversary = null; String firstName = null; String middleName = null; String lastName = null; String displayName = null; String birthday = null; String categories = null; String gender = null; String hobbies = null; String initials = null; String languages = null; String nickName = null; String spouse = null; String suffix = null; String assistant = null; String officeLocation = null; String company = null; String companies = null; String department = null; String manager = null; String role = null; String children = null; String salutation = null; String sId = null; Timestamp lastUpdate = cw.getLastUpdate(); if (lastUpdate == null) { lastUpdate = new Timestamp(System.currentTimeMillis()); } try { // Looks up the data source when the first connection is created con = getUserDataSource().getRoutedConnection(userId); sId = cw.getId(); if (sId == null) { // ...as it should be sId = getNextID(); cw.setId(sId); } id = Long.parseLong(sId); Contact c = cw.getContact(); personalDetail = c.getPersonalDetail(); businessDetail = c.getBusinessDetail(); name = c.getName(); if (personalDetail != null) { homeAddressBook = personalDetail.getAddress(); otherAddressBook = personalDetail.getOtherAddress(); webPages.addAll(personalDetail.getWebPages()); emails.addAll(personalDetail.getEmails()); phones.addAll(personalDetail.getPhones()); } if (businessDetail != null) { workAddressBook = businessDetail.getAddress(); webPages.addAll(businessDetail.getWebPages()); emails.addAll(businessDetail.getEmails()); phones.addAll(businessDetail.getPhones()); companies = businessDetail.getCompanies(); } importance = c.getImportance(); sensitivity = c.getSensitivity(); mileage = c.getMileage(); subject = c.getSubject(); languages = c.getLanguages(); categories = Property.stringFrom(c.getCategories()); folder = c.getFolder(); if (personalDetail != null) { anniversary = personalDetail.getAnniversary(); birthday = personalDetail.getBirthday(); children = personalDetail.getChildren(); spouse = personalDetail.getSpouse(); hobbies = personalDetail.getHobbies(); gender = personalDetail.getGender(); } if (businessDetail != null) { assistant = businessDetail.getAssistant(); manager = businessDetail.getManager(); officeLocation = businessDetail.getOfficeLocation(); company = Property.stringFrom(businessDetail.getCompany()); department = Property.stringFrom(businessDetail.getDepartment()); role = Property.stringFrom(businessDetail.getRole()); } if (name != null) { firstName = Property.stringFrom(name.getFirstName()); middleName = Property.stringFrom(name.getMiddleName()); lastName = Property.stringFrom(name.getLastName()); displayName = Property.stringFrom(name.getDisplayName()); initials = Property.stringFrom(name.getInitials()); nickName = Property.stringFrom(name.getNickname()); suffix = Property.stringFrom(name.getSuffix()); salutation = Property.stringFrom(name.getSalutation()); } ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT); // // GENERAL // if (log.isTraceEnabled()) { log.trace("Preparing statement with ID " + id); } ps.setLong(1, id); if (log.isTraceEnabled()) { log.trace("Preparing statement with user ID " + userId); } ps.setString(2, userId); ps.setLong(3, lastUpdate.getTime()); ps.setString(4, String.valueOf(Def.PIM_STATE_NEW)); boolean hasPhoto = false; Photo photo = personalDetail.getPhotoObject(); if (photo != null && (photo.getImage() != null || photo.getUrl() != null)) { hasPhoto = true; ps.setShort(5, photo.getImage() != null ? ContactWrapper.PHOTO_IMAGE : ContactWrapper.PHOTO_URL); } else if (photo != null) { ps.setShort(5, ContactWrapper.EMPTY_PHOTO); } else { ps.setNull(5, Types.SMALLINT); } // // CONTACT DETAILS // if (importance != null) { ps.setShort(6, importance.shortValue()); } else { ps.setNull(6, Types.SMALLINT); } if (sensitivity != null) { ps.setShort(7, sensitivity.shortValue()); } else { ps.setNull(7, Types.SMALLINT); } ps.setString(8, StringUtils.left(subject, SQL_SUBJECT_DIM)); ps.setString(9, StringUtils.left(folder, SQL_FOLDER_DIM)); // // PERSONAL DETAILS // ps.setString(10, StringUtils.left(anniversary, SQL_ANNIVERSARY_DIM)); ps.setString(11, StringUtils.left(firstName, SQL_FIRSTNAME_DIM)); ps.setString(12, StringUtils.left(middleName, SQL_MIDDLENAME_DIM)); ps.setString(13, StringUtils.left(lastName, SQL_LASTNAME_DIM)); ps.setString(14, StringUtils.left(displayName, SQL_DISPLAYNAME_DIM)); ps.setString(15, StringUtils.left(birthday, SQL_BIRTHDAY_DIM)); if (c.getNotes() != null && c.getNotes().size() > 0) { String noteValue = ((Note) c.getNotes().get(0)).getPropertyValueAsString(); ps.setString(16, StringUtils.left(noteValue, SQL_NOTE_DIM)); } else { ps.setString(16, null); } ps.setString(17, StringUtils.left(categories, SQL_CATEGORIES_DIM)); ps.setString(18, StringUtils.left(children, SQL_CHILDREN_DIM)); ps.setString(19, StringUtils.left(hobbies, SQL_HOBBIES_DIM)); ps.setString(20, StringUtils.left(initials, SQL_INITIALS_DIM)); ps.setString(21, StringUtils.left(languages, SQL_LANGUAGES_DIM)); ps.setString(22, StringUtils.left(nickName, SQL_NICKNAME_DIM)); ps.setString(23, StringUtils.left(spouse, SQL_SPOUSE_DIM)); ps.setString(24, StringUtils.left(suffix, SQL_SUFFIX_DIM)); ps.setString(25, StringUtils.left(salutation, SQL_SALUTATION_DIM)); // // BUSINESS DETAILS // ps.setString(26, StringUtils.left(assistant, SQL_ASSISTANT_DIM)); ps.setString(27, StringUtils.left(company, SQL_COMPANY_DIM)); ps.setString(28, StringUtils.left(department, SQL_DEPARTMENT_DIM)); if (businessDetail.getTitles() != null && businessDetail.getTitles().size() > 0) { String titleValue = ((Title) businessDetail.getTitles().get(0)).getPropertyValueAsString(); ps.setString(29, StringUtils.left(titleValue, SQL_TITLE_DIM)); } else { ps.setString(29, null); } ps.setString(30, StringUtils.left(manager, SQL_MANAGER_DIM)); if (mileage != null && mileage.length() > SQL_MILEAGE_DIM) { mileage = mileage.substring(0, SQL_MILEAGE_DIM); } ps.setString(31, StringUtils.left(mileage, SQL_MILEAGE_DIM)); ps.setString(32, StringUtils.left(officeLocation, SQL_OFFICELOCATION_DIM)); ps.setString(33, StringUtils.left(role, SQL_ROLE_DIM)); ps.setString(34, StringUtils.left(companies, SQL_COMPANIES_DIM)); ps.setString(35, StringUtils.left(gender, SQL_GENDER_DIM)); ps.executeUpdate(); DBTools.close(null, ps, null); // // emails // if (!emails.isEmpty()) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = emails.size(); i < l; i++) { email = emails.get(i); type = getContactEmailItemTypeFromEmailPropertyType(email.getEmailType()); // Unknown property: saves nothing if (TYPE_UNDEFINED == type) continue; String emailValue = email.getPropertyValueAsString(); if (emailValue != null && emailValue.length() != 0) { if (emailValue.length() > SQL_EMAIL_DIM) { emailValue = emailValue.substring(0, SQL_EMAIL_DIM); } ps.setLong(1, id); ps.setInt(2, type); ps.setString(3, emailValue); ps.executeUpdate(); } } DBTools.close(null, ps, null); } // // phones // if (!phones.isEmpty()) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = phones.size(); i < l; i++) { phone = phones.get(i); type = getContactPhoneItemTypeFromPhonePropertyType(phone.getPhoneType()); // Unknown property: saves nothing if (TYPE_UNDEFINED == type) continue; String phoneValue = phone.getPropertyValueAsString(); if (phoneValue != null && phoneValue.length() != 0) { if (phoneValue.length() > SQL_PHONE_DIM) { phoneValue = phoneValue.substring(0, SQL_PHONE_DIM); } ps.setLong(1, id); ps.setInt(2, type); ps.setString(3, phoneValue); ps.executeUpdate(); } } DBTools.close(null, ps, null); } // // webPages // if (!webPages.isEmpty()) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = webPages.size(); i < l; i++) { webPage = webPages.get(i); webPageType = webPage.getWebPageType(); if ((FIELD_WEB_PAGE).equals(webPageType)) { type = TYPE_WEB_PAGE; } else if ((FIELD_HOME_WEB_PAGE).equals(webPageType)) { type = TYPE_HOME_WEB_PAGE; } else if ((FIELD_BUSINESS_WEB_PAGE).equals(webPageType)) { type = TYPE_BUSINESS_WEB_PAGE; } else { // // Unknown property: saves nothing // continue; } String webPageValue = webPage.getPropertyValueAsString(); if (webPageValue != null && webPageValue.length() != 0) { if (webPageValue.length() > SQL_WEBPAGE_DIM) { webPageValue = webPageValue.substring(0, SQL_WEBPAGE_DIM); } ps.setLong(1, id); ps.setInt(2, type); ps.setString(3, webPageValue); ps.executeUpdate(); } } DBTools.close(null, ps, null); } if (homeAddressBook != null) { String homeStreet = Property.stringFrom(homeAddressBook.getStreet()); String homeCity = Property.stringFrom(homeAddressBook.getCity()); String homePostalCode = Property.stringFrom(homeAddressBook.getPostalCode()); String homeState = Property.stringFrom(homeAddressBook.getState()); String homeCountry = Property.stringFrom(homeAddressBook.getCountry()); String homePostalOfficeAddress = Property.stringFrom(homeAddressBook.getPostOfficeAddress()); String homeExtendedAddress = Property.stringFrom(homeAddressBook.getExtendedAddress()); String homeLabel = Property.stringFrom(homeAddressBook.getLabel()); if (homeLabel != null) { String[] label = { homeLabel, FIELD_HOME_LABEL }; labels.add(label); } String[] homeAddressFields = { homeStreet, homeCity, homePostalCode, homeCountry, homeState, homePostalOfficeAddress, homeExtendedAddress }; if (!hasOnlyEmptyOrNullContent(homeAddressFields)) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_ADDRESS); ps.setLong(1, id); ps.setInt(2, ADDRESS_TYPE_HOME); ps.setString(3, replaceNewLine(StringUtils.left(homeStreet, SQL_STREET_DIM))); ps.setString(4, StringUtils.left(homeCity, SQL_CITY_DIM)); ps.setString(5, StringUtils.left(homeState, SQL_STATE_DIM)); ps.setString(6, StringUtils.left(homePostalCode, SQL_POSTALCODE_DIM)); ps.setString(7, StringUtils.left(homeCountry, SQL_COUNTRY_DIM)); ps.setString(8, StringUtils.left(homePostalOfficeAddress, SQL_POSTALOFFICEADDRESS_DIM)); ps.setString(9, StringUtils.left(homeExtendedAddress, SQL_EXTENDEDADDRESS_DIM)); ps.executeUpdate(); DBTools.close(null, ps, null); } } if (otherAddressBook != null) { String otherStreet = Property.stringFrom(otherAddressBook.getStreet()); String otherCity = Property.stringFrom(otherAddressBook.getCity()); String otherPostalCode = Property.stringFrom(otherAddressBook.getPostalCode()); String otherState = Property.stringFrom(otherAddressBook.getState()); String otherCountry = Property.stringFrom(otherAddressBook.getCountry()); String otherPostalOfficeAddress = Property.stringFrom(otherAddressBook.getPostOfficeAddress()); String otherExtendedAddress = Property.stringFrom(otherAddressBook.getExtendedAddress()); String otherLabel = Property.stringFrom(otherAddressBook.getLabel()); if (otherLabel != null) { String[] label = { otherLabel, FIELD_OTHER_LABEL }; labels.add(label); } String[] otherAddressFields = { otherStreet, otherCity, otherPostalCode, otherCountry, otherState, otherPostalOfficeAddress, otherExtendedAddress }; if (!hasOnlyEmptyOrNullContent(otherAddressFields)) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_ADDRESS); ps.setLong(1, id); ps.setInt(2, ADDRESS_TYPE_OTHER); ps.setString(3, replaceNewLine(StringUtils.left(otherStreet, SQL_STREET_DIM))); ps.setString(4, StringUtils.left(otherCity, SQL_CITY_DIM)); ps.setString(5, StringUtils.left(otherState, SQL_STATE_DIM)); ps.setString(6, StringUtils.left(otherPostalCode, SQL_POSTALCODE_DIM)); ps.setString(7, StringUtils.left(otherCountry, SQL_COUNTRY_DIM)); ps.setString(8, StringUtils.left(otherPostalOfficeAddress, SQL_POSTALOFFICEADDRESS_DIM)); ps.setString(9, StringUtils.left(otherExtendedAddress, SQL_EXTENDEDADDRESS_DIM)); ps.executeUpdate(); DBTools.close(null, ps, null); } } if (workAddressBook != null) { String workStreet = Property.stringFrom(workAddressBook.getStreet()); String workCity = Property.stringFrom(workAddressBook.getCity()); String workPostalCode = Property.stringFrom(workAddressBook.getPostalCode()); String workState = Property.stringFrom(workAddressBook.getState()); String workCountry = Property.stringFrom(workAddressBook.getCountry()); String workPostalOfficeAddress = Property.stringFrom(workAddressBook.getPostOfficeAddress()); String workExtendedAddress = Property.stringFrom(workAddressBook.getExtendedAddress()); String workLabel = Property.stringFrom(workAddressBook.getLabel()); if (workLabel != null) { String[] label = { workLabel, FIELD_BUSINESS_LABEL }; labels.add(label); } String[] workAddressFields = { workStreet, workCity, workPostalCode, workCountry, workState, workPostalOfficeAddress, workExtendedAddress }; if (!hasOnlyEmptyOrNullContent(workAddressFields)) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_ADDRESS); ps.setLong(1, id); ps.setInt(2, ADDRESS_TYPE_WORK); ps.setString(3, replaceNewLine(StringUtils.left(workStreet, SQL_STREET_DIM))); ps.setString(4, StringUtils.left(workCity, SQL_CITY_DIM)); ps.setString(5, StringUtils.left(workState, SQL_STATE_DIM)); ps.setString(6, StringUtils.left(workPostalCode, SQL_POSTALCODE_DIM)); ps.setString(7, StringUtils.left(workCountry, SQL_COUNTRY_DIM)); ps.setString(8, StringUtils.left(workPostalOfficeAddress, SQL_POSTALOFFICEADDRESS_DIM)); ps.setString(9, StringUtils.left(workExtendedAddress, SQL_EXTENDEDADDRESS_DIM)); ps.executeUpdate(); DBTools.close(null, ps, null); } } // // labels // if (!labels.isEmpty()) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = labels.size(); i < l; i++) { String[] label = labels.get(i); String labelType = label[1]; if ((FIELD_HOME_LABEL).equals(labelType)) { type = TYPE_HOME_LABEL; } else if ((FIELD_BUSINESS_LABEL).equals(labelType)) { type = TYPE_BUSINESS_LABEL; } else if ((FIELD_OTHER_LABEL).equals(labelType)) { type = TYPE_OTHER_LABEL; } else { // // Unknown property: saves nothing // continue; } String labelValue = label[0]; if (labelValue != null && labelValue.length() != 0) { if (labelValue.length() > SQL_LABEL_DIM) { labelValue = labelValue.substring(0, SQL_LABEL_DIM); } ps.setLong(1, id); ps.setInt(2, type); ps.setString(3, labelValue); ps.executeUpdate(); } } DBTools.close(null, ps, null); } if (hasPhoto) { insertPhoto(con, Long.parseLong(cw.getId()), photo); } } catch (Exception e) { throw new DAOException("Error adding contact.", e); } finally { DBTools.close(con, ps, null); } if (log.isTraceEnabled()) { log.trace("Added item with ID '" + id + "'"); } }