Mybatis

最新推荐文章于 2024-08-19 13:32:03 发布

xiaoxiaosu1996

最新推荐文章于 2024-08-19 13:32:03 发布

阅读量104

点赞数

分类专栏：框架文章标签： hibernate 数据库 mysql

本文链接：https://blog.csdn.net/xiaoxiaosu1996/article/details/120257723

版权

框架专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一.主流持久层框架

1.JDBC

使用JDBC的五个步骤:

注册驱动和数据库信息
获得Connection, 并使用它打开Statement对象
通过Statement对象执行SQL语句, 并获得结果对象ResultSet
通过代码将ResultSet对象转化为POJO对象
关闭数据库资源

缺点:

代码量很大, 麻烦。
需要我们对异常进行正确捕获并关闭链接。

public static void main(String[] args) throws Throwable {
	// 1.注册驱动和数据库信息
	// PS: jdk1.6及其以上+mysql-connector-java-5.1.5.jar版本以上可省略
    Class.forName("com.mysql.jdbc.Driver");

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet rs = null;
    try {
        // 2.获得Connection, 并使用它打开statement对象
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/learn-test?userSSL=false&serverTimezone=GMT%2b8",
                "root",
                "root");

        // 3.通过statement对象执行SQL语句, 并获得结果对象ResultSet
        // preparedStatement = connection.prepareStatement("SELECT `name`, age FROM tb_user");
        // rs = preparedStatement.executeQuery();

        preparedStatement = connection.prepareStatement("SELECT `name`, age FROM tb_user WHERE id = ?");
        preparedStatement.setInt(1, 1);
        rs = preparedStatement.executeQuery();

        // 4.获得Connection, 并使用它打卡statement
        while (rs.next()) {
            System.out.println(String.format("姓名: %s, 年龄: %d", rs.getString("name"), rs.getInt("age")));
        }
    } catch (Throwable e) {
        System.out.println("error: " +e.getMessage());
    } finally {
        close(connection, preparedStatement, rs);
    }
}

/**
 * 5.关闭数据库资源
 * 版本不同, 资源关闭有差异
 */
private static void close(Connection connection,
                          PreparedStatement ps,
                          ResultSet rs) {
    try {
        if (rs != null && !rs.isClosed()) {
            rs.close();
        }
    } catch (Throwable e) {
        System.out.println("rs.close error! " + e.getMessage());
    }

    try {
        if (ps != null && !ps.isClosed()) {
            ps.close();
        }
    } catch (Throwable e) {
        System.out.println("ps.close error! " + e.getMessage());
    }

    try {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    } catch (Throwable e) {
        System.out.println("connection.close error! " + e.getMessage());
    }
}

2.Hibernate

优点:

将映射规则分离到XML注解中, 减少了代码的耦合度
无需管理数据库连接, 只需配置响应的XML
一个会话, 只需要操作Session对象即可
关闭资源, 只需关闭Session即可

缺点:

全表映射不便利, 更新时需要发送所有字段
无法根据不同的条件组装不同的SQL(语句自动生成)
对于多表关联和复杂SQL查询支持较差, 需要自己写SQL, 返回后, 需要自己将数据组装为POJO
HQL(Hibernate Query Language)性能较差, 无法优化SQL
不能有效支持存储过程

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <!-- 配置数据库连接 connection -->
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/learn-test?useSSL=false</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
        <!-- 数据库方言 MySQL -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <!-- 格式化输出生成的SQL语句 -->
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>

        <!-- 加载映射文件  -->
        <mapping resource="xx.xml" />
    </session-factory>
</hibernate-configuration>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
	<!--如使用idea且tb_user, column爆红, 可添加目标database配置信息, 进行辅助关联-->
    <class name="xx.User" table="tb_user">
        <id name = "id" column="id">
            <!--主键生成策略-->
            <generator class="native"/>
        </id>
        <property name="name" column="name"/>
        <property name="age" column="age"/>
    </class>
</hibernate-mapping>

public static void main(String[] args) {
    Configuration configuration = new Configuration().configure("xx.xml");
    SessionFactory sessionFactory = configuration.buildSessionFactory();
    Session session = null;
    try {
        session = sessionFactory.openSession();
        User user = session.get(User.class, 2L);
        System.out.println("user = " + user);
    } catch (Throwable e) {
        System.out.println("error: " + e.getMessage());
    } finally {
        if (session != null) {
            session.close();
            sessionFactory.close();
        }
    }
}

