《山东大学数据库系统英语课件05高级SQL》由会员分享，可在线阅读，更多相关《山东大学数据库系统英语课件05高级SQL（84页珍藏版）》请在金锄头文库上搜索。
1、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 ODBClEmbedde
2、d 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 t
3、he 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, K
4、orth 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 abo
5、ut 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,
6、 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
7、); 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 i
8、nto 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.
9、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 Nu
10、ll 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); pSt
11、mt.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
12、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 cons
13、tructed 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 u
14、sed4X; 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 FeaturesnResul
15、tSet 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.11D
16、atabase 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
