Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

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

Introduction

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

Prototype

void setObject(int parameterIndex, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter using the given object.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    String[] columnNames = { "id", "name", "content", "date_created" };
    Object[] inputValues = new Object[columnNames.length];
    inputValues[0] = new java.math.BigDecimal(100);
    inputValues[1] = new String("String Value");
    inputValues[2] = new String("This is my resume.");
    inputValues[3] = new Timestamp((new java.util.Date()).getTime());

    String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(insert);

    pstmt.setObject(1, inputValues[0]);
    pstmt.setObject(2, inputValues[1]);/*  w ww .j a  v a  2  s .co  m*/
    pstmt.setObject(3, inputValues[2]);
    pstmt.setObject(4, inputValues[3]);

    pstmt.executeUpdate();
    String query = "select id, name, content, date_created from resume where id=?";

    PreparedStatement pstmt2 = conn.prepareStatement(query);
    pstmt2.setObject(1, inputValues[0]);
    ResultSet rs = pstmt2.executeQuery();
    Object[] outputValues = new Object[columnNames.length];
    if (rs.next()) {
        for (int i = 0; i < columnNames.length; i++) {
            outputValues[i] = rs.getObject(i + 1);
        }
    }
    System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString());
    System.out.println("name=" + ((String) outputValues[1]));
    System.out.println("content=" + ((Clob) outputValues[2]));
    System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString());

    rs.close();
    pstmt.close();
    pstmt2.close();
    conn.close();

}

From source file:XMLDBDOM.java

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

    Class.forName("COM.cloudscape.core.JDBCDriver").newInstance();

    Connection conn = DriverManager.getConnection("jdbc:cloudscape:GAMETRADER");

    conn.setAutoCommit(false);/*from  w w  w. j  a  v  a  2 s . c om*/

    Statement s = conn.createStatement();
    s.executeUpdate("CREATE TABLE XMLData(GAMEID INT, MANUAL SERIALIZE(org.w3c.dom.Document))");
    conn.commit();

    File file = new File("XMLData.xml");
    InputStream is = new FileInputStream(file);

    PreparedStatement ps = conn.prepareStatement("INSERT INTO XMLData VALUES(?,?)");

    ps.setInt(1, 1285757);

    DOMParser parser = new DOMParser();
    parser.parse("XMLData.xml");
    Document manual = parser.getDocument();
    ps.setObject(2, manual);

    ps.execute();

    conn.commit();
}

From source file:InsertCustomType2_Oracle.java

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

    // create the Book object
    Book book = new Book(isbn, title, author, edition);
    book.print();// w w w .j a  v  a2  s . c  o  m

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        // create type map
        java.util.Map map = conn.getTypeMap();
        System.out.println("map=" + map);
        map.put("BOOK", Class.forName("Book"));
        System.out.println("map=" + map);

        String insert = "insert into book_table(ID, BOOK) values(?, ?)";
        pstmt = conn.prepareStatement(insert);
        pstmt.setString(1, id);
        pstmt.setObject(2, book);
        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 {
    ResultSet rs = null;//ww w  .j a  v a 2s  .c  o m
    Connection conn = null;
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    conn = getOracleConnection();
    String[] columnNames = { "id", "name", "content", "date_created" };
    Object[] inputValues = new Object[columnNames.length];
    inputValues[0] = new java.math.BigDecimal(100);
    inputValues[1] = new String("String Value");
    inputValues[2] = new String("This is my resume.");
    inputValues[3] = new Timestamp((new java.util.Date()).getTime());

    // prepare blob object from an existing binary column
    String insert = "insert into resume (id, name, content, date_created ) values(?, ?, ?, ?)";
    pstmt = conn.prepareStatement(insert);

    pstmt.setObject(1, inputValues[0]);
    pstmt.setObject(2, inputValues[1]);
    pstmt.setObject(3, inputValues[2]);
    pstmt.setObject(4, inputValues[3]);

    pstmt.executeUpdate();
    String query = "select id, name, content, date_created from resume where id=?";

    pstmt2 = conn.prepareStatement(query);
    pstmt2.setObject(1, inputValues[0]);
    rs = pstmt2.executeQuery();
    Object[] outputValues = new Object[columnNames.length];
    if (rs.next()) {
        for (int i = 0; i < columnNames.length; i++) {
            outputValues[i] = rs.getObject(i + 1);
        }
    }
    System.out.println("id=" + ((java.math.BigDecimal) outputValues[0]).toString());
    System.out.println("name=" + ((String) outputValues[1]));
    System.out.println("content=" + ((Clob) outputValues[2]));
    System.out.println("date_created=" + ((java.sql.Date) outputValues[3]).toString());

    rs.close();
    pstmt.close();
    pstmt2.close();
    conn.close();

}

