编写一个Groovy脚本以在PostgresDB中执行此命令

Question

我目前正在使用SOAPUI 4.0 Pro来访问特定主机上的Postgresdb。我已经修改了我的属性文件，以包括端口、用户名、密码和数据库名称。下面是我的问题：

SELECT 
  eis_entity.local_id, 
  eis_trait_instance_history.trait_value,
  eis_identifier_domain.identifier,
  eis_identifier_domain.label
FROM 
  public.eis_entity,
  public.eis_trait_instance_history,
  public.eis_version_label,
  public.eis_identifier_domain
WHERE eis_identifier_domain.eis_identifier_domain_key = eis_entity.eis_identifier_domain_key
  AND eis_entity.eis_entity_key=eis_version_label.eis_entity_key  
  AND eis_version_label.eis_version_label_key=eis_trait_instance_history.eis_version_label_key
  AND eis_trait_instance_history.trait_value='<name of patient>';

如何为上面的查询创建一个groovy脚本？

Answer 1

假设您能够掌握Groovy SQL基础知识并掌握sql实例的句柄……

sql = Sql.newInstance(...)
def List eisEntityList = sql.rows("""
    SELECT eis_entity.local_id,
           eis_trait_instance_history.trait_value,
           eis_identifier_domain.identifier,
           eis_identifier_domain.label
    FROM public.eis_entity,
         public.eis_trait_instance_history,
         public.eis_version_label,
         public.eis_identifier_domain
    WHERE eis_identifier_domain.eis_identifier_domain_key = eis_entity.eis_identifier_domain_key
          AND eis_entity.eis_entity_key = eis_version_label.eis_entity_key
          AND eis_version_label.eis_version_label_key = eis_trait_instance_history.eis_version_label_key
          AND eis_trait_instance_history.trait_value = ''
""")

if (eisEntityList?.size() > 0) {

    eisEntityList.each {
        // do something with each item...
    }
}