Example usage for java.sql Connection 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.l2jfree.gameserver.model.entity.faction.FactionQuest.java

public static void deleteFactionQuest(L2Player player, int factionQuestId) {
    Connection con = null;
    try {//w  w  w . j a  va  2 s.co m
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement;
        statement = con.prepareStatement(
                "DELETE FROM character_faction_quests WHERE char_id=? AND faction_quest_id=?");
        statement.setInt(1, player.getObjectId());
        statement.setInt(2, factionQuestId);
        statement.executeUpdate();
        statement.close();
    } catch (Exception e) {
        _log.warn("could not delete char faction quest:", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.sql.EmailOutRelatedCase.java

/**
 * Deletes related case based off of email ID
 * //from   www  .  j av  a 2s .  c om
 * @param emailOutId Integer
 */
public static void deleteEmailOutRelatedForEmail(int emailOutId) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "DELETE FROM EmailOutRelatedCase WHERE emailOutID = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, emailOutId);
        ps.executeUpdate();
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
    }
}

From source file:Main.java

/**
 * <p>/*  w w w .  ja v  a2s  . c  om*/
 * Execute a DDL statement to drop an object if it exists. Swallow exceptions.
 * </p>
 */
public static void dropObject(String objectType, String objectName, boolean objectIfMissing)
        throws SQLException {
    String dropDDL = "drop " + objectType + " " + objectName;
    Connection conn = getLocalConnection();

    // Drop the object if it does exist. Swallow exception if it doesn't.
    print(dropDDL);
    try {
        PreparedStatement dropStatement = conn.prepareStatement(dropDDL);

        dropStatement.execute();
        dropStatement.close();
    } catch (SQLException s) {
        if (objectIfMissing) {
            throw s;
        }
    }
}

From source file:com.github.ibole.infrastructure.persistence.db.mybatis.pagination.SqlHelper.java

/**
 * /*from ww w . j a  v  a2 s.  c om*/
 *
 * @param mappedStatement mapped
 * @param parameterObject ?
 * @param boundSql boundSql
 * @param dialect database dialect
 * @return 
 * @throws java.sql.SQLException sql
 */
public static int getCount(String sql, final MappedStatement mappedStatement, final Transaction transaction,
        final Object parameterObject, final BoundSql boundSql, Dialect dialect) throws SQLException {
    final String countSql = dialect.getCountString(sql);
    if (logger.isDebugEnabled()) {
        logger.debug("Total count SQL [{}] ", countSql);
        logger.debug("Total count Parameters: {} ", parameterObject);
    }
    Connection connection = transaction.getConnection();
    PreparedStatement countStmt = connection.prepareStatement(countSql);
    DefaultParameterHandler handler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
    handler.setParameters(countStmt);

    ResultSet rs = countStmt.executeQuery();
    int count = 0;
    if (rs.next()) {
        count = rs.getInt(1);
    }
    if (logger.isDebugEnabled()) {
        logger.debug("Total count: {}", count);
    }
    return count;

}

From source file:com.sql.DBBackupScript.java

/**
 * Backup database command/*from ww  w .  j  a v  a2 s  .c om*/
 * 
 * @param databaseName String
 */
public static void backupDB(String databaseName) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDBforBackup();
        String sql = "BACKUP DATABASE " + databaseName + " TO DISK = '" + Global.getDatabaseBackupPath() + "' ";
        ps = conn.prepareStatement(sql);
        ps.execute();
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(conn);
    }
}

From source file:com.chaosinmotion.securechat.server.commands.ChangePassword.java

