Im use myBatis for work with myMysql. I have several identical tables (actors, producers, composers end etc..) that contain only two fields - id and name.
I have to write a lot of almost identical code to work with this. For example mapper
<?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="ru.common.mapper.NamedEntityMapper">
<resultMap id="actor" type="Actor">
<id property="id" column="id"/>
<result property="name" column="name"/>
</resultMap>
<select id="getActorByName" parameterType="String" resultMap="actor">
SELECT * FROM actors WHERE name = #{name}
</select>
<select id="getActorById" parameterType="String" resultMap="actor">
SELECT * FROM actors WHERE id = #{id}
</select>
<insert id="saveActor" useGeneratedKeys="true" parameterType="Actor" keyProperty="id">
INSERT INTO actors (name) VALUES (#{name})
</insert>
<resultMap id="writer" type="Writer">
<id property="id" column="id"/>
<result property="name" column="name"/>
</resultMap>
<select id="getWriterByName" parameterType="String" resultMap="writer">
SELECT * FROM writers WHERE name = #{name}
</select>
<select id="getWriterById" parameterType="String" resultMap="writer">
SELECT * FROM writers WHERE id = #{id}
</select>
<insert id="saveWriter" useGeneratedKeys="true" parameterType="Writer" keyProperty="id">
INSERT INTO writers (name) VALUES (#{name})
</insert>
</mapper>
As can be seen in the mapper, very similar methods and queries that differ only in the name of the table and the type being returned. In reality, there are more such methods and it looks awful.
And it is a interface
public interface NamedEntityMapper {
Actor getActorById(long id);
Actor getActorByName(String name);
void saveActor(Actor actor);
Writer getWriterById(long id);
Writer getWriterByName(String name);
void saveWriter(Writer writer);
}
I tried to do it like this, I made a common interface for each similar model. (BaseModel)
public interface BaseModel {
int getId();
void setId(int id);
String getName();
void setName(String name);
}
And implemented this interface in all models used like Actor...
But this led to failure because it is not clear how to explain to the mapper to create an instance of the desired class. How to transfer type which needs to be created in a xml mapper?
Something like that
public interface NamedEntityMapper<T extends BaseModel> {
T getEntityById(long id, String tableName, Class clazz);
}
and xml mapper
<mapper namespace="ru.common.mapper.NamedEntityMapper">
<resultMap id="entity" type="${clazz}">
<id property="id" column="id"/>
<result property="name" column="name"/>
</resultMap>
<select id="getEntityById" parameterType="String" resultMap="entity">
SELECT * FROM ${tableName} WHERE id = #{id}
</select>
</mapper>
But I could not pass the return type as a parameter to the mapper. This can be done ? This will allow to remove a lot of duplicate code in my case.
Mybatis (as of version 3.3.5) has no elegant way to solve this.
You can eliminate some duplication using technics described below but
You can try to (somewhat) get rid of duplication in mapper interface by defining generic mapper like this:
interface CrudMapper<T> {
T getById(int id);
T getByName(String name);
void create(T);
void update(T);
}
And then use it to define individual mappers for entities:
interface AuthorMapper extends CrudMapper<Author> {}
interface WriterMapper extends CrudMapper<Writer> {}
You can also try to use discriminator to reuse result map:
<resultMap id="workerResult" type="Worker">
<id property="id" column="id" />
<result property="name" column="name"/>
<discriminator javaType="string" column="worker_type">
<case value="author" resultType="Author"/>
<case value="writer" resultType="Writer"/>
</discriminator>
</resultMap>
But it requires to complicate the query namely to add the new column worker_type to every select query:
<select id="getByName" parameterType="String" resultMap="workerResult">
SELECT 'actor' as worker_type, id, name FROM actors WHERE name = #{name}
</select>
Unfortunately there is no way to avoid creation of individual methods in xml mapper. The only thing you can do is to use velocity macros to have query in one place (namely in velocity macro). In this case method can look like this:
<select id="getByName" parameterType="String" resultMap="workerResult">
#select_by_name('actor')
</select>
And macro would be:
#macro(select_by_name $worker_table)
SELECT '${worker_table}' as worker_type, id, name FROM ${worker_table}s WHERE name = @name
Java API may be better in this regard but not without its own downsides.
public interface HierarchyMapper<T> {
@SelectProvider(method = "buildGetByName", type = HierarchySqlBuilder.class)
@Results(id = "workerResult", value = {
@Result(property = "id", column = "id", id = true),
@Result(property = "name", column = "name")
})
@TypeDiscriminator(cases = {
@Case(type = Actor.class, value = "actor"),
@Case(type = Writer.class, value = "writer")},
column = "worker_type")
T getByName(@Param("name") String name, @Param("table") String table);
}
@Mapper
public interface ActorMapper extends HierarchyMapper<Actor> {
}
public class HierarchySqlBuilder {
public static String buildGetByName(
@Param("name") String name, @Param("table") String table) {
return String.format(
"SELECT '%s' as worker_type, id, name from %s where name = #{name}", table, table);
}
}
Unfortunately I don't know the way to avoid passing table into the mapper. The problem here is that in this case we need to build dynamic query and the entity type (or table) is the parameter. Somewhere the dispatch should happen. One way is to have a repository layer above mappers which would do this dispatch like this:
class WorkerRepository {
@Autowired ActorMapper actorMapper;
@Autowired WriterMapper writerMapper;
public Actor getActorByName(String name) {
return actorMapper.getByNae(name, 'actor');
}
public Writer getWriterByName(String name) {
return writerMapper.getByNae(name, 'writer');
}
}
You may want to reconsider the problem. Given that all classes have the same fields you can store all data in one table and have a discriminator column like worker_type in that table to know the actual type of the object. In this case you avoid the problem altogether as you have one table, but still can get different classes in Java (probably having the common parent).
One thing you can try is spring-data-mybatis. It allows to annotate the entity:
@Entity
class Actor extends LongId {
private String name;
// getters and setters
}
@Entity
class Writer extends LongId {
private String name;
// getters and setters
}
And then define repository classes which are basically spring data repositories:
public interface AuthorRepository extends CrudRepository<Author, Long> {
List<Author> findByName(String name);
}
public interface WriterRepository extends CrudRepository<Writer, Long> {
List<Writer> findByName(String name);
}
In this case you do not create mappers manually at all and use CrudRepository in clients which used mybatis mappers before.
CrudRepository gives basic crud and additional automatically generated methods based on the method signature. For more details see spring-data documentation.