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

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);//from   w  ww  .j a v a2s.  co  m
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");

    String INSERT_RECORD = "insert into survey(id, name) values(?,?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);

    pstmt.setInt(1, 1);
    pstmt.setString(2, "name");
    pstmt.executeUpdate();

    // Get warnings on PreparedStatement object
    SQLWarning warning = pstmt.getWarnings();
    while (warning != null) {
        // Process statement warnings...
        String message = warning.getMessage();
        String sqlState = warning.getSQLState();
        int errorCode = warning.getErrorCode();
        warning = warning.getNextWarning();
    }

    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 {
    try {//from  w w  w. j  a  v a2s  .  c  o  m
        String url = "jdbc:odbc:databaseName";
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String user = "guest";
        String password = "guest";
        FileInputStream fis = new FileInputStream("somefile.txt");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement createTable = connection.createStatement();

        createTable.executeUpdate("CREATE TABLE source_code (name CHAR(20), source LONGTEXT)");

        String ins = "INSERT INTO source_code VALUES(?,?)";
        PreparedStatement statement = connection.prepareStatement(ins);

        statement.setString(1, "TryInputStream"); // Set first field
        statement.setAsciiStream(2, fis, fis.available()); // Stream is source

        int rowsUpdated = statement.executeUpdate();
        System.out.println("Rows affected: " + rowsUpdated);
        connection.close();
    } catch (Exception e) {
        System.err.println(e);
    }
}

---

From source file:ConnectionPoolBasics.java

public static void main(String args[]) throws Exception {

    GenericObjectPool gPool = new GenericObjectPool();

    /*Class.forName("com.mysql.jdbc.Driver");
            /*from  w  ww  .  j a  v a 2s . com*/
    DriverManagerConnectionFactory cf =
       new DriverManagerConnectionFactory(
    "jdbc:mysql://localhost/commons", "root", "");*/

    Properties props = new Properties();
    props.setProperty("Username", "root");
    props.setProperty("Password", "");
    ConnectionFactory cf = new DriverConnectionFactory(new com.mysql.jdbc.Driver(),
            "jdbc:mysql://localhost/commons", props);

    KeyedObjectPoolFactory kopf = new GenericKeyedObjectPoolFactory(null, 8);

    PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, gPool, kopf, null, false, true);

    for (int i = 0; i < 5; i++) {
        gPool.addObject();
    }

    // PoolingDataSource pds = new PoolingDataSource(gPool);
    PoolingDriver pd = new PoolingDriver();
    pd.registerPool("example", gPool);

    for (int i = 0; i < 5; i++) {
        gPool.addObject();
    }

    Connection conn = java.sql.DriverManager.getConnection("jdbc:apache:commons:dbcp:example");

    System.err.println("Connection: " + conn); //": Delegate: " + ((org.apache.commons.dbcp.PoolingConnection)conn).getDelegate());

    // do some work with the connection
    PreparedStatement ps = conn.prepareStatement("Select * from customer where id = ?");

    System.err.println("Active: " + gPool.getNumActive() + ", Idle: " + gPool.getNumIdle());

    conn.close();

    System.err.println("Active: " + gPool.getNumActive() + ", Idle: " + gPool.getNumIdle());

}

---

From source file:DemoPreparedStatementSetIntegers.java

