Core Java_JDBC_Problem Statement1

Write a program to display all the Roles available in the Shipment Management System. The Roles are to be displayed sorted based on the Role Name. 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 Role with the following 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 RoleDAO with below methods to handle all database related operations

S.NO	Method Description	Method Name
1	public ArrayList getAllRoles()	This method is used to fetch all the role details from user table and display the role details sorted based on role name.

Create a class Main with main method. 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.

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/userrole 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. Display the role details in sorted based on the role name. Sample Input and Output : Role Details: Admin Manager Maritime Operator Shipper

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

RoleDAO.java

import java.sql.Connection;

import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList;

public class RoleDAO {

public ArrayList getAllRoles() { // fill the code }

}

mysql.properties

db.url=jdbc:mysql://localhost:3306/userrole db.username=root db.password=test

Main.java

public class Main {

public static void main(String[] args) {

// fill the code

}

}

DbConnection.java

import java.sql.Connection;

import java.sql.DriverManager;

public class DbConnection {

public static Connection getConnection() {

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

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

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

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

// fill the code

}

}

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; } }

rolescript.sql

drop table if exists role; create table role( id int not null, name varchar (255) not null, primary key(id) );

insert into role values(1,'Manager'); insert into role values(2,'Admin'); insert into role values(3,'Shipper'); insert into role values(4,'Maritime Operator');