反馈已提交

网络繁忙

数据库查询

  • 文档创建者:天玑科技-叶然
  • 历史版本:47
  • 最近更新:Suki陈 于 2023-06-09
  • 1. 概述

    1.1 版本

    版本
    功能变动
    11.0
    -
    11.0.5选择 Postgre 系列数据库连接时,只能添加连接时所选模式下的数据表
    11.0.16支持在数据库日志的 SQL 语句前添加注释,帮助排查性能问题

    1.2 功能介绍

    数据库查询是指从定义好的数据库连接中,也就是数据源中使用 SQL 语句来选择需要的字段,一般是直接对数据源中的数据库表直接进行操作。

    2. 查询方式

    数据库查询方式可分为:表查询、视图查询、模糊查询三种。

    2.1 表查询

    2.1.1 查询步骤

    1)点击「模板>模板数据集」,弹出模板数据集对话框,点击模板数据集窗口的左上角的按钮,选择「数据库查询」,然后给数据库查询命名,选择所需的数据连接,如下图所示:

    1576807345689546.png

    2)选中查询编辑器,在其中输入 SQL 语句SELECT * FROM ORDERS where ORDERID=${ID}

    这是一个带参数的 SQL 查询,${}为参数的形式,中间的 ID 即参数的名称,点击「预览」,跳出提示框,输入参数 ID=10004 ,结果可以在预览组中看,实际就是执行SELECT * FROM ORDERS where ORDERID=10004的 SQL 查询。如下图所示:

    1576807722411821.png

    注:一般的 SQL 语句通用,只是在设置参数时需要参照以上的形式。

    注:若参数名称是字母和数字组合(例如:A1)则会被系统认为是单元格,如果不设置默认值会触发模板计算,从而导致执行一次 SQL。所以用户需要避免使用字母和数字组合的参数名称,若要用则必须给它设置默认值。

    2.1.2 数据集界面的基本属性

    下面介绍整个数据集界面的一些基本属性,如下表所示:

    类型

    对于查询类型,FineReport 既支持普通的 SQL 查询,也支持存储过程,并且这两种类型的查询语句中都可以包含参数。 

    注:  SQL 查询和存储过程,通过 JDBC最终都是由数据库软件来解析执行,并将结果返回给 FineReport 来显示,所以查询执行的速度和性能完全由对应的数据库软件来决定。

    向导

    FineReport 提供了简单的向导来帮助用户创建简单的 SQL 查询和存储过程。  

    注:由于 SQL 语言比较复杂,FineReport 提供的向导只能定义常用的简单 SQL 查询(不支持分组,子查询等)。

    存储过程的向导基本可以满足要求,能够列出所连接数据库中所有的存储过程,并根据所选择存储过程的名字,自动获得存储过程的参数来产生可以执行的存储过程语句。 

    查询编辑器

    当向导产生的查询不能完全满足要求的时候,可以通过我们提供的文本编辑器来编辑任意复杂的查询语句(利用向导产生的存储过程,一般不需要再次编辑)。

    参数

    FineReport 可以完美地支持参数查询,在定义查询语句的时候,只需要用特殊字符串 "${XXX}" (参数值为数值型,为${参数},否则为 '${XXX}') ,将参数名字前后包含起来,点击下面的刷新按钮,可以给参数赋默认值。这样当报表引擎执行这个 SQL 语句时,在 C/S 系统会弹出一个对话框来要求用户输入对应参数的数值,并且会自动显示默认值,在 B/S 系统会遍历客户端(通常是浏览器)传递过来的所有参数数值; FineReport 利用这些参数的数值来替换查询中对应的字符串“${XXX}”,将替换过的查询语句提交给数据库解析和执行。  

    比如: 1. 用户输入的查询语句为: select * from Customers where ID = ${ID};  

    2. 从客户端动态取到的 ID 数值为: 1; 

    3. 根据参数值产生新的查询语句:select * from Customers  where ID = 1,利用 JDBC 将这个新产生的查询语句传递给相应的数据库解析和执行。

    最大预览数

    为了加快预览的速度,设置了预览的最大行数(默认值 200),这个数值只有在点击「预览」按钮的时候才起作用,不会错误地作用到引用这个查询的报表模板文件中。

    2.2 视图查询

    视图查询是指对定义好的数据源中的视图进行操作。

    2.2.1 数据库查询

    1)点击菜单「模板>模板数据集」,弹出模板数据集对话框,点击模板数据集窗口的左上角的按钮,选择「数据库查询」,然后给数据库查询命名,即可添加视图查询。如下图所示:

    1576807345689546.png

    2.2.2 使用视图查询

    选择数据源使用 SQL 对视图查询进行操作。下文以 SQL Server 视图数据库为例:

    在查询编辑器中输入 SQL 语句:SELECT * FROM [sys].[types] 其中 [sys].[types] 是某个视图查询的名字,然后点击预览即可看到视图查询引用成功。如下图所示:

    1576808418856458.png

    注:可以将数据连接中的表、视图直接拖动到查询编辑器中。

    2.3 模糊查询

    在表下方有一块空白搜索框,它们用作模糊查询。例如想要搜索客户相关的表,在搜索框中填入客户二字,则会进行自动搜索进行模糊匹配,如下图所示:

    1576808722921608.png

    注1:FineReport 数据库查询只支持一条自然 SQL 语句的查询,不支持多个自然 SQL 语句,比如 SELECT * FROM A SELECT * FROM B

    注2:create 、drop 等新建表、删除表不在官方支持范围内,官方只支持 select 。

    3. 复制 SQL 语句

    在之前版本中,若 SQL 中含有参数,就不方便直接从查询界面将 SQL 语句复制到其他地方。

    10.0.17 及之后的版本,新增「查看执行 SQL」功能,方便用户粘贴其他数据库软件进行排查测试。

    3.1 示例

    点击查询页面上方的「查看执行 SQL」按钮,由于 SQL 语句中包含参数,会自动弹出参数输入界面。如下图所示:

    1.png

    执行的 SQL 语句自动复制到剪贴板,用户可将其粘贴到其他数据库软件进行排查测试。如下图所示:

    2.png

    3.2 注意事项

    1)若SQL中调用了自定义函数,则无法正常使用「查看执行SQL」功能。复制的SQL语句在其他数据库软件无法正常运行。

    2)某些用户为了 SQL 防注入 ,开启了转义功能。当参数与转义字符匹配时,会影响查询结果在这种情况下会提示:转义部分参数防止SQL注入,查询结果与预期可能不符,请检查并修改参数或联系管理员     

    鼠标触碰右上角提示图标,可以看到已转义的参数。在本示例中,输入的参数值 1002 是被转义的参数。

    6.png

    4. 性能排查

    用户在进行数据库查询,即使用 SQL 语句从定义好的数据库连接中取数时,相关 SQL 语句会被记录在数据库日志中。

    当某张模板出现取数慢、卡顿等问题时,难以在数据库日志的大量 SQL 语句中定位到该模板相关的 SQL 语句。

    11.0.16 及之后版本,支持在数据库日志中的 SQL 语句前添加注释,便于用户快速定位「模板」/「数据集」/「任务」相关 SQL 语句,帮助排查数据库性能问题。

    注:不支持在 fanruan.log 中的 SQL 语句前添加注释。

    4.1 SQL注释内容

    SQL 查询场景及对应的注释内容如下表所示:

    注1:注释中的「模板名称」/「数据集名称」/「任务名称」为 base64 加密后的密文,用户可自行使用解码工具进行解码查看。

    注2:存储过程不支持显示 SQL 注释。

    SQL查询场景日志格式效果示例
    设计器中预览数据集

    -- DesignerDatasetPreview_[数据集名称]_[正在编辑的模板名]

    SQL语句

    数据决策系统中预览服务器数据集

    -- DatasetPreview_[数据集名称]

    SQL语句

    预览模板

    -- TemplatePreviewFetch_[模板名称]

    SQL语句

    抽数缓存

    -- ESDFetch_[模板名称]

    SQL语句

    定时调度

    -- ScheduleFetch_[模板名称]_[任务名称]

    SQL语句

    4.2 数据库日志查询

    4.2.1 MySQL

    MySQL 服务器执行的查询语句会记录在通用查询日志(General Query Log)中,默认情况下,通用查询日志功能是关闭的。

    使用以下命令查看通用查询日志是否开启,其中,「general_log_file」变量指定了日志文件所在的位置。如下图所示:

    mysql>SHOW VARIABLES LIKE '%general%';


    若通用查询日志功能为关闭状态,可以通过修改 MySQL 的 my.cnf 配置文件来开启通用查询日志功能。

    general_log_file = /var/log/mysql/mysql.log    #general_log_file的路径为日志的输出路径
    general_log = 1    #将general_log设置为1,表示开启查询日志

    修改完成后,保存配置文件,并重启 MySQL 即可。

    4.2.2 Oracle

    v$sql 记录共享池缓存中用户执行过的 SQL 信息,可以通过 SQL 语句查询出具体 SQL 以及操作的时间,如下图所示:

    注:若查询不到已执行的 SQL,可能是执行的 SQL 已经被置换出共享 SQL 区。

    SELECT SQL_TEXT,FIRST_LOAD_TIME
    FROM v$sql

    4.2.3 SQL SERVER

    sys.dm_exec_query_stats 记录 SQL Server 缓存中的查询语句,可以通过 SQL 语句查询出具体 SQL 以及操作的时间,如下图所示:

    注:sys.dm_exec_query_stats 仅记录已完成的查询数据。

    SELECT TOP 1000
    --创建时间
    QS.creation_time,
    --查询语句
    SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
    ((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
    ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
    ) AS statement_text,
    --执行文本
    ST.text,
    --执行计划
    QS.total_worker_time,
    QS.last_worker_time,
    QS.max_worker_time,
    QS.min_worker_time
    FROM
    sys.dm_exec_query_stats QS
    --关键字
    CROSS APPLY
    sys.dm_exec_sql_text(QS.sql_handle) ST
    WHERE
    QS.creation_time BETWEEN '2011-10-20 16:00:00' AND '2023-10-20 17:00:00'
    AND ST.text LIKE '%%'
    ORDER BY
    QS.creation_time DESC

    附件列表


    主题: 数据准备
    • 有帮助
    • 没帮助
    • 只是浏览
    中文(简体)

    鼠标选中内容,快速反馈问题

    鼠标选中存在疑惑的内容,即可快速反馈问题,我们将会跟进处理。

    不再提示

    10s后关闭

    联系我们
    在线支持
    获取专业技术支持,快速帮助您解决问题
    工作日9:00-12:00,13:30-17:30在线
    页面反馈
    针对当前网页的建议、问题反馈
    售前咨询
    采购需求/获取报价/预约演示
    或拨打: 400-811-8890 转1
    qr
    热线电话
    咨询/故障救援热线:400-811-8890转2
    总裁办24H投诉:17312781526
    提交页面反馈
    仅适用于当前网页的意见收集,帆软产品问题请在 问答板块提问前往服务平台 获取技术支持