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.registry.core.jdbc.dao.JDBCRatingsDAO.java

public void addRating(ResourceImpl resourceImpl, String userID, int rating) throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();

    PreparedStatement ps1 = null, ps2 = null, ps3 = null;
    ResultSet result = null;/*from w  w w .  j  av  a  2  s.  com*/
    try {

        String sql1 = "INSERT INTO REG_RATING (REG_RATING, REG_USER_ID, REG_RATED_TIME, "
                + "REG_TENANT_ID) VALUES (?,?,?,?)";
        String sql2 = "SELECT MAX(REG_ID) FROM REG_RATING";
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps1 = conn.prepareStatement(sql1, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, DatabaseConstants.ID_FIELD) });
        } else {
            ps1 = conn.prepareStatement(sql1);
        }
        ps1.setInt(1, rating);
        ps1.setString(2, userID);
        long now = System.currentTimeMillis();
        ps1.setDate(3, new Date(now));
        ps1.setInt(4, CurrentSession.getTenantId());
        if (returnsGeneratedKeys) {
            ps1.executeUpdate();
            result = ps1.getGeneratedKeys();
        } else {
            synchronized (ADD_RATING_LOCK) {
                ps1.executeUpdate();
                ps2 = conn.prepareStatement(sql2);
                result = ps2.executeQuery();
            }
        }
        if (result.next()) {
            int rateID = result.getInt(1);

            String sql3 = "INSERT INTO REG_RESOURCE_RATING (REG_RATING_ID, REG_PATH_ID, "
                    + "REG_RESOURCE_NAME, REG_TENANT_ID) " + "VALUES(?,?,?,?)";
            ps3 = conn.prepareStatement(sql3);

            ps3.setInt(1, rateID);
            ps3.setInt(2, resourceImpl.getPathID());
            ps3.setString(3, resourceImpl.getName());
            ps3.setInt(4, CurrentSession.getTenantId());

            ps3.executeUpdate();

        }

    } catch (SQLException e) {

        String msg = "Failed to rate resource " + resourceImpl.getPath() + " with rating " + rating + ". "
                + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    try {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    } finally {
                        if (ps3 != null) {
                            ps3.close();
                        }
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCTagsDAO.java

public void addTaggings(ResourceImpl resource, TaggingDO[] taggingDOs) throws RegistryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    long now = System.currentTimeMillis();

    for (TaggingDO taggingDO : taggingDOs) {

        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        ResultSet result = null;/*from   w  w w. ja  v a  2  s .  c o  m*/
        try {
            String sql = "INSERT INTO REG_TAG (REG_TAG_NAME, REG_USER_ID, REG_TAGGED_TIME, "
                    + "REG_TENANT_ID) VALUES (?,?,?,?)";

            String dbProductName = conn.getMetaData().getDatabaseProductName();
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_ID") });
            ps.setString(1, taggingDO.getTagName());
            ps.setString(2, taggingDO.getTaggedUserName());
            ps.setDate(3, new Date(now));
            ps.setInt(4, CurrentSession.getTenantId());

            ps.executeUpdate();

            result = ps.getGeneratedKeys();
            if (result.next()) {
                int tagId = result.getInt(1);

                String sql2 = "INSERT INTO REG_RESOURCE_TAG (REG_TAG_ID, REG_PATH_ID, "
                        + "REG_RESOURCE_NAME, REG_TENANT_ID) " + "VALUES(?,?,?,?)";
                ps2 = conn.prepareStatement(sql2);

                ps2.setInt(1, tagId);
                ps2.setInt(2, resource.getPathID());
                ps2.setString(3, resource.getName());
                ps2.setInt(4, CurrentSession.getTenantId());

                ps2.executeUpdate();
            }
        } catch (SQLException e) {
            String msg = "Failed to add tags to resource " + resource.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            // closing open prepared statements & result sets before moving on to next iteration
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    try {
                        if (ps != null) {
                            ps.close();
                        }
                    } finally {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }
}

From source file:org.wso2.carbon.device.mgt.core.dao.impl.DeviceTypeSensorDAOImpl.java

@Override
public void addDeviceTypeSensor(DeviceTypeSensorTransactionObject deviceTypeSensorTransactionObject)
        throws DeviceManagementDAOException {
    Connection conn;//w  ww  . jav  a 2 s.c o  m
    ResultSet rs;
    PreparedStatement stmt = null;

    int deviceTypeSensorId;
    int deviceTypeId = deviceTypeSensorTransactionObject.getDeviceTypeId();
    DeviceTypeSensor deviceTypeSensor = deviceTypeSensorTransactionObject.getDeviceTypeSensor();
    String deviceTypeSensorName = deviceTypeSensor.getUniqueSensorName();
    String deviceTypeSensorTypeTAG = deviceTypeSensor.getSensorTypeTAG();
    Map<String, String> deviceTypeSensorProperties = deviceTypeSensor.getStaticProperties();

    try {
        conn = this.getConnection();
        String insertDBQuery = "INSERT INTO DM_DEVICE_TYPE_SENSOR (" + "SENSOR_NAME," + "DEVICE_TYPE_ID,"
                + "DESCRIPTION," + "SENSOR_TYPE," + "STREAM_DEFINITION) " + "VALUES (?,?,?,?,?)";

        stmt = conn.prepareStatement(insertDBQuery, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, deviceTypeSensorName);
        stmt.setInt(2, deviceTypeId);
        stmt.setString(3, deviceTypeSensor.getDescription());
        stmt.setString(4, deviceTypeSensorTypeTAG);
        stmt.setString(5, deviceTypeSensor.getStreamDefinition());
        int rows = stmt.executeUpdate();

        if (rows > 0) {
            if (log.isDebugEnabled()) {
                log.debug("Details of DeviceTypeSensor [" + deviceTypeSensorName + "] of "
                        + "DeviceType with Id [" + deviceTypeId + "] was added successfully.");
            }

            rs = stmt.getGeneratedKeys();
            rs.next();
            deviceTypeSensorId = rs.getInt(1);

            if (deviceTypeSensorProperties != null
                    && !addDeviceTypeSensorProperties(deviceTypeSensorId, deviceTypeSensorProperties)) {
                String msg = "Error occurred whilst adding Properties of the registered new DeviceTypeSensor "
                        + "[" + deviceTypeSensorName + "] whose Id is [" + deviceTypeSensorId + "]";
                log.error(msg);
                throw new DeviceManagementDAOException(msg);
            }
        }
    } catch (SQLException e) {
        String msg = "Error occurred whilst registering a new DeviceTypeSensor [" + deviceTypeSensorName
                + "] for the DeviceType with Id [" + deviceTypeId + "]";
        log.error(msg, e);
        throw new DeviceManagementDAOException(msg, e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
}

From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java

@Override
public List<PropertyPair> executeInsertWithGeneratedKeys(PlasmaType type, StringBuilder sql,
        Map<String, PropertyPair> values) {
    List<PropertyPair> resultKeys = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    ResultSet generatedKeys = null;
    try {/*from  w w w.ja v  a2  s  .  c o  m*/

        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }

        statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);

        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();
            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }
        }

        statement.execute();
        generatedKeys = statement.getGeneratedKeys();
        ResultSetMetaData rsMeta = generatedKeys.getMetaData();
        int numcols = rsMeta.getColumnCount();
        if (log.isDebugEnabled())
            log.debug("returned " + numcols + " keys");

        if (generatedKeys.next()) {
            // FIXME; without metadata describing which properties
            // are actually a sequence, there is guess work
            // involved in matching the values returned
            // automatically from PreparedStatment as they
            // are anonymous in terms of the column names
            // making it impossible to match them to a metadata
            // property.
            List<Property> pkPropList = type.findProperties(KeyType.primary);
            if (pkPropList == null || pkPropList.size() == 0)
                throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'");
            if (pkPropList.size() > 1)
                throw new DataAccessException("multiple pri-key properties found for type '" + type.getName()
                        + "' - cannot map to generated keys");
            PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0);
            // FIXME: need to find properties per column by physical name
            // alias
            // in case where multiple generated pri-keys
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                if (log.isDebugEnabled())
                    log.debug("returned key column '" + columnName + "'");
                int columnType = rsMeta.getColumnType(i);
                Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop);
                PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                resultKeys.add(pair);
            }
        }
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
    }

    return resultKeys;
}

