Mybatis(一):基本使用
一 前言
Mybatis是我们常用的一个持久层框架,他支持自定义SQL、存储过程以及高级映射。Mybatis封装了JDBC的一些逻辑,简化了我们的开发。
本篇文章是Mybatis的第一篇文章,在本文中我们主要介绍下Mybatis的基础知识,包括Mybatis的单独使用、Spring集成及Spring Boot中使用。
本篇文章中涉及的示例代码我会提交到github上,地址会在文末给出,需要的可以自行去下载。
示例中使用的依赖版本如下:
- JDK 1.8
- Mybatis 3.5.7
- Spring 5.3.9
- Druid 1.2.6
- mysql-connector 8.0.19
- SpringBoot 2.5.6
二 基本使用
数据库表结构如下
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(3) NOT NULL DEFAULT '0' COMMENT '年龄',
`gender` varchar(20) NOT NULL DEFAULT '' COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
1 JDBC的使用
我们直接JDBC进行数据操作时的流程如下:
1.加载驱动
2.建立连接
3.创建Statement对象
4.执行SQL语句
5.处理结果集
6.关闭资源
示例代码如下
public class JDBCSample {
private static final String DS_URL = "jdbc:mysql://localhost:3306/user";
private static final String DS_USERNAME = "root";
private static final String DS_PASSWORD = "xiehua123";
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立连接
connection = DriverManager.getConnection(DS_URL, DS_USERNAME, DS_PASSWORD);
// SQL语句
String querySql = "SELECT * FROM user";
// 创建statement对象
statement = connection.prepareStatement(querySql);
// 执行sql
resultSet = statement.executeQuery(querySql);
// 解析结果
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String gender = resultSet.getString("gender");
System.out.println("id:" + id + " name:" + name + " age:" + age + " gender:" + gender);
}
resultSet.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2 Mybatis使用
在上面的内容中我们介绍了JDBC的使用,在本部门我们介绍下如何单独的使用Mybatis进行数据库操作。
创建maven项目,并引入如下依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
</dependency>
在resource目录下创建mybatis.xml和datasource.properties文件,其内容如下:
mybatis.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 resource="datasource.properties"/>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${datasource.driver}"/>
<property name="url" value="${datasource.url}"/>
<property name="username" value="${datasource.username}"/>
<property name="password" value="${datasource.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
datasource.properties
datasource.driver = com.mysql.cj.jdbc.Driver
datasource.url = jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8
datasource.username = root
datasource.password = xiehua123
User实体对象如下
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;
/**
* Create By IntelliJ IDEA
*
* @author: XieHua
* @date: 2021-08-03 17:39
*/
@Setter
@Getter
public class User implements Serializable {
private Integer id;
private String name;
private Integer age;
private String gender;
}
创建UserMapper类和UserMapper.xml文件
import com.xh.sample.mybatis.entity.User;
import org.apache.ibatis.annotations.Param;
/**
* Create By IntelliJ IDEA
*
* @author: XieHua
* @date: 2021-08-02 15:05
*/
public interface UserMapper {
User getById(@Param("id") Integer id);
}
<?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="com.xh.sample.mybatis.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.xh.sample.mybatis.entity.User">
<result column="id" property="id" jdbcType="INTEGER"/>
<result column="age" property="age" jdbcType="INTEGER"/>
<result column="gender" property="gender" jdbcType="VARCHAR"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
</resultMap>
<select id="getById" resultMap="BaseResultMap">
SELECT * FROM user WHERE id = ${id}
</select>
</mapper>
至此我们的项目就创建完了,项目目录如下:
创建测试类进行测试,测试类代码如下
import com.alibaba.fastjson.JSON;
import com.xh.sample.mybatis.entity.User;
import com.xh.sample.mybatis.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
* Create By IntelliJ IDEA
*
* @author: XieHua
* @date: 2021-08-02 15:01
*/
public class TestDemo {
public static void main(String[] args) throws IOException {
String resource = "mybatis.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getById(1);
System.out.println(JSON.toJSONString(user));
}
}
运行我们发现可以正常获取到数据库里相应的数据,通过与JDBC的操作代码比较,我们会发现Mybatis给我们封装了驱动加载、连接建立、SQL执行及结果集的映射等。
3 Spring集成Mybatis
在正常工作中,我们通常都是在Spring环境下使用的Mybatis的。
在接下来我们使用Spring来集成Mybatis和Druid来进行数据库操作。
创建一个Maven项目并添加相关依赖
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
</dependency>
在resources目录下创建applicationContext.xml和application.properties文件,如下
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.3.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">
<context:property-placeholder location="classpath:application.properties"/>
<context:component-scan base-package="com.xh.sample.mybatis.spring"/>
<!-- druid连接池 -->
<bean name="datasource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="driverClassName" value="${datasource.druid.driver-class-name}"/>
<property name="url" value="${datasource.druid.url}"/>
<property name="username" value="${datasource.druid.username}"/>
<property name="password" value="${datasource.druid.password}"/>
</bean>
<bean name="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="datasource"/>
<property name="mapperLocations" value="classpath:mapper/*Mapper.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
<property name="basePackage" value="com.xh.sample.mybatis.spring.mapper"/>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="datasource"/>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
datasource.druid.url = jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8
datasource.druid.driver-class-name = com.mysql.cj.jdbc.Driver
datasource.druid.username = root
datasource.druid.password = xiehua123
User、UserMapper和UserMapper.xml文件同上。
创建UserService及其实现类
/**
* Create By IntelliJ IDEA
*
* @author: XieHua
* @date: 2021-08-03 17:45
*/
public interface UserService {
/**
* 添加用户
*
* @param user 用户对象
* @return 是否成功
*/
boolean insert(User user);
/**
* 通过id获取用户
*
* @param id 用户id
* @return 用户对象
*/
User getById(Integer id);
}
@Service
public class UserServiceImpl implements UserService {
@Resource
private UserMapper userMapper;
public boolean insert(User user) {
return userMapper.insert(user) > 0;
}
public User getById(Integer id) {
return userMapper.getById(id);
}
}
创建测试类如下
/**
* Create By IntelliJ IDEA
*
* @author: XieHua
* @date: 2021-08-03 17:47
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class UserServiceTest {
@Resource
private UserService userService;
@Test
public void insertTest() {
User user = new User();
user.setAge(17);
user.setName("李四");
user.setGender("男");
boolean result = userService.insert(user);
System.out.println("添加用户结果为:" + result);
}
@Test
public void getByIdTest() {
User user = userService.getById(1);
System.out.println("获取用户结果为:" + JSON.toJSONString(user));
}
}
项目目录如下图所示
运行测试类发现可以正常的操作数据库。
4 SpringBoot集成Mybatis
在SpringBoot项目下我们集成Mybatis会更加简单。
User、UserMapper、UserMapper.xml、UserService和UserServiceImpl的代码和上部分的一致,我这里就不再展示了。
在resources目录下创建application.yml文件,其内容如下:
spring:
datasource:
druid:
url: jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8
username: root
password: xiehua123
driver-class-name: com.mysql.cj.jdbc.Driver
创建DataSourceConfig类,代码如下:
/**
* Create By IntelliJ IDEA
*
* @author: XieHua
* @date: 2021-08-05 18:00
*/
@Configuration
@EnableTransactionManagement
@MapperScan("com.xh.sample.mybatis.springboot.mapper")
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.druid")
public DataSource dataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource());
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mapper/**/*Mapper.xml"));
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject();
return sqlSessionFactory;
}
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dataSource());
}
}
创建项目的启动类Bootstarp,内容如下:
/**
* Create By IntelliJ IDEA
*
* @author: XieHua
* @date: 2021-08-05 18:09
*/
@SpringBootApplication
public class Bootstrap {
public static void main(String[] args) {
SpringApplication.run(Bootstrap.class, args);
}
}
测试类如下
@SpringBootTest
public class UserServiceTest {
@Resource
private UserService userService;
@Test
public void insertTest() {
User user = new User();
user.setAge(17);
user.setName("SpringBoot");
user.setGender("男");
boolean result = userService.insert(user);
System.out.println("添加用户结果为:" + result);
}
@Test
public void getByIdTest() {
User user = userService.getById(1);
System.out.println("获取用户结果为:" + JSON.toJSONString(user));
}
}
至此我们在SpringBoot项目中集成Mybatis的代码便写完了,项目目录如下:
三 总结
本篇文章是Mybatis相关的第一篇文章,在这篇文章中主要介绍了下Mybatis的基本使用。想对Mybatis的使用有更多了解的可以去看其官方文档,地址为:https://mybatis.org/mybatis-3/zh/index.html。
本篇文章的代码已上传github,需要的可以去自取:https://github.com/xiehuaa/mybatis-sample
如果感觉对您有帮助,欢迎关注我的公众号“Bug搬运小能手”,或者扫面下面二维码进行关注