Somehow I never came across this problem before. I had a table that looked like this:
| links | CREATE TABLE `links` (
`link_id` int(5) unsigned NOT NULL auto_increment,
`link` varchar(250) NOT NULL default ”,
`x` float unsigned NOT NULL default ’0′,
`y` float unsigned NOT NULL default ’0′,
`h` float unsigned NOT NULL default ’0′,
`w` float unsigned NOT NULL default ’0′,
`link_type_id` int(5) unsigned NOT NULL default ’0′,
`alt` varchar(255) default NULL,
`color` varchar(8) default NULL,
`style` varchar(16) default NULL,
PRIMARY KEY (`link_id`)
) ENGINE=MyISAM AUTO_INCREMENT=65123341 DEFAULT CHARSET=latin1 |
A program I was writing needed to check if a link already existed in this table so I created this in my dao layer:
public List<Links> findByParsedLink(Links instance) {
log.debug(“finding Links”);
try {
Query q = sessionFactory.getCurrentSession().createQuery(
“from com.texterity.webreader.data.Links l where ” +
“l.x =” +
“and l.y = :y ” +
“and l.h = :h ” +
“and l.w = :w ” +
“and ” +
“l.link = :link and l.style = :style”);
q.setFloat(“x”, instance.getX());
q.setFloat(“y”, instance.getY());
q.setFloat(“h”, instance.getH());
q.setFloat(“w”, instance.getW());
q.setParameter(“link”, instance.getLink());
q.setParameter(“style”, instance.getStyle());
List<Links> results = (List<Links>)q.list();
log.debug(“find by example successful, result size: ”
+ results.size());
return results;
} catch (RuntimeException re) {
log.error(“find by example failed”, re);
throw re;
}
}
For some reason, none of the floats would match. Didn’t matter if I was using setFloat, setParameter, etc. I changed the ‘=’ to ‘like’ and everything seemed fine until I encountered a ’0′ value for one of the floats in which case ‘like’ would not match, but ‘=’ would. Turns out that floating point values are difficult to match in SQL when they lack precision. To make this work, I altered the table to look like so (note the precision values for the floats):
| links | CREATE TABLE `links` (
`link_id` int(5) unsigned NOT NULL auto_increment,
`link` varchar(250) NOT NULL,
`x` float(10,2) unsigned NOT NULL,
`y` float(10,2) unsigned NOT NULL,
`h` float(10,2) unsigned NOT NULL,
`w` float(10,2) unsigned NOT NULL,
`link_type_id` int(5) unsigned NOT NULL,
`alt` varchar(255) default NULL,
`color` varchar(8) default NULL,
`style` varchar(16) default NULL,
PRIMARY KEY (`link_id`)
) ENGINE=MyISAM AUTO_INCREMENT=835 DEFAULT CHARSET=latin1 |
Now I’m matching on ‘=’ with all values.