数据源动态传参

应用场景

复杂SQL查询语句，且有外部变量传递过滤

相关知识

数据源、自定义SQL数据源、参数传递

操作步骤

1.创建自定义SQL数据源编写SQL。

2.如何获取外部参数。参数格式为 ${vjspMap.userId:VARCHAR}

vjspMap为request提交的所有参数集合.您可以直接获取指定参数的数值。

VARCHAR为数据库字段与java类型转换的JDBCTYPE。

范例代码如下：

SELECT
  L.LM_NAME AS CNAME,
  L.LM_EMAIL AS EMAIL,
  L.LM_TEL AS TEL,
  TO_CHAR(L.LM_MARK) AS MARK,
  1 AS LINKTYPE,
  L.LM_ZW AS ZW,
  L.LM_ID AS USERID,
  L.LM_GSID AS CLIENTID,
  A.AS_NAME AS CLIENTNAME,
  G.LMG_NAME AS BM,
  M.LMG_ID AS GROUPID,
  L.LM_SEX,
  '' AS USERHEADPIC,
  '' AS OFID
FROM
  VJSP_LINKMAN L
  LEFT JOIN VJSP_ASSET_INFO A ON L.LM_GSID = A.AS_ID
  LEFT JOIN VJSP_DEPT D ON L.LM_BMID = D.DEPT_ID
  LEFT JOIN VJSP_LINKMANGROUP_MX M ON L.LM_ID = M.LM_ID
  LEFT JOIN VJSP_LINKMANGROUP G ON M.LMG_ID = G.LMG_ID
WHERE
  L.LM_CUSERID = '${vjspMap.userId:VARCHAR}'
UNION ALL
SELECT
  U.USER_NAME AS CNAME,
  U.USER_EMAIL AS EMAIL,
  U.USER_PHONE AS TEL,
  '' AS MARK,
  2 AS LINKTYPE,
  U.USER_ZW AS ZW,
  U.USER_ID AS USERID,
  U.USER_GSID AS CLIENTID,
  A1.AS_NAME AS CLIENTNAME,
  D1.DEPT_NAME AS BM,
  U.USER_BM_ID AS GROUPID,
  U.USER_SEX,
  '' AS USERHEADPIC,
  U.TS_SSO_USER_INFO_ID AS OFID
FROM
  VJSP_USER U
  LEFT JOIN VJSP_ASSET_INFO A1 ON U.USER_GSID = A1.AS_ID
  LEFT JOIN VJSP_DEPT D1 ON U.USER_BM_ID = D1.DEPT_ID
WHERE
  U.USER_GSID IN (
    SELECT
      *
    FROM
      TABLE(F_UGSNMS('${vjspMap.userId:VARCHAR}'))
  )