目录
一.主流持久层框架
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>
四.动态代理
可移步 设计模式之代理模式, 有较为详细的介绍