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:InsertStores.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;
    Statement stmt;//from  w w  w.j  av a2  s  .  c  o m
    try {
        Class.forName("myDriver.ClassName");

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

    try {

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

        stmt = con.createStatement();
        con.setAutoCommit(false);

        String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, "
                + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore1);

        String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, "
                + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore2);

        String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, "
                + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore3);

        String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, "
                + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore4);

        String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, "
                + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))";

        stmt.addBatch(insertStore5);

        int[] updateCounts = stmt.executeBatch();

        ResultSet rs = stmt.executeQuery("SELECT * FROM STORES");

        System.out.println("Table STORES after insertion:");
        System.out.println("STORE_NO  LOCATION          COF_TYPE     MGR");
        while (rs.next()) {
            int storeNo = rs.getInt("STORE_NO");
            Struct location = (Struct) rs.getObject("LOCATION");
            Object[] locAttrs = location.getAttributes();
            Array coffeeTypes = rs.getArray("COF_TYPE");
            String[] cofTypes = (String[]) coffeeTypes.getArray();

            Ref managerRef = rs.getRef("MGR");
            PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?");
            pstmt.setRef(1, managerRef);
            ResultSet rs2 = pstmt.executeQuery();
            rs2.next();
            Struct manager = (Struct) rs2.getObject("MANAGER");
            Object[] manAttrs = manager.getAttributes();

            System.out.print(storeNo + "   ");
            System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + "  "
                    + locAttrs[4] + " ");
            for (int i = 0; i < cofTypes.length; i++)
                System.out.print(cofTypes[i] + " ");
            System.out.println(manAttrs[1] + ", " + manAttrs[2]);

            rs2.close();
            pstmt.close();
        }

        rs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

From source file:Main.java

public static Object readJavaObject(Connection conn, long id) throws Exception {
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);//from   www .j a  v  a 2s .com
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    Object object = rs.getObject("object_value");
    String className = object.getClass().getName();
    rs.close();
    pstmt.close();
    return object;
}

From source file:Main.java

public static Object readJavaObject(Connection conn, long id) throws Exception {
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);/*w  w w.  jav  a 2s.  c  o m*/
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    Object object = rs.getObject(1);
    String className = object.getClass().getName();
    rs.close();
    pstmt.close();
    return object;
}

From source file:SerializeJavaObjects_MySQL.java

public static Object readJavaObject(Connection conn, long id) throws Exception {
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);//w  ww . ja v  a2 s.  c om
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    Object object = rs.getObject(1);
    String className = object.getClass().getName();

    rs.close();
    pstmt.close();
    System.out.println("readJavaObject: done de-serializing: " + className);
    return object;
}

From source file:com.xpfriend.fixture.runner.example.ExampleJob.java

/**
 * ??/*from w ww . ja  va  2  s .  c  om*/
 * @param id ?ID
 * @param name?NAME
 * @param connection ?
 */
private static void updateDatabase(int id, String name, Connection connection) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(SQL);
    try {
        statement.setString(1, name);
        statement.setInt(2, id);
        statement.executeUpdate();
    } finally {
        statement.close();
    }
}

From source file:com.afforess.nsdump.Test.java

public static void testRegionDump() throws IOException, SQLException {
    RegionsDump dump = new RegionsDump();
    dump.parse();/*from  w w  w  .  jav  a2s  . com*/

    Connection conn = dump.getDatabaseConnection();
    PreparedStatement statement = conn.prepareStatement("SELECT (numnations) FROM regions");
    ResultSet result = statement.executeQuery();
    int total = 0, regions = 0;
    while (result.next()) {
        total += result.getInt(1);
        regions++;
    }
    System.out.println("Total nations: " + total);
    System.out.println("Total regions: " + regions);
    result.close();
    conn.close();

    File db = new File("./ns-db.h2.db");
    db.delete();
}

From source file:de.unibayreuth.bayceer.bayeos.xmlrpc.ConnectionPool.java

/** Sets the user id for the current session
 *//*from  w  w  w.j av a 2s .co  m*/
private static void setUserId(Connection con, int Id) throws SQLException {
    PreparedStatement st = con.prepareStatement("select set_userid(?)");
    st.setInt(1, Id);
    st.execute();
    st.close();
}

From source file:io.apiman.tools.jdbc.ApimanJdbcServer.java

private static void executeUpdate(Connection connection, String statement) throws SQLException {
    try (PreparedStatement ps = connection.prepareStatement(statement)) {
        ps.executeUpdate();//w  w  w. j  a va  2  s .c  o  m
    }
}

From source file:com.afforess.nsdump.Test.java

public static void testNationDump() throws IOException, SQLException {
    NationsDump dump = new NationsDump();
    dump.parse();//w w w.ja va 2s  .  c o  m

    Connection conn = dump.getDatabaseConnection();
    PreparedStatement statement = conn.prepareStatement("SELECT (name) FROM nations");
    ResultSet result = statement.executeQuery();
    int total = 0;
    while (result.next()) {
        total++;
    }
    result.close();

    System.out.println("Total nations: " + total);

    statement = conn.prepareStatement("SELECT * FROM nations WHERE name = 'sakhovelo'");
    result = statement.executeQuery();
    result.next();
    for (int i = 1; i <= 10; i++) {
        if (i == 4) {
            Clob clob = result.getClob(i);
            String motto = clob.getSubString(1, (int) clob.length());
            String mottoEscaped = StringEscapeUtils.unescapeHtml(motto);
            System.out.println("Raw: " + motto + " Escaped: " + mottoEscaped);
        } else {
            System.out.println(result.getString(i));
        }
    }

    File db = new File("./ns-db.h2.db");
    db.delete();
}

From source file:Main.java

private static void saveAll(Connection conn, List<Person> people) throws SQLException {
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
    for (Person person : people) {
        prep.setString(1, person.getName());
        prep.setString(2, person.getOccupation());
        prep.addBatch();//from ww w. j  av a  2s .c  o m
    }
    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);
    close(prep);
}