Mybatis博客

1.简单使用Mybatis

1.1 导入lib压缩包内的文件，也就是mybatis和数据库驱动

 

转存失败重新上传取消

 

1.2 在数据库创建个表并添加数据，内容你喜欢就好

 

CREATE TABLE category_ (

  id int(11) NOT NULL AUTO_INCREMENT,

  name varchar(32) DEFAULT NULL,

  PRIMARY KEY (id)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

 

INSERT INTO category_ VALUES (null,'category1');

INSERT INTO category_ VALUES (null,'category2');

 

 

1.3 创建个实体类，到时候要绑定，并且实体类的字段名要和数据库了的列名一样，奇怪的是似乎微调一点不会有影响，例如name改成names，而name改成fgdasuhi就不行

public class Category {
    private int id;
    private String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

}

 

 

1.4 创建mybatis-config.xml配置文件，放在src下

 

<?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>
        <!--自动扫描mybatis类下的类型,在写resultType的时候就不用在前面加类名，如果不写的话到时候就要加 -->
        <package name="mybatis"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库的驱动，地址，名字，密码 -->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/qq?characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--映射Category.xml -->
        <mapper resource="mybatis/Category.xml"/>
    </mappers>
</configuration>

 

1.5 创建配置文件Category.xml，这里放在mybatis目录下

 

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

<!--表示命名空间是mybatis,也就是这个文件的位置
    namespace其实我不太了解，总的来说写这个就是确保能找到Category.xml
    不写的话就会以当前路径的这个包为准
 -->
<mapper namespace="mybatis">
    <!--设置id和resultType，resultType代表该语句结果和Category关联 -->
    <select id="listCategory" resultType="Category">
            /*查询语句*/
            select * from   category_
        </select>
</mapper>

 

1.6 创建一个测试类

 

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 TestMybatis {

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

        //配置文件名称和位置
        String resource = "mybatis-config.xml";
        //获取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //通过SqlSessionFactoryBuilder创建
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //获得session实例
        SqlSession session=sqlSessionFactory.openSession();

        //listCategory是在其他文件中定义的查询语句的id，可以在配置文件中看到
        List<Category> cs=session.selectList("listCategory");
        //遍历没什么好说的
        for (Category c : cs) {
            System.out.println(c.getName());
        }

    }
}

 

1.7 运行查看结果！！！！！！

转存失败重新上传取消

 

结构目录：

转存失败重新上传取消

 

 

2.mybatis的CRUD

(crud是指在做计算处理时的增加(Create)、读取(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。)

 

2.1 在上一个代码的基础上更改，先添加各种语句，在Category.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">

<!--表示命名空间是mybatis,也就是这个文件的位置
    namespace其实我不太了解，总的来说写这个就是确保能找到Category.xml
    不写的话就会以当前路径的这个包为准
 -->
<mapper namespace="mybatis">
    <!--添加语句 -->
    <!--#{name}会字段获取类.getName -->
    <insert id="addCategory" parameterType="Category" >
            insert into category_ ( name ) values (#{name})
        </insert>

    <!--删除语句，#{id}和上面的#{name}也是一样的，只不过名字改了 -->
    <delete id="deleteCategory" parameterType="Category" >
            delete from category_ where id= #{id}
        </delete>

    <!--查询语句 -->
    <select id="getCategory" parameterType="_int" resultType="Category">
            select * from   category_  where id= #{id}
        </select>

<!--更新语句 -->
    <update id="updateCategory" parameterType="Category" >
            update category_ set name=#{name} where id=#{id}
        </update>

    <!--查询语句 -->
    <select id="listCategory" resultType="Category">
            select * from   category_
        </select>

</mapper>

 

 

2.2 添加语句的运行

 

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        //创建实体类
        Category c = new Category();
        //给实体类设置名字
        c.setName("新增加的Category");
        //给语句赋值，第一个参数的语句的id，第二个则是object
        session.insert("addCategory",c);

        //把循环遍历写成了一个类
        listAll(session);

        //commit是提交数据，凡是修改了数据库的都要提交，例如增加删除修改
        session.commit();
        //关闭
        session.close();

    }

    private static void listAll(SqlSession session) {
        List<Category> cs = session.selectList("listCategory");
        for (Category c : cs) {
            System.out.println(c.getName());
        }
    }
}

 

 

