1 / 15

Adding Power to Database Access with JSP

Adding Power to Database Access with JSP. JavaServer Pages By Xue Bai. Objectives. In this chapter, you will: Create more powerful query statements using the IN and BETWEEN Access database metadata from a JSP page Control transactions to ensure database integrity

abla
Download Presentation

Adding Power to Database Access with JSP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Adding Power to Database Access with JSP JavaServer Pages By Xue Bai Chapter 12

  2. Objectives In this chapter, you will: • Create more powerful query statements using the IN and BETWEEN • Access database metadata from a JSP page • Control transactions to ensure database integrity • Create and use beans to access a database Chapter 12

  3. The IN Operator • The IN operator is used to determine whether a field’s value falls within a specified range of values • The use of the IN operator takes the following form: SELECT column_list FROM table_name WHERE a_column_name IN (value1, value2, …) Chapter 12

  4. The IN Operator • The series values listed within the IN operator can be the result of another nested query SELECT * FROM purchase WHERE custnum IN ( SELECT custid FROM customer WHERE custname = ‘Weiei Co.’) Chapter 12

  5. The BETWEEN Operator • The BETWEEN operator is used to determine whether a column’s value is between one value and another • The use of the BETWEEN operator takes the following form: SELECT column_list FROM table_name WHERE a_column_name BETWEEN value1 AND value2 Chapter 12

  6. Metadata • Metadata in a database is data about the database itself, for example, information about the tables, the columns in a table, the column data type, and so on • JDBC allows you to access information about the whole database as well as information about a table within a database Chapter 12

  7. Database Metadata • Information about the database • Use connection object to get a reference to DatabaseMetaData object, which provides methods to get information about the database DatabaseMetaData dbmd = conn.getMetaData(); Chapter 12

  8. Database Metadata Chapter 12

  9. Get Information About Table • Use ResultSetMetaData object to get table information: ResultSet rst = stm.executeQuery("select * from customer"); ResultSetMetaData rsmd = rst.getMetaData(); • ResultSetMetaData object methods: • getColumnCount(), which returns the number of columns in the ResultSet object • getColumnName(int column), which returns the designated column's name, and takes a parameter which is the column number • getColumnDisplaySize(int column), which indicates the designated column's normal maximum width in characters Chapter 12

  10. Figure 12-3: Column Data Chapter 12

  11. Transactions • A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements are executed • Within a transaction, you can actually control when each statement is executed, and when all actions take effect, or even abort the whole transaction (leaving the database unchanged) Chapter 12

  12. Auto-Commit Mode • Default mode • To use transaction control, the auto-commit mode must be set to false: conn.setAutoCommit(false); Chapter 12

  13. Committing a Transaction • If auto-commit is set to false, all transactions are not complete until the SQL statements are either committed or aborted • When a statement is committed, any change made by the SQL statement is reflected in the database; when a statement is aborted, the database remains unchanged • To commit a SQL statement, use the following syntax: conn.commit(); Chapter 12

  14. Abort a Transaction conn.rollBack() • Calling this method aborts a transaction and leaves the database unchanged within the current transaction • Therefore, you can use the auto-commit mode combined with the commit and rollback methods to control transactions Chapter 12

  15. Beans and Databases • You can create various beans to facilitate database processing: • A connection bean to connect to a database • A database meta data bean to get database meta information • A table information bean that helps to get table information • A bean that helps to execute SQL statements and displays results Chapter 12

More Related