I have 2 Java POJO files
class Filter Variables { private String id; private String name; private List<ValueVariables> variableValues; getters() setters() }
class ValueVariables { private String id; private String valueListId; private String value; }
The database has a FK mapping between FilterVariable.id and ValueVariable.valueListId. Inserts into the table work perfectly, i have a single transaction that inserts into both tables separately. While retrieving the data using select
and join
, I only receive the first row from the table in the database.
Database Tables:
CREATE TABLE IF NOT EXISTS filter_variables ( id VARCHAR(36) NOT NULL, name VARCHAR(30) NOT NULL, PRIMARY KEY(id) ); CREATE TABLE IF NOT EXISTS variable_values ( id INT(10) NOT NULL AUTO_INCREMENT, valueListId VARCHAR(36) NOT NULL, value VARCHAR(100) NOT NULL, PRIMARY KEY(id), FOREIGN KEY (valueListId) REFERENCES filter_variables(id) );
<select id="selectFilterVariableById" parameterType="java.lang.String" resultMap="FilterVariablesJoinResultMap"> select FV.id, FV.name, VV.value, VV.valueListId from filter_variables as FV left outer join variable_values as VV ON FV.id=VV.valueListId where FV.id = #{id,jdbcType=VARCHAR} </select>
The method in the interface is defined as List<FilterVariables> selectFilterVariableById(String id);
I understand that with 2 separate I can store values into the two Pojo and use them, but is there any way with joins where I can retrieve multiple rows?
The result map is defined as so:
<resultMap id="FilterVariablesJoinResultMap" type="com.example.mybatissample.FilterVariables"> <id column="id" jdbcType="VARCHAR" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> <collection property="variableValues" ofType="com.example.mybatissample.ValueVariables"> <id property="id" column="id"/> <result property="valueListId" column="valueListId"/> <result property="value" column="value"/> </collection> </resultMap>
This is how the data in mysql looks like.
When a request is made from Java:
List<FilterVariables> vals= filterVariablesMapper.selectFilterVariableById(id); System.out.println(vals); System.out.println(vals.size());
I only get the first row
[FilterVariables [id=b218df5c-eac1-4e01-846a-119345bb254d, name=ME, valueId=null, storageRecordType=null, values={b218df5c-eac1-4e01-846a-119345bb254d test@mailinator.com}]]
1
Advertisement
Answer
As mentioned by @ave, the solution is to either
- Remove the from the collection – degrades performance
- Just rename the variable used in – Implementation below:
The result map is now:
<resultMap id="FilterVariablesJoinResultMap" type="com.example.mybatissample.FilterVariables"> <id column="id" jdbcType="VARCHAR" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> <collection property="variableValues" ofType="com.example.mybatissample.ValueVariables"> <id property="varId" column="varId"/> <result property="valueListId" column="valueListId"/> <result property="value" column="value"/> </collection> </resultMap>
With this the results come as required.
ValueVariable is now:
class ValueVariables { private String varId; private String valueListId; private String value; getters() and setters() }
The select query is now:
<select id="selectFilterVariableById" parameterType="java.lang.String" resultMap="FilterVariablesJoinResultMap"> select FV.id, name, description, type, columnId, storageRecordType, valueId, operator, dbId, sharedValue, systemVariable, VV.id as varId, VV.value, VV.valueListId from filter_variables as FV left outer join variable_values as VV ON FV.id=VV.valueListId where FV.id = #{id,jdbcType=VARCHAR} </select>
Please note that VV.id is to be aliased for this to work.