半自动ORM—mybatis

末蓝、 2024-04-08 10:15 51阅读 0赞

### 开篇： ###

#### ORM 框架 ： ####

>     jdbc取出来的是记录集 ResultSet, 遍历 （同时取字段），  new对象，制作集合
>     ORM框架：取出来的直接就是对象（集合）。 配置（表book<----->实体类Book）

#### 开发步骤： ####

>     1.github下载   mybatis-3.4.4.zip
>     2.准备com.qq.model  实体类--------------model模型
>     3.Mybatis 基础配置文件  mybatis-config.xml   编写 
>     	一、db.properties
>     		（1）driver
>     		（2）url
>     		（3）user
>     	        （4）password
>     	二、映射文件所在的路径
>     		sql语句，要写到一个文件中， 映射文件BookMapper.xml	
>     		<mappers>
>     			com/qq/mapper/BookMapper.xml
>     	三、驼峰转换
>     		<settings>
>     		类的属性名<---->表字段
>     		personName	person_name
>     	四、日志组件 设置
>     		<settings>
>     4.映射文件的编写
>     5.写java代码：
>     	一般流程
>     		（1）加载 mybatis-config.xml   
>     		（2）去执行xxxMapper.xml文件中的某个sql语句
>     
>     	三种:
>     		（一） SqlSession.方法()  selectAll(),selectOne()
>     		（二）接口的方式(常用)
>     			抽象方法,都对应了映射文件中的sql语句
>     			包com.hqyj.mapper
>     			interface BookMapper
>     			{
>     				List<Book> selectAll(); 
>     				Book		selectOne();
>     			}		
>     		（三）注解的方式：sql语句标记在接口的方法上面

### 一、文件的编写 ###

#### 1.基础配置文件的编写 ####

1.1、配置文件—mybatis-config.xml

顺序：

mybatis-config.xml

> 1.  driver
> 2.  url
> 3.  user
> 4.  password

<environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                
                <dataSource type="POOLED">
                    <property name="drvier" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/shop?useSSL=true&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai"/>
                    <property name="user" value="root"/>
                    <property name="password" value="123456"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper resource="com.hqyj.mapper.BookMapper.xml"/>
    
        </mappers>

1.2、隐射文件的所在的路径

要写到一个文件之中，映射文件，BookMapper.xml

设置映射文件所在的路径

<mappers>
            <mapper resource="com.hqyj.mapper.BookMapper.xm"/>  //方法一
            <package name="com.hqyj.mapper"/>   //批量导入法，直接用
     </mappers>

1.3、驼峰转换

> 类的属性名 --------------表字段名
> 
> personName -----------person\_name

日志组件的选择

<settings>
            <setting name="mapUnderscoreToCamelCase" value="true"/>
                
            <setting name="logImpl" value="STDOUT_LOGGING"/>
        </settings>

1.4 mapper路径的写法

#### 2.映射文件的编写，BookMapper.xml文件 ####

2.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">
    
       
    <mapper namespace="mapper对">
        <resultMap id="map集合的名字" type="对应的实体类>
            <id property="主键名" column="数据库的列名" ></id>   
    
            <result column="数据库列名" property="实体类对应的名字"></result
        </resultMap> 
    </mapper>

2.2 sql语句编写

> 如果数据库的列名与实体类的不一致，需要使用resultMap来转换
> 
> 1.getcount():查询表中的数据条数
> 
> 2.selectAll：查询全部的book
> 
> 3.findUserAuthor：不管大小写模糊查询tom的author的记录
> 
> 4.selectOne:查询单个book
> 
> 5.insertOne:插入一条记录，行内配置模式
> 
> 6.insertOne3：插入一条记录，全局模式
> 
> 7.modify:修改记录，并且判断是否为空或者“”,是则不修改
> 
> 8.deleteById：根据id删除记录
> 
> 9.selectByAuthor：根author查询记录，不同的写法
> 
> 10.queryOrder：根据price排序显示

