This free online course will teach you how to interact with a MySQL database programmatically using Java. Almost without exception, every application you build in the real world will store critical information in a database, and knowing how to use Java database connectivity (JDBC) to communicate with a database will be an excellent skill to have on your resume.

If you haven't worked with databases or SQL before, then please first complete the Database Course: MySQL and SQL CRUD Operations before taking this course.

Course Prep:

1. Read Chapter #14 in the Core Servlets and JavaServer Pages textbook (pages 591 - 606, and pages 616 - 622).
2. View the JDBC lecture in Powerpoint format (also available here in pdf format), which will provide an overview of JDBC and several useful code samples.
3. Download and install MySQL Server 5.0 by following the instructions in MySQL5_Install_Configure.doc.

Course Exercises:

1. Follow course exercises 1 through 4 in the Database Course: MySQL and SQL CRUD Operations to load a pre-built SQL script. This will create an Etraining database containing two tables: users and accounts.
2. Create the following Java business objects and data access object, and implement the methods defined below:
• Login (business logic specific to user information)
  // Query the users table and return an integer representing the user's database key.
  
  public int lookupUser(String UserName, String Password);

• Account (business logic specific to account information and processes)

  // Store the user's database key as a class property.
  
  public void setUserKey(int UserKey);

  // Lookup the user's account balance from the accounts table
  
  public float getAccountBalance();

  // Deposit money into the user's account
  
  public void deposit(float Amount);

  // Withdraw money from the user's account
  
  public void withdraw(float Amount);

• DatabaseManager (database logic for connecting to a database and executing SQL commands)

  // Declare a JDBC driver and establish a database connection (called internally only)
  
  private Connection getConnection();

  // Query the database and return the resulting set of records
  
  public ResultSet executeQuery(String SQL)

  // Update the database and return the number of affected rows
  
  public int executeUpdate(String SQL);

• UnitTest

// Main entry point to test your application

public static void main (String[] args);

1. Use the Login object to lookup a user's database key from the database. You may choose any of the users that exist in the users table, and hard-code that user's username and password in the UnitTest class.
2. Print the user's database key to System.out.
3. Set the UserKey property on the Account object, and then use the Account object to lookup the user's account balance from the database based on that UserKey.
4. Print the user's account balance to System.out.
5. Use the Account object to make a deposit (eg. $500.00) and print the resulting account balance to System.out.
6. Use the Account object to make a withdrawal (eg. $250.00) and print the resulting account balance to System.out.
3. Compile your JDBC application using the following command: javac *.java -classpath mysql-connector-java-3.1.14-bin.jar (assuming that both your jar file and the JDBC driver are located in the same folder).
4. Package your JDBC application into a jar file using the following command: jar -cvf assign06_YourName.jar *
5. Make sure your UnitTest class contains a public static void main (String[] args) implementation that performs the above tests, so that it can be run by entering "java -cp assign06_YourName.jar;mysql-connector-java-3.1.14-bin.jar UnitTest" at the command line.

One important thing to remember when working with a ResultSet is that it does not contain the data itself - it merely provides a wrapper around the data on a per-record basis. Therefore the database Connection object itself must remain open the entire time that a ResultSet is returning data. A common mistake is to close the Connection right after getting the ResultSet object. Each time you loop through the ResultSet's data, it is requesting each record of data from the database one at a time - using the Connection object to communicate with the database.

That might all sound like a mouthful right now, but you'll see what I mean when you start working with the Connection and ResultSet objects directly.

Check your work: assign06_Solution.zip

DiskCopy and Clean - Download Safely Copy Everything to Your New Drive & Permanently Erase Data from Your Old Drive Are you purchasing a new hard drive? Disk Copy & Clean is the perfect solution for upgrading from your old hard drive to your new hard drive. DiskCopy & Clean moves all your data, applications and Windows to your new hard in one easy step. Plus, if you have partitions on your old hard drive, they can be moved and expanded proportionally to your new hard drive. Special Offer: Save 20% with Coupon Code AFDCC20