Example usage for java.sql PreparedStatement setNull

List of usage examples for java.sql PreparedStatement setNull

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setNull

Introduction

In this page you can find the example usage for java.sql PreparedStatement setNull.

Prototype

void setNull(int parameterIndex, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

From source file:Main.java

public static void main(String args[]) throws Exception {
    Connection con = null;/*from   ww w  . j  a  v a 2 s  . co m*/
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con = DriverManager.getConnection("jdbc:oracle:thin:@192.201.32.92:1521:psprd1", "username", "password");
    String query = null;
    ResultSet rset = null;
    query = "UPDATE t1 " + " SET id = ?";
    PreparedStatement stmt = con.prepareStatement(query);
    // stmt.setInt(paramIndex++, null);
    stmt.setNull(1, java.sql.Types.INTEGER);
    stmt.executeUpdate();
    stmt.close();
    query = "select id from t1 ";
    stmt = con.prepareStatement(query);
    rset = stmt.executeQuery();
    rset.next();
    System.out.println(rset.getString("id"));
    rset.close();
    stmt.close();
    con.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int,myDate DATE);");
    String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setNull(2, java.sql.Types.DATE);

    pstmt.executeUpdate();/*  w  w  w.j a  v  a 2 s . c  o m*/

    ResultSet rs = st.executeQuery("SELECT * FROM survey");

    outputResultSet(rs);

    rs.close();
    st.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    String id = "0001";
    Connection conn = null;/*from   w w  w.  java  2s  .  co  m*/
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "insert into nullable_table(id,string_column, int_column) values(?, ?, ?)";

        // create PrepareStatement object
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, id);
        pstmt.setNull(2, java.sql.Types.VARCHAR);
        pstmt.setNull(3, java.sql.Types.INTEGER);

        // execute query, and return number of rows created
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:com.l2jfree.loginserver.tools.L2AccountManager.java

/**
 * Launches the interactive account manager.
 * /*from   w w w.j a  v  a2s  . c o  m*/
 * @param args ignored
 */
