Does anyone know of a tool like PowerArchitect or SquirrelSQL or maybe eclipse plugin that lets you also generate jpa/sql queries?
Imagine you choosing your database, or your entity beans, and the modeling would reverse engineer your database/entity model, so that you could visually just choose the columns you wanted to select, and it would generate jpa or sql queries for you.
For instance choosing A.b and X.y would generate something like this:
select a.b, x.y from A a, X x join ......
It's a fairly heavy-weight and non-free solution that requires significant investment of time to justify itself, but I believe Database Visual Architect from Visual Paradigm will do what you're asking for.
Take a look at the Generate SQL documentation for DB-VA to get a sense of whether it supports the operations and types of queries you're looking for. If you can't fully assess its applicability from the docs, there's a free 30-day trial that might be worth a shot.
For what it's worth, I've used older versions of several of Visual Paradigm's products and they're definitely top-notch even if a little cumbersome at times.
Eclipse (more precisely the Data Tools Platform Project) has a SQL Query Builder built-in. Open a SQL Scrapbook, right-click in it and select Edit in SQL Query Builder:
This will open the SQL Query Builder:
Then add tables, conditions, etc. There is a Webinar showing it in action.
MyEclipse also has a Visual SQL Query Builder. And I'm pretty sure other standalone universal clients support this visual query builder too.
For JPA, I'm not aware of any visual builder in Dali. This has been mentioned in the past but I have no idea of the exact status (I don't use Dali actually). However, this article mentions a Visual JPQL query builder in IBM Rational Application Developer.