DEPRECATED!

The course staff is no longer supporting the JDBC version of the project. You may use these instructions, but do not expect support from the course staff.

Introduction

The Oracle database on the undergraduate servers runs on a protected department host. This means that you will have to take a few additional steps before you can run your code.

Sample Project

To give you a basic idea on how to make database calls and iterate through resultsets, we have created a sample project. This sample project is not meant as a small scale representation of the term project. You should carefully read all the project documentation provided to understand what your term project requires.

A sample project can be found here. Clone this repository into your IDE of choice to take a look at how you can use Java to send SQL commands to a database.

If you have trouble to clone the project by using the HTTP link, try to use the SSH link or download the zip file

What Should I Use?

We do not endorse any IDE. The best IDE is the one you feel the most comfortable with. If you feel more comfortable with VS Code, then that is a fine choice. In the rest of this document, we mention Eclipse and IntelliJ but these are, by no means, the only choices available to you. Do note that due to the variety of IDEs available, we cannot provide support on how to use your particular IDE. Our instructions will provide support for IntellliJ as other courses in the department use IntelliJ.

For those of you who choose to use IntelliJ, there is a Database Tool available in the IntelliJ Ultimate Edition (you can get a yearlong free trial by signing up with your student email here). This tool allows you to access the Oracle database directly without SSH-ing into an undergrad server and running SQL*Plus. You can access this tool by going to View -> Tool Windows -> Database, clicking + ..., and then adding an Oracle Data Source. Enter your CWL undername and password in the SSH tab, and your Oracle account username and password (ora_CWLusername and a<student number>) in the General tab. Now, you can execute queries in IntelliJ, and the queries will be processed by your Oracle database. Once again, this tool is not mandatory and you do not have to use this version of IntelliJ (nor do you have to use IntelliJ at all) if you do not want to.

IntelliJ Ultimate

If you choose to use IntelliJ Ultimate, follow the setup instructions below to run the sample project

  1. Make sure your Intellij version is up-to-date

  2. Oracle Database Setting:

    • View -> Tool Windows -> Database
    • Click the "Database" on the right bar
    • Click "+" ->Data Source -> Oracle
    • Under SSH/SSL tab
      • Set Use SSH tunnel & Create new configuration

      • Under the configuration tab
        • Set Host as remote.students.cs.ubc.ca
        • Set Port as 22
        • Set Local Port as 1522
        • Set Username as <CWL_Username>
          • Example: A student with CWL username "student1" should type student1 in the Username field

    • Under General tab
      • Set Host as dbhost.students.cs.ubc.ca
      • Set Port as 1522
      • Set SID as stu
      • Set Authentications as User&Password
      • Set User as ora_<CWL_Username>
        • Example: A student with CWL username "student1" should type ora_student1 in the User field
      • Set Password as a<student number>
        • Example: A student with student number "12345678" should type a12345678 in the Password field
    • Test Connection
        • If everything works, you should see the following message if you click Test Connection on the left bottom corner of the General tab
    • Click Apply, then click OK

  3. Adding Oracle Driver as a Project Dependency

    • If you try to run the code before adding the Oracle driver as a project dependency, you will encounter a "Error:(30, 76) java: package oracle.jdbc.driver does not exist" error. Use the following instructions to add the ojdbc8.jar file (found in the resources folder) to your classpath.
    • Follow the instruction in this link here. Make sure the dependency exists in the "Global Libraries" under "Project Structure" window.
      • If not : File -> Project Structure -> Global Libraries -> click "+" to add new global library ->select the driver under the resource folder

Other IDEs

If you choose to use VSCode or Eclipse, follow the setup instructions below before running the sample project

  1. Tunneling Into the Undergraduate Servers

  2. Adding Oracle Driver as a Project Dependency

Running Code From Your Machine

The following steps assume that you have already imported the sample project into an IDE of your choice. We are not endorsing any IDE.

Running the sample Project in IntelliJ

  1. Under the CPSC304-JavaDemo project, go to CPSC304-JavaDemo->src->ca.ubc.cs304->controller->Bank
  2. Run the project in your IDE. You can run your project in IntelliJ by going to the main class which is "Bank" in this case, and click "Run" from the right-click dropdown. You should see a login window appear. Enter your database credentials (ora_CWL as the username and a>student number<) into the window and click Login. Unlike when you login to sqlplus, the username used here does not have @stu at the end of it.
    3. The login window you should see when you run the Java sample project
  3. Once you have successfully logged in, you should see some text printed onto your console.
    4. The printout you should see in the console after successfully logging in

If you still continue to have problems, check that you haven't installed a personal version of Oracle which can cause problems with these instructions.

Creating Your Own Project

In the sample project,the Oracle JDBC driver has already been added to the build path. When you start implementing your project, be sure to include the Oracle driver in your build path! You can find a copy of the driver from the department's servers at /usr/lib/oracle/12.2/client64/lib/ojdbc8.jar.