oracle是CLOB类型mybatis,Mybatis之Oracle增删查改示例（含Date、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

t_users

id,name,sex,birs,message

where 1=1

and id = #{id}

and name like concat(concat('%', '${name}'), '%')

and sex like concat(concat('%', '${sex}'), '%')

and birs = #{birs}

and message = #{message}

select seq_t_users_id.nextval as id from dual

insert into t_users(id,name,sex,birs,message) values(#{id},#{name},#{sex},#{birs},#{message,jdbcType=CLOB})

update t_users set name=#{name},sex=#{sex},birs=#{birs},message=#{message} where id=#{id}

update t_users set

name=#{name},

sex=#{sex},

birs=#{birs},

message=#{message}, where id=#{id}

delete from t_users where id = #{id}

select

from t_users where id = #{id}

select

from t_users

select count(1) from t_users

select

from t_users

Mapper类接口

package examples.mapper;

import java.util.List;

public interface UsersMapper{

public void add(T t);

public void update(T t);

public void updateBySelective(T t);

public void delete(Object id);

public T queryById(Object id);

public ListqueryBySelective(T t);

public int queryByCount(T t);

public ListqueryByList(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{

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配置文件

oracle.jdbc.driver.OracleDriver

jdbc:oracle:thin:@127.0.0.1:1521:pms

pms

pms

classpath:examples/mybatis/oracle/UsersMapper.xml

测试类

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");

UsersMapperdao = (UsersMapper)ac.getBean("dao");

//删除表中所有信息

Users nullBean = new Users();

ListdelList = dao.queryByList(nullBean);

if(delList != null) {

for(Users user : delList) {

dao.delete(user.getId());

}

}

//新增

Users bean = new Users();

bean.setName("ding");

bean.setSex("男");

bean.setBirs(new SimpleDateFormat("yyyy-MM-dd").parse("1985-01-01"));

bean.setMessage("This is Clob!");

dao.add(bean);

Listlist = dao.queryByList(nullBean);

if(list != null) {

for(Users user : list) {

System.out.println(user);

}

}

//查询并更新

bean = new Users();

bean.setName("ding");

ListqueList = dao.queryByList(bean);

if(queList != null) {

for(Users user : list) {

user.setSex("女");

dao.updateBySelective(user);

}

}

list = dao.queryByList(nullBean);

if(list != null) {

for(Users user : list) {

System.out.println(user);

}

}

//查询并更新

bean = new Users();

bean.setName("ding");

ListqueList2 = dao.queryByList(bean);

if(queList != null) {

for(Users user : queList2) {

user.setSex("男");

user.setMessage("");

dao.update(user);

}

}

list = dao.queryByList(nullBean);

if(list != null) {

for(Users user : list) {

System.out.println(user);

}

}

int num = dao.queryByCount(nullBean);

System.out.println("num=" + num);

}

}