Example usage for java.sql PreparedStatement setString

List of usage examples for java.sql PreparedStatement setString

Introduction

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

Prototype

void setString(int parameterIndex, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

From source file:DemoPreparedStatementSetAsciiStream.java

public static void main(String[] args) {
    Connection conn = null;//w w w .j  a v a  2s  .  c  om
    PreparedStatement pstmt = null;
    String query = null;
    try {
        conn = getConnection();
        String fileName = "fileName.txt";
        File file = new File(fileName);
        int fileLength = (int) file.length();
        InputStream stream = (InputStream) new FileInputStream(file);

        query = "insert into  LONG_VARCHAR_TABLE(id, stream) values(?, ?)";
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, fileName);
        pstmt.setAsciiStream(2, stream, fileLength);

        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
    } catch (Exception e) {
        e.printStackTrace();
    } 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 = null;//from w  w  w  . j  ava 2s . co  m
    PreparedStatement pstmt = null;
    java.sql.Array sqlArray = null;
    conn = getOracleConnection();
    //  ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
    String[] content = { "v1", "v2", "v3", "v4" };
    // sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);

    String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)";

    pstmt = conn.prepareStatement(query);
    pstmt.setString(1, "0001");
    pstmt.setArray(2, sqlArray);

    int rowCount = pstmt.executeUpdate();
    System.out.println("rowCount=" + rowCount);
    System.out.println("--Demo_PreparedStatement_SetArray end--");
    pstmt.close();
    conn.close();
}

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();//from   w w w .  ja va2  s  .c  om

    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[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);//from   w w  w  .  ja  va 2s . c  om
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    String insertTableSQL = "INSERT INTO Person" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);

    dbConnection.setAutoCommit(false);

    java.util.Date today = new java.util.Date();

    preparedStatement.setInt(1, 101);
    preparedStatement.setString(2, "101");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 102);
    preparedStatement.setString(2, "102");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 103);
    preparedStatement.setString(2, "103");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4, new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.executeBatch();

    dbConnection.commit();

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

}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);//w  ww  .ja va2 s.  co  m
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    PreparedStatement preparedStatementInsert = null;
    PreparedStatement preparedStatementUpdate = null;

    String insertTableSQL = "INSERT INTO Person" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";

    String updateTableSQL = "UPDATE Person SET USERNAME =? " + "WHERE USER_ID = ?";

    java.util.Date today = new java.util.Date();
    dbConnection.setAutoCommit(false);

    preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
    preparedStatementInsert.setInt(1, 9);
    preparedStatementInsert.setString(2, "101");
    preparedStatementInsert.setString(3, "system");
    preparedStatementInsert.setTimestamp(4, new java.sql.Timestamp(today.getTime()));
    preparedStatementInsert.executeUpdate();

    preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
    preparedStatementUpdate.setString(1, "new string");
    preparedStatementUpdate.setInt(2, 999);
    preparedStatementUpdate.executeUpdate();

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

From source file:SetSavepoint.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    try {/*from w  w w .  j a va2 s.  com*/

        Class.forName("myDriver.className");

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

    try {

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

        String query = "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE TOTAL > ?";
        String update = "UPDATE COFFEES SET PRICE = ? " + "WHERE COF_NAME = ?";

        PreparedStatement getPrice = con.prepareStatement(query);
        PreparedStatement updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 7000);
        ResultSet rs = getPrice.executeQuery();

        Savepoint save1 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save1);
            }

        }

        getPrice = con.prepareStatement(query);
        updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 8000);

        rs = getPrice.executeQuery();
        System.out.println();

        Savepoint save2 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save2);
            }
        }

        con.commit();

        Statement stmt = con.createStatement();
        rs = stmt.executeQuery("SELECT COF_NAME, " + "PRICE FROM COFFEES");

        System.out.println();
        while (rs.next()) {
            String name = rs.getString("COF_NAME");
            float price = rs.getFloat("PRICE");
            System.out.println("Current price of " + name + " is " + price);
        }

        con.close();

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

}

From source file:DemoPreparedStatementSetBinaryStream.java