<mapper namespace="com.hqyj.mapper.BookMapper">
         
        <resultMap id="bookMap" type="com.hqyj.model.Book">
            <id property="id" column="id" ></id>   
            <result column="name" property="name"></result>
            <result column="author" property="author"></result>
            <result column="price" property="price"></result>
        </resultMap>
        
        <select id="getCount"  resultType="java.lang.Integer">
            select count(1) from book
        </select>
    
        <select id="selectAll"  resultMap="bookMap">
            SELECT id,name,author,price from Book where lower(author) like lower("%#{author}%")
        </select>
    
    
        <select id="findUserAuthor" parameterType="String" resultMap="bookMap">
            select * from book where upper(author) like upper("%${author}%")
        </select>
    
        <select id="selectOne" resultType="com.hqyj.model.Book" >
            SELECT * from Book   where id = #{id}
        </select>
    
        <insert id="insertOne"  keyProperty="id"  parameterType="com.hqyj.model.Book">
            insert  into book(name,author,price)  values (#{name},#{author},#{price})
        </insert>
    
        <insert id="insertOne3" >
            insert  into book(name,author,price) values (#{name},#{author},#{price})
        </insert>
    
        <update id="modify" parameterType="com.hqyj.model.Book">
            update book  set name=#{name},
                             <if test="author!=null and author!=''">
                                author=#{author},
                             </if>
                             <if test="price!=null">
                                 price=#{price}
                             </if>
                             where id=#{id}
        </update>
    
    
        <delete id="deleteById">
            DELETE From  book  Where  id = #{id}
        </delete>
    
        <select id="selectByAuthor" resultType="com.hqyj.model.Book">
    --         select * from book  where name like '%${author}%';
    --         select * from book  where name like '%${value}%';
    --         select * from book where name like #{author}
            select * from book  where name like concat('%',#{author},'%');
    --             select * from book  where name like '%' #{author} '%';
        </select>
    
        <select id="queryOrder" parameterType="com.hqyj.model.Book">
            select * from book order  by #{price} desc
        </select>
    
    </mapper>

#### 3.编写Java代码 ####

> 1.  加载mybatis-config.xml
> 2.  去执行XXXmapper.xml文件中的猫扑个SQL语句

public class BookTest {
        public static void main(String[] args) {
            String resource = "mybatis-config.xml";
            InputStream is = BookTest.class.getClassLoader().getResourceAsStream(resource);
    
            //创建sqlsession
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
    
            //打开会话
            SqlSession sqlSession = sessionFactory.openSession();
         
    
            1.查询单个
             Book book = sqlSession.selectOne("com.hqyj.mapper.BookMapper.selectOne",1);
            System.out.println(book.getAuthor());
    
            2.查询全部
            List<Book> bookList = sqlSession.selectList("com.hqyj.mapper.BookMapper.selectAll");
            for (Book one:bookList) {
                System.out.println(one.getAuthor());
            }
            
            3.插入一条记录
            Book book = new Book("爱的守护","明世隐",100);
            int res = sqlSession.insert("com.hqyj.mapper.BookMapper.insertOne",book);
            System.out.println(res);
            sqlSession.commit();
         
            sqlSession.close();
        }
    
    }

#### 4.编写方式 ####

4.1 SqlSession.方法（）

public class BookTest {
        public static void main(String[] args) {
            String resource = "mybatis-config.xml";
            InputStream is = BookTest.class.getClassLoader().getResourceAsStream(resource);
    
            //创建sqlsession
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
    
            //打开会话
            SqlSession sqlSession = sessionFactory.openSession();
            
    
            1.查询单个
             Book book = sqlSession.selectOne("com.hqyj.mapper.BookMapper.selectOne",1);
            System.out.println(book.getAuthor());
    
            2.查询全部
            List<Book> bookList = sqlSession.selectList("com.hqyj.mapper.BookMapper.selectAll");
            for (Book one:bookList) {
                System.out.println(one.getAuthor());
            }
            
            3.插入一条记录
            Book book = new Book("爱的守护","明世隐",100);
            int res = sqlSession.insert("com.hqyj.mapper.BookMapper.insertOne",book);
            System.out.println(res);
            sqlSession.commit();
    
            sqlSession.close();
        }
    
    }

4.2 接口的方式

抽象方法

com.hqyj.mapper
    
    	interface  BookMapper{
    
    			List selectAll();
    
    			Book  selectOne();
    
    	}
    public interface BookMapper {
    
        List<Book> selectAll();
    
        Book  selectOne(int   id);
    
        int  insertOne(Book book);
    
    
    }

<mapper namespace="com.hqyj.mapper.BookMapper">
        <select id="selectAll" resultType="com.hqyj.model.Book">
            SELECT * from Book
        </select>
    
        <select id="selectOne" resultType="com.hqyj.model.Book">
            SELECT * from Book   where id = #{id}
        </select>
    
        <insert id="insertOne" parameterType="com.hqyj.model.Book">
            insert  into book(name,author,price)
            values (#{name},#{author},#{price})
        </insert>
    
    </mapper>

4.3 sql语句标记在接口的方法上面

package com.hqyj.mapper;
    
    import com.hqyj.model.Book;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface BookMapper {
    
        @Select("select * from book")
        List<Book> selectAll();
    
        @Select("select * from book where id = #{id}")
        Book  selectOne(int   id);
    
        @Insert("insert  into book(name,author,price) values (#{name},#{author},#{price})")
        int  insertOne(Book book);
    
    
    }

public class BookTest2 {
    
        public static void main(String[] args) throws IOException {
    
            //法二：
    //        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    //        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
    //        SqlSession session = sessionFactory.openSession();
    //
    //        BookMapper bookMapper = session.getMapper(BookMapper.class);
    
            //法三：
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sessionFactory.openSession();
    
            BookMapper bookMapper = session.getMapper(BookMapper.class);
    
    
            List<Book> books = bookMapper.selectAll();
            for (Book b:books) {
                System.out.println(b.getAuthor());
            }
    
            Book book = bookMapper.selectOne(4);
            System.out.println(book);
    
            int num = bookMapper.insertOne(new Book("太难起","zs",10));
            System.out.println(num);
            session.commit();
    
            session.close();
            
        }
    }

4.4 以后编程实现

使用第二种接口的方式

思想：

> Book-------->MyTest----->BookMapper.java接口定义方法------->BookMapper.xml文件进行sql操作------>返回数据给MyTest并打印。

#### 5.db.properties编写 ####

>     user = root
>     password=123456

![6f7456ff5c149f92329c84905915a579.png][]

5.1 自动扫描实体类编写

方式一：

![02515b79974a89b2c233963ddec283a8.png][]

方式二：

![6a4b61b4034f923e7e6bd52245c61945.png][]

mapper.xml文件：

![6863199496a8a7f70c5817ddb4fd42e3.png][]

#### 6.日志：级别 ####

6.1 Logger\{\}接口：

> interface Logger\{
> 
> warn():警告
> 
> error()：致命提示
> 
> info():
> 
> falte():
> 
> \}

6.2 实现者

> 1.  log4J
> 2.  log4j2

### 二、sql——增删改查 ###

> parameterType，数据类型
> 
> java jdbc mysql(int)
> 
> c\# jdbc oracle(int)
> 
> int ------->interger

#### 1.添加 ####

1.2数据反填回实体类中

方法一：

<insert id="insertOne" useGeneratedKeys="true" keyProperty="id"  parameterType="com.hqyj.model.Book">
            insert  into book(name,author,price)
            values (#{name},#{author},#{price})
        </insert>

方法二：

<insert id="insertOne4" parameterType="com.qq.model.Book">
            <selectKey keyProperty="id" keyColumn="id" order="AFTER" resultType="int">
                SELECT LAST_INSERT_ID()
            </selectKey>
            Insert Into book(name,author,price) value(#{name},#{author},#{price})
        </insert>

![c867bbd8790f06c63bf39eaa2188633a.png][]

1.2问题

**如果不用框架来反填，使用纯粹的jdbc,但有的数据库不支持自增**

**原生jdbc写法:**

> **第一种：**Statement.RETURN\_GENERATED\_KEYS
> 
> 使用Statement.RETURN\_GENERATED\_KEYS回旋自增主键，stmt.getGeneratedKeys()获取自增主键，然后进行遍历，找出最大的自增主键
> 
> **如果表ID不是自动增长，想返回ID**

<insert id="addUserBackUUID" parameterType="com.hqyj.system.model.User2">
            <selectKey keyProperty="id" keyColumn="id" order="BEFORE" resultType="String">
                SELECT uuid()
            <lectKey>
            INSERT INTO user2 (id,username,birthday,sex,address) VALUES (#{id},#{username},#{birthday},#{sex},#{address});
        </insert>

1.3设置回填为全局配置

1.4全局后有些用不着

![fecd6c3b023c7cd207c844a94839ad60.png][]

1.5 解决多参数问题

> 1.  mybatis使用了arg0、arg1、arg2.....对应传过来的参数

Book book = new Book("天气预报", "zs", 10);
            System.out.println("最初的id="+book.getId());
    int num = bookMapper.insertOne(book);
        <insert id="insertOne2" >
            insert  into book(name,author,price) values (#{arg0},#{arg1},#{arg2})
        </insert>

2.使用@Param

测试：   
    int num = bookMapper.insertOne2("爱的供养","杨幂", (double) 100);
    接口：    
    int insertOne2(@Param("name") String name, @Param("author") String author,@Param("price") Double  price);
    xml:  
    <insert id="insertOne3" >
            insert  into book(name,author,price) values (#{name},#{author},#{price})
        </insert>

3.传递Map

> Map map = new HashMap();
> 
> map.put("name","...");
> 
> map.put("author","...");
> 
> map.put("price","...");

测试：

//方法三：
            HashMap<String, String> map = new HashMap<>();
            map.put("name","成功的秘诀");
            map.put("author","何梦");
            map.put("price","111");
            int num = bookMapper.insertOne3(map);

接口：

int insertOne3(Map map);
    xml: 
    <insert id="insertOne3" >
            insert  into book(name,author,price) values (#{name},#{author},#{price})
    </insert>

#### 2.修改功能-----if判断 ####

测试：

public class modifybook {
        public static void main(String[] args) throws IOException {
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sessionFactory.openSession();
    
            BookMapper bookMapper = session.getMapper(BookMapper.class);
    
            Book book = new Book(1, "我的爱", "lily", 200);
            int num = bookMapper.modify(book);
            System.out.println("结果="+num);
            session.commit();
            session.close();
        }
    
    }

mapper:

int modify(Book book);

XML：

<update id="modify" parameterType="com.hqyj.model.Book">
            update book  set name=#{name},author=#{author} ,price=#{price}  where id=#{id}
     </update>

当输入错误，作者为空时，不修改：

需要使用

<if test="条件"></if>
    
        <update id="modify" parameterType="com.hqyj.model.Book">
            update book  set name=#{name},
                             <if test="author!=null and author!=''">
                                author=#{author},
                             </if>
                             <if test="price!=null">
                                 price=#{price}
                             </if>
                             where id=#{id}
        </update>

#### 3.删除功能 ####

<delete id="deleteById">
      DELETE From  book  Where  id = #{id}
        </delete>

//删除
     int num1 = bookMapper.deleteById(18);    
     System.out.println("删除的结果num="+num1);
     int deleteById(int Id);

#### 4.模糊查询 ####

4.1 concat拼接

select * from book  where name like concat('%',#{author},'%');

4.2底层拼接

List<Book> books = bookMapper.selectByAuthor("%中%");
     
     select * from book where name like #{author}

4.3 value功能

select * from book  where name like '%${value}%';

4.4 拼接

select * from book  where name like '%' #{author} '%';

4.5 传入参数用法

select * from book  where name like '%${author}%';

4.6 \#和$的区别

1 \#表示sql模板的占位符，

2 \#可以防止sql注入，一般能用\#  
3 $\{\}内部的参数名必须写value。

#### 5.查询并排序 ####

5.1单个字段排序

//查询并排序
            List<Book> books = bookMapper.queryOrder("price");
            for (Book one:books) {
                System.out.println(one.getName()+":"+one.getAuthor());
            }
            session.commit();
            session.close();
    
    
            List<Book> queryOrder(String  xxx);
    
    
            <select id="queryOrder" parameterType="com.hqyj.model.Book">
                select * from book order  by ${value} desc
            </select>

#### 6.表字段和实体类属性的对应 ####

6.1 使用mapUnderscoreToCamelCase

数据库中的price字段名为:book\_price

实体类中的price为：bookprice

6.2 As 别名

mysql数据库定别名可以不写AS

sqlserver必须写AS

<select id="selectAll" resultType="com.hqyj.model.Book">
            SELECT id,name,author,price  money from Book
        </select>
    
        SELECT id,name,author,price as money from Book

6.3 resultMap（常用）

![ff7724c8f6a1903df91aba26f8205b53.png][]

[6f7456ff5c149f92329c84905915a579.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/bc3beb1bb94942738e2119acf8f4c531.png
[02515b79974a89b2c233963ddec283a8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/5a935e041cca48ca8fd0e8e975f7f6e4.png
[6a4b61b4034f923e7e6bd52245c61945.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/00eeab26560041bebca1173f592a8176.png
[6863199496a8a7f70c5817ddb4fd42e3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/0e8c440f7ca243428eec592e285c4a51.png
[c867bbd8790f06c63bf39eaa2188633a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/4b7afa1014d24dd8b5b5096e10709fad.png
[fecd6c3b023c7cd207c844a94839ad60.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/76f397f27b7844dc9531b7ca52d803d2.png
[ff7724c8f6a1903df91aba26f8205b53.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/25cb30eb6c194f0b9cd59ce82ed0c98e.png