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 {
    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 .  jav a 2 s.  com

    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 {
    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);//from  ww w .  jav  a 2 s. com

    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[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn.createStatement();

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

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    java.sql.Time sqlDate = new java.sql.Time(new java.util.Date().getTime());
    pstmt.setTime(2, sqlDate);//w ww.jav  a  2 s .c o  m

    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[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");

    Statement stmt = conn.createStatement();

    createBlobClobTables(stmt);// ww  w.j ava2s. c  o  m

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");

    File file = new File("blob.txt");
    FileInputStream fis = new FileInputStream(file);
    pstmt.setBinaryStream(1, fis, (int) file.length());

    file = new File("clob.txt");
    fis = new FileInputStream(file);
    pstmt.setAsciiStream(2, fis, (int) file.length());
    fis.close();

    pstmt.execute();

    ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
    rs.next();

    java.sql.Blob blob = rs.getBlob(2);
    java.sql.Clob clob = rs.getClob(3);

    byte blobVal[] = new byte[(int) blob.length()];
    InputStream blobIs = blob.getBinaryStream();
    blobIs.read(blobVal);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bos.write(blobVal);
    blobIs.close();

    char clobVal[] = new char[(int) clob.length()];
    Reader r = clob.getCharacterStream();
    r.read(clobVal);
    StringWriter sw = new StringWriter();
    sw.write(clobVal);

    r.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");

    Statement stmt = conn.createStatement();

    createBlobClobTables(stmt);//from www.  j  a  v a 2 s. co  m

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");

    File file = new File("blob.txt");
    FileInputStream fis = new FileInputStream(file);
    pstmt.setBinaryStream(1, fis, (int) file.length());

    file = new File("clob.txt");
    fis = new FileInputStream(file);
    pstmt.setAsciiStream(2, fis, (int) file.length());
    fis.close();

    pstmt.execute();

    ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
    rs.next();

    java.sql.Blob blob = rs.getBlob(2);
    java.sql.Clob clob = rs.getClob("myClobColumn");

    byte blobVal[] = new byte[(int) blob.length()];
    InputStream blobIs = blob.getBinaryStream();
    blobIs.read(blobVal);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bos.write(blobVal);
    blobIs.close();

    char clobVal[] = new char[(int) clob.length()];
    Reader r = clob.getCharacterStream();
    r.read(clobVal);
    StringWriter sw = new StringWriter();
    sw.write(clobVal);

    r.close();
    conn.close();
}

From source file:com.intelius.iap4.TigerLineHit.java

public static void main(String[] args) {

    String _tigerDs = "jdbc:h2:/home/sxu/playground/tiger";
    ResultSet rs = null;//from w ww  . j  av  a 2  s  . com
    PreparedStatement ps = null;
    List<TigerLineHit> ret = new ArrayList<TigerLineHit>();
    try {
        //      if (_tigerDs instanceof JdbcDataSource) {
        //        JdbcDataSource ds = (JdbcDataSource) _tigerDs;
        //        conn = ds.getPooledConnection().getConnection();
        //      }else{
        //        conn = _tigerDs.getConnection();
        //      }

        //try address "540 westerly parkway, state college, pa 16801"

        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection(_tigerDs, "sa", "");
        ps = conn.prepareStatement(generateSelectQuery("PA"));
        int i = 1;
        String streetNum = "540";
        String zip = "16801";
        ps.setString(i++, "Westerly");
        ps.setString(i++, streetNum);
        ps.setString(i++, streetNum);
        ps.setString(i++, streetNum);
        ps.setString(i++, streetNum);
        ps.setString(i++, streetNum);
        ps.setString(i++, streetNum);
        ps.setString(i++, streetNum);
        ps.setString(i++, streetNum);
        ps.setString(i++, zip);
        ps.setString(i++, zip);
        rs = ps.executeQuery();
        while (rs.next()) {
            TigerLineHit hit = new TigerLineHit();
            hit.streetNum = streetNum;
            hit.tlid = rs.getLong("tlid");
            hit.frAddL = rs.getString("fraddl");
            hit.frAddR = rs.getString("fraddr");
            hit.toAddL = rs.getString("toaddl");
            hit.toAddR = rs.getString("toaddr");
            hit.zipL = rs.getString("zipL");
            hit.zipR = rs.getString("zipR");
            hit.toLat = rs.getFloat("tolat");
            hit.toLon = rs.getFloat("tolong");
            hit.frLat = rs.getFloat("frlat");
            hit.frLon = rs.getFloat("tolong");
            hit.lat1 = rs.getFloat("lat1");
            hit.lat2 = rs.getFloat("lat2");
            hit.lat3 = rs.getFloat("lat3");
            hit.lat4 = rs.getFloat("lat4");
            hit.lat5 = rs.getFloat("lat5");
            hit.lat6 = rs.getFloat("lat6");
            hit.lat7 = rs.getFloat("lat7");
            hit.lat8 = rs.getFloat("lat8");
            hit.lat9 = rs.getFloat("lat9");
            hit.lat10 = rs.getFloat("lat10");
            hit.lon1 = rs.getFloat("long1");
            hit.lon2 = rs.getFloat("long2");
            hit.lon3 = rs.getFloat("long3");
            hit.lon4 = rs.getFloat("long4");
            hit.lon5 = rs.getFloat("long5");
            hit.lon6 = rs.getFloat("long6");
            hit.lon7 = rs.getFloat("long7");
            hit.lon8 = rs.getFloat("long8");
            hit.lon9 = rs.getFloat("long9");
            hit.lon10 = rs.getFloat("long10");
            hit.fedirp = rs.getString("fedirp");
            hit.fetype = rs.getString("fetype");
            hit.fedirs = rs.getString("fedirs");
            ret.add(hit);

            //            
            System.out.println(ret.toString());
            //
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(ps);
    }
    //return ret;
}

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 TIMESTAMP );");

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

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.executeUpdate();/*from   w w  w .ja  v  a 2s . com*/

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

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

    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 {
    Connection conn = getConnection();
    Statement st = conn.createStatement();

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

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    java.sql.Timestamp sqlDate = new java.sql.Timestamp(new java.util.Date().getTime());
    pstmt.setTimestamp(2, sqlDate);//from  w  w  w.j a v  a  2 s. c o m

    pstmt.executeUpdate();

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

    outputResultSet(rs);

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

From source file:InsertCustomType_Oracle.java

public static void main(String[] args) {
    String id = "001";
    String isbn = "1234567890";
    String title = "java demo";
    String author = "java2s";
    int edition = 1;

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {/*from   w ww  .j a  va2  s. c o m*/
        conn = getConnection();
        String insert = "insert into book_table values(?, BOOK(?, ?, ?, ?))";
        pstmt = conn.prepareStatement(insert);
        pstmt.setString(1, id);
        pstmt.setString(2, isbn);
        pstmt.setString(3, title);
        pstmt.setString(4, author);
        pstmt.setInt(5, edition);
        pstmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
        System.exit(1);
    } finally {
        try {
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

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  .  ja  va2s . c  o m*/

    pstmt.executeUpdate();

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

    Calendar cal = Calendar.getInstance();

    // get the TimeZone for "America/Los_Angeles"
    TimeZone tz = TimeZone.getTimeZone("America/Los_Angeles");
    cal.setTimeZone(tz);

    while (rs.next()) {
        // the JDBC driver will use the time zone information in
        // Calendar to calculate the date, with the result that
        // the variable dateCreated contains a java.sql.Date object
        // that is accurate for "America/Los_Angeles".
        java.sql.Date dateCreated = rs.getDate(2, cal);
        System.out.println(dateCreated);
    }

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