myBatis mapping example from docs within oracle procedure

1. I just want to show how to setup complete batis project. I’ll be using example from docs. As you can read there you need at least 4 files at least:

• Configuration.xml
• BlogMapper.xml
• BlogBean.java (session bean)
• Blog.java (domain class)

So then you have ready and working object-relational mapping you can easily publish as web service by adding annotation @webservice.

Configuration.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias alias="Blog" type="pl.centertel.tis.soa.adapte.db.smart.domain.Blog"/>
    </typeAliases>
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="MANAGED"/>
            <dataSource type="JNDI">
                <property name="data_source" value="jdbc/__SMART"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="BlogMapper.xml"/>
    </mappers>
</configuration>

BlogMapper.xml

<?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="BlogMapper">
    <select id="selectBlog" parameterType="int" resultType="Blog">
select * from Blog where id = #{id}
    </select>
</mapper>

BlogBean.java (session bean)

@Stateless
@WebService
public class BlogBean implements BlogLocal {

    private SqlSessionFactory factory = null;
    private static final Logger logger = Logger.getLogger(BlogBean.class.getName());

    public BlogBean() {
        try {
            Reader reader = Resources.getResourceAsReader("Configuration.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            factory = builder.build(reader);
        } catch (IOException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
    }

    @WebMethod(operationName = "getBlog")
    public String getBlog(@WebParam(name = "id") String id) {
        String name = null;
        SqlSession session = factory.openSession();
        try {
            Blog blog = (Blog) session.selectOne("BlogMapper.selectBlog", 101);
            name = blog.getName();
        } finally {
            session.close();
        }
        return name;
    }
}

Blog.java (domain class)

public class Blog {

    private String Name;
    private int id;

    public String getName() {
        return Name;
    }

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

    public int getId() {
        return id;
    }

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

Except those you will need and interface for bean but that will be generated for you by your favorite IDE tool :P

2. What if you want to call an stored oracle procedure returning ie. result set??

1. you have to change your mapper.xml
2. you have to change domain class for mapping data
3. you need to add middle class let’s call it params for catching result set into one variable

New mapper would look like this:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
    PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
    "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace=BlogMapper">
    <resultMap id="rsGetBlog" type="Blog">
        <result property="ID" column="ID" />
        <result property="USR" column="USR" />
        <result property="USR_EMAIL" column="USR_EMAIL" />
    </resultMap>
    <select id="getBlog" statementType="CALLABLE" parameterType="Params" resultMap="rsGetBlog">
        {#{out, jdbcType=CURSOR, mode=OUT, javaType=java.sql.ResultSet,resultMap=rsGetBlog} = call BLOGGING.GETBLOG(#{in})}
    </select>
</mapper>

New domain class would look like this: (simple domain .java class with getters and setters)
Params:

public class Params {
    private String in;
    private Object out;

    public String getIn() {
        return in;
    }

    public void setIn(String in) {
        this.in = in;
    }

    public Object getOut() {
        return out;
    }

    public void setOut(Object out) {
        this.out = out;
    }

    @Override
    public String toString() {
        return "[in="+in+"][out="+out+"]";
    }
}

Of course you would need to fix all the imports :) Example bean method could like this:

@WebMethod  public List getBlog(@WebParam(name = "id") String name) {
SqlSession session = factory.openSession();
Params params = new Params();
params.setIn(name);
session.selectList("BlogMapper.getBlog", params);
List liBlog= (List) params.getOut();
session.close();
return liBlog;
}