Mybatis实现增删改查及分页查询的方法

MyBatis的前身就是iBatis。是一个数据持久层(ORM)框架。 MyBatis是支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC 代码和参数的手工设置以及结果集的检索。MyBatis使用简单的XML或注解用于配置和原始映射，将接口和Java 的POJOs（Plan Old Java Objects，普通的Java 对象）映射成数据库中的记录。每个 MyBatis应用程序主要都是使用SqlSessionFactory实例的，一个 SqlSessionFactory实例可以通过SqlSessionFactoryBuilder获得。

具体代码如下所示：

				?

									<?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> 

									<!-- give a alias for model --> 

									<typeAlias alias="goods" type="com.clark.model.Goods"></typeAlias> 

									</typeAliases> 

									<environments default="development"> 

									<environment id="development"> 

									<transactionManager type="JDBC" /> 

									<dataSource type="POOLED"> 

									<property name="driver" value="oracle.jdbc.driver.OracleDriver" /> 

									<property name="url" value="jdbc:oracle:thin:@172.30.0.125:1521:oradb01" /> 

									<property name="username" value="settlement" /> 

									<property name="password" value="settlement" /> 

									</dataSource> 

									</environment> 

									</environments> 

									<mappers> 

									<mapper resource="com/clark/model/goodsMapper.xml" /> 

									</mappers> 

									</configuration>

									<?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="clark"> 

									<!-- 将db查询出来的结果映射到Model--Goods --> 

									<resultMap type="com.clark.model.Goods" id="t_good"> 

									<id column="id" property="id"/> 

									<result column="cate_id" property="cateId"/> 

									<result column="name" property="name"/> 

									<result column="price" property="price"/> 

									<result column="description" property="description"/> 

									<result column="order_no" property="orderNo"/> 

									<result column="update_time" property="updateTime"/> 

									</resultMap> 

									<!-- 根据id查询 返回Goods类型 <typeAlias alias="goods" type="com.clark.model.Goods"></typeAlias>--> 

									<!--resultMap 和 resultType的使用区别--> 

									<select id="selectGoodById" parameterType="int" resultType="goods"> 

									select id,cate_id,name,price,description,order_no,update_time 

									from goods where id = #{id} 

									</select> 

									<!-- 查询所有Goods 返回resultMap类型--> 

									<select id="selectAllGoods" resultMap="t_good"> 

									select id,cate_id,name,price,description,order_no,update_time from goods 

									</select> 

									<!-- 指定parameterType=map 其中map的形式为Map<String,PageBean> map--> 

									<select id="selectGoodsByPage" resultMap="t_good" parameterType="map"> 

									<!-- order by id asc是指对查询后的结果进行升序排序 --> 

									<![CDATA[ 

									select * from 

									(select g.*,rownum rn from (select * from goods) g where 1=1 and rownum <= #{pageBean.endNumber}) 

									where rn >= #{pageBean.startNumber}

									order by id asc

									]]> 

									</select> 

									<!-- 新增Goods 参数类型为Goods--> 

									<insert id="insertGood" parameterType="goods"> 

									insert into goods(id,cate_id,name,price,description,order_no,update_time) 

									values(#{id},#{cateId},#{name},#{price},#{description},#{orderNo},#{updateTime}) 

									</insert> 

									<!-- 更新Goods 参数类型为Goods--> 

									<update id="updateGood" parameterType="goods"> 

									update goods g 

									set g.name = #{name},g.order_no =#{orderNo} 

									where g.id = #{id} 

									</update> 

									<!-- 删除Goods 参数类型为int--> 

									<delete id="deleteGood" parameterType="int"> 

									delete from goods g 

									where g.id = #{id} 

									</delete> 

									</mapper>

									package com.clark.model; 

									import java.util.Date; 

									public class Goods { 

									private Integer id; 

									private Integer cateId; 

									private String name; 

									private double price; 

									private String description; 

									private Integer orderNo; 

									private Date updateTime; 

									public Goods(){ 

									} 

									public Goods(Integer id, Integer cateId, String name, double price, 

									String description, Integer orderNo, Date updateTime) { 

									super(); 

									this.id = id; 

									this.cateId = cateId; 

									this.name = name; 

									this.price = price; 

									this.description = description; 

									this.orderNo = orderNo; 

									this.updateTime = updateTime; 

									} 

									public Integer getId() { 

									return id; 

									} 

									public void setId(Integer id) { 

									this.id = id; 

									} 

									public Integer getCateId() { 

									return cateId; 

									} 

									public void setCateId(Integer cateId) { 

									this.cateId = cateId; 

									} 

									public String getName() { 

									return name; 

									} 

									public void setName(String name) { 

									this.name = name; 

									} 

									public double getPrice() { 

									return price; 

									} 

									public void setPrice(double price) { 

									this.price = price; 

									} 

									public String getDescription() { 

									return description; 

									} 

									public void setDescription(String description) { 

									this.description = description; 

									} 

									public Integer getOrderNo() { 

									return orderNo; 

									} 

									public void setOrderNo(Integer orderNo) { 

									this.orderNo = orderNo; 

									} 

									public Date getTimeStamp() { 

									return updateTime; 

									} 

									public void setTimeStamp(Date updateTime) { 

									this.updateTime = updateTime; 

									} 

									@Override

									public String toString() { 

									return "[goods include:Id="+this.getId()+",name="+this.getName()+ 

									",orderNo="+this.getOrderNo()+",cateId="+this.getCateId()+ 

									",updateTime="+this.getTimeStamp()+"]"; 

									} 

									}

									package com.clark.model; 

									//模拟的一个分页对象PageBean 

									public class PageBean { 

									//开始数 

									private Integer startNumber; 

									//结束数 

									private Integer endNumber; 

									public PageBean(){ 

									} 

									public PageBean(Integer startNumber, Integer endNumber) { 

									super(); 

									this.startNumber = startNumber; 

									this.endNumber = endNumber; 

									} 

									public Integer getStartNumber() { 

									return startNumber; 

									} 

									public void setStartNumber(Integer startNumber) { 

									this.startNumber = startNumber; 

									} 

									public Integer getEndNumber() { 

									return endNumber; 

									} 

									public void setEndNumber(Integer endNumber) { 

									this.endNumber = endNumber; 

									} 

									}

									package com.clark.mybatis; 

									import java.io.IOException; 

									import java.io.Reader; 

									import java.util.HashMap; 

									import java.util.List; 

									import java.util.Map; 

									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.clark.model.Goods; 

									import com.clark.model.PageBean; 

									public class TestGoods { 

									public static void main(String[] args) throws IOException { 

									String resource = "configuration.xml"; 

									Reader reader = null; 

									SqlSessionFactory sessionFactory = null; 

									SqlSession session = null; 

									try { 

									reader = Resources.getResourceAsReader(resource); 

									sessionFactory = new SqlSessionFactoryBuilder().build(reader); 

									session = sessionFactory.openSession(); 

									PageBean pageBean = new PageBean(8, 20); 

									Map<String,PageBean> map = new HashMap<String, PageBean>(); 

									map.put("pageBean", pageBean); 

									List<Goods> gs = findGoodsByPage(session,map); 

									for (Goods goods2 : gs) { 

									System.out.println(goods2.toString()); 

									} 

									} catch (IOException e) { 

									e.printStackTrace(); 

									}finally{ 

									session.close(); 

									reader.close(); 

									} 

									} 

									//find by id 

									public static Goods findGoodById(SqlSession session,Integer id){ 

									//clark对应着goodMapper.xml配置文件中的namespace name="clark" 

									Goods goods = (Goods)session.selectOne("clark.selectGoodById", id); 

									return goods; 

									} 

									//find all 

									public static List<Goods> findAllGoods(SqlSession session){ 

									List<Goods> goods = session.selectList("clark.selectAllGoods"); 

									return goods; 

									} 

									public static List<Goods> findGoodsByPage(SqlSession session,Map<String,PageBean> map){ 

									List<Goods> goods = session.selectList("clark.selectGoodsByPage",map); 

									return goods; 

									} 

									//insert a goods 

									public static int insertGoods(SqlSession session,Goods goods){ 

									int result = session.insert("clark.insertGood", goods); 

									session.commit(); 

									return result; 

									} 

									//update goods 

									public static int updateGoods(SqlSession session,Goods goods){ 

									int result = session.update("clark.updateGood", goods); 

									session.commit(); 

									return result; 

									} 

									//delete goods 

									public static int deleteGood(SqlSession session,Integer id){ 

									int result = session.delete("clark.deleteGood", id); 

									session.commit(); 

									return result; 

									} 

									}