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:com.keybox.manage.db.AuthDB.java

/**
 * updates the admin table based on auth id
 *
 * @param con  DB connection/*from w  ww .j  ava  2  s  . c  o m*/
 * @param auth username and password object
 */
public static void updateLogin(Connection con, Auth auth) {

    try {
        PreparedStatement stmt = con.prepareStatement(
                "update users set username=?, auth_type=?, auth_token=?, password=?, salt=? where id=?");
        stmt.setString(1, auth.getUsername());
        stmt.setString(2, auth.getAuthType());
        stmt.setString(3, auth.getAuthToken());
        if (StringUtils.isNotEmpty(auth.getPassword())) {
            String salt = EncryptionUtil.generateSalt();
            stmt.setString(4, EncryptionUtil.hash(auth.getPassword() + salt));
            stmt.setString(5, salt);
        } else {
            stmt.setString(4, null);
            stmt.setString(5, null);
        }
        stmt.setLong(6, auth.getId());
        stmt.execute();

        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }

}

From source file:com.splicemachine.derby.test.framework.SpliceIndexWatcher.java

/**
 * Use this static method in cases where you want to create an index after creating/loading table.
 * TODO: redirect starting(Description) to call this method
 * @param connection/*www .j ava2 s  . co  m*/
 * @param schemaName
 * @param tableName
 * @param indexName
 * @param definition
 * @param unique
 * @throws Exception
 */
public static void createIndex(Connection connection, String schemaName, String tableName, String indexName,
        String definition, boolean unique) throws Exception {
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        //            connection = SpliceNetConnection.getConnection();
        statement = connection.prepareStatement(SELECT_SPECIFIC_INDEX);
        statement.setString(1, schemaName);
        statement.setString(2, indexName);
        rs = statement.executeQuery();
        if (rs.next()) {
            SpliceIndexWatcher.executeDrop(connection, schemaName, indexName);
        }
        try (Statement s = connection.createStatement()) {
            System.out.println(String.format("create " + (unique ? "unique" : "") + " index %s.%s on %s.%s %s",
                    schemaName, indexName, schemaName, tableName, definition));
            s.execute(String.format("create " + (unique ? "unique" : "") + " index %s.%s on %s.%s %s",
                    schemaName, indexName, schemaName, tableName, definition));
        }
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(statement);
    }
}

From source file:com.sql.RelatedCase.java

