File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Other Application Frameworks and the fly likes Spring JDBC: registering the SQL type with MapSqlParameterSource Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Frameworks » Other Application Frameworks
Bookmark "Spring JDBC: registering the SQL type with MapSqlParameterSource" Watch "Spring JDBC: registering the SQL type with MapSqlParameterSource" New topic
Author

Spring JDBC: registering the SQL type with MapSqlParameterSource

Hiren Shah
Greenhorn

Joined: Oct 04, 2008
Posts: 3
Hi,

I am using simpleJdbcTemplate.queryForInt for retrieving the count. Table has one CHAR(4) datatype and the value for the parameter for queryForInt is "88" supplied as String.

When i execute this method by passing MapSqlParameterSource or the Object having "88" it returns "0". However, when i harcode "88" directly in the sql it returns the expected count.

Do you know what am i missing here? I even tried specifying Types.CHAR as the sqlType.

Thank you,
SpringForever
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

"Spring Forever"

Please click on the My Profile link above and change your display name to meet the JavaRanch Naming Policy of using your real first and real last names.

Thanks

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

After changing your display name, then please post your code so we can see what you mean.

Thanks

Mark
Hiren Shah
Greenhorn

Joined: Oct 04, 2008
Posts: 3
Thank you, Mark!

The code and the sql is:

public int getRecordsCount(VehicleVO vehicleVO)
{
MapSqlParameterSource mapParameters = new MapSqlParameterSource();
mapParameters.addValue("id",vehicleVO.getId(),Types.VARCHAR);
mapParameters.addValue("code", vehicleVO.getCd(),Types.CHAR);
mapParameters.addValue("date", vehicleVO.getDt(), Types.NUMERIC);

return jdbcOperations.queryForInt(getRecordCountQuery, mapParameters);
}

SELECT COUNT (*) TOTAL_RECORDS
FROM VEHICLE_DETAIL
WHERE ID = :id
AND PRICE_CD = :code
AND REC_DT = ate


If i put the hard-coded values in the sql then it works fine. But with the method above it returns "0". When i changed the column type for "code" to varchar in the database then it returns the count greater than "0".
[ October 06, 2008: Message edited by: Hiren Shah ]
Hiren Shah
Greenhorn

Joined: Oct 04, 2008
Posts: 3
I got this reply on Spring forum.....

"You are using a CHAR(4) column so the data in that field is 4 characters long which would be '88 ' (notice the two spaces at the end). Supplying a string of length 2 will never match.

The reason the hardcoded SQL works is because the database engine is converting the literals provided to match the column type and is padding the values provided. When you are using bind variables there is no such conversion - you have to provide the correct data values."

It works.

Thank you,
Hiren
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Spring JDBC: registering the SQL type with MapSqlParameterSource