JDBC Code public static void JDBCexample(String dbid,String userid,String passwd) try (Connection conn DriverManager.getConnection( "jdbc:oracle:thin:@db.yale.edu:2000:univdb",userid,passwd); Statement stmt conn.createStatement(); ..Do Actual Work ... catch(SQLException sqle){ System.out.println("SQLException "sqle); NOTE:Above syntax works with Java 7,and JDBC 4 onwards. Resources opened in“try(..)”syntax(“try with resources")are automatically closed at the end of the try block Database System Concepts-7th Edition 5.7 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 5.7 ©Silberschatz, Korth and Sudarshan th Edition JDBC Code public static void JDBCexample(String dbid, String userid, String passwd) { try (Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd); Statement stmt = conn.createStatement(); ) { … Do Actual Work …. } catch (SQLException sqle) { System.out.println("SQLException : " + sqle); } } NOTE: Above syntax works with Java 7, and JDBC 4 onwards. Resources opened in “try (….)” syntax (“try with resources”) are automatically closed at the end of the try block
JDBC Code for Older Versions of Java/JDBC public static void JDBCexample(String dbid,String userid,String passwd) { try Class.forName ("oracle.jdbc.driver.OracleDriver"); Connection conn DriverManager.getConnection( "jdbc:oracle:thin:@db.yale.edu:2000:univdb",userid,passwd); Statement stmt conn.createStatement(); ..Do Actual Work.... stmt.close(); conn.close(); catch(SQLException sqle){ System.out.println("SQLException "sqle); NOTE:Class.forName is not required from JDBC 4 onwards.The try with resources syntax in prev slide is preferred for Java 7 onwards. Database System Concepts-7th Edition 5.8 Silberschatz,Korth and Sudarshan
Database System Concepts - 7 5.8 ©Silberschatz, Korth and Sudarshan th Edition JDBC Code for Older Versions of Java/JDBC public static void JDBCexample(String dbid, String userid, String passwd) { try { Class.forName ("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd); Statement stmt = conn.createStatement(); … Do Actual Work …. stmt.close(); conn.close(); } catch (SQLException sqle) { System.out.println("SQLException : " + sqle); } } NOTE: Class.forName is not required from JDBC 4 onwards. The try with resources syntax in prev slide is preferred for Java 7 onwards
JDBC Code(Cont.) Update to database try stmt.executeUpdate( "insert into instructor values('77987','Kim','Physics',98000)"); catch(SQLException sqle) System.out.println("Could not insert tuple."+sqle); } Execute query and fetch and print results ResultSet rset =stmt.executeQuery( "select dept_name,avg (salary) from instructor group by dept_name"); while(rset.next()){ System.out.println(rset.getString("dept_name")+""+ rset.getFloat(2)); } Database System Concepts-7th Edition 5.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 5.9 ©Silberschatz, Korth and Sudarshan th Edition JDBC Code (Cont.) ▪ Update to database try { stmt.executeUpdate( "insert into instructor values('77987', 'Kim', 'Physics', 98000)"); } catch (SQLException sqle) { System.out.println("Could not insert tuple. " + sqle); } ▪ Execute query and fetch and print results ResultSet rset = stmt.executeQuery( "select dept_name, avg (salary) from instructor group by dept_name"); while (rset.next()) { System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2)); }
JDBC SUBSECTIONS Connecting to the Database Shipping SQL Statements to the Database System Exceptions and Resource Management Retrieving the Result of a Query Prepared Statements ■ Callable Statements Metadata Features ■ Other Features Database Access from Python Database System Concepts-7th Edition 5.10 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 5.10 ©Silberschatz, Korth and Sudarshan th Edition JDBC SUBSECTIONS ▪ Connecting to the Database ▪ Shipping SQL Statements to the Database System ▪ Exceptions and Resource Management ▪ Retrieving the Result of a Query ▪ Prepared Statements ▪ Callable Statements ▪ Metadata Features ▪ Other Features ▪ Database Access from Python
JDBC Code Details ■Getting result fields: 9 rs.getString("dept_name")and rs.getString(1)equivalent if dept_name is the first argument of select result. Dealing with Null values inta rs.getint("a"); if(rs.wasNull())Systems.out.println("Got null value"); Database System Concepts-7th Edition 5.11 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 5.11 ©Silberschatz, Korth and Sudarshan th Edition JDBC Code Details ▪ Getting result fields: • rs.getString(“dept_name”) and rs.getString(1) equivalent if dept_name is the first argument of select result. ▪ Dealing with Null values int a = rs.getInt(“ a ”); if (rs.wasNull()) Systems.out.println(“Got null value”);