Spring JDBC template query

Question

I am facing issue, while passing the list of values to the SQL query which is placed in the properties file. Is there anyway that i can dynamically generate the placeholders ? according to the values received. If so, static query will work ? please advise.

DAO Code:

public List<DestinationDTO> fetchEmsStatistics(DestinationDTO destinationDTO)
        //throws Exception
{
    LOG.info("start of  -- MonitorDAOImpl.fetchEmsStatistics()");
    List<DestinationDTO> destinationDTOList = new ArrayList<DestinationDTO>();

    String str = destinationDTO.getDestinationNames();
    String dest[] = str.split(","); 

/* "example 1" , "example2" are hardcoded,however i want the values of dest array and according to count the placeholders should be placed in the query */       
try{
            destinationDTOList = this.jdbcTemplate.query(
                        this.fetchEmsStatisticsQuery,
                        new Object[] {
                                "%" + destinationDTO.getDestinationTypeId() + "%",
                                destinationDTO.getDestinationSourceId(),
                                "example1","example2",
                                destinationDTO.getStartTime() + ":00",
                                destinationDTO.getEndTime() + ":00" },new DestinationDTORowMapper());

             System.out.println("finally"+ destinationDTOList.size());


         }
         catch(Exception e)
         {
             e.printStackTrace();
         }

    LOG.info("end of  -- MonitorDAOImpl.fetchEmsStatistics()");

    return destinationDTOList;
}

Query:

Select 
 DESTINATION 
 ,MIN_IC
 , MAX_IC
 ,MAX_OC
 ,MAX_IC-MIN_IC as PROCESSEDMSGS 
from (
 select 
  DESTINATION 
  ,min(IN_MSG_COUNT) MIN_IC
  ,max(IN_MSG_COUNT) MAX_IC
  , max(OUT_MSG_COUNT) MAX_OC 
 from 
  EMS_MONITOR C 
 where 
  C.DESTINATION_TYPE_ID like ? and 
  C.SOURCE_ID=? and 
  C.DESTINATION IN  (?,?) and 
  C.RCVD_DATE >=? and C.RCVD_DATE <=? 
 group by 
  DESTINATION
)

Desired query format:example

Select 
 DESTINATION 
 ,MIN_IC
 , MAX_IC
 ,MAX_OC
 ,MAX_IC-MIN_IC as PROCESSEDMSGS 
from (
 select 
  DESTINATION 
  ,min(IN_MSG_COUNT) MIN_IC
  ,max(IN_MSG_COUNT) MAX_IC
  , max(OUT_MSG_COUNT) MAX_OC 
 from 
  EMS_MONITOR C 
 where 
  C.DESTINATION_TYPE_ID like '%1%' and 
  C.SOURCE_ID=1 and 
  C.DESTINATION in ('M.COM.CAT.AVAIL.STORE.Q.FCC','M.COM.CAT.ELIG.BOPS.STORE.Q.FCC') and 
  C.RCVD_DATE >='2014-09-16 17:00:00' and 
  C.RCVD_DATE <='2014-09-16 20:01:00' 
 group by 
  DESTINATION
)

@mustaccio I changed my code now and it is working fine with the hard coded values, however, I want the dynamic values to be placed instead of hardcoded ones. How can I improve my code either by changing it in the DAO or in the query (I want the query to be placed in properties file only). please provide your valuable suggestions. — Jalal
– Jalal, Commented Jan 20, 2015 at 16:36
If your IN list has a reasonable maximum number of values, you can save your statement text with that number of parameter markers: ...DESTINATION IN (?,?,?,?...,?)... and assign nulls to the parameters when you don't have enough values. Alternatively, use dynamic SQL, as suggested in one of the answers below. — mustaccio
– mustaccio, Commented Jan 20, 2015 at 16:43
sure.. I will try it and get back with the result.. thank you ! — Jalal
– Jalal, Commented Jan 20, 2015 at 17:02

josivan · Accepted Answer · 2015-01-20 13:36:59Z

0

Considering your extra comment.

Try something like:

in properties file:

myquery=SELECT * from somehting where in (%IN_CLAUSE%);

In code you can get the string and:

String q = originalQuery.replace("%IN_CLAUSE%", str);

then check the q value.

answered Jan 20, 2015 at 13:36

community wiki

josivan

Sign up to request clarification or add additional context in comments.

2 Comments

Jalal Over a year ago

thank you for your suggestion.. Will try it and let you know, if this works !

Jalal Over a year ago

Muito Obrigado viu Senhor !! :)

josivan · Accepted Answer · 2015-01-20 11:11:32Z

0

Why do not try to use a subselect instead send parameter in IN clause?

I know that my answer can't help by is a different approach.

answered Jan 20, 2015 at 11:11

josivan

2,0732 gold badges16 silver badges28 bronze badges

1 Comment

Jalal Over a year ago

I have list of values , so I am using IN operator. All I want is how to work with the IN operator. thank you !

Collectives™ on Stack Overflow

Spring JDBC template query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related