List of usage examples for java.sql PreparedStatement close
void close() throws SQLException;
Statement
object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. From source file:SelectRecordsUsingPreparedStatement.java
public static void main(String[] args) { ResultSet rs = null;/*from ww w .ja v a2 s. com*/ Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); String query = "select deptno, deptname, deptloc from dept where deptno > ?"; pstmt = conn.prepareStatement(query); // create a statement pstmt.setInt(1, 1001); // set input parameter rs = pstmt.executeQuery(); // extract data from the ResultSet while (rs.next()) { int dbDeptNumber = rs.getInt(1); String dbDeptName = rs.getString(2); String dbDeptLocation = rs.getString(3); System.out.println(dbDeptNumber + "\t" + dbDeptName + "\t" + dbDeptLocation); } } catch (Exception e) { e.printStackTrace(); } finally { try { rs.close(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
From source file:DemoPreparedStatementSetClob.java
public static void main(String[] args) throws Exception { String id = "0001"; String newID = "0002"; ResultSet rs = null;/* w ww . ja va 2s . c o m*/ Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); // begin transaction conn.setAutoCommit(false); String query1 = "select clob_column from clob_table where id = ?"; pstmt = conn.prepareStatement(query1); pstmt.setString(1, id); rs = pstmt.executeQuery(); rs.next(); java.sql.Clob clob = (java.sql.Clob) rs.getObject(1); String query = "insert into clob_table(id, clob_column) values(?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, newID); pstmt.setClob(2, clob); int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); conn.commit(); } finally { rs.close(); pstmt.close(); conn.close(); } }
From source file:Batch.java
static public void main(String[] args) { Connection conn = null;/*ww w .j a va 2 s. c om*/ try { ArrayList breakable = new ArrayList(); PreparedStatement stmt; Iterator users; ResultSet rs; Class.forName(args[0]).newInstance(); conn = DriverManager.getConnection(args[1], args[2], args[3]); stmt = conn.prepareStatement("SELECT user_id, password " + "FROM user"); rs = stmt.executeQuery(); while (rs.next()) { String uid = rs.getString(1); String pw = rs.getString(2); // Assume PasswordCracker is some class that provides // a single static method called crack() that attempts // to run password cracking routines on the password // if( PasswordCracker.crack(uid, pw) ) { // breakable.add(uid); // } } stmt.close(); if (breakable.size() < 1) { return; } stmt = conn.prepareStatement("UPDATE user " + "SET bad_password = 'Y' " + "WHERE uid = ?"); users = breakable.iterator(); while (users.hasNext()) { String uid = (String) users.next(); stmt.setString(1, uid); stmt.addBatch(); } stmt.executeBatch(); } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (Exception e) { } } } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = null;//from w ww . j a v a 2s . co m PreparedStatement pstmt = null; try { conn = getConnection(); String query = "insert into message values(?, ?, ?)"; pstmt = conn.prepareStatement(query); // create a statement pstmt.setInt(1, 5); // set input parameter 1 pstmt.setString(2, "head5"); // set input parameter 2 pstmt.setString(3, "data5"); // set input parameter 3 pstmt.executeUpdate(); // execute insert statement pstmt = conn.prepareStatement("insert into msgtag values(?, ?, ?)"); // create a statement pstmt.setInt(1, 55); // set input parameter 1 pstmt.setString(2, "tag5"); // set input parameter 2 pstmt.setInt(3, 5); // set input parameter 3 pstmt.executeUpdate(); // execute insert statement } catch (Exception e) { e.printStackTrace(); } finally { pstmt.close(); conn.close(); } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getHSQLConnection(); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar);"); st.executeUpdate("create view surveyView as (select * from survey);"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); String query = "select * from survey where id > ? and name = ?"; PreparedStatement pstmt = conn.prepareStatement(query); ParameterMetaData paramMetaData = pstmt.getParameterMetaData(); if (paramMetaData == null) { System.out.println("db vendor does NOT support ParameterMetaData"); } else {// www . j a v a 2 s . com System.out.println("db vendor supports ParameterMetaData"); // find out the number of dynamic parameters int paramCount = paramMetaData.getParameterCount(); System.out.println("paramCount=" + paramCount); System.out.println("-------------------"); for (int param = 1; param <= paramCount; param++) { System.out.println("param number=" + param); String paramTypeName = paramMetaData.getParameterTypeName(param); System.out.println("param SQL type name=" + paramTypeName); } } pstmt.close(); conn.close(); }
From source file:DemoPreparedStatementSetClob.java
public static void main(String[] args) throws Exception { String id = "0001"; String newID = "0002"; ResultSet rs = null;//w w w.j ava 2 s.c o m Connection conn = null; PreparedStatement pstmt = null; try { conn = getConnection(); // begin transaction conn.setAutoCommit(false); String query1 = "select clob_column from clob_table where id = ?"; pstmt = conn.prepareStatement(query1); pstmt.setString(1, id); rs = pstmt.executeQuery(); rs.next(); java.sql.Clob clob = (java.sql.Clob) rs.getObject(1); String query = "insert into clob_table(id, clob_column) values(?, ?)"; pstmt = conn.prepareStatement(query); pstmt.setString(1, newID); pstmt.setClob(2, clob); // execute query, and return number of rows created int rowCount = pstmt.executeUpdate(); System.out.println("rowCount=" + rowCount); // end transaction conn.commit(); } finally { rs.close(); pstmt.close(); conn.close(); } }
From source file:InsertDateToOracle.java
public static void main(String args[]) throws Exception { String INSERT_RECORD = "insert into TestDates(id, date_column, " + "time_column, timestamp_column) values(?, ?, ?, ?)"; Connection conn = null;/* w w w.j a va 2 s.co m*/ PreparedStatement pstmt = null; try { conn = getConnection(); pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "001"); java.util.Date date = new java.util.Date(); long t = date.getTime(); java.sql.Date sqlDate = new java.sql.Date(t); java.sql.Time sqlTime = new java.sql.Time(t); java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(t); System.out.println("sqlDate=" + sqlDate); System.out.println("sqlTime=" + sqlTime); System.out.println("sqlTimestamp=" + sqlTimestamp); pstmt.setDate(2, sqlDate); pstmt.setTime(3, sqlTime); pstmt.setTimestamp(4, sqlTimestamp); pstmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); System.out.println("Failed to insert the record."); } finally { pstmt.close(); conn.close(); } }
From source file:MainClass.java
public static void main(String[] args) { Connection connection = null; PreparedStatement statement = null; try {/*from w ww. j a va2 s .c o m*/ Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://localhost/database"; connection = DriverManager.getConnection(url, "username", "password"); String sql = "UPDATE employees SET email = ? WHERE employee_id = ?"; statement = connection.prepareStatement(sql); statement.setString(1, "a@a.com"); statement.setLong(2, 1); statement.addBatch(); statement.setString(1, "b@b.com"); statement.setLong(2, 2); statement.addBatch(); statement.setString(1, "c@c.com"); statement.setLong(2, 3); statement.addBatch(); statement.executeBatch(); } catch (Exception e) { e.printStackTrace(); } finally { if (statement != null) { try { statement.close(); } catch (SQLException e) { } // nothing we can do } if (connection != null) { try { connection.close(); } catch (SQLException e) { } // nothing we can do } } }
From source file:InsertStores.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*from w ww .ja va 2s . c o m*/ Statement stmt; 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"); stmt = con.createStatement(); con.setAutoCommit(false); String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, " + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))"; stmt.addBatch(insertStore1); String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, " + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))"; stmt.addBatch(insertStore2); String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, " + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))"; stmt.addBatch(insertStore3); String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, " + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))"; stmt.addBatch(insertStore4); String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, " + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))"; stmt.addBatch(insertStore5); int[] updateCounts = stmt.executeBatch(); ResultSet rs = stmt.executeQuery("SELECT * FROM STORES"); System.out.println("Table STORES after insertion:"); System.out.println("STORE_NO LOCATION COF_TYPE MGR"); while (rs.next()) { int storeNo = rs.getInt("STORE_NO"); Struct location = (Struct) rs.getObject("LOCATION"); Object[] locAttrs = location.getAttributes(); Array coffeeTypes = rs.getArray("COF_TYPE"); String[] cofTypes = (String[]) coffeeTypes.getArray(); Ref managerRef = rs.getRef("MGR"); PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?"); pstmt.setRef(1, managerRef); ResultSet rs2 = pstmt.executeQuery(); rs2.next(); Struct manager = (Struct) rs2.getObject("MANAGER"); Object[] manAttrs = manager.getAttributes(); System.out.print(storeNo + " "); System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + " " + locAttrs[4] + " "); for (int i = 0; i < cofTypes.length; i++) System.out.print(cofTypes[i] + " "); System.out.println(manAttrs[1] + ", " + manAttrs[2]); rs2.close(); pstmt.close(); } rs.close(); stmt.close(); con.close(); } catch (BatchUpdateException b) { System.err.println("-----BatchUpdateException-----"); System.err.println("SQLState: " + b.getSQLState()); System.err.println("Message: " + b.getMessage()); System.err.println("Vendor: " + b.getErrorCode()); System.err.print("Update counts: "); int[] updateCounts = b.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { System.err.print(updateCounts[i] + " "); } System.err.println(""); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Message: " + ex.getMessage()); System.err.println("Vendor: " + ex.getErrorCode()); } }
From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = null;//w w w.j a v a 2 s . 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(); }