Okay so this is kinda re-posting of this question Inserting HashMap Values to a table using ibatis (but I am looking for a different way – the answer wont work for me)..
DB1GetStudentDataMapper.xml (this queries to one database)
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.testing.db1.DB1GetStudentDataMapper"> <select id="selectAllStudents" resultType="java.util.Map"> SELECT STUDENT_CD, STUDENT_NM, PARENT_CD, CREATED_DATE FROM STUDENT WHERE STD_STATUS='ACT' </select> </mapper>
DB2InsertStudentMapper.xml (this queries to different database)
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.testing.db2.DB2InsertStudentMapper"> <insert id="insertStudent" parameterType="java.util.HashMap"> INSERT INTO STUDENT <!-- dynamically select column names from hashmap --> (#{stdMap.keySet}) // this is not working - its coming as null <!-- dynamically select values for the above columns from hashmap --> VALUES (#{stdMap.values}) // this is not working - its coming as null </insert> </mapper>
DB2InsertStudentMapper.java
public interface TMODSBDataRefreshMapper { void insertStudent(@Param("stdMap") HashMap stdMap); }
StudentDataProcess.java
public class Student { // I have defaultExecutorType as BATCH in my mapper config file private DB1GetStudentDataMapper db1Mapper; // Interface Mapper for first data source private DB2InsertStudentMapper db2Mapper; // Interface Mapper for second data source public processStudent() throws Exception { List<HashMap> rs = db1Mapper.selectAllStudents(); // Gets some 15k+ records for(int i =0; i < rs.size(); i++) { // so this will loop through 15k+ records HashMap result = rs.get(i); System.out.println(result.keySet()); // prints column names from select query [STUDENT_CD, STUDENT_NM, PARENT_CD, CREATED_DATE] System.out.println(result.values()); // prints above column values of first data set [1001, Mike, 5001, 2021-07-01] // All I am trying is to insert above 15k records into different database dynamically rather than creating POJO db2Mapper.insertStudent(result); } } }
Note: Just for example I used 4 columns – I have some 150+ columns to work with..
PS: Please remember that this solution works better when you work with fewer columns – but doesn’t work well if you have bulk insert – it effects performance.
Advertisement
Answer
When iterating a map using <foreach />
, the key and the value are assigned to the variables specified in index
and item
respectively.
So, your insert statement should look something like this.
<insert id="insertStudent"> INSERT INTO STUDENT ( <foreach collection="stdMap" index="col" separator=","> ${col} </foreach> ) VALUES ( <foreach collection="stdMap" item="val" separator=","> #{val} </foreach> ) </insert>
- You must use
${}
for column names and#{}
for values. See the FAQ for the details. - To iterates the map in the same order, you should use
java.util.LinkedHashMap
as the result type of<select />
.