public static void main(String[] args) throws Exception {
    String id = "0001";
    byte byteValue = 1;
    short shortValue = 1;
    int intValue = 12345;
    long longValue = 100000000L;

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {/*  w  w  w.  j  a  va  2s  .co  m*/
        conn = getConnection();
        String query = "insert into integer_table(id, byte_column, "
                + "short_column, int_column, long_column) values(?, ?, ?, ?, ?)";

        // create PrepareStatement object
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, id);
        pstmt.setByte(2, byteValue);
        pstmt.setShort(3, shortValue);
        pstmt.setInt(4, intValue);
        pstmt.setLong(5, longValue);

        // 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:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);/*from  www  .  ja v a 2s . co m*/
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");

    String INSERT_RECORD = "insert into survey(id, name) values(?,?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setString(2, "name1");
    pstmt.addBatch();

    pstmt.setString(1, "2");
    pstmt.setString(2, "name2");
    pstmt.addBatch();

    // execute the batch
    int[] updateCounts = pstmt.executeBatch();

    checkUpdateCounts(updateCounts);

    // since there were no errors, commit
    conn.commit();

    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[] argv) throws Exception {
    Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/old", "user", "pass");
    Connection con1 = DriverManager.getConnection("jdbc:postgresql://localhost:5432/new", "user", "pass");

    String sql = "INSERT INTO users(" + "name," + "active," + "login," + "password)" + "VALUES(?,?,?,?)";

    Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

    PreparedStatement pstmt = con1.prepareStatement(sql);

    ResultSet rs = statement.executeQuery("SELECT * FROM users");
    while (rs.next()) {
        String nm = rs.getString(2);
        Boolean ac = rs.getBoolean(3);
        String log = rs.getString(4);
        String pass = rs.getString(5);
        pstmt.setString(1, nm);/*from   w  w  w .j a  v a 2s.co m*/
        pstmt.setBoolean(2, ac);
        pstmt.setString(3, log);
        pstmt.setString(4, pass);
        pstmt.executeUpdate();
    }
    con.close();
    con1.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");
    java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
    pstmt.setDate(2, sqlDate);// w w  w .j a  v a 2 s .c o m

    pstmt.executeUpdate();

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

    ResultSetMetaData rsmd = rs.getMetaData();

    int numCols = rsmd.getColumnCount();

    System.out.print("\t");
    for (int i = 1; i <= numCols; i++) {
        System.out.println(rsmd.getColumnLabel(i));
    }

    System.out.print("\nAuto Increment\t");
    for (int i = 1; i <= numCols; i++) {
        System.out.println(rsmd.isAutoIncrement(i));
    }
    for (int i = 1; i <= numCols; i++) {

        System.out.println(rsmd.isCaseSensitive(i));
    }
    System.out.print("\nSearchable\t");
    for (int i = 1; i <= numCols; i++) {

        System.out.println(rsmd.isSearchable(i));
    }
    System.out.print("\nCurrency\t");
    for (int i = 1; i <= numCols; i++) {
        System.out.println(rsmd.isCurrency(i));
    }
    System.out.print("\nAllows nulls\t");
    for (int i = 1; i <= numCols; i++) {

        System.out.println(rsmd.isNullable(i));
    }
    System.out.print("\nSigned\t");
    for (int i = 1; i <= numCols; i++) {

        System.out.println(rsmd.isSigned(i));
    }
    System.out.print("\nRead only\t");
    for (int i = 1; i <= numCols; i++) {

        System.out.println(rsmd.isReadOnly(i));
    }
    System.out.print("\nWritable\t");
    for (int i = 1; i <= numCols; i++) {

        System.out.print(rsmd.isWritable(i));
    }
    System.out.print("\nDefinitely Writable\t");
    for (int i = 1; i <= numCols; i++) {

        System.out.println(rsmd.isDefinitelyWritable(i));
    }

    conn.close();
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    String deptName = "oldName";
    String newDeptName = "newName";

    ResultSet rs = null;/*from  ww w  .  j a  va 2s . c  o m*/
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {
        conn = getConnection();
        // prepare query for getting a REF object and PrepareStatement object
        String refQuery = "select manager from dept_table where dept_name=?";
        pstmt = conn.prepareStatement(refQuery);
        pstmt.setString(1, deptName);
        rs = pstmt.executeQuery();
        java.sql.Ref ref = null;
        if (rs.next()) {
            ref = rs.getRef(1);
        }
        if (ref == null) {
            System.out.println("error: could not get a reference for manager.");
            System.exit(1);
        }
        String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)";
        pstmt2 = conn.prepareStatement(query);
        pstmt2.setString(1, newDeptName);
        pstmt2.setRef(2, ref);
        // execute query, and return number of rows created
        int rowCount = pstmt2.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } finally {
        pstmt.close();
        pstmt2.close();
        conn.close();
    }
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    String deptName = "oldName";
    String newDeptName = "newName";

    ResultSet rs = null;//from  w ww  .  j av  a2 s  .c o  m
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {
        conn = getConnection();
        // prepare query for getting a REF object and PrepareStatement object
        String refQuery = "select manager from dept_table where dept_name=?";
        pstmt = conn.prepareStatement(refQuery);
        pstmt.setString(1, deptName);
        rs = pstmt.executeQuery();
        java.sql.Ref ref = null;
        if (rs.next()) {
            ref = rs.getRef("manager");
        }
        if (ref == null) {
            System.out.println("error: could not get a reference for manager.");
            System.exit(1);
        }
        String query = "INSERT INTO dept_table(dept_name, manager)values(?, ?)";
        pstmt2 = conn.prepareStatement(query);
        pstmt2.setString(1, newDeptName);
        pstmt2.setRef(2, ref);
        // execute query, and return number of rows created
        int rowCount = pstmt2.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } finally {
        pstmt.close();
        pstmt2.close();
        conn.close();
    }
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    try {// w w w . ja  v  a 2s .com
        String url = "jdbc:odbc:yourdatabasename";
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String user = "guest";
        String password = "guest";

        FileInputStream fis = new FileInputStream("sometextfile.txt");

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement createTable = connection.createStatement();
        createTable.executeUpdate("CREATE TABLE source_code (name char(20), source LONGTEXT)");
        String ins = "INSERT INTO source_code VALUES(?,?)";
        PreparedStatement statement = connection.prepareStatement(ins);

        statement.setString(1, "TryInputStream2");
        statement.setAsciiStream(2, fis, fis.available());

        int rowsUpdated = statement.executeUpdate();
        System.out.println("Rows affected: " + rowsUpdated);
        Statement getCode = connection.createStatement();
        ResultSet theCode = getCode.executeQuery("SELECT name,source FROM source_code");
        BufferedReader reader = null;
        String input = null;

        while (theCode.next()) {
            reader = new BufferedReader(new InputStreamReader(theCode.getAsciiStream(2)));
            while ((input = reader.readLine()) != null) {
                System.out.println(input);
            }
        }
        connection.close();
    } catch (Exception e) {
        System.err.println(e);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• »