public static void main(String[] args) {
    // LOW rework this crap
    Util.printSection("Account Management");

    _log.info("Please choose:");
    //_log.info("list - list registered accounts");
    _log.info("reg - register a new account");
    _log.info("rem - remove a registered account");
    _log.info("prom - promote a registered account");
    _log.info("dem - demote a registered account");
    _log.info("ban - ban a registered account");
    _log.info("unban - unban a registered account");
    _log.info("quit - exit this application");

    BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
    L2AccountManager acm = new L2AccountManager();

    String line;
    try {
        while ((line = br.readLine()) != null) {
            line = line.trim();
            Connection con = null;
            switch (acm.getState()) {
            case USER_NAME:
                line = line.toLowerCase();
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("SELECT superuser FROM account WHERE username LIKE ?");
                    ps.setString(1, line);
                    ResultSet rs = ps.executeQuery();
                    if (!rs.next()) {
                        acm.setUser(line);

                        _log.info("Desired password:");
                        acm.setState(ManagerState.PASSWORD);
                    } else {
                        _log.info("User name already in use.");
                        acm.setState(ManagerState.INITIAL_CHOICE);
                    }
                    rs.close();
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not access database!", e);
                    acm.setState(ManagerState.INITIAL_CHOICE);
                } finally {
                    L2Database.close(con);
                }
                break;
            case PASSWORD:
                try {
                    MessageDigest sha = MessageDigest.getInstance("SHA");
                    byte[] pass = sha.digest(line.getBytes("US-ASCII"));
                    acm.setPass(HexUtil.bytesToHexString(pass));
                } catch (NoSuchAlgorithmException e) {
                    _log.fatal("SHA1 is not available!", e);
                    Shutdown.exit(TerminationStatus.ENVIRONMENT_MISSING_COMPONENT_OR_SERVICE);
                } catch (UnsupportedEncodingException e) {
                    _log.fatal("ASCII is not available!", e);
                    Shutdown.exit(TerminationStatus.ENVIRONMENT_MISSING_COMPONENT_OR_SERVICE);
                }
                _log.info("Super user: [y/n]");
                acm.setState(ManagerState.SUPERUSER);
                break;
            case SUPERUSER:
                try {
                    if (line.length() != 1)
                        throw new IllegalArgumentException("One char required.");
                    else if (line.charAt(0) == 'y')
                        acm.setSuper(true);
                    else if (line.charAt(0) == 'n')
                        acm.setSuper(false);
                    else
                        throw new IllegalArgumentException("Invalid choice.");

                    _log.info("Date of birth: [yyyy-mm-dd]");
                    acm.setState(ManagerState.DOB);
                } catch (IllegalArgumentException e) {
                    _log.info("[y/n]?");
                }
                break;
            case DOB:
                try {
                    Date d = Date.valueOf(line);
                    if (d.after(new Date(System.currentTimeMillis())))
                        throw new IllegalArgumentException("Future date specified.");
                    acm.setDob(d);

                    _log.info("Ban reason ID or nothing:");
                    acm.setState(ManagerState.SUSPENDED);
                } catch (IllegalArgumentException e) {
                    _log.info("[yyyy-mm-dd] in the past:");
                }
                break;
            case SUSPENDED:
                try {
                    if (line.length() > 0) {
                        int id = Integer.parseInt(line);
                        acm.setBan(L2BanReason.getById(id));
                    } else
                        acm.setBan(null);

                    try {
                        con = L2Database.getConnection();
                        PreparedStatement ps = con.prepareStatement(
                                "INSERT INTO account (username, password, superuser, birthDate, banReason) VALUES (?, ?, ?, ?, ?)");
                        ps.setString(1, acm.getUser());
                        ps.setString(2, acm.getPass());
                        ps.setBoolean(3, acm.isSuper());
                        ps.setDate(4, acm.getDob());
                        L2BanReason lbr = acm.getBan();
                        if (lbr == null)
                            ps.setNull(5, Types.INTEGER);
                        else
                            ps.setInt(5, lbr.getId());
                        ps.executeUpdate();
                        _log.info("Account " + acm.getUser() + " has been registered.");
                        ps.close();
                    } catch (SQLException e) {
                        _log.error("Could not register an account!", e);
                    } finally {
                        L2Database.close(con);
                    }
                    acm.setState(ManagerState.INITIAL_CHOICE);
                } catch (NumberFormatException e) {
                    _log.info("Ban reason ID or nothing:");
                }
                break;
            case REMOVE:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con.prepareStatement("DELETE FROM account WHERE username LIKE ?");
                    ps.setString(1, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been removed.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not remove an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case PROMOTE:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET superuser = ? WHERE username LIKE ?");
                    ps.setBoolean(1, true);
                    ps.setString(2, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been promoted.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not promote an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case DEMOTE:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET superuser = ? WHERE username LIKE ?");
                    ps.setBoolean(1, false);
                    ps.setString(2, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been demoted.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not demote an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case UNBAN:
                acm.setUser(line.toLowerCase());
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET banReason = ? WHERE username LIKE ?");
                    ps.setNull(1, Types.INTEGER);
                    ps.setString(2, acm.getUser());
                    int cnt = ps.executeUpdate();
                    if (cnt > 0)
                        _log.info("Account " + acm.getUser() + " has been unbanned.");
                    else
                        _log.info("Account " + acm.getUser() + " does not exist!");
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not demote an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            case BAN:
                line = line.toLowerCase();
                try {
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("SELECT superuser FROM account WHERE username LIKE ?");
                    ps.setString(1, line);
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) {
                        acm.setUser(line);

                        _log.info("Ban reason ID:");
                        acm.setState(ManagerState.REASON);
                    } else {
                        _log.info("Account does not exist.");
                        acm.setState(ManagerState.INITIAL_CHOICE);
                    }
                    rs.close();
                    ps.close();
                } catch (SQLException e) {
                    _log.error("Could not access database!", e);
                    acm.setState(ManagerState.INITIAL_CHOICE);
                } finally {
                    L2Database.close(con);
                }
                break;
            case REASON:
                try {
                    int ban = Integer.parseInt(line);
                    con = L2Database.getConnection();
                    PreparedStatement ps = con
                            .prepareStatement("UPDATE account SET banReason = ? WHERE username LIKE ?");
                    ps.setInt(1, ban);
                    ps.setString(2, acm.getUser());
                    ps.executeUpdate();
                    _log.info("Account " + acm.getUser() + " has been banned.");
                    ps.close();
                } catch (NumberFormatException e) {
                    _log.info("Ban reason ID:");
                } catch (SQLException e) {
                    _log.error("Could not ban an account!", e);
                } finally {
                    L2Database.close(con);
                }
                acm.setState(ManagerState.INITIAL_CHOICE);
                break;
            default:
                line = line.toLowerCase();
                if (line.equals("reg")) {
                    _log.info("Desired user name:");
                    acm.setState(ManagerState.USER_NAME);
                } else if (line.equals("rem")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.REMOVE);
                } else if (line.equals("prom")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.PROMOTE);
                } else if (line.equals("dem")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.DEMOTE);
                } else if (line.equals("unban")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.UNBAN);
                } else if (line.equals("ban")) {
                    _log.info("User name:");
                    acm.setState(ManagerState.BAN);
                } else if (line.equals("quit"))
                    Shutdown.exit(TerminationStatus.MANUAL_SHUTDOWN);
                else
                    _log.info("Incorrect command.");
                break;
            }
        }
    } catch (IOException e) {
        _log.fatal("Could not process input!", e);
    } finally {
        IOUtils.closeQuietly(br);
    }
}

