Recently I had the task of moving a program from one database engine to another. The program primarily used Hibernate. The actual job of moving it from one database to another was... locating the JDBC module for the other database, locating the dialect name for the other database, telling Hibernate about both, and, mostly, it just worked. Except. Except there were six places where the program issued genuine, real actual SQL. Those six queries had to be re-written because they used a feature of the original database engine that didn't exist on the other database engine. Still, six queries are a lot easier to re-write than hundreds of queries.
I still consider Spring/Hibernate to be evil. But this demonstrates that an ORM with a query dialect engine does have significant advantages in making your program portable into different environments. Being able to prototype your program on your personal desktop with MySQL then deploy it against a real database like Oracle without changing anything about the program other than a couple of configuration settings... that is really cool. And useful, since it causes productivity to go sky high.
Now to see if there's any decent ORM for Java...
I am a senior lead engineer and architect who has taken multiple products from concept to market and beyond. I am also one of the original Linux penguins -- my first Linux product hit the market in June 1996 and its latest incarnation is still running to this day.