Core Java_JDBC_Problem Statement 3 New Roles may emerge as the funtionalities of
ID: 3908360 • Letter: C
Question
Core Java_JDBC_Problem Statement 3
New Roles may emerge as the funtionalities of the Shipment Management System expands. Write a program that will create new roles into the System and map privileges to those newly created roles. The program should initially list all the privileges in the system, then get the new roles and their privileges using comma separated values. Then it should create the new roles and insert the corresponding privileges in the mapping table. Finally the program should fetch the privileges of a specified role and display them.
Problem Specifications:
[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 Privilege with below attributes,
Integer id
String name
Include getter and setter method for all the attributes
Include default constructor with and constructor argument order (id,name)
Create a class Role with below attributes,
Integer id
String name
ArrayList<Privilege> privilegeList
Include getter and setter method for all the attributes
Include default constructor with and constructor argument order (id,name)
Create a class PrivilegeDAO with following methods:
Create a class RoleDAO with following methods:
Create a class DbConnection with following method:
Here I am providing the piece of code. Please update it and send me back.
Role.java
import java.util.ArrayList;
public class Role {
private Integer id;
private String name;
private ArrayList<Privilege> privilegeList;
public Role(){}
public Role(String name) {
this.name = name;
}
public Role(Integer id, String name) {
this.id = id;
this.name = name;
}
public Role(Integer id, String name, ArrayList<Privilege> privilegeList) {
this.id = id;
this.name = name;
this.privilegeList = privilegeList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
RoleDAO.java
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class RoleDAO {
public void createRole(Role roleIns, ArrayList<Privilege> privilegeList) throws SQLException, ClassNotFoundException {
//fill the code
}
public List<Privilege> getPreviligeByRole(String role) throws ClassNotFoundException, SQLException {
//fill the code
}
}
script.sql
drop table if exists privilege;
create table privilege
(id int not null AUTO_INCREMENT,
name varchar(255) not null,primary key(id));
insert into privilege(name) values('Create');
insert into privilege(name) values('Process');
insert into privilege(name) values('Schedule');
insert into privilege(name) values('Cancel');
insert into privilege(name) values('View');
drop table if exists role;
create table role
(id int not null AUTO_INCREMENT,
name varchar(255) not null,primary key(id));
drop table if exists role_privilege;
create table role_privilege
(role_id int not null references role(id),
privilege_id int not null references privilege(id),
primary key(role_id,privilege_id));
Main.java
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class Main {
public static void main(String[] args) throws NumberFormatException, IOException, ClassNotFoundException, SQLException {
RoleDAO roleDAO = new RoleDAO();
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
PrivilegeDAO privilegeDAO = new PrivilegeDAO();
List<Privilege> privileges = privilegeDAO.getAllPrivileges();
System.out.println("List of privileges :");
System.out.format("%-15s %s ","Privilege ID","Privilege Name");
for(int i=0;i<privileges.size();i++) {
System.out.format("%-15s %s ",privileges.get(i).getId(),privileges.get(i).getName());
}
System.out.println("Enter number of new Roles to be created :");
Integer n = Integer.parseInt(br.readLine());
System.out.println("Enter the role and privileges :");
//fill the code
System.out.println("Enter the Role :");
String rol = br.readLine();
System.out.println("Privileges for "+rol+" :");
//fill the code
}
}
PriviledgeDAO.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class PrivilegeDAO {
public List<Privilege> getAllPrivileges() throws SQLException, ClassNotFoundException {
//fill the 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 ClassNotFoundException, SQLException {
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
}
}
Priviledge.java
public class Privilege {
private Integer id;
private String name;
public Privilege(){}
public Privilege(String name){
this.name = name;
}
public Privilege(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/role_privilege
db.username=root
db.password=test
Explanation / Answer
Ans.
Hello folks, i will tell that you should try to write this type of program on ownself. This will increase your confidence. One thing i want to mention that in solution i am not providing main method code. I will expect that you will try to write that code on ownself. If you will face any problem in understanding on solution you can write comment on this question. Now i am providing my solution below
----------------------------------------
DbConnection.java
import java.util.ResourceBundle;
public class DbConnection {
public static Connection getConnection() throws ClassNotFoundException, SQLException {
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
Connection con=DriverManager.getConnection(url, username, password);
return con;
}
}
--------------------------------------------------
privilege.java
public class Privilege {
private Integer id;
private String name;
public Privilege(){}
public Privilege(String name){
this.name = name;
}
public Privilege(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;
}
}
-----------------------------------
privilegeDAO.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class PrivilegeDAO {
public List<Privilege> getAllPrivileges() throws SQLException, ClassNotFoundException {
//fill the code
Connection con=null;
ResultSet rs=null;
Statement st=null;
List<Privilege> privilegeList=new ArrayList<>();
String query = "select * from privilege order by id asc";
con=DbConnection.getConnection();
st=con.createStatement();
rs=st.executeQuery(query);
while(rs.next()){
Privilege privilege=new Privilege();
privilege.setId(rs.getInt("id"));
privilege.setName(rs.getString("name"));
privilegeList.add(privilege);
}
return privilegeList;
}
}
-------------------------------------------
Role.java
import java.util.ArrayList;
public class Role {
private Integer id;
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
private ArrayList<Privilege> privilegeList;
public ArrayList<Privilege> getPrivilegeList() {
return privilegeList;
}
public void setPrivilegeList(ArrayList<Privilege> privilegeList) {
this.privilegeList = privilegeList;
}
public Role(){}
public Role(String name) {
this.name = name;
}
public Role(Integer id, String name) {
this.id = id;
this.name = name;
}
public Role(Integer id, String name, ArrayList<Privilege> privilegeList) {
this.id = id;
this.name = name;
this.privilegeList = privilegeList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
}
--------------------------------------
RoleDAO.java
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class RoleDAO {
public void createRole(Role roleIns, ArrayList<Privilege> privilegeList) throws SQLException, ClassNotFoundException {
//fill the code
String query="insert into role(name) values(?)";
String query_role_privilege="insert into role_privilege(role_id,privilege_id) values(?,?)";
String select_role_query="select id from role where name=?";
Connection con=null;
PreparedStatement st=null;
ResultSet rs=null;
if(null!=roleIns && null!=privilegeList){
con=DbConnection.getConnection();
st=con.prepareStatement(query);
//inserting data into role table
st.setString(1, roleIns.getName());
st.executeQuery(query);
//now first we will fetch the id from role table then for that id we insert how much role is available.
st=con.prepareStatement(select_role_query);
st.setString(1, roleIns.getName());
rs=st.executeQuery();
if(rs.next()){
roleIns.setId(rs.getInt("id"));
}
st=con.prepareStatement(query_role_privilege);
for(Privilege pri:privilegeList){
st.setInt(1, roleIns.getId());
st.setInt(2, pri.getId());
st.addBatch();
}
st.executeBatch();
}
}
public List<Privilege> getPreviligeByRole(String role) throws ClassNotFoundException, SQLException {
//fill the code
String query = "select id from role where name='"+role+"'";
String query_privilege_id ="select privilege_id from role_privilege where role_id=?";
String query_privilege = "select * from privilege where id=?";
List<Privilege> privilegeList=new ArrayList<>();
Privilege pri=null;
Role rl=new Role();
Connection con=DbConnection.getConnection();
PreparedStatement st= con.prepareStatement(query);
ResultSet rs=st.executeQuery();
if(rs.next()){
rl.setId(rs.getInt("id"));
rl.setName(role);
}
//now getting all the privilege_id for particular role_id from role_privilege table
st=con.prepareStatement(query_privilege_id);
st.setInt(1, rl.getId());
rs=st.executeQuery();
while(rs.next()){
pri=new Privilege();
pri.setId(rs.getInt("privilege_id"));
privilegeList.add(pri);
}
//now will get privileges from the privilege table on the basis of privilege id
st=con.prepareStatement(query_privilege);
if(!privilegeList.isEmpty()){
for(Privilege priv:privilegeList){
st.setInt(1, priv.getId());
rs=st.executeQuery();
if(rs.next()){
priv.setName(rs.getString("name"));
}
}
}
return privilegeList;
}
}
-----------------------------------------------
Main.java
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class Main {
public static void main(String[] args) throws NumberFormatException, IOException, ClassNotFoundException, SQLException {
RoleDAO roleDAO = new RoleDAO();
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
PrivilegeDAO privilegeDAO = new PrivilegeDAO();
List<Privilege> privileges = privilegeDAO.getAllPrivileges();
System.out.println("List of privileges :");
System.out.format("%-15s %s ","Privilege ID","Privilege Name");
for(int i=0;i<privileges.size();i++) {
System.out.format("%-15s %s ",privileges.get(i).getId(),privileges.get(i).getName());
}
System.out.println("Enter number of new Roles to be created :");
Integer n = Integer.parseInt(br.readLine());
RoleDAO dao=new RoleDAO();
for(int i=0;i<n;i++){
System.out.println("Enter the role and privileges :");
//fill the code
String roleAndPivilege=br.readLine();
String[] privilege=roleAndPivilege.split(",");
Role role=new Role();
role.setName(privilege[0]);
ArrayList<Privilege> list=new ArrayList<>();
for(int j=1;j<privilege.length;j++){
for(Privilege pri:privileges){
if(pri.getName().equalsIgnoreCase(privilege[j])){
list.add(pri);
break;
}
}
}
dao.createRole(role, list);
}
System.out.println("Enter the Role :");
String rol = br.readLine();
System.out.println("Privileges for "+rol+" :");
//fill the code
List<Privilege> pri=new ArrayList<>();
pri=dao.getPreviligeByRole(rol);
for(Privilege p:pri){
System.out.println(p.getName());
}
}
}