I wouldn't follow this approach at all, trying to create a dynamic SQL by hand using StringBuilder or StringBuffer.
It is inevitable that the complexity will shoot off the roof. And it is very rare to find a need for building SQL statements dynamically like that.
In 30 years, I haven't seen a justification. Every application I've known has a finite set of access patterns, ergo a finite set of SQL statements it needs.
Instead, I would suggest you use parameterized SQL statements (see example below from this tutorial)
String sql = "update people set firstname=? , lastname=? where id=?";
String sql = "update people set firstname=? , lastname=? where id=?";
PreparedStatement preparedStatement =
connection.prepareStatement(sql);
preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong (3, 123);
int rowsAffected = preparedStatement.executeUpdate();
Better yet, load that string from a resource bundle or property.
Other possibilities include using a DSL like jOOQ (example below, from the jOOQ's site):
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
.where(BOOK.LANGUAGE.eq("DE"))
.and(BOOK.PUBLISHED.gt(date("2008年01月01日")))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.having(count().gt卌)
.orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
.limit(2)
.offset(1)
Or QueryDSL (example below):
QCustomer customer = new QCustomer("c"); // alias for the CUSTOMER table
SQLTemplates dialect = new HSQLDBTemplates(); // SQL-dialect
SQLQuery query = new SQLQueryImpl(connection, dialect);
List<String> lastNames = query.from(customer)
.where(customer.firstName.eq("Bob"))
.list(customer.lastName);
Or, if you are using JPA, to use the JPA's Criteria API (example below):
Subquery<Department> subquery = criteriaQuery.subquery(Department.class);
Root<Department> dept = subquery.from(Department.class);
subquery.select(dept)
.distinct(true)
.where(criteriaBuilder.like(dept.get("name"), "%" + searchKey + "%"));
criteriaQuery.select(emp)
.where(criteriaBuilder.in(emp.get("department")).value(subquery));
Good luck.
I wouldn't follow this approach at all, trying to create a dynamic SQL by hand using StringBuilder or StringBuffer.
It is inevitable that the complexity will shoot off the roof. And it is very rare to find a need for building SQL statements dynamically like that.
In 30 years, I haven't seen a justification. Every application I've known has a finite set of access patterns, ergo a finite set of SQL statements it needs.
Instead, I would suggest you use parameterized SQL statements (see example below from this tutorial)
String sql = "update people set firstname=? , lastname=? where id=?";
PreparedStatement preparedStatement =
connection.prepareStatement(sql);
preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong (3, 123);
int rowsAffected = preparedStatement.executeUpdate();
Better yet, load that string from a resource bundle or property.
Other possibilities include using a DSL like jOOQ (example below, from the jOOQ's site):
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
.where(BOOK.LANGUAGE.eq("DE"))
.and(BOOK.PUBLISHED.gt(date("2008年01月01日")))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.having(count().gt卌)
.orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
.limit(2)
.offset(1)
Or QueryDSL (example below):
QCustomer customer = new QCustomer("c"); // alias for the CUSTOMER table
SQLTemplates dialect = new HSQLDBTemplates(); // SQL-dialect
SQLQuery query = new SQLQueryImpl(connection, dialect);
List<String> lastNames = query.from(customer)
.where(customer.firstName.eq("Bob"))
.list(customer.lastName);
Or, if you are using JPA, to use the JPA's Criteria API (example below):
Subquery<Department> subquery = criteriaQuery.subquery(Department.class);
Root<Department> dept = subquery.from(Department.class);
subquery.select(dept)
.distinct(true)
.where(criteriaBuilder.like(dept.get("name"), "%" + searchKey + "%"));
criteriaQuery.select(emp)
.where(criteriaBuilder.in(emp.get("department")).value(subquery));
Good luck.
I wouldn't follow this approach at all, trying to create a dynamic SQL by hand using StringBuilder or StringBuffer.
It is inevitable that the complexity will shoot off the roof. And it is very rare to find a need for building SQL statements dynamically like that.
In 30 years, I haven't seen a justification. Every application I've known has a finite set of access patterns, ergo a finite set of SQL statements it needs.
Instead, I would suggest you use parameterized SQL statements (see example below from this tutorial)
String sql = "update people set firstname=? , lastname=? where id=?";
PreparedStatement preparedStatement =
connection.prepareStatement(sql);
preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong (3, 123);
int rowsAffected = preparedStatement.executeUpdate();
Better yet, load that string from a resource bundle or property.
Other possibilities include using a DSL like jOOQ (example below, from the jOOQ's site):
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
.where(BOOK.LANGUAGE.eq("DE"))
.and(BOOK.PUBLISHED.gt(date("2008年01月01日")))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.having(count().gt卌)
.orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
.limit(2)
.offset(1)
Or QueryDSL (example below):
QCustomer customer = new QCustomer("c"); // alias for the CUSTOMER table
SQLTemplates dialect = new HSQLDBTemplates(); // SQL-dialect
SQLQuery query = new SQLQueryImpl(connection, dialect);
List<String> lastNames = query.from(customer)
.where(customer.firstName.eq("Bob"))
.list(customer.lastName);
Or, if you are using JPA, to use the JPA's Criteria API (example below):
Subquery<Department> subquery = criteriaQuery.subquery(Department.class);
Root<Department> dept = subquery.from(Department.class);
subquery.select(dept)
.distinct(true)
.where(criteriaBuilder.like(dept.get("name"), "%" + searchKey + "%"));
criteriaQuery.select(emp)
.where(criteriaBuilder.in(emp.get("department")).value(subquery));
Good luck.
I wouldn't follow this approach at all, trying to create a dynamic SQL by hand using StringBuilder or StringBuffer.
It is inevitable that the complexity will shoot off the roof. And it is very rare to find a need for building SQL statements dynamically like that.
In 30 years, I haven't seen a justification. Every application I've known has a finite set of access patterns, ergo a finite set of SQL statements it needs.
Instead, I would suggest you use parameterized SQL statements (see example below from this tutorial)
String sql = "update people set firstname=? , lastname=? where id=?";
PreparedStatement preparedStatement =
connection.prepareStatement(sql);
preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong (3, 123);
int rowsAffected = preparedStatement.executeUpdate();
Better yet, load that string from a resource bundle or property.
Other possibilities include using a DSL like jOOQ (example below, from the jOOQ's site):
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
.where(BOOK.LANGUAGE.eq("DE"))
.and(BOOK.PUBLISHED.gt(date("2008年01月01日")))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.having(count().gt卌)
.orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
.limit(2)
.offset(1)
Or QueryDSL (example below):
QCustomer customer = new QCustomer("c"); // alias for the CUSTOMER table
SQLTemplates dialect = new HSQLDBTemplates(); // SQL-dialect
SQLQuery query = new SQLQueryImpl(connection, dialect);
List<String> lastNames = query.from(customer)
.where(customer.firstName.eq("Bob"))
.list(customer.lastName);
Or, if you are using JPA, to use the JPA's Criteria API (example below):
Subquery<Department> subquery = criteriaQuery.subquery(Department.class);
Root<Department> dept = subquery.from(Department.class);
subquery.select(dept)
.distinct(true)
.where(criteriaBuilder.like(dept.get("name"), "%" + searchKey + "%"));
criteriaQuery.select(emp)
.where(criteriaBuilder.in(emp.get("department")).value(subquery));
Good luck.