List of usage examples for java.sql PreparedStatement executeUpdate
int executeUpdate() throws SQLException;
PreparedStatement
object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT
, UPDATE
or DELETE
; or an SQL statement that returns nothing, such as a DDL statement. 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 www . ja v a 2 s .co 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:DemoPreparedStatementSetBlob.java
public static void main(String[] args) throws Exception { Connection conn = null;/*from w w w .j ava 2 s .c o m*/ PreparedStatement pstmt = null; ResultSet rs = null; java.sql.Blob blob = null; try { conn = getConnection(); // prepare blob object from an existing binary column pstmt = conn.prepareStatement("select photo from my_pictures where id = ?"); pstmt.setString(1, "0001"); rs = pstmt.executeQuery(); rs.next(); blob = rs.getBlob(1); // prepare SQL query for inserting a new row using setBlob() String query = "insert into blob_table(id, blob_column) values(?, ?)"; // begin transaction conn.setAutoCommit(false); pstmt = conn.prepareStatement(query); pstmt.setString(1, "0002"); pstmt.setBlob(2, blob); int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); // end transaction conn.commit(); } finally { rs.close(); pstmt.close(); conn.close(); } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = null;//from w ww . j a v a 2 s . co 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(); }
From source file:DemoPreparedStatementSetCharacterStream.java
public static void main(String[] args) throws Exception { String fileName = "charDataFile.txt"; Reader fileReader = null;/*from w w w. jav a 2 s . c om*/ 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:DemoPreparedStatementSetBinaryStream.java
public static void main(String[] args) throws Exception { String smallFileName = "smallFileName.dat"; String largeFileName = "largeFileName.dat"; Connection conn = null;/*w w w . j a v a 2 s .c o 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: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:Main.java
public static void main(String[] args) throws Exception { String deptName = "oldName"; String newDeptName = "newName"; ResultSet rs = null;// w w w . j a v a 2 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(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 www. j a v a 2 s. co 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[] argv) throws Exception { String driverName = "com.jnetdirect.jsql.JSQLDriver"; Class.forName(driverName);/*www.j av a 2 s. co 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); String sql = "INSERT INTO mysql_all_table(" + "col_boolean," + "col_byte," + "col_short," + "col_int," + "col_long," + "col_float," + "col_double," + "col_bigdecimal," + "col_string," + "col_date," + "col_time," + "col_timestamp," + "col_asciistream," + "col_binarystream," + "col_blob) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = connection.prepareStatement(sql); pstmt.setBoolean(1, true); pstmt.setByte(2, (byte) 123); pstmt.setShort(3, (short) 123); pstmt.setInt(4, 123); pstmt.setLong(5, 123L); pstmt.setFloat(6, 1.23F); pstmt.setDouble(7, 1.23D); pstmt.setBigDecimal(8, new BigDecimal(1.23)); pstmt.setString(9, "a string"); pstmt.setDate(10, new java.sql.Date(System.currentTimeMillis())); pstmt.setTime(11, new Time(System.currentTimeMillis())); pstmt.setTimestamp(12, new Timestamp(System.currentTimeMillis())); File file = new File("infilename1"); FileInputStream is = new FileInputStream(file); pstmt.setAsciiStream(13, is, (int) file.length()); file = new File("infilename2"); is = new FileInputStream(file); pstmt.setBinaryStream(14, is, (int) file.length()); file = new File("infilename3"); is = new FileInputStream(file); pstmt.setBinaryStream(15, is, (int) file.length()); pstmt.executeUpdate(); }
From source file:TransactionPairs.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con = null;//from w ww .j a v a 2 s .c om Statement stmt; PreparedStatement updateSales; PreparedStatement updateTotal; String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; String updateStatement = "update COFFEES " + "set TOTAL = TOTAL + ? where COF_NAME = ?"; String query = "select COF_NAME, SALES, TOTAL from COFFEES"; try { Class.forName("myDriver.ClassName"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { con = DriverManager.getConnection(url, "myLogin", "myPassword"); updateSales = con.prepareStatement(updateString); updateTotal = con.prepareStatement(updateStatement); int[] salesForWeek = { 175, 150, 60, 155, 90 }; String[] coffees = { "Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf" }; int len = coffees.length; con.setAutoCommit(false); for (int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); updateTotal.setInt(1, salesForWeek[i]); updateTotal.setString(2, coffees[i]); updateTotal.executeUpdate(); con.commit(); } con.setAutoCommit(true); updateSales.close(); updateTotal.close(); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String c = rs.getString("COF_NAME"); int s = rs.getInt("SALES"); int t = rs.getInt("TOTAL"); System.out.println(c + " " + s + " " + t); } stmt.close(); con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); if (con != null) { try { System.err.print("Transaction is being "); System.err.println("rolled back"); con.rollback(); } catch (SQLException excep) { System.err.print("SQLException: "); System.err.println(excep.getMessage()); } } } }