Tag Archives: insert

[Solved] Mybatis batch insert error: The incoming Table Format Data Stream (TDS) Remote Procedure Call…

Error message:

The incoming Table Format Data Stream (TDS) Remote Procedure Call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum should be 2100

Error analysis:

Due to too many SQL statement parameters spliced by mybatis

Solution:

Batch processing without mybatis

① Configuration file   applicationContext-mybatis.xml

  <!-- Solving mybatis batch insert -->
    <bean class="org.mybatis.spring.SqlSessionTemplate" id="sqlSession">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" />
        <constructor-arg name="executorType" value="BATCH" />
    </bean>

②service import

    @Autowired
	private SqlSessionTemplate sst;

③ The self encapsulated method was used in the service for batch insert

	public Boolean insert(List<PDA_JWL_INTERFACE_H> inList,String zyh){
		// Get a new session with mode BATCH and auto-commit set to false
		// If auto-commit is set to true, it will not be possible to control the number of commits and will instead be submitted uniformly at the end, which may lead to a memory overflow
		SqlSession session = sst.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
		moMapper= session.getMapper(MOMapper.class);
		Map<String,Object> cc = new HashMap<>();
		
		try {
			for (PDA_JWL_INTERFACE_H in : inList) {
				moMapper.insert(in);
			}
			cc.put("zyh", zyh);
			moMapper.call(cc);
			session.commit();
			//Clearing the cache to prevent overflow
			session.clearCache();
			return true;
		} catch (Exception e) {
			session.rollback();
			return false;
		} finally {
			session.close();
		}
	}

 

End 9315;

	/**
	 * submit
	 */
	@RequestMapping(value="/tj", method = RequestMethod.POST,produces="application/json;charset=utf-8")
	@ResponseBody
	public Map<String,Object> tj(@RequestBody List<PDA_JWL_INTERFACE_H> tList,HttpServletRequest req){
		
		//Encapsulating the return value of a map
		Map<String,Object> m = new HashMap<>();
		
		
		//Decryption Certification
		String token = req.getParameter("token");
		String tk = MD5Utils.encode(CREKEY);
		System.out.println(tk);
		if(!token.equals(tk)){
			m.put("flag",Code.FLAG1.getIndex());
			m.put("erroCode", Code.ERROR1.getIndex());
			m.put("content", "");
			return m;
		}
		//Store the data to be placed in the intermediate table
		List<PDA_JWL_INTERFACE_H> inList = new LinkedList<>();
		
		// Job number, produced with UUID, unique number, distinguishes job type
		
		//Long t1 = System.currentTimeMillis();
		
		String zyh = UUID.randomUUID().toString();
		try {
			for (PDA_JWL_INTERFACE_H t : tList) {
				
				PDA_JWL_INTERFACE_H in = new PDA_JWL_INTERFACE_H();
				
				//Job number
				in.setZYH(zyh);
				// Order maker number: default current login ID (required, not shown)
				in.setZDRH(t.getZDRH());
				//Things type: default "Purchase Inbound" (required, not shown)
				in.setSWLX(t.getSWLX());
				//Supplier number: required, not shown
				in.setGYSH(t.getGYSH());
				//Supplier: The company number in the view is the same as the company number of this job
				in.setGYS(t.getGYS());
				//Company number: required, not shown
				in.setGSH(t.getGSH());
				//Warehouse number: required, not shown
				in.setCKH(t.getCKH());
				//Warehouse: the operator number in the view is the same as the current login ID
				in.setCK(t.getCK());
				//new warehouse number
				in.setXCKH(t.getXCKH());
				//new warehouse
				in.setXCK(t.getXCK());
				//claimers
				in.setLYR(t.getLYR());
				//new warehouse position number
				in.setXKWH(t.getXKWH());
				//new storage position
				in.setXKW(t.getXKW());
				//Purchase Type:Required
				in.setCGLX(t.getCGLX());
				//Purchaser: required
				in.setCGR(t.getCGR());
				//Stockroom number: required, not displayed
				in.setKWH(t.getKWH());
				// Warehouse location: the warehouse number in the view is the same as the warehouse number of this job
				in.setKW( t.getKW());
				//line number: automatically generate a non-repeating serial number for this job. app-side pass over
				in.setHH( t.getHH());
				//Item number: return the corresponding value directly after inputting the item number, which needs to be matched exactly. The company number and item type in the view are the same as the company number and purchase type of this job. (required)
				in.setWPH(t.getWPH());
				//Item name: display, not editable
				in.setWPMC(t.getWPMC());
				//spec: show, not editable
				in.setGG(t.getGG());
				//units: display, non-editable
				in.setDW(t.getDW());
				//production lot number: popup selection after entering keyword.
				in.setSCPH(t.getSCPH());
				//Grade: default First class (required)
				in.setDJI(t.getDJI());
				//Quantity: required
				in.setSL(t.getSL());
				//Number of pieces: Not required
				in.setJS(t.getJS());
				//Unit price: non-required, if it is empty or 0, only prompted, no control over subsequent operations.
				in.setDJ(t.getDJ());
				//amount: unit price*quantity, not editable.
				in.setJE(t.getJE());
				//Currency code: default RMB (required)
				in.setBBM(t.getBBM());
				//Tax rate: default 0.17 (required)
				in.setSLV(t.getSLV());
				// First level sector
				in.setYJBM(t.getYJBM());
				//Inventory source
				in.setPDLY(t.getPDLY());
				//lot number
				in.setPH(t.getPH());
				
				inList.add(in);
				
			}
			Boolean flag = moService.insert(inList,zyh); if(!flag){ int i = 1/0; }else{ /*Long t2 = System.currentTimeMillis(); System.out.println(t2-t1);*/ }
			
		} catch (Exception e) {
			e.printStackTrace();
			m.put("flag",Code.FLAG1.getIndex());
			m.put("erroCode", Code.ERROR2.getIndex());
			m.put("content", "");
			return m;
		}
		m.put("flag",Code.FLAG.getIndex());
		m.put("erroCode", "");
		m.put("content", "");
		return m;
	}
	

 

⑤ Xxmapper.xml in the mapping file

	<!-- submit -->
	<insert id="insert" parameterType="PDA_JWL_INTERFACE_H" >
	  insert into PDA_JWL_INTERFACE_H ( 
	  
	  ZYH,ZDRH,SWLX,SWRQ,GYSH,GYS,GSH,CKH,CK,XCKH,
	  XCK,LYR,XKWH,XKW,CGLX,CGR,KWH,KW,HH,WPH,
	  WPMC,GG,DW,SCPH,DJI,SL,JS,DJ,JE,BBM,
	  SLV,YJBM,PDLY,PH
	  
	  ) values
	  
	  (#{ZYH}, #{ZDRH}, #{SWLX},getdate(), #{GYSH},
 	   #{GYS}, #{GSH},#{CKH}, #{CK},#{XCKH}, 
 	   #{XCK},#{LYR},#{XKWH},#{XKW},#{CGLX}, 
	   #{CGR}, #{KWH},#{KW}, #{HH}, #{WPH},
	   #{WPMC}, #{GG},#{DW}, #{SCPH}, #{DJI},
	   #{SL}, #{JS},#{DJ}, #{JE}, #{BBM},
	   #{SLV},#{YJBM},#{PDLY}, #{PH})
	 
	</insert>