public static List<RelatedCaseModel> getRelatedCases(EmailOutModel eml) {
    List<RelatedCaseModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;//from w w w.  j  a v a2s.c  o m
    ResultSet rs = null;
    try {
        int i = 0;
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM RelatedCase WHERE LEN(relatedCaseNumber) = 16 " + " AND CaseYear = ? "
                + " AND CaseType = ? " + " AND CaseMonth = ? " + " AND CaseNumber = ? ";

        ps = conn.prepareStatement(sql);
        ps.setString(1, eml.getCaseYear());
        ps.setString(2, eml.getCaseType());
        ps.setString(3, eml.getCaseMonth());
        ps.setString(4, eml.getCaseNumber());

        rs = ps.executeQuery();
        while (rs.next()) {
            String[] relatedCase = rs.getString("relatedCaseNumber").split("-");

            if (relatedCase.length == 4) {
                RelatedCaseModel item = new RelatedCaseModel();
                item.setCaseYear(rs.getString("caseYear"));
                item.setCaseType(rs.getString("caseType"));
                item.setCaseMonth(rs.getString("caseMonth"));
                item.setCaseNumber(rs.getString("caseNumber"));
                item.setRelatedCaseYear(relatedCase[0]);
                item.setRelatedCaseType(relatedCase[1]);
                item.setRelatedCaseMonth(relatedCase[2]);
                item.setRelatedCaseNumber(relatedCase[3]);
                list.add(item);
            }
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:ca.qc.adinfo.rouge.leaderboard.db.LeaderboardDb.java

public static boolean createLeaderboard(DBManager dbManager, String key, String name) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;//from w ww.  j  a v a  2  s  .com
    String sql = null;

    sql = "INSERT INTO rouge_leaderboards (`key`, `name`) VALUES (?, ?);";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setString(1, key);
        stmt.setString(2, name);

        int ret = stmt.executeUpdate();

        return (ret > 0);

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return false;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

From source file:genericepayadmin.AddIpBean.java

public static int getPages(Connection con) throws Exception {
    int totalcount = 0;
    PreparedStatement ps = null;/*from  www.j a v  a2 s .  co  m*/
    ResultSet rs = null;
    try {
        String sql = "select ceil(count(*)/10) as totalpage from webservice_validator";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            totalcount = rs.getInt("totalpage");
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
            if (rs != null)
                rs.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    return totalcount;
}

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

public static Optional<Event> loadUnique(Connection connection, Class<? extends Event> eventType)
        throws SQLException, IOException {
    String statement = "SELECT \"id\", \"data\" FROM \"events\" WHERE \"type\" = ? AND \"removed\" = false";
    try (PreparedStatement stmt = connection.prepareStatement(statement)) {
        stmt.setString(1, eventType.getSimpleName());
        ResultSet result = stmt.executeQuery();
        if (result.next()) {
            String evString = result.getString("data");
            Event event = Serialization.getJsonMapper().readValue(evString, Event.class);
            event.id = result.getLong("id");
            event.removed = false;/* w w w .ja  v a  2 s  .c o  m*/
            return Optional.of(event);
        } else {
            return Optional.empty();
        }
    }
}

From source file:es.tena.foundation.util.POIUtil.java

public static void generateXLS(String tabla, String filename, Connection conn, String encoding)
        throws SQLException {
    String query = "";
    try {//from   w  ww  .  ja  va2s  . c o  m
        query = "SELECT * FROM (" + tabla + ")";
        PreparedStatement stmt = conn.prepareStatement(query);
        ResultSet rset = stmt.executeQuery();

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(filename);
        String sheetRef = sheet.getPackagePart().getPartName().getName();
        String template = "c:\\temp\\template_" + filename + ".xlsx";
        FileOutputStream os = new FileOutputStream(template);
        wb.write(os);
        os.close();

        File tmp = File.createTempFile("sheet", ".xml");
        Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), encoding);
        generate(fw, rset, encoding);
        rset.close();
        stmt.close();
        fw.close();

        FileOutputStream out = new FileOutputStream(
                "c:\\temp\\" + filename + sdf.format(calendario.getTime()) + ".xlsx");
        FileUtil.substitute(new File(template), tmp, sheetRef.substring(1), out);
        out.close();
        Logger.getLogger(POIUtil.class.getName()).log(Level.INFO, "Creado con exito {0}", filename);
    } catch (Exception ex) {
        ex.printStackTrace();
        Logger.getLogger(POIUtil.class.getName()).log(Level.SEVERE, null, query + "\n" + ex);
        System.out.println(query);
    } finally {
        conn.close();
    }
}

From source file:com.magnet.mmx.server.plugin.mmxmgmt.db.TopicItemDAOImplTest.java

@AfterClass
public static void cleanupDatabase() {
    final String unboundStr = "DELETE FROM ofPubsubItem where serviceID = ? AND nodeID = ?";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {/*from  w ww .  j  a  v  a  2s  .  c  o m*/
        conn = UnitTestDSProvider.getDataSource().getConnection();
        pstmt = conn.prepareStatement(unboundStr);
        pstmt.setString(1, SERVICE_ID);
        pstmt.setString(2, NODE_ID);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        LOGGER.error("cleanupDatabase : caught exception cleaning ofPubsubItem");
    } finally {
        CloseUtil.close(LOGGER, pstmt, conn);
    }
}

From source file:com.krawler.common.util.SchedulingUtilities.java

public static String getNonWorkWeekdays(Connection conn, String projid) throws ServiceException {
    String retStr = null;/*w w  w.jav a  2s .  c om*/
    PreparedStatement pstmt = null;
    try {
        // week holidays
        pstmt = conn.prepareStatement("select day from proj_workweek where isholiday = true and projectid = ?");
        pstmt.setString(1, projid);
        ResultSet rs = pstmt.executeQuery();
        KWLJsonConverter kjs = new KWLJsonConverter();
        retStr = kjs.GetJsonForGrid(rs);
    } catch (SQLException e) {
        throw ServiceException.FAILURE("SchedulUtilities.getNonWorkWeekdays error", e);
    } finally {
        DbPool.closeStatement(pstmt);
    }
    return retStr;
}

From source file:com.sql.SECExceptions.java

/**
 * Gets a count of errors where the description text matches. This is to
 * eliminate the repeat of entries from the application looping
 *
 * @param description String//from w  ww  .ja  va  2 s.  c  o  m
 * @return Integer count
 */
public static int getExistingException(String description) {
    int count = 0;
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT COUNT(*) AS num FROM SECExceptions WHERE "
                + "timeOccurred >= CAST(CURRENT_TIMESTAMP AS DATE) AND exceptionDescrption LIKE ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, description + "%");

        rs = ps.executeQuery();
        while (rs.next()) {
            count = rs.getInt("num");
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return count;
}