I am trying to write type handler for array of objects but this is giving me errors. I want to store arraylist as JSON in DB(mysql) and retrieve it as arraylist . I got the type handler examples with arraylist of String but here I am using custom class instead of String and not able to understand what is the correct way to write type handler for it.
Here is the code for typeHandler
import com.knimbus.model.Options;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.google.common.collect.Lists;
@MappedJdbcTypes(JdbcType.VARCHAR)
public class MyBatisJsonTypeHandler extends BaseTypeHandler<List<Options>> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<Options> parameter, JdbcType jdbcType) throws SQLException {
Array array = ps.getConnection().createArrayOf("text", parameter.toArray());
ps.setArray(i, array);
}
@Override
public List<Options> getNullableResult(ResultSet rs, String columnName) throws SQLException {
return toList(rs.getArray(columnName));
}
@Override
public List<Options> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return toList(rs.getArray(columnIndex));
}
@Override
public List<Options> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return toList(cs.getArray(columnIndex));
}
private List<Options> toList(Array pgArray) throws SQLException {
if (pgArray == null) return Lists.newArrayList();
Options[] options = (Options[]) pgArray.getArray();
return containsOnlyNulls(options) ? Lists.<Options>newArrayList() : Lists.newArrayList(options);
}
private boolean containsOnlyNulls(Options[] options) {
for (Options ops : options) {
if (ops != null) {
return false;
}
}
return true;
}}`
My dto class looks like this
import java.util.ArrayList;
import java.util.List;
public class Question {
private Long questionId;
private String question;
private List<Options> options = new ArrayList<Options>();
public Question() {
}
public Long getQuestionId() {
return questionId;
}
public void setQuestionId(Long questionId) {
this.questionId = questionId;
}
public String getQuestion() {
return question;
}
public void setQuestion(String question) {
this.question = question;
}
public List<Options> getOptions() {
return options;
}
public void setOptions(List<Options> options) {
this.options = options;
}
}
public class Options {
private String optionCode;
private String optionName;
private int order;
public Options() {}
public String getOptionCode() {
return optionCode;
}
public void setOptionCode(String optionCode) {
this.optionCode = optionCode;
}
public String getOptionName() {
return optionName;
}
public void setOptionName(String optionName) {
this.optionName = optionName;
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
@Override
public String toString() {
return "Options [optionCode=" + optionCode + ", optionName=" + optionName + ", order=" + order + "]";
}
}
`
my mapper file code is like this
`
</resultMap>
<select id="getAllQuestion" resultType="question" >
SELECT * FROM question
</select>
<insert id="addQuestion" parameterType="question">
INSERT INTO question( questionId, question, options)
VALUES(#{questionId},#{question}, #{options, typeHandler=
{com.knimbus.util.config.MyBatisJsonTypeHandler}, javaType =
{com.knimbus.model.Options}})
</insert>
Below is the request data passing through client
{
"questionId": 4,
"question":"Who will win FIFA world cup?",
"options": [
{
"optionCode": "A",
"optionName": "Brazil",
"order": 1
},
{
"optionCode": "B",
"optionName": "France",
"order": 2
},
{
"optionCode": "C",
"optionName": "Argentina",
"order": 3
}
]
}
below is the table structure
` CREATE TABLE `question`(
`questionId` int NOT NULL AUTO_INCREMENT,
`question` varchar(50) NOT NULL,
`Options` json DEFAULT NULL,
PRIMARY KEY (`questionId`));`
While inserting the data getting below errors. Looks like I am missing something in type handler class but not able to figure it out.
SEVERE: Servlet.service() for servlet [spring] in context with path [/UserMyBatis] threw exception [Request processing failed; nested exception is org.apache.ibatis.exceptions.PersistenceException:
Error updating database. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias '{com.knimbus.util.config.MyBatisJsonTypeHandler'. Cause: java.lang.ClassNotFoundException: Cannot find class: {com.knimbus.util.config.MyBatisJsonTypeHandler
Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias '{com.knimbus.util.config.MyBatisJsonTypeHandler'. Cause: java.lang.ClassNotFoundException: Cannot find class: {com.knimbus.util.config.MyBatisJsonTypeHandler] with root cause
java.lang.ClassNotFoundException: Cannot find class: {com.knimbus.util.config.MyBatisJsonTypeHandler
and while retrieving data it does not send the "options" class related data, something like below
`[
{
"questionId": 3,
"question": "Who will win FIFA world cup ",
"options": []
}
]`
com.knimbus.util.config.MyBatisJsonTypeHandleris wrong or 2) the class is not in the classpath. You don't use Spring Boot, do you? Also, the type handler does not look OK. Neither MySQL nor the driver (mysql-connector-java) supportsARRAYtype.