发表新帖
发表新帖

web全自动配置数据权限sql报错

Blade 已结
关注 举报
1 682
861869769
861869769 剑圣 2021-03-29 09:22

一、该问题的重现步骤是什么？

1. 页面配置数据权限image.png

2. image.png


3.给人员配置此角色，查询列表报错


二、你期待的结果是什么？实际看到的又是什么？

能展示完整列表，实际看到报错。image.png

三、你正在使用的是什么产品，什么版本？在什么操作系统上？

saber，bladex-boot    windows    数据库sqlserver

四、请提供详细的错误堆栈信息，这很重要。

image.png


org.springframework.jdbc.BadSqlGrammarException: 

### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'from' 附近有语法错误。

### The error may exist in file [D:\workspace\blade\bladex-boot\target\classes\org\springblade\modules\roster\mapper\RosterCertMapper.xml]

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: WITH selectTemp AS (SELECT TOP 100 PERCENT  ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, t * from (SELECT * FROM nx_roster_cert WHERE is_deleted = 0) scope where scope.create_user in (1374711535960338434)) SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 10 ORDER BY __row_number__

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'from' 附近有语法错误。

; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'from' 附近有语法错误。

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)

at com.sun.proxy.$Proxy136.selectList(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)

at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:173)

at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)

at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)

at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)

at com.sun.proxy.$Proxy233.selectRosterCertPage(Unknown Source)

at org.springblade.modules.roster.service.impl.RosterCertServiceImpl.selectRosterCertPage(RosterCertServiceImpl.java:22)

at org.springblade.modules.roster.service.impl.RosterCertServiceImpl$$FastClassBySpringCGLIB$$1.invoke(<generated>)

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:119)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)

at org.springblade.modules.roster.service.impl.RosterCertServiceImpl$$EnhancerBySpringCGLIB$$1.selectRosterCertPage(<generated>)

at org.springblade.modules.roster.controller.RosterCertController.page(RosterCertController.java:65)

at org.springblade.modules.roster.controller.RosterCertController$$FastClassBySpringCGLIB$$1.invoke(<generated>)

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)

at org.springblade.core.log.aspect.RequestLogAspect.aroundApi(RequestLogAspect.java:112)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)

at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)

at org.springblade.modules.roster.controller.RosterCertController$$EnhancerBySpringCGLIB$$1.page(<generated>)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:497)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:584)

at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)

at org.springblade.core.log.filter.LogTraceFilter.doFilter(LogTraceFilter.java:39)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springblade.core.boot.request.BladeRequestFilter.doFilter(BladeRequestFilter.java:50)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)

at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)

at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)

at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)

at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)

at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)

at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)

at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:111)

at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)

at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)

at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)

at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)

at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)

at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)

at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)

at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)

at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)

at io.undertow.servlet.handlers.ServletInitialHandler.jrHandle(ServletInitialHandler.java:40001)

at org.zeroturnaround.javarebel.integration.servlet.undertow.cbp.ServletInitialHandlerCBP.handleRequest(ServletInitialHandlerCBP.java:131)

at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)

at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)

at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)

at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)

at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)

at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)

at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)

at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)

at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)

at io.undertow.server.Connectors.executeRootHandler(Connectors.java:376)

at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'from' 附近有语法错误。

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:505)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)

at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)

at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)

at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)

at com.sun.proxy.$Proxy296.query(Unknown Source)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)

at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)

at com.sun.proxy.$Proxy295.query(Unknown Source)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)

... 111 common frames omitted

五、若有更多详细信息，请在下面提供。



生成sql:

WITH selectTemp AS (

SELECT TOP

100 PERCENT ROW_NUMBER ( ) OVER ( ORDER BY CURRENT_TIMESTAMP ) AS __row_number__,

t * 

FROM

( SELECT * FROM nx_roster_cert WHERE is_deleted = 0 ) scope 

WHERE

scope.create_user IN ( 1374711535960338434 ) 

) SELECT

* 

FROM

selectTemp 

WHERE

__row_number__ BETWEEN 1 

AND 10 

ORDER BY

__row_number__


1条回答
  • admin
    admin (最佳回答者)
    2021-03-29 10:27

    image.png这个类的实现是什么，你需要贴出来才可以帮你判断。另外数据权限只是加了这么一段

    WHERE scope.create_user IN ( 1374711535960338434 )

    具体的你得写一个简单可重现的demo给我们，才可以帮忙排查，把最简化的代码以及sql压缩后邮件发到bladejava@qq.com

    作者追问:2021-03-29 10:27

    我们验证了下，mysql数据库是没问题的，sqlserver数据库就存在这个问题，那个方法就是一个简单的分页，

    select a.*,b.real_name,b.account,c.dept_name from nx_roster_edu a
    left join blade_user b on a.owner_user = b.id
    left join blade_dept c on a.owner_dept = c.id
    where a.is_deleted = 0
    and (a.owner_dept = #{vo.ownerDept}
        or (CHARINDEX(concat(#{vo.ownerDept},''),c.ancestors) > 0))
    and b.real_name LIKE concat('%',#{vo.realName},'%')
    and b.account = #{vo.account}

    源码等下发给您。

    回答: 2021-03-29 10:27

    已经解决，问题在于MybatisPlus的 com.baomidou.mybatisplus.extension.plugins.pagination.dialects.SQLServer2005Dialect

    没有同时加上trimimage.png

    解决办法有两种：

    一、修改BladeX-Tool的org.springblade.core.datascope.handler.BladeDataScopeHandler,最后的return StringUtil.format(" select {} from ({}) scope.....）改为return StringUtil.format("select {} from ({}) scope ".....) 修改完毕后在BladeX-Tool根目录执行mvn clean install，再运行工程就可以了。

    二、拷贝 com.baomidou.mybatisplus.extension.plugins.pagination.dialects.SQLServer2005Dialect 创建一个新的类。将上图的String sqlPartString = originalSql改为String sqlPartString = originalSql.trim() 最后重新定义分页的类即可

    image.png

    1 讨论(1)
 看不清?
提交回复
官方产品
BladeX 企业级开发平台 BladeX 可视化数据大屏
官方新品
BladeX 物联网开发平台
热议问题
Property "vaildData" was accessed during 前端报错 2
node-red里数据读取不完整 2
OSS如何设置文件上传的大小 1
blade-swagger 自动服务发现 1
feign远程调用空对象问题 1
如何增加配置文件 spring.profiles.active支持 1
swagger 无法关闭 1
在租户的账号下登录 blade_user表未实现租户隔离（在tool中已经删除排除） 1
新建租户问题 1
nacos 服务注册不上 1
layui

扫一扫访问 Blade技术社区 移动端

Blade技术社区 2024 © 上海布雷德科技有限公司 沪ICP备2023009528号-1 苏公网安备 32041102000998号