2.3 删除语句使用

改中间一段就好了：

Category c = new Category();
c.setId(11);
session.delete("deleteCategory",c);

 

 

2.4 获取语句的使用

也是改一段就好：

Category c= session.selectOne("getCategory",3);

System.out.println(c.getName());

//listAll(session);

 

 

2.5 修改语句的使用

也是改中间一段就好：

//先用查询语句取得要修改的数据
Category c= session.selectOne("getCategory",3);
c.setName("修改了的Category名稱");
//使用修改语句修改数据
session.update("updateCategory",c);

 

 

 

 

 

3.更多查询

3.1 模糊查询使用，依旧再之前的基础上改

 

3.1.1 还是先改语句，

<select id="listCategoryByName"  parameterType="string" resultType="Category">
        select * from   category_  where name like concat('%',#{0},'%')
    </select>

 

3.1.2 改Test类

List<Category> cs = session.selectList("listCategoryByName","增加");
for (Category c : cs) {
    System.out.println(c.getName());
}

 

3.2 多条件查询

3.2.1 还是改语句

    <select id="listCategoryByIdAndName"  parameterType="map" resultType="Category">
    select * from   category_  where id> #{id}  and name like concat('%',#{name},'%')
</select>

3.2.2 改Test类

//因为是多个参数，而selectList方法又只接受一个参数对象，所以需要把多个参数放在Map里，然后把这个Map对象作为参数传递进去
Map<String,Object> params = new HashMap<>();
params.put("id", 1);
params.put("name", "ca");
List<Category> cs = session.selectList("listCategoryByIdAndName",params);

 

 

 

4.一对多

4.1 先添加数据库和数据

create table product_(

id int NOT NULL AUTO_INCREMENT,

name varchar(30)  DEFAULT NULL,

price float  DEFAULT 0,

cid int ,

PRIMARY KEY (id)

)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

 

delete from category_;

INSERT INTO category_ VALUES (1,'category1');

INSERT INTO category_ VALUES (2,'category2');

delete from product_;

INSERT INTO product_ VALUES (1,'product a', 88.88, 1);

INSERT INTO product_ VALUES (2,'product b', 88.88, 1);

INSERT INTO product_ VALUES (3,'product c', 88.88, 1);

INSERT INTO product_ VALUES (4,'product x', 88.88, 2);

INSERT INTO product_ VALUES (5,'product y', 88.88, 2);

INSERT INTO product_ VALUES (6,'product z', 88.88, 2);

 

4.2 添加实体类并修改之前的实体类

 

public class Category {
    private int id;
    private String name;
    List<Product> products;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public List<Product> getProducts() {
        return products;
    }
    public void setProducts(List<Product> products) {
        this.products = products;
    }
    @Override
    public String toString() {
        return "Category [id=" + id + ", name=" + name + "]";
    }


}

 

 

 

public class Product {
    private int id;
    private String name;
    private float price;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";
    }

}

 

 

4.3 添加语句

 

<!--这里不是用的resultType, 而是resultMap，通过resultMap把数据取出来放在对应的 对象属性里 -->
<!--类型是Category -->
<resultMap type="Category" id="categoryBean">
    <id column="cid" property="id" />
    <result column="cname" property="name" />

    <!-- 一对多的关系 -->
    <!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
    <collection property="products" ofType="Product">
        <id column="pid" property="id" />
        <result column="pname" property="name" />
        <result column="price" property="price" />
    </collection>
</resultMap>

<!-- 关联查询分类和产品表 -->
<!--c.*代表c表下的所有 字段，category_ c 代表 category_  等于  c
    分解开来就是c下所有数据，p下所有数据，然后各个手动输入的数据
    然后就是先筛选出on配对的数据，然后p表再加入c表 -->
<select id="listCategory" resultMap="categoryBean">
        select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname' from category_ c left join product_ p on c.id = p.cid
    </select>

 

 

4.4 修改test类

 

