CODEWITHSUNDEEP
javasqldropwizardjdbi
sanjay patel
sanjay patel

Reputation: 449

Use custom where condition with JDBI dropwizard

I am trying to explore JDBI with dropwizard, my custome code is as below.

public interface UserDao {
@SqlQuery("SELECT FROM `t_user` :cond")
@Mapper(UserMapper.class)
List<User> fetch(@Bind("cond") String cond);
}

and trying to call with below code

Application.getJdbi().onDemand(UserDao.class).fetch("where logon_id="+p.getEmail()+"'");

and getting below issue

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `t_user` 'where [email protected]\''' at line 1

I am getting quotes issues as this is been passed as String. This way I am trying to achieve common where clause code for all the queries. My questions are 1. How to resolve this issue. 2. Is this is proper way to code like this, or we can use prepared statement. If yes then how.

Great Thanks in Advance :)

Upvotes: 2

Views: 3295

Answers (3)

bald2b
bald2b

Reputation: 189

You must use annotation @UseStringTemplate3StatementLocator for DAO interface. Then you can use "dynamic" sql with <arg> syntax:

@UseStringTemplate3StatementLocator
public interface UserDao {
   @SqlQuery("SELECT FROM `t_user` <cond>")
   @Mapper(UserMapper.class)
   List<User> fetch(@Bind("cond") String cond);
}

For working @UseStringTemplate3StatementLocator annotation project need antlr:stringtemplate maven dependency:

<dependency>
  <groupId>antlr</groupId>
  <artifactId>stringtemplate</artifactId>
  <version>3.0</version>
</dependency>

Upvotes: 1

jhsheets
jhsheets

Reputation: 520

I've written a Freemarker integration module for JDBI which can dynamically generate SQL, as opposed to the simple string replacement functionality JDBI provides using @Define.

https://github.com/jhsheets/jdbi-freemarker

Upvotes: 0

Manikandan
Manikandan

Reputation: 3165

Use Define instead of Bind for dynamic queries.

public interface UserDao {
 @SqlQuery("SELECT * FROM t_user :cond")
 @Mapper(UserMapper.class)
 List<User> fetch(@Define("cond") String cond);
}

Upvotes: 5

Related Questions