《数据库系统概念 Database System Concepts》原书教学资源（第六版，PPT课件讲稿，英文版）Chapter 5 Advanced SQL

Outline Accessing SQL From a Programming Language Functions and Procedural Constructs Triggers Recursive Queries Advanced Aggregation Features OLAP Database System Concepts-6th Edition 5.2 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 5.2 ©Silberschatz, Korth and Sudarshan th Edition Outline Accessing SQL From a Programming Language Functions and Procedural Constructs Triggers Recursive Queries Advanced Aggregation Features OLAP

Accessing SQL From a Programming Language Database System Concepts-6th Edition 5.3 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 5.3 ©Silberschatz, Korth and Sudarshan th Edition Accessing SQL From a Programming Language

Accessing SQL From a Programming Language API (application-program interface)for a program to interact with a database server Application makes calls to Connect with the database server Send SQL commands to the database server Fetch tuples of result one-by-one into program variables Various tools: ODBC (Open Database Connectivity)works with C,C++,C#, and Visual Basic.Other APl's such as ADO.NET sit on top of ODBC JDBC (Java Database Connectivity)works with Java Embedded SQL Database System Concepts-6th Edition 5.4 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 5.4 ©Silberschatz, Korth and Sudarshan th Edition Accessing SQL From a Programming Language API (application-program interface) for a program to interact with a database server Application makes calls to Connect with the database server Send SQL commands to the database server Fetch tuples of result one-by-one into program variables Various tools: ODBC (Open Database Connectivity) works with C, C++, C#, and Visual Basic. Other API’s such as ADO.NET sit on top of ODBC JDBC (Java Database Connectivity) works with Java Embedded SQL

ODBC Open DataBase Connectivity (ODBC)standard standard for application program to communicate with a database server. application program interface (API)to open a connection with a database, send queries and updates, get back results. Applications such as GUl,spreadsheets,etc.can use ODBC Database System Concepts-6th Edition 5.5 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 5.5 ©Silberschatz, Korth and Sudarshan th Edition ODBC Open DataBase Connectivity (ODBC) standard standard for application program to communicate with a database server. application program interface (API) to  open a connection with a database,  send queries and updates,  get back results. Applications such as GUI, spreadsheets, etc. can use ODBC

JDBC JDBC is a Java API for communicating with database systems supporting SQL. JDBC supports a variety of features for querying and updating data, and for retrieving query results. JDBC also supports metadata retrieval,such as querying about relations present in the database and the names and types of relation attributes. Model for communicating with the database: Open a connection Create a“statement”object Execute queries using the Statement object to send queries and fetch results Exception mechanism to handle errors Database System Concepts-6th Edition 5.6 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 5.6 ©Silberschatz, Korth and Sudarshan th Edition JDBC JDBC is a Java API for communicating with database systems supporting SQL. JDBC supports a variety of features for querying and updating data, and for retrieving query results. JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes. Model for communicating with the database: Open a connection Create a “statement” object Execute queries using the Statement object to send queries and fetch results Exception mechanism to handle errors

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”?