如题所述
1ãMyBatisä»ç»
MyBatis æ¬æ¯apacheçä¸ä¸ªå¼æºé¡¹ç®iBatis, 2010å¹´è¿ä¸ªé¡¹ç®ç±apache software foundation è¿ç§»å°äºgoogle codeï¼å¹¶ä¸æ¹å为MyBatis ã2013å¹´11æè¿ç§»å°Githubã
iBATISä¸è¯æ¥æºäºâinternetâåâabatisâçç»åï¼æ¯ä¸ä¸ªåºäºJavaçæä¹ å±æ¡æ¶ãiBATISæä¾çæä¹ å±æ¡æ¶å æ¬SQL MapsåData Access Objectsï¼DAOï¼
2ãCLOB
SQL CLOB æ¯å 置类åï¼å®å°å符大对象 (Character Large Object) åå¨ä¸ºæ°æ®åºè¡¨æä¸è¡ä¸çä¸ä¸ªåå¼ãé»è®¤æ åµä¸ï¼é©±å¨ç¨åºä½¿ç¨ SQL locator(CLOB) å®ç° Clob 对象ï¼è¿æå³ç CLOB 对象å å«ä¸ä¸ªæå SQL CLOB æ°æ®çé»è¾æéèä¸æ¯æ°æ®æ¬èº«ãClob 对象å¨å®è¢«å建çäºå¡å¤çæé´ææã
3ãMyBatis对CLOBç±»åæ°æ®å®ç°å¢å æ¹æ¥
oracle表ç»æ
create table T_USERS(
ID NUMBER not null,
NAME VARCHAR2(30),
SEX VARCHAR2(3),
BIRS DATE,
MESSAGE CLOB
)
create sequence SEQ_T_USERS_ID
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 20;
é ç½®mybatisé ç½®æ件UsersMapper.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN">
<mapper namespace="examples.mapper.UsersMapper" >
<!-- Result Map-->
<resultMap type="examples.bean.Users" id="BaseResultMap">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="birs" column="birs" jdbcType="TIMESTAMP"/>
<result property="message" column="message" jdbcType="CLOB"
javaType = "java.lang.String" typeHandler ="examples.service.OracleClobTypeHandler"/>
</resultMap>
<sql id="Tabel_Name">
t_users
</sql>
<!-- 表ä¸ææå -->
<sql id="Base_Column_List" >
id,name,sex,birs,message
</sql>
<!-- æ¥è¯¢æ¡ä»¶ -->
<sql id="Example_Where_Clause">
where 1=1
<trim suffixOverrides=",">
<if test="id != null">
and id = #{id}
</if>
<if test="name != null and name != ''">
and name like concat(concat('%', '${name}'), '%')
</if>
<if test="sex != null and sex != ''">
and sex like concat(concat('%', '${sex}'), '%')
</if>
<if test="birs != null">
and birs = #{birs}
</if>
<if test="message != null">
and message = #{message}
</if>
</trim>
</sql>
<!-- 2.æ¥è¯¢å表 -->
<select id="queryByList" resultMap="BaseResultMap" parameterType="Object">
select
<include refid="Base_Column_List" />
from t_users
<include refid="Example_Where_Clause"/>
</select>
</mapper>
Mapperç±»æ¥å£
package examples.mapper;import java.util.List;
public interface UsersMapper<T> {
public List<T> queryBySelective(T t);
public List<T> queryByList(T t);
}
ç±»å转æ¢å·¥å ·ç±»
package examples.service;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.CLOB;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
public class OracleClobTypeHandler implements TypeHandler<Object> {
public Object valueOf(String param) {
return null;
}
@Override
public Object getResult(ResultSet arg0, String arg1) throws SQLException {
CLOB clob = (CLOB) arg0.getClob(arg1);
return (clob == null || clob.length() == 0) ? null : clob.getSubString((long) 1, (int) clob.length());
}
@Override
public Object getResult(ResultSet arg0, int arg1) throws SQLException {
return null;
}
@Override
public Object getResult(CallableStatement arg0, int arg1) throws SQLException {
return null;
}
@Override
public void setParameter(PreparedStatement arg0, int arg1, Object arg2, JdbcType arg3) throws SQLException {
CLOB clob = CLOB.empty_lob();
clob.setString(1, (String) arg2);
arg0.setClob(arg1, clob);
}
}
Springé ç½®æ件
<?xml version="1.0" encoding="UTF-8"?><beans xmlns="
xmlns:xsi="
xmlns:mvc="
xmlns:tx="
xsi:schemaLocation="
default-autowire="byType">
<!-- é ç½®æ°æ®æº -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
<property name="url"><value>jdbc:oracle:thin:@127.0.0.1:1521:pms</value></property>
<property name="username"><value>pms</value></property>
<property name="password"><value>pms</value></property>
</bean>
<!-- é å®æ°æ®æº å æ¥æç sqlæ å°æ件 sqlSessionFactory ä¹å¯ä»¥è®¿é®æ°æ®åº åæ¥æ sqlæä½è½åäº -->
<!--
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations">
<list>
<value>classpath:examples/mybatis/oracle/UsersMapper.xml</value>
</list>
</property>
</bean>
<!-- éè¿è®¾ç½® mapperInterfaceå±æ§ï¼ä½¿æ¥å£æå¡bean å对åºxmlæ件管ç å¯ä»¥ä½¿ç¨å ¶ä¸çsql -->
<bean id="dao" class="org.mybatis.spring.mapper.MapperFactoryBean">
<!-- æ¤å¤çåäº Mybatis ä¸ ServerDao serverDao = sqlSession.getMapper(ServerDao.class); æææ å°å ³ç³» -->
<property name="mapperInterface" value="examples.mapper.UsersMapper" />
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
</beans>
æµè¯ç±»
package examples.service;import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import examples.bean.Users;
import examples.mapper.UsersMapper;
public class TestUsersService {
@SuppressWarnings("unchecked")
public static void main(String[] args) throws ParseException {
ApplicationContext ac =
new ClassPathXmlApplicationContext("classpath:/examples/service/spring.xml");
UsersMapper<Users> dao = (UsersMapper<Users>)ac.getBean("dao");
//æ¥è¯¢
Users nullBean = new Users();
List<Users> list = dao.queryByList(nullBean);
if(list != null) {
for(Users user : list) {
System.out.println(user);
}
}
}
}
例子:
表结构
CREATE TABLE USERINFO(USERID VARCHAR2(5),USERNAME VARCHAR2(20),
MEMO CLOB,
constraint PK_USERINFO primary key(USERID));
java代码:
public class OracleClobTypeHandlerCallback implements TypeHandlerCallback {public void setParameter(ParameterSetter setter, Object obj)
throws SQLException {
CLOB clob = CLOB.empty_lob();
clob.setString(1, (String)obj);
setter.setClob(clob);
}
public Object getResult(ResultGetter getter) throws SQLException {
CLOB clob = (CLOB) getter.getClob();
return (clob == null || clob.length() == 0 )? null :clob.getSubString((long)1, (int)clob.length());
}
public Object valueOf(String param) {
return null;
}
}
sqlmap配置:
<resultMap id="userResult" class="com.prs.application.ehld.sample.common.dto.UserInfoDTO"><result property="userID" column="USERID" columnIndex="1"/>
<result property="userName" column="USERNAME" columnIndex="2"/>
<result property="memo" column="memo" jdbcType="CLOB" javaType = "java.lang.String" typeHandler =" OracleClobTypeHandlerCallback "/>
</resultMap>