List<Category> cs = session.selectList("listCategory");
for (Category c : cs) {
    System.out.println(c);
    List<Product> ps = c.getProducts();
    for (Product p : ps) {
        System.out.println("\t"+p);
    }
}

 

 

 

5.多对多

 

5.1 先创建表添加数据

create table order_ (

  id int(11) NOT NULL AUTO_INCREMENT,

  code varchar(32) DEFAULT NULL,

  PRIMARY KEY (id)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

create table order_item_(

  id int(11) NOT NULL AUTO_INCREMENT, 

  oid int ,

  pid int ,

  number int ,

  PRIMARY KEY(id)

)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

 

INSERT INTO order_ VALUES (1,'code000A');

INSERT INTO order_ VALUES (2,'code000B');

 

INSERT INTO order_item_ VALUES (null, 1, 1, 100);

INSERT INTO order_item_ VALUES (null, 1, 2, 100);

INSERT INTO order_item_ VALUES (null, 1, 3, 100);

INSERT INTO order_item_ VALUES (null, 2, 2, 100);

INSERT INTO order_item_ VALUES (null, 2, 3, 100);

INSERT INTO order_item_ VALUES (null, 2, 4, 100);

 

 

5.2 创建实体类

public class Order {
    private int id;
    private String code;

    List<OrderItem> orderItems;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public List<OrderItem> getOrderItems() {
        return orderItems;
    }
    public void setOrderItems(List<OrderItem> orderItems) {
        this.orderItems = orderItems;
    }

}

 

 

public class OrderItem {
    private int id;
    private int number;
    private Order order;
    private Product product;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getNumber() {
        return number;
    }
    public void setNumber(int number) {
        this.number = number;
    }
    public Order getOrder() {
        return order;
    }
    public void setOrder(Order order) {
        this.order = order;
    }
    public Product getProduct() {
        return product;
    }
    public void setProduct(Product product) {
        this.product = product;
    }

}

 

 

5.3 创建各个xml

5.3.1 Order.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">
<mapper namespace="mybatis">
    <!--通过resultMap ，进行字段和属性的对应 -->
    <resultMap type="Order" id="orderBean">
        <id column="oid" property="id" />
        <result column="code" property="code" />

        <collection property="orderItems" ofType="OrderItem">
            <id column="oiid" property="id" />
            <result column="number" property="number" />
            <association property="product" javaType="Product">
                <id column="pid" property="id"/>
                <result column="pname" property="name"/>
                <result column="price" property="price"/>
            </association>
        </collection>
    </resultMap>

    <!--定义语句，而且语句中'oid'等名字需要和上面的column="oid"一样 -->
    <select id="listOrder" resultMap="orderBean">
            select o.*,p.*,oi.*, o.id 'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'
                from order_ o
                left join order_item_ oi    on o.id =oi.oid
                left join product_ p on p.id = oi.pid
        </select>

    <!--同样是定义语句，这里是查询单个的 -->
    <select id="getOrder" resultMap="orderBean">
            select o.*,p.*,oi.*, o.id 'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'
                from order_ o
                left join order_item_ oi on o.id =oi.oid
                left join product_ p on p.id = oi.pid
            where o.id = #{id}
        </select>
</mapper>

 

 

 

 

5.3.2 OrderItem.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">
<mapper namespace="mybatis">

    <insert id="addOrderItem" parameterType="OrderItem">
            insert into order_item_
                values(null,#{order.id},#{product.id},#{number})
        </insert>
    <delete  id="deleteOrderItem" parameterType="OrderItem">
            delete from order_item_
                where oid = #{order.id} and pid = #{product.id}
        </delete>

</mapper>

 

 

5.3.3 Product.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">
<mapper namespace="mybatis">
    <resultMap type="Product" id="productBean">
        <id column="pid" property="id" />
        <result column="pname" property="name" />
        <result column="price" property="price" />

        <!-- 多对一的关系 -->
        <!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
        <association property="category" javaType="Category">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </association>
    </resultMap>

    <select id="listProduct" resultMap="productBean">
            select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname'
                from category_ c
                left join product_ p on c.id = p.cid
        </select>
    <select id="getProduct" resultMap="productBean">
            select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname'
                from category_ c
                left join product_ p on c.id = p.cid
            where p.id = #{id}
        </select>
</mapper>

 

 

5.4 添加映射

<mappers>
    <!--映射Category.xml -->
    <mapper resource="mybatis/Category.xml"/>
    <mapper resource="mybatis/Product.xml"/>
    <mapper resource="mybatis/Order.xml"/>
    <mapper resource="mybatis/OrderItem.xml"/>
</mappers>

 

 

5.5 修改Test类

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        //删除方法
//        deleteOrderItem(session);
        //添加方法
//        addOrderItem(session);
        //查询方法
        listOrder(session);

        session.commit();
        session.close();

    }

    private static void deleteOrderItem(SqlSession session) {
        Order o1 = session.selectOne("getOrder",1);
        Product p6 = session.selectOne("getProduct",6);
        OrderItem oi = new OrderItem();
        oi.setProduct(p6);
        oi.setOrder(o1);
        session.delete("deleteOrderItem", oi);
    }

    private static void addOrderItem(SqlSession session) {
        Order o1 = session.selectOne("getOrder",1);
        Product p6 = session.selectOne("getProduct",6);
        OrderItem oi = new OrderItem();
        oi.setProduct(p6);
        oi.setOrder(o1);
        oi.setNumber(200);

        session.insert("addOrderItem", oi);
    }

    private static void listOrder(SqlSession session) {
        // 调用listOrder方法
        List<Order> os = session.selectList("listOrder");
        for (Order o : os) {
            System.out.println(o.getCode());
            List<OrderItem> ois= o.getOrderItems();
            for (OrderItem oi : ois) {
                /*/t代表按一下Tab，%s是string型占位符，%f是float型，%d是int型，%n是换行*/
                System.out.format("\t%s\t%f\t%d%n", oi.getProduct().getName(),oi.getProduct().getPrice(),oi.getNumber());
            }
        }
    }
}

 

 

 

 

