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. Database System Concepts-6th Edition 5.17 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 5.17 ©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("{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 parameterlndex,InputStream inputStream). Database System Concepts-6th Edition 5.18 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 5.18 ©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-6th Edition 5.19 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 5.19 ©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 deptInfolter 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-6th Edition 5.20 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 5.20 ©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();
Embedded SQL The SQL standard defines embeddings of SQL in a variety of programming languages such as C,C++,Java,Fortran,and PL/1, A language to which SQL queries are embedded is referred to as a host language,and the SQL structures permitted in the host language comprise embedded SQL. The basic form of these languages follows that of the System R embedding of SQL into PL/1. EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > Note:this varies by language: In some languages,like COBOL,the semicolon is replaced with END-EXEC In Java embedding uses SQL{....} Database System Concepts-6th Edition 5.21 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 5.21 ©Silberschatz, Korth and Sudarshan th Edition Embedded SQL The SQL standard defines embeddings of SQL in a variety of programming languages such as C, C++, Java, Fortran, and PL/1, A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL. The basic form of these languages follows that of the System R embedding of SQL into PL/1. EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement >; Note: this varies by language: In some languages, like COBOL, the semicolon is replaced with END-EXEC In Java embedding uses # SQL { …. };