Advanced Internet Programming (Java EE) - JDBC 31242/32549/ AJPCPEAdvanced Internet Programming INFO: What's New FAQ Software Machines Oracle Assignments MODULES: 00 Admin 01 Intro 02 Architecture 03 Servlets 04 JSP 05 JDBC 06 RMI 06 JNDI 07 EJB 08 XML 08 Web Services 09 Security 10 Transactions 11 Legacy 12 Review 99 Design LINKS: START page Faculty of IT UTS Module: JDBC Lab exercise - Familiarisation - Eclipse Web Tools version In this module, we will be writing Java programs that connect to a database server, issue queries, and update information. Before looking at the Java syntax, it is useful to take a moment to become familiar with the database server we will mostly be using. Some basic knowledge of databases is assumed, however no detailed technical knowledge is expected as the queries we will be performing are very basic. Level of Difficulty: 1 (easy) Estimated time: 15 minutes Pre-requisites: None Initial settings The faculty of Engineering and IT provides 2 main database servers for students. You will be given a userid, password, database name, server name and connection details by your tutor The following table are the default settings for the faculty servers Database Oracle Mysql Driver jar ojdbc6.jar mysql-connector-*.jar Driver class oracle.jdbc.OracleDriver com.mysql.jdbc.Driver URL format jdbc:oracle:thin:@server:port/database jdbc:mysql://server:port/database server oralab.it.uts.edu.au rerun.it.uts.edu.au port 1523 3306 database (SID for oracle) ell same as userid You can locate the drivers in your $WL_HOME/server/lib directory, which on the default faculty workstations is at /opt/oracle-middleware directory ie: /opt/oracle-middleware/wlserver/server/lib directory Setting up your Workshop project You will need to add the relevant JDBC jar file into your project build classpath. To do this, select your project (labs) and press Right Mouse Button -> Properties and select Java Build Path. (Alternative: Right Mouse Button -> Build Path -> Add external Archives ) Choose the Libraries tab and Select Add External Jars. Browse to the appropriate directory (see above) and select your jdbc jar file.& press [OK] to return to the project Creating a Database Connection Eclipse WTP provides a facility based on the Eclipse Data Tools Project which enables you to dynamically test your SQL against an existing database. We will create a connection to Oracle in this example. If you plan to use an alternative database such as mysql, please refer to the seperate tutorials on the different parameters to use. First, we need to select the Data Source Explorer view - this is normally a tab at the bottom of your workspace. If not, choose Window -> Show view -> Data Source explorer This will normally show all your database connections. Select the Database Connections folder in the Data Source Explorer and press the Right Mouse Button --> New You should get a New Connection Profile wizard. Choose the appropriate driver for your database - for example, Oracle Database Connection or Mysql & press [Next] Now enter the Connection name: eg Oracle at Oralab or Mysql at Rerun & press [Next] Now select a driver from the drop-down. CAUTION: If no driver appears, you will need to manually define one: (OPTIONAL STEP - only if no drivers found!) Manually defining a driver for your database connection: Press the circular icon next to the driver selection pulldown Choose the appropriate Name/Type of driver for your database IF you get a "Unable to locate Jar/zip in the file system" you will need to manually locate it! To do this: Choose the "JAR List" tab Select the the existing jar file & press "Edit Jar/Zip" button Locate the appropriate driver jar file on the file system (eg: ojdbc6.jar) You should now have a "New Connection Profile - Specify a Driver and connection details" window. Enter your database details here as per the above table and your userid, database name and password. Example: (ajpxx is the userid) Database: ajpxx URL: jdbc:oracle:thin:@oralab.it.uts.edu.au:1523:ell User name: ajpxx Password: your_oracle_password Select the [Save Password] checkbox. Try pressing the [Test Connection] button. You should get a message that says "Ping Succeeded!" Please note your connection URL. You will need this later... Pressing [Next] will show a verification panel. Press [Finish] to create your connection profile. Using the Data Source Explorer If you select the connection eg "Oracle at Oralab"/"Mysql at Rerun" and press the Right Mouse Button, you can see various options such as connect, ping and Properties. If Connect is not greyed out, choose the Connect option. You should see a popup of the connection progress. Hopefully all went well!! Now do the same thing, but select the Properties option. By default, Eclipse will return all the schemas which contains possibly hundreds of students entries so you probably will want to narrow this list down to your own schemas. On the Properties panel, choose the Default Schema Filter. De-select the Disable Filter checkbox, and then Select Expression and Name "starts with the characters". In the entry field, enter your database userid in UPPER CASE. (unfortunately, this is case sensitive) & press [OK] Now you can expand the database connection folder and schema subfolder to view the existing Tables etc details about your database. The Data Source Explorer gives you facilities to create tables, edit tables and run ad-hoc queries. For the moment, let's create a new table using the SQL wizard. Creating an SQL file Each person will have an database account set up which will contain your own database tables, separate to everyone else's. However initially, your database account will be blank, so the first task is to create a test table. Eclipse WTP provides an editor for creating SQL scripts. You can use the Control-spacebar shortcut key sequence for help as usual. You can create the SQL files in your project by selecting the project (Labs) and then Right Mouse Button -> New -> SQL File Let's create one called addressbook.sql. Entering the name into the File Name field: addressbook.sql. Next, select the Connection Profile name:eg: Oracle at Oralab Next, select the drop down in the Database Server Type field: Choose your database type if not already selected eg: Oracle 10 Then, select the Database Name: eg: ajpxx & [Finish] Now enter the following SQL: NOTE: change ALL date formats from 'yyyy-mm-dd' to 'dd-mon-yyyy' for Oracle ONLY!!
DROP TABLE addressbook ;
CREATE TABLE addressbook (
name VARCHAR(30) PRIMARY KEY,
address VARCHAR(50),
email VARCHAR(50),
workphone VARCHAR(12),
birthday DATE
) ;
-- note that dates are strings normally in the yyyy-mm-dd
-- formats for SQL92 eg:mysql, pointbase etc
-- oracle only accepts dd-mon-yyyy format, you need to use the non-standard
-- TO_DATE() function to convert other date strings to Oracle format
-- However, Oracle JDBC driver does accept the JDBC escape format eg:
-- {d 'yyyy-mm-dd'}
-- MYSQL VERSION
INSERT INTO addressbook VALUES ('chris','CB10.04.226',
'chw@it.uts.edu.au', '7938',
'2001-01-01') ;
-- ORACLE VERSION
-- INSERT INTO addressbook VALUES ('chris','CB10.04.226',
-- 'chw@it.uts.edu.au', '7938',
-- '01-JAN-2001') ;
INSERT INTO addressbook VALUES ('maolin','CB10.04.260',
'maolin@it.uts.edu.au', '7858',
'1990-12-30') ;
INSERT INTO addressbook VALUES ('wayne','CB10.04.230',
'brookes@it.uts.edu.au', '7991',
'1992-04-29');
SELECT * FROM addressbook ;
Points to note: SQL commands are not case sensitive. Case only matters when you are entering literal strings (between quotes). Single quotes must be used around strings, not double. Comments start with two dashes (--). You can also use C-style comments in one line /* */. You should not mix comments in with your SQL statements. Each single SQL command should be uninterrupted, and the comments can be placed between SQL commands. VARCHAR is a string data type which can contain a VARiable number of CHARacters, up to the specified maximum. DECIMAL is a data type for representing numbers, both with and without fractions. In this case we want a fixed-point number with four digits before the decimal point (integer part), and zero digits after the decimal point (fraction part). If you were storing monetary values, you might choose a data type of DECIMAL(8,2) for example, allowing for 2 digits of precision after the decimal point. Oracle has a non-standard representation of DATE, which only accepts strings in the format of 'DD-MON-YYYY' where MON is a 3 character string representing the month eg: JAN = January. You can use the Oracle-only TO_DATE() function to convert from a string in many date formats using a format string such as 'YYYY-MM-DD'. Running your SQL command file The next step is to run the SQL statements. You can do this by pressing Right Mouse Button -> Execute All command. You should also see a SQL Results window appear in the lower pane. Note that for each command that was executed, you will get a status line and status panel showing relevant messages & results. You can clear this by pressing Right Mouse Button -> Remove All to clear the results. Any time you are in an SQL page you can test an individual SQL statement by highlighting (selecting) the relevant SQL statement and then using the Right Mouse Button -> Execute Selected Text. command You can only use valid SQL commands here. Do not use SQL*PLUS or Mysql commands here (such as set autocommit on, password, describe addressbook etc). Exploring your Database The Data Source Explorer allows you explore your database. If you select your database connection folder, you can expand the folder to view the database tables Note: You can filter the schemas displayed by using Right Mouse Button -> Properties -> Default Schema Filter. You can optionally turn off filters by selecting the Disable Filter checkbox as well. Expanding this further you will see + tables, then your addressbook table. The neat feature of Database Explorer is that you can drill down in further details (such as column definitions, indexes etc). If you select table (eg: ADDRESSBOOK) and pressing Right Mouse Button -> Data you can: Edit the table in a spreadsheet - this lets you view, update, add, delete rows in the table Load - load data from text files (eg: CSV files) Extract - save data to a text file Try adding, updating and deleting records from the addressbook table. Finally, save the data by Extracting the records to a text file. Disconnecting When you wish to disconnect from the database, switch to the Database Source Explorer window and select the connection.Press Right Mouse Button -> Disconnect © 2008 University of Technology, Sydney. All Rights Reserved. Redistribution without permission prohibited.
