经过

1、项目中运用了，使用了存储过程。

2、定义的service接口名为：

/**     * 根据参数查询业绩目标列表     * @param pageNo     * @param pageSize     * @param orgNo     * @param businessNo     * @param year     * @see AssetPlan     * @return     */    PageModel selectAssetPlanList(int pageNo,int pageSize, String orgNo, String businessNo, String year);

3、在调用此方法的时候报错：Connection is read-only

错误详细信息

2017-11-14 15:39:57.971 [   ] INFO  org.springframework.beans.factory.xml.XmlBeanDefinitionReader 315 : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]2017-11-14 15:39:58.930 [   ] INFO  org.springframework.jdbc.support.SQLErrorCodesFactory 126 : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]2017-11-14 15:40:20.333 [   ] ERROR com.hzcf.flagship.web.AssetPlanController 53 : ### Error querying database.  Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed### The error may exist in class path resource [com/hzcf/flagship/mapping/AssetPlanMapper.xml]### The error may involve com.hzcf.flagship.dao.AssetPlanMapper.selectAssetPlanList-Inline### The error occurred while setting parameters### SQL: CALL f_asset_plan_list(?,?,?,?,?)### Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed; SQL []; Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowedorg.springframework.dao.TransientDataAccessResourceException: ### Error querying database.  Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed### The error may exist in class path resource [com/hzcf/flagship/mapping/AssetPlanMapper.xml]### The error may involve com.hzcf.flagship.dao.AssetPlanMapper.selectAssetPlanList-Inline### The error occurred while setting parameters### SQL: CALL f_asset_plan_list(?,?,?,?,?)### Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed; SQL []; Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:107)	...	at java.lang.Thread.run(Thread.java:745)Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed	... 106 more2017-11-14 15:45:27 JRebel: Reloading class 'com.hzcf.flagship.web.AssetPlanController'.

产生原因

一般如果报了这个错，是项目中配置了只读事务的问题。

结合我在此处使用了存储过程，存储过程中使用了INTO关键字，决定了存储过程为非只读操作。所以会报错。

项目中配置的aop事务

        
    		
     			
      				
       
        com.hzcf.flagship.service.*
       			
      		
     	
    	
    		
     	
    	
    	
    		
     			
      			
      			
      			
      			
      			
      			
      			
      			
      			
      			
      		
     	
    

解决办法

因为我项目中事务切入点是所有的Service方法。

只需把selectAssetPlanList()方法名称改成queryAssetPlanList()。使此方法不加入aop事务中。就ok了。

存储过程（不是创建存储过程的sql语句）

BEGINSET @SQL = NULL;SELECT	GROUP_CONCAT(		DISTINCT CONCAT(			'MAX(IF(ap.month = ''',			ap.month,			''', ap.plan_value, 0)) AS ''',			ap.month,			''''		)	) INTO @SQLFROM	f_asset_plan ap;SET @SQL = CONCAT(	'Select ap.org_name, ap.business_name, ap.year, ',	@SQL,	' From f_asset_plan ap where 1=1 ');IF v_org_no is not null then set @sql = CONCAT(@sql,' and ap.org_no = ''',v_org_no,'''');end if;IF v_business_no is not null then set @sql = CONCAT(@sql,' and ap.business_no = ''',v_business_no,'''');end if;IF v_year is not null then set @sql = CONCAT(@sql,' and ap.year = ''',v_year,'''');end if;IF v_from IS NULL THEN SET v_from = 0; END IF;IF v_pageSize IS NULL THEN SET v_pageSize = 10; END IF;SET @SQL = CONCAT(@SQL,' Group by ap.org_no,ap.business_no limit ',v_from,',',v_pageSize);PREPARE stmt FROM @SQL;EXECUTE stmt;DEALLOCATE PREPARE stmt;END

参数

IN v_from int,IN v_pageSize int,IN v_org_no varchar(225),IN v_business_no varchar(225),IN v_year varchar(225)

参考