二、mybatis快速入门
2.1、准备开发环境
1、创建测试项目，普通java项目或者是JavaWeb项目均可，如下图所示：
2、添加相应的jar包
【mybatis】
mybatis-3.1.1.jar
【MYSQL驱动包】
mysql-connector-java-5.1.7-bin.jar
3、创建数据库和表，针对MySQL数据库
SQL脚本如下：
1 create database mybatis;
2 use mybatis;
3 CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);
4 INSERT INTO users(NAME, age) VALUES('孤傲苍狼', 27);
5 INSERT INTO users(NAME, age) VALUES('白虎神皇', 27);
将SQL脚本在MySQL数据库中执行，完成创建数据库和表的操作，如下：
到此，前期的开发环境准备工作全部完成。
2.2、使用MyBatis查询表中的数据
1、添加Mybatis的配置文件conf.xml
在src目录下创建一个conf.xml文件，如下图所示：
conf.xml文件中的内容如下：
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
3 <configuration>
4 <environments default="development">
5 <environment id="development">
6 <transactionManager type="JDBC" />
7 <!-- 配置数据库连接信息 -->
8 <dataSource type="POOLED">
9 <property name="driver" value="com.mysql.jdbc.Driver" />
10 <property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
11 <property name="username" value="root" />
12 <property name="password" value="XDP" />
13 </dataSource>
14 </environment>
15 </environments>
16
17 </configuration>
2、定义表所对应的实体类，如下图所示：
User类的代码如下：
1 package me.gacl.domain;
2
3 /**
4 * @author gacl
5 * users表所对应的实体类
6 */
7 public class User {
8
9 //实体类的属性和表的字段名称一一对应
10 private int id;
11 private String name;
12 private int age;
13
14 public int getId() {
15 return id;
16 }
17
18 public void setId(int id) {
19 this.id = id;
20 }
21
22 public String getName() {
23 return name;
24 }
25
26 public void setName(String name) {
27 this.name = name;
28 }
29
30 public int getAge() {
31 return age;
32 }
33
34 public void setAge(int age) {
35 this.age = age;
36 }
37
38 @Override
39 public String toString() {
40 return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
41 }
42 }
3、定义操作users表的sql映射文件userMapper.xml
创建一个me.gacl.mapping包，专门用于存放sql映射文件，在包中创建一个userMapper.xml文件，如下图所示：
userMapper.xml文件的内容如下：
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <!-- 为这个mapper指定一个唯一的namespace，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的
4 例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)
5 -->
6 <mapper namespace="me.gacl.mapping.userMapper">
7 <!-- 在select标签中编写查询的SQL语句， 设置select标签的id属性为getUser，id属性值必须是唯一的，不能够重复
8 使用parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型
9 resultType="me.gacl.domain.User"就表示将查询结果封装成一个User类的对象返回
10 User类就是users表所对应的实体类
11 -->
12 <!--
13 根据id查询得到一个user对象
14 -->
15 <select id="getUser" parameterType="int"
16 resultType="me.gacl.domain.User">
17 select * from users where id=#{id}
18 </select>
19 </mapper>
4、在conf.xml文件中注册userMapper.xml文件
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
3 <configuration>
4 <environments default="development">
5 <environment id="development">
6 <transactionManager type="JDBC" />
7 <!-- 配置数据库连接信息 -->
8 <dataSource type="POOLED">
9 <property name="driver" value="com.mysql.jdbc.Driver" />
10 <property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
11 <property name="username" value="root" />
12 <property name="password" value="XDP" />
13 </dataSource>
14 </environment>
15 </environments>
16
17 <mappers>
18 <!-- 注册userMapper.xml文件，
19 userMapper.xml位于me.gacl.mapping这个包下，所以resource写成me/gacl/mapping/userMapper.xml-->
20 <mapper resource="me/gacl/mapping/userMapper.xml"/>
21 </mappers>
22
23 </configuration>
5、编写测试代码：执行定义的select语句
创建一个Test1类，编写如下的测试代码：
package me.gacl.test;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import me.gacl.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class Test1 {
public static void main(String[] args) throws IOException {
//mybatis的配置文件
String resource = "conf.xml";
//使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）
InputStream is = Test1.class.getClassLoader().getResourceAsStream(resource);
//构建sqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）
//Reader reader = Resources.getResourceAsReader(resource);
//构建sqlSession的工厂
//SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
//创建能执行映射文件中sql的sqlSession
SqlSession session = sessionFactory.openSession();
/**
* 映射sql的标识字符串，
* me.gacl.mapping.userMapper是userMapper.xml文件中mapper标签的namespace属性的值，
* getUser是select标签的id属性值，通过select标签的id属性值就可以找到要执行的SQL
*/
String statement = "me.gacl.mapping.userMapper.getUser";//映射sql的标识字符串
//执行查询返回一个唯一user对象的sql
User user = session.selectOne(statement, 1);
System.out.println(user);
}
}
执行结果如下：
可以看到，数据库中的记录已经成功查询出来了。
http://www.linuxidc.com/Linux/2014-06/103456.html Java实战应用:Mybatis实现单表的增删改
http://www.linuxidc.com/Linux/2014-04/99889.html [Java][Mybatis]物理分页实现
http://www.linuxidc.com/Linux/2013-06/85762.html Mybatis快速入门教程
http://www.linuxidc.com/Linux/2014-02/96916.html Mybatis中对List<Object> 对象List的批处理插入操作
订单事例：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://code.mysteeltech.com/schema/mybatis/mybatis-3-mapper.dtd">
<mapper namespace="com.banksteel.order.dao.impl.OrderDaoImpl">
<sql id="t_order">trade_order</sql>
<sql id="t_order_detail">trade_order_detail</sql>
<resultMap id="beanMap" type="order">
<id column="id" property="id" />
<result column="parent_contract_code" property="parentContractCode" />
<result column="contract_code" property="contractCode" />
<result column="sell_contract_code" property="sellContractCode" />
<result column="contract_time" property="contractTime" />
<result column="sum_qty" property="sumQty" />
<result column="sum_amt" property="sumAmt" />
<result column="source" property="source" />
<result column="pay_type" property="payType" />
<result column="agent_type" property="agentType" />
<result column="platform" property="platform" />
<result column="ip" property="ip" />
<result column="creat_time" property="creatTime" />
<result column="pay_time" property="payTime" />
<result column="last_pay_time" property="lastPayTime" />
<result column="goods_member_id" property="goodsMemberId" />
<result column="goods_member_name" property="goodsMemberName" />
<result column="user_id" property="userId" />
<result column="user_name" property="userName" />
<result column="member_id" property="memberId" />
<result column="member_name" property="memberName" />
<result column="shop_id" property="shopId" />
<result column="shop_name" property="shopName" />
<result column="admin_id" property="adminId" />
<result column="admin_depart_id" property="adminDepartId" />
<result column="need_logistics" property="needLogistics" />
<result column="need_machines" property="needMachines" />
<result column="status" property="status" />
<result column="sub_status" property="subStatus" />
<result column="url" property="url" />
<result column="note" property="note" />
<result column="last_access" property="lastAccess" />
</resultMap>
<insert id="insert" parameterType="order" useGeneratedKeys="true" keyProperty="id">
INSERT INTO <include refid="t_order"/>
(parent_contract_code, contract_code, sell_contract_code, contract_time, sum_qty, sum_amt, source, pay_type, agent_type,
platform, ip, creat_time, pay_time, last_pay_time, goods_member_id, goods_member_name, user_id, user_name, member_id,
member_name, shop_id, shop_name, admin_id, admin_depart_id, need_logistics, need_machines, status, sub_status, note, version, last_access)
VALUES
(#{parentContractCode}, #{contractCode}, #{sellContractCode}, #{contractTime}, #{sumQty}, #{sumAmt}, #{source}, #{payType}, #{agentType},
#{platform}, #{ip}, #{creatTime}, #{payTime}, #{lastPayTime}, #{goodsMemberId}, #{goodsMemberName}, #{userId}, #{userName}, #{memberId},
#{memberName},#{shopId}, #{shopName}, #{adminId}, #{adminDepartId}, #{needLogistics}, #{needMachines}, #{status}, #{subStatus}, #{note}, #{version}, #{lastAccess})
</insert>
<select id="nextSequence" resultType="String" parameterType="map">
SELECT LPAD(next_sequence(#{date}),#{place},'0');
</select>
<update id="update" parameterType="order">
UPDATE
<include refid="t_order"/>
<set>
parent_contract_code = #{parentContractCode},
contract_code = #{contractCode},
sell_contract_code = #{sellContractCode},
contract_time = #{contractTime},
sum_qty = #{sumQty},
sum_amt = #{sumAmt},
source = #{source},
pay_type = #{payType},
agent_type = #{agentType},
platform = #{platform},
ip = #{ip},
creat_time = #{creatTime},
pay_time = #{payTime},
last_pay_time = #{lastPayTime},
user_id = #{userId},
user_name = #{userName},
member_id = #{memberId},
member_name = #{memberName},
admin_id = #{adminId},
admin_depart_id = #{adminDepartId},
need_logistics = #{needLogistics},
need_machines = #{needMachines},
status = #{status},
sub_status = #{subStatus},
url = #{url},
note = #{note},
version = #{version},
last_access = #{lastAccess}
</set>
<where>
id = #{id}
</where>
</update>
<select id="getById" parameterType="long" resultMap="beanMap">
SELECT * FROM <include refid="t_order"/> WHERE id = #{id}
</select>
<select id="listBy" parameterType="map" resultMap="beanMap">
SELECT * FROM <include refid="t_order"/> o
<where>
<if test="parentContractCode != null">
AND o.parent_contract_code = #{parentContractCode}
</if>
<if test="contractCode != null">
AND o.contract_code = #{contractCode}
</if>
<if test="startContractTime != null">
AND o.contract_time >= #{startContractTime}
</if>
<if test="endContractTime != null">
AND o.contract_time <= #{endContractTime}
</if>
<if test="startCreateTime != null">
AND o.creat_time >= #{startCreateTime}
</if>
<if test="endCreateTime != null">
AND o.creat_time <= #{endCreateTime}
</if>
<if test="status != null">
AND o.status >= #{status}
</if>
<if test="userId != null">
AND o.user_id = #{userId}
</if>
<if test="shopId != null">
AND o.shop_id = #{shopId}
</if>
</where>
ORDER BY id DESC
</select>
<select id="listPage" parameterType="map" resultMap="beanMap">
SELECT o.* FROM <include refid="t_order"/> o
<if test="platform != null or breed != null or breedAndContractCode != null">
LEFT JOIN <include refid="t_order_detail"/> d ON o.id = d.order_id
</if>
<where>
<if test="parentContractCode != null">
AND o.parent_contract_code LIKE #{parentContractCode}
</if>
<if test="contractCode != null">
AND o.contract_code LIKE #{contractCode}
</if>
<if test="startContractTime != null">
AND o.contract_time >= #{startContractTime}
</if>
<if test="endContractTime != null">
AND o.contract_time <= #{endContractTime}
</if>
<if test="startCreateTime != null">
AND o.creat_time >= #{startCreateTime}
</if>
<if test="endCreateTime != null">
AND o.creat_time <= #{endCreateTime}
</if>
<if test="status != null">
AND o.status = #{status}
</if>
<if test="subStatus != null">
AND o.sub_status = #{subStatus}
</if>
<if test="auitStatus == 10">
AND o.sub_status = #{auitStatus}
</if>
<if test="auitStatus == 90">
AND o.sub_status = #{auitStatus}
</if>
<if test="auitStatus == 100">
AND (o.sub_status != 10 AND o.sub_status != 90)
</if>
<if test="buyAdminId != null">
AND o.admin_id = #{buyAdminId}
</if>
<if test="buyerAdminDepartId != null">
AND o.admin_depart_id LIKE CONCAT(#{buyerAdminDepartId},'%')
</if>
<if test="memberId != null">
AND o.member_id = #{memberId}
</if>
<if test="shopId != null">
AND o.shop_id = #{shopId}
</if>
<if test="userId != null">
AND o.user_id = #{userId}
</if>
<if test="platform != null">
AND d.platform = #{platform}
</if>
<if test="breed != null">
AND d.breed = #{breed}
</if>
<if test="breedAndContractCode != null">
AND (o.contract_code LIKE #{breedAndContractCode} OR d.breed LIKE #{breedAndContractCode})
</if>
<if test="depts != null">
AND
<foreach item="depId" collection="depts" open="(" separator="OR" close=")">
o.admin_depart_id LIKE CONCAT(#{depId}, '%')
</foreach>
</if>
</where>
GROUP BY o.id
ORDER BY
<if test="orderField != null">
<if test="orderModel == 0">
o.${orderField} ASC, o.id DESC
</if>
<if test="orderModel == 1">
o.${orderField} DESC, o.id DESC
</if>
</if>
<if test="orderField == null">
o.id DESC
</if>
<if test="startRowNum != null">
LIMIT #{startRowNum}, #{pageSize}
</if>
</select>
<select id="listPageCount" parameterType="map" resultType="long">
SELECT COUNT(*) FROM (SELECT o.id FROM <include refid="t_order"/> o
<if test="platform != null or breed != null or breedAndContractCode != null">
LEFT JOIN <include refid="t_order_detail"/> d ON o.id = d.order_id
</if>
<where>
<if test="parentContractCode != null">
AND o.parent_contract_code LIKE #{parentContractCode}
</if>
<if test="contractCode != null">
AND o.contract_code LIKE #{contractCode}
</if>
<if test="startContractTime != null">
AND o.contract_time >= #{startContractTime}
</if>
<if test="endContractTime != null">
AND o.contract_time <= #{endContractTime}
</if>
<if test="startCreateTime != null">
AND o.creat_time >= #{startCreateTime}
</if>
<if test="endCreateTime != null">
AND o.creat_time <= #{endCreateTime}
</if>
<if test="status != null">
AND o.status = #{status}
</if>
<if test="subStatus != null">
AND o.sub_status = #{subStatus}
</if>
<if test="auitStatus == 10">
AND o.sub_status = #{auitStatus}
</if>
<if test="auitStatus == 90">
AND o.sub_status = #{auitStatus}
</if>
<if test="auitStatus == 100">
AND (o.sub_status != 10 AND o.sub_status != 90)
</if>
<if test="buyAdminId != null">
AND o.admin_id = #{buyAdminId}
</if>
<if test="buyerAdminDepartId != null">
AND o.admin_depart_id LIKE CONCAT(#{buyerAdminDepartId},'%')
</if>
<if test="memberId != null">
AND o.member_id = #{memberId}
</if>
<if test="shopId != null">
AND o.shop_id = #{shopId}
</if>
<if test="userId != null">
AND o.user_id = #{userId}
</if>
<if test="platform != null">
AND d.platform = #{platform}
</if>
<if test="breed != null">
AND d.breed = #{breed}
</if>
<if test="breedAndContractCode != null">
AND (o.contract_code LIKE #{breedAndContractCode} OR d.breed LIKE #{breedAndContractCode})
</if>
<if test="depts != null">
AND
<foreach item="depId" collection="depts" open="(" separator="OR" close=")">
o.admin_depart_id LIKE CONCAT(#{depId}, '%')
</foreach>
</if>
</where>
GROUP BY o.id) tempTable
</select>
</mapper>
OrderDetail
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://code.mysteeltech.com/schema/mybatis/mybatis-3-mapper.dtd">
<mapper namespace="com.banksteel.order.dao.impl.OrderDetailDaoImpl">
<sql id="t_order_detail">trade_order_detail</sql>
<resultMap id="beanMap" type="orderdetail">
<id column="id" property="id" />
<result column="order_id" property="orderId" />
<result column="resource_id" property="resourceId" />
<result column="goods_id" property="goodsId" />
<result column="platform" property="platform" />
<result column="goods_type" property="goodsType" />
<result column="breed_code" property="breedCode" />
<result column="breed_alias" property="breedAlias" />
<result column="breed" property="breed" />
<result column="first_breed_code" property="firstBreedCode" />
<result column="first_breed_name" property="firstBreedName" />
<result column="second_breed_code" property="secondBreedCode" />
<result column="second_breed_name" property="secondBreedName" />
<result column="third_breed_code" property="thirdBreedCode" />
<result column="third_breed_name" property="thirdBreedName" />
<result column="spec_code" property="specCode" />
<result column="spec_alias" property="specAlias" />
<result column="spec" property="spec" />
<result column="thickness" property="thickness" />
<result column="width" property="width" />
<result column="length" property="length" />
<result column="material_code" property="materialCode" />
<result column="material_alias" property="materialAlias" />
<result column="material" property="material" />
<result column="brand_code" property="brandCode" />
<result column="brand_alias" property="brandAlias" />
<result column="brand" property="brand" />
<result column="edge_type" property="edgeType" />
<result column="sku" property="sku" />
<result column="surface_type" property="surfaceType" />
<result column="serialNo" property="serialNo" />
<result column="batchNo" property="batchNo" />
<result column="outDate" property="outDate" />
<result column="city_id" property="cityId" />
<result column="city" property="city" />
<result column="warehouse_code" property="warehouseCode" />
<result column="warehouse_alias" property="warehouseAlias" />
<result column="warehouse" property="warehouse" />
<result column="shop_id" property="shopId" />
<result column="weight_way" property="weightWay" />
<result column="quantity_unit" property="quantityUnit" />
<result column="weight_unit" property="weightUnit" />
<result column="average_qty" property="averageQty" />
<result column="items" property="items" />
<result column="qty" property="qty" />
<result column="price" property="price" />
<result column="inout_warehouse_price" property="inOutWarehousePrice" />
<result column="onboard_time" property="onboardTime" />
<result column="quality_grade" property="qualityGrade" />
<result column="quality_remark" property="qualityRemark" />
</resultMap>
<insert id="insert" parameterType="orderdetail" useGeneratedKeys="true" keyProperty="id">
INSERT INTO <include refid="t_order_detail"/>
(order_id, resource_id, goods_id, platform,goods_type, breed_code, breed, breed_alias, first_breed_code, first_breed_name,
second_breed_code, second_breed_name, third_breed_code, third_breed_name, spec_code, spec_alias, spec, thickness, width,
length, material_code, material_alias, material, brand_code, brand_alias, brand, serialNo, batchNo, outDate, shop_id,
city_id, city, warehouse_code, warehouse_alias, warehouse, edge_type, sku, surface_type, weight_way, quantity_unit,
weight_unit, quality_grade, quality_remark, average_qty, items, qty, price,inout_warehouse_price,onboard_time, version,
last_access)
VALUES
(#{orderId}, #{resourceId}, #{goodsId}, #{platform}, #{goodsType}, #{breedCode}, #{breed}, #{breedAlias}, #{firstBreedCode}, #{firstBreedName},
#{secondBreedCode}, #{secondBreedName}, #{thirdBreedCode}, #{thirdBreedName}, #{specCode}, #{specAlias}, #{spec}, #{thickness}, #{width},
#{length}, #{materialCode}, #{materialAlias}, #{material}, #{brandCode}, #{brandAlias}, #{brand}, #{serialNo}, #{batchNo}, #{outDate}, #{shopId},
#{cityId}, #{city}, #{warehouseCode}, #{warehouseAlias}, #{warehouse}, #{edgeType}, #{sku}, #{surfaceType}, #{weightWay}, #{quantityUnit},
#{weightUnit}, #{qualityGrade}, #{qualityRemark}, #{averageQty}, #{items}, #{qty}, #{price},#{inOutWarehousePrice}, #{onboardTime}, #{version},
#{lastAccess})
</insert>
<update id="update" parameterType="orderdetail">
UPDATE
<include refid="t_order_detail"/>
<set>
breed_code = #{breedCode},
breed = #{breed},
breed_alias = #{breedAlias},
first_breed_code = #{firstBreedCode},
first_breed_name = #{firstBreedName},
second_breed_code = #{secondBreedCode},
second_breed_name = #{secondBreedName},
third_breed_code = #{thirdBreedCode},
third_breed_name = #{thirdBreedName},
spec_code = #{specCode},
spec_alias = #{specAlias},
spec = #{spec},
thickness = #{thickness},
width = #{width},
length = #{length},
material_code = #{materialCode},
material_alias = #{materialAlias},
material = #{material},
brand_code = #{brandCode},
brand_alias = #{brandAlias},
brand = #{brand},
average_qty = #{averageQty},
items = #{items},
qty = #{qty},
price = #{price},
inout_warehouse_price = #{inOutWarehousePrice},
goods_type = #{goodsType}
</set>
<where>
id = #{id}
</where>
</update>
<select id="getById" parameterType="long" resultMap="beanMap">
SELECT * FROM <include refid="t_order_detail"/> WHERE id = #{id}
</select>
<select id="queryByOrderId" parameterType="long" resultMap="beanMap">
SELECT * FROM <include refid="t_order_detail"/> WHERE order_Id = #{orderId}
</select>
</mapper>
扫一扫
2977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
