Finding Primary Keys DatabaseMetaData dmd connection.getMetaData(); /Arguments below are:Catalog,Schema,and Table /The value for Catalog/Schema indicates current catalog/schema /The value null indicates all catalogs/schemas ResultSet rs=dmd.getPrimaryKeys(,tableName); while(rs.next()){ /KEY SEQ indicates the position of the attribute in /the primary key,which is required if a primary key has multiple /attributes System.out.println(rs.getString("KEY_SEQ"), rs.getString("COLUMN_NAME"); Database System Concepts-7th Edition 5.17 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 5.17 ©Silberschatz, Korth and Sudarshan th Edition Finding Primary Keys ▪ DatabaseMetaData dmd = connection.getMetaData(); // Arguments below are: Catalog, Schema, and Table // The value “” for Catalog/Schema indicates current catalog/schema // The value null indicates all catalogs/schemas ResultSet rs = dmd.getPrimaryKeys(“”, “”, tableName); while(rs.next()){ // KEY_SEQ indicates the position of the attribute in // the primary key, which is required if a primary key has multiple // attributes System.out.println(rs.getString(“KEY_SEQ”), rs.getString("COLUMN_NAME"); }
Transaction Control in JDBC By default,each SQL statement is treated as a separate transaction that is committed automatically bad idea for transactions with multiple updates Can turn off automatic commit on a connection .conn.setAutoCommit(false); Transactions must then be committed or rolled back explicitly ·conn.commit(0; or ·conn.rollback(); conn.setAutoCommit(true)turns on automatic commit. Database System Concepts-7th Edition 5.18 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 5.18 ©Silberschatz, Korth and Sudarshan th Edition Transaction Control in JDBC ▪ By default, each SQL statement is treated as a separate transaction that is committed automatically • bad idea for transactions with multiple updates ▪ Can turn off automatic commit on a connection • conn.setAutoCommit(false); ▪ Transactions must then be committed or rolled back explicitly • conn.commit(); or • conn.rollback(); ▪ conn.setAutoCommit(true) turns on automatic commit
Other JDBC Features Calling functions and procedures CallableStatement cStmt1 conn.prepareCall("{?call some function(?))"); 。 CallableStatement cStmt2 conn.prepareCall("fcall some procedure(?,?)}"); Handling large object types getBlob()and getClob()that are similar to the getString()method,but return objects of type Blob and Clob,respectively get data from these objects by getBytes() 。 associate an open stream with Java Blob or Clob object to update large objects blob.setBlob(int parameterlndex,InputStream inputStream). Database System Concepts-7th Edition 5.19 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 5.19 ©Silberschatz, Korth and Sudarshan th Edition Other JDBC Features ▪ Calling functions and procedures • CallableStatement cStmt1 = conn.prepareCall("{? = call some function(?)}"); • CallableStatement cStmt2 = conn.prepareCall("{call some procedure(?,?)}"); ▪ Handling large object types • getBlob() and getClob() that are similar to the getString() method, but return objects of type Blob and Clob, respectively • get data from these objects by getBytes() • associate an open stream with Java Blob or Clob object to update large objects ▪ blob.setBlob(int parameterIndex, InputStream inputStream)
JDBC Resources ■JDBC Basics Tutorial https://docs.oracle.com/javase/tutorial/jdbc/index.html Database System Concepts-7th Edition 5.20 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 5.20 ©Silberschatz, Korth and Sudarshan th Edition JDBC Resources ▪ JDBC Basics Tutorial • https://docs.oracle.com/javase/tutorial/jdbc/index.html
SQLJ JDBC is overly dynamic,errors cannot be caught by compiler SQLJ:embedded SQL in Java #sql iterator deptlnfolter String dept name,int avgSal); deptInfolter iter null; #sql iter =select dept_name,avg(salary)from instructor group by dept name } while (iter.next()){ String deptName iter.dept_name(); int avgSal iter.avgSal(); System.out.println(deptName +""avgSal); iter.close(); Database System Concepts-7th Edition 5.21 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 5.21 ©Silberschatz, Korth and Sudarshan th Edition SQLJ ▪ JDBC is overly dynamic, errors cannot be caught by compiler ▪ SQLJ: embedded SQL in Java • #sql iterator deptInfoIter ( String dept name, int avgSal); deptInfoIter iter = null; #sql iter = { select dept_name, avg(salary) from instructor group by dept name }; while (iter.next()) { String deptName = iter.dept_name(); int avgSal = iter.avgSal(); System.out.println(deptName + " " + avgSal); } iter.close();