Java程序辅导

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

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.

1. 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.
   
2. Select the Database Connections folder in the Data Source Explorer and press the Right Mouse Button --> New
   
3. You should get a New Connection Profile wizard. Choose the appropriate driver for your database - for example, Oracle Database Connection or Mysql & press [Next]
   
4. Now enter the Connection name: eg  Oracle at Oralab or Mysql at Rerun & press [Next]
   
5. Now select a driver from the drop-down.
   CAUTION: If no driver appears, you will need to manually define one: 
   
6. (OPTIONAL STEP - only if no drivers found!)
    Manually defining a driver for your database connection:
   1. Press the circular icon next to the driver selection pulldown
   2. Choose the appropriate Name/Type of driver for your database
   3. IF you get a "Unable to locate Jar/zip in the file system" you will need to manually locate it! To do this:
      1. Choose the "JAR List" tab
      2. Select the the existing jar file & press "Edit Jar/Zip" button
      3. Locate the appropriate driver jar file on the file system (eg: ojdbc6.jar)
         
   
   
7. You should now have a "New Connection Profile - Specify a Driver and connection details" window.
8. Enter your database details here as per the above table and your userid, database name and password.
9. 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.
     
10. Try pressing the [Test Connection] button. You should get a message that says "Ping Succeeded!"
11. Please note your connection URL. You will need this later...
    
12. Pressing [Next] will show a verification panel. Press [Finish] to create your connection profile.

Using the Data Source Explorer

1. 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.
   
2. If Connect is not greyed out, choose the Connect option. You should see a popup of the connection progress. Hopefully all went well!!
   
3. 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.
   
4. De-select the Disable Filter checkbox, and then Select Expression and Name "starts with the characters".
   
5. In  the entry field, enter your database userid in UPPER CASE. (unfortunately, this is case sensitive) & press [OK]
   
6. Now you can expand the database connection folder and schema subfolder to view the existing Tables etc details about your database.
   
7. 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 willcontain your own database tables, separate to everyone else's.However initially, your database account will be blank, so thefirst 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

1. Let's create one called addressbook.sql. Enteringthe name into the File Name field: addressbook.sql.
2. Next, select the Connection Profile name:eg: Oracle at Oralab
3. Next, select the drop down in the Database Server Type field: Choose your database type if not already selected eg:  Oracle 10
4. 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 VERSIONINSERT 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-stylecomments in one line /* */. You should not mix comments in with your SQL statements.Each single SQL command should be uninterrupted, and the commentscan be placed between SQL commands.

• VARCHAR is a string data type which can contain a VARiable numberof CHARacters, up to the specified maximum.

• DECIMAL is a data type for representing numbers, both with and withoutfractions. In this case we want a fixed-point number with four digitsbefore the decimal point (integer part), and zero digits after the decimalpoint (fraction part). If you were storing monetary values, you mightchoose a data type of DECIMAL(8,2) for example, allowing for 2 digitsof 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