List of usage examples for java.sql PreparedStatement setNull
void setNull(int parameterIndex, int sqlType) throws SQLException;
NULL. From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * Creates a group assignment/*w ww. j a va2s . co m*/ * * @param con a valid and open connection * @param sql an instance of StringBuilder * @param group an instance of the FxGroupAssignment to be persisted * @param createSubAssignments if true calls createGroupAssignment is called recursively to create sub assignments * @return returns the assignmentId * @throws FxApplicationException on errors */ private long createGroupAssignment(Connection con, StringBuilder sql, FxGroupAssignmentEdit group, boolean createSubAssignments) throws FxApplicationException { if (!group.isNew()) throw new FxInvalidParameterException("ex.structure.assignment.create.existing", group.getXPath()); if (sql == null) { sql = new StringBuilder(1000); } PreparedStatement ps = null; long newAssignmentId; try { FxGroupAssignment thisGroupAssignment; String XPath; if (!group.getXPath().startsWith(group.getAssignedType().getName())) { if (group.getAlias() != null) XPath = XPathElement.buildXPath(false, group.getAssignedType().getName(), XPathElement.stripType(group.getXPath())); else XPath = "/"; } else XPath = group.getXPath(); if (group.getAlias() != null) { sql.setLength(0); sql.append("INSERT INTO ").append(TBL_STRUCT_ASSIGNMENTS). // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 append("(ID,ATYPE,ENABLED,TYPEDEF,MINMULT,MAXMULT,DEFMULT,POS,XPATH,XALIAS,BASE,PARENTGROUP,AGROUP,SYSINTERNAL,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, group.isEnabled()); ps.setLong(4, group.getAssignedType().getId()); ps.setInt(5, group.getMultiplicity().getMin()); ps.setInt(6, group.getMultiplicity().getMax()); ps.setInt(7, group.getDefaultMultiplicity()); int position = getValidPosition(con, sql, group.getPosition(), group.getAssignedType().getId(), group.getParentGroupAssignment()); ps.setInt(8, position); ps.setString(9, XPath); ps.setString(10, group.getAlias()); if (group.getBaseAssignmentId() == FxAssignment.NO_BASE) ps.setNull(11, java.sql.Types.NUMERIC); else ps.setLong(11, group.getBaseAssignmentId()); ps.setLong(12, group.getParentGroupAssignment() == null ? FxAssignment.NO_PARENT : group.getParentGroupAssignment().getId()); ps.setLong(13, group.getGroup().getId()); ps.setBoolean(14, group.isSystemInternal()); ps.setInt(15, group.getMode().getId()); ps.executeUpdate(); ps.close(); Database.storeFxString(new FxString[] { group.getLabel(), group.getHint() }, con, TBL_STRUCT_ASSIGNMENTS, new String[] { "DESCRIPTION", "HINT" }, "ID", newAssignmentId); thisGroupAssignment = new FxGroupAssignment(newAssignmentId, true, group.getAssignedType(), group.getAlias(), XPath, position, group.getMultiplicity(), group.getDefaultMultiplicity(), group.getParentGroupAssignment(), group.getBaseAssignmentId(), group.getLabel(), group.getHint(), group.getGroup(), group.getMode(), null); setAssignmentPosition(con, newAssignmentId, group.getPosition()); } else { thisGroupAssignment = null; newAssignmentId = FxAssignment.NO_PARENT; } htracker.track(group.getAssignedType(), "history.assignment.createGroupAssignment", XPath, group.getAssignedType().getId(), group.getAssignedType().getName(), group.getGroup().getId(), group.getGroup().getName()); // FxStructureOption inheritance boolean isInheritedAssignment = FxSharedUtils.checkAssignmentInherited(group); if (isInheritedAssignment) { // FxStructureOptions - retrieve only those with an activated "isInherited" flag final List<FxStructureOption> inheritedOpts = FxStructureOption.cloneOptions(group.getOptions(), true); if (inheritedOpts.size() > 0) { storeOptions(con, TBL_STRUCT_GROUP_OPTIONS, "ID", group.getGroup().getId(), newAssignmentId, inheritedOpts); } } else { storeOptions(con, TBL_STRUCT_GROUP_OPTIONS, "ID", group.getGroup().getId(), newAssignmentId, group.getOptions()); } if (group.getBaseAssignmentId() > 0 && createSubAssignments) { FxGroupAssignment baseGroup = (FxGroupAssignment) CacheAdmin.getEnvironment() .getAssignment(group.getBaseAssignmentId()); for (FxGroupAssignment ga : baseGroup.getAssignedGroups()) { FxGroupAssignmentEdit gae = new FxGroupAssignmentEdit(ga); gae.setEnabled(group.isEnabled()); createGroupAssignment(con, sql, FxGroupAssignmentEdit.createNew(gae, group.getAssignedType(), ga.getAlias(), XPath, thisGroupAssignment), createSubAssignments); } for (FxPropertyAssignment pa : baseGroup.getAssignedProperties()) { FxPropertyAssignmentEdit pae = new FxPropertyAssignmentEdit(pa); pae.setEnabled(group.isEnabled()); createPropertyAssignment(con, sql, FxPropertyAssignmentEdit.createNew(pae, group.getAssignedType(), pa.getAlias(), XPath, thisGroupAssignment)); } } try { StructureLoader.reloadAssignments(FxContext.get().getDivisionId()); } catch (FxApplicationException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e, "ex.cache", e.getMessage()); } if (group.getAssignedType().getId() != FxType.ROOT_ID) createInheritedAssignments(CacheAdmin.getEnvironment().getAssignment(newAssignmentId), con, sql, group.getAssignedType().getDerivedTypes()); } catch (SQLException e) { final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(e); EJBUtils.rollback(ctx); if (uniqueConstraintViolation) throw new FxEntryExistsException("ex.structure.assignment.group.exists", group.getAlias(), group.getAssignedType().getName() + group.getXPath()); throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } catch (FxNotFoundException e) { throw new FxCreateException(e); } finally { Database.closeObjects(AssignmentEngineBean.class, null, ps); } return newAssignmentId; }
From source file:com.flexive.core.storage.GenericDivisionImporter.java
/** * Import flat storages to the hierarchical storage * * @param con an open and valid connection to store imported data * @param zip zip file containing the data * @throws Exception on errors/*from ww w . j a v a2 s.c o m*/ */ protected void importFlatStoragesHierarchical(Connection con, ZipFile zip) throws Exception { //mapping: storage->level->columnname->assignment id final Map<String, Map<Integer, Map<String, Long>>> flatAssignmentMapping = new HashMap<String, Map<Integer, Map<String, Long>>>( 5); //mapping: assignment id->position index final Map<Long, Integer> assignmentPositions = new HashMap<Long, Integer>(100); //mapping: flatstorage->column sizes [string,bigint,double,select,text] final Map<String, Integer[]> flatstoragesColumns = new HashMap<String, Integer[]>(5); ZipEntry zeMeta = getZipEntry(zip, FILE_FLATSTORAGE_META); DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder(); Document document = builder.parse(zip.getInputStream(zeMeta)); XPath xPath = XPathFactory.newInstance().newXPath(); //calculate column sizes NodeList nodes = (NodeList) xPath.evaluate("/flatstorageMeta/storageMeta", document, XPathConstants.NODESET); Node currNode; for (int i = 0; i < nodes.getLength(); i++) { currNode = nodes.item(i); int cbigInt = Integer.parseInt(currNode.getAttributes().getNamedItem("bigInt").getNodeValue()); int cdouble = Integer.parseInt(currNode.getAttributes().getNamedItem("double").getNodeValue()); int cselect = Integer.parseInt(currNode.getAttributes().getNamedItem("select").getNodeValue()); int cstring = Integer.parseInt(currNode.getAttributes().getNamedItem("string").getNodeValue()); int ctext = Integer.parseInt(currNode.getAttributes().getNamedItem("text").getNodeValue()); String tableName = null; if (currNode.hasChildNodes()) { for (int j = 0; j < currNode.getChildNodes().getLength(); j++) if (currNode.getChildNodes().item(j).getNodeName().equals("name")) { tableName = currNode.getChildNodes().item(j).getTextContent(); } } if (tableName != null) { flatstoragesColumns.put(tableName, new Integer[] { cstring, cbigInt, cdouble, cselect, ctext }); } } //parse mappings nodes = (NodeList) xPath.evaluate("/flatstorageMeta/mapping", document, XPathConstants.NODESET); for (int i = 0; i < nodes.getLength(); i++) { currNode = nodes.item(i); long assignment = Long.valueOf(currNode.getAttributes().getNamedItem("assid").getNodeValue()); int level = Integer.valueOf(currNode.getAttributes().getNamedItem("lvl").getNodeValue()); String storage = null; String columnname = null; final NodeList childNodes = currNode.getChildNodes(); for (int c = 0; c < childNodes.getLength(); c++) { Node child = childNodes.item(c); if ("tblname".equals(child.getNodeName())) storage = child.getTextContent(); else if ("colname".equals(child.getNodeName())) columnname = child.getTextContent(); } if (storage == null || columnname == null) throw new Exception("Invalid flatstorage export: could not read storage or column name!"); if (!flatAssignmentMapping.containsKey(storage)) flatAssignmentMapping.put(storage, new HashMap<Integer, Map<String, Long>>(20)); Map<Integer, Map<String, Long>> levelMap = flatAssignmentMapping.get(storage); if (!levelMap.containsKey(level)) levelMap.put(level, new HashMap<String, Long>(30)); Map<String, Long> columnMap = levelMap.get(level); if (!columnMap.containsKey(columnname)) columnMap.put(columnname, assignment); //calculate position assignmentPositions.put(assignment, getAssignmentPosition(flatstoragesColumns.get(storage), columnname)); } if (flatAssignmentMapping.size() == 0) { LOG.warn("No flatstorage assignments found to process!"); return; } ZipEntry zeData = getZipEntry(zip, FILE_DATA_FLAT); final String xpathStorage = "flatstorages/storage"; final String xpathData = "flatstorages/storage/data"; final PreparedStatement psGetAssInfo = con.prepareStatement( "SELECT DISTINCT a.APROPERTY,a.XALIAS,p.DATATYPE FROM " + DatabaseConst.TBL_STRUCT_ASSIGNMENTS + " a, " + DatabaseConst.TBL_STRUCT_PROPERTIES + " p WHERE a.ID=? AND p.ID=a.APROPERTY"); final Map<Long, Object[]> assignmentPropAlias = new HashMap<Long, Object[]>(assignmentPositions.size()); final String insert1 = "INSERT INTO " + DatabaseConst.TBL_CONTENT_DATA + //1 2 3 4 5 6 =1 =1 =1 =1 7 8 9 "(ID,VER,POS,LANG,TPROP,ASSIGN,XDEPTH,XMULT,XINDEX,PARENTXMULT,ISMAX_VER,ISLIVE_VER,ISMLDEF,"; final String insert2 = "(?,?,?,?,1,?,?,1,1,1,?,?,?,"; final PreparedStatement psString = con .prepareStatement(insert1 + "FTEXT1024,UFTEXT1024,FSELECT,FINT)VALUES" + insert2 + "?,?,0,?)"); final PreparedStatement psText = con .prepareStatement(insert1 + "FCLOB,UFCLOB,FSELECT,FINT)VALUES" + insert2 + "?,?,0,?)"); final PreparedStatement psDouble = con .prepareStatement(insert1 + "FDOUBLE,FSELECT,FINT)VALUES" + insert2 + "?,0,?)"); final PreparedStatement psNumber = con .prepareStatement(insert1 + "FINT,FSELECT,FBIGINT)VALUES" + insert2 + "?,0,?)"); final PreparedStatement psLargeNumber = con .prepareStatement(insert1 + "FBIGINT,FSELECT,FINT)VALUES" + insert2 + "?,0,?)"); final PreparedStatement psFloat = con .prepareStatement(insert1 + "FFLOAT,FSELECT,FINT)VALUES" + insert2 + "?,0,?)"); final PreparedStatement psBoolean = con .prepareStatement(insert1 + "FBOOL,FSELECT,FINT)VALUES" + insert2 + "?,0,?)"); final PreparedStatement psReference = con .prepareStatement(insert1 + "FREF,FSELECT,FINT)VALUES" + insert2 + "?,0,?)"); final PreparedStatement psSelectOne = con .prepareStatement(insert1 + "FSELECT,FINT)VALUES" + insert2 + "?,?)"); try { final SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); final DefaultHandler handler = new DefaultHandler() { private String currentElement = null; private String currentStorage = null; private Map<String, String> data = new HashMap<String, String>(10); private StringBuilder sbData = new StringBuilder(10000); boolean inTag = false; boolean inElement = false; List<String> path = new ArrayList<String>(10); StringBuilder currPath = new StringBuilder(100); int insertCount = 0; /** * {@inheritDoc} */ @Override public void startDocument() throws SAXException { inTag = false; inElement = false; path.clear(); currPath.setLength(0); sbData.setLength(0); data.clear(); currentElement = null; currentStorage = null; insertCount = 0; } /** * {@inheritDoc} */ @Override public void endDocument() throws SAXException { LOG.info("Imported [" + insertCount + "] flatstorage entries into the hierarchical storage"); } /** * {@inheritDoc} */ @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { pushPath(qName, attributes); if (currPath.toString().equals(xpathData)) { inTag = true; data.clear(); for (int i = 0; i < attributes.getLength(); i++) { String name = attributes.getLocalName(i); if (StringUtils.isEmpty(name)) name = attributes.getQName(i); data.put(name, attributes.getValue(i)); } } else if (currPath.toString().equals(xpathStorage)) { currentStorage = attributes.getValue("name"); LOG.info("Processing storage: " + currentStorage); } else { currentElement = qName; } inElement = true; sbData.setLength(0); } /** * Push a path element from the stack * * @param qName element name to push * @param att attributes */ @SuppressWarnings({ "UnusedDeclaration" }) private void pushPath(String qName, Attributes att) { path.add(qName); buildPath(); } /** * Pop the top path element from the stack */ private void popPath() { path.remove(path.size() - 1); buildPath(); } /** * Rebuild the current path */ private synchronized void buildPath() { currPath.setLength(0); for (String s : path) currPath.append(s).append('/'); if (currPath.length() > 1) currPath.delete(currPath.length() - 1, currPath.length()); // System.out.println("currPath: " + currPath); } /** * {@inheritDoc} */ @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (currPath.toString().equals(xpathData)) { // LOG.info("Insert [" + xpathData + "]: [" + data + "]"); inTag = false; processData(); /*try { if (insertMode) { if (executeInsertPhase) { processColumnSet(insertColumns, psInsert); counter += psInsert.executeUpdate(); } } else { if (executeUpdatePhase) { if (processColumnSet(updateSetColumns, psUpdate)) { processColumnSet(updateClauseColumns, psUpdate); counter += psUpdate.executeUpdate(); } } } } catch (SQLException e) { throw new SAXException(e); } catch (ParseException e) { throw new SAXException(e); }*/ } else { if (inTag) { data.put(currentElement, sbData.toString()); } currentElement = null; } popPath(); inElement = false; sbData.setLength(0); } void processData() { // System.out.println("processing " + currentStorage + " -> " + data); final String[] cols = { "string", "bigint", "double", "select", "text" }; for (String column : data.keySet()) { if (column.endsWith("_mld")) continue; for (String check : cols) { if (column.startsWith(check)) { if ("select".equals(check) && "0".equals(data.get(column))) continue; //dont insert 0-referencing selects try { insertData(column); } catch (SQLException e) { //noinspection ThrowableInstanceNeverThrown throw new FxDbException(e, "ex.db.sqlError", e.getMessage()) .asRuntimeException(); } } } } } private void insertData(String column) throws SQLException { final int level = Integer.parseInt(data.get("lvl")); long assignment = flatAssignmentMapping.get(currentStorage).get(level) .get(column.toUpperCase()); int pos = FxArrayUtils.getIntElementAt(data.get("positions"), ',', assignmentPositions.get(assignment)); String _valueData = data.get("valuedata"); Integer valueData = _valueData == null ? null : FxArrayUtils.getHexIntElementAt(data.get("valuedata"), ',', assignmentPositions.get(assignment)); Object[] propXP = getPropertyXPathDataType(assignment); long prop = (Long) propXP[0]; String xpath = (String) propXP[1]; FxDataType dataType; try { dataType = FxDataType.getById((Long) propXP[2]); } catch (FxNotFoundException e) { throw e.asRuntimeException(); } long id = Long.parseLong(data.get("id")); int ver = Integer.parseInt(data.get("ver")); long lang = Integer.parseInt(data.get("lang")); boolean isMaxVer = "1".equals(data.get("ismax_ver")); boolean isLiveVer = "1".equals(data.get("islive_ver")); boolean mlDef = "1".equals(data.get(column + "_mld")); PreparedStatement ps; int vdPos; switch (dataType) { case String1024: ps = psString; ps.setString(10, data.get(column)); ps.setString(11, data.get(column).toUpperCase()); vdPos = 12; break; case Text: case HTML: ps = psText; ps.setString(10, data.get(column)); ps.setString(11, data.get(column).toUpperCase()); vdPos = 12; break; case Number: ps = psNumber; ps.setLong(10, Long.valueOf(data.get(column))); vdPos = 11; break; case LargeNumber: ps = psLargeNumber; ps.setLong(10, Long.valueOf(data.get(column))); vdPos = 11; break; case Reference: ps = psReference; ps.setLong(10, Long.valueOf(data.get(column))); vdPos = 11; break; case Float: ps = psFloat; ps.setFloat(10, Float.valueOf(data.get(column))); vdPos = 11; break; case Double: ps = psDouble; ps.setDouble(10, Double.valueOf(data.get(column))); vdPos = 11; break; case Boolean: ps = psBoolean; ps.setBoolean(10, "1".equals(data.get(column))); vdPos = 11; break; case SelectOne: ps = psSelectOne; ps.setLong(10, Long.valueOf(data.get(column))); vdPos = 11; break; default: //noinspection ThrowableInstanceNeverThrown throw new FxInvalidParameterException("assignment", "ex.structure.flatstorage.datatype.unsupported", dataType.name()) .asRuntimeException(); } ps.setLong(1, id); ps.setInt(2, ver); ps.setInt(3, pos); ps.setLong(4, lang); ps.setLong(5, prop); ps.setLong(6, assignment); ps.setBoolean(7, isMaxVer); ps.setBoolean(8, isLiveVer); ps.setBoolean(9, mlDef); if (valueData == null) ps.setNull(vdPos, java.sql.Types.NUMERIC); else ps.setInt(vdPos, valueData); ps.executeUpdate(); insertCount++; } /** * Get property id, xpath and data type for an assignment * * @param assignment assignment id * @return Object[] {propertyId, xpath, datatype} */ private Object[] getPropertyXPathDataType(long assignment) { if (assignmentPropAlias.get(assignment) != null) return assignmentPropAlias.get(assignment); try { psGetAssInfo.setLong(1, assignment); ResultSet rs = psGetAssInfo.executeQuery(); if (rs != null && rs.next()) { Object[] data = new Object[] { rs.getLong(1), rs.getString(2), rs.getLong(3) }; assignmentPropAlias.put(assignment, data); return data; } } catch (SQLException e) { throw new IllegalArgumentException( "Could not load data for assignment " + assignment + ": " + e.getMessage()); } throw new IllegalArgumentException("Could not load data for assignment " + assignment + "!"); } /** * {@inheritDoc} */ @Override public void characters(char[] ch, int start, int length) throws SAXException { if (inElement) sbData.append(ch, start, length); } }; parser.parse(zip.getInputStream(zeData), handler); } finally { Database.closeObjects(GenericDivisionImporter.class, psGetAssInfo, psString, psBoolean, psDouble, psFloat, psLargeNumber, psNumber, psReference, psSelectOne, psText); } }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Create a new main entry//from w w w . ja v a2 s .co m * * @param con an open and valid connection * @param newId the id to use * @param version the version to use * @param content content to create * @return primary key of the created content * @throws FxCreateException on errors */ protected FxPK createMainEntry(Connection con, long newId, int version, FxContent content, boolean keepCreationLCI) throws FxCreateException { PreparedStatement ps = null; FxPK pk = new FxPK(newId, version); try { ps = con.prepareStatement(CONTENT_MAIN_INSERT); ps.setLong(1, newId); ps.setInt(2, version); ps.setLong(3, content.getTypeId()); ps.setLong(4, content.getAclIds().size() > 1 ? ACL.NULL_ACL_ID : content.getAclIds().get(0)); ps.setLong(5, content.getStepId()); ps.setInt(6, 1); //if creating a new version, max_ver will be fixed in a later step ps.setInt(7, content.isLiveVersion() ? 1 : 0); ps.setBoolean(8, content.isMaxVersion()); ps.setBoolean(9, content.isLiveVersion()); ps.setBoolean(10, content.isActive()); ps.setInt(11, (int) content.getMainLanguage()); if (content.isRelation()) { ps.setLong(12, content.getRelatedSource().getId()); ps.setInt(13, content.getRelatedSource().getVersion()); ps.setLong(14, content.getRelatedDestination().getId()); ps.setInt(15, content.getRelatedDestination().getVersion()); ps.setLong(16, content.getRelatedSourcePosition()); ps.setLong(17, content.getRelatedDestinationPosition()); } else { ps.setNull(12, java.sql.Types.NUMERIC); ps.setNull(13, java.sql.Types.NUMERIC); ps.setNull(14, java.sql.Types.NUMERIC); ps.setNull(15, java.sql.Types.NUMERIC); ps.setNull(16, java.sql.Types.NUMERIC); ps.setNull(17, java.sql.Types.NUMERIC); } if (!content.isForceLifeCycle()) { final long userId = FxContext.getUserTicket().getUserId(); final long now = System.currentTimeMillis(); if (keepCreationLCI) { // keep created_at of existing content ps.setLong(18, content.getValue(FxLargeNumber.class, "/CREATED_BY").getBestTranslation()); ps.setLong(19, content.getValue(FxDateTime.class, "/CREATED_AT").getBestTranslation().getTime()); } else { ps.setLong(18, userId); ps.setLong(19, now); } ps.setLong(20, userId); ps.setLong(21, now); } else { ps.setLong(18, content.getValue(FxLargeNumber.class, "/CREATED_BY").getBestTranslation()); ps.setLong(19, content.getValue(FxDateTime.class, "/CREATED_AT").getBestTranslation().getTime()); ps.setLong(20, content.getValue(FxLargeNumber.class, "/MODIFIED_BY").getBestTranslation()); ps.setLong(21, content.getValue(FxDateTime.class, "/MODIFIED_AT").getBestTranslation().getTime()); } ps.setLong(22, content.getMandatorId()); final String groupPositions = getGroupPositions(content); if (groupPositions != null) { StorageManager.getStorageImpl().setBigString(ps, 23, groupPositions); } else { ps.setNull(23, Types.CLOB); } ps.executeUpdate(); updateACLEntries(con, content, pk, true); } catch (SQLException e) { throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } catch (FxUpdateException e) { throw new FxCreateException(e); } finally { Database.closeObjects(GenericHierarchicalStorage.class, ps); } return pk; }
From source file:org.jamwiki.db.AnsiQueryHandler.java
/** * *//* w ww.ja v a 2 s .c o m*/ public void insertWikiFileVersion(WikiFileVersion wikiFileVersion, Connection conn) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; try { int index = 1; if (!this.autoIncrementPrimaryKeys()) { stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_FILE_VERSION); int fileVersionId = this.nextWikiFileVersionId(conn); wikiFileVersion.setFileVersionId(fileVersionId); stmt.setInt(index++, wikiFileVersion.getFileVersionId()); } else { stmt = conn.prepareStatement(STATEMENT_INSERT_WIKI_FILE_VERSION_AUTO_INCREMENT, Statement.RETURN_GENERATED_KEYS); } if (wikiFileVersion.getUploadDate() == null) { Timestamp uploadDate = new Timestamp(System.currentTimeMillis()); wikiFileVersion.setUploadDate(uploadDate); } stmt.setInt(index++, wikiFileVersion.getFileId()); stmt.setString(index++, wikiFileVersion.getUploadComment()); stmt.setString(index++, wikiFileVersion.getUrl()); if (wikiFileVersion.getAuthorId() == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, wikiFileVersion.getAuthorId()); } stmt.setString(index++, wikiFileVersion.getAuthorDisplay()); stmt.setTimestamp(index++, wikiFileVersion.getUploadDate()); stmt.setString(index++, wikiFileVersion.getMimeType()); stmt.setLong(index++, wikiFileVersion.getFileSize()); stmt.executeUpdate(); if (this.autoIncrementPrimaryKeys()) { rs = stmt.getGeneratedKeys(); if (!rs.next()) { throw new SQLException("Unable to determine auto-generated ID for database record"); } wikiFileVersion.setFileVersionId(rs.getInt(1)); } } finally { // close only the statement and result set - leave the connection open for further use DatabaseConnection.closeConnection(null, stmt, rs); } }
From source file:fll.web.api.SubjectiveScoresServlet.java
@SuppressFBWarnings(value = {
"SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "columns and category are dynamic")
@Override/*www . jav a 2s. c o m*/
protected final void doPost(final HttpServletRequest request, final HttpServletResponse response)
throws IOException, ServletException {
int numModified = 0;
final ObjectMapper jsonMapper = new ObjectMapper();
final ServletContext application = getServletContext();
final ChallengeDescription challengeDescription = ApplicationAttributes
.getChallengeDescription(application);
Connection connection = null;
PreparedStatement deletePrep = null;
PreparedStatement noShowPrep = null;
PreparedStatement insertPrep = null;
try {
final DataSource datasource = ApplicationAttributes.getDataSource(application);
connection = datasource.getConnection();
final int currentTournament = Queries.getCurrentTournament(connection);
final StringWriter debugWriter = new StringWriter();
IOUtils.copy(request.getReader(), debugWriter);
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("Read data: " + debugWriter.toString());
}
final Reader reader = new StringReader(debugWriter.toString());
final Map<String, Map<String, Map<Integer, SubjectiveScore>>> allScores = jsonMapper.readValue(reader,
ScoresTypeInfo.INSTANCE);
for (final Map.Entry<String, Map<String, Map<Integer, SubjectiveScore>>> catEntry : allScores
.entrySet()) {
final String category = catEntry.getKey();
final ScoreCategory categoryDescription = challengeDescription
.getSubjectiveCategoryByName(category);
deletePrep = connection.prepareStatement("DELETE FROM " + category //
+ " WHERE TeamNumber = ?" //
+ " AND Tournament = ?" //
+ " AND Judge = ?" //
);
deletePrep.setInt(2, currentTournament);
noShowPrep = connection.prepareStatement("INSERT INTO " + category //
+ "(TeamNumber, Tournament, Judge, NoShow) VALUES(?, ?, ?, ?)");
noShowPrep.setInt(2, currentTournament);
noShowPrep.setBoolean(4, true);
final int NUM_COLUMNS_BEFORE_GOALS = 6;
insertPrep = createInsertStatement(connection, categoryDescription);
insertPrep.setInt(2, currentTournament);
insertPrep.setBoolean(4, false);
for (final Map.Entry<String, Map<Integer, SubjectiveScore>> judgeEntry : catEntry.getValue()
.entrySet()) {
final String judgeId = judgeEntry.getKey();
deletePrep.setString(3, judgeId);
noShowPrep.setString(3, judgeId);
insertPrep.setString(3, judgeId);
for (final Map.Entry<Integer, SubjectiveScore> teamEntry : judgeEntry.getValue().entrySet()) {
final int teamNumber = teamEntry.getKey();
final SubjectiveScore score = teamEntry.getValue();
if (score.getModified()) {
deletePrep.setInt(1, teamNumber);
noShowPrep.setInt(1, teamNumber);
insertPrep.setInt(1, teamNumber);
insertPrep.setString(5, score.getNote());
++numModified;
if (score.getDeleted()) {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("Deleting team: " + teamNumber + " judge: " + judgeId
+ " category: " + category);
}
deletePrep.executeUpdate();
} else if (score.getNoShow()) {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("NoShow team: " + teamNumber + " judge: " + judgeId + " category: "
+ category);
}
deletePrep.executeUpdate();
noShowPrep.executeUpdate();
} else {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("scores for team: " + teamNumber + " judge: " + judgeId
+ " category: " + category);
}
int goalIndex = 0;
for (final AbstractGoal goalDescription : categoryDescription.getGoals()) {
if (!goalDescription.isComputed()) {
final String goalName = goalDescription.getName();
if (goalDescription.isEnumerated()) {
final String value = score.getEnumSubScores().get(goalName);
if (null == value) {
insertPrep.setNull(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
Types.VARCHAR);
} else {
insertPrep.setString(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
value.trim());
}
} else {
final Double value = score.getStandardSubScores().get(goalName);
if (null == value) {
insertPrep.setNull(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
Types.DOUBLE);
} else {
insertPrep.setDouble(goalIndex + NUM_COLUMNS_BEFORE_GOALS, value);
}
}
++goalIndex;
} // not computed
} // end for
deletePrep.executeUpdate();
insertPrep.executeUpdate();
}
} // is modified
} // foreach team score
} // foreach judge
SQLFunctions.close(deletePrep);
deletePrep = null;
SQLFunctions.close(noShowPrep);
noShowPrep = null;
SQLFunctions.close(insertPrep);
insertPrep = null;
} // foreach category
UploadSubjectiveData.removeNullSubjectiveRows(connection, currentTournament, challengeDescription);
final Tournament tournament = Tournament.findTournamentByID(connection, currentTournament);
tournament.recordSubjectiveModified(connection);
final UploadResult result = new UploadResult(true, "Successfully uploaded scores", numModified);
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, result);
} catch (final SQLException sqle) {
LOGGER.error("Error uploading scores", sqle);
final UploadResult result = new UploadResult(false, sqle.getMessage(), numModified);
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, result);
} finally {
SQLFunctions.close(deletePrep);
SQLFunctions.close(noShowPrep);
SQLFunctions.close(insertPrep);
SQLFunctions.close(connection);
}
}
From source file:com.enonic.vertical.engine.handlers.MenuHandler.java
private void updateMenuItemData(User user, Element menuItemElem, int type, MenuItemKey parent, int order) throws VerticalUpdateException { String tmp;//from w w w. j a v a 2 s . com boolean modified = "modified".equals(menuItemElem.getAttribute("modified")); Connection con = null; PreparedStatement preparedStmt = null; try { con = getConnection(); // update the main table: int psCounter = 1; if (modified) { preparedStmt = con.prepareStatement(MENU_ITEM_UPDATE); } else { preparedStmt = con.prepareStatement(MENU_ITEM_UPDATE_NO_DATA); } //} // name Element tmp_elem = XMLTool.getElement(menuItemElem, ELEMENT_NAME_MENUITEM_NAME); String name = XMLTool.getElementText(tmp_elem); validateMenuItemName(name); StringReader sreader = new StringReader(name); preparedStmt.setCharacterStream(psCounter++, sreader, name.length()); if (parent != null) { preparedStmt.setInt(psCounter++, parent.toInt()); } else { preparedStmt.setNull(psCounter++, Types.INTEGER); } // order preparedStmt.setInt(psCounter++, order); Element dataElem; if (modified) { dataElem = XMLTool.getElement(menuItemElem, "data"); // parameters tmp_elem = XMLTool.getElement(menuItemElem, "parameters"); if (tmp_elem != null) { dataElem.appendChild(tmp_elem); } } else { dataElem = null; } // alternative name: tmp_elem = XMLTool.getElement(menuItemElem, ELEMENT_NAME_MENU_NAME); if (tmp_elem != null) { tmp = XMLTool.getElementText(tmp_elem); preparedStmt.setString(psCounter++, tmp); } else { preparedStmt.setNull(psCounter++, Types.VARCHAR); } // visibility: tmp = menuItemElem.getAttribute("visible"); if ("no".equals(tmp)) { preparedStmt.setInt(psCounter++, 1); } else { preparedStmt.setInt(psCounter++, 0); } // description: tmp_elem = XMLTool.getElement(menuItemElem, "description"); String data = XMLTool.getElementText(tmp_elem); if (data == null) { data = ""; } StringReader reader = new StringReader(data); preparedStmt.setCharacterStream(psCounter++, reader, data.length()); // keywords tmp_elem = XMLTool.getElement(menuItemElem, "keywords"); String keywords = XMLTool.getElementText(tmp_elem); if (keywords == null || keywords.length() == 0) { preparedStmt.setNull(psCounter++, Types.VARCHAR); } else { StringReader keywordReader = new StringReader(keywords); preparedStmt.setCharacterStream(psCounter++, keywordReader, keywords.length()); } // language String lanKey = menuItemElem.getAttribute("languagekey"); if ((lanKey != null) && (lanKey.length() > 0)) { preparedStmt.setInt(psCounter++, Integer.parseInt(lanKey)); } else { preparedStmt.setNull(psCounter++, Types.INTEGER); } // get menuitem key: tmp = menuItemElem.getAttribute("key"); int key = Integer.parseInt(tmp); // owner String ownerKey = menuItemElem.getAttribute("owner"); // modifier String modifierKey = menuItemElem.getAttribute("modifier"); if (modified && type == 4) { //byte[] document = null; Element docElem = XMLTool.getElement(menuItemElem, "document"); dataElem.appendChild(docElem); } preparedStmt.setString(psCounter++, ownerKey); preparedStmt.setString(psCounter++, modifierKey); RunAsType runAs = RunAsType.INHERIT; String runAsStr = menuItemElem.getAttribute("runAs"); if (StringUtils.isNotEmpty(runAsStr)) { runAs = RunAsType.valueOf(runAsStr); } preparedStmt.setInt(psCounter++, runAs.getKey()); // displayname: tmp_elem = XMLTool.getElement(menuItemElem, ELEMENT_NAME_DISPLAY_NAME); if (tmp_elem == null) { throw new IllegalArgumentException("Displayname must be set"); } else { tmp = XMLTool.getElementText(tmp_elem); preparedStmt.setString(psCounter++, tmp); } // data if (modified) { Document dataDoc = XMLTool.createDocument(); dataDoc.appendChild(dataDoc.importNode(dataElem, true)); byte[] bytes = XMLTool.documentToBytes(dataDoc, "UTF-8"); preparedStmt.setBinaryStream(psCounter++, new ByteArrayInputStream(bytes), bytes.length); } preparedStmt.setInt(psCounter, key); preparedStmt.executeUpdate(); if (multicaster.hasListeners()) { int menuKey = getMenuKeyByMenuItem(key); MenuHandlerEvent e = new MenuHandlerEvent(user, menuKey, key, name, this); multicaster.updatedMenuItem(e); } } catch (SQLException sqle) { VerticalEngineLogger.errorUpdate(this.getClass(), 20, "SQL error while updating menuitem data.", sqle); } finally { close(preparedStmt); close(con); } }
From source file:com.flexive.ejb.beans.PhraseEngineBean.java
/** * {@inheritDoc}// w ww . java 2 s .c o m */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public FxPhraseTreeNode saveTreeNode(FxPhraseTreeNode node) throws FxNoAccessException, FxNotFoundException { Connection con = null; PreparedStatement ps = null; checkMandatorAccess(node.getMandatorId(), FxContext.getUserTicket()); //load the node's phrase to check if it exists FxPhrase checkPhrase = loadPhrase(node.getPhrase().getCategory(), node.getPhrase().getKey(), node.getPhrase().getMandator()); if (!checkPhrase.hasId()) throw new FxNotFoundException("ex.phrases.noId"); try { // Obtain a database connection con = Database.getDbConnection(); ps = con.prepareStatement( "SELECT ID, MANDATOR FROM " + TBL_PHRASE_TREE + " WHERE ID=? AND MANDATOR=? AND CAT=?"); ps.setInt(3, node.getCategory()); boolean newNode = node.isNew(); if (!node.isNew()) { //check if the node has an id set but does not exist yet ps.setLong(1, node.getId()); ps.setLong(2, node.getMandatorId()); ResultSet rs = ps.executeQuery(); newNode = !(rs != null && rs.next()); } if (node.hasParent()) { //check if the parent node exists ps.setLong(1, node.getParentNodeId()); ps.setLong(2, node.getParentNodeMandatorId()); ResultSet rs = ps.executeQuery(); if (!(rs != null && rs.next())) throw new FxNotFoundException("ex.phrase.tree.parent.notFound", node.getParentNodeId(), node.getParentNodeMandatorId()); } ps.close(); if (!newNode) { ps = con.prepareStatement("UPDATE " + TBL_PHRASE_TREE + " SET PARENTID=?,PARENTMANDATOR=?,PHRASEID=?,PMANDATOR=?,POS=? WHERE ID=? AND MANDATOR=? AND CAT=?"); if (node.hasParent()) { ps.setLong(1, node.getParentNodeId()); ps.setLong(2, node.getParentNodeMandatorId()); } else { ps.setNull(1, Types.NUMERIC); ps.setNull(2, Types.NUMERIC); } ps.setLong(3, checkPhrase.getId()); ps.setLong(4, checkPhrase.getMandator()); if (!node.hasPos()) node.setPos(getNextNodePos(con, node.getCategory(), node.getParentNodeId(), node.getParentNodeMandatorId(), node.getMandatorId())); ps.setLong(5, node.getPos()); ps.setLong(6, node.getId()); ps.setLong(7, node.getMandatorId()); ps.setInt(8, node.getCategory()); ps.executeUpdate(); node.getPhrase().setId(checkPhrase.getId()); return node; } else { ps = con.prepareStatement("INSERT INTO " + TBL_PHRASE_TREE + " (ID,MANDATOR,PARENTID,PARENTMANDATOR,PHRASEID,PMANDATOR,POS,CAT)VALUES(?,?,?,?,?,?,?,?)"); final long nodeId = node.isNew() ? fetchNextNodeId(node.getMandatorId(), node.getCategory()) : node.getId(); ps.setLong(1, nodeId); ps.setLong(2, node.getMandatorId()); if (node.hasParent()) { ps.setLong(3, node.getParentNodeId()); ps.setLong(4, node.getParentNodeMandatorId()); } else { ps.setNull(3, Types.NUMERIC); ps.setNull(4, Types.NUMERIC); } ps.setLong(5, checkPhrase.getId()); ps.setLong(6, checkPhrase.getMandator()); if (!node.hasPos()) node.setPos(getNextNodePos(con, node.getCategory(), node.getParentNodeId(), node.getParentNodeMandatorId(), node.getMandatorId())); ps.setLong(7, node.getPos()); ps.setInt(8, node.getCategory()); ps.executeUpdate(); node.setId(nodeId); node.getPhrase().setId(checkPhrase.getId()); return node; } } catch (SQLException exc) { EJBUtils.rollback(ctx); throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException(); } finally { Database.closeObjects(PhraseEngineBean.class, con, ps); } }
From source file:org.jamwiki.db.AnsiQueryHandler.java
/** * *///from ww w.j a va 2 s. c om public void updateNamespace(Namespace namespace, Connection conn) throws SQLException { PreparedStatement stmt = null; try { // update if the ID is specified AND a namespace with the same ID already exists boolean isUpdate = (namespace.getId() != null && this.lookupNamespaces(conn).indexOf(namespace) != -1); // if adding determine the namespace ID(s) if (!isUpdate && namespace.getId() == null) { // note - this returns the last id in the system, so add one int nextId = DatabaseConnection.executeSequenceQuery(STATEMENT_SELECT_NAMESPACE_SEQUENCE, "namespace_id", conn); if (nextId < 200) { // custom namespaces start with IDs of 200 or more to leave room for future expansion nextId = 199; } namespace.setId(nextId + 1); } // execute the adds/updates stmt = (isUpdate) ? conn.prepareStatement(STATEMENT_UPDATE_NAMESPACE) : conn.prepareStatement(STATEMENT_INSERT_NAMESPACE); stmt.setString(1, namespace.getDefaultLabel()); if (namespace.getMainNamespaceId() != null) { stmt.setInt(2, namespace.getMainNamespaceId()); } else { stmt.setNull(2, Types.INTEGER); } stmt.setInt(3, namespace.getId()); stmt.executeUpdate(); } finally { DatabaseConnection.closeStatement(stmt); } }
From source file:com.funambol.foundation.items.dao.PIMNoteDAO.java
public String updateItem(NoteWrapper nw) throws DAOException { Connection con = null;/* w w w.ja v a 2 s . c o m*/ PreparedStatement ps = null; ResultSet rs = null; try { // // Note fields // Note note = nw.getNote(); StringBuilder updateQuery = new StringBuilder(); updateQuery.append(SQL_UPDATE_FNBL_PIM_NOTE_BEGIN); updateQuery.append(SQL_FIELD_LAST_UPDATE + SQL_EQUALS_QUESTIONMARK_COMMA); updateQuery.append(SQL_FIELD_STATUS + SQL_EQUALS_QUESTIONMARK_COMMA); String subject = note.getSubject().getPropertyValueAsString(); if (subject != null) { updateQuery.append(SQL_FIELD_SUBJECT + SQL_EQUALS_QUESTIONMARK_COMMA); } String textDescription = note.getTextDescription().getPropertyValueAsString(); if (textDescription != null) { updateQuery.append(SQL_FIELD_TEXTDESCRIPTION + SQL_EQUALS_QUESTIONMARK_COMMA); updateQuery.append(SQL_FIELD_CRC + SQL_EQUALS_QUESTIONMARK_COMMA); } String categories = note.getCategories().getPropertyValueAsString(); if (categories != null) { updateQuery.append(SQL_FIELD_CATEGORIES + SQL_EQUALS_QUESTIONMARK_COMMA); } String folder = note.getFolder().getPropertyValueAsString(); if (folder != null) { updateQuery.append(SQL_FIELD_FOLDER + SQL_EQUALS_QUESTIONMARK_COMMA); } String color = note.getColor().getPropertyValueAsString(); if (color != null) { updateQuery.append(SQL_FIELD_COLOR + SQL_EQUALS_QUESTIONMARK_COMMA); } String height = note.getHeight().getPropertyValueAsString(); if (height != null) { updateQuery.append(SQL_FIELD_HEIGHT + SQL_EQUALS_QUESTIONMARK_COMMA); } String width = note.getWidth().getPropertyValueAsString(); if (width != null) { updateQuery.append(SQL_FIELD_WIDTH + SQL_EQUALS_QUESTIONMARK_COMMA); } String top = note.getTop().getPropertyValueAsString(); if (top != null) { updateQuery.append(SQL_FIELD_TOP + SQL_EQUALS_QUESTIONMARK_COMMA); } String left = note.getLeft().getPropertyValueAsString(); if (left != null) { updateQuery.append(SQL_FIELD_LEFT_MARGIN + SQL_EQUALS_QUESTIONMARK_COMMA); } if (updateQuery.charAt(updateQuery.length() - 2) == ',') { updateQuery.deleteCharAt(updateQuery.length() - 2); } updateQuery.append(SQL_UPDATE_FNBL_PIM_NOTE_END); // Looks up the data source when the first connection is created con = getUserDataSource().getRoutedConnection(userId); ps = con.prepareStatement(updateQuery.toString()); int k = 1; // // last update // Timestamp lastUpdate = (nw.getLastUpdate() == null) ? new Timestamp(System.currentTimeMillis()) : nw.getLastUpdate(); ps.setLong(k++, lastUpdate.getTime()); // // status // ps.setString(k++, String.valueOf(Def.PIM_STATE_UPDATED)); // // subject // if (subject != null) { ps.setString(k++, StringUtils.left(subject, SQL_SUBJECT_DIM)); } // // textDescription // if (textDescription != null) { textDescription = textDescription.replace('\0', ' '); String truncatedTextDescription = StringUtils.left(textDescription, SQL_TEXTDESCRIPTION_DIM); ps.setString(k++, truncatedTextDescription); ps.setLong(k++, calculateCrc(truncatedTextDescription)); } // // categories // if (categories != null) { ps.setString(k++, truncateCategoriesField(categories, SQL_CATEGORIES_DIM)); } // // folder // if (folder != null) { ps.setString(k++, truncateFolderField(folder, SQL_FOLDER_DIM)); } // // color // if (color != null) { if (color.length() == 0) { ps.setNull(k++, Types.INTEGER); } else { ps.setInt(k++, Integer.parseInt(color)); } } // // height // if (height != null) { if (height.length() == 0) { ps.setNull(k++, Types.INTEGER); } else { ps.setInt(k++, Integer.parseInt(height)); } } // // width // if (width != null) { if (width.length() == 0) { ps.setNull(k++, Types.INTEGER); } else { ps.setInt(k++, Integer.parseInt(width)); } } // // top // if (top != null) { if (top.length() == 0) { ps.setNull(k++, Types.INTEGER); } else { ps.setInt(k++, Integer.parseInt(top)); } } // // left // if (left != null) { if (left.length() == 0) { ps.setNull(k++, Types.INTEGER); } else { ps.setInt(k++, Integer.parseInt(left)); } } // // id // ps.setLong(k++, Long.parseLong(nw.getId())); // // userId // ps.setString(k++, userId); ps.executeUpdate(); DBTools.close(null, ps, null); } catch (Exception e) { throw new DAOException("Error updating note.", e); } finally { DBTools.close(con, ps, rs); } return nw.getId(); }
From source file:com.enonic.vertical.engine.handlers.MenuHandler.java
private SiteKey createMenu(User user, CopyContext copyContext, Document doc, boolean useOldKey) throws VerticalCreateException, VerticalSecurityException { Element rootElement = doc.getDocumentElement(); // Get site key: String tmp;/*from w w w .j av a 2 s . c o m*/ // Prepare insertion into the database SiteKey siteKey = null; Connection con = null; PreparedStatement preparedStmt = null; try { con = getConnection(); tmp = rootElement.getAttribute("key"); if (!useOldKey || tmp == null || tmp.length() == 0) { // generate key: siteKey = new SiteKey(getNextKey(MENU_TABLE)); } else { siteKey = new SiteKey(tmp); } preparedStmt = con.prepareStatement(MENU_INSERT); if (copyContext != null) { copyContext.putMenuKey(Integer.parseInt(tmp), siteKey.toInt()); } preparedStmt.setInt(1, siteKey.toInt()); // name: Element tmpElement = XMLTool.getElement(rootElement, "name"); String name = null; if (tmpElement != null) { name = XMLTool.getElementText(tmpElement); } if (name != null) { preparedStmt.setString(6, name); } else { preparedStmt.setNull(6, Types.VARCHAR); } // firstpage: tmpElement = XMLTool.getElement(rootElement, "firstpage"); tmp = tmpElement.getAttribute("key"); if (tmp != null && tmp.length() > 0) { preparedStmt.setInt(2, Integer.parseInt(tmp)); } else { preparedStmt.setNull(2, Types.INTEGER); } // loginpage: tmpElement = XMLTool.getElement(rootElement, "loginpage"); tmp = tmpElement.getAttribute("key"); if (tmp != null && tmp.length() > 0) { preparedStmt.setInt(3, Integer.parseInt(tmp)); } else { preparedStmt.setNull(3, Types.INTEGER); } // errorpage: tmpElement = XMLTool.getElement(rootElement, "errorpage"); tmp = tmpElement.getAttribute("key"); if (tmp != null && tmp.length() > 0) { preparedStmt.setInt(4, Integer.parseInt(tmp)); } else { preparedStmt.setNull(4, Types.INTEGER); } // default pagetemplate: tmpElement = XMLTool.getElement(rootElement, "defaultpagetemplate"); if (tmpElement != null) { tmp = tmpElement.getAttribute("pagetemplatekey"); if (tmp != null && tmp.length() > 0) { preparedStmt.setInt(5, Integer.parseInt(tmp)); } else { preparedStmt.setNull(5, Types.INTEGER); } } else { preparedStmt.setNull(5, Types.INTEGER); } SiteData siteData = new SiteData(); // DeviceClassResolver: tmpElement = XMLTool.getElement(rootElement, "deviceclassresolver"); if (tmpElement != null) { String deviceClassResolverUrl = tmpElement.getAttribute("key"); if (StringUtils.isNotEmpty(deviceClassResolverUrl)) { siteData.setDeviceClassResolver(new ResourceKey(deviceClassResolverUrl)); } } // Default localization resource: tmpElement = XMLTool.getElement(rootElement, "defaultlocalizationresource"); if (tmpElement != null) { String defaultLocalizationResource = tmpElement.getAttribute("key"); if (StringUtils.isNotEmpty(defaultLocalizationResource)) { siteData.setDefaultLocalizationResource(new ResourceKey(defaultLocalizationResource)); } } // locale resolver tmpElement = XMLTool.getElement(rootElement, "localeresolver"); if (tmpElement != null) { String localeResolver = tmpElement.getAttribute("key"); if (StringUtils.isNotEmpty(localeResolver)) { siteData.setLocaleResolver(new ResourceKey(localeResolver)); } } // Path to public home: String pathToPublicHome = rootElement.getAttribute("path-to-public-home-resources"); if (StringUtils.isNotEmpty(pathToPublicHome)) { siteData.setPathToPublicResources(new ResourceKey(pathToPublicHome)); } // Path to home: String pathToHome = rootElement.getAttribute("path-to-home-resources"); if (StringUtils.isNotEmpty(pathToHome)) { siteData.setPathToResources(new ResourceKey(pathToHome)); } tmpElement = XMLTool.getElement(rootElement, "menudata"); if (tmpElement != null) { parseAndAddMenudataToSiteData(tmpElement, siteData); } final byte[] xmldata = siteData.getAsBytes(); preparedStmt.setBinaryStream(7, new ByteArrayInputStream(xmldata), xmldata.length); // language key: preparedStmt.setInt(8, Integer.parseInt(rootElement.getAttribute("languagekey"))); Element detailsElement = XMLTool.getElement(rootElement, "details"); // Statistics URL: tmpElement = XMLTool.getElement(detailsElement, "statistics"); if (tmpElement != null) { String statisticsURL = XMLTool.getElementText(tmpElement); if (statisticsURL != null) { preparedStmt.setString(9, statisticsURL); } else { preparedStmt.setNull(9, Types.VARCHAR); } } else { preparedStmt.setNull(9, Types.VARCHAR); } // Run As User: String runAsUserKey = rootElement.getAttribute("runas"); if (StringUtils.isNotEmpty(runAsUserKey)) { preparedStmt.setString(10, runAsUserKey); } else { preparedStmt.setNull(10, Types.VARCHAR); } // insert the data: preparedStmt.executeUpdate(); // create default menu access rights GroupHandler groupHandler = getGroupHandler(); String groupKey = groupHandler.getAdminGroupKey(); Document tmpDoc = XMLTool.createDocument("accessrights"); Element root = tmpDoc.getDocumentElement(); root.setAttribute("type", String.valueOf(AccessRight.MENUITEM_DEFAULT)); root.setAttribute("key", String.valueOf(siteKey)); Element accessrightElem = XMLTool.createElement(tmpDoc, root, "accessright"); accessrightElem.setAttribute("groupkey", groupKey); accessrightElem.setAttribute("grouptype", GroupType.ADMINS.toInteger().toString()); accessrightElem.setAttribute("read", "true"); accessrightElem.setAttribute("create", "true"); accessrightElem.setAttribute("update", "true"); accessrightElem.setAttribute("delete", "true"); accessrightElem.setAttribute("publish", "true"); accessrightElem.setAttribute("add", "true"); accessrightElem.setAttribute("administrate", "true"); getSecurityHandler().updateAccessRights(user, tmpDoc); //////// Create menuitems: Element itemsElement = XMLTool.getElement(rootElement, "menuitems"); if (itemsElement != null) { Element[] itemElems = XMLTool.getElements(itemsElement); for (int i = 0; i < itemElems.length; i++) { createMenuItem(user, copyContext, itemElems[i], siteKey, i, null, useOldKey); } } } catch (SQLException sqle) { String msg = "Failed to create menu: %t"; VerticalEngineLogger.errorCreate(this.getClass(), 0, msg, sqle); } catch (VerticalKeyException gke) { String msg = "Unable to generate key for table %0."; VerticalEngineLogger.errorCreate(this.getClass(), 0, msg, MENU_TABLE, gke); } catch (VerticalUpdateException e) { VerticalEngineLogger.errorCreate(this.getClass(), 10, "Error creating default access rights: %t", e); } finally { close(preparedStmt); close(con); } return siteKey; }