java - SQL 查询生成器实用程序

Question

我有一组列和表，在各自的下拉列表中，我正在编写代码以根据表列选择生成动态 SQL

它在简单 Select 语句的情况下有效，但在多重连接的情况下，我试图找出处理右连接和左连接的语法。

请帮助..这是 SQL 语法的错误

1)
(Select dbo.Employee.Dept_ID,dbo.Employee.Emp_ID,dbo.Employee.Emp_Name,dbo.Employee_DataVal.DeptNo,
dbo.Employee_DataVal.EmpName,dbo.Employee_DataVal.EmpNo,dbo.Employee_DataVal.Salary,dbo.Emp_Sal.Emp_ID,dbo.Emp_Sal.Salary
FROM Employee
INNER JOIN Employee_DataVal
ON Employee.Dept_ID = Employee_DataVal.DeptNo
OR Employee_DataVal.EmpName = Employee.Emp_Name)
LEFT JOIN Emp_Sal
ON Employee.Emp_ID = Emp_Sal.Emp_ID

Incorrect syntax near the keyword 'LEFT'.

2)Select dbo.Employee.Dept_ID,dbo.Employee.Emp_ID,
dbo.Employee.Emp_Name,dbo.Employee_DataVal.DeptNo,
dbo.Employee_DataVal.EmpName,dbo.Employee_DataVal.EmpNo
,dbo.Emp_Sal.Emp_ID,dbo.Emp_Sal.Salary
FROM Employee INNER JOIN Employee_DataVal
ON Employee.Emp_ID = Employee_DataVal.EmpNo
AND Employee.Dept_ID = Employee_DataVal.DeptNo
LEFT JOIN Employee
ON Employee_DataVal.EmpName = Employee.Emp_Name

The objects "Employee" and "Employee" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

PS: 在 SQL Server 上运行此 sql

Answer 1

这是在处理基于字符串的复杂动态 SQL 字符串时的常见问题 - 正确处理字符串形式的 SQL 语法很困难，而且很容易产生 SQL 注入(inject)漏洞。

SQL 构建器 API，如 jOOQ和 others非常适合这项任务。我不确定您的情况到底是什么问题，但我们假设最后一个 LEFT JOIN 在您的查询中是可选的。您可以编写这样的查询:

List<Field<?>> c = new ArrayList<>(Arrays.asList(
    EMPLOYEE.DEPT_ID,
    EMPLOYEE.EMP_ID,
    EMPLOYEE.EMP_NAME,
    EMPLOYEE_DATAVAL.DEPTNO,
    EMPLOYEE_DATAVAL.EMPNAME,
    EMPLOYEE_DATAVAL.EMPNO,
    EMPLOYEE_DATAVAL.SALARY
));
Table<?> t = EMPLOYEE
   .join(EMPLOYEE_DATAVAL)
     .on(EMPLOYEE.DEPT_ID.eq(EMPLOYEE_DATAVAL.DEPTNO)
     .or(EMPLOYEE_DATAVAL.EMPNAME.eq(EMPLOYEE.EMP_NAME));

if (someCondition) {
    t = t.leftJoin(EMP_SAL).on(EMPLOYEE.EMP_ID.eq(EMP_SAL.EMP_ID));
    c.addAll(Arrays.asList(
        EMP_SAL.EMP_ID,
        EMP_SAL.SALARY
    ));
}

Result<?> result =
ctx.select(c)
   .from(t)
   .fetch();