• 关于

    callablestatement 存储过程 返回结果

    的搜索结果

问题

JAVA 调用存储过程出错。提示:ORA-00917: missing comm?400报错

JAVA 调用存储过程出错。提示:ORA-00917: missing comma(丢失逗号)? 400 报错 //我的JAVA调用代码为: Connection conn = UtilDB.getIn...
爱吃鱼的程序员 2020-05-30 23:46:26 0 浏览量 回答数 1

回答

我想知道是否可以使用JDBC执行类似的操作。 "SELECT FROM * TABLE;INSERT INTO TABLE;" 是的,有可能。据我所知,有两种方法。他们是 通过设置数据库连接属性以允许多个查询,默认情况下用分号分隔。 通过调用返回隐式游标的存储过程。 以下示例演示了上述两种可能性。 示例1:(允许多个查询): 发送连接请求时,您需要将连接属性附加allowMultiQueries=true到数据库URL。这是额外的连接属性,如果那些已经存在的一些,比如autoReConnect=true,对等。可接受的值allowMultiQueries属性是true,false,yes,和no。其他任何值在运行时都会被拒绝SQLException。 String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true"; 除非通过了此类指令,否则SQLException将引发an 。 您必须使用execute( String sql )或其其他变体来获取查询执行的结果。 boolean hasMoreResultSets = stmt.execute( multiQuerySqlString ); 要遍历和处理结果,您需要执行以下步骤: READING_QUERY_RESULTS: // label while ( hasMoreResultSets || stmt.getUpdateCount() != -1 ) { if ( hasMoreResultSets ) { Resultset rs = stmt.getResultSet(); // handle your rs here } // if has rs else { // if ddl/dml/... int queryResult = stmt.getUpdateCount(); if ( queryResult == -1 ) { // no more queries processed break READING_QUERY_RESULTS; } // no more queries processed // handle success, failure, generated keys, etc here } // if ddl/dml/... // check to continue in the loop hasMoreResultSets = stmt.getMoreResults(); } // while results 示例2:要遵循的步骤: 创建一个或多个过程select,并进行DML查询。 使用从Java调用它CallableStatement。 您可以捕获ResultSet在过程中执行的多个。 DML结果无法捕获,但可以发出另一个结果select 来查找表中行的影响方式。 样品表和程序: mysql> create table tbl_mq( i int not null auto_increment, name varchar(10), primary key (i) ); Query OK, 0 rows affected (0.16 sec) mysql> delimiter // mysql> create procedure multi_query() -> begin -> select count(*) as name_count from tbl_mq; -> insert into tbl_mq( names ) values ( 'ravi' ); -> select last_insert_id(); -> select * from tbl_mq; -> end; -> // Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql> call multi_query(); +------------+ | name_count | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec) +---+------+ | i | name | +---+------+ | 1 | ravi | +---+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 从Java调用过程: CallableStatement cstmt = con.prepareCall( "call multi_query()" ); boolean hasMoreResultSets = cstmt.execute(); READING_QUERY_RESULTS: while ( hasMoreResultSets ) { Resultset rs = stmt.getResultSet(); // handle your rs here } // while has more rs来源:stack overflow
保持可爱mmm 2020-05-10 17:49:36 0 浏览量 回答数 0

回答