From source file:org.nuxeo.App.java

public static void main(String[] args) throws SQLException, IOException {

    Properties prop = readProperties();
    String user = prop.getProperty("user");
    String password = prop.getProperty("password");
    String connectionURL = prop.getProperty("url");
    String driver = prop.getProperty("driver");
    String query = prop.getProperty("query");

    log.info("Connect to:" + connectionURL + " from " + getHostName());
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    PrintStream printStream = new PrintStream(baos);
    final ConsoleReporter reporter = new ConsoleReporter(printStream);

    Connection conn = null;/*from w  w  w .  ja  v  a 2s  .  c  om*/
    PreparedStatement ps = null;
    ResultSet rs = null;
    TimerContext tc = null;
    int repeat = Integer.valueOf(System.getProperty(REPEAT_KEY, DEFAULT_REPEAT)).intValue();

    log.info("Submiting " + repeat + " queries: " + query);
    try {
        Class.forName(driver);
        tc = connTimer.time();
        conn = DriverManager.getConnection(connectionURL, user, password);
        tc.stop();
        ps = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        int paramCount = countOccurrences(query, '?');
        for (int i = 1; i <= paramCount; i++) {
            String key = "p" + i;
            String param = prop.getProperty(key);
            if (param == null) {
                break;
            }
            log.info(key + " = " + param);
            String type = "object";
            if (param.contains(":")) {
                type = param.split(":", 2)[0];
                param = param.split(":", 2)[1];
            }
            if (type.equalsIgnoreCase("object")) {
                ps.setObject(i, (Object) param);
            } else if (type.equalsIgnoreCase("string")) {
                ps.setString(i, param);
            } else if (type.equalsIgnoreCase("nstring")) {
                ps.setNString(i, param);
            } else {
                log.warn("Unknown type " + type + " use setObject");
                ps.setObject(i, (Object) param);
            }
        }

        int rows = 0;
        int bytes = 0;

        for (int i = 0; i < repeat; i++) {
            tc = execTimer.time();
            rs = ps.executeQuery();
            tc.stop();
            tc = fetchingTimer.time();
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols = rsmd.getColumnCount();
            while (rs.next()) {
                rows++;
                for (int c = 1; c <= cols; c++) {
                    bytes += rs.getBytes(1).length;
                }
            }
            rs.close();
            tc.stop();
            // don't stress too much
            Thread.sleep((int) (Math.random() * 100));
        }
        log.info("Fetched rows: " + rows + ", total bytes: " + bytes + ", bytes/rows: "
                + ((float) bytes) / rows);

    } catch (SQLException e) {
        log.error(e.getMessage(), e);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
    } finally {
        if (rs != null) {
            rs.close();
        }
        if (ps != null) {
            ps.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
    reporter.run();
    try {
        String content = baos.toString("ISO-8859-1");
        log.info(content);
    } catch (UnsupportedEncodingException e) {
        log.error(e.getMessage(), e);
    }

}

From source file:Blobs.java

public static void main(String args[]) {
    if (args.length != 1) {
        System.err.println("Syntax: <java Blobs [driver] [url] " + "[uid] [pass] [file]");
        return;//from ww w . j av  a2s. co m
    }
    try {
        Class.forName(args[0]).newInstance();
        Connection con = DriverManager.getConnection(args[1], args[2], args[3]);
        File f = new File(args[4]);
        PreparedStatement stmt;

        if (!f.exists()) {
            // if the file does not exist
            // retrieve it from the database and write it to the named file
            ResultSet rs;

            stmt = con.prepareStatement("SELECT blobData " + "FROM BlobTest " + "WHERE fileName = ?");

            stmt.setString(1, args[0]);
            rs = stmt.executeQuery();
            if (!rs.next()) {
                System.out.println("No such file stored.");
            } else {
                Blob b = rs.getBlob(1);
                BufferedOutputStream os;

                os = new BufferedOutputStream(new FileOutputStream(f));
                os.write(b.getBytes(0, (int) b.length()), 0, (int) b.length());
                os.flush();
                os.close();
            }
        } else {
            // otherwise read it and save it to the database
            FileInputStream fis = new FileInputStream(f);
            byte[] tmp = new byte[1024];
            byte[] data = null;
            int sz, len = 0;

            while ((sz = fis.read(tmp)) != -1) {
                if (data == null) {
                    len = sz;
                    data = tmp;
                } else {
                    byte[] narr;
                    int nlen;

                    nlen = len + sz;
                    narr = new byte[nlen];
                    System.arraycopy(data, 0, narr, 0, len);
                    System.arraycopy(tmp, 0, narr, len, sz);
                    data = narr;
                    len = nlen;
                }
            }
            if (len != data.length) {
                byte[] narr = new byte[len];

                System.arraycopy(data, 0, narr, 0, len);
                data = narr;
            }
            stmt = con.prepareStatement("INSERT INTO BlobTest(fileName, " + "blobData) VALUES(?, ?)");
            stmt.setString(1, args[0]);
            stmt.setObject(2, data);
            stmt.executeUpdate();
            f.delete();
        }
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:Main.java

public static long writeJavaObject(Connection conn, Object object) throws Exception {
    String className = object.getClass().getName();
    PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL);
    pstmt.setString(1, className);/*from  w  w w  . ja v  a 2 s  .  c o  m*/
    pstmt.setObject(2, object);
    pstmt.executeUpdate();
    ResultSet rs = pstmt.getGeneratedKeys();
    int id = -1;
    if (rs.next()) {
        id = rs.getInt(1);
    }
    rs.close();
    pstmt.close();
    return id;
}

From source file:com.dangdang.ddframe.rdb.sharding.example.transaction.Main.java

private static void updateFailure(final DataSource dataSource) throws SQLException {
    String sql1 = "UPDATE t_order SET status='UPDATE_1' WHERE user_id=10 AND order_id=1000";
    String sql2 = "UPDATE t_order SET not_existed_column=1 WHERE user_id=1 AND order_id=?";
    String sql3 = "UPDATE t_order SET status='UPDATE_2' WHERE user_id=10 AND order_id=1000";
    SoftTransactionManager transactionManager = new SoftTransactionManager(
            getSoftTransactionConfiguration(dataSource));
    transactionManager.init();/*w  w  w  . ja  va  2  s .  c  o m*/
    BEDSoftTransaction transaction = (BEDSoftTransaction) transactionManager
            .getTransaction(SoftTransactionType.BestEffortsDelivery);
    Connection conn = null;
    try {
        conn = dataSource.getConnection();
        transaction.begin(conn);
        PreparedStatement preparedStatement1 = conn.prepareStatement(sql1);
        PreparedStatement preparedStatement2 = conn.prepareStatement(sql2);
        preparedStatement2.setObject(1, 1000);
        PreparedStatement preparedStatement3 = conn.prepareStatement(sql3);
        preparedStatement1.executeUpdate();
        preparedStatement2.executeUpdate();
        preparedStatement3.executeUpdate();
    } finally {
        transaction.end();
        if (conn != null) {
            conn.close();
        }
    }
}

From source file:SerializeJavaObjects_MySQL.java

public static long writeJavaObject(Connection conn, Object object) throws Exception {
    String className = object.getClass().getName();
    PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL);

    // set input parameters
    pstmt.setString(1, className);//  w  w w  .  j ava  2  s .  c om
    pstmt.setObject(2, object);
    pstmt.executeUpdate();

    // get the generated key for the id
    ResultSet rs = pstmt.getGeneratedKeys();
    int id = -1;
    if (rs.next()) {
        id = rs.getInt(1);
    }

    rs.close();
    pstmt.close();
    System.out.println("writeJavaObject: done serializing: " + className);
    return id;
}

From source file:at.molindo.dbcopy.util.Utils.java

public static <T> T executePrepared(Connection c, String query, ResultSetHandler<T> handler, Object... params)
        throws SQLException {
    PreparedStatement stmt = c.prepareStatement(query);
    try {//ww  w . j a  va  2s  .  c om
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        ResultSet rs = stmt.executeQuery();
        return handle(rs, handler);
    } finally {
        stmt.close();
    }
}