List of usage examples for java.sql PreparedStatement setRef
void setRef(int parameterIndex, Ref x) throws SQLException;
REF(<structured-type>) value. From source file:InsertStores.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/*from w w w .ja va2 s . c om*/ 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 { String deptName = "oldName"; String newDeptName = "newName"; ResultSet rs = null;/* www.j a va 2s.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(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;// ww w .j av a 2 s . c om 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:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Set the given value as a parameter to the statement. *//* w ww.ja v a2 s. c o m*/ public void setRef(PreparedStatement stmnt, int idx, Ref val, Column col) throws SQLException { stmnt.setRef(idx, val); }
From source file:org.orbisgis.corejdbc.internal.ReadRowSetImpl.java
private PreparedStatement createBatchQuery(Connection connection, Long firstPk, boolean cacheData, int queryOffset, int limit, boolean queryPk) throws SQLException { StringBuilder command = new StringBuilder(); if (cachedColumnNames == null) { cacheColumnNames();/*from w w w .j a v a2 s . c o m*/ } command.append("SELECT "); if (queryPk) { command.append(pk_name); if (cacheData) { command.append(","); } } if (cacheData) { command.append(select_fields); } command.append(" FROM "); command.append(getTable()); if (firstPk != null || !select_where.isEmpty()) { command.append(" WHERE "); if (!select_where.isEmpty()) { command.append(select_where); } if (firstPk != null) { if (!select_where.isEmpty()) { command.append(" AND "); } command.append(pk_name); command.append(" >= ?"); } } if (isH2 || !pk_name.equals(MetaData.POSTGRE_ROW_IDENTIFIER)) { command.append(" ORDER BY "); command.append(pk_name); } command.append(" LIMIT "); command.append(limit); if (queryOffset > 0) { command.append(" OFFSET "); command.append(queryOffset); } PreparedStatement st = connection.prepareStatement(command.toString()); for (Map.Entry<Integer, Object> entry : parameters.entrySet()) { st.setObject(entry.getKey(), entry.getValue()); } if (firstPk != null) { if (isH2 || !pk_name.equals(MetaData.POSTGRE_ROW_IDENTIFIER)) { st.setLong(parameters.size() + 1, firstPk); } else { Ref pkRef = new Tid(firstPk); st.setRef(parameters.size() + 1, pkRef); } } return st; }