首先搜了下druid 官网 FAQ https://github.com/alibaba/druid/wiki/常见问题 发现是druid有对连接泄露进行监控处理 https://github.com/alibaba/druid/wiki/连接泄漏监测 https://github.com/alibaba/druid/issues/872 我这边没有配对监控,直接是从日志中看到的,有相应的线程栈信息,可以方便排查。 public DruidPooledConnection getConnectionDirect(long maxWaitMillis) throws SQLException { int notFullTimeoutRetryCnt = 0; DruidPooledConnection poolableConnection; while(true) { while(true) { try { Connection realConnection = poolableConnection.getConnection(); this.discardConnection(realConnection); } else { Connection realConnection = poolableConnection.getConnection(); if(realConnection.isClosed()) { this.discardConnection((Connection)null); } else { if(!this.isTestWhileIdle()) { break; } long currentTimeMillis = System.currentTimeMillis(); long lastActiveTimeMillis = poolableConnection.getConnectionHolder().getLastActiveTimeMillis(); long idleMillis = currentTimeMillis - lastActiveTimeMillis; long timeBetweenEvictionRunsMillis = this.getTimeBetweenEvictionRunsMillis(); if(timeBetweenEvictionRunsMillis <= 0L) { timeBetweenEvictionRunsMillis = 60000L; } if(idleMillis < timeBetweenEvictionRunsMillis) { break; } this.discardConnection(realConnection); } } } if(this.isRemoveAbandoned()) { StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace(); poolableConnection.setConnectStackTrace(stackTrace); poolableConnection.setConnectedTimeNano(); poolableConnection.setTraceEnable(true); Map var21 = this.activeConnections; synchronized(this.activeConnections) { this.activeConnections.put(poolableConnection, PRESENT); } } if(!this.isDefaultAutoCommit()) { poolableConnection.setAutoCommit(false); } return poolableConnection; } 这个连接泄露不会导致OOM,因为druid会去主动detroy这些未关闭的连接,也就是上面日志中的错误信息 现在知道是有数据库连接未关闭,但是代码中并没有去管理数据库连接池,都是交给了Spring去管理的呢,而且不是每个数据库操作都会有问题,而是特定的数据库操作有问题 开启debug日志 2018-01-23 21:08:35,760 DEBUG [org.springframework.data.redis.core.RedisConnectionUtils] - Opening RedisConnection 2018-01-23 21:08:35,761 DEBUG [org.springframework.data.redis.core.RedisConnectionUtils] - Closing Redis Connection 2018-01-23 21:08:35,762 DEBUG [org.mybatis.spring.SqlSessionUtils] - Creating a new SqlSession 2018-01-23 21:08:35,762 DEBUG [org.mybatis.spring.SqlSessionUtils] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5ba28a19] was not registered for synchronization because synchronization is not active 2018-01-23 21:08:35,765 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource 2018-01-23 21:08:35,765 DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.taobao.tddl.matrix.jdbc.TConnection@24f6de0] will not be managed by Spring 2018-01-23 21:08:35,766 DEBUG [com.taobao.tddl.group.jdbc.TGroupConnection] - [TDDL] dataSourceIndex=GroupIndex [index=0, failRetry=false], tddl version: 5.1.7 2018-01-23 21:08:35,810 DEBUG [org.mybatis.spring.SqlSessionUtils] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5ba28a19] 2018-01-23 21:08:35,810 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource 这里接可以看出阿里的开源软件距离工业级还有距离,Spring的可以清楚的看到连接释放与返回,而Druid并没有 因为这个是和特定的SQL有关系,所有看下具体的这个SQL操作 @Options(statementType = StatementType.CALLABLE) @Insert("<script> " + "</script>") int saveDemo(Demo demo); 这个是之前人留下的,这种写法目前在项目组不是很常见了,没有实现代码与SQL语句分离,不是很提倡。 这边的StatementType为Callable,看起来比奇怪,一般CallableStatement由于数据库存储过程的操作,显然这个语句不是这么调用存储过程。不知道前任为什么这么写,所以首先干掉了这个StatementType.CALLABLE参数。 结果果然出错了,调用的时候报错,一个好笑的错误,没有使用auto generate id,却调用了。 这个应该是MySQL 5.7 driver的一个bug,前任为了避免这个问题,使用CallableStatement绕过去了 Caused by: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement(). at com.taobao.tddl.repo.mysql.handler.PutMyHandlerCommon.handle(PutMyHandlerCommon.java:52) at com.taobao.tddl.executor.AbstractGroupExecutor.executeInner(AbstractGroupExecutor.java:59) at com.taobao.tddl.executor.AbstractGroupExecutor.execByExecPlanNode(AbstractGroupExecutor.java:40) at com.taobao.tddl.executor.TopologyExecutor.execByExecPlanNode(TopologyExecutor.java:59) at com.taobao.tddl.executor.MatrixExecutor.execByExecPlanNode(MatrixExecutor.java:282) 所以先改成xml方式验证下,xml默认是PreparedStatement,然后看下是否还能发生错误 发现没有了,所以问题的原因就是CallableStatement造成的
保持可爱mmm 2020-04-15 23:11:09 0 浏览量 回答数 0

