Example usage for java.sql PreparedStatement executeUpdate

List of usage examples for java.sql PreparedStatement executeUpdate

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

Introduction

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

Prototype

int executeUpdate() throws SQLException;

Source Link

Document

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Usage

From source file:com.concursive.connect.web.modules.common.social.rating.dao.Rating.java

/**
 * Deletes just a specific user's rating, and updates the parent table with a proper calculation
 *
 * @param db//from   w  w  w  .jav  a 2  s .c om
 * @param userId
 * @param objectId
 * @param table
 * @param uniqueField
 * @throws SQLException
 */
public static synchronized void delete(Connection db, int userId, int objectId, String table,
        String uniqueField) throws SQLException {
    boolean commit = false;
    try {
        commit = db.getAutoCommit();
        if (commit) {
            db.setAutoCommit(false);
        }

        // Get the project's rating
        int ratingCount = queryObjectRatingCount(db, objectId, table, uniqueField);

        // Get the user's rating
        int thisRating = queryUserRating(db, userId, objectId, table, uniqueField);

        // Delete the user's rating
        PreparedStatement pst = db.prepareStatement(
                "DELETE FROM " + table + "_rating " + "WHERE " + uniqueField + " = ? " + "AND enteredby = ? ");
        pst.setInt(1, objectId);
        pst.setInt(2, userId);
        int deleteCount = pst.executeUpdate();
        pst.close();

        if (deleteCount > 0 && thisRating != INAPPROPRIATE_COMMENT) {
            // Update the parent table's rating information
            // NOTE: make sure not to divide by 0
            pst = db.prepareStatement("UPDATE " + table + " "
                    + "SET rating_count = rating_count - ?, rating_value = rating_value - ?, "
                    + (ratingCount == 0 ? "rating_avg = 0 "
                            : "rating_avg = ((rating_value - ?) / (rating_count - ?)) ")
                    + "WHERE " + uniqueField + " = ? ");
            int i = 0;
            pst.setInt(++i, 1);
            pst.setInt(++i, thisRating);
            if (ratingCount > 1) {
                pst.setInt(++i, thisRating);
                pst.setInt(++i, 1);
            }
            pst.execute();
            pst.close();
        }
    } catch (Exception e) {
        if (commit) {
            db.rollback();
        }
        throw new SQLException(e.getMessage());
    } finally {
        if (commit) {
            db.setAutoCommit(true);
        }
    }
}

From source file:FacultyAdvisement.StudentRepository.java

public static void update(DataSource ds, Student student) throws SQLException {
    String studentSQL = "UPDATE STUDENT SET STUID = ?, FIRSTNAME = ?, LASTNAME = ?, MAJORCODE = ?, PHONE = ? WHERE EMAIL = ?";

    if (ds == null) {
        throw new SQLException("ds is null; Can't get data source");
    }//from ww  w  .  j  a  v a 2  s. c  o  m

    Connection conn = ds.getConnection();

    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }

    try {
        //Student Information
        PreparedStatement sqlStatement = conn.prepareStatement(studentSQL);
        sqlStatement.setString(1, student.getId());
        sqlStatement.setString(2, student.getFirstName());
        sqlStatement.setString(3, student.getLastName());
        sqlStatement.setString(4, student.getMajorCode());
        sqlStatement.setString(5, student.getPhoneNumber());
        sqlStatement.setString(6, student.getUsername());

        sqlStatement.executeUpdate();

    } finally {
        conn.close();
    }
}

From source file:com.dynamobi.network.DynamoNetworkUdr.java

/**
 * Shows all available packages we have available.
 *///w  w w  .j a va 2 s.c o  m
public static void showPackages(PreparedStatement resultInserter) throws SQLException {
    List<RepoInfo> repos = getRepoUrls();
    for (RepoInfo inf : repos) {
        String repo = inf.url;
        if (!inf.accessible) {
            resultInserter.setString(1, repo);
            resultInserter.setBoolean(2, inf.accessible);
            resultInserter.executeUpdate();
            continue;
        }

        JSONObject repo_data = downloadMetadata(repo);
        JSONArray pkgs = (JSONArray) repo_data.get("packages");
        for (JSONObject obj : (List<JSONObject>) pkgs) {
            String jar = jarName(obj);
            String status = getStatus(jar);
            int c = 0;
            resultInserter.setString(++c, repo);
            resultInserter.setBoolean(++c, inf.accessible);
            resultInserter.setString(++c, obj.get("type").toString());
            resultInserter.setString(++c, obj.get("publisher").toString());
            resultInserter.setString(++c, obj.get("package").toString());
            resultInserter.setString(++c, obj.get("version").toString());
            resultInserter.setString(++c, jar);
            resultInserter.setString(++c, status);
            resultInserter.setString(++c, obj.get("depend").toString());
            resultInserter.setString(++c, obj.get("rdepend").toString());
            resultInserter.executeUpdate();
        }
    }
}