From source file:Main.java

/**
 * Sets the optional string.//from ww w  .  j av  a2 s.  c  om
 *
 * @param statement the statement
 * @param string the string
 * @param optionIndex the option index
 * @return the prepared statement
 * @throws SQLException the sQL exception
 */
static PreparedStatement setOptionalString(PreparedStatement statement, String string, int optionIndex)
        throws SQLException {
    if (string != null)
        statement.setString(optionIndex, string);
    else
        statement.setNull(optionIndex, Types.VARCHAR);

    return statement;
}

From source file:com.sf.ddao.factory.param.ParameterHelper.java

public static void bind(PreparedStatement preparedStatement, int idx, Object param, Context context)
        throws SQLException {
    if (param == null) {
        preparedStatement.setNull(idx, java.sql.Types.NULL);
        return;/*from  ww w  . ja v a  2 s.  co m*/
    }
    bind(preparedStatement, idx, param, param.getClass(), context);
}

From source file:org.apache.sqoop.TestExportUsingProcedure.java

/**
 * This test case is special - we're only inserting into a subset of the
 * columns in the table.//w w w. java 2s  .c  o  m
 */
public static void insertFunctiontestColumnsExport(int id, String msg, final int int1, final int int2)
        throws SQLException {
    insertFunction(id, msg, new SetExtraArgs() {
        @Override
        public void set(PreparedStatement on) throws SQLException {
            on.setInt(3, int1);
            on.setNull(4, Types.INTEGER);
            on.setInt(5, int2);
        }
    });
}

From source file:org.kawanfw.test.api.client.autogeneratedkeys.InsertPrepStatementAutoKeysTest.java

/**
 * Do a 100 row insert inside a loop//from  w  w w  . jav a  2  s  .  com
 * 
 * @param connection
 *            the AceQL Connection
 * 
 * @throws Exception
 *             it any Exception occurs
 */
