Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCResourceDAO.java

public void addResourceDO(ResourceDO resourceDO) throws RepositoryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet result = null;/*from  www .  j  a  v a  2s. c om*/

    try {
        String sql = "INSERT INTO REG_RESOURCE (REG_PATH_ID, REG_NAME, REG_MEDIA_TYPE, "
                + "REG_CREATOR, REG_CREATED_TIME, REG_LAST_UPDATOR, "
                + "REG_LAST_UPDATED_TIME, REG_DESCRIPTION, " + "REG_CONTENT_ID, REG_TENANT_ID, REG_UUID) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        String sql1 = "SELECT MAX(REG_VERSION) FROM REG_RESOURCE";

        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);

        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_VERSION") });
        } else {
            ps = conn.prepareStatement(sql);
        }

        ps.setInt(1, resourceDO.getPathID());
        ps.setString(2, resourceDO.getName());
        ps.setString(3, resourceDO.getMediaType());
        ps.setString(4, resourceDO.getAuthor());
        ps.setTimestamp(5, new Timestamp(resourceDO.getCreatedOn()));
        ps.setString(6, resourceDO.getLastUpdater());
        ps.setTimestamp(7, new Timestamp(resourceDO.getLastUpdatedOn()));
        ps.setString(8, resourceDO.getDescription());

        if (resourceDO.getContentID() > 0) {
            ps.setInt(9, resourceDO.getContentID());
        } else {
            ps.setNull(9, Types.INTEGER);
        }

        ps.setInt(10, CurrentContext.getTenantId());
        ps.setString(11, resourceDO.getUUID());

        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            result = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_RESOURCE_LOCK) {
                ps.executeUpdate();
                ps1 = conn.prepareStatement(sql1);
                result = ps1.executeQuery();
            }
        }

        if (result.next()) {
            long version = result.getLong(1);
            resourceDO.setVersion(version);
        }

        ps.close();
    } catch (SQLException e) {
        String msg = "Failed to add resource to version " + resourceDO.getVersion() + ". " + e.getMessage();
        log.error(msg, e);
        throw new RepositoryDBException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:org.cerberus.crud.dao.impl.TestCaseExecutionDAO.java

@Override
public long insertTCExecution(TestCaseExecution tCExecution) throws CerberusException {
    boolean throwEx = false;
    final String query = "INSERT INTO testcaseexecution(test, testcase, build, revision, environment, environmentData, country, browser, application, ip, "
            + "url, port, tag, verbose, status, start, controlstatus, controlMessage, crbversion, finished, browserFullVersion, executor, screensize,"
            + "conditionOper, conditionVal1Init, conditionVal2Init, conditionVal1, conditionVal2, manualExecution) "
            + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    Connection connection = this.databaseSpring.connect();
    try {/*w w w  .jav  a  2 s . co m*/
        PreparedStatement preStat = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        try {
            int i = 1;
            preStat.setString(i++, tCExecution.getTest());
            preStat.setString(i++, tCExecution.getTestCase());
            preStat.setString(i++, tCExecution.getBuild());
            preStat.setString(i++, tCExecution.getRevision());
            preStat.setString(i++, tCExecution.getEnvironment());
            preStat.setString(i++, tCExecution.getEnvironmentData());
            preStat.setString(i++, tCExecution.getCountry());
            preStat.setString(i++, tCExecution.getBrowser());
            preStat.setString(i++, tCExecution.getApplicationObj().getApplication());
            preStat.setString(i++, tCExecution.getIp());
            preStat.setString(i++, tCExecution.getUrl());
            preStat.setString(i++, tCExecution.getPort());
            preStat.setString(i++, tCExecution.getTag());
            preStat.setInt(i++, tCExecution.getVerbose());
            preStat.setString(i++, tCExecution.getStatus());
            preStat.setTimestamp(i++, new Timestamp(tCExecution.getStart()));
            preStat.setString(i++, tCExecution.getControlStatus());
            preStat.setString(i++, StringUtil.getLeftString(tCExecution.getControlMessage(), 500));
            preStat.setString(i++, tCExecution.getCrbVersion());
            preStat.setString(i++, tCExecution.getFinished());
            preStat.setString(i++, tCExecution.getBrowserFullVersion());
            preStat.setString(i++, tCExecution.getExecutor());
            preStat.setString(i++, tCExecution.getScreenSize());
            preStat.setString(i++, tCExecution.getConditionOper());
            preStat.setString(i++, tCExecution.getConditionVal1Init());
            preStat.setString(i++, tCExecution.getConditionVal2Init());
            preStat.setString(i++, tCExecution.getConditionVal1());
            preStat.setString(i++, tCExecution.getConditionVal2());
            preStat.setString(i++, tCExecution.isManualExecution() ? "Y" : "N");

            preStat.executeUpdate();
            ResultSet resultSet = preStat.getGeneratedKeys();
            try {
                if (resultSet.first()) {
                    return resultSet.getInt(1);
                }
            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
                throwEx = true;
            } finally {
                resultSet.close();
            }

        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
            throwEx = true;
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
        throwEx = true;
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    if (throwEx) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.EXECUTION_FA));
    }
    return 0;
}

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

