MyBatis简单的增删改查以及简单的分页查询实现
jopen
10年前
MyBatis简单的增删改查以及简单的分页查询实现
<?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; } }