public static boolean processRequest(Login.UserInfo userinfo, JSONObject requestParams, String token)
        throws ClassNotFoundException, SQLException, IOException {
    String oldpassword = requestParams.optString("oldpassword");
    String newpassword = requestParams.optString("newpassword");

    /*/*from   ww  w  . j  a  v a2s  .c  o m*/
     * Validate the old password against the token we received
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = Database.get();
        ps = c.prepareStatement("SELECT password " + "FROM Users " + "WHERE userid = ?");
        ps.setInt(1, userinfo.getUserID());
        rs = ps.executeQuery();

        if (rs.next()) {
            /*
             * If the result is found, hash the entry in the way it would
             * be hashed by the front end, and compare to see if the
             * hash codes match. (This requires that the hashed password
             * stored in the back-end has a consistent capitalization.
             * We arbitrarily pick lower-case for our SHA-256 hex string.
             */
            String spassword = rs.getString(1);

            /*
             * Encrypt password with token and salt
             */

            spassword = spassword + Constants.SALT + token;
            spassword = Hash.sha256(spassword);

            /*
             * Compare; if matches, then return the user info record
             * so we can store away. While the SHA256 process returns
             * consistent case, we compare ignoring case anyway, just
             * because. :-)
             */

            if (!spassword.equalsIgnoreCase(oldpassword)) {
                /* Wrong password */
                return false;
            }
        }

        /*
         * Update password stored with the updated value passed in.
         */

        rs.close();
        ps.close();

        ps = c.prepareStatement("UPDATE Users " + "SET password = ? " + "WHERE userid = ?");
        ps.setString(1, newpassword);
        ps.setInt(2, userinfo.getUserID());
        ps.execute();

        return true;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:io.apiman.gateway.engine.policies.BasicAuthJDBCTest.java

/**
 * Creates an in-memory datasource./*from www. ja v  a 2s.c o m*/
 * @throws SQLException
 */
private static BasicDataSource createInMemoryDatasource() throws SQLException {
    BasicDataSource ds = new BasicDataSource();
    ds.setDriverClassName(Driver.class.getName());
    ds.setUsername("sa"); //$NON-NLS-1$
    ds.setPassword(""); //$NON-NLS-1$
    ds.setUrl("jdbc:h2:mem:BasicAuthJDBCTest;DB_CLOSE_DELAY=-1"); //$NON-NLS-1$
    Connection connection = ds.getConnection();
    connection.prepareStatement(
            "CREATE TABLE users ( username varchar(255) NOT NULL, password varchar(255) NOT NULL, PRIMARY KEY (username))")
            .executeUpdate();
    connection.prepareStatement(
            "INSERT INTO users (username, password) VALUES ('bwayne', 'ae2efd698aefdf366736a4eda1bc5241f9fbfec7')")
            .executeUpdate();
    connection.prepareStatement(
            "INSERT INTO users (username, password) VALUES ('ckent', 'ea59f7ca52a2087c99374caba0ff29be1b2dcdbf')")
            .executeUpdate();
    connection.prepareStatement(
            "INSERT INTO users (username, password) VALUES ('ballen', 'ea59f7ca52a2087c99374caba0ff29be1b2dcdbf')")
            .executeUpdate();
    connection
            .prepareStatement(
                    "CREATE TABLE roles (rolename varchar(255) NOT NULL, username varchar(255) NOT NULL)")
            .executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('user', 'bwayne')")
            .executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('admin', 'bwayne')")
            .executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('ckent', 'user')")
            .executeUpdate();
    connection.prepareStatement("INSERT INTO roles (rolename, username) VALUES ('ballen', 'user')")
            .executeUpdate();
    connection.close();
    return ds;
}

From source file:Main.java

public static int update(Connection connection, String sql, List<Object> parameters) throws SQLException {
    int numRowsUpdated = 0;
    PreparedStatement ps = null;/*from  w w  w  .  j  ava2s.  c  om*/
    try {
        ps = connection.prepareStatement(sql);

        int i = 0;
        for (Object parameter : parameters) {
            ps.setObject(++i, parameter);
        }
        numRowsUpdated = ps.executeUpdate();
    } finally {
        close(ps);
    }
    return numRowsUpdated;
}

From source file:com.l2jfree.gameserver.instancemanager.RaidPointsManager.java

public static void init() {
    _list.clear();//from w ww .  j a  v  a  2s  .  c o  m

    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement = con.prepareStatement("SELECT * FROM character_raid_points");
        ResultSet rset = statement.executeQuery();

        while (rset.next())
            getList(rset.getInt("charId")).put(rset.getInt("boss_id"), rset.getInt("points"));

        rset.close();
        statement.close();
    } catch (Exception e) {
        _log.warn("", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.chaosinmotion.securechat.server.commands.Login.java

/**
 * Process the login request. This returns null if the user could not
 * be logged in./*  ww  w.  j a  v a 2 s . c  om*/
 * 
 * The expected parameters are 'username' and 'password', which should
 * be hashed.
 * 
 * @param requestParams
 * @return
 * @throws IOException 
 * @throws SQLException 
 * @throws ClassNotFoundException 
 */
public static UserInfo processRequest(JSONObject requestParams, String token)
        throws ClassNotFoundException, SQLException, IOException {
    String username = requestParams.optString("username");
    String password = requestParams.optString("password");

    /*
     * Obtain user information from database
     */

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = Database.get();
        ps = c.prepareStatement("SELECT userid, password " + "FROM Users " + "WHERE username = ?");
        ps.setString(1, username);
        rs = ps.executeQuery();

        if (rs.next()) {
            /*
             * If the result is found, hash the entry in the way it would
             * be hashed by the front end, and compare to see if the
             * hash codes match. (This requires that the hashed password
             * stored in the back-end has a consistent capitalization.
             * We arbitrarily pick lower-case for our SHA-256 hex string.
             */
            int userID = rs.getInt(1);
            String spassword = rs.getString(2);

            /*
             * Encrypt password with token and salt
             */

            spassword = spassword + Constants.SALT + token;
            spassword = Hash.sha256(spassword);

            /*
             * Compare; if matches, then return the user info record
             * so we can store away. While the SHA256 process returns
             * consistent case, we compare ignoring case anyway, just
             * because. :-)
             */

            if (spassword.equalsIgnoreCase(password)) {
                return new UserInfo(userID);
            }
        }
        return null;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}