Task 12 - Create Java Program with MySQL Database Connectivity

There are 3 parts of this assignment or programming task.

Part 1: Understand Database Server, Client and SQL

In MySQL Server, create a database "infosys" that shall have "users" table, using MySQL. 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
  2. Retrieve all (using GUI) or selected records and columns from the table using GUI and SQL
  3. Delete records, single and multiple using GUI and SQL
  4. Update records' different fields using GUI and SQL
If you have not attended database introduction or database connectivity lectures. You can watch here:
  1. Database Introduction 
  2. Database Connectivity
Do not start Part 2, until you are comfortable with running CRUD queries from Java program.

Part 2: Its 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 or well understood my given solution before moving to Part 3.

Part 3: Define a new class named DbUserService that shall implement UserService interface. So, you shall redefine 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 assignment, 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 user 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 use in this task.