How to build a little tricky with dynamic SQL query generation

background

I was reviewing the code of a newcomer (PG history about 3 weeks?), And I was generating a slightly interesting SQL query, so share the story. I'm talking about using some kind of OR mapper or Doma, but leave that out.

Premise

What I want to do is that there are multiple input boxes on the search screen, and the items entered by the user are added to the SQL search conditions.

Unless you're using an Active Record-like OR mapper, you're not using a 2-way-SQL library like Doma.

If you feel like it, you may cut out SQL to an external file, but I think that the usual idea for beginners is to dynamically generate strings and build SQL.

Therefore, you would normally generate a SQL query with string concatenation in your code.

** This schema: ** http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05780-02/scripts.htm#Cihgfecd

Example: A query when you want to search for "Sales are included in the job category" and "Salary is 6 million yen or more" from the employee table. Let me omit the escape setting in the Like clause.

SQL you want to generate

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || ? || '%';     --I want to add it to the condition if specified by the user
AND jb.SALARY >= ?                         --I want to add it to the condition if specified by the user
;

Usually, the presence or absence of string concatenation is often branched from the presence or absence of a value. Aside from using StringBuilder, I don't know.

python

String sql = ~ ; //abridgement. SQL assumptions up to the first line of the WHERE clause.

if (jobTitle != null && jobTitle.length >= 0) {
  sql += "AND jb.JOB_TITLE LIKE '%' || ? || '%'"
}

if (minSalary != null && minSalaly.length >= 0) {
  sql += "AND jb.SALARY >= ?"
}

However, a newcomer was doing this.

jobTitleComment = "";
minSalaryComment = "";
if (jobTitle != null && jobTitle.length >= 0) {
 jobTitleComment = "--";
}

if (minSalary != null && minSalaly.length >= 0) {
 minSalaryComment = "--";
}

String sql = "SELECT"
+ " em.EMPLOYEE_ID"
+ ", em.FIRST_NAME"
+ ", em.LAST_NAME"
+ "FROM"
+ "  EMPLOYEES em"
+ ", INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)"
+ ", INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)"
+ "WHERE 1 = 1"
+ "AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE"
+ jobTitleComment + "AND jb.JOB_TITLE LIKE '%' || ? || '%'"  //point
+ minSalaryComment + "AND jb.SALARY >= ?"                     //point
;

I couldn't understand the intention when I looked at Pat, and it got stuck for a few seconds, but when I looked closely, I couldn't understand it. I'm trying to switch whether to use SQL search conditions with "-" indicating a comment.

For example, an example of SQL generation after execution when neither condition is set is as follows.

When the search condition is not set

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
-- AND jb.JOB_TITLE LIKE '%' || ? || '%'  /*★ Both will be commented out*/
-- AND jb.SALARY >= ?　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　/*★ Both will be commented out*/
;

Since the search condition is empty, the refinement condition of the Where clause is commented out and invalidated.

Next is an example of generation when only the job name is set.

When only the job name is set

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || 'sales' || '%'  /*This is valid*/
-- AND jb.SALARY >= '??'                      /*This remains commented out*/
;

If the search condition exists, the corresponding refinement condition in the Where clause is valid. I thought it was surprisingly easy to see.

Summary

• It may be interesting to have 2-way-SQL that is the output result like this.
• But it feels like you can use Doma.