6.动态SQL-if

6.1 修改语句

<?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">
    <!--if里面的代替
    e1 or e2
    e1 and e2
    e1 == e2,e1 eq e2
    e1 != e2,e1 neq e2
    e1 lt e2：小于
    e1 lte e2：小于等于，其他gt（大于）,gte（大于等于）
    e1 in e2
    e1 not in e2
    e1 + e2,e1 * e2,e1/e2,e1 - e2,e1%e2
    !e,not e：非，求反
    e.method(args)调用对象方法
    e.property对象属性值
    e1[ e2 ]按索引取值，List,数组和Map
    @class@method(args)调用类的静态方法
    @class@field调用类的静态字段值
    -->
    <!--sql语句中的代替
    特殊字符   替代符号
     &            &
     <            <
     >            >
     "             "
     '              '
小于等于    a<=b                 a <= b      a <![CDATA[<= ]]>b
大于等于    a>=b                 a >= b      a <![CDATA[>= ]]>b
不等于        a!=b                 a <![CDATA[ <> ]]>b      a <![CDATA[!= ]]>b
    -->
    <mapper namespace="com.how2java.pojo">
    <select id="listProduct" resultType="Product">
        select * from product_
        <if test="id lte 3">

            where id < #{id}
        </if>
    </select>

</mapper>

 

 

6.2 test类修改

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        System.out.println("查询id<3的");
        Map<String,Object> params = new HashMap<>();
        params.put("id",3);
        List<Product> ps2 = session.selectList("listProduct",params);
        for (Product p : ps2) {
            System.out.println(p);
        }

        session.commit();
        session.close();

    }
}

 

 

 

7.动态SQL-where

7.1 修改语句

<mapper namespace="com.how2java.pojo">
    <select id="listProduct" resultType="Product">
        select * from product_
        <where>
            <if test="name!=null">
                and name like concat('%',#{name},'%')
            </if>
            <if test="price!=null and price!=0">
                and price > #{price}
            </if>
        </where>
    </select>

</mapper>

 

 

7.2 修改Test

 

 

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        System.out.println("多条件查询");
        Map<String,Object> params = new HashMap<>();
        params.put("name","a");
        params.put("price","10");
        List<Product> ps2 = session.selectList("listProduct",params);
        for (Product p : ps2) {
            System.out.println(p);
        }

        session.commit();
        session.close();

    }
}

 

 

 

