Databases with JSP

1. Databases with JSP JavaServer Pages By Xue Bai Chapter 11

2. Objectives In this chapter, you will: • Learn basic database and DBMS concepts • Use SQL to manipulate databases via your JSP Web applications • Use the JDBC or JDBC-ODBC bridge to connect to databases from JSP pages • Create tables and insert data into tables via JSP pages • Get data from tables via JSP pages Chapter 11

3. Database Concepts • A database is a collection of data items related to some enterprise • For example, a database might contain checking account information in a bank, product items information in an on-line store, students’ records in a college, and so on • Relational databases store information in simple structures called tables • Most commercial databases today are relational databases Chapter 11

4. Database Concepts • The data in a table is organized into rows and columns • The rows in the table are called records • A record contains information about a given entity • An entity is a distinct object, for example, a person's name, a product, or an event, to be presented in the table • The columns in the table contain fields • A field contains a single, specific piece of information within a record. So, a record is a group of related fields Chapter 11

5. Item Number Item Name Unit Price Inventory 100 Camera $267.99 13 101 Washer $489.56 8 102 TV $189.99 29 Table 11-1: Sample Database Table Chapter 11

6. Creating Tables • Table name • Fields and their data types • The table name identifies the table • A database can consist of many tables • You use the name of a table to reference the table and to manipulate data in that table Chapter 11

7. manufacturerID productID productName name model address Price phone manufacturerID 1 Weiwei Co. Edward Rd 123456 100 Washer D1 356.99 1 200 2 TV XYZ Co. S2 Central 255.68 654321 2 Using Common Fields to Link Two Tables Chapter 11

8. Primary Key • Unique identifier • Used to uniquely identify a record in a table Chapter 11

9. An Introduction to SQL • Computer language to process relational database • Data definition language (DDL): • Create table, alter table, and so on • Used to define a table’s column, add or delete columns, and delete unneeded tables • Data manipulation language(DML): • Insert into, update, delete, and select • Used to insert, update, delete, and retrieve data in a table Chapter 11

10. Data type Sample data Description CHAR(length) Newcastle Dr. For nonnumeric data. Fixed length VARCHAR(length) Newcastle Dr. For nonnumeric data. Variable length (listed length indicates maximum) INTEGER 123456 For whole number data between –231 and +231-1 SMALLINT 31 For whole number data between –215 and +215-1 FLOAT 2.6E+10 Very large or vary small numbers DATE 11/16/2001 For date. Implementations vary in different databases Data Types Chapter 11

11. Creating and Dropping Tables CREATE TABLE tableName (field1 dataType, field2 dataType, …) CREATE TABLE tableName (field1 dataType PRIMARY KEY, field2 dataType, …) DROP TABLE tableName Chapter 11

12. Example: Student Table CREATE TABLE student ( id integer PRIMARY KEY, firstName varchar(15), lastName varchar(15)); Chapter 11

13. Inserting Data Into a Table INSERT INTO TABLE tableName VALUES (value1, value2, …) or INSERT INTO TABLE tableName (field1, field2, …) VALUES ( value1, value2, …) Chapter 11

14. Updating Table Data UPDATE tableName SET field1=value1, fiedl2=value2, … WHERE conditions • The WHERE clause gives the condition for selecting which rows (records) are to be updated in the table identified as tableName • The SET keyword is followed by the field list to be updated • If the WHERE clause is omitted, all rows in the table are updated Chapter 11

15. Updating Table Data • Conditions in a WHERE clause are similar to conditional statements in JSP • Conditions can be constructed with comparison operators and logical operators • You can use the six comparison operators ( =, <> for not equal, <, >, <=, >=) as well as the three logical operators (AND, OR, and NOT) to create compound conditions or to negate a condition Chapter 11

16. Deleting Records from a Table • DELETE FROM tableName WHERE conditions • This deletes all rows that satisfy the WHERE clause in the statement • If there is no WHERE clause, then all rows in the table are deleted Chapter 11

