Task 12 - Create Java Program with MySQL Database Connectivity

There are two parts of this task:

Part 1: Understand database server, client and SQL

In MySQL Server, create a database "infosys" that shall have "users" table using MySQL Workbench or some other client program. The table shall have id, name, gender, phone and status field of type int, varchar, varchar, varchar and boolean/tinyint respectively. After creating the table, do following:
  1. Add 4-5 records using MySQL Workbench and SQL
  2. Retrieve all (using GUI) or selected records and columns from the table using SQL
  3. Delete records, single and multiple using GUI and SQL
  4. Update records different fields using GUI and SQL
How to know the SQL queries for differnt operations as we have not studied database course?
1. When you use Workbench to perform an operation e.g. create table, add / update records, the Workbench display the SQL query that it send to MySQL database. If you carefully read/analyze the query, you can easily get basic idea of what SQL queries are for different operations.

2. Open SQL Tutorial of W3Schools. See left navigation bar of tutorial, it lists different SQL queries. Practice all queries uptil joins related query (excluding all type of joins at the moment).

I have recorded video tutorial where I explained and run each basic query along with how to establish connection with MySQL Server using MySQL Workbench, you can Click Here to watch. After watching, read W3Schools SQL tutorial to explore other interested quries and its clauses.

Do not start Part 2 below until you very comfortable with running all 4 types of queires.


Part 2: Java program with database connectivity using JDBC API

Part 2 is in continuation of Task 10. I have also uploaded the solution on this blog for your help, Click Here to view the solution. Make sure you have done Task 10 before starting the Part 2.

Define a new class named DbUserService that shall implement UserService interface. So, you would define the methods of the interface as you did in Task 10. In Task 10, you saved, read, updated the records in/from an ArrayList. In this task, you shall perform these operations in MySQL database using JDBC API. For example in User getUser(int userID) method, you would use SELECT statement to fetch the record based on userID, then you would make User object using returned data from the database and return that user object to calling code, if database do not contain the user, you would return null. In same way, you would use different SQL queries to define each method, depending on what method requires you to do.

Make a menu driven CLI program that shall allow operator to perform different operations (listed in the interface). You need to use JDBC API to perform CRUD operations on MySQL. I have written a post on Java JDBC CRUD Examples, it would help you to see JDBC classes and methods to be used for this task. If you need detailed explanation of how to use JDBC API, you can watch my video tutorial that explains how to use JDBC API to perform CRUD operations from Java program.