Type handler for ArrayList in myBatis

Question

I am Trying to write type handler for ArrayList but this is giving me errors can any one help me.

I want to store ArrayList as VARCHAR in DB and retrieve it as ArrayList.

package com.someweb.typehandlers;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
//@MappedTypes(java.util.ArrayList.class)

//@MappedJdbcTypes(JdbcType.VARCHAR)
public class StringArrayListTypeHandler extends BaseTypeHandler<ArrayList<String>> 
{
@Override
public void setNonNullParameter(PreparedStatement ps, int i, ArrayList<String> parameter, JdbcType jdbcType)
        throws SQLException {
    // TODO Auto-generated method stub
    StringBuilder str=new StringBuilder(parameter.toString());
    ps.setString(i,str.substring(1,str.length()-1));

}

@Override
public ArrayList<String> getNullableResult(ResultSet rs, String columnName) throws SQLException {
    // TODO Auto-generated method stub

    String str=rs.getString(columnName);

    ArrayList<String> roles=new ArrayList<String>();
    String[] rolesarray=str.split(",");
    for(String s:rolesarray)
    roles.add(s);

    return roles;
}

@Override
public ArrayList<String> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
    // TODO Auto-generated method stub
    String str=rs.getString(columnIndex);

    ArrayList<String> roles=new ArrayList<String>();
    String[] rolesarray=str.split(",");
    for(String s:rolesarray)
    roles.add(s);

    return roles;   }

@Override
public ArrayList<String> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    // TODO Auto-generated method stub
    String str=cs.getString(columnIndex);

    ArrayList<String> roles=new ArrayList<String>();
    String[] rolesarray=str.split(",");
    for(String s:rolesarray)
    roles.add(s);

    return roles;   }

}

I am new to myBatis . So some one help me i am not able to figure out how this can be accomplished

My dto class looks like this

package com.someweb.dto;

import java.security.Principal;
import java.sql.Array;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class UserDTO implements Principal {

private int id;
private String username;

private String name;
private String password;
private String token;
private String email;
private boolean isAuthenticated;
private boolean is_active;

private List<String> role;
private String phone;
public String getToken() {
    return token;
}

public void setToken(String token) {
    this.token = token;
}

public String getUsername() {
    return username;
}

public void setUsername(String username) {
    this.username = username;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public boolean isAuthenticated() {
    return isAuthenticated;
}

public void setAuthenticated(boolean isAuthenticated) {
    this.isAuthenticated = isAuthenticated;
}

public List<String> getRole() {
    return role;
}



public void setRole(List<String> role) {
    this.role = role;
}
public void setRole(String role) {
     this.role.add(role);
}
 public void addRole(String role)
 {
     if(role==null) this.role=new ArrayList<String>();
     this.role.add(role);

 }

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) {
    this.password = password;
}

public boolean isIs_active() {
    return is_active;
}

public void setIs_active(boolean is_active) {
    this.is_active = is_active;
}

public String getPhone() {
    return phone;
}

public void setPhone(String phone) {
    this.phone = phone;
}
}

my mapper file code is like this

    <resultMap id="userResultMap" type="com.someweb.dto.UserDTO">
  <id property="id" column="id" />
  <result property="username" column="username"/>
  <result property="password" column="password"/>
  <result property="email" column="email"/>
 <result property="phone" column="phone"/>
 <result property="is_active" column="is_active"/>
  <collection property="role" ofType="java.lang.String" >
        <result column="role" />
    </collection>

</resultMap>
<insert id="insertUser" useGeneratedKeys="true"
    keyProperty="id">
  insert into tblusers(username,password,email,phone,role,is_active) 
  values(#{username},#{password},#{email},#{phone},#{role,typeHandler=com.someweb.typehandlers.StringArrayListTypeHandler},#{is_active})
</insert>

now the insertion executes . But when retrieving the data to DTO the entire the role field is been fetched as single string in ArrayList .

And if remove the typeHandler attribute i am getting errors

Cause: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

Answer 1

I found the solution

Collection is used to fetch many side of one-to-many relation ship So it always returns Collection of Objects from multiple records

In my case i need ArrayList from single cell of record so . i have to remove <collection> tag and by just using <result> tag as shown below code works

<result property="role" column="role" javaType="java.util.ArrayList"
        jdbcType="VARCHAR" typeHandler="com.greenflight.typehandlers.StringArrayListTypeHandler" />