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.splicemachine.derby.test.framework.SpliceRoleWatcher.java

public static void executeDrop(String roleName) {
    LOG.trace("ExecuteDrop");
    Connection connection = null;
    PreparedStatement statement = null;
    try {/*from  w w  w  . j av a2 s  .  co  m*/
        connection = SpliceNetConnection.getConnection();
        statement = connection.prepareStatement("select roleid from sys.sysroles where roleid = ?");
        statement.setString(1, roleName);
        ResultSet rs = statement.executeQuery();
        if (rs.next())
            connection.createStatement().execute(String.format("drop role %s", roleName));
        connection.commit();
    } catch (Exception e) {
        LOG.error("error Dropping " + e.getMessage());
        e.printStackTrace();
        throw new RuntimeException(e);
    } finally {
        DbUtils.closeQuietly(statement);
        DbUtils.commitAndCloseQuietly(connection);
    }
}

From source file:com.us.test.H2Helper.java

public static void laodData2H2Db(File ips, String url, String uname, String upasswd)
        throws IOException, SQLException {
    List<String> ipsegma = FileUtils.readLines(ips, "GBK");

    JdbcConnectionPool cp = JdbcConnectionPool.create(url, uname, upasswd);
    Connection conn = cp.getConnection();
    String sql = "insert into ips values(?,?,?,?,?,?)";
    int i = 0;//from  w ww  .  ja v a 2 s  .c  o m
    for (String ip : ipsegma) {
        PreparedStatement statement = conn.prepareStatement(sql);
        String[] ipary = ip.split("\\s+");
        if (ipary.length < 2 || ipary[0].indexOf(".") < 0)
            continue;
        statement.setString(1, ip2Long(ipary[0].trim()));
        statement.setString(2, ip2Long(ipary[1].trim()));
        statement.setString(3, ipary[1].trim());
        statement.setString(4, ipary[1].trim());
        statement.setString(5, ipary.length <= 2 ? "" : ipary[2].trim());
        statement.setString(6, ipary.length <= 3 ? "" : ipary[3].trim());
        System.out.println(i++ + ":" + statement.execute());

    }
    conn.close();
    cp.dispose();
}

From source file:com.ec2box.manage.db.UserThemeDB.java

/**
 * saves user theme/*from  w  ww .j  a v  a 2 s.  c o m*/
 * 
 * @param userId object
 */
