Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 nut自定义sql打印问题
发布于 2707天前 作者 shudaixiong001 3038 次浏览 复制 上一个帖子 下一个帖子
标签: 无

最近用nutz做postgresql的后台框架，执行复杂的sql，但是sql在执行execute后不再执行，控制台不报错；手动将@参数值补全，放到pg中可以执行；
请问，如何可以打印最终提交到数据库的带参数值的sql？如果不能打印，请问如何让控制台报出sql错误？

11 回复

上代码

public List<Map> searchCounties(@Param("geometry")String geometry,@Param("table")String table,@Param("type")String type,@Param("areal")String areal,@Param("areah")String areah){
		System.out.println("*************");
		String sqlstr="with a as ("
				+"select st_transform(st_geomfromtext(@geometry,4326),32650) shape" 
				+")"
				+"select t.name,st_area(st_transform(t.shape,32650)) tarea,st_astext(st_transform(t.shape,4326)) tshape  from gisdb.$table t,a where st_intersects(t.shape,a.shape) and t.name like '%@type%' and st_area(st_transform(t.shape,32650)) between @areal and @areah"
				+" ";
		//Sql sql=Sqls.queryRecord("select st_area(st_transform(t.shape,32650)),* from gisdb.urbanplan t where ST_Intersects(t.shape,st_transform(st_geomfromtext(@geometry,4326),4490)) ");
		Sql sql=Sqls.queryRecord(sqlstr);
		List<Map> lists=new ArrayList<>();
		sql.vars().set("table",table);
		sql.params().set("geometry", geometry);
		sql.params().set("areal", areal);
		sql.params().set("areah", areah);

		dao.execute(sql);
		List<Record> list=sql.getList(Record.class);
		Map listMap=new HashMap<>();
		listMap.put("type",table);
		listMap.put("data",list);
		lists.add(listMap);
		return lists;

	}

execute之前加一句:

sql.forceExecQuery();
dao.execute(sql);

另外，日志里面没有打印sql吗？日志没配好？

还是原来的错误，请问可以打印最终提交到数据库的带参数值的sql吗，这个应该是sql错误

日志里面就有sql啊，贴日志看看

因为是预处理sql，不存在"真正执行的sql”，只有预处理sql加参数矩阵

设置参数后，可以打印sql.toString()，是示意sql语句

好的，麻烦版主了，我再调试一下吧，入门有点着急了

贴日志有多难？

好吧，贴一个，不知道是不是特简单的错误，本想自己看看解决的

2017-07-25 11:30:37,481 [http-nio-8081-exec-1] WARN  org.nutz.mvc.impl.processor.FailProcessor - Error@/user/searchCounties :
org.nutz.dao.DaoException: !Nutz SQL Error: 'with a as (select st_transform(st_geomfromtext('polygon((115.72544264984131 40.08076200073242,115.88611769866944 40.082135291748045,115.88131118011475 39.95510587280273,115.73093581390381 39.96471890991211,115.72544264984131 40.08076200073242))',4326),32650) shape)select t.name,st_area(st_transform(t.shape,32650)) tarea,st_astext(st_transform(t.shape,4326)) tshape  from gisdb.controlplan t,a where st_intersects(t.shape,a.shape) and  st_area(st_transform(t.shape,32650)) between '0' and '55555555''
PreparedStatement: 
'with a as (select st_transform(st_geomfromtext(?,4326),32650) shape)select t.name,st_area(st_transform(t.shape,32650)) tarea,st_astext(st_transform(t.shape,4326)) tshape  from gisdb.controlplan t,a where st_intersects(t.shape,a.shape) and  st_area(st_transform(t.shape,32650)) between ? and ?'
CaseMessage=ERROR: operator does not exist: double precision >= character varying
  建议：No operator matches the given name and argument type(s). You might need to add explicit type casts.
  位置：279
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:102)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:158)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithTransaction(NutDaoRunner.java:104)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:88)
	at org.nutz.dao.impl.sql.run.NutDaoRunner$1.run(NutDaoRunner.java:74)
	at org.nutz.trans.Trans.exec(Trans.java:174)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:72)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:245)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:257)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:241)
	at org.nutz.dao.impl.NutDao.execute(NutDao.java:1008)
	at net.wendal.nutzbook.module.UserModule.searchCounties(UserModule.java:131)
	at net.wendal.nutzbook.module.UserModule$FM$searchCounties$058e245231db2793ad48b2d9f18f5922.invoke(UserModule.java)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:202)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: double precision >= character varying
  建议：No operator matches the given name and argument type(s). You might need to add explicit type casts.
  位置：279
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2125)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:272)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:53)
	... 50 more

type参数没赋值，而且在sql里面定义得不对

t.name like @type

其他字段的类型啥？geo？

测试通过了，是因为between and 的值带引号的数字在pg中可以运行，在程序里面判断为类型错误
double precision >= character varying

添加回复
请先登陆
回到顶部