Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareStatement

Introduction

In this page you can find the example usage for java.sql Connection prepareStatement.

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

From source file:org.ulyssis.ipp.snapshot.Event.java

public static Optional<Event> load(Connection connection, long id) throws SQLException, IOException {
    try (PreparedStatement statement = connection
            .prepareStatement("SELECT \"data\",\"removed\" FROM \"events\" WHERE \"id\"=?")) {
        statement.setLong(1, id);/*from  ww w.ja v a 2  s. c  o  m*/
        ResultSet result = statement.executeQuery();
        if (result.next()) {
            String evString = result.getString("data");
            Event event = Serialization.getJsonMapper().readValue(evString, Event.class);
            event.id = id;
            event.removed = result.getBoolean("removed");
            return Optional.of(event);
        } else {
            return Optional.empty();
        }
    }
}

From source file:com.sql.EmailOutRelatedCase.java

public static List<EmailOutRelatedCaseModel> getRelatedCases(EmailOutModel eml) {
    List<EmailOutRelatedCaseModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;/*from w ww. jav a2  s  .  c  o m*/
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutRelatedCase WHERE emailOutId = ? ";

        ps = conn.prepareStatement(sql);
        ps.setInt(1, eml.getId());

        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutRelatedCaseModel item = new EmailOutRelatedCaseModel();
            item.setId(rs.getInt("id"));
            item.setEmailOutId(rs.getInt("emailOutId"));
            item.setCaseYear(rs.getString("caseYear"));
            item.setCaseType(rs.getString("caseType"));
            item.setCaseMonth(rs.getString("caseMonth"));
            item.setCaseNumber(rs.getString("caseNumber"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:de.iteratec.iteraplan.db.SqlScriptExecutor.java

public static void executeSqlStatements(final List<String> sqlStrings, Connection connection)
        throws SQLException {
    for (String statement : sqlStrings) {
        PreparedStatement stmt = null;
        try {/*from   ww w .  ja  v a2 s.  c o m*/
            stmt = connection.prepareStatement(statement);
            stmt.executeUpdate();
        } catch (SQLException se) {
            // ignore alter table errors because these might be ok, if tables do not exist
            if (!statement.trim().startsWith("alter table")) {
                LOGGER.error("database error when running db statement  '" + statement + "'.");
                throw se;
            }
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    connection.commit();
}

From source file:ch.newscron.referral.ReferralManager.java

/**
 * Calls the database to query for all rows in ShortURL table of database
 * @return a List of CustomerShortURL objects, consisting of all shortURL entries in table
 *///  w  w  w  . j a  v a  2  s  .c  o  m
public static List<CustomerShortURL> getAllShortURLs() {
    Connection connection = null;
    PreparedStatement query = null;
    ResultSet rs = null;
    try {
        connection = connect();
        query = connection.prepareStatement("SELECT * FROM ShortURL");
        rs = query.executeQuery();
        List<CustomerShortURL> shortURLList = parseResultSet(rs);
        return shortURLList;
    } catch (Exception ex) {
        Logger.getLogger(ReferralManager.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        disconnect(connection, query, rs);
    }
    return null;
}

From source file:ch.newscron.referral.ReferralManager.java

/**
 * Calls the database to query for all rows in ShortURL table of database in relation to customerId
 * @param customerId a long representing the unique customer id
 * @return a List of CustomerShortURL objects, consisting of the shortURL table entries corresponding to the given customerId 
 *///from   w w  w  .j  a va  2 s.  co m
public static List<CustomerShortURL> getCustomerShortURLs(long customerId) {
    Connection connection = null;
    PreparedStatement query = null;
    ResultSet rs = null;
    try {
        connection = connect();
        query = connection.prepareStatement("SELECT * FROM ShortURL WHERE custId = ?");
        query.setLong(1, customerId);
        rs = query.executeQuery();
        List<CustomerShortURL> shortURLList = parseResultSet(rs);
        return shortURLList;
    } catch (Exception ex) {
        Logger.getLogger(ReferralManager.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        disconnect(connection, query, rs);
    }
    return null;
}

From source file:com.wso2telco.dao.TransactionDAO.java

/**
 * Insert sub value./* w  w w  . j  a  va  2  s  .c  o m*/
 *
 * @param token the context id
 * @param sub   the status code
 * @throws Exception the exception
 */
public static void insertTokenScopeLog(String token, String sub) throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DbUtil.getConnectDBConnection();
        String query = "INSERT INTO scope_log ( access_token,sub) VALUES " + "(? ,?);";
        ps = conn.prepareStatement(query);
        ps.setString(1, token);
        ps.setString(2, sub);
        ps.execute();
        log.debug("Sub value inserted successfully");
    } catch (SQLException e) {
        handleException("Error in inserting transaction log record : " + e.getMessage(), e);
    } finally {
        DbUtil.closeAllConnections(ps, conn, null);
    }
}

From source file:ch.newscron.referral.ReferralManager.java

public static int numberOfRegisteredUsers(String shortURL) {
    Connection connection = null;
    PreparedStatement query = null;
    ResultSet rs = null;/*from   w  w w . ja  v a 2 s.  com*/
    try {
        connection = connect();
        query = connection.prepareStatement(
                "SELECT COUNT(*) as total FROM User, ShortURL WHERE User.campaignId = ShortURL.id AND ShortURL.shortUrl = ?");
        query.setString(1, shortURL);
        rs = query.executeQuery();
        rs.next();
        int totalNumbUsers = rs.getInt("total");
        return totalNumbUsers;
    } catch (Exception ex) {
        Logger.getLogger(ReferralManager.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        disconnect(connection, query, rs);
    }
    return -1;
}

From source file:com.senior.g40.service.UserService.java

public static Profile getProfileByUserId(long userId) throws SQLException {
    Profile pf = null;//from ww w .j a  va2s.  com

    Connection conn = ConnectionBuilder.getConnection();
    String sqlCmd = "SELECT * FROM `profile` WHERE userId = ?;";
    PreparedStatement pstm = conn.prepareStatement(sqlCmd);
    pstm.setLong(1, userId);
    ResultSet rs = pstm.executeQuery();

    if (rs.next()) {
        pf = new Profile();
        setProfile(rs, pf);
        conn.close();
        return pf;
    }
    conn.close();
    return null;
}

From source file:net.codjo.dataprocess.server.treatmenthelper.TreatmentHelper.java

public static void updateDateRepositoryImport(Connection con, String date) throws SQLException {
    PreparedStatement pStmt = con.prepareStatement(
            "delete from PM_DP_CONFIG where CLE = ? " + "insert into PM_DP_CONFIG (CLE, VALEUR) values (?, ?)");
    try {/*from ww w.  java2  s.c  o  m*/
        pStmt.setString(1, DataProcessConstants.KEY_DATE_LAST_IMPORT_REPOSITORY);
        pStmt.setString(2, DataProcessConstants.KEY_DATE_LAST_IMPORT_REPOSITORY);
        pStmt.setString(3, date);
        pStmt.executeUpdate();
    } finally {
        pStmt.close();
    }
}

From source file:com.sql.CaseType.java

/**
 * Gathers a list of active case types for finding the proper section based 
 * on the case number./*from w w  w .  j  av  a 2  s.  c om*/
 * 
 * @param section For which section the method is currently processing
 * @return List CaseTypeModel
 */
public static List<CaseTypeModel> getCaseTypesBySection(String section) {
    List<CaseTypeModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM CaseType WHERE active = 1 AND section = ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, section);
        rs = ps.executeQuery();
        while (rs.next()) {
            CaseTypeModel item = new CaseTypeModel();
            item.setId(rs.getInt("id"));
            item.setActive(rs.getBoolean("active"));
            item.setSection(rs.getString("Section"));
            item.setCaseType(rs.getString("caseType"));
            item.setDescription(rs.getString("Description"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}