注意点:

<dependency>
   <groupId>org.hibernate</groupId>
    <artifactId>hibernate-agroal</artifactId>
    <version>5.4.18.Final</version>
    <!-- 如未指定pom, 则会出现以下错误 -->
    <type>pom</type>
</dependency>

Caused by: java.lang.IllegalArgumentException: max size attribute is mandatory
	at io.agroal.api.configuration.supplier.AgroalConnectionPoolConfigurationSupplier.validate(AgroalConnectionPoolConfigurationSupplier.java:237)
	at io.agroal.api.configuration.supplier.AgroalConnectionPoolConfigurationSupplier.get(AgroalConnectionPoolConfigurationSupplier.java:278)
	at io.agroal.api.configuration.supplier.AgroalConnectionPoolConfigurationSupplier.get(AgroalConnectionPoolConfigurationSupplier.java:25)
	at io.agroal.api.configuration.supplier.AgroalDataSourceConfigurationSupplier.connectionPoolConfiguration(AgroalDataSourceConfigurationSupplier.java:53)
	at io.agroal.api.configuration.supplier.AgroalPropertiesReader.readProperties(AgroalPropertiesReader.java:176)
	at org.hibernate.agroal.internal.AgroalConnectionProvider.configure(AgroalConnectionProvider.java:81)
	... 22 more

3.Mybatis

可以配置动态SQL
可以对SQL进行优化, 并通过配置来决定SQL的映射规则
具有自动映射功能, 在注意命名规则的基础上, 无需在写映射规则
MyBatis提供接口编程的映射器, 只需要一个接口和映射文件便可以运行
与代码耦合度低
支持存储过程

# xx.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/learn-test?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
username=root
password=root

<?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 resource="../xx.properties"/>

    <settings>
        <!--打印查询语句-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!-- NONE PARTIAL(默认) FULL -->
        <setting name="autoMappingBehavior" value="PARTIAL"/>
        <!-- 配置驼峰转下划线 数据库中的下划线，转换Java Bean中的驼峰 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--别名-->
    <typeAliases>
        <package name="..."/>
    </typeAliases>

    <environments default="dev">
        <environment id="dev">
            <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>

    <!--数据库厂商标识-->
    <databaseIdProvider type="DB_VENDOR"/>

    <!-- mappers 映射器 -->
    <mappers>
        <!-- 使用相对于类路径的资源引用 -->
        <mapper resource=".../UserMapper.xml"/>

        <!-- 使用完全限定资源定位符(URL) -->
        <!--<mapper url="file://.../xx.xml"/>-->

        <!-- (idea)以下两种方式需要将mapper.class以及mapper.xml放在同一文件夹下, 且pom文件需要额外进行编译配置(*.xml文件在非resources文件夹下不进行编译) -->
        <!-- 使用映射器接口实现类的完全限定类名 -->
        <!--<mapper class="..."/>-->

        <!-- 将包内的映射器接口实现全部注册为映射器 -->
        <!--<package name="..."/>-->
    </mappers>

</configuration>

public class SqlSessionFactoryUtil {

    private static SqlSessionFactory sqlSessionFactory;

    public static SqlSession openSession() {
        if (Objects.isNull(sqlSessionFactory)) {
            init();
        }
        // true: 开启自动commit, 默认false
        return sqlSessionFactory.openSession(true);
    }

    private static SqlSessionFactory init() {
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream("xx.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        synchronized (SqlSessionFactoryUtil.class) {
            if (sqlSessionFactory == null) {
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            }
            return sqlSessionFactory;
        }
    }

}

public static void main(String[] args) {
   SqlSession sqlSession = null;
    try {
        sqlSession = SqlSessionFactoryUtil.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(2L);
        System.out.println("user = " + user);
    } catch (Throwable e) {
        System.out.println("error: " + e.getMessage());
    } finally {
        if (Objects.nonNull(sqlSession)) {
            sqlSession.close();
        }
    }
}

二.MyBatis的使用

1.select

<select id="selectUser" parameterType="int" resultType="user">
	SELECT * FROM tb_user WHERE id = #{id}
</select>

<select
	id="selectUser"
	parameterType="int"
	parameterMap="deprecated"
	resultType="user"
	resultMap="userResultMap"
	flushCache="false"
	useCache="true"
	timeout="10"
	fetchSize="256"
	statementType="PREPARED"
	resultSetType="FORWARD_ONLY">

2.insert

