Springmvc calls the stored procedure and returns the way the stored procedure returns the data
- 2021-12-11 18:03:35
- OfStack
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>