/**
 * Save given inputs to the database./*from w ww  .  j av  a2s  .c  o m*/
 *
 * Use given prepare statement to save all inputs into repository.
 *
 * @param configId Identifier for corresponding config
 * @param inputs List of inputs that needs to be saved
 * @param baseInputStmt Statement that we can utilize
 * @throws SQLException In case of any failure on Derby side
 */
private void registerConfigInputs(long configId, List<MInput<?>> inputs, PreparedStatement baseInputStmt)
        throws SQLException {
    short inputIndex = 0;
    for (MInput<?> input : inputs) {
        baseInputStmt.setString(1, input.getName());
        baseInputStmt.setLong(2, configId);
        baseInputStmt.setShort(3, inputIndex++);
        baseInputStmt.setString(4, input.getType().name());
        baseInputStmt.setBoolean(5, input.isSensitive());
        // String specific column(s)
        if (input.getType().equals(MInputType.STRING)) {
            MStringInput strInput = (MStringInput) input;
            baseInputStmt.setShort(6, strInput.getMaxLength());
        } else {
            baseInputStmt.setNull(6, Types.INTEGER);
        }
        // Enum specific column(s)
        if (input.getType() == MInputType.ENUM) {
            baseInputStmt.setString(7, StringUtils.join(((MEnumInput) input).getValues(), ","));
        } else {
            baseInputStmt.setNull(7, Types.VARCHAR);
        }

        int baseInputCount = baseInputStmt.executeUpdate();
        if (baseInputCount != 1) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0017, Integer.toString(baseInputCount));
        }

        ResultSet rsetInputId = baseInputStmt.getGeneratedKeys();
        if (!rsetInputId.next()) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0018);
        }

        long inputId = rsetInputId.getLong(1);
        input.setPersistenceId(inputId);
    }
}

From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java

/**
 * create jforum user/*w w w .j a v a  2s  .  com*/
 * @param connection
 * @param username
 * @param email
 * @param firstName
 * @param lastName
 * @param sakaiUserId
 * @return
 * @throws Exception
 */
private int createJforumUser(Connection connection, String username, String email, String firstName,
        String lastName, String sakaiUserId) throws SQLException {
    int jforumUserId = -1;

    try {
        PreparedStatement p = null;
        ResultSet rs = null;

        String addNewUser = null;
        if (sqlService.getVendor().equals("oracle")) {
            addNewUser = "INSERT INTO jforum_users (user_id, username, user_password, "
                    + "user_email, user_regdate, user_fname, user_lname, sakai_user_id) VALUES "
                    + "(jforum_users_seq.nextval, ?, ?, ?,  SYSDATE, ?, ?, ?)";

            p = connection.prepareStatement(addNewUser);
            p.setString(1, username);
            p.setString(2, "password");
            p.setString(3, email);
            p.setString(4, firstName);
            p.setString(5, lastName);
            p.setString(6, sakaiUserId);

            p.executeUpdate();

            p.close();

            String categoryLastGeneratedCategoryId = "SELECT jforum_users_seq.currval  FROM DUAL";
            p = connection.prepareStatement(categoryLastGeneratedCategoryId);
            rs = p.executeQuery();

            if (rs.next()) {
                jforumUserId = rs.getInt(1);
            }

            rs.close();
            p.close();
        } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
            addNewUser = "INSERT INTO jforum_users (username, user_password, "
                    + "user_email, user_regdate, user_fname, user_lname, "
                    + "sakai_user_id) VALUES (?, ?, ?, NOW(), ?, ?, ?)";

            p = connection.prepareStatement(addNewUser, Statement.RETURN_GENERATED_KEYS);
            p.setString(1, username);
            p.setString(2, "password");
            p.setString(3, email);
            p.setString(4, firstName);
            p.setString(5, lastName);
            p.setString(6, sakaiUserId);

            p.executeUpdate();

            rs = p.getGeneratedKeys();
            if (rs.next()) {
                jforumUserId = rs.getInt(1);
            }
            rs.close();
            p.close();
        }
    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("createJforumUser():Error occurred while creating user with username : " + username);
        e.printStackTrace();
        throw e;
    }
    return jforumUserId;
}

From source file:net.pms.dlna.DLNAMediaDatabase.java

