mybatis连接Oracle做增删改查

入门请看http://blog.csdn.net/liumengcheng/article/details/34409783

首先创建表

create table T_USERS(  ID      NUMBER not null,  NAME    VARCHAR2(30),  SEX     VARCHAR2(3),  BIRS    DATE,  MESSAGE CLOB);create sequence SEQ_T_USERS_IDminvalue 1maxvalue 99999999start with 1increment by 1cache 20;

User.java

package com.southgis.scout.demo;import java.util.Date;/** * 用户类 *  * @author lmc *  */public class User {private String name;private String sex;private Integer id;private Date birs;private String message;public String getMessage() {return message;}public void setMessage(String pMessage) {this.message = pMessage;}public Date getBirs() {return birs;}public void setBirs(Date pbirs) {this.birs = pbirs;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String psex) {this.sex = psex;}public Integer getId() {return id;}public void setID(Integer pid) {this.id = pid;}public User() {}}

UsersMapper.java

package com.southgis.scout.demo;import java.util.List;import com.southgis.scout.demo.User;  public interface UsersMapper {public void add(User t);public void update(User t);public void updateBySelective(User t);public void delete(Object id);public User queryById(Object id);public List<User> queryBySelective(User t);public int queryByCount(User t);public List<User> queryByList(User t);}

OracleClobTypeHandler.java

package com.southgis.scout.demo;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;}@Overridepublic 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());}@Overridepublic Object getResult(ResultSet arg0, int arg1) throws SQLException {return null;}@Overridepublic Object getResult(CallableStatement arg0, int arg1) throws SQLException {return null;}@Overridepublic 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);}}

main.java

package com.southgis.scout.demo;import java.io.IOException;  import java.text.DateFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.List;import java.util.Date;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder;  import com.southgis.scout.demo.UsersMapper;  import com.southgis.scout.demo.User;  public class main {/** * 获得MyBatis SqlSessionFactory   * SqlSessionFactory负责创建SqlSession，一旦创建成功，就可以用SqlSession实例来执行映射语句，commit，rollback，close等方法。 * @return */private static SqlSessionFactory getSessionFactory() {SqlSessionFactory sessionFactory = null;String resource = "configuration.xml";try {sessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader(resource));} catch (IOException e) {e.printStackTrace();}return sessionFactory;}public static void main(String[] args) {SqlSession sqlSession = getSessionFactory().openSession();UsersMapper dao = sqlSession.getMapper(UsersMapper.class);//删除表中所有信息User nullBean = new User();List<User> delList = dao.queryByList(nullBean);if(delList != null) {for(User user : delList) {dao.delete(user.getId());}}DateFormat dd=new SimpleDateFormat("yyyy-MM-dd");Date date=null;try {date = dd.parse("1985-01-01");} catch (ParseException e) {e.printStackTrace();}//新增User bean = new User();bean.setName("ding");bean.setSex("男");bean.setBirs(date);bean.setMessage("This is Clob!");dao.add(bean);List<User> list = dao.queryByList(nullBean);if(list != null) {for(User user : list) {System.out.println(user.getName()+user.getMessage());}}//查询并更新bean = new User();bean.setName("ding");List<User> queList = dao.queryByList(bean);if(queList != null) {for(User user : list) {user.setSex("女");dao.updateBySelective(user);}}list = dao.queryByList(nullBean);if(list != null) {for(User user : list) {System.out.println(user.getSex());}}//查询并更新bean = new User();bean.setName("ding");List<User> queList2 = dao.queryByList(bean);if(queList != null) {for(User user : queList2) {user.setSex("男");user.setMessage("");dao.update(user);}}list = dao.queryByList(nullBean);if(list != null) {for(User user : list) {System.out.println(user.getSex());}}int num = dao.queryByCount(nullBean);System.out.println("num=" + num);//默认是不提交的，所以这里手工提交sqlSession.commit();}}

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><!--给实体类起一个别名 user 不过建议不要起别名，容易搞混--><typeAlias type="com.southgis.scout.demo.User" alias="User" /></typeAliases><!--数据源配置  这块用 Oracle数据库 --><environments default="development"><environment id="development"><transactionManager type="jdbc" /><dataSource type="POOLED"><property name="driver" value="oracle.jdbc.OracleDriver" /><property name="url" value="jdbc:oracle:thin:@192.168.56.102:1521:orcl" /><property name="username" value="scott" /><property name="password" value="tiger" /></dataSource></environment></environments><mappers><!--UsersMapper.xml装载进来  同等于把“dao”的实现装载进来 --><mapper resource="UsersMapper.xml" /></mappers></configuration> 

UsersMapper.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">  <!--这块等于dao接口的实现  namespace必须和接口的类路径一样--><mapper namespace="com.southgis.scout.demo.UsersMapper" ><!-- Result Map--><resultMap type="com.southgis.scout.demo.User" 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 ="com.southgis.scout.demo.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><!-- 下面的id都和接口UsersMapper中的方法名一样--><!-- 1.新增记录 --><insert id="add" parameterType="Object" > <selectKey resultType="int" order="BEFORE" keyProperty="id">select seq_t_users_id.nextval as id from dual</selectKey>insert into t_users(id,name,sex,birs,message) values(#{id},#{name},#{sex},#{birs},#{message,jdbcType=CLOB})</insert><!-- 2.根据id修改记录-->  <update id="update" parameterType="Object" >update t_users set name=#{name},sex=#{sex},birs=#{birs},message=#{message} where id=#{id}</update><!-- 3.只修改不为空的字段 --><update id="updateBySelective" parameterType="Object" >update t_users set <trim  suffixOverrides="," ><if test="name != null  and name != '' ">name=#{name},</if><if test="sex != null  and sex != '' ">sex=#{sex},</if><if test="birs != null  ">birs=#{birs},</if><if test="message != null  and message != '' ">message=#{message},</if></trim> where id=#{id}</update><!-- 4.根据id进行删除 --><delete id="delete" parameterType="Object">delete from t_users where id = #{id}</delete><!-- 5.根据id查询 --><select id="queryById" resultMap="BaseResultMap" parameterType="Object">select<include refid="Base_Column_List" />from t_users where id = #{id}</select><!-- 6.查询列表，只查询不为空的字段 --><select id="queryBySelective" resultMap="BaseResultMap" parameterType="Object">select<include refid="Base_Column_List" />from t_users<include refid="Example_Where_Clause" /></select><!-- 7.列表总数 --><select id="queryByCount" resultType="java.lang.Integer" parameterType="Object">select count(1) from t_users<include refid="Example_Where_Clause" /></select><!-- 8.查询列表 --><select id="queryByList" resultMap="BaseResultMap" parameterType="Object">select<include refid="Base_Column_List" />from t_users <include refid="Example_Where_Clause"/></select></mapper>