List of usage examples for java.sql PreparedStatement getGeneratedKeys
ResultSet getGeneratedKeys() throws SQLException;
Statement
object. From source file:org.kawanfw.sql.servlet.sql.ServerStatementRawExecute.java
/** * Execute the passed SQL PreparedStatement as execute() sand return: <br> * - The result set as a List of Maps for SELECT statements. <br> * - The return code for other statements * /*from ww w. jav a 2s. c o m*/ * @param sqlOrder * the qsql order * @param sqlParms * the sql parameters * @param out * the output stream where to write to result set output * * * @throws SQLException */ private void executePrepStatement(OutputStream out) throws SQLException, IOException { String sqlOrder = statementHolder.getSqlOrder(); PreparedStatement preparedStatement = null; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { preparedStatement = connection.prepareStatement(sqlOrder); } Map<Integer, Integer> parameterTypes = null; Map<Integer, String> parameterStringValues = null; // Class to set all the statement parameters ServerPreparedStatementParameters serverPreparedStatementParameters = null; try { ServerSqlUtil.setStatementProperties(preparedStatement, statementHolder); parameterTypes = statementHolder.getParameterTypes(); parameterStringValues = statementHolder.getParameterStringValues(); if (!SqlConfiguratorCall.allowExecute(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized for execute}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; throw new SecurityException(message); } debug("before ServerPreparedStatementParameters"); serverPreparedStatementParameters = new ServerPreparedStatementParameters(request, username, fileConfigurator, preparedStatement, statementHolder); serverPreparedStatementParameters.setParameters(); // Throws a SQL exception if the order is not authorized: debug("before new SqlSecurityChecker()"); boolean isAllowed = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, serverPreparedStatementParameters.getParameterValues()); if (!isAllowed) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; throw new SecurityException(message); } debug("before preparedStatement.execute()"); ServerSqlUtil.setMaxRowsToReturn(preparedStatement, sqlConfigurator); boolean isResultSet = preparedStatement.execute(); if (isResultSet) { ResultSet rs = preparedStatement.getResultSet(); try { //out.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) rs.close(); } } else { int rc = preparedStatement.getUpdateCount(); //out.write(TransferStatus.SEND_OK + CR_LF); //out.write("getUpdateCount=" + rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "getUpdateCount=" + rc); // Write the preparedStatement.getGeneratedKeys() on the stream // if necessary if (usesAutoGeneratedKeys) { ResultSet rs = preparedStatement.getGeneratedKeys(); try { ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) rs.close(); } } } } catch (SQLException e) { ServerLogger.getLogger().log(Level.WARNING, Tag.PRODUCT_PRODUCT_FAIL + CR_LF + "Prepared statement: " + sqlOrder + CR_LF + "- sql order : " + sqlOrder + CR_LF + "- sql parms : " + parameterTypes + CR_LF + "- sql values: " + parameterStringValues + CR_LF + "- exception : " + e.toString()); throw e; } finally { // Close the ServerPreparedStatementParameters if (serverPreparedStatementParameters != null) { serverPreparedStatementParameters.close(); } if (preparedStatement != null) { preparedStatement.close(); } } }
From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java
/** * creates category//from w w w . j a va 2 s . c o m * @param connection * @param toContextId * @param catName * @param moderated * @return categoy Id */ private int createCategory(Connection connection, String toContextId, String catName, int moderated, int gradable, Date startDate, Date endDate, int lockEndDate, int exisCatgeoryId) throws Exception { if (logger.isDebugEnabled()) logger.debug("creating category with title :" + catName + " for site : " + toContextId); int categoryId = -1; try { int order = 1; String categoryGetMaxOrderSql = "SELECT MAX(display_order) FROM jforum_categories"; PreparedStatement p = connection.prepareStatement(categoryGetMaxOrderSql); ResultSet rs = p.executeQuery(); if (rs.next()) { order = rs.getInt(1) + 1; } rs.close(); p.close(); String categoryAddNewSql = null; if (sqlService.getVendor().equals("oracle")) { categoryAddNewSql = "INSERT INTO jforum_categories (categories_id, title, display_order, moderated, gradable, start_date, end_date, lock_end_date) " + "VALUES (jforum_categories_seq.nextval, ?, ?, ?, ?, ?, ?, ?)"; p = connection.prepareStatement(categoryAddNewSql); p.setString(1, catName); p.setInt(2, order); p.setInt(3, moderated); p.setInt(4, gradable); if (startDate == null) { p.setTimestamp(5, null); } else { p.setTimestamp(5, new Timestamp(startDate.getTime())); } if (endDate == null) { p.setTimestamp(6, null); p.setInt(7, 0); } else { p.setTimestamp(6, new Timestamp(endDate.getTime())); p.setInt(7, lockEndDate); } p.executeUpdate(); p.close(); String categoryLastGeneratedCategoryId = "SELECT jforum_categories_seq.currval FROM DUAL"; p = connection.prepareStatement(categoryLastGeneratedCategoryId); rs = p.executeQuery(); if (rs.next()) { categoryId = rs.getInt(1); } rs.close(); p.close(); } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) { categoryAddNewSql = "INSERT INTO jforum_categories (title, display_order, moderated, gradable, start_date, end_date, lock_end_date) VALUES (?, ?, ?, ?, ?, ?, ?)"; p = connection.prepareStatement(categoryAddNewSql, Statement.RETURN_GENERATED_KEYS); p.setString(1, catName); p.setInt(2, order); p.setInt(3, moderated); p.setInt(4, gradable); if (startDate == null) { p.setTimestamp(5, null); } else { p.setTimestamp(5, new Timestamp(startDate.getTime())); } if (endDate == null) { p.setTimestamp(6, null); p.setInt(7, 0); } else { p.setTimestamp(6, new Timestamp(endDate.getTime())); p.setInt(7, lockEndDate); } p.executeUpdate(); rs = p.getGeneratedKeys(); if (rs.next()) { categoryId = rs.getInt(1); } rs.close(); p.close(); } String courseCategoryAddNewSql = "INSERT INTO jforum_sakai_course_categories (course_id,categories_id) VALUES (?, ?)"; p = connection.prepareStatement(courseCategoryAddNewSql); p.setString(1, toContextId); p.setInt(2, categoryId); p.execute(); p.close(); // create grade if category is gradable if (gradable == 1) { String gradeModelSelectByCategoryId = "SELECT grade_id, context, grade_type, forum_id, topic_id, points, add_to_gradebook, categories_id, min_posts, min_posts_required " + "FROM jforum_grade WHERE forum_id = 0 and topic_id = 0 and categories_id = ?"; PreparedStatement gradePrepStmnt = connection.prepareStatement(gradeModelSelectByCategoryId); gradePrepStmnt.setInt(1, exisCatgeoryId); ResultSet rsGrade = gradePrepStmnt.executeQuery(); float gradePoints = 0f; if (rsGrade.next()) { gradePoints = rsGrade.getFloat("points"); } int addToGradebook = rsGrade.getInt("add_to_gradebook"); boolean minPostsRequired = false; int minPosts = 0; if (rsGrade.getInt("min_posts_required") == 1) { minPostsRequired = true; minPosts = rsGrade.getInt("min_posts"); } rsGrade.close(); gradePrepStmnt.close(); int gradeId = createGrade(connection, toContextId, GRADE_BY_CATEGORY, 0, 0, categoryId, gradePoints, addToGradebook, minPostsRequired, minPosts, catName); // add to gradebook if ((gradeId > 0) && (addToGradebook == 1)) { createGradebookEntry(gradeId, catName, JForumUtil.toDoubleScore(gradePoints), endDate); } } } catch (SQLException e) { if (logger.isErrorEnabled()) logger.error("createCategory():Error occurred while creating category with title : " + catName); e.printStackTrace(); throw e; } return categoryId; }
From source file:pt.iflow.flows.FlowHolderBean.java
private synchronized State insertOrUpdateFlow(UserInfoInterface userInfo, String file, String name, byte[] data, boolean forceCreate, boolean makeVersion, String comment) { // recorrer a um metodo privado para efectuar a actualizacao // propriamente dita. // Esse mesmo metodo sera usado pelo deploy no caso de ser necessario // actualizar o catalogo. State result = new State(); Connection db = null;/*from ww w . j ava 2 s. co m*/ PreparedStatement pst = null; ResultSet rs = null; boolean flowFound = false; int flowid = -1; try { db = Utils.getDataSource().getConnection(); db.setAutoCommit(false); String query = "select flowid,flowversion from flow where flowfile=? and organizationid=?"; Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query1: " + query); pst = db.prepareStatement(query); pst.setString(1, file); pst.setString(2, userInfo.getOrganization()); rs = pst.executeQuery(); if (rs.next()) { flowFound = true; flowid = rs.getInt("flowid"); result.version = rs.getInt("flowversion"); } Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Flow exists " + flowFound + "id: " + flowid + "; version " + result.version); rs.close(); pst.close(); boolean copyToHistory = false; if (flowFound) { int arg = 0; query = "update flow set " + (StringUtils.isNotEmpty(name) ? "flowname=?," : "") + "flowdata=?," + (makeVersion ? "flowversion=flowversion+1," : "") + "modified=? where flowid=?"; Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query2a: " + query); pst = db.prepareStatement(query); if (StringUtils.isNotEmpty(name)) pst.setString(++arg, name); pst.setBinaryStream(++arg, new ByteArrayInputStream(data), data.length); pst.setTimestamp(++arg, new Timestamp(System.currentTimeMillis())); pst.setInt(++arg, flowid); int upd = pst.executeUpdate(); pst.close(); result.created = false; copyToHistory = (upd != 0); } else if (forceCreate) { if (null == name) name = file; Timestamp now = new Timestamp(System.currentTimeMillis()); query = DBQueryManager.getQuery("FlowHolder.INSERT_FLOW"); Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query2b: " + query); pst = db.prepareStatement(query, new String[] { "flowid" }); pst.setString(1, name); pst.setString(2, file); pst.setTimestamp(3, now); pst.setString(4, userInfo.getOrganization()); pst.setBinaryStream(5, new ByteArrayInputStream(data), data.length); pst.setTimestamp(6, now); pst.executeUpdate(); rs = pst.getGeneratedKeys(); if (rs.next()) { result.version = 1; result.created = true; flowid = rs.getInt(1); copyToHistory = true; } rs.close(); pst.close(); notifyNewFlow(userInfo, flowid); } else { throw new Exception("Cannot create flow."); } if (copyToHistory && makeVersion) { if (null != comment && comment.length() > MAX_COMMENT_SIZE) comment = comment.substring(0, MAX_COMMENT_SIZE); query = DBQueryManager.getQuery("FlowHolder.COPY_FLOW_TO_HISTORY"); Logger.debug(userInfo.getUtilizador(), this, "insertOrUpdateFlow", "Query3: " + query); pst = db.prepareStatement(query); pst.setString(1, comment); pst.setInt(2, flowid); pst.executeUpdate(); pst.close(); result.version++; } db.commit(); result.success = true; } catch (Exception e) { try { db.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); result.success = false; } finally { DatabaseInterface.closeResources(db, pst, rs); } result.flowid = flowid; return result; }
From source file:org.jamwiki.db.AnsiQueryHandler.java
/** * *///from w ww. ja va2 s. co m public void insertTopic(Topic topic, int virtualWikiId, Connection conn) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; try { int index = 1; if (!this.autoIncrementPrimaryKeys()) { stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC); int topicId = this.nextTopicId(conn); topic.setTopicId(topicId); stmt.setInt(index++, topic.getTopicId()); } else { stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_AUTO_INCREMENT, Statement.RETURN_GENERATED_KEYS); } stmt.setInt(index++, virtualWikiId); stmt.setString(index++, topic.getName()); stmt.setInt(index++, topic.getTopicType().id()); stmt.setInt(index++, (topic.getReadOnly() ? 1 : 0)); if (topic.getCurrentVersionId() == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, topic.getCurrentVersionId()); } stmt.setTimestamp(index++, topic.getDeleteDate()); stmt.setInt(index++, (topic.getAdminOnly() ? 1 : 0)); stmt.setString(index++, topic.getRedirectTo()); stmt.setInt(index++, topic.getNamespace().getId()); stmt.setString(index++, topic.getPageName()); stmt.setString(index++, topic.getPageName().toLowerCase()); stmt.executeUpdate(); if (this.autoIncrementPrimaryKeys()) { rs = stmt.getGeneratedKeys(); if (!rs.next()) { throw new SQLException("Unable to determine auto-generated ID for database record"); } topic.setTopicId(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:org.jamwiki.db.AnsiQueryHandler.java
/** * *//*from ww w. j a v a2 s. co m*/ public void insertUserBlock(UserBlock userBlock, Connection conn) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; try { int index = 1; if (!this.autoIncrementPrimaryKeys()) { stmt = conn.prepareStatement(STATEMENT_INSERT_USER_BLOCK); int blockId = this.nextUserBlockId(conn); userBlock.setBlockId(blockId); stmt.setInt(index++, userBlock.getBlockId()); } else { stmt = conn.prepareStatement(STATEMENT_INSERT_USER_BLOCK_AUTO_INCREMENT, Statement.RETURN_GENERATED_KEYS); } if (userBlock.getWikiUserId() == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, userBlock.getWikiUserId()); } stmt.setString(index++, userBlock.getIpAddress()); stmt.setTimestamp(index++, userBlock.getBlockDate()); stmt.setTimestamp(index++, userBlock.getBlockEndDate()); stmt.setString(index++, userBlock.getBlockReason()); stmt.setInt(index++, userBlock.getBlockedByUserId()); stmt.setTimestamp(index++, userBlock.getUnblockDate()); stmt.setString(index++, userBlock.getUnblockReason()); if (userBlock.getUnblockedByUserId() == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, userBlock.getUnblockedByUserId()); } stmt.executeUpdate(); if (this.autoIncrementPrimaryKeys()) { rs = stmt.getGeneratedKeys(); if (!rs.next()) { throw new SQLException("Unable to determine auto-generated ID for database record"); } userBlock.setBlockId(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:org.jamwiki.db.AnsiQueryHandler.java
/** * *//*from w w w. j av a 2s .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:org.etudes.component.app.jforum.JforumDataServiceImpl.java
private int createGrade(Connection connection, String toContextId, int gradeType, int forumId, int topicId, int categoryId, float gradePoints, int addToGradebook, boolean minPostsRequired, int minPosts, String gradebookTitle) throws SQLException { int gradeId = -1; ResultSet rs = null;// w ww . j a v a 2 s.com String gradebookToolId = ServerConfigurationService.getString(JForumGradeService.GRADEBOOK_TOOL_ID); if ((gradebookToolId == null) || (gradebookToolId.trim().length() == 0)) { gradebookToolId = "sakai.gradebook.tool"; } Site site = null; try { site = SiteService.getSite(ToolManager.getCurrentPlacement().getContext()); } catch (IdUnusedException e) { if (logger.isWarnEnabled()) { logger.warn(e.toString(), e); } } boolean gradebookExists = false; if ((site != null) && (site.getToolForCommonId(gradebookToolId) != null)) { gradebookExists = true; } if (sqlService.getVendor().equals("oracle")) { String gradeModelAddNewsql = "INSERT INTO jforum_grade(grade_id, context, grade_type, forum_id, topic_id, categories_id, points, add_to_gradebook, min_posts_required, min_posts) " + "VALUES (jforum_grade_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement p = connection.prepareStatement(gradeModelAddNewsql); p.setString(1, toContextId); p.setInt(2, gradeType); p.setInt(3, forumId); p.setInt(4, topicId); p.setInt(5, categoryId); p.setFloat(6, gradePoints); if (gradebookExists) { String gradebookUid = ToolManager.getInstance().getCurrentPlacement().getContext(); JForumGBService jForumGBService = null; jForumGBService = (JForumGBService) ComponentManager .get("org.etudes.api.app.jforum.JForumGBService"); if (!jForumGBService.isAssignmentDefined(gradebookUid, gradebookTitle)) { p.setInt(7, addToGradebook); } else { addToGradebook = 0; p.setInt(7, 0); } } else { p.setInt(7, 0); } if (minPostsRequired) { p.setInt(8, 1); p.setInt(9, minPosts); } else { p.setInt(8, 0); p.setInt(9, 0); } p.executeUpdate(); p.close(); String gradeLastGeneratedGradeId = "SELECT jforum_grade_seq.currval FROM DUAL"; p = connection.prepareStatement(gradeLastGeneratedGradeId); rs = p.executeQuery(); if (rs.next()) { gradeId = rs.getInt(1); } rs.close(); p.close(); } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) { String gradeModelAddNewsql = "INSERT INTO jforum_grade(context, grade_type, forum_id, topic_id, categories_id, points, add_to_gradebook, min_posts_required, min_posts) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement p = connection.prepareStatement(gradeModelAddNewsql, Statement.RETURN_GENERATED_KEYS); p.setString(1, toContextId); p.setInt(2, gradeType); p.setInt(3, forumId); p.setInt(4, topicId); p.setInt(5, categoryId); p.setFloat(6, gradePoints); if (gradebookExists) { String gradebookUid = ToolManager.getInstance().getCurrentPlacement().getContext(); JForumGBService jForumGBService = null; jForumGBService = (JForumGBService) ComponentManager .get("org.etudes.api.app.jforum.JForumGBService"); if (!jForumGBService.isAssignmentDefined(gradebookUid, gradebookTitle)) { p.setInt(7, addToGradebook); } else { addToGradebook = 0; p.setInt(7, 0); } } else { p.setInt(7, 0); } if (minPostsRequired) { p.setInt(8, 1); p.setInt(9, minPosts); } else { p.setInt(8, 0); p.setInt(9, 0); } p.executeUpdate(); rs = p.getGeneratedKeys(); if (rs.next()) { gradeId = rs.getInt(1); } rs.close(); p.close(); } return gradeId; }
From source file:org.kawanfw.sql.servlet.sql.ServerStatement.java
/** * Execute the passed SQL Statement and return: <br> * - The result set as a List of Maps for SELECT statements. <br> * - The return code for other statements * /*from w w w. j a va2s . c o m*/ * @param sqlOrder * the qsql order * @param sqlParms * the sql parameters * @param out * the output stream where to write to result set output * * * @throws SQLException */ private void executeQueryOrUpdatePrepStatement(OutputStream out) throws SQLException, IOException { String sqlOrder = statementHolder.getSqlOrder(); debug("statementHolder: " + statementHolder.getSqlOrder()); debug("sqlOrder : " + sqlOrder); // sqlOrder = HtmlConverter.fromHtml(sqlOrder); if (statementHolder.isDoExtractResultSetMetaData()) { sqlOrder = DbVendorManager.addLimit1(sqlOrder, connection); } PreparedStatement preparedStatement = null; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { preparedStatement = connection.prepareStatement(sqlOrder); } Map<Integer, Integer> parameterTypes = null; Map<Integer, String> parameterStringValues = null; // Class to set all the statement parameters ServerPreparedStatementParameters serverPreparedStatementParameters = null; try { ServerSqlUtil.setStatementProperties(preparedStatement, statementHolder); parameterTypes = statementHolder.getParameterTypes(); parameterStringValues = statementHolder.getParameterStringValues(); debug("before ServerPreparedStatementParameters"); serverPreparedStatementParameters = new ServerPreparedStatementParameters(request, username, fileConfigurator, preparedStatement, statementHolder); serverPreparedStatementParameters.setParameters(); // Throws a SQL exception if the order is not authorized: debug("before new SqlSecurityChecker()"); boolean isAllowedAfterAnalysis = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, serverPreparedStatementParameters.getParameterValues()); if (!isAllowedAfterAnalysis) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); debug("Before SqlConfiguratorCall.runIfStatementRefused"); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, ipAddress, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); debug("After SqlConfiguratorCall.runIfStatementRefused"); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; throw new SecurityException(message); } isAllowedAfterAnalysis = SqlConfiguratorCall.allowResultSetGetMetaData(sqlConfigurator, username, connection); if (statementHolder.isDoExtractResultSetMetaData() && !isAllowedAfterAnalysis) { String message = Tag.PRODUCT_SECURITY + " ResultSet.getMetaData() Query not authorized."; throw new SecurityException(message); } debug("before executeQuery() / executeUpdate()"); if (statementHolder.isExecuteUpdate()) { if (!SqlConfiguratorCall.allowExecuteUpdate(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized for executeUpdate}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; throw new SecurityException(message); } int rc = preparedStatement.executeUpdate(); //br.write(TransferStatus.SEND_OK + CR_LF); //br.write(rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "" + rc); // Write the preparedStatement.getGeneratedKeys() on the stream // if necessary if (usesAutoGeneratedKeys) { ResultSet rs = null; try { rs = preparedStatement.getGeneratedKeys(); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) { rs.close(); } } } } else { ResultSet rs = null; try { if (statementHolder.isDoExtractResultSetMetaData()) { preparedStatement.setMaxRows(1); } else { ServerSqlUtil.setMaxRowsToReturn(preparedStatement, sqlConfigurator); } rs = preparedStatement.executeQuery(); //br.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); // If a a ResultSet.getMetaData() has been asked, send it // back! if (statementHolder.isDoExtractResultSetMetaData()) { ResultSetMetaDataWriter resultSetMetaDataWriter = new ResultSetMetaDataWriter(out, commonsConfigurator, sqlConfigurator); resultSetMetaDataWriter.write(rs); } else { // print(rs, br); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } } finally { if (rs != null) { rs.close(); } } } } catch (SQLException e) { ServerLogger.getLogger().log(Level.WARNING, Tag.PRODUCT_EXCEPTION_RAISED + CR_LF + "Prepared statement: " + sqlOrder + CR_LF + "- sql order : " + sqlOrder + CR_LF + "- sql parms : " + parameterTypes + CR_LF + "- sql values: " + parameterStringValues + CR_LF + "- exception : " + e.toString()); throw e; } finally { // Close the ServerPreparedStatementParameters if (serverPreparedStatementParameters != null) { serverPreparedStatementParameters.close(); } if (preparedStatement != null) { preparedStatement.close(); } // Clean all parameterTypes = null; parameterStringValues = null; serverPreparedStatementParameters = null; } }
From source file:i5.las2peer.services.mobsos.SurveyService.java
/** * Stores a new questionnaire described with JSON into the MobSOS database. * The MobSOS database thereby generates a new id returned by this method. * @throws UnsupportedEncodingException * @throws ParseException /*from ww w. j a v a 2 s .c om*/ */ private int storeNewQuestionnaire(JSONObject questionnaire) throws IllegalArgumentException, SQLException, UnsupportedEncodingException, ParseException { String sub = (String) getActiveUserInfo().get("sub"); Connection conn = null; PreparedStatement stmt = null; ResultSet rset = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement("insert into " + jdbcSchema + ".questionnaire(owner, organization, logo, name, description, lang) values (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); stmt.clearParameters(); stmt.setString(1, sub); // active agent becomes owner automatically stmt.setString(2, (String) questionnaire.get("organization")); stmt.setString(3, (String) questionnaire.get("logo")); stmt.setString(4, (String) questionnaire.get("name")); stmt.setString(5, (String) questionnaire.get("description")); stmt.setString(6, (String) questionnaire.get("lang")); stmt.executeUpdate(); ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()) { return rs.getInt(1); } else { throw new NoSuchElementException("No new questionnaire was created!"); } } catch (UnsupportedOperationException e) { e.printStackTrace(); } finally { try { if (rset != null) rset.close(); } catch (Exception e) { e.printStackTrace(); } try { if (stmt != null) stmt.close(); } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } throw new NoSuchElementException("No new questionnaire was created!"); }
From source file:org.jamwiki.db.AnsiQueryHandler.java
/** * */// w ww . j a v a 2s . c o m public void insertTopicVersions(List<TopicVersion> topicVersions, Connection conn) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; boolean useBatch = (topicVersions.size() > 1); try { if (!this.autoIncrementPrimaryKeys()) { stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION); } else if (useBatch) { // generated keys don't work in batch mode stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT); } else { stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT, Statement.RETURN_GENERATED_KEYS); } int topicVersionId = -1; if (!this.autoIncrementPrimaryKeys() || useBatch) { // manually retrieve next topic version id when using batch // mode or when the database doesn't support generated keys. topicVersionId = this.nextTopicVersionId(conn); } for (TopicVersion topicVersion : topicVersions) { if (!this.autoIncrementPrimaryKeys() || useBatch) { // FIXME - if two threads update the database simultaneously then // it is possible that this code could set the topic version ID // to a value that is different from what the database ends up // using. topicVersion.setTopicVersionId(topicVersionId++); } this.prepareTopicVersionStatement(topicVersion, stmt); if (useBatch) { stmt.addBatch(); } else { stmt.executeUpdate(); } if (this.autoIncrementPrimaryKeys() && !useBatch) { rs = stmt.getGeneratedKeys(); if (!rs.next()) { throw new SQLException("Unable to determine auto-generated ID for database record"); } topicVersion.setTopicVersionId(rs.getInt(1)); } } if (useBatch) { stmt.executeBatch(); } } finally { // close only the statement and result set - leave the connection open for further use DatabaseConnection.closeConnection(null, stmt, rs); stmt = null; rs = null; } }