From source file:com.concursive.connect.web.modules.login.utils.UserUtils.java

private static boolean updateProfileProjectId(Connection db, User user, Project project) throws SQLException {
    PreparedStatement pst = db
            .prepareStatement("UPDATE users " + "SET profile_project_id = ? " + "WHERE user_id = ? ");
    int i = 0;//from   w w  w. ja va2s  .  com
    pst.setInt(++i, project.getId());
    pst.setInt(++i, user.getId());
    int count = pst.executeUpdate();
    pst.close();
    // Relate the two objects
    user.setProfileProjectId(project.getId());
    CacheUtils.invalidateValue(Constants.SYSTEM_USER_CACHE, user.getId());
    return count == 1;
}

From source file:com.concursive.connect.web.modules.login.utils.UserUtils.java

public static boolean detachProfile(Connection db, int projectId) throws SQLException {
    PreparedStatement pst = db.prepareStatement(
            "UPDATE users " + "SET profile_project_id = ? " + "WHERE profile_project_id = ? ");
    int i = 0;/*w ww .j  a  v  a2 s.co  m*/
    DatabaseUtils.setInt(pst, ++i, -1);
    pst.setInt(++i, projectId);
    int count = pst.executeUpdate();
    pst.close();
    return count == 1;
}

From source file:edu.lafayette.metadb.model.userman.UserManDAO.java

/**
 * Update the password of a user.//from w  w  w.  java  2  s . c o  m
 * 
 * @param userName The username of the user to update.
 * @param newPassword The new password for the user to update.
 * @return true if the user's password is updated successfully, false otherwise
 */
public static boolean updatePassword(String userName, String newPassword) {
    Connection conn = Conn.initialize(); // Establish connection
    if (conn != null) {
        try {
            PreparedStatement updateUser = conn.prepareStatement(UPDATE_USER_PASSWORD);
            newPassword = encryptPassword(newPassword);

            updateUser.setString(1, newPassword);
            updateUser.setString(2, userName);
            updateUser.executeUpdate();

            updateUser.close();
            conn.close(); // Close statement and connection

            return true;

        } catch (Exception e) {
            MetaDbHelper.logEvent(e);

        }
    }
    return false;

}

From source file:FacultyAdvisement.StudentRepository.java

public static void create(DataSource ds, Student student) throws SQLException {
    String studentSQL = "INSERT INTO STUDENT(STUID, EMAIL, FIRSTNAME, LASTNAME, MAJORCODE, PHONE, ADVISED) "
            + "VALUES (?, ?, ?, ?, ?, ?, \'false\')";
    String userSQL = "INSERT INTO USERTABLE(PASSWORD, USERNAME, VERIFIED) VALUES (?, ?, ?)"; //haseeb was here

    String groupSQL = "INSERT INTO GROUPTABLE(GROUPNAME, USERNAME) VALUES (\'customergroup\', ?)";

    if (ds == null) {
        throw new SQLException("ds is null; Can't get data source");
    }// w  ww  .ja va2 s .  com

    Connection conn = ds.getConnection();

    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }

    try {

        //Here we execute three SQL statements
        //Student Information
        PreparedStatement sqlStatement = conn.prepareStatement(studentSQL);

        sqlStatement.setString(1, student.getId());
        sqlStatement.setString(2, student.getUsername());
        sqlStatement.setString(3, student.getFirstName());
        sqlStatement.setString(4, student.getLastName());
        sqlStatement.setString(5, student.getMajorCode());
        sqlStatement.setString(6, student.getPhoneNumber());

        sqlStatement.executeUpdate();

        //user credentials
        sqlStatement = conn.prepareStatement(userSQL);

        //Encrypt the pssword into SHA-256
        sqlStatement.setString(1, SHA256Encrypt.encrypt(student.getPassword()));

        sqlStatement.setString(2, student.getUsername());
        sqlStatement.setString(3, "false");
        sqlStatement.execute();

        //Group Table
        sqlStatement = conn.prepareStatement(groupSQL);
        sqlStatement.setString(1, student.getUsername());
        sqlStatement.execute();
    } finally {
        conn.close();
    }

}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public static void testCoalesce() throws SQLException {
    Statement stat = conn.createStatement();
    stat.executeUpdate("create table test(tm timestamp)");
    stat.executeUpdate("insert into test values(current_timestamp)");
    PreparedStatement prep = conn.prepareStatement("update test set tm = coalesce(?,tm)");
    prep.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
    prep.executeUpdate();
    stat.executeUpdate("drop table test");
}

From source file:module.entities.NameFinder.DB.java