public synchronized void insertData(String name, long modified, int type, DLNAMediaInfo media) {
    Connection conn = null;//from   w  w w  .j  a va 2s.co m
    PreparedStatement ps = null;
    try {
        conn = getConnection();
        ps = conn.prepareStatement(
                "INSERT INTO FILES(FILENAME, MODIFIED, TYPE, DURATION, BITRATE, WIDTH, HEIGHT, SIZE, CODECV, FRAMERATE, ASPECT, ASPECTRATIOCONTAINER, ASPECTRATIOVIDEOTRACK, REFRAMES, AVCLEVEL, BITSPERPIXEL, THUMB, CONTAINER, MODEL, EXPOSURE, ORIENTATION, ISO, MUXINGMODE, FRAMERATEMODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        ps.setString(1, name);
        ps.setTimestamp(2, new Timestamp(modified));
        ps.setInt(3, type);
        if (media != null) {
            if (media.getDuration() != null) {
                ps.setDouble(4, media.getDurationInSeconds());
            } else {
                ps.setNull(4, Types.DOUBLE);
            }

            int databaseBitrate = 0;
            if (type != Format.IMAGE) {
                databaseBitrate = media.getBitrate();
                if (databaseBitrate == 0) {
                    logger.debug("Could not parse the bitrate from: " + name);
                }
            }
            ps.setInt(5, databaseBitrate);

            ps.setInt(6, media.getWidth());
            ps.setInt(7, media.getHeight());
            ps.setLong(8, media.getSize());
            ps.setString(9, left(media.getCodecV(), SIZE_CODECV));
            ps.setString(10, left(media.getFrameRate(), SIZE_FRAMERATE));
            ps.setString(11, left(media.getAspect(), SIZE_ASPECT));
            ps.setString(12, left(media.getAspect(), SIZE_ASPECTRATIO_CONTAINER));
            ps.setString(13, left(media.getAspect(), SIZE_ASPECTRATIO_VIDEOTRACK));
            ps.setByte(14, media.getReferenceFrameCount());
            ps.setString(15, left(media.getAvcLevel(), SIZE_AVC_LEVEL));
            ps.setInt(16, media.getBitsPerPixel());
            ps.setBytes(17, media.getThumb());
            ps.setString(18, left(media.getContainer(), SIZE_CONTAINER));
            if (media.getExtras() != null) {
                ps.setString(19, left(media.getExtrasAsString(), SIZE_MODEL));
            } else {
                ps.setString(19, left(media.getModel(), SIZE_MODEL));
            }
            ps.setInt(20, media.getExposure());
            ps.setInt(21, media.getOrientation());
            ps.setInt(22, media.getIso());
            ps.setString(23, left(media.getMuxingModeAudio(), SIZE_MUXINGMODE));
            ps.setString(24, left(media.getFrameRateMode(), SIZE_FRAMERATE_MODE));
        } else {
            ps.setString(4, null);
            ps.setInt(5, 0);
            ps.setInt(6, 0);
            ps.setInt(7, 0);
            ps.setLong(8, 0);
            ps.setString(9, null);
            ps.setString(10, null);
            ps.setString(11, null);
            ps.setString(12, null);
            ps.setString(13, null);
            ps.setByte(14, (byte) -1);
            ps.setString(15, null);
            ps.setInt(16, 0);
            ps.setBytes(17, null);
            ps.setString(18, null);
            ps.setString(19, null);
            ps.setInt(20, 0);
            ps.setInt(21, 0);
            ps.setInt(22, 0);
            ps.setString(23, null);
            ps.setString(24, null);
        }
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        int id = -1;
        while (rs.next()) {
            id = rs.getInt(1);
        }
        rs.close();
        if (media != null && id > -1) {
            PreparedStatement insert = null;
            if (media.getAudioTracksList().size() > 0) {
                insert = conn.prepareStatement(
                        "INSERT INTO AUDIOTRACKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            }

            for (DLNAMediaAudio audio : media.getAudioTracksList()) {
                insert.clearParameters();
                insert.setInt(1, id);
                insert.setInt(2, audio.getId());
                insert.setString(3, left(audio.getLang(), SIZE_LANG));
                insert.setString(4, left(audio.getFlavor(), SIZE_FLAVOR));
                insert.setInt(5, audio.getAudioProperties().getNumberOfChannels());
                insert.setString(6, left(audio.getSampleFrequency(), SIZE_SAMPLEFREQ));
                insert.setString(7, left(audio.getCodecA(), SIZE_CODECA));
                insert.setInt(8, audio.getBitsperSample());
                insert.setString(9, left(trimToEmpty(audio.getAlbum()), SIZE_ALBUM));
                insert.setString(10, left(trimToEmpty(audio.getArtist()), SIZE_ARTIST));
                insert.setString(11, left(trimToEmpty(audio.getSongname()), SIZE_SONGNAME));
                insert.setString(12, left(trimToEmpty(audio.getGenre()), SIZE_GENRE));
                insert.setInt(13, audio.getYear());
                insert.setInt(14, audio.getTrack());
                insert.setInt(15, audio.getAudioProperties().getAudioDelay());
                insert.setString(16, left(trimToEmpty(audio.getMuxingModeAudio()), SIZE_MUXINGMODE));
                insert.setInt(17, audio.getBitRate());

                try {
                    insert.executeUpdate();
                } catch (JdbcSQLException e) {
                    if (e.getErrorCode() == 23505) {
                        logger.debug(
                                "A duplicate key error occurred while trying to store the following file's audio information in the database: "
                                        + name);
                    } else {
                        logger.debug(
                                "An error occurred while trying to store the following file's audio information in the database: "
                                        + name);
                    }
                    logger.debug("The error given by jdbc was: " + e);
                }
            }

            if (media.getSubtitleTracksList().size() > 0) {
                insert = conn.prepareStatement("INSERT INTO SUBTRACKS VALUES (?, ?, ?, ?, ?)");
            }
            for (DLNAMediaSubtitle sub : media.getSubtitleTracksList()) {
                if (sub.getExternalFile() == null) { // no save of external subtitles
                    insert.clearParameters();
                    insert.setInt(1, id);
                    insert.setInt(2, sub.getId());
                    insert.setString(3, left(sub.getLang(), SIZE_LANG));
                    insert.setString(4, left(sub.getFlavor(), SIZE_FLAVOR));
                    insert.setInt(5, sub.getType().getStableIndex());
                    try {
                        insert.executeUpdate();
                    } catch (JdbcSQLException e) {
                        if (e.getErrorCode() == 23505) {
                            logger.debug(
                                    "A duplicate key error occurred while trying to store the following file's subtitle information in the database: "
                                            + name);
                        } else {
                            logger.debug(
                                    "An error occurred while trying to store the following file's subtitle information in the database: "
                                            + name);
                        }
                        logger.debug("The error given by jdbc was: " + e);
                    }
                }
            }
            close(insert);
        }
    } catch (SQLException se) {
        if (se.getErrorCode() == 23001) {
            logger.debug("Duplicate key while inserting this entry: " + name + " into the database: "
                    + se.getMessage());
        } else {
            logger.error(null, se);
        }
    } finally {
        close(ps);
        close(conn);
    }
}

From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java

/**
 * process post attachments//from   ww  w . j  a  v  a  2s  .com
 * @param connection connection   
 * @param fromPostId from postid
 * @param toPostId to post id
 * @param jforumUserId jforum user id
 */
private void processAttachments(Connection connection, int fromPostId, int toPostId, int jforumUserId) {
    //get attachments from fromPostId and make copy for toPostId
    String getAttachementsSql = "SELECT attach_id, post_id, privmsgs_id, user_id "
            + "FROM jforum_attach WHERE post_id = ?";
    try {
        PreparedStatement p = connection.prepareStatement(getAttachementsSql);
        p.setInt(1, fromPostId);
        ResultSet rs = p.executeQuery();

        while (rs.next()) {
            int attachId = rs.getInt("attach_id");
            //int postId = rs.getInt("post_id");

            //create attachment for toPostId
            String addAttachmentSql = null;
            PreparedStatement createAttachmentStmnt = null;
            ResultSet rsAttachment = null;
            int createdAttachId = -1;
            if (sqlService.getVendor().equals("oracle")) {
                addAttachmentSql = "INSERT INTO jforum_attach (attach_id, post_id, privmsgs_id, user_id) "
                        + "VALUES (jforum_attach_seq.nextval, ?, ?, ?)";

                createAttachmentStmnt = connection.prepareStatement(addAttachmentSql);
                createAttachmentStmnt.setInt(1, toPostId);
                createAttachmentStmnt.setInt(2, 0);
                createAttachmentStmnt.setInt(3, jforumUserId);
                createAttachmentStmnt.executeUpdate();

                createAttachmentStmnt.close();

                String forumLastGeneratedTopicId = "SELECT jforum_attach_seq.currval FROM DUAL";
                createAttachmentStmnt = connection.prepareStatement(addAttachmentSql);
                rsAttachment = createAttachmentStmnt.executeQuery();

                if (rsAttachment.next()) {
                    createdAttachId = rsAttachment.getInt(1);
                }

                rsAttachment.close();
                createAttachmentStmnt.close();
            } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
                addAttachmentSql = "INSERT INTO jforum_attach (post_id, privmsgs_id, user_id) "
                        + "VALUES (?, ?, ?)";

                createAttachmentStmnt = connection.prepareStatement(addAttachmentSql,
                        Statement.RETURN_GENERATED_KEYS);
                createAttachmentStmnt.setInt(1, toPostId);
                createAttachmentStmnt.setInt(2, 0);
                createAttachmentStmnt.setInt(3, jforumUserId);
                createAttachmentStmnt.executeUpdate();

                rsAttachment = createAttachmentStmnt.getGeneratedKeys();
                if (rsAttachment.next()) {
                    createdAttachId = rsAttachment.getInt(1);
                }
                rsAttachment.close();
                createAttachmentStmnt.close();
            }

            String attachmentDetailSql = "SELECT attach_desc_id, attach_id, physical_filename, "
                    + "real_filename, description, mimetype, filesize, thumb, extension_id "
                    + " FROM jforum_attach_desc WHERE attach_id = ?";

            PreparedStatement p1 = connection.prepareStatement(attachmentDetailSql);
            p1.setInt(1, attachId);
            ResultSet rs1 = p1.executeQuery();
            if (rs1.next()) {
                int attachDescId = rs1.getInt("attach_desc_id");
                //int attachId = rs1.getInt("attach_id");
                String physicalFilename = rs1.getString("physical_filename");
                String realFilename = rs1.getString("real_filename");
                String description = rs1.getString("description");
                String mimetype = rs1.getString("mimetype");
                int filesize = rs1.getInt("filesize");
                int thumb = rs1.getInt("thumb");
                int extensionId = rs1.getInt("extension_id");

                try {
                    String attachmentsStoreDir = ServerConfigurationService.getString(ATTACHMENTS_STORE_DIR);

                    if (attachmentsStoreDir == null || attachmentsStoreDir.trim().length() == 0) {
                        //to get the file path is needed
                        String tomcatPath = getCatalina();
                        //if (logger.isInfoEnabled()) logger.info("Tomcat path is : "+ tomcatPath);
                        //load jforums SystemGlobal.properties and get attachments store directory
                        String propFile = tomcatPath
                                + "/webapps/etudes-jforum-tool/WEB-INF/config/SystemGlobals.properties";
                        Properties props = new Properties();
                        props.load(new FileInputStream(propFile));
                        /*attachment file path is the value of System global 
                         variable attachments.store.dir + physical_filename*/
                        attachmentsStoreDir = (String) props.get(ATTACHMENTS_STORE_DIR);

                        //String attachmentsUploadDir = (String)props.get("attachments.upload.dir");
                        File attSrorePath = new File(attachmentsStoreDir);
                        if (!attSrorePath.exists()) {
                            //assuming default path
                            attachmentsStoreDir = tomcatPath + "/webapps/etudes-jforum-tool/upload/";
                        }
                    }

                    String addAttachmentInfo = null;
                    //create attachment file
                    Calendar c = new GregorianCalendar();
                    c.setTimeInMillis(System.currentTimeMillis());
                    c.get(Calendar.YEAR);
                    int year = Calendar.getInstance().get(Calendar.YEAR);
                    int month = Calendar.getInstance().get(Calendar.MONTH) + 1;
                    int day = Calendar.getInstance().get(Calendar.DAY_OF_MONTH);

                    String dir = "" + year + "/" + month + "/" + day + "/";
                    new File(attachmentsStoreDir + "/" + dir).mkdirs();
                    String fileext = realFilename.trim().substring(realFilename.trim().lastIndexOf('.') + 1);
                    String physicalFilenameActpath = dir + IdManager.createUuid() + "_" + jforumUserId + "."
                            + fileext;
                    try {
                        saveAttachmentFile(attachmentsStoreDir + File.separator + physicalFilenameActpath,
                                new File(attachmentsStoreDir + File.separator + physicalFilename));
                    } catch (Exception e) {
                        if (logger.isErrorEnabled())
                            logger.error("processAttachments(): Error while saving attachemnt file");
                        e.printStackTrace();
                    }

                    PreparedStatement createAttachmentInfoStmnt = null;
                    ResultSet rsAttachmentInfo = null;
                    int createdAttachInfoId = -1;
                    if (sqlService.getVendor().equals("oracle")) {
                        addAttachmentInfo = "INSERT INTO jforum_attach_desc (attach_desc_id, attach_id, physical_filename, "
                                + "real_filename, description, mimetype, filesize, upload_time, thumb, extension_id ) "
                                + "VALUES (jforum_attach_desc_seq.nextval, ?, ?, ?, ?, ?, ?, SYSDATE, ?, ?)";

                        createAttachmentInfoStmnt = connection.prepareStatement(addAttachmentSql);
                        createAttachmentInfoStmnt.setInt(1, createdAttachId);
                        createAttachmentInfoStmnt.setString(2, physicalFilenameActpath);
                        createAttachmentInfoStmnt.setString(3, realFilename);
                        createAttachmentInfoStmnt.setString(4, description);
                        createAttachmentInfoStmnt.setString(5, mimetype);
                        createAttachmentInfoStmnt.setInt(6, filesize);
                        createAttachmentInfoStmnt.setInt(7, thumb);
                        createAttachmentInfoStmnt.setInt(8, extensionId);

                        createAttachmentInfoStmnt.close();

                        String lastGeneratedAttachInfoId = "SELECT jforum_attach_desc_seq.currval FROM DUAL";
                        createAttachmentInfoStmnt = connection.prepareStatement(lastGeneratedAttachInfoId);
                        rsAttachmentInfo = createAttachmentInfoStmnt.executeQuery();

                        if (rsAttachmentInfo.next()) {
                            createdAttachInfoId = rsAttachmentInfo.getInt(1);
                        }
                        rsAttachmentInfo.close();
                        createAttachmentInfoStmnt.close();
                    } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) {
                        addAttachmentInfo = "INSERT INTO jforum_attach_desc (attach_id, physical_filename, "
                                + "real_filename, description, mimetype, filesize, upload_time, thumb, extension_id ) "
                                + "VALUES (?, ?, ?, ?, ?, ?, NOW(), ?, ?)";

                        createAttachmentInfoStmnt = connection.prepareStatement(addAttachmentInfo,
                                Statement.RETURN_GENERATED_KEYS);
                        createAttachmentInfoStmnt.setInt(1, createdAttachId);
                        createAttachmentInfoStmnt.setString(2, physicalFilenameActpath);
                        createAttachmentInfoStmnt.setString(3, realFilename);
                        createAttachmentInfoStmnt.setString(4, description);
                        createAttachmentInfoStmnt.setString(5, mimetype);
                        createAttachmentInfoStmnt.setInt(6, filesize);
                        createAttachmentInfoStmnt.setInt(7, thumb);
                        createAttachmentInfoStmnt.setInt(8, extensionId);

                        createAttachmentInfoStmnt.executeUpdate();

                        rsAttachmentInfo = createAttachmentInfoStmnt.getGeneratedKeys();
                        if (rsAttachmentInfo.next()) {
                            createdAttachInfoId = rsAttachmentInfo.getInt(1);
                        }
                        rsAttachmentInfo.close();
                        createAttachmentInfoStmnt.close();
                    }
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            rs1.close();
            p1.close();

        }
        rs.close();
        p.close();
    } catch (SQLException e) {
        if (logger.isErrorEnabled())
            logger.error("processAttachments():Error while processing attachments : " + e.toString());
        e.printStackTrace();
    }

}

From source file:uk.ac.ed.epcc.webapp.model.data.Repository.java

/** Default insert operation that uses Generated Keys to 
 * obtain the unique id. Not all DBs support this but it is a good default.
 * /*from  www  .  j  a  v a2s.  c  o  m*/
 * If the id value is greater than zero this is taken as a required id to be inserted.
 * 
 * @param r
 * @return
 * @throws DataFault
 */
protected int insert(Record r) throws DataFault {
    if (READ_ONLY_FEATURE.isEnabled(ctx)) {
        return -1;
    }
    TimerService time = ctx.getService(TimerService.class);
    if (time != null) {
        time.startTimer(getTag() + "-insert");
    }
    int id;
    // Ok, now we should save the object in the database before
    // anything else happens
    StringBuilder query = new StringBuilder("INSERT INTO ");
    addTable(query, true);
    query.append(" (");
    StringBuilder query_values = new StringBuilder(") VALUES (");
    boolean atleastone = false;
    if (r.id > 0) {
        addUniqueName(query, false, true);
        query_values.append('?');
        atleastone = true;
    }
    for (Iterator<String> it = getFields().iterator(); it.hasNext();) {
        String field = it.next();
        FieldInfo info = getInfo(field);
        // as this is an insert we should skip null fields and allow the database default to
        // take precedence
        if (r.get(field) != null) {
            if (atleastone) {
                query.append(", ");
                query_values.append(", ");
            } else {
                atleastone = true;
            }
            info.addName(query, false, true);
            query_values.append('?');

        }
    }
    query.append(query_values.toString());
    query.append(')');
    if (!atleastone) {
        throw new DataFault("Insert with no values");
    }

    try {
        PreparedStatement stmt = sql.getConnection().prepareStatement(query.toString(),
                Statement.RETURN_GENERATED_KEYS);
        int pos = 1;
        if (r.id > 0) {
            stmt.setInt(pos, r.id);
            pos++;
        }
        for (Iterator it = getFields().iterator(); it.hasNext();) {
            String field = (String) it.next();
            if (r.get(field) != null) {
                r.setValue(query, stmt, pos, field);
                pos++;
            }
        }
        if (DatabaseService.LOG_INSERT_FEATURE.isEnabled(getContext())) {
            LoggerService serv = getContext().getService(LoggerService.class);
            if (serv != null) {
                serv.getLogger(getClass()).debug("insert query is " + query.toString());
            }
        }
        int count = stmt.executeUpdate();
        if (time != null) {
            time.stopTimer(getTag() + "-insert");
        }
        if (count != 1) {
            throw new DataFault("Wrong count from INSERT");
        }
        if (r.id > 0) {
            //know the id 
            return r.id;
        }
        if (use_id) {
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            } else {
                throw new DataFault("cannot retrieve auto_key");
            }
            rs.close();
            stmt.close();
            return id;
        } else {
            return 0;
        }
    } catch (SQLException e) {
        throw new DataFault("Insert exception " + query.toString(), e);
    }
}

From source file:org.osaf.cosmo.migrate.ZeroPointFiveToZeroPointSixMigration.java

private void migrateEvents(Connection conn, String dialect) throws Exception {
    PreparedStatement stmt = null;
    PreparedStatement insertStampStmt1 = null;
    PreparedStatement insertStampStmt2 = null;
    PreparedStatement insertEventStmt = null;
    PreparedStatement insertAttributeStmt1 = null;
    PreparedStatement insertAttributeStmt2 = null;
    PreparedStatement deleteContentDataStmt = null;
    PreparedStatement selectContentDataStmt = null;
    PreparedStatement updateEventStmt = null;
    PreparedStatement updatePropsStmt = null;
    PreparedStatement updateTimerangesStmt = null;

    ResultSet rs = null;//ww w . ja  v  a2 s.  c  om
    long count = 0;

    System.setProperty("ical4j.unfolding.relaxed", "true");
    CalendarBuilder calBuilder = new CalendarBuilder();

    VersionFourGenerator uidGenerator = new VersionFourGenerator();

    log.debug("begin migrateEvents()");

    try {
        stmt = conn.prepareStatement("select id, contentdataid from item where itemtype=?");
        stmt.setString(1, "event");

        insertStampStmt1 = conn
                .prepareStatement("insert into stamp (stamptype, itemid, isactive) values (?,?,1)");
        insertStampStmt1.setString(1, "event");
        insertStampStmt2 = conn
                .prepareStatement("insert into stamp (stamptype, itemid, id, isactive) values (?,?,?,1)");
        insertStampStmt2.setString(1, "event");

        insertAttributeStmt1 = conn.prepareStatement(
                "insert into attribute (attributetype, namespace, localname, itemid, textvalue, attributename) values (?,?,?,?,?,'a')");
        insertAttributeStmt2 = conn.prepareStatement(
                "insert into attribute (attributetype, namespace, localname, itemid, textvalue, id, attributename) values (?,?,?,?,?,?,'a')");
        insertAttributeStmt1.setString(1, "text");
        insertAttributeStmt2.setString(1, "text");
        insertAttributeStmt1.setString(2, "org.osaf.cosmo.model.NoteItem");
        insertAttributeStmt2.setString(2, "org.osaf.cosmo.model.NoteItem");
        insertAttributeStmt1.setString(3, "body");
        insertAttributeStmt2.setString(3, "body");

        deleteContentDataStmt = conn.prepareStatement("delete from content_data where id=?");
        selectContentDataStmt = conn.prepareStatement("select content from content_data where id=?");

        updateEventStmt = conn.prepareStatement(
                "update item set itemtype=?, contentdataid=?, contentlength=?, icaluid=?, displayname=? where id=?");
        updateEventStmt.setString(1, "note");
        updateEventStmt.setNull(2, Types.BIGINT);

        insertEventStmt = conn.prepareStatement("insert into event_stamp (stampid, icaldata) values (?,?)");
        updatePropsStmt = conn.prepareStatement("update cal_property_index set eventstampid=? where itemid=?");
        updateTimerangesStmt = conn
                .prepareStatement("update cal_timerange_index set eventstampid=? where itemid=?");

        rs = stmt.executeQuery();

        while (rs.next()) {
            count++;
            long itemId = rs.getLong(1);
            long contentDataId = rs.getLong(2);
            long stampId = 0;

            // Add record to stamp
            if ("MySQL5".equals(dialect)) {
                insertStampStmt1.setLong(2, itemId);
                insertStampStmt1.executeUpdate();
            } else {
                stampId = hibernateHelper.getNexIdUsingHiLoGenerator(conn);
                insertStampStmt2.setLong(2, itemId);
                insertStampStmt2.setLong(3, stampId);
                insertStampStmt2.executeUpdate();
            }

            // MySQL uses autogenerated id
            if ("MySQL5".equals(dialect)) {
                ResultSet generatedKeysRs = insertStampStmt1.getGeneratedKeys();
                generatedKeysRs.next();
                stampId = generatedKeysRs.getLong(1);
                generatedKeysRs.close();
            }

            // Get binary content data
            selectContentDataStmt.setLong(1, contentDataId);

            Calendar calendar = null;
            long icalLength = 0;
            String icalUid = null;
            String eventDesc = null;
            String eventSummary = null;
            ResultSet contentDataRs = selectContentDataStmt.executeQuery();
            if (contentDataRs.next()) {
                log.debug("itemid=" + itemId);
                Blob icalBlob = contentDataRs.getBlob(1);
                byte[] icalBytes = icalBlob.getBytes(1, (int) icalBlob.length());
                // have to parse data into Calendar to get right contentlength
                calendar = calBuilder.build(new ByteArrayInputStream(icalBytes));
                VEvent event = (VEvent) calendar.getComponents().getComponents(Component.VEVENT).get(0);

                // Now that we parsed, lets get the UID, DESCRIPTION, and
                // SUMMARY so we can update NoteItem, ContentItem
                Uid uid = event.getUid();

                // Handle the case where events don't have a UID (should be rare)
                if (uid != null)
                    icalUid = event.getUid().getValue();

                if (icalUid == null || "".equals(icalUid))
                    icalUid = null;

                // If there is no UID, create a new one
                if (icalUid == null) {
                    icalUid = uidGenerator.nextIdentifier().toString();
                    if (uid != null)
                        uid.setValue(icalUid);
                    else
                        event.getProperties().add(new Uid(icalUid));
                }

                Property p = event.getProperties().getProperty(Property.DESCRIPTION);
                if (p != null)
                    eventDesc = p.getValue();

                if ("".equals(eventDesc))
                    eventDesc = null;

                p = event.getProperties().getProperty(Property.SUMMARY);
                if (p != null)
                    eventSummary = p.getValue();

                if ("".equals(eventSummary))
                    eventSummary = null;

                // Make sure we can fit summary in displayname column
                if (eventSummary != null && eventSummary.length() >= 255)
                    eventSummary = eventSummary.substring(0, 254);

                // Calculate new length
                icalLength = calendar.toString().getBytes("UTF-8").length;
            }

            contentDataRs.close();

            // update item record with new contentLength, itemtype,
            // icaluid, and displayname
            updateEventStmt.setLong(3, icalLength);
            updateEventStmt.setString(4, icalUid);
            if (eventSummary != null)
                updateEventStmt.setString(5, eventSummary);
            else
                updateEventStmt.setNull(5, Types.VARCHAR);
            updateEventStmt.setLong(6, itemId);
            updateEventStmt.executeUpdate();

            // add event_stamp record
            insertEventStmt.setLong(1, stampId);
            insertEventStmt.setString(2, calendar.toString());

            insertEventStmt.executeUpdate();

            // If there is a DESCRIPTION, add a text attribute
            if (eventDesc != null) {
                if ("MySQL5".equals(dialect)) {
                    insertAttributeStmt1.setLong(4, itemId);
                    insertAttributeStmt1.setString(5, eventDesc);
                    insertAttributeStmt1.executeUpdate();
                } else {
                    long attributeId = hibernateHelper.getNexIdUsingHiLoGenerator(conn);
                    insertAttributeStmt2.setLong(4, itemId);
                    insertAttributeStmt2.setString(5, eventDesc);
                    insertAttributeStmt2.setLong(6, attributeId);
                    insertAttributeStmt2.executeUpdate();
                }
            }

            // Update calendar indexes to reflect item and stamp
            updatePropsStmt.setLong(1, stampId);
            updatePropsStmt.setLong(2, itemId);
            updatePropsStmt.executeUpdate();

            updateTimerangesStmt.setLong(1, stampId);
            updateTimerangesStmt.setLong(2, itemId);
            updateTimerangesStmt.executeUpdate();

            // no longer need content for events
            deleteContentDataStmt.setLong(1, contentDataId);
            deleteContentDataStmt.executeUpdate();
        }

    } finally {
        if (rs != null)
            rs.close();

        if (stmt != null)
            stmt.close();

        if (insertStampStmt1 != null)
            insertStampStmt1.close();

        if (insertStampStmt2 != null)
            insertStampStmt2.close();

        if (insertAttributeStmt1 != null)
            insertAttributeStmt1.close();

        if (insertAttributeStmt2 != null)
            insertAttributeStmt2.close();

        if (deleteContentDataStmt != null)
            deleteContentDataStmt.close();

        if (selectContentDataStmt != null)
            selectContentDataStmt.close();

        if (updateEventStmt != null)
            updateEventStmt.close();

        if (insertEventStmt != null)
            insertEventStmt.close();

        if (updatePropsStmt != null)
            updatePropsStmt.close();

        if (updateTimerangesStmt != null)
            updateTimerangesStmt.close();
    }

    log.debug("processed " + count + " events");
}

From source file:org.lockss.subscription.SubscriptionManager.java

/**
 * Adds a subscription to the database.//from  www . j  a v  a2 s  .  c  o m
 * 
 * @param conn
 *          A Connection with the database connection to be used.
 * @param publicationSeq
 *          A Long with the identifier of the publication.
 * @param providerSeq
 *          A Long with the identifier of the provider.
 * @return a Long with the identifier of the subscription just added.
 * @throws DbException
 *           if any problem occurred accessing the database.
 */
Long persistSubscription(Connection conn, Long publicationSeq, Long providerSeq) throws DbException {
    final String DEBUG_HEADER = "persistSubscription(): ";
    if (log.isDebug2()) {
        log.debug2(DEBUG_HEADER + "publicationSeq = " + publicationSeq);
        log.debug2(DEBUG_HEADER + "providerSeq = " + providerSeq);
    }

    PreparedStatement insertSubscription = dbManager.prepareStatement(conn, INSERT_SUBSCRIPTION_QUERY,
            Statement.RETURN_GENERATED_KEYS);

    ResultSet resultSet = null;
    Long subscriptionSeq = null;

    try {
        // Skip auto-increment key field #0
        insertSubscription.setLong(1, publicationSeq);
        insertSubscription.setLong(2, providerSeq);
        dbManager.executeUpdate(insertSubscription);
        resultSet = insertSubscription.getGeneratedKeys();

        if (!resultSet.next()) {
            log.error("Unable to create SUBSCRIPTION table row: publicationSeq = " + publicationSeq
                    + ", providerSeq = " + providerSeq + " - No keys were generated.");
            if (log.isDebug2())
                log.debug2(DEBUG_HEADER + "subscriptionSeq = null");
            return null;
        }

        subscriptionSeq = resultSet.getLong(1);
        if (log.isDebug3())
            log.debug3(DEBUG_HEADER + "Added subscriptionSeq = " + subscriptionSeq);
    } catch (SQLException sqle) {
        log.error("Cannot insert subscription", sqle);
        log.error("SQL = '" + INSERT_SUBSCRIPTION_QUERY + "'.");
        log.error("publicationSeq = " + publicationSeq);
        log.error("providerSeq = " + providerSeq);
        throw new DbException("Cannot insert subscription", sqle);
    } finally {
        DbManager.safeCloseResultSet(resultSet);
        DbManager.safeCloseStatement(insertSubscription);
    }

    if (log.isDebug2())
        log.debug2(DEBUG_HEADER + "subscriptionSeq = " + subscriptionSeq);
    return subscriptionSeq;
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///w ww .j  a va2 s. co  m
public void insertWikiGroup(WikiGroup group, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        int index = 1;
        if (!this.autoIncrementPrimaryKeys()) { // && group.getGroupId()>0) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_GROUP);
            int groupId = this.nextWikiGroupId(conn);
            group.setGroupId(groupId);
            stmt.setInt(index++, group.getGroupId());
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_GROUP_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        stmt.setString(index++, group.getName());
        stmt.setString(index++, group.getDescription());
        stmt.executeUpdate();
        if (this.autoIncrementPrimaryKeys()) {
            rs = stmt.getGeneratedKeys();
            if (!rs.next()) {
                throw new SQLException("Unable to determine auto-generated ID for database record");
            }
            group.setGroupId(rs.getInt(1));
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
    }
}