Core Java_JDBC_Problem Statement 2

Write a program to display all the Users available in the system and their corresponding roles along with Users details like Street, City and State. The Users are to be displayedsorted based on their username. Problem Specification : [Note: Strictly adhere to the object oriented specifications given as a part of the problem statement. Use the same class names, attribute names and method names.] Create a class Main with main method Create a class Contact with below private attributes,

Integer id

String street

String city

String state

Include getter and setter method for all the attributes Include default constructor and 4 argument constructor with arguments id,street,city,state. Create a class Role with below private attributes,

Integer id

String name

Include getter and setter method for all the attributes Include default constructor and 2 argument constructor with arguments id,name Create a class User with below private attributes,

Integer id

String name

Role role

Contact contact

Include getter and setter method for all the attributes Include default constructor and 4 argument constructor with arguments id,name,role,contact. Create a class DbConnection with following method

Method	Description
public static Connection getConnection()	This method is used to connect the java application with mysql database. Here register the JDBC driver for the application,configure the database properties(fetch from mysql.properties) and return the connection object.

Create a class UserDAO with below methods to handle all database related operations

S.NO	Method Name	Method Description
1	public ArrayList getAllUsers()	This method is used to fetch all the user details from user table and display the user details with their contact and role info in ascending sorted order based on user name.

Problem Hint : DAO Layer - Data access layer provides the gateway to create, reterive, update or delete any data in the database. All database related operations will be performed in this layer. mysql.properties : db.url=jdbc:mysql://localhost:3306/user_role db.username=root db.password=test Use the below code to retrieve the connection details from mysql.properties to establish connection ResourceBundle rb = ResourceBundle.getBundle("mysql"); String url = rb.getString("db.url"); String user = rb.getString("db.username"); String pass = rb.getString("db.password");

Table Structure:

DBMS create scripts and insert queries and supporting jar files are available as part of code template.The table and column names are case sensitive. Follow the table and column names along with its case as mentioned in the table structure to execute SQL queries.

Input and Output Format: Refer sample input and output for formatting specifications.Print all the values in main function. Use System.out.format("%-10s %-20s %-25s %-10s %-10s ","User","Role","Street","City","State"); to display the user details with contact and role info . Display the user details in sorted order(ascending) based on user name Note : [All text in bold corresponds to input and rest corresponds to output.] Sample Output: User Role Street City State Bula Accountant GC Road Belgaum Karnataka Jane Manager 9/5A Hosur Road Bangalore Karnataka Jennifer Manager 43/d warren road Chennai TamilNadu Mathew Managing Director 10/a Eatteri Road Salem TamilNadu Rehaana Receptionist Hosa Road Ankola Karnataka Sinjana Accountant RSP Road Salem TamilNadu

Here, I am providing the piece of code. Please update them and send me back.

Contact.java

public class Contact {

private Integer id;

private String street;

private String city;

private String state;

Contact(Integer id,String street,String city,String state){

this.id = id;

this.street = street;

this.city = city;

this.state = state;

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getStreet() {

return street;

}

public void setStreet(String street) {

this.street = street;

}

public String getCity() {

return city;

}

public void setCity(String city) {

this.city = city;

}

public String getState() {

return state;

}

public void setState(String state) {

this.state = state;

}

}

Main.java

public class Main {

public static void main(String[] args) { //fill your code } }

script.sql

drop table if exists user; drop table if exists contact; drop table if exists role; create table role( id int not null auto_increment, name varchar(30) not null, primary key(id));

create table contact( id int not null auto_increment, street varchar(30) not null, city varchar(30) not null, state varchar(30) not null, primary key(id));

create table user( id int not null auto_increment, name varchar(30) not null, contact_id int not null, role_id int not null, primary key(id), foreign key(contact_id) references contact(id), foreign key(role_id) references role(id));

insert into role (name) values ('Managing Director'); insert into role (name) values ('Manager'); insert into role (name) values ('Accountant'); insert into role (name) values ('Receptionist');

insert into contact (street,city,state) values ('10/a Eatteri Road','Salem','TamilNadu'); insert into contact (street,city,state) values ('43/d warren road','Chennai','TamilNadu'); insert into contact (street,city,state) values ('9/5A Hosur Road','Bangalore','Karnataka'); insert into contact (street,city,state) values ('GC Road','Belgaum','Karnataka'); insert into contact (street,city,state) values ('Hosa Road','Ankola','Karnataka'); insert into contact (street,city,state) values ('RSP Road','Salem','TamilNadu');

insert into user (name,role_id,contact_id) values ('Mathew',1,1); insert into user (name,role_id,contact_id) values ('Jane',2,3); insert into user (name,role_id,contact_id) values ('Jennifer',2,2); insert into user (name,role_id,contact_id) values ('Bula',3,4); insert into user (name,role_id,contact_id) values ('Rehaana',4,5); insert into user (name,role_id,contact_id) values ('Sinjana',3,6);

UserDAO.java

public class UserDAO {

public ArrayList getAllUsers() {

//fill your code }

}

DbConnection.java

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.ResourceBundle;

public class DbConnection {

public static Connection getConnection() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException{

ResourceBundle rb = ResourceBundle.getBundle("mysql");

String url = rb.getString("db.url");

String username = rb.getString("db.username");

String password = rb.getString("db.password");

Connection connect = null;

//fill your code

}

}

User.java

public class User {

private Integer id;

private String name;

private Role role;

private Contact contact;

public User(Integer id, String name,Role role,Contact contact) {

this.id = id;

this.name = name;

this.role = role;

this.contact = contact;

}

public Contact getContact() {

return contact;

}

public void setContact(Contact contact) {

this.contact = contact;

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Role getRole() {

return role;

}

public void setRole(Role role) {

this.role = role;

}

}

Role.java

public class Role {

private Integer id; private String name;

public Role(Integer id, String name) { this.id = id; this.name = name; }

public Integer getId() { return id; }

public void setId(Integer id) { this.id = id; }

public String getName() { return name; }

public void setName(String name) { this.name = name; } }

mysql.properties

db.url=jdbc:mysql://localhost:3306/user_role db.username=root db.password=student