Good way to generate SQL strings in java?

Question

I am not looking for a persistence layer like Hibernate, I just want to generate sql strings and they should be compatible with PreparedStatement , I tried libraries like Squiggle but only supports SELECT , I would also like to generate insert and updates. An ideal use would be something like:

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

which will generate this line:

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

I know that there are questions that are very similar to mine, such as, but they do not ask the same thing as I do.

Greetings

Answer 1

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

 // 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 offers such as ON DUPLICATE KEY UPDATE , FOR UPDATE , LOCK IN SHARE MODE , etc.

See details

http://www.jooq.org

Answer 2

You should finally take a look at SQLBuilder . This allows a simple but complete generation of SQL using a very seamless API.

Answer 3

Stepping on a limb, did you consider iBatis? This is the real structure of the mapping of requests to the earth (I cannot call it the ORM structure in any way). You should create XML files like this:

 <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 connects such a mapper:

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

Allows you to access data from these services:

  @Autowired private ProductMapper productMapper; public Product getProduct(String productId) { return productMapper.getProduct(productId); } 

What you can connect 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 am not a uniquivocal fan of iBatis, but it can fit your needs in this particular case.