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.... 3 catch (SQLException sqle){ System.out.printIn("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-6th Edition 5.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 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:Classs.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-6th Edition 5.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 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: Classs.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.printin(rset.getString("dept_name")+"" rset.getFloat(2)); } Database System Concepts-6th Edition 5.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 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 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.printIn("Got null value"); Database System Concepts-6th Edition 5.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 5.10 ©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”);
Prepared Statement PreparedStatement pStmt conn.prepareStatement( "insert into instructor values(?,??,?)") pStmt.setString(1,"88877"); pStmt.setString(2,"Perry"); pStmt.setString(3,"Finance"); pStmt.setInt(4,125000); pStmt.executeUpdate(); pStmt.setString(1,"88878"); pStmt.executeUpdate(); WARNING:always use prepared statements when taking an input from the user and adding it to a query NEVER create a query by concatenating strings "insert into instructor values('"+ID +"'"name+"',"+" dept name +"'"balance+")" Vhat if name is“D'Souza”? Database System Concepts-6th Edition 5.11 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 5.11 ©Silberschatz, Korth and Sudarshan th Edition Prepared Statement PreparedStatement pStmt = conn.prepareStatement( "insert into instructor values(?,?,?,?)"); pStmt.setString(1, "88877"); pStmt.setString(2, "Perry"); pStmt.setString(3, "Finance"); pStmt.setInt(4, 125000); pStmt.executeUpdate(); pStmt.setString(1, "88878"); pStmt.executeUpdate(); WARNING: always use prepared statements when taking an input from the user and adding it to a query NEVER create a query by concatenating strings "insert into instructor values(’ " + ID + " ’, ’ " + name + " ’, " + " ’ + dept name + " ’, " ’ balance + ")“ What if name is “D’Souza”?