Springmvc calls the stored procedure and returns the way the stored procedure returns the data

Directory Springmvc calls the stored procedure and returns the data returned by the stored procedure. The implementation is as follows. Here, we should focus on the description of Springmvc calling the stored procedure and entity file writing under 1

Springmvc calls the stored procedure and returns the data returned by the stored procedure

The java backend often needs to interact with the database and return business data. 1 In general, SQL is used to interact, but in some special scenarios, stored procedures can also be directly used to return data.

The advantage of returning data from a stored procedure is that it takes only one call to return different business data according to different parameters, and these business data may have completely different column names.

The implementation is as follows

First define the SqlMap. xml file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="YJSPGJ">
  <parameterMap id="yjspgj_test" class="java.util.Map">
   <parameter property="v_dxlx" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
   <parameter property="i_qsrq" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
  </parameterMap>
  <procedure id="yjspgj_test" resultClass="java.util.HashMap" remapResults="true" parameterMap="yjspgj_test">
   {call sp_test_returnmap(?,?)}
  </procedure>   
</sqlMap> 

Here, we should focus on 1

1. The returned resultClass = "java. util. HashMap", 1 must be HashMap. If you write Map directly, you will report an error, because Map is an interface, which cannot be instantiated, and HashMap is a class, which can be instantiated.

2, 1 must be added remapResults = "true", otherwise, when the stored procedure returns a column that is not 1, it will cause the system to report an error.

Define the base class api:

public interface YjspgjService {
 String KEY="yjspgj.YjspgjService";
 public ResultCommon selectTest(Map<String,Object> map);
}

Define the implementation class service:

@Service(YjspgjService.KEY)
public class YjspgjServiceImpl extends SubService implements YjspgjService {
 
 @Autowired 
 private YjspgjDao yjspgjDao;
 public ResultCommon selectTest(Map<String, Object> map) {
  // TODO Auto-generated method stub
  ResultListData result=new ResultListData(PasCloudCode.SUCCESS);
  List listData=yjspgjDao.selectTest(map);
  result.setRows(listData);
  return result;
 } 
}

Define the database operation class dao:

@Repository
public class YjspgjDao { 
 private static final Logger log = LoggerFactory.getLogger(YjspgjDao.class); 
    @Autowired
    private IBaseDAO ibaseDAO;
    public List selectTest(Map<String,Object> map){
  String sqlKey="yjspgj_test";
  return (List)ibaseDAO.selectInfoByPara(sqlKey, map);     
    } 
}

Call controller:

@Controller
@RequestMapping("/yjspgj")  
public class YjspgjController extends BaseController { 
 @Autowired 
 YjspgjService yjspgjService; 
  private static Gson gson = new GsonBuilder().serializeNulls().create();// Used for json Format conversion 
 @RequestMapping("/showData")
 @ResponseBody
 public void showData(HttpServletRequest request, HttpServletResponse response){
  Map<String,Object> map=new HashMap();
  setMap(map,request);// Self-defined Map Value of 
  ResultListData rc= (ResultListData) yjspgjService.selectTest(map);
  List list=rc.getRows();
  for(int i=0;i<list.size();i++){
   try {
    response.getWriter().write(list.get(i).toString()+"\n");
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 } 
 
 @Override
 protected Class setClass() {
  // TODO Auto-generated method stub
  return this.getClass();
 } 
}

Springmvc Calling Stored Procedures, entity File Writing

<!-- The application for advertising task was approved -->
	<select id="approveAdTask" statementType="CALLABLE" parameterType="java.util.Map">
		{call approveAdTask(
		 #{sn,mode=IN,jdbcType=VARCHAR}
		,#{ssn,mode=IN,jdbcType=VARCHAR}
		,#{psn,mode=IN,jdbcType=VARCHAR}
		,#{sname,mode=IN,jdbcType=VARCHAR})}
	</select>