From source file:com.adanac.module.blog.dao.CommentDao.java

public Integer save(final Integer articleId, final String visitorIp, final Date commentDate,
        final String content, final String username, final String resourceUsername, final String resourceId,
        final Integer referenceCommentId) {
    return execute(new TransactionalOperation<Integer>() {
        @Override//  w ww. j a va2  s  .  co m
        public Integer doInConnection(Connection connection) {
            try {
                PreparedStatement statement = null;
                if (referenceCommentId == null) {
                    statement = connection.prepareStatement(
                            "insert into comments (visitor_ip,city,content,article_id,"
                                    + "create_date,username,resource_username,resource_id) values (?,?,?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                } else {
                    statement = connection.prepareStatement(
                            "insert into comments (visitor_ip,city,content,article_id,"
                                    + "create_date,username,resource_username,resource_id,reference_comment_id) values (?,?,?,?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                }
                statement.setString(1, visitorIp);
                statement.setString(2,
                        Configuration.isProductEnv() ? HttpApiHelper.getCity(visitorIp) : "?");
                statement.setString(3, content);
                statement.setInt(4, articleId);
                Date finalCommentDate = commentDate;
                if (commentDate == null) {
                    finalCommentDate = new Date();
                }
                statement.setTimestamp(5, new Timestamp(finalCommentDate.getTime()));
                statement.setString(6, username);
                statement.setString(7, resourceUsername);
                statement.setString(8, resourceId);
                if (referenceCommentId != null) {
                    statement.setInt(9, referenceCommentId);
                }
                int result = statement.executeUpdate();
                if (result > 0) {
                    ResultSet resultSet = statement.getGeneratedKeys();
                    if (resultSet.next()) {
                        return resultSet.getInt(1);
                    }
                }
            } catch (SQLException e) {
                error("save comments failed ...", e);
            }
            return null;
        }
    });
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCCommentsVersionDAO.java

/**
 * Method to persist comments.//from   ww  w  . jav a2  s.  c  om
 *
 * @param resource   the resource
 * @param commentDOs the comments to be persisted.
 *
 * @throws RegistryException if some error occurs while adding comments
 */
public void addComments(ResourceImpl resource, CommentDO[] commentDOs) throws RegistryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps1 = null, ps2 = null, ps3 = null;
    try {
        String sql1 = "INSERT INTO REG_COMMENT (REG_COMMENT_TEXT,"
                + "REG_USER_ID, REG_COMMENTED_TIME, REG_TENANT_ID) VALUES (?, ?, ?, ?)";
        String sql2 = "SELECT MAX(REG_ID) FROM REG_COMMENT";
        String sql3 = "INSERT INTO REG_RESOURCE_COMMENT (REG_COMMENT_ID, "
                + "REG_VERSION, REG_TENANT_ID) VALUES (?, ?, ?)";

        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps1 = conn.prepareStatement(sql1, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, DatabaseConstants.ID_FIELD) });
        } else {
            ps1 = conn.prepareStatement(sql1);
        }
        ps3 = conn.prepareStatement(sql3);

        for (CommentDO comment : commentDOs) {
            // prepare to execute query2 for the comments
            ps1.setString(1, comment.getCommentText());
            ps1.setString(2, comment.getCommentedUser());

            long now = System.currentTimeMillis();
            ps1.setTimestamp(3, new Timestamp(now));
            ps1.setInt(4, CurrentSession.getTenantId());
            ResultSet resultSet1;
            if (returnsGeneratedKeys) {
                ps1.executeUpdate();
                resultSet1 = ps1.getGeneratedKeys();
            } else {
                synchronized (ADD_COMMENT_LOCK) {
                    ps1.executeUpdate();
                    ps2 = conn.prepareStatement(sql2);
                    resultSet1 = ps2.executeQuery();
                }
            }
            try {
                if (resultSet1.next()) {
                    // setting the RES_COMMENTS_ID
                    int commentId = resultSet1.getInt(1);

                    ps3.setInt(1, commentId);
                    ps3.setLong(2, resource.getVersionNumber());
                    ps3.setInt(3, CurrentSession.getTenantId());

                    ps3.executeUpdate();
                    ps3.clearParameters();
                }
            } finally {
                if (resultSet1 != null) {
                    resultSet1.close();
                }
            }
            ps3.clearParameters();
        }

    } catch (SQLException e) {

        String msg = "Failed to add comments to the resource " + resource.getPath() + ". " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (ps1 != null) {
                    ps1.close();
                }
            } finally {
                try {
                    if (ps2 != null) {
                        ps2.close();
                    }
                } finally {
                    if (ps3 != null) {
                        ps3.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:fr.gael.dhus.database.liquibase.CopyProductImages.java

@Override
public void execute(Database database) throws CustomChangeException {
    PreparedStatement products = null;
    ResultSet products_res = null;
    JdbcConnection db_connection = (JdbcConnection) database.getConnection();
    try {/*from   w  ww. j a  v a2  s  .c  o  m*/
        products = db_connection.prepareStatement("SELECT ID,QUICKLOOK,THUMBNAIL FROM PRODUCTS");
        products_res = products.executeQuery();
        while (products_res.next()) {
            PreparedStatement copy_blob_stmt = null;
            ResultSet generated_key_res = null;
            try {
                Blob ql = (Blob) products_res.getObject("QUICKLOOK");
                Blob th = (Blob) products_res.getObject("THUMBNAIL");
                Long pid = products_res.getLong("ID");

                // No images: add false flags
                if ((ql == null) && (th == null)) {
                    PreparedStatement product_flags_stmt = null;
                    // Add related flags
                    try {
                        product_flags_stmt = db_connection.prepareStatement(
                                "UPDATE PRODUCTS SET THUMBNAIL_FLAG=?,QUICKLOOK_FLAG=? " + "WHERE ID=?");
                        product_flags_stmt.setBoolean(1, false);
                        product_flags_stmt.setBoolean(2, false);
                        product_flags_stmt.setLong(3, pid);
                        product_flags_stmt.execute();
                    } finally {
                        if (product_flags_stmt != null)
                            try {
                                product_flags_stmt.close();
                            } catch (Exception e) {
                                logger.warn("Cannot close Statement !");
                            }
                    }
                    continue;
                }

                copy_blob_stmt = db_connection.prepareStatement(
                        "INSERT INTO PRODUCT_IMAGES (QUICKLOOK,THUMBNAIL) " + "VALUES (?,?)",
                        Statement.RETURN_GENERATED_KEYS);

                copy_blob_stmt.setBlob(1, ql);
                copy_blob_stmt.setBlob(2, th);
                copy_blob_stmt.execute();

                generated_key_res = copy_blob_stmt.getGeneratedKeys();
                if (generated_key_res.next()) {
                    PreparedStatement set_product_image_id_stmt = null;
                    Long iid = generated_key_res.getLong(1);

                    // Add ProductImages "IMAGES" entry in product
                    try {
                        set_product_image_id_stmt = db_connection
                                .prepareStatement("UPDATE PRODUCTS SET IMAGES_ID=?, THUMBNAIL_FLAG=?, "
                                        + "QUICKLOOK_FLAG=?  WHERE ID=?");
                        set_product_image_id_stmt.setLong(1, iid);
                        set_product_image_id_stmt.setBoolean(2, th != null);
                        set_product_image_id_stmt.setBoolean(3, ql != null);
                        set_product_image_id_stmt.setLong(4, pid);
                        set_product_image_id_stmt.execute();
                    } finally {
                        if (set_product_image_id_stmt != null)
                            try {
                                set_product_image_id_stmt.close();
                            } catch (Exception e) {
                                logger.warn("Cannot close Statement !");
                            }
                    }
                } else {
                    logger.error("Cannot retrieve Image primary key for " + "product ID #"
                            + products_res.getLong("ID"));
                }
            } finally {
                if (generated_key_res != null)
                    try {
                        generated_key_res.close();
                    } catch (Exception e) {
                        logger.warn("Cannot close ResultSet !");
                    }
                if (copy_blob_stmt != null)
                    try {
                        copy_blob_stmt.close();
                    } catch (Exception e) {
                        logger.warn("Cannot close Statement !");
                    }
            }
        }
    } catch (Exception e) {
        throw new CustomChangeException("Cannot move Blobs from product", e);
    } finally {
        if (products_res != null) {
            try {
                products_res.close();
            } catch (Exception e) {
                logger.warn("Cannot close ResultSet !");
            }
        }
        if (products != null) {
            try {
                products.close();
            } catch (Exception e) {
                logger.warn("Cannot close Statement !");
            }
        }
        //if (db_connection!=null) try { db_connection.close (); }
        // catch (Exception e) {}
    }
}

From source file:iddb.runtime.db.model.dao.impl.mysql.PenaltyDAOImpl.java

@Override
public void save(Penalty penalty) {
    String sql;//from www .  jav  a2s  .  co m
    if (penalty.getKey() == null) {
        sql = "insert into penalty (playerid, adminid, type, reason, duration, synced, active, created, updated, expires) values (?,?,?,?,?,?,?,?,?,?)";
    } else {
        sql = "update penalty set playerid = ?," + "adminid = ?," + "type = ?," + "reason = ?,"
                + "duration = ?," + "synced = ?," + "active = ?," + "created = ?," + "updated = ?,"
                + "expires = ? where id = ? limit 1";
    }
    Connection conn = null;
    try {
        conn = ConnectionFactory.getMasterConnection();
        PreparedStatement st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        st.setLong(1, penalty.getPlayer());
        if (penalty.getAdmin() != null)
            st.setLong(2, penalty.getAdmin());
        else
            st.setNull(2, Types.INTEGER);
        st.setInt(3, penalty.getType().intValue());
        if (penalty.getReason() != null)
            st.setString(4, penalty.getReason());
        else
            st.setNull(4, Types.VARCHAR);
        if (penalty.getDuration() == null)
            penalty.setDuration(0L);
        st.setLong(5, penalty.getDuration());
        st.setBoolean(6, penalty.getSynced());
        st.setBoolean(7, penalty.getActive());
        if (penalty.getCreated() == null)
            penalty.setCreated(new Date());
        if (penalty.getUpdated() == null)
            penalty.setUpdated(new Date());
        st.setTimestamp(8, new java.sql.Timestamp(penalty.getCreated().getTime()));
        st.setTimestamp(9, new java.sql.Timestamp(penalty.getUpdated().getTime()));
        st.setTimestamp(10, new java.sql.Timestamp(
                DateUtils.addMinutes(penalty.getCreated(), penalty.getDuration().intValue()).getTime()));
        if (penalty.getKey() != null)
            st.setLong(11, penalty.getKey());
        st.executeUpdate();
        if (penalty.getKey() == null) {
            ResultSet rs = st.getGeneratedKeys();
            if (rs != null && rs.next()) {
                penalty.setKey(rs.getLong(1));
            } else {
                logger.warn("Couldn't get id for penalty player id {}", penalty.getPlayer());
            }
        }
    } catch (SQLException e) {
        logger.error("Save: {}", e);
    } catch (IOException e) {
        logger.error("Save: {}", e);
    } finally {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
}

From source file:com.adanac.module.blog.dao.ArticleDao.java

public Integer saveOrUpdate(String resourceId, String subject, String createDate, Status status,
        String username, Integer accessTimes, Integer goodTimes, String html, String content) {
    return execute((TransactionalOperation<Integer>) connection -> {
        String selectSql = "select id,status from articles where resource_id=?";
        String insertSql = "insert into articles (resource_id,username,icon,create_date,"
                + "access_times,good_times,subject,html,content,status) values (?,?,?,?,?,?,?,?,?,?)";
        String updateSql = "update articles set subject=?,html=?,content=?,icon=?,status=? where resource_id=? and type=0 ";
        try {/*w w w. j a v  a  2 s .co m*/
            PreparedStatement statement = connection.prepareStatement(selectSql);
            statement.setString(1, resourceId);
            ResultSet resultSet = statement.executeQuery();
            Boolean exists = false;
            Status currentStatus = Status.draft;
            Integer id = null;
            if (resultSet.next()) {
                exists = true;
                currentStatus = Status.valueOf(resultSet.getInt("status"));
                id = resultSet.getInt("id");
            }
            PreparedStatement saveOrUpdate = null;
            if (!exists) {
                saveOrUpdate = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                saveOrUpdate.setString(1, resourceId);
                saveOrUpdate.setString(2, username);
                saveOrUpdate.setString(3, ImageUtil.randomArticleImage(subject) == null ? ""
                        : ImageUtil.randomArticleImage(subject));
                saveOrUpdate.setString(4, createDate);
                saveOrUpdate.setInt(5, accessTimes);
                saveOrUpdate.setInt(6, goodTimes);
                saveOrUpdate.setString(7, subject);
                saveOrUpdate.setString(8, html);
                saveOrUpdate.setString(9, content);
                saveOrUpdate.setInt(10, status.getIntValue());
            } else {
                saveOrUpdate = connection.prepareStatement(updateSql);
                saveOrUpdate.setString(1, subject);
                saveOrUpdate.setString(2, html);
                saveOrUpdate.setString(3, content);
                saveOrUpdate.setString(4, ImageUtil.randomArticleImage(subject));
                saveOrUpdate.setInt(5,
                        currentStatus == Status.published ? currentStatus.getIntValue() : status.getIntValue());
                saveOrUpdate.setString(6, resourceId);
            }
            int result = saveOrUpdate.executeUpdate();
            if (!exists && result > 0) {
                ResultSet keyResultSet = saveOrUpdate.getGeneratedKeys();
                if (keyResultSet.next()) {
                    id = keyResultSet.getInt(1);
                }
            }
            return id;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    });
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public ServiceInfo addServiceInfo(String name, File file) {
    ServiceInfo service = null;//ww w .j a  v  a2  s.co  m
    if (file == null || name == null) {
        Log.e(TAG, "One cannot create a serviceInfo where the file is null, or name is null");
        return null;
    }
    try {
        checkOpenness();
        connection.setAutoCommit(false);
        String sqlInsert = "INSERT INTO " + IotHubDataHandler.TABLE_SERVICE_INFO + "("
                + IotHubDataHandler.KEY_SERVICE_INFO_SERVICE_NAME + ","
                + IotHubDataHandler.KEY_SERVICE_INFO_FILENAME + ") VALUES (?,?)";
        PreparedStatement psInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS);
        psInsert.setString(1, name);
        psInsert.setString(2, file.getName());
        psInsert.executeUpdate();
        ResultSet genKeys = psInsert.getGeneratedKeys();
        if (genKeys.next()) {
            long insertId = genKeys.getLong(1);
            //At point we should have everything set so it is time to retrieve the plugin from the database
            //Log.d(TAG, "Now i will try to collect the service info " + name + " that was just added to the db");
            service = getServiceInfo(insertId);
            if (service == null) {
                Log.e(TAG, "The service info " + name + " should not be null");
            }
        } else {
            Log.e(TAG, "The insert of service info " + name + " did not work");
        }
        genKeys.close();
        psInsert.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        service = null;
    }
    try {
        if (service == null) {
            connection.rollback();
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return service;
}