public static void saveTheme(Long userId, UserSettings theme) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("delete from user_theme where user_id=?");
        stmt.setLong(1, userId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

        if (StringUtils.isNotEmpty(theme.getPlane()) || StringUtils.isNotEmpty(theme.getTheme())) {

            stmt = con.prepareStatement(
                    "insert into user_theme(user_id, bg, fg, d1, d2, d3, d4, d5, d6, d7, d8, b1, b2, b3, b4, b5, b6, b7, b8) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            stmt.setLong(1, userId);
            stmt.setString(2, theme.getBg());
            stmt.setString(3, theme.getFg());
            //if contains all 16 theme colors insert
            if (theme.getColors() != null && theme.getColors().length == 16) {
                for (int i = 0; i < 16; i++) {
                    stmt.setString(i + 4, theme.getColors()[i]);
                }
                //else set to null
            } else {
                for (int i = 0; i < 16; i++) {
                    stmt.setString(i + 4, null);
                }
            }
            stmt.execute();
            DBUtils.closeStmt(stmt);
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DBUtils.closeConn(con);
    }

}

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

/**
 * saves user theme/* w ww  .  jav a2 s.  co m*/
 * 
 * @param userId object
 */
public static void saveTheme(Long userId, UserSettings theme) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("delete from user_theme where user_id=?");
        stmt.setLong(1, userId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

        if (org.apache.commons.lang.StringUtils.isNotEmpty(theme.getPlane())
                || org.apache.commons.lang.StringUtils.isNotEmpty(theme.getTheme())) {

            stmt = con.prepareStatement(
                    "insert into user_theme(user_id, bg, fg, d1, d2, d3, d4, d5, d6, d7, d8, b1, b2, b3, b4, b5, b6, b7, b8) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            stmt.setLong(1, userId);
            stmt.setString(2, theme.getBg());
            stmt.setString(3, theme.getFg());
            //if contains all 16 theme colors insert
            if (theme.getColors() != null && theme.getColors().length == 16) {
                for (int i = 0; i < 16; i++) {
                    stmt.setString(i + 4, theme.getColors()[i]);
                }
                //else set to null
            } else {
                for (int i = 0; i < 16; i++) {
                    stmt.setString(i + 4, null);
                }
            }
            stmt.execute();
            DBUtils.closeStmt(stmt);
        }

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

}

From source file:com.dangdang.ddframe.rdb.sharding.example.transaction.Main.java

private static void updateFailure(final DataSource dataSource) throws SQLException {
    String sql1 = "UPDATE t_order SET status='UPDATE_1' WHERE user_id=10 AND order_id=1000";
    String sql2 = "UPDATE t_order SET not_existed_column=1 WHERE user_id=1 AND order_id=?";
    String sql3 = "UPDATE t_order SET status='UPDATE_2' WHERE user_id=10 AND order_id=1000";
    SoftTransactionManager transactionManager = new SoftTransactionManager(
            getSoftTransactionConfiguration(dataSource));
    transactionManager.init();//from w  w  w .j a  va 2s.  co  m
    BEDSoftTransaction transaction = (BEDSoftTransaction) transactionManager
            .getTransaction(SoftTransactionType.BestEffortsDelivery);
    Connection conn = null;
    try {
        conn = dataSource.getConnection();
        transaction.begin(conn);
        PreparedStatement preparedStatement1 = conn.prepareStatement(sql1);
        PreparedStatement preparedStatement2 = conn.prepareStatement(sql2);
        preparedStatement2.setObject(1, 1000);
        PreparedStatement preparedStatement3 = conn.prepareStatement(sql3);
        preparedStatement1.executeUpdate();
        preparedStatement2.executeUpdate();
        preparedStatement3.executeUpdate();
    } finally {
        transaction.end();
        if (conn != null) {
            conn.close();
        }
    }
}

From source file:com.ec2box.manage.db.UserThemeDB.java

/**
 * get user theme/*  w ww. j a  v a 2s .c  o  m*/
 *
 * @param userId object
 * @return user theme object
 */
public static UserSettings getTheme(Long userId) {

    UserSettings theme = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement("select * from user_theme where user_id=?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            theme = new UserSettings();
            theme.setBg(rs.getString("bg"));
            theme.setFg(rs.getString("fg"));
            if (StringUtils.isNotEmpty(rs.getString("d1"))) {
                String[] colors = new String[16];
                colors[0] = rs.getString("d1");
                colors[1] = rs.getString("d2");
                colors[2] = rs.getString("d3");
                colors[3] = rs.getString("d4");
                colors[4] = rs.getString("d5");
                colors[5] = rs.getString("d6");
                colors[6] = rs.getString("d7");
                colors[7] = rs.getString("d8");
                colors[8] = rs.getString("b1");
                colors[9] = rs.getString("b2");
                colors[10] = rs.getString("b3");
                colors[11] = rs.getString("b4");
                colors[12] = rs.getString("b5");
                colors[13] = rs.getString("b6");
                colors[14] = rs.getString("b7");
                colors[15] = rs.getString("b8");
                theme.setColors(colors);
            }
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DBUtils.closeConn(con);
    }

    return theme;

}

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

/**
 * get user theme/*from  w  w  w . j ava2 s  .  c  o m*/
 *
 * @param userId object
 * @return user theme object
 */
public static UserSettings getTheme(Long userId) {

    UserSettings theme = null;
    Connection con = null;
    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement("select * from user_theme where user_id=?");
        stmt.setLong(1, userId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            theme = new UserSettings();
            theme.setBg(rs.getString("bg"));
            theme.setFg(rs.getString("fg"));
            if (StringUtils.isNotEmpty(rs.getString("d1"))) {
                String[] colors = new String[16];
                colors[0] = rs.getString("d1");
                colors[1] = rs.getString("d2");
                colors[2] = rs.getString("d3");
                colors[3] = rs.getString("d4");
                colors[4] = rs.getString("d5");
                colors[5] = rs.getString("d6");
                colors[6] = rs.getString("d7");
                colors[7] = rs.getString("d8");
                colors[8] = rs.getString("b1");
                colors[9] = rs.getString("b2");
                colors[10] = rs.getString("b3");
                colors[11] = rs.getString("b4");
                colors[12] = rs.getString("b5");
                colors[13] = rs.getString("b6");
                colors[14] = rs.getString("b7");
                colors[15] = rs.getString("b8");
                theme.setColors(colors);
            }
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    return theme;

}

From source file:com.concursive.connect.web.modules.activity.utils.ProjectHistoryUtils.java

public static int findNextThreadPosition(Connection db, ProjectHistory parentProjectHistory)
        throws SQLException {
    int count = 0;
    PreparedStatement pst = db
            .prepareStatement("SELECT count(*) AS ccount " + "FROM project_history " + "WHERE lineage LIKE ? ");
    pst.setString(1, parentProjectHistory.getLineage() + parentProjectHistory.getId() + "/%");
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        count = rs.getInt("ccount");
    }/*ww  w . java 2s .  c om*/
    rs.close();
    pst.close();
    return (parentProjectHistory.getThreadPosition() + count + 1);
}

From source file:com.sql.Audit.java

/**
 * Adds an entry to the audit table/*  www . j  a  va 2 s .c o m*/
 * @param action performed action to be stored
 */
public static void addAuditEntry(String action) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {

        conn = DBConnection.connectToDB();

        String sql = "INSERT INTO Audit VALUES" + "(?,?,?)";

        ps = conn.prepareStatement(sql);
        ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
        ps.setInt(2, 0);
        ps.setString(3, action == null ? "MISSING ACTION" : StringUtils.left(action, 255));

        ps.executeUpdate();
    } catch (SQLException ex) {
        if (ex.getCause() instanceof SQLServerException) {
            addAuditEntry(action);
        }
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
    }
}

From source file:com.sql.EmailOut.java

/**
 * Deletes email entry based off of the ID
 *
 * @param id Integer - emailID from the database
 *///from www . java 2s  .com
public static void deleteEmailEntry(int id) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "DELETE FROM EmailOut WHERE id = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
    }
}