阿里云高校特惠,助力学生创业梦!0元体验,快速入门云计算!

建个炫酷的简历网页,制作一个浪漫的表白网页,打造个人专属网盘,多种动手场景应用免费学!!!

回答

我看到谢了个,轻轻笑了。没别意思啊^-^,帮顶一下?谢了个~packageorg.lsqt.components.dao.dbutil;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Properties;importjavax.naming.InitialContext;importjavax.naming.NamingException;importjavax.sql.DataSource;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.handlers.ArrayListHandler;importorg.apache.commons.lang.StringUtils;importorg.apache.log4j.Logger;importorg.lsqt.components.dao.suport.DataRow;importorg.lsqt.components.dao.suport.DataSet;importorg.lsqt.components.dao.suport.DataTable;importorg.lsqt.components.dao.suport.DbHelper;importorg.lsqt.components.dao.suport.ParamType;/ <pre>   功能说明:   该执行器优先使用JNDI配置的数据源,其次才是jdbc数据源   该SQL执行器,提供简易并直接的SQL语句操作数据库.   该SQL执行器不依赖于任何第三方的数据源创建    (注:在多个service层内调用多个执行器,没有事务控制)   编写日期:2011-4-21 作者:袁Sky   历史记录 修改日期:2012-3-10 修改人:袁Sky 修改内容:   1.添加JNDI支持   2.添加存储过程执行支持 </pre> /publicclassSqlExecutor{//privatestaticfinalLoggerLOGGER=Logger.getLogger(SqlExecutor.class);//privatestaticStringUSERNAME;/**/privatestaticStringPASSWORD;//privatestaticStringURL;/**/privatestaticStringDRIVERCLASSNAME;//privatestaticStringJNDINAME;/**/privatestaticDataSourcedataSource;//privatestaticbooleanhasJndiDataSource;//privatestaticPropertiesproperties=newProperties();static{loadConfigFile();hasJndiDataSource=loadJndiDataSource();if(hasJndiDataSource==false){try{Class.forName(DRIVERCLASSNAME);}catch(ClassNotFoundExceptione){e.printStackTrace();}}}privatestaticvoidloadConfigFile(){try{<spanstyle="background-color:#E53333;">properties.load(SqlExecutor.class.getResourceAsStream("/dbconfig.properties"));LOGGER.debug(properties);USERNAME=properties.getProperty("username");PASSWORD=properties.getProperty("password");URL=properties.getProperty("url");DRIVERCLASSNAME=properties.getProperty("driverClassName");JNDINAME=properties.getProperty("jndiName");}catch(Exceptione){try{LOGGER.error("notfounddataSource'sconfigurationfile");thrownewException(e.getMessage());}catch(Exceptione1){e1.printStackTrace();}}}privatestaticbooleanloadJndiDataSource(){try{if(StringUtils.isNotEmpty(JNDINAME)){InitialContextcontext=newInitialContext();dataSource=(DataSource)context.lookup(JNDINAME);}}catch(NamingExceptione){LOGGER.warn("havenoJNDIconfigfound");returnfalse;}returnfalse;}privatestatic ConnectiongetConnection(){try{if(hasJndiDataSource){LOGGER.debug("database'sconnectionfromJNDIdataSource");returndataSource.getConnection();}else{Connectionconn=DriverManager.getConnection(URL,USERNAME,PASSWORD);LOGGER.debug("database'sconnectionfromDriverManager");returnconn;}}catch(SQLExceptione){LOGGER.debug(e);e.printStackTrace();}returnnull;}//-------------------------------------------------------------------------------/处理参数占位符字符串@paramparamValues paramValues<aclass='referer'target='_blank'>@returnprocessprocedureParamHold/privatestaticStringprocessProcedureParamHold(Object[]paramValues){StringholdString="";intholdLength=paramValues.length;for(inti=0;i<holdLength;i++){if(i!=holdLength-1){holdString="?,"+holdString;}else{holdString=holdString+"?";}}LOGGER.debug(holdString);returnholdString;}/**执行存储过程,返回结果集@paramprocedureName存储过程名称<aclass='referer'target='_blank'>@returnDataSet /publicstaticDataSetexecuteProcedure(finalStringprocedureName){finalDataSetDATASET=newDataSet();CallableStatementcstmt=null;ResultSetrs=null;Connectioncon=null;try{con=getConnection();cstmt=con.prepareCall("{call"+procedureName+"()}");booleanhasResults=cstmt.execute();while(hasResults){DataTabledt=newDataTable();rs=cstmt.getResultSet();intcolCount=rs.getMetaData().getColumnCount();while(rs.next()){DataRowrow=newDataRow();for(inti=0;i<colCount;i++){row.add(rs.getObject(i+1));}dt.add(row);}DATASET.add(dt);hasResults=cstmt.getMoreResults();}}catch(Exceptione){LOGGER.error("procedureexecutefail==>"+e.getMessage());returnnull;}finally{DbHelper.destroy(con,cstmt,rs);}returnDATASET;}/**执行只带输入参数的存储过程@paramprocedureName 存储过程名称@paramparamValues入参值<aclass='referer'target='_blank'>@returnDataSet数据集/publicstaticDataSetexecuteProcedure(finalStringprocedureName,finalObject[]paramValues){finalDataSetDATASET=newDataSet();CallableStatementcstmt=null;ResultSetrs=null;Connectioncon=null;try{con=getConnection();cstmt=con.prepareCall("{call"+procedureName+"("+processProcedureParamHold(paramValues)+")}");booleanhasResults=cstmt.execute();while(hasResults){DataTabledt=newDataTable();rs=cstmt.getResultSet();intcolCount=rs.getMetaData().getColumnCount();while(rs.next()){DataRowrow=newDataRow();for(inti=0;i<colCount;i++){row.add(rs.getObject(i+1));}dt.add(row);}DATASET.add(dt);hasResults=cstmt.getMoreResults();}}catch(Exceptione){LOGGER.error("procedureexecutefail==>"+e.getMessage());returnnull;}finally{DbHelper.destroy(con,cstmt,rs); }returnDATASET;}/存储过程调用(返回结果集+输出参数+执行系列更新语句),调用时显示指定入参和输出参数据数据类型 @paramprocedureName存储过程名称@paramparamValues入参值和输出参数的值@paramparamValueTypes储存过程入参和输出参数据的数据类型:ParamType.String待<aclass='referer'target='_blank'>@returnDataSet数据集/publicstaticDataSetexecuteProcedure(finalStringprocedureName,finalObject[]paramValues,finalint[]paramValueTypes){if(paramValues.length!=paramValueTypes.length){LOGGER.error("procedureexecutefail,Parametersandparametertypesvalue,thelengthnotequal ");returnnull;}finalDataSetDATASET=newDataSet();StringsqlHold=processProcedureParamHold(paramValues);CallableStatementcstmt=null;ResultSetrs=null;Connectioncon=null;try{con=getConnection();cstmt=con.prepareCall("{call"+procedureName+"("+sqlHold+")}");List<Integer>outputParamIndex=newArrayList<Integer>(); for(inti=0;i<paramValues.length;i++){if(paramValues[i]==ParamType.HOLDER){cstmt.registerOutParameter(i+1,paramValueTypes[i]);outputParamIndex.add(i+1);}else{cstmt.setObject(i+1,paramValues[i]);}}booleanhasResults=cstmt.execute();//处理输出参数值List<Object>outputValues=newArrayList<Object>();for(inti=0;i<outputParamIndex.size();i++){outputValues.add(cstmt.getObject(outputParamIndex.get(i)));}DATASET.setOutputParams(outputValues.toArray(newObject[outputValues.size()]));while(hasResults){DataTabledt=newDataTable();rs=cstmt.getResultSet();//---取得第一个结果集intcolCount=rs.getMetaData().getColumnCount();while(rs.next()){DataRowrow=newDataRow();for(inti=0;i<colCount;i++){row.add(rs.getObject(i+1));}dt.add(row);}DATASET.add(dt);hasResults=cstmt.getMoreResults();}}catch(Exceptione){LOGGER.error("executeprocedurefail==>"+e.getMessage());returnnull;}finally{DbHelper.destroy(con,cstmt,rs); }returnDATASET;}/**执行更新的SQL语句@paramsql<aclass='referer'target='_blank'>@return/publicstaticbooleanexecuteSql(Stringsql){QueryRunnerrun=newQueryRunner();Connectionconn=getConnection();try{returnrun.update(conn,sql)>0;}catch(SQLExceptionex){LOGGER.error("executesqlfail==>"+ex.getMessage());returnfalse;}finally{DbHelper.destroy(conn,null,null);}}publicstaticbooleanexecuteSql(Stringsql,Object[]paramValues){QueryRunnerrun=newQueryRunner();Connectionconn=getConnection();try{returnrun.update(conn,sql,paramValues)>0;}catch(SQLExceptionex){LOGGER.error("executesqlfail==>"+ex.getMessage());returnfalse;}finally{DbHelper.destroy(conn,null,null);}}/**批量执行SQL更新语句@paramsql带占位符的SQL语句@paramdataTable参数数据表格 /publicstaticbooleanexecuteSql(Stringsql,Object[][]dataTable){QueryRunnerrun=newQueryRunner();Connectionconn=getConnection();try{ returnrun.batch(conn,sql,dataTable).length>0; }catch(SQLExceptionex){LOGGER.debug("executesqlfail==>"+ex.getMessage());returnfalse;}finally{  DbHelper.destroy(conn,null,null);}}/**执行查询语句,返回二维结果集@paramsql<aclass='referer'target='_blank'>@return<aclass='referer'target='_blank'>@throwsSQLException/publicstaticList<Object[]>executeSqlQuery(Stringsql){QueryRunnerrun=newQueryRunner();Connectionconn=getConnection();try{return run.query(conn,sql,newArrayListHandler(){@OverrideprotectedObject[]handleRow(ResultSetrs)throwsSQLException{returnsuper.handleRow(rs);}});}catch(SQLExceptionex){LOGGER.error("executesqlfail==>"+ex.getMessage());returnnull;}finally{DbHelper.destroy(conn,null,null);}}/**执行带"?"占位符的查询语句,返回二维结果集@paramsql@paramparamValues<aclass='referer'target='_blank'>@return<aclass='referer'target='_blank'>@throwsSQLException/publicstaticList<Object[]>executeSqlQuery(Stringsql,Object[]paramValues){QueryRunnerrun=newQueryRunner();Connectionconn=getConnection();try{returnrun.query(conn,sql,newArrayListHandler(){@OverrideprotectedObject[]handleRow(ResultSetrs)throwsSQLException{returnsuper.handleRow(rs);}},paramValues);}catch(SQLExceptionex){LOGGER.error("executesqlfail==>"+ex.getMessage());returnnull;}finally{DbHelper.destroy(conn,null,null);}}}谢谢阿,我这个自定义加载怎么搞定那~就是读取了配置文件,怎么跟spring整合那~<?xmlversion="1.0"encoding="UTF-8"?><beansxmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.0.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context-3.0.xsd"><!--wicket的web框架用-->  <beanid="consoleApplication"class="org.lsqt.content.web.wicket.ConsoleApplication"/><!--spring加载xml属性配置--><beanclass="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer"><propertyname="location"value="classpath:springConfig.properties"/></bean><!--common--><importresource="applicationContext-dataSource.xml"/><importresource="applicationContext-hibernate.xml"/><importresource="applicationContext-aop.xml"/><importresource="applicationContext-ehcache.xml"/><importresource="application-spring-mvc.xml"/><spanstyle="background-color:#E53333;"><context:annotation-config/><spanstyle="background-color:#E53333;">  <context:component-scanbase-package="org.lsqt"/></beans>不同myjar.jar由JDK的ApplicationClassLoader加载而你自己定义的classLoader去加载xx.jar那么在myjar中是找不到xx.jar中的东西的建议学习一下OSGi,对classLoader应用的典型好的我看看谢谢阿
爱吃鱼的程序员 2020-06-22 21:36:09 0 浏览量 回答数 0

云产品推荐

上海奇点人才服务相关的云产品 小程序定制 上海微企信息技术相关的云产品 国内短信套餐包 ECS云服务器安全配置相关的云产品 开发者问答 阿里云建站 自然场景识别相关的云产品 万网 小程序开发制作 视频内容分析 视频集锦 代理记账服务 阿里云AIoT