Example usage for java.sql PreparedStatement setInt

List of usage examples for java.sql PreparedStatement setInt

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

Introduction

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

Prototype

void setInt(int parameterIndex, int x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java int value.

Usage

From source file:Main.java

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

    stmt.executeUpdate("create table survey (id int, id2 tinyint, id3 smallint, id4 bigint, id5 real);");

    String sql = "INSERT INTO survey (id2,id3,id4,id5) VALUES(?,?,?,?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);

    byte b = 1;//w  ww .jav  a  2 s .c  o  m
    short s = 2;
    pstmt.setByte(1, b);
    pstmt.setShort(2, s);
    pstmt.setInt(3, 3);
    pstmt.setLong(4, 4L);
    pstmt.executeUpdate();

    ResultSet rs = stmt.executeQuery("SELECT * FROM survey");
    while (rs.next()) {
        System.out.println(rs.getString(2));
    }

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

From source file:Main.java

public static void main(String[] args) throws Exception {
    String url = "jdbc:mysql://localhost/testdb";
    String username = "root";
    String password = "";
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = null;/*  w  ww  .  ja  v  a  2s  .c om*/
    try {
        conn = DriverManager.getConnection(url, username, password);
        conn.setAutoCommit(false);

        Statement st = conn.createStatement();
        st.execute("INSERT INTO orders (username, order_date) VALUES ('java', '2007-12-13')",
                Statement.RETURN_GENERATED_KEYS);

        ResultSet keys = st.getGeneratedKeys();
        int id = 1;
        while (keys.next()) {
            id = keys.getInt(1);
        }
        PreparedStatement pst = conn.prepareStatement(
                "INSERT INTO order_details (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)");
        pst.setInt(1, id);
        pst.setString(2, "1");
        pst.setInt(3, 10);
        pst.setDouble(4, 100);
        pst.execute();

        conn.commit();
        System.out.println("Transaction commit...");
    } catch (SQLException e) {
        if (conn != null) {
            conn.rollback();
            System.out.println("Connection rollback...");
        }
        e.printStackTrace();
    } finally {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);/* w w  w.j  a v  a2  s .  co  m*/

    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";

    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "INSERT INTO mysql_all_table(" + "col_boolean," + "col_byte," + "col_short," + "col_int,"
            + "col_long," + "col_float," + "col_double," + "col_bigdecimal," + "col_string," + "col_date,"
            + "col_time," + "col_timestamp," + "col_asciistream," + "col_binarystream," + "col_blob) "
            + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);

    pstmt.setBoolean(1, true);
    pstmt.setByte(2, (byte) 123);
    pstmt.setShort(3, (short) 123);
    pstmt.setInt(4, 123);
    pstmt.setLong(5, 123L);
    pstmt.setFloat(6, 1.23F);
    pstmt.setDouble(7, 1.23D);
    pstmt.setBigDecimal(8, new BigDecimal(1.23));
    pstmt.setString(9, "a string");
    pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis()));
    pstmt.setTime(11, new Time(System.currentTimeMillis()));
    pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis()));

    File file = new File("infilename1");
    FileInputStream is = new FileInputStream(file);
    pstmt.setAsciiStream(13, is, (int) file.length());

    file = new File("infilename2");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(14, is, (int) file.length());

    file = new File("infilename3");
    is = new FileInputStream(file);
    pstmt.setBinaryStream(15, is, (int) file.length());

    pstmt.executeUpdate();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = null;//  w  ww. ja  va  2s  .  co  m
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "insert into message values(?, ?, ?)";

        pstmt = conn.prepareStatement(query); // create a statement
        pstmt.setInt(1, 5); // set input parameter 1
        pstmt.setString(2, "head5"); // set input parameter 2
        pstmt.setString(3, "data5"); // set input parameter 3
        pstmt.executeUpdate(); // execute insert statement
        pstmt = conn.prepareStatement("insert into msgtag values(?, ?, ?)"); // create a statement
        pstmt.setInt(1, 55); // set input parameter 1
        pstmt.setString(2, "tag5"); // set input parameter 2
        pstmt.setInt(3, 5); // set input parameter 3
        pstmt.executeUpdate(); // execute insert statement
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
    st.executeUpdate("insert into survey (id,name ) values (2,'anotherValue')");

    ResultSet rs = null;// w w w.j  a  va 2 s.  c  o m
    PreparedStatement ps = null;

    String query = "select id, name from survey where id = ?";

    ps = conn.prepareStatement(query);
    // specify values for all input parameters
    ps.setInt(1, 001); // set the first parameter: id

    // now, PreparedStatement object is ready to be executed.
    rs = ps.executeQuery();
    // iterate the result set object
    while (rs.next()) {
        int id = rs.getInt(1);
        String name = rs.getString(2);
        System.out.println("[id=" + id + "][name=" + name + "]");
    }

    // NOTE: you may use PreparedStatement as many times as you want
    // here we use it for another set of parameters:
    ps.setInt(1, 002); // set the first parameter: id

    // now, PreparedStatement object is ready to be executed.
    rs = ps.executeQuery();
    // iterate the result set object
    while (rs.next()) {
        int id = rs.getInt(1);
        String name = rs.getString(2);
        System.out.println("[id=" + id + "][name=" + name + "]");
    }
    rs.close();
    ps.close();
    conn.close();

}

