Issue with JdbcTemplate BeanPropertyRowMapper - setting int & double field as 0 instead of actual value

Question

I am trying to fetch an record from a database using Spring Jdbctemplate.. In JdbcTemplate I am trying to automate data bind by making use of the class BeanPropertyRowMapper... My database table column name-type & model object field name-type are same...I implemented this API but I am not getting expected results.. For int & double fields I am getting 0 & 0.0 value even though these are different values in database...

Please help to solve this problem... My code is like below,

@Override
public DiscountDetail getDiscountDetailById(int ID) throws ServiceException 
{
    String sql = "SELECT * FROM TBL_DISCOUNT where Id="+ID;
    logger.info("sql :"+sql);
    List<DiscountDetail> discounts  = jdbcTemplate.query(sql, new BeanPropertyRowMapper(DiscountDetail.class));

    if (discounts.size() == 1) {
        logger.info("discount :"+discounts.get(0).toString());
        return discounts.get(0);
    }   
    return null;
}

DescountDetail.java

public class DiscountDetail extends GenericEntity{

private int discountId,higherLimit,lowerLimit;
private double discountPercentage;

public int getDiscountId() {
    return discountId;
}

public void setDiscountId(int discountId) {
    this.discountId = discountId;
}

public int getHigherLimit() {
    return higherLimit;
}

public void setHigherLimit(int higherLimit) {
    this.higherLimit = higherLimit;
}

public int getLowerLimit() {
    return lowerLimit;
}

public void setLowerLimit(int lowerLimit) {
    this.lowerLimit = lowerLimit;
}

public double getDiscountPercentage() {
    return discountPercentage;
}

public void setDiscountPercentage(double discountPercentage) {
    this.discountPercentage = discountPercentage;
}


public String toString() 
{
    StringBuffer sb = new StringBuffer("");
    sb.append("discountId: " + discountId);
    sb.append(", higherLimit:" + higherLimit);
    sb.append(", lowerLimit:" + lowerLimit);
    sb.append(", discountPercentage:" + discountPercentage);
    sb.append(super.toString());
    return sb.toString();
}

}

--
-- Table structure for table `TBL_DISCOUNT_DETAIL`
--

CREATE TABLE `TBL_DISCOUNT_DETAIL` (
  `Id` int(11) NOT NULL,
  `DiscountId` int(11) NOT NULL,
  `HigherLimit` int(11) NOT NULL,
  `LowerLimit` int(11) NOT NULL,
  `DiscountPercentage` double NOT NULL,
  `CreatedOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CreatedBy` varchar(100) NOT NULL,
  `UpdatedOn` timestamp NULL DEFAULT NULL,
  `UpdatedBy` varchar(100) DEFAULT NULL,
  `VersionId` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Actual Output:

2018-05-08 01:28:15 INFO InventoryDAO:1052 - sql :SELECT * FROM TBL_DISCOUNT where Id=30 2018-05-08 01:28:15 INFO InventoryDAO:1056 - discount :discountId: 0, higherLimit:0, lowerLimit:0, discountPercentage:0.0: ID[30]: CreatedBy SYSTEM on 2018-05-07 19:06:42.0, UpdatedBy Rahul on 2018-05-07 19:06:42.0:V[2]

Expected Output:

2018-05-08 01:28:15 INFO InventoryDAO:1052 - sql :SELECT * FROM TBL_DISCOUNT where Id=30 2018-05-08 01:28:15 INFO InventoryDAO:1056 - discount :discountId: 1, higherLimit:5, lowerLimit:20, discountPercentage:5.0: ID[30]: CreatedBy SYSTEM on 2018-05-07 19:06:42.0, UpdatedBy Rahul on 2018-05-07 19:06:42.0:V[2]

You should probably use a prepared statement for your SQL to start off. Also, need the column names to verify that the Bean Mapper is working as intended. — Compass
@Compass....There was silly mistake from my side... I was getting wrong table name... Thanks... — rahul shalgar

Michael Peacock Michael Peacock · Accepted Answer · 2018-05-07T20:53:40

Bear in mind that if you're using a BeanPropertyRowMapper, you're also agreeing to allow it to actually map your database field names to your object based on its internal logic.

Particularly important is this bit from the JavaDoc:

Column values are mapped based on matching the column name as obtained from result set metadata to public setters for the corresponding properties. The names are matched either directly or by transforming a name separating the parts with underscores to the same name using "camel" case.

So, in this case, you're using

new BeanPropertyRowMapper(DiscountDetail.class)

and based on the DiscountDetail class, you should have also defined the column names in your target database table as:

discountId
higherLimit
lowerLimit
discountPercentage

Or as:

discount_id
higher_limit
lower_limit
discount_percentage

Most likely, the names of the public setter methods in DiscountDetail don't match the field names of the underlying table.

Issue with JdbcTemplate BeanPropertyRowMapper - setting int & double field as 0 instead of actual value

1 Answers