17. Retrieving Data SELECT field1, field2, … FROM tableName WHERE conditions • The SELECT clause lists the fields retrieved in the query result, separated by commas • The FROM clause lists one or more table names to be used by the query • All fields listed in the SELECT or WHERE clauses must be found in one and only one of the tables listed in the FROM clause Chapter 11

18. Retrieving Data • The WHERE clause contains conditions for selecting rows from the tables listed in the FROM clause • The data retrieved are from the rows that satisfy the condition (and are therefore selected) • If the WHERE clause is omitted, all rows are selected Chapter 11

19. Wildcard Characters • Special symbols that represent any character or combination of characters • Make it easier to use inexact spelling in a query • The percent symbol % represents any collection of characters, including zero characters • The underscore _ represents any individual character Chapter 11

20. Sorting Retrieved Data SELECT field1, field2, … FROM tableName WHERE conditions ORDER BY sort_field1 DESC, sort_field2, … • Sort data retrieved in a particular order Chapter 11

21. Database Access from JSP • Via JDBC connection • Via JDBC to ODBC bridge • JDBC is a technology developed by Sun to allow to access virtually any database system from JSP pages • ODBC is a technology developed by Microsoft to allow generic access to database systems Chapter 11

22. Steps to Access Database in JSP • Load the JDBC driver • Define the connection URL • Establish the connection • Create the statement object • Execute a query or update • Process the results • Close the connection Chapter 11

23. Loading the JDBC Driver • The driver acts as the bridge between the JDBC classes (the classes used in JSP and JavaBeans to access database) and the database itself • The driver is a piece of software that knows how to talk to the DBMS • To load a driver, all you need to do is to load the appropriate class Class.forName(“fully qualified class name”); Chapter 11

24. Loading the JDBC Driver Example • Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); • Class.forName(“oracle.jdbc.driver.OracleDriver”); • Class.forName(“org.mysql.Driver”); • Class.forName(“org.gjt.mm.mysql.Driver”); Chapter 11

25. Defining the Connection URL • Location of the database server • URLs referring to databases use the jdbc: protocol followed by specific subprotocal and the name of the database • For some subprotocals, you may need to specify the database server host name and port number, user name and password, etc • The URLs are different in different databases • For jdbc:odbc bridge connection, the URL has the following form: jdbc.odbc.data_source_name Chapter 11

26. Establishing the Connection • Create a connection to the database server • To make the actual network connection, you need to pass the URL, the database username, and the password to the getConnection method of the DriverManager class, as follows: Connection conn = DriverManager.getConnection(connURL,username, password); Chapter 11

27. Creating the Statement Object • Used to send queries and commands to the database Statement stm = conn.createStatement(); Chapter 11

28. Executing a Query or Command • The statement object has two methods: the executeUpdate() method for table updating, creating, modifying, and so on • The executeQuery() method for retrieving data from a table • The executeUpdate() method returns an integer indicating how many rows were affected • The executeQuery() method returns a ResultSet object containing the selected rows from a table in the database Chapter 11

29. Example: Create a Table String query = “CREATE TABLE product “ + “ (productID char(5), name “ + “ varchar(15))”; stm.executeUpdate(query); Chapter 11

30. Processing ResultSets • Use getXXX() method, where XXX represents data type. For example: getString(“name”) • The parameter passed can be column index, which starts at 1 (not 0) • Use the next() method of a ResultSet to navigate to the next record Chapter 11

31. Closing the Connection Close connection: • Close ResultSet • Close Statement • Close Connection Chapter 11

32. Registering a Database as an ODBC Data Source • To use JDBC-ODBC bridge to connect to Microsoft Access database, you need to register the database as an ODBC data source in advance Chapter 11

33. ODBC Data Source Administrator Dialog Box Chapter 11

34. Create New Data Source Dialog Box Chapter 11

35. ODBC Microsoft Access Setup Dialog Box Chapter 11