public static void insertPrepStatementExecuteUpdate(Connection connection, int valueToInsert,
        boolean useRawExecute, boolean autoCommitOn) throws Exception {
    // We can now use our Remote JDBC Connection as a regular Connection!

    long maxCustomerId = InsertStatementTestAutoKeysTest.getMaxCustomerId(connection);
    MessageDisplayer.display("");
    MessageDisplayer.display("maxCustomerId: " + maxCustomerId);

    if (!autoCommitOn) {
        connection.setAutoCommit(false);
    }

    // We will do all our remote insert in a SQL Transaction
    try {
        // 1) First create a Customer
        String sql = "insert into customer_auto (customer_title, fname, lname, addressline, town, zipcode, phone) "
                + " values ( ?, ?, ?, ?, ?, ?, ? )";

        MessageDisplayer.display("");
        MessageDisplayer.display("Inserting " + valueToInsert + " customers...");

        PreparedStatement prepStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        int i = valueToInsert;
        int j = 1;
        prepStatement.setString(j++, "Sir");
        prepStatement.setNull(j++, Types.VARCHAR);
        prepStatement.setString(j++, "Smith_" + i);
        prepStatement.setString(j++, i + ", Csar Avenue");
        prepStatement.setString(j++, "JavaLand_" + i);
        prepStatement.setString(j++, i + "45");
        prepStatement.setString(j++, i + "-12345678");

        int rc = -1;

        if (!useRawExecute) {
            rc = prepStatement.executeUpdate();
            MessageDisplayer.display("after executeUpdate(): row count: " + rc);

        } else {
            prepStatement.execute();

            rc = prepStatement.getUpdateCount();
            MessageDisplayer.display("after execute(): prepStatement.getUpdateCount(): " + rc);
        }

        if (!autoCommitOn) {
            connection.commit();
        }

        ResultSet keys = prepStatement.getGeneratedKeys();

        long lastKey = -1;
        while (keys.next()) {
            lastKey = keys.getLong(1);
        }
        keys.close();

        MessageDisplayer.display("Last Key: " + lastKey);

        // Don't know why: there is a bug in some engines....
        // Assert.assertEquals("last key = maxCustomerId + 1", lastKey,
        // maxCustomerId + 1);

        // So do another test:
        Assert.assertEquals("last key >= 1", true, lastKey >= 1);

        prepStatement.close();

    } catch (Exception e) {
        e.printStackTrace();

        if (!autoCommitOn) {
            connection.rollback();
        }

        throw e;
    } finally {
        if (!autoCommitOn) {
            connection.setAutoCommit(true);
        }

    }

}

From source file:com.example.querybuilder.server.Jdbc.java

public static void setNull(PreparedStatement preparedStatement, int parameterNumber, int parameterType) {
    try {// w  w w.ja v  a  2 s. c o  m
        preparedStatement.setNull(parameterNumber, parameterType);
    } catch (SQLException e) {
        throw new SqlRuntimeException(e);
    }
}

From source file:org.kawanfw.test.api.client.InsertPreparedStatementUrlTest.java

/**
 * Do a 100 row insert inside a loop//from   www.  ja v  a2 s  .c o m
 * 
 * @param connection
 *            the AceQL Connection
 * 
 * @param useRawExecute
 *            if true, we will insert using execute()
 * 
 * @throws Exception
 *             it any Exception occurs
 */
public static void insertLoopPrepStatement(Connection connection, int numberToInsert, boolean useRawExecute)
        throws Exception {
    // We can now use our Remote JDBC Connection as a regular Connection!

    if (!useRawExecute) {
        connection.setAutoCommit(false);
    }

    // We will do all our remote insert in a SQL Transaction
    try {
        // 1) First create a Customer
        String sql = "insert into customer values ( ?, ?, ?, ?, ?, ?, ?, ? )";

        MessageDisplayer.display("Inserting " + numberToInsert + " customers...");

        for (int customerId = 1; customerId < numberToInsert + 1; customerId++) {
            PreparedStatement prepStatement = connection.prepareStatement(sql);
            prepStatement.setInt(1, customerId);
            prepStatement.setString(2, "Sir");
            // prepStatement.setString(3, "Jol_" + customerId);
            prepStatement.setNull(3, Types.VARCHAR);
            prepStatement.setString(4, "Smith_" + customerId);
            prepStatement.setURL(5, new URL("http://wwww.kawansoft.com"));
            prepStatement.setString(6, "JavaLand_" + customerId);
            prepStatement.setString(7, customerId + "45");
            prepStatement.setString(8, customerId + "-12345678");

            if (useRawExecute) {
                prepStatement.execute();
            } else {
                prepStatement.executeUpdate();
            }

            prepStatement.close();
        }

        MessageDisplayer.display(new Date() + " Before Commit...");

        // We do either everything in a single transaction or nothing

        if (!useRawExecute) {
            connection.commit(); // Commit is propagated on Server
        }

        MessageDisplayer.display(new Date() + " Remote Commit Done on AceQL Server!");
    } catch (Exception e) {
        e.printStackTrace();
        if (!useRawExecute) {
            connection.rollback();
        }
        throw e;
    } finally {
        connection.setAutoCommit(true);
    }

}