From source file:SelectRecordsUsingPreparedStatement.java

public static void main(String[] args) {
    ResultSet rs = null;//w w  w. j av  a  2 s .  c  om
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "select deptno, deptname, deptloc from dept where deptno > ?";

        pstmt = conn.prepareStatement(query); // create a statement
        pstmt.setInt(1, 1001); // set input parameter
        rs = pstmt.executeQuery();
        // extract data from the ResultSet
        while (rs.next()) {
            int dbDeptNumber = rs.getInt(1);
            String dbDeptName = rs.getString(2);
            String dbDeptLocation = rs.getString(3);
            System.out.println(dbDeptNumber + "\t" + dbDeptName + "\t" + dbDeptLocation);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);/* www . ja v  a2 s  .c  om*/
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
    String deleteSQL = "DELETE Person WHERE USER_ID = ?";
    preparedStatement = dbConnection.prepareStatement(deleteSQL);
    preparedStatement.setInt(1, 1001);

    preparedStatement.executeUpdate();
    preparedStatement.close();
    dbConnection.close();

}

From source file:SetSavepoint.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    try {//from ww w  .  j  a  va 2 s.  co m

        Class.forName("myDriver.className");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");
        con.setAutoCommit(false);

        String query = "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE TOTAL > ?";
        String update = "UPDATE COFFEES SET PRICE = ? " + "WHERE COF_NAME = ?";

        PreparedStatement getPrice = con.prepareStatement(query);
        PreparedStatement updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 7000);
        ResultSet rs = getPrice.executeQuery();

        Savepoint save1 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save1);
            }

        }

        getPrice = con.prepareStatement(query);
        updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 8000);

        rs = getPrice.executeQuery();
        System.out.println();

        Savepoint save2 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save2);
            }
        }

        con.commit();

        Statement stmt = con.createStatement();
        rs = stmt.executeQuery("SELECT COF_NAME, " + "PRICE FROM COFFEES");

        System.out.println();
        while (rs.next()) {
            String name = rs.getString("COF_NAME");
            float price = rs.getFloat("PRICE");
            System.out.println("Current price of " + name + " is " + price);
        }

        con.close();

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

}

From source file:DemoUpdatableResultSet.java

public static void main(String[] args) {
    ResultSet rs = null;/*from  w w  w. j  a v a2  s  . c om*/
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "select id, name, age from employees where age > ?";
        pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pstmt.setInt(1, 20); // set input values
        rs = pstmt.executeQuery(); // create an updatable ResultSet
                                   // update a column value in the current row.
        rs.absolute(2); // moves the cursor to the 2nd row of rs
        rs.updateString("name", "newName"); // updates the 'name' column of row 2 to newName
        rs.updateRow(); // updates the row in the data source
                        // insert column values into the insert row.
        rs.moveToInsertRow(); // moves cursor to the insert row
        rs.updateInt(1, 1234); // 1st column id=1234
        rs.updateString(2, "newName"); // updates the 2nd column
        rs.updateInt(3, 99); // updates the 3rd column to 99
        rs.insertRow();
        rs.moveToCurrentRow();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:UpdateRecordsUsingPreparedStatement.java

public static void main(String[] args) throws Exception {
    Connection conn = null;/*from ww w .  j a  va2s  .c o m*/
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        String query = "update dept set DEPT_LOC = ? where DEPT_NUM = ? ";
        pstmt = conn.prepareStatement(query); // create a statement
        pstmt.setString(1, "deptLocation"); // set input parameter 1
        pstmt.setInt(2, 1001); // set input parameter 2
        pstmt.executeUpdate(); // execute update statement
    } catch (Exception e) {
        e.printStackTrace();
        System.exit(1);
    } finally {
        pstmt.close();
        conn.close();
    }
}