public static void main(String[] args) throws Exception {
    String smallFileName = "smallFileName.dat";
    String largeFileName = "largeFileName.dat";
    Connection conn = null;// ww w  .  j a v a  2s  .co  m
    PreparedStatement pstmt = null;
    try {
        conn = getConnection();
        File smallFile = new File(smallFileName);
        int smallFileLength = (int) smallFile.length();
        InputStream smallStream = (InputStream) new FileInputStream(smallFile);
        File largeFile = new File(largeFileName);
        int largeFileLength = (int) largeFile.length();
        InputStream largeStream = (InputStream) new FileInputStream(largeFile);

        String query = "insert into binary_table(id, raw_column, long_raw_column) values(?, ?, ?)";

        conn.setAutoCommit(false);
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0001");
        pstmt.setBinaryStream(2, smallStream, smallFileLength);
        pstmt.setBinaryStream(3, largeStream, largeFileLength);

        // execute query, and return number of rows created
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        conn.commit();
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:de.tudarmstadt.ukp.csniper.resbuild.EvaluationItemFixer.java

public static void main(String[] args) {
    connect(HOST, DATABASE, USER, PASSWORD);

    Map<Integer, String> items = new HashMap<Integer, String>();
    Map<Integer, String> failed = new HashMap<Integer, String>();

    // fetch coveredTexts of dubious items and clean it
    PreparedStatement select = null;
    try {/*from  ww w. j  ava2  s. c o  m*/
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.append("SELECT * FROM EvaluationItem ");
        selectQuery.append("WHERE LOCATE(coveredText, '  ') > 0 ");
        selectQuery.append("OR LOCATE('" + LRB + "', coveredText) > 0 ");
        selectQuery.append("OR LOCATE('" + RRB + "', coveredText) > 0 ");
        selectQuery.append("OR LEFT(coveredText, 1) = ' ' ");
        selectQuery.append("OR RIGHT(coveredText, 1) = ' ' ");

        select = connection.prepareStatement(selectQuery.toString());
        log.info("Running query [" + selectQuery.toString() + "].");
        ResultSet rs = select.executeQuery();

        while (rs.next()) {
            int id = rs.getInt("id");
            String coveredText = rs.getString("coveredText");

            try {
                // special handling of double whitespace: in this case, re-fetch the text
                if (coveredText.contains("  ")) {
                    coveredText = retrieveCoveredText(rs.getString("collectionId"), rs.getString("documentId"),
                            rs.getInt("beginOffset"), rs.getInt("endOffset"));
                }

                // replace bracket placeholders and trim the text
                coveredText = StringUtils.replace(coveredText, LRB, "(");
                coveredText = StringUtils.replace(coveredText, RRB, ")");
                coveredText = coveredText.trim();

                items.put(id, coveredText);
            } catch (IllegalArgumentException e) {
                failed.put(id, e.getMessage());
            }
        }
    } catch (SQLException e) {
        log.error("Exception while selecting: " + e.getMessage());
    } finally {
        closeQuietly(select);
    }

    // write logs
    BufferedWriter bwf = null;
    BufferedWriter bws = null;
    try {
        bwf = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(LOG_FAILED)), "UTF-8"));
        for (Entry<Integer, String> e : failed.entrySet()) {
            bwf.write(e.getKey() + " - " + e.getValue() + "\n");
        }

        bws = new BufferedWriter(
                new OutputStreamWriter(new FileOutputStream(new File(LOG_SUCCESSFUL)), "UTF-8"));
        for (Entry<Integer, String> e : items.entrySet()) {
            bws.write(e.getKey() + " - " + e.getValue() + "\n");
        }
    } catch (IOException e) {
        log.error("Got an IOException while writing the log files.");
    } finally {
        IOUtils.closeQuietly(bwf);
        IOUtils.closeQuietly(bws);
    }

    log.info("Texts for [" + items.size() + "] items need to be cleaned up.");

    // update the dubious items with the cleaned coveredText
    PreparedStatement update = null;
    try {
        String updateQuery = "UPDATE EvaluationItem SET coveredText = ? WHERE id = ?";

        update = connection.prepareStatement(updateQuery);
        int i = 0;
        for (Entry<Integer, String> e : items.entrySet()) {
            int id = e.getKey();
            String coveredText = e.getValue();

            // update item in database
            update.setString(1, coveredText);
            update.setInt(2, id);
            update.executeUpdate();
            log.debug("Updating " + id + " with [" + coveredText + "]");

            // show percentage of updated items
            i++;
            int part = (int) Math.ceil((double) items.size() / 100);
            if (i % part == 0) {
                log.info(i / part + "% finished (" + i + "/" + items.size() + ").");
            }
        }
    } catch (SQLException e) {
        log.error("Exception while updating: " + e.getMessage());
    } finally {
        closeQuietly(update);
    }

    closeQuietly(connection);
}

From source file:DemoPreparedStatementSetCharacterStream.java

public static void main(String[] args) throws Exception {
    String fileName = "charDataFile.txt";
    Reader fileReader = null;/*w ww.java 2s  .  com*/
    long fileLength = 0;
    Connection conn = null;
    PreparedStatement pstmt = null;

    try {
        File file = new File(fileName);
        fileLength = file.length();
        fileReader = (Reader) new BufferedReader(new FileReader(file));
        System.out.println("fileName=" + fileName);
        System.out.println("fileLength=" + fileLength);

        conn = getConnection();
        // begin transaction
        conn.setAutoCommit(false);
        // prepare SQL query for inserting a new row using SetCharacterStream()
        String query = "insert into char_stream_table (id, char_stream_column) values(?, ?)";
        // create PrepareStatement object
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, fileName);
        pstmt.setCharacterStream(2, fileReader, (int) fileLength);
        // execute query, and return number of rows created
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        // end transaction
        conn.commit();
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = null;/*from   w w  w.ja v  a2s  .c  o  m*/
    PreparedStatement pstmt = null;
    java.sql.Array sqlArray = null;
    conn = getOracleConnection();
    // For oracle you need an array descriptor specifying
    // the type of the array and a connection to the database
    // the first parameter must match with the SQL ARRAY type created
    ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("CHAR_ARRAY", conn);
    // then obtain an Array filled with the content below
    String[] content = { "v1", "v2", "v3", "v4" };
    sqlArray = new oracle.sql.ARRAY(arrayDescriptor, conn, content);

    String query = "insert into CHAR_ARRAY_TABLE(id, array) values(?, ?)";

    pstmt = conn.prepareStatement(query);
    pstmt.setString(1, "0001");
    pstmt.setArray(2, sqlArray);

    int rowCount = pstmt.executeUpdate();
    System.out.println("rowCount=" + rowCount);
    System.out.println("--Demo_PreparedStatement_SetArray end--");
    pstmt.close();
    conn.close();
}