/**
 * Starts the activity log/*w ww .j a  v a 2 s. c o  m*/
 *
 * @param startTime - The start time of the crawling procedure
 * @return - The activity's log id
 * @throws java.sql.SQLException
 */
public static int LogRegexFinder(long startTime) throws SQLException {
    String insertLogSql = "INSERT INTO log.activities (module_id, start_date, end_date, status_id, message) VALUES (?,?,?,?,?)";
    PreparedStatement prepLogCrawlStatement = connection.prepareStatement(insertLogSql,
            Statement.RETURN_GENERATED_KEYS);
    prepLogCrawlStatement.setInt(1, 4);
    prepLogCrawlStatement.setTimestamp(2, new java.sql.Timestamp(startTime));
    prepLogCrawlStatement.setTimestamp(3, null);
    prepLogCrawlStatement.setInt(4, 1);
    prepLogCrawlStatement.setString(5, null);
    prepLogCrawlStatement.executeUpdate();
    ResultSet rsq = prepLogCrawlStatement.getGeneratedKeys();
    int crawlerId = 0;
    if (rsq.next()) {
        crawlerId = rsq.getInt(1);
    }
    prepLogCrawlStatement.close();
    return crawlerId;
}

From source file:DeliverWork.java

private static String saveIntoWeed(RemoteFile remoteFile, String projectId)
        throws SQLException, UnsupportedEncodingException {
    String url = "http://59.215.226.174/WebDiskServerDemo/doc?doc_id="
            + URLEncoder.encode(remoteFile.getFileId(), "utf-8");
    CloseableHttpClient httpClient = null;
    CloseableHttpResponse response = null;
    String res = "";
    try {/*from  ww w  . j a v a 2 s.  c om*/
        httpClient = HttpClients.createSystem();
        // http(get?)
        HttpGet httpget = new HttpGet(url);
        response = httpClient.execute(httpget);
        HttpEntity result = response.getEntity();
        String fileName = remoteFile.getFileName();
        FileHandleStatus fileHandleStatus = getFileTemplate().saveFileByStream(fileName,
                new ByteArrayInputStream(EntityUtils.toByteArray(result)));
        System.out.println(fileHandleStatus);
        File file = new File();
        if (result != null && result.getContentType() != null && result.getContentType().getValue() != null) {
            file.setContentType(result.getContentType().getValue());
        } else {
            file.setContentType("application/error");
        }
        file.setDataId(Integer.parseInt(projectId));
        file.setName(fileName);
        if (fileName.contains(".bmp") || fileName.contains(".jpg") || fileName.contains(".jpeg")
                || fileName.contains(".png") || fileName.contains(".gif")) {
            file.setType(1);
        } else if (fileName.contains(".doc") || fileName.contains(".docx")) {
            file.setType(2);
        } else if (fileName.contains(".xlsx") || fileName.contains("xls")) {
            file.setType(3);
        } else if (fileName.contains(".pdf")) {
            file.setType(4);
        } else {
            file.setType(5);
        }
        String accessUrl = "/" + fileHandleStatus.getFileId().replaceAll(",", "/").concat("/").concat(fileName);
        file.setUrl(accessUrl);
        file.setSize(fileHandleStatus.getSize());
        file.setPostTime(new java.util.Date());
        file.setStatus(0);
        file.setEnumValue(findFileType(remoteFile.getFileType()));
        file.setResources(1);
        //            JdbcFactory jdbcFactoryChanye=new JdbcFactory("jdbc:mysql://127.0.0.1:3306/fp_guimin?useUnicode=true&characterEncoding=UTF-8","root","111111","com.mysql.jdbc.Driver");
        //            Connection connection = jdbcFactoryChanye.getConnection();
        DatabaseMetaData dmd = connection.getMetaData();
        PreparedStatement ps = connection.prepareStatement(insertFile, new String[] { "ID" });
        ps.setString(1, sdf.format(file.getPostTime()));
        ps.setInt(2, file.getType());
        ps.setString(3, file.getEnumValue());
        ps.setInt(4, file.getDataId());
        ps.setString(5, file.getUrl());
        ps.setString(6, file.getName());
        ps.setString(7, file.getContentType());
        ps.setLong(8, file.getSize());
        ps.setInt(9, file.getStatus());
        ps.setInt(10, file.getResources());
        ps.executeUpdate();
        if (dmd.supportsGetGeneratedKeys()) {
            ResultSet rs = ps.getGeneratedKeys();
            while (rs.next()) {
                System.out.println(rs.getLong(1));
            }
        }
        ps.close();
        res = "success";
    } catch (ClientProtocolException e) {
        e.printStackTrace();
        res = e.getMessage();
    } catch (IOException e) {
        e.printStackTrace();
        res = e.getMessage();
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        if (httpClient != null) {
            try {
                httpClient.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (response != null) {
            try {
                response.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    return res;
}