半自动ORM—mybatis

最新推荐文章于 2024-03-20 17:42:15 发布

喜乐L

最新推荐文章于 2024-03-20 17:42:15 发布

阅读量477

点赞数 1

分类专栏： ssm框架学习文章标签： mybatis mysql java

本文链接：https://blog.csdn.net/weixin_46048259/article/details/126433229

版权

ssm框架学习专栏收录该内容

8 篇文章 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

driver
url
user
password

<!--    environments : 环境
     transactionManager :事务的管理者        
     dataSource : 数据源

-->
 <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&amp;useUnicode=true&amp;characterEncoding=utf8&amp;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"/>
    </settings>

日志组件的选择

<!--驼峰转换-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
            <!--日志组件的选择-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

1.4 mapper路径的写法

 <mappers>
<!--        <mapper resource="com/hqyj/mapper/BookMapper.xml"></mapper>-->
<!--        <mapper url="file:///D:/hqyj/ssm/mybatis/studyMybatis/src/com/hqyj/mapper/BookMapper.xml"></mapper>-->

        <package name="com.hqyj.mapper"/>
    </mappers>

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

<!--命名空间 namespace=""-->   
<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排序显示

<!--命名空间 namespace=""-->
<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代码

加载mybatis-config.xml
去执行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);


}

<!--命名空间 namespace=""-->
<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

5.1 自动扫描实体类编写

方式一：

方式二：

mapper.xml文件：

6.日志：级别

6.1 Logger{}接口：

interface Logger{

warn():警告

error()：致命提示

info():

falte():

}

6.2 实现者

log4J
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>

1.2问题

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

原生jdbc写法:

第一种：Statement.RETURN_GENERATED_KEYS

使用Statement.RETURN_GENERATED_KEYS回旋自增主键，stmt.getGeneratedKeys()获取自增主键，然后进行遍历，找出最大的自增主键

如果表ID不是自动增长，想返回ID

  <!--增加返回ID【类型UUID】-->
    <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设置回填为全局配置

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

1.4全局后有些用不着

1.5 解决多参数问题

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

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

数据库中的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（常用）

<resultMap id="" type="实体类">

<id property="" column=""></id>   <!--用于主键-->

<result column="数据库字段名" property="实体类的变量">.....
</resultMap>


<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="money"></result>
</resultMap>