Basically i have a Stored Procedure which accepts 8 parameters, and based on all these parameters only it's executing the query and giving me an output, but now i have a situation where i can either pass a single value or multiple value anywhere in between 1 to 8 parameters, it should work flawlessly and it should return some value based on the parameters that has been passed. My StoredProcedure is in MySQL, and i'm calling this through CallableStatement in Java code. Below is the snippet of my Storedprocedure.
DELIMITER $$
CREATE PROCEDURE get_details(IN cus_name varchar(255),u_id int, ent_type varchar(255)
,branchId varchar(255), from_date datetime,to_date datetime, val varchar(255),rep_status varchar(255)
)
BEGIN
SELECT
r.id,r.parent_request_id,r.customer_master_id,
r.user_master_id,r.request_status,
cm.customer_name,
u.unique_id,
c.branch_id,c.created_date,c.entity_name,
c.entity_status,c.entity_type,c.no_of_documents,
c.no_of_parties,c.request_id,c.validity,c.itr_validity
FROM request as r
left join user_master as u on r.user_master_id = u.id
left join customer_master as cm on u.customer_master_id = cm.id
left join customer_entity as c on r.id= c.request_id
where
customer_name = cus_name and
unique_id = u_id and
entity_type = ent_type and
branch_id = branchId and
created_date >= from_date and
created_date <= to_date and
validity = val and
request_status = rep_status and
entity_status='parent';
END$$
DELIMITER ;
and Below is my code to call this StoredProcedure in Java
String query = "{ call get_details(?,?,?,?,?,?,?,?)}";
ResultSet resultSet = null;
List<SuperAdmin> admins = new ArrayList<SuperAdmin>();
try(
Connection connection = getconnection();
CallableStatement callableStatement = connection.prepareCall(query)){
if(ReportDTO.getCustomerName() != null){
callableStatement.setString(1, ReportDTO.getCustomerName());
}
if(ReportDTO.getUniqueId() != null){
callableStatement.setString(2, ReportDTO.getUniqueId());
}
if(ReportDTO.getEntity_type() != null){
callableStatement.setString(3, ReportDTO.getEntity_type());
}
if(ReportDTO.getBranchId() != null){
callableStatement.setString(4, ReportDTO.getBranchId().toString());
}
if(ReportDTO.getFilter_from() != null){
Date newFilterDate_from = dateFormat.parse(ReportDTO.getFilter_from().toString());
String newFormattedDate = dateFormat2.format(newFilterDate_from);
callableStatement.setString(5, newFormattedDate);
}
if(ReportDTO.getFilter_to() != null){
Date newFilterDate_to = (Date) dateFormat.parse(ReportDTO.getFilter_to().toString());
String newFormattedDateTo = dateFormat2.format(newFilterDate_to);
callableStatement.setString(6,newFormattedDateTo);
}
if(ReportDTO.getValidity() != null){
callableStatement.setString(7, ReportDTO.getValidity());
}
if(ReportDTO.getReport_status() != null){
callableStatement.setString(8, ReportDTO.getReport_status());
}
resultSet = callableStatement.executeQuery();
while(resultSet.next()){
SuperAdmin sAdmin = new SuperAdmin();
sAdmin.setRequest_status(resultSet.getString("request_status"));
sAdmin.setCustomer_entity_type(resultSet.getString("entity_type"));
sAdmin.setCustomer_entity_name(resultSet.getString("entity_name"));
sAdmin.setRequest_id(resultSet.getInt("id"));
sAdmin.setCustomer_entity_created_date(resultSet.getDate("created_date"));
sAdmin.setCustomer_entity_status(resultSet.getString("entity_status"));
sAdmin.setCustomer_entity_no_of_documents(resultSet.getString("no_of_documents"));
sAdmin.setCustomer_entity_no_of_parties(resultSet.getString("no_of_parties"));
sAdmin.setCustomer_entity_validity(resultSet.getString("validity"));
sAdmin.setCustomer_entity_itr_validity(resultSet.getString("itr_validity"));
sAdmin.setCustomer_entity_branch_id(resultSet.getLong("branch_id"));
admins.add(sAdmin);
}
}
any help would be highly appreciable.Thanks well in advance.
