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.SystemStatusDB.java

/**
 * inserts into the status table to keep track of key placement status
 *
 * @param con                DB connection object
 * @param hostSystem systems for authorized_keys replacement
 * @param userId user id//  ww  w. jav  a 2s  .c  o  m
 */
private static void insertSystemStatus(Connection con, HostSystem hostSystem, Long userId) {

    try {

        PreparedStatement stmt = con
                .prepareStatement("insert into status (id, status_cd, user_id) values (?,?,?)");
        stmt.setLong(1, hostSystem.getId());
        stmt.setString(2, hostSystem.getStatusCd());
        stmt.setLong(3, userId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

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

}

From source file:com.keybox.manage.db.ProfileDB.java

/**
 * method to do order by based on the sorted set object for profiles
 * @return list of profiles//from ww w. j av  a 2s . c  o m
 */
public static SortedSet getProfileSet(SortedSet sortedSet) {

    ArrayList<Profile> profileList = new ArrayList<>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = " order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select distinct p.* from  profiles p ";
    if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM))) {
        sql = sql + ", system_map m, system s where m.profile_id = p.id and m.system_id = s.id"
                + " and (lower(s.display_nm) like ? or lower(s.host) like ?)";
    } else if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER))) {
        sql = sql + ", user_map m, users u where m.profile_id = p.id and m.user_id = u.id"
                + " and (lower(u.first_nm) like ? or lower(u.last_nm) like ?"
                + " or lower(u.email) like ? or lower(u.username) like ?)";
    }
    sql = sql + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_SYSTEM))) {
            stmt.setString(1, "%" + sortedSet.getFilterMap().get(FILTER_BY_SYSTEM).toLowerCase() + "%");
            stmt.setString(2, "%" + sortedSet.getFilterMap().get(FILTER_BY_SYSTEM).toLowerCase() + "%");
        } else if (StringUtils.isNotEmpty(sortedSet.getFilterMap().get(FILTER_BY_USER))) {
            stmt.setString(1, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
            stmt.setString(2, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
            stmt.setString(3, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
            stmt.setString(4, "%" + sortedSet.getFilterMap().get(FILTER_BY_USER).toLowerCase() + "%");
        }
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Profile profile = new Profile();
            profile.setId(rs.getLong("id"));
            profile.setNm(rs.getString("nm"));
            profile.setDesc(rs.getString("desc"));
            profileList.add(profile);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    } finally {
        DBUtils.closeConn(con);
    }

    sortedSet.setItemList(profileList);
    return sortedSet;
}

From source file:com.glaf.activiti.util.ExecutionUtils.java

@SuppressWarnings("unchecked")
public static void executeSqlUpdate(DelegateExecution execution, Expression sql) {
    CommandContext commandContext = Context.getCommandContext();

    ExecutionEntity executionEntity = commandContext.getExecutionEntityManager()
            .findExecutionById(execution.getId());
    String processDefinitionId = executionEntity.getProcessDefinitionId();
    ProcessDefinitionEntity processDefinitionEntity = commandContext.getProcessDefinitionEntityManager()
            .findProcessDefinitionById(processDefinitionId);
    String processName = processDefinitionEntity.getKey();

    Map<String, Object> params = new java.util.HashMap<String, Object>();

    Map<String, Object> variables = execution.getVariables();
    if (variables != null && variables.size() > 0) {
        Iterator<String> iterator = variables.keySet().iterator();
        while (iterator.hasNext()) {
            String variableName = iterator.next();
            if (params.get(variableName) == null) {
                Object value = execution.getVariable(variableName);
                params.put(variableName, value);
            }//from w ww  . j  a va  2s.c o  m
        }
    }

    params.put(Constants.BUSINESS_KEY, execution.getProcessBusinessKey());
    params.put("processInstanceId", execution.getProcessInstanceId());
    params.put("processDefinitionId", processDefinitionEntity.getId());
    params.put("processName", processName);
    params.put("now", new java.util.Date());

    if (sql != null) {
        String sqlx = sql.getExpressionText();

        if (sqlx.indexOf("#{tableName}") != -1) {
            String tableName = (String) execution.getVariable("tableName");
            if (StringUtils.isNotEmpty(tableName)) {
                sqlx = StringTools.replace(sqlx, "#{tableName}", tableName);
            }
        } else if (sqlx.indexOf("${tableName}") != -1) {
            String tableName = (String) execution.getVariable("tableName");
            if (StringUtils.isNotEmpty(tableName)) {
                sqlx = StringTools.replace(sqlx, "${tableName}", tableName);
            }
        }

        sqlx = StringTools.replaceIgnoreCase(sqlx, "${", "#{");

        List<Object> values = new java.util.ArrayList<Object>();
        SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sqlx, params);
        sqlx = sqlExecutor.getSql();
        if (sqlExecutor.getParameter() != null) {
            if (sqlExecutor.getParameter() instanceof List) {
                List<Object> list = (List<Object>) sqlExecutor.getParameter();
                values.addAll(list);
            }
        }

        logger.debug(sqlx);
        logger.debug(values);

        Connection con = null;
        try {
            con = commandContext.getDbSqlSession().getSqlSession().getConnection();
            PreparedStatement psmt = con.prepareStatement(sqlx);
            JdbcUtils.fillStatement(psmt, values);
            psmt.executeUpdate();
            psmt.close();
            psmt = null;
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

    }
}

From source file:com.nabla.dc.server.handler.fixed_asset.Asset.java

static public void dispose(final Connection conn, final Integer assetId, final IDisposal disposal)
        throws SQLException, DispatchException {
    final PreparedStatement redo = conn
            .prepareStatement("INSERT INTO fa_transaction_redo (fa_asset_id, command) VALUES(?,?);");
    try {//from   w w  w  . ja v a2s .  c o m
        redo.setInt(1, assetId);
        // backup transaction after disposal if any
        if (log.isDebugEnabled())
            log.debug("backing up transactions after disposal date");
        // charge monthly depreciation in disposal month if disposal is after 15
        final Calendar dt = Util.dateToCalendar(disposal.getDate());
        if (dt.get(GregorianCalendar.DAY_OF_MONTH) >= dt.getActualMaximum(GregorianCalendar.DAY_OF_MONTH) / 2)
            dt.add(GregorianCalendar.MONTH, 1);
        dt.set(GregorianCalendar.DAY_OF_MONTH, 1);
        final Date from = Util.calendarToSqlDate(dt);
        // get list of transactions to backup before we delete them
        final IntegerSet transIds = new IntegerSet();
        final PreparedStatement stmt = StatementFormat.prepare(conn,
                "SELECT t.*" + " FROM fa_transaction AS t INNER JOIN period_end AS p ON t.period_end_id=p.id"
                        + " WHERE t.fa_asset_id=? AND p.end_date>?;",
                assetId, from);
        try {
            final ResultSet rs = stmt.executeQuery();
            try {
                while (rs.next()) {
                    transIds.add(rs.getInt("id"));
                    final String command = MessageFormat.format("INSERT INTO fa_transaction"
                            + " (id,fa_asset_id,period_end_id,amount,class,type,depreciation_period)"
                            + " VALUES({0,number,0},{1,number,0},{2,number,0},{3,number,0},''{4}'',''{5}'',{6,number,0});",
                            rs.getInt("id"), rs.getInt("fa_asset_id"), rs.getInt("period_end_id"),
                            rs.getInt("amount"), rs.getString("class"), rs.getString("type"),
                            Database.getInteger(rs, "depreciation_period"));
                    if (log.isTraceEnabled())
                        log.trace("redo = " + command);
                    redo.setString(2, command);
                    redo.addBatch();
                }
            } finally {
                rs.close();
            }
        } finally {
            stmt.close();
        }
        // remove any transaction after disposal date
        if (log.isDebugEnabled())
            log.debug("removing transactions after disposal date");
        Database.executeUpdate(conn, "DELETE FROM fa_transaction WHERE id IN (?);", transIds);
        // add disposal transactions
        if (log.isDebugEnabled())
            log.debug("adding transactions for disposal");
        final TransactionList transactions = new TransactionList(assetId);
        // closing cost
        transactions.add(new Transaction(TransactionClasses.COST, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetCostBeforeDisposal(conn, assetId)));
        // closing accumulated depreciation
        transactions.add(new Transaction(TransactionClasses.DEP, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetDepreciationBeforeDisposal(conn, assetId)));
        for (Integer newTransId : transactions.save(conn, true)) {
            redo.setString(2,
                    MessageFormat.format("DELETE FROM fa_transaction WHERE id={0,number,0};", newTransId));
            redo.addBatch();
        }
        if (!Database.isBatchCompleted(redo.executeBatch()))
            throw new InternalErrorException("failed to save disposal transactions");
    } finally {
        redo.close();
    }
}

From source file:org.red5.server.plugin.admin.dao.UserDAO.java

public static boolean addUser(String username, String hashedPassword) {
    boolean result = false;

    Connection conn = null;
    PreparedStatement stmt = null;
    try {/*  w w w  .  ja va  2s .  c  om*/
        // JDBC stuff
        DataSource ds = UserDatabase.getDataSource();

        conn = ds.getConnection();
        //make a statement
        stmt = conn
                .prepareStatement("INSERT INTO APPUSER (username, password, enabled) VALUES (?, ?, 'enabled')");
        stmt.setString(1, username);
        stmt.setString(2, hashedPassword);
        log.debug("Add user: {}", stmt.execute());
        //add role
        stmt = conn.prepareStatement("INSERT INTO APPROLE (username, authority) VALUES (?, 'ROLE_SUPERVISOR')");
        stmt.setString(1, username);
        log.debug("Add role: {}", stmt.execute());
        //
        result = true;
    } catch (Exception e) {
        log.error("Error connecting to db", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
    return result;
}

From source file:com.tethrnet.manage.db.SystemStatusDB.java

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id//w  w w .  ja  v  a 2  s. co  m
 * @return hostSystem systems for authorized_keys replacement
 */
public static HostSystem getNextPendingSystem(Long userId) {

    HostSystem hostSystem = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from status where (status_cd like ? or status_cd like ? or status_cd like ?) and user_id=? order by id asc");
        stmt.setString(1, HostSystem.INITIAL_STATUS);
        stmt.setString(2, HostSystem.AUTH_FAIL_STATUS);
        stmt.setString(3, HostSystem.PUBLIC_KEY_FAIL_STATUS);
        stmt.setLong(4, userId);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
    return hostSystem;

}

From source file:ca.qc.adinfo.rouge.variable.db.PersistentVariableDb.java

public static Variable getPersitentVariable(DBManager dbManager, String key) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*ww w.j av a  2 s. c  o  m*/

    String sql = "SELECT value, version FROM rouge_persistant_variable WHERE `key` = ?";

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

        stmt.setString(1, key);

        rs = stmt.executeQuery();

        if (rs.next()) {
            JSONObject jSonObject = JSONObject.fromObject(rs.getString("value"));

            return new Variable(key, new RougeObject(jSonObject), rs.getLong("version"));
        } else {
            return null;
        }

    } catch (SQLException e) {

        log.error(e);
        return null;

    } finally {

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

From source file:com.hangum.tadpole.db.bander.oracle.OracleExecutePlanUtils.java

/**
 * oracle query plan? . //  w  w  w  .ja  va 2  s . co  m
 * 
 * @param userDB
 * @param sql
 * @param planTableName
 * @throws Exception
 */
public static void plan(UserDBDAO userDB, String sql, String planTableName, java.sql.Connection javaConn,
        PreparedStatement stmt, String statement_id) throws Exception {

    String query = PartQueryUtil.makeExplainQuery(userDB, sql);
    query = StringUtils.replaceOnce(query, PublicTadpoleDefine.STATEMENT_ID, statement_id);
    query = StringUtils.replaceOnce(query, PublicTadpoleDefine.DELIMITER, planTableName);

    stmt = javaConn.prepareStatement(query);
    stmt.execute();

}

From source file:com.keybox.manage.db.SystemStatusDB.java

/**
 * returns the first system that authorized keys has not been tried
 *
 * @param userId user id/*from   w w  w. ja  va2  s .  c  o  m*/
 * @return hostSystem systems for authorized_keys replacement
 */
public static HostSystem getNextPendingSystem(Long userId) {

    HostSystem hostSystem = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "select * from status where (status_cd like ? or status_cd like ? or status_cd like ?) and user_id=? order by id asc");
        stmt.setString(1, HostSystem.INITIAL_STATUS);
        stmt.setString(2, HostSystem.AUTH_FAIL_STATUS);
        stmt.setString(3, HostSystem.PUBLIC_KEY_FAIL_STATUS);
        stmt.setLong(4, userId);
        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            hostSystem = SystemDB.getSystem(con, rs.getLong("id"));
            hostSystem.setStatusCd(rs.getString(STATUS_CD));
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);
    return hostSystem;

}

From source file:com.microsoft.sqlserver.jdbc.connection.PoolingTest.java

/**
 * setup connection, get connection from pool, and test threads
 * /*from w  w  w . jav  a2  s.c om*/
 * @param ds
 * @throws SQLException
 */
private static void connect(DataSource ds) throws SQLException {
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;

    try {
        con = ds.getConnection();
        pst = con.prepareStatement("SELECT SUSER_SNAME()");
        pst.setQueryTimeout(5);
        rs = pst.executeQuery();

        // TODO : we are commenting this out due to AppVeyor failures. Will investigate later.
        // assertTrue(countTimeoutThreads() >= 1, "Timeout timer is missing.");

        while (rs.next()) {
            rs.getString(1);
        }
    } finally {
        if (rs != null) {
            rs.close();
        }

        if (pst != null) {
            pst.close();
        }

        if (con != null) {
            con.close();
        }
    }
}