7.3 set标签，在update中也涉及了多标签的问题，就用set标签解决

 

<update id="updateProduct" parameterType="Product" >
    update product_
    <set>
        <if test="name != null">name=#{name},</if>
        <if test="price != null">price=#{price}</if>

    </set>

    where id=#{id}
</update>

 

7.4 修改Test类

 

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        Product p = new Product();
        p.setId(6);
        p.setName("product zzzs");
        p.setPrice(29.99f);
        session.update("updateProduct",p);

        listAll(session);

        session.commit();
        session.close();

    }

    private static void listAll(SqlSession session) {
        Map<String,Object> params = new HashMap<>();
//        params.put("name","a");
//        params.put("price","10");
        List<Product> ps2 = session.selectList("listProduct",params);
        for (Product p : ps2) {
            System.out.println(p);
        }
    }
}

 

 

 

 

7.动态SQL-choose

7.1 修改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">
<mapper namespace="com.how2java.pojo">
<!--
    Mybatis里面没有else标签，但是可以使用when otherwise标签来达到这样的效果。
    其作用是： 提供了任何条件，就进行条件查询，否则就使用id>1这个条件。
    -->
    <select id="listProduct" resultType="Product">
        SELECT * FROM product_
        <where>
            <choose>
                <when test="name != null">
                    and name like concat('%',#{name},'%')
                </when>
                <when test="price !=null and price != 0">
                    and price > #{price}
                </when>
                <otherwise>
                    and id >1
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>

 

7.2 修改Test类

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        Map<String,Object> params = new HashMap<>();
        params.put("name","a");
        params.put("price","10");
        List<Product> ps = session.selectList("listProduct",params);
        for (Product p : ps) {
            System.out.println(p);
        }

        session.commit();
        session.close();

    }

    private static void listAll(SqlSession session) {

    }
}

 

 

 

 

 

8.动态SQL-foreach

8.1 还是修改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">
<!--
item：集合中元素迭代时的别名，该参数为必选
index：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选
collection: 要做foreach的对象，作为入参时，List对象默认用"list"代替作为键，数组对象有"array"代替作为键，Map对象没有默认的键。
open：foreach代码的开始符号，一般是(和close=")"合用。常用在in(),values()时。该参数可选
separator：元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。
lose: foreach代码的关闭符号，一般是)和open="("合用。常用在in(),values()时。该参数可选。
虽然看起来很复杂，不过展现出来的语句就是： SELECT * FROM product_    WHERE ID in      (      ?     ,      ?     ,     ?    )
-->
<mapper namespace="com.how2java.pojo">
    <select id="listProduct" resultType="Product">
        SELECT * FROM product_
        WHERE ID in
        <foreach item="item" index="index" collection="list"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
</mapper>

 

 

 

8.2 修改Test

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        List<Integer> ids = new ArrayList();
        // 查询id为1，3，5的数据
        ids.add(1);
        ids.add(3);
        ids.add(5);

        List<Product> ps = session.selectList("listProduct",ids);
        for (Product p : ps) {
            System.out.println(p);
        }

        session.commit();
        session.close();

    }
}

 

 

 

 

9.动态SQL-bind

9.1 修改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">
<mapper namespace="com.how2java.pojo">
    <!-- 本来的模糊查询方式 -->
    <!--         <select id="listProduct" resultType="Product"> -->
    <!--             select * from   product_  where name like concat('%',#{0},'%') -->
    <!--         </select> -->

    <select id="listProduct" resultType="Product">
        <bind name="likename" value="'%' + name + '%'" />
        select * from   product_  where name like #{likename}
    </select>

</mapper>

 

 

9.2 修改Test

public class TestMybatis {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        Map<String, String> params =new HashMap();
        params.put("name", "product");

        List<Product> ps = session.selectList("listProduct",params);
        for (Product p : ps) {
            System.out.println(p);
        }

        session.commit();
        session.close();

    }

}

 

参考网站: https://how2j.cn/k/mybatis/mybatis-tutorial/1087.html