山东大学数据库系统英语课件05高级SQL

Database System Concepts, 6th Ed.Silberschatz, Korth and SudarshanSee www.db- for conditions on re-use Chapter 5: Advanced SQLSilberschatz, Korth and Sudarshan5.2Database System Concepts - 6th EditionChapter 5: Advanced SQLnAccessing SQL From a Programming Language lDynamic SQL4JDBC and ODBClEmbedded SQLnFunctions and Procedural ConstructsnTriggersn Advanced Aggregation FeaturesnOLAPSilberschatz, Korth and Sudarshan5.3Database System Concepts - 6th EditionJDBC and ODBCnAPI (application-program interface) for a program to interact with a database servernApplication makes calls tolConnect with the database serverlSend SQL commands to the database serverlFetch tuples of result one-by-one into program variablesnODBC (Open Database Connectivity) works with C, C+, C#, and Visual BasiclOther APIs such as ADO.NET sit on top of ODBCnJDBC (Java Database Connectivity) works with JavaSilberschatz, Korth and Sudarshan5.4Database System Concepts - 6th EditionJDBCnJDBC is a Java API for communicating with database systems supporting SQL.nJDBC supports a variety of features for querying and updating data, and for retrieving query results.nJDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes.nModel for communicating with the database:lOpen a connectionlCreate a “statement” objectlExecute queries using the Statement object to send queries and fetch resultslException mechanism to handle errorsSilberschatz, Korth and Sudarshan5.5Database System Concepts - 6th EditionJDBC Codepublic 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); Silberschatz, Korth and Sudarshan5.6Database System Concepts - 6th EditionJDBC Code (Cont.)nUpdate to databasetry stmt.executeUpdate( insert into instructor values(77987, Kim, Physics, 98000); catch (SQLException sqle) System.out.println(Could not insert tuple. + sqle);nExecute 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);Silberschatz, Korth and Sudarshan5.7Database System Concepts - 6th EditionJDBC Code Details nGetting result fields:lrs.getString(“dept_name”) and rs.getString(1) equivalent if dept_name is the first argument of select result.nDealing with Null valueslint a = rs.getInt(“a”); if (rs.wasNull() Systems.out.println(“Got null value”);Silberschatz, Korth and Sudarshan5.8Database System Concepts - 6th EditionPrepared StatementnPreparedStatement 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();nFor queries, use pStmt.executeQuery(), which returns a ResultSet nWARNING: always use prepared statements when taking an input from the user and adding it to a querylNEVER create a query by concatenating strings which you get as inputslinsert into instructor values( + ID + , + name + , + + dept name + , balance + )“lWhat if name is “DSouza”?Silberschatz, Korth and Sudarshan5.9Database System Concepts - 6th EditionSQL InjectionnSuppose query is constructed usinglselect * from instructor where name = + name + nSuppose the user, instead of entering a name, enters:lX or Y = Ynthen the resulting statement becomes:lselect * from instructor where name = + X or Y = Y + lwhich is:4select * from instructor where name = X or Y = YlUser could have even used4X; update instructor set salary = salary + 10000; -nPrepared statement internally uses:select * from instructor where name = X or Y = YlAlways use prepared statements, with user inputs as parametersSilberschatz, Korth and Sudarshan5.10Database System Concepts - 6th EditionMetadata FeaturesnResultSet metadatanE.g., after executing query to get a ResultSet rs:lResultSetMetaData rsmd = rs.getMetaData(); for(int i = 1; i = rsmd.getColumnCount(); i+) System.out.println(rsmd.getColumnName(i); System.out.println(rsmd.getColumnTypeName(i); nHow is this useful?Silberschatz, Korth and Sudarshan5.11Database System Concepts - 6th EditionMetadata (Cont)nDatabase metadatanDatabaseMetaData dbmd = conn.getMetaData();ResultSet rs = dbmd.getColumns(null, univdb, department, %);/ Arguments to getColumns: Catalog, Schema-pattern, Table-pattern,/ and Column-Pattern/ Returns: One row for each column; row has a number of attributes/ such as COLUMN_NAME, TYPE_NAMEwhile( rs.next() System.out.println(rs.getString(COLUMN_NAME), rs.getString(TYPE_NAME); nAnd where is this useful?Silberschatz, Korth and Suda