11

I'm not looking for a persistence layer like Hibernate, I just want to generate SQL-strings and they should be compatible with PreparedStatement. I've tried libraries such as Squiggle, but it only supports SELECT, I would also like to generate insert and updates. An ideal usage would be something like:

generateInsertOn("myTable").addValue("value1").addValue("value2").generate();

that would generate this string:

"INSERT INTO myTable (value1, value2) VALUES(?, ?)"

I know that there exists questions that are a lot like mine, such as this, but they don't quite ask the same thing as I do.

Improve this question
11
  • 1
    If you already know that there are similar questions, then it's great that you tell us about them. But telling us why these questions are not what you want would be even better! Commented Aug 8, 2011 at 13:58
  • What is your problem in writing it yourself? Commented Aug 8, 2011 at 14:00
  • 2
    whats the problem with hibernate, its great ... Commented Aug 8, 2011 at 14:01
  • 1
    @NimChimpsky, I don't think Hibernate is up for the job if you want to have full control over the SQL your render... Commented Aug 9, 2011 at 6:37
  • 2
    @NimChimpsky: Hibernate is good for many purposes, but not when you want to control your SQL. In many RDBMS and large-scale systems, if you don't control your SQL, then you're doomed. Think of adding hints to Oracle queries, or fine-tuning your nested LEFT OUTER JOIN expressions, integrating stored procedures, etc, etc. But we don't know what the OP intends, so let's not start this discussion... Commented Aug 9, 2011 at 7:27

3 Answers 3

Reset to default
14

For arbitrary SQL, use jOOQ. jOOQ currently supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and MERGE. You can create SQL like this:

// Since you're not executing the SQL, set connection to null
Connection connection = null;
Factory create = new MySQLFactory(connection);
String sql1 = create.select(A, B, C)
                    .from(MY_TABLE)
                    .where(A.equal(5))
                    .and(B.greaterThan(8))
                    .getSQL();

String sql2 = create.insertInto(MY_TABLE)
                    .values(A, 1)
                    .values(B, 2)
                    .getSQL();

String sql3 = create.update(MY_TABLE)
                    .set(A, 1)
                    .set(B, 2)
                    .where(C.greaterThan(5))
                    .getSQL();

The supported syntax is quite rich. You will also find support for clauses such as ON DUPLICATE KEY UPDATE, FOR UPDATE, LOCK IN SHARE MODE, etc.

For more details, see

http://www.jooq.org

(Disclaimer, I work for the company behind jOOQ)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

is there a way to use it without generating classes for my tables?
@wutzebaer: Check out the plain SQL related manual pages: jooq.org/doc/2.5/manual/sql-building/plain-sql
3

You should definitively take a look at SQLBuilder. It allows simple, yet complete, SQL generation using a very fluent API.

Improve this answer

10 Comments

Is SQLBuilder still active? There don't seem to be a lot of updates in recent history... sourceforge.net/projects/openhms/files/sqlbuilder
Does it work? I mean, I don't know, I've not used it. But if it works, who cares if it's active?
Well it depends, if the OP is looking for a tool for quick-and-dirty jobs, or if he needs to live with this decision for another while. Take Xalan, for instance. It works very well but it is not at all active. There are some important open issues that will probably never be addressed... :-/
@Lukas_Eder Are you talking about Xalan or SQLBuilder here ? I understand you want to defend JOOQ, as you're the code author (a thing I absolutely don't want to minimize), but I don't find very good policy to immediatly dismiss other frameworks on unclear issues (in other words, if you find SQLBuilder misses some important points, write them down or don't talk about them, but please don't say they have bugs, as I think you also have in JOOQ).
Haha, you French guys :-) so, now I understand. I was actually referring to Xalan. The latest release was in 2007. So I considered it inactive.
|
0

Going out on a limb here, have you considered iBatis? It's a real down to earth query mapping framework (I hesitate to call it an ORM framework somehow). You have to create XML files like this one:

<mapper namespace="org.mybatis.jpetstore.persistence.ProductMapper">    
  <cache />    
  <select id="getProduct" parameterType="string" resultType="Product">
    SELECT
      PRODUCTID,
      NAME,
      DESCN as description,
      CATEGORY as categoryId
    FROM PRODUCT
    WHERE PRODUCTID = #{productId}
  </select>   
</mapper>

which wires up a mapper like this one:

public interface ProductMapper {
  Product getProduct(String productId);
}

Which allows you to access data from services like this:

  @Autowired
  private ProductMapper productMapper;  

  public Product getProduct(String productId) {
    return productMapper.getProduct(productId);
  }

Which you can wire up with Spring:

<!-- enable autowire -->
<context:annotation-config />

<!-- enable transaction demarcation with annotations -->
<tx:annotation-driven />

<!-- define the SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="typeAliasesPackage" value="org.mybatis.jpetstore.domain" />
</bean>

<!-- scan for mappers and let them be autowired -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="org.mybatis.jpetstore.persistence" />
</bean>

See also the full petstore example.

I'm not an uniquivocal fan of iBatis but it might fit your needs in this specific case.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.