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

public static void main(String[] args) throws Exception {
    try {/*w ww  .j  ava2s  .com*/
        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);

        pstmt.executeUpdate();

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

        rs.close();
        st.close();
        conn.close();
    } catch (SQLException e) {
        while (e != null) {
            String errorMessage = e.getMessage();
            System.err.println("sql error message:" + errorMessage);

            // This vendor-independent string contains a code.
            String sqlState = e.getSQLState();
            System.err.println("sql state:" + sqlState);

            int errorCode = e.getErrorCode();
            System.err.println("error code:" + errorCode);
            // String driverName = conn.getMetaData().getDriverName();
            // System.err.println("driver name:"+driverName);
            // processDetailError(drivername, errorCode);
            e = e.getNextException();
        }

    }
}

From source file:Main.java

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

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

    String INSERT_RECORD = "select * from survey where id < ?";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);

    pstmt.setInt(1, 1);

    pstmt.setFetchSize(200);

    ResultSet rs = pstmt.executeQuery();

    outputResultSet(rs);

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

From source file:Main.java

public static void main(String args[]) throws Exception {
    Connection con = null;
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con = DriverManager.getConnection("jdbc:oracle:thin:@192.201.32.92:1521:psprd1", "username", "password");
    String query = null;/*  ww  w  . jav a2s . c om*/
    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 con = DriverManager.getConnection("jdbc:h2:mem:");
    Statement s = con.createStatement();
    s.execute("CREATE TABLE Table1 (Column1 CLOB)");

    InputStream is = new FileInputStream("data.txt");
    Reader rdr = new InputStreamReader(is, StandardCharsets.ISO_8859_1);
    PreparedStatement ps = con.prepareStatement("INSERT INTO Table1 (Column1) VALUES (?)");
    ps.setCharacterStream(1, rdr);//from w w w .ja  v a  2s  .  co m
    ps.executeUpdate();

    ResultSet rs = s.executeQuery("SELECT Column1 FROM Table1");
    int rowNumber = 0;
    while (rs.next()) {
        String str = rs.getString("Column1");
        System.out.println(String.format("Row %d: CLOB is %d character(s) long.", ++rowNumber, str.length()));
    }
    rs.close();
    con.close();
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);//from  w w  w .jav  a  2s.c o  m
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    String selectSQL = "SELECT USER_ID, USERNAME FROM Person WHERE USER_ID = ?";
    preparedStatement = dbConnection.prepareStatement(selectSQL);
    preparedStatement.setInt(1, 1001);

    ResultSet rs = preparedStatement.executeQuery();

    while (rs.next()) {

        String userid = rs.getString("USER_ID");
        String username = rs.getString("USERNAME");

        System.out.println("userid : " + userid);
        System.out.println("username : " + username);

    }

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

}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);/*from  w  ww.j a va2s .  c  om*/
    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);

    for (int i = 0; i < 10; i++) {
        pstmt.setInt(1, i);
        pstmt.setString(2, "name" + i);
        pstmt.executeUpdate();
    }

    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 {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);/* w  ww .j  a v  a2  s  .  c o  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);
    Statement stmt = connection.createStatement();
    String sql = "DELETE FROM my_table WHERE col_string='a string'";
    int deleteCount = stmt.executeUpdate(sql);
    sql = "DELETE FROM my_table WHERE col_string=?";
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setString(1, "a string");
    deleteCount = pstmt.executeUpdate();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);/* w  ww  . j av a2  s . 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();
    int[] updateCounts = pstmt.executeBatch();
    checkUpdateCounts(updateCounts);
    conn.commit();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
}

From source file:ca.uwaterloo.iss4e.algorithm.PARX.java

public static void main(String[] args) {

    String url = "jdbc:postgresql://localhost/essex";
    Properties props = new Properties();
    props.setProperty("user", "xiliu");
    props.setProperty("password", "Abcd1234");

    try {/*from   w  w  w .j  a  v a  2 s . co m*/
        /* Random generator = new Random();
         BigDecimal[] d = new BigDecimal[40];
         for (int i=0; i<40; ++i){
        d[i] = new BigDecimal(generator.nextInt(10));
        System.out.print(d[i].doubleValue()+"("+i+") |");
         }
         System.out.println("\n ---------------");
         Pair<double[], double[][]> values = PARX.prepareVariable(d, 11, 12, 2, 4);
         double[] Y = values.getKey();
         double X[][] = values.getValue();
         for (int i=0; i<Y.length; ++i){
                
        for (int j=0; j<X[i].length; ++j) {
            System.out.print(X[i][j] + " |");
        }
        System.out.print("|" +Y[i]);
        System.out.println();
         }
        */

        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection(url, props);
        PreparedStatement pstmt = conn.prepareStatement(
                "select array_agg(A.reading) from (select reading from meterreading where homeid=? and readdate between ? and ? order by readdate desc, readtime desc) A ");
        pstmt.setInt(1, 19419);
        pstmt.setDate(2, java.sql.Date.valueOf("2011-04-03"));
        pstmt.setDate(3, java.sql.Date.valueOf("2011-09-07"));

        ResultSet rs = pstmt.executeQuery();

        int order = 3;

        int numOfSeasons = 24;
        int intervalOfUpdateModel = 4; //Every four hours
        int startPredict = 6 * 24;
        int numOfPointsForTraining = 24 * 5;
        double[] pointsForPredict = new double[order];

        if (rs.next()) {
            BigDecimal[] readings = (BigDecimal[]) (rs.getArray(1).getArray());
            double[][] data = new double[2][readings.length + 1];
            int i = 0;
            for (; i < startPredict; ++i) {
                data[0][i] = readings[i].doubleValue();
                data[1][i] = 0.0;
            }

            double[] beta = null;
            int updateModelCount = 0;
            for (; i < readings.length; ++i) {
                // if (updateModelCount%intervalOfUpdateModel==0) {
                // beta = PARX.computePARModel(readings, i, i+1, order, numOfSeasons);
                ++updateModelCount;
                //}
                for (int p = 0; p < order; ++p) {
                    pointsForPredict[order - 1 - p] = readings[i - p].doubleValue();
                }
                data[0][i] = readings[i].doubleValue();
                //   data[1][i+1] = PARX.predict(pointsForPredict, order, beta);
            }

            for (int j = 0; j < readings.length + 1; ++j) {
                System.out.println(data[0][j] + ", " + data[1][j]);
            }

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

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);// ww w.  j  a  v  a  2 s.c om
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
    PreparedStatement preparedStatement = null;
    java.util.Date today = new java.util.Date();
    String insertTableSQL = "INSERT INTO DBUSER" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);
    preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime()));

    dbConnection.commit();
    dbConnection.close();
}