<insert id="insertUser">
	INSERT INTO tb_user (name, age) values (#{username}, #{age})
</insert>

<insert
	id="insertUser"
	parameterType="user"
	flushCache="true"
	statementType="PREPARED"
	keyProperty=""
	keyColumn=""
	useGeneratedKeys=""
	timeout="20">

回显新增后的主键值设置:useGeneratedKeys="true"且keyProperty设置为目标属性

3.update

<update id="updateUser">
	UPDATE tb_user SET name = #{name}, age= #{age} WHERE id = #{id}
</update>

<update
	id="updateUser"
	parameterType="user"
	flushCache="true"
	statementType="PREPARED"
	timeout="20">

4.delete

<delete id="deleteUser">
	DELETE FROM tb_user WHERE id = #{id}
</delete>

<delete
	id="deleteUser"
	parameterType="user"
	flushCache="true"
	statementType="PREPARED"
	timeout="20">

4.入参和结果集

①.$与#的区别

采用值传递的方式构建SQL语句($, 存在SQL注入的问题)
采用预编译的方式构建SQL语句(#)

例如: tb_user中有以下数据

id(int)	name(String)	age(int)
1	测试1	18
2	测试2	20
3	测试3	19

<!-- $ -->
<select id="selectUserByStrId" parameterType="string" resultType="user">
	SELECT * FROM tb_user WHERE id = ${id}
</select>

// 此时会将全表数据都查询出来
List<User> user = ListuserMapper.selectUserByStrId("1 or (select count(1) from tb_user) > 0")

<!-- # -->
<select id="selectUserByStrId" parameterType="string" resultType="user">
	SELECT * FROM tb_user WHERE id = #{id}
</select>

// 此时只会查询到id=1的这一条数据
List<User> user = ListuserMapper.selectUserByStrId("1 or (select count(1) from tb_user) > 0")

②.存储结果集方式有哪些

map方式存储结果集
POJO方式存储结果集

5.级联

一对一级联: association
一对多级联: collection

6.缓存

一级缓存
注: (默认设置)只缓存在当前sqlSession实例中
二级缓存
开启方式: 1.xxMapper中添加<cache/>
2.执行一次后, 需要主动执行sqlSession.commit()
自定义缓存
实现方式: 1.实现org.apache.ibatis.cache.Cache接口
2.xxMapper中添加<cache type="实现类"/>
3.执行一次后, 需要主动执行sqlSession.commit()

三.动态SQL

1.if、test

<select id="findUserByAgeWithNameLike" resultType="user">
	SELECT
		* 
	FROM 
		tb_user
	WHERE
		age = 18
		<if test="name != null and name != ''">
			AND `name` like #{name}
		</if>
</select>

2.choose、when、otherwise

<select id="findUserLike" resultType="user">
	SELECT
		*
	FROM
		tb_user
	WHERE
		age = 18
		<choose>
			<when test="name != null and name != ''">
				AND `name` like #{name}
			</when>
			<otherwise>
				AND id = 1
			</otherwise>
		</choose>
</select>

3.trim、where、set

<update id="updateUser">
	UPDATE tb_user
	<set>
		<if test="name != null and name != ''">
			`name` = #{name},
		</if>
		<if test="age != null">
			age = #{age}
		</if>
	</set>
	<where>
		id = #{id}
	</where>
</update>

<trim prefix="SET" suffixOverrides=",">
...
</trim>

4.foreach

<select id="selectUser" resultType="user">
	SELECT
		*
	FROM
		tb_user
	WHERE
		id IN
		<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
			#{item}
		</foreach>
</select>

5.bind

<select id="selectUsersLike" resultType="user">
<bind name="name" value="'%' + _parameter.getName() + '%'" />
	SELECT
		*
	FROM
		tb_user
	WHERE
		`name` LIKE #{name}
</select>

四.动态代理

可移步设计模式之代理模式, 有较为详细的介绍

五.参考文档

mybatis官方中文文档

xiaoxiaosu1996

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis

目录一.主流持久层框架1.JDBC2.Hibernate3.Mybatis二.MyBatis的使用1.select2.insert3.update4.delete4.入参和结果集①.$与#的区别②.存储结果集方式有哪些5.级联6.缓存三.动态SQL1.if、test2.choose、when、otherwise3.trim、where、set4.foreach5.bind四.动态代理五.参考文档一.主流持久层框架1.JDBC使用JDBC的五个步骤:注册驱动和数据库信息获得Connection,.
复制链接

扫一扫

专栏目录