java增强： mybatis框架

青旅半醒 2022-05-12 09:26 165阅读 0赞

1.  mybatis简介
2.  如何使用： 入门级
3.  进阶教程
    
    **目录**
    
    part1: 初步配置使用
    
    part2: mybatis 流式查询
    
    part3:  高级语法（多表关联查询）
    
    --------------------

--------------------

MyBatis ，持久层框架， 以XML 或注解的方式， 将接口和 Java 的 POJO(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

![70][]

### part1: 初步配置使用 ###

（使用 idea + maven)

第一步： 创建一个module, java -->  添加maven 支持，在pom.xml中， 添加依赖

<dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.1</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
            </dependency> 
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.41</version>
            </dependency>

第二步： 创建javabean类---->表和java类的映射配置: 类名Mapper.xml---> 配置数据库连接信息: mybatis-config.xml

A, 创建package: a,  新建javabean类

public class User {
        //字段
        private int id;
        private int age;
        private String name;
        private String sex;
    
        private List<Orders> listOrders;
    
        //构造
        public User(int id ,int age, String name, String sex) {
            this.age = age;
            this.name = name;
            this.sex = sex;
            this.id=id;
        }
        public User() { }
    
        //get,set
        //toString()
    }

B,  表和java类的映射配置: 类名Mapper.xml     (放在： resources文件夹下)

<?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="users">
       
        <insert id="insert">
    		insert into users2(name,sex) values(#{name},#{sex})
        </insert>
        <update id="update" >
            update users2 set name=#{name}, sex=#{sex} where id=#{id}
        </update>
    
        <select id="selectById" resultType="a.User" >
            select * from users2 where id=#{id}
        </select>
        <select id="selectAll" resultType="_user">
            select * from users2
        </select>
    </mapper>

C,  配置数据库连接信息: mybatis-config.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>
    
        <properties>
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/db?allowMultiQueries=true"/>
            <property name="username" value="root"/>
            <property name="password" value="root"/>
        </properties>
    
        
        <typeAliases>
            <typeAlias type="a.User" alias="_user" />
            <typeAlias type="a.Orders" alias="_orders" />
        </typeAliases>
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${driver}"/>
                    <property name="url" value="${url}"/>
                    <property name="username" value="${username}"/>
                    <property name="password" value="${password}"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper resource="UserMapper.xml"/>
        </mappers>
    </configuration>

第三步： 开始使用mybatis 的java api

public class TestMybatis {
        SqlSession session;
    
        @Before
        public void init() throws IOException {
            //加载文件
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
    
            //创建session
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
             session = factory.openSession();
        }
    
        /**
         * 插入
         * @throws IOException
         */
        @Test
        public void t() throws IOException {
            //crud
            int res = session.insert("users.insert", new User(23, "insert3", "boy"));
    
            session.commit();
            session.close();
            System.out.println("ok,  " +res);
        }
    
        /**
         * 更新
         * @throws IOException
         */
        @Test
        public void t2() throws IOException {
            //crud
            int res = session.update("users.update", new User(1, 23, "updte2", "girl"));
    
            session.commit();
            session.close();
            System.out.println("ok,  "+ res);
        }
    
        /**
         * 查询
         * @throws IOException
         */
        @Test
        public void t3() throws IOException {
            //crud
            User user = session.selectOne("users.selectById", 1);
    
            session.commit();
            session.close();
            System.out.println("ok");
            System.out.println(user);
        }
        /**
         * 删除
         * @throws IOException
         */
        @Test
        public void t4() throws IOException {
            //crud
            int res = session.delete("users.delete");
    
            session.commit();
            session.close();
            System.out.println("ok, "+res);
    
        }

### part2: mybatis 流式查询 ###

<mapper namespace="com.example.demo.mappers.PublicClassMapper">
        <select id="querysql"  resultType="java.util.HashMap" parameterType="String">
            SELECT  *  FROM  ${params}
        </select>
    </mapper>
    
    @Mapper
    public interface PublicClassMapper {
    //     List<Map<String,String>> querysql(@Param("params") String params);
         Cursor<Map<String,String>> querysql(@Param("params") String params);
    }
    
    @Component
    public class OracleReadService {
        @Resource
        PublicClassMapper publicClassMapper;
        @Transactional
        public String   querysql(String sql) {
            Cursor<Map<String,String>> querysql = publicClassMapper.querysql(sql);
                Iterator<Map<String, String>> iterator = querysql.iterator();
                while (iterator.hasNext()){
                    Map<String, String> map = iterator.next();
                    Set<String> keys = map.keySet();
                    for (String k: keys){
                        String value = map.get(k);
                        System.out.println(k+"==>" + value);
                    }
                }
    //        querysql.forEach(map->{
    //            Set<String> keys = map.keySet();
    //            for (String k: keys){
    //                String value = map.get(k);
    //                System.out.println(k+"==>" + value);
    //            }
    //            System.out.println("----------------");
    //        });
            return "ok";
        }
    }

### part3:  高级语法（多表关联查询） ###

![70 1][]

A,   一个用户---->多个订单（每个订单： 多个产品 ），关系如下

（需求1： 查询出a用户的所有订单信息， 商品信息 )

(需求2：  查询出产品p的所在订单信息， 用户信息）

![70 2][]

//需求1： 解答
       <select id="selectOrderListDetailsById" resultMap="map3" >
           select orders2.id  orderId,
            orders2.user_id  user_id,
            orders2.product_id  order_productId,
    
            users2.id  userId,
            users2.name  userName,
            users2.sex  userSex,
    
            product.id productId,
            product.name productName,
            product.price productPrice
    
            from  users2
            left join orders2
            on users2.id=orders2.user_id
    
            left join product
            on orders2.product_id=product.id
            where users2.id=#{id}
        </select>
    
        <resultMap id="map3" type="_user">
            <id column="userId" property="id" ></id>
            <result column="userName" property="name" ></result>
            <result column="userSex" property="sex" ></result>
    
            <collection property="listOrders" ofType="_orders" column="userId">
                <id column="orderId" property="id" ></id>
    
                <collection property="listProduct" ofType="_product" column="orderId">
                    <id column="id" property="productId" ></id>
                    <result column="productName" property="name" ></result>
                    <result column="productPrice" property="price" ></result>
                </collection>
            </collection>
        </resultMap>

//需求2： 解答
        
        
        <select id="selectById" resultMap="map1" >
            select orders2.id  orderId,
            orders2.user_id  user_id,
            orders2.product_id  order_productId,
    
            users2.id  userId,
            users2.name  userName,
            users2.sex  userSex,
    
            product.id productId,
            product.name productName,
            product.price productPrice
    
            from  product
            left join orders2
            on orders2.product_id=product.id
    
            left join users2
            on users2.id=orders2.user_id
    
            where product.id=#{id}
        </select>
        <resultMap id="map1" type="_product">
            <id column="productId" property="id" ></id>
            <result column="productName" property="name" ></result>
            <result column="productPrice" property="price" ></result>
    
            <association property="orders" javaType="_orders">
                <id column="orderId" property="id" ></id>
    
                <association property="user" javaType="_user">
                    <id column="userId" property="id" ></id>
                    <result column="userName" property="name" ></result>
                    <result column="userSex" property="sex" ></result>
                </association>
    
            </association>
        </resultMap>

[70]: /images/20220512/5ef515ec26c542029aef5228959d1122.png
[70 1]: /images/20220512/9ab3b7f189674e2491a3101e56b0bbd0.png
[70 2]: /images/20220512/7c2b14ca74bb466b975c393b4caef650.png