傻瓜式使用MyBatis_mybatis更傻瓜

查看详情请去原文：
原文链接: https://xiezhenyu98.github.io/2019/10/13/傻瓜式使用MyBatis/

---

MyBatis如何快速使用？

MyBatis框架图

1、下载MyBatis、创建项目、导包

  下载MyBatis：https://github.com/mybatis/mybatis-3/releases

  创建项目之后导入MyBatis的核心包和依赖包以及MySQL的连接包

2、创建测试用例，测试数据库、测试Bean对象

  创建测试数据库，直接运行以下SQL语句即可

/*
SQLyog Professional v12.08 (64 bit)
MySQL - 5.5.49 : Database - ssm_mybatis
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`ssm_mybatis` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `ssm_mybatis`;
/*Table structure for table `country` */
DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '国家id',
  `c_countryname` varchar(128) NOT NULL COMMENT '国家名称',
  `c_capital` varchar(128) DEFAULT NULL COMMENT '国家首都名称',
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*Data for the table `country` */
insert  into `country`(`c_id`,`c_countryname`,`c_capital`) values (1,'中国','北京'),(3,'美国','华盛顿'),(4,'英国','伦敦'),(5,'日本','东京');
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `u_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `u_username` varchar(64) NOT NULL COMMENT '用户名',
  `u_password` varchar(64) DEFAULT NULL COMMENT '用户密码',
  `u_sex` varchar(16) DEFAULT NULL COMMENT '用户性别',
  `u_createTime` datetime DEFAULT NULL COMMENT '用户创建时间',
  `u_cid` int(11) DEFAULT NULL COMMENT '用户国家id',
  PRIMARY KEY (`u_id`),
  KEY `FK_user_cid` (`u_cid`),
  CONSTRAINT `FK_user_cid` FOREIGN KEY (`u_cid`) REFERENCES `country` (`c_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert  into `user`(`u_id`,`u_username`,`u_password`,`u_sex`,`u_createTime`,`u_cid`) values (1,'老王','123','1',NULL,1),(2,'jack','123321','1',NULL,3),(3,'alice','ali111','0',NULL,3),(4,'王司机','666','1',NULL,1),(5,'anna','ali111','0',NULL,4),(6,'李师傅','222','1',NULL,1),(7,'漩涡鸣人','233','1',NULL,5),(8,'娜美','233','0',NULL,5),(9,'王五','abc','1',NULL,1),(10,'老赵',NULL,NULL,NULL,NULL),(11,'老孙',NULL,NULL,NULL,NULL),(12,'98k',NULL,NULL,NULL,NULL);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

  创建测试Bean对象，与数据库创建的字段一致，并生成GET和SET方法。

public class User {
	private Integer u_id;
	private String u_username;
	private String u_password;
	private String u_sex;
	private Date u_createTime;
	private Integer u_cid;
	public Integer getU_id() {
		return u_id;
	}
	public void setU_id(Integer u_id) {
		this.u_id = u_id;
	}
	public String getU_username() {
		return u_username;
	}
	public void setU_username(String u_username) {
		this.u_username = u_username;
	}
	public String getU_password() {
		return u_password;
	}
	public void setU_password(String u_password) {
		this.u_password = u_password;
	}
	public String getU_sex() {
		return u_sex;
	}
	public void setU_sex(String u_sex) {
		this.u_sex = u_sex;
	}
	public Date getU_createTime() {
		return u_createTime;
	}
	public void setU_createTime(Date u_createTime) {
		this.u_createTime = u_createTime;
	}
	public Integer getU_cid() {
		return u_cid;
	}
	public void setU_cid(Integer u_cid) {
		this.u_cid = u_cid;
	}
	@Override
	public String toString() {
		return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + ", u_sex=" + u_sex
				+ ", u_createTime=" + u_createTime + ", u_cid=" + u_cid + "]";
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

3、创建sqlMapConfig.xml主配置文件

  以下只是sqlMapConfig.xml主配置文件的一部分(会在快速使用MyBatis中使用到的一部分)，如果想要查看完整的配置文件请查看官方文档：https://mybatis.org/mybatis-3/zh/configuration.html

<?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>
  <!-- 在集成spring时不用 -->
  <environments default="development">
    <environment id="development">
      <!-- 使用jdbc的事务 -->
      <transactionManager type="JDBC"/>
      <!-- 使用连接池 链接数据库 -->
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ssm_mybatis?serverTimezone=Asia/Shanghai&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="p123456"/>
      </dataSource>
    </environment>
  </environments>
  <!-- 引入映射文件 -->
  <mappers>
    <mapper resource="mapper/UserMapper.xml"/>
  </mappers>
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4、创建Mapper.xml映射文件

  Mapper.xml映射文件中可以书写了查找用户、模糊查找用户、添加用户、修改用户、删除用户的sql语句，其中模糊查找中如果用字符串拼接的形式是不安全的，推荐使用占位符模糊查找。
  由于使用占位符查找，如果这样使用#{id}，如果传过来的id为1，他会在#{id}这个位置上显示’1’，加上了单引号，我们都知道模糊查找是

select * from user where u_username like %你%
1

  如果我们使用占位符的话%‘你’%这样是不行的，所以我们书写SQL语句的时候可以这样用

select * from user where u_username like "%"'你'"%"
1

  以下是Mapper.xml映射文件

<?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="UserMapper">
	<!-- 查找用户 -->
	<select id="selectUserById" parameterType="Integer" resultType="com.siliedu.bean.User">
		select * from user where u_id = #{id}
	</select>
	<!-- #{}占位符 尽量选用占位符解决问题 -->
	<!-- ${}字符串拼接 -->
	<!-- 模糊查找用户 -->
	<select id="selectUserByName" parameterType="String" resultType="com.siliedu.bean.User">
		<!--  select * from user where u_username like '%${value}%' -->
		select * from user where u_username like "%"#{name}"%"
	</select>
	<!-- 添加用户 -->
	<insert id="insertUser" parameterType="com.siliedu.bean.User">
		insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})
	</insert>
	<!-- 修改用户 -->
	<update id="updateUser" parameterType="com.siliedu.bean.User">
		update user set u_username = #{u_username} where u_id=#{u_id}
	</update>
	<!-- 删除用户 -->
	<delete id="deleteUserById" parameterType="Integer">
		delete from user where u_id = #{id}
	</delete>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

5、书写测试方法

测试通过id查找用户

@Test
//通过ID查询用户
public void Test1() throws IOException {
	String resource = "sqlMapConfig.xml";
	//读取配置文件
	InputStream in = Resources.getResourceAsStream(resource);
	//需要sqlSessionFactoryBulider
	SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
	//创建sqlSessionFactory
	SqlSessionFactory ssf = ssfb.build(in);
	//生产一个sqlSession
	SqlSession session = ssf.openSession();
	//操作数据库
	//参数1:要操作的sql语句(对应UserMapper.xml里面的namespace.id)  参数2:sql语句的参数
	User user = session.selectOne("UserMapper.selectUserById", 1);
	System.out.println(user);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

测试通过名字模糊查询用户

@Test
//通过name模糊查询用户
public void Test2() throws IOException {
	String resource = "sqlMapConfig.xml";
	//读取配置文件
	InputStream in = Resources.getResourceAsStream(resource);
	//需要sqlSessionFactoryBulider
	SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
	//创建sqlSessionFactory
	SqlSessionFactory ssf = ssfb.build(in);
	//生产一个sqlSession
	SqlSession session = ssf.openSession();
	//操作数据库
	//参数1:要操作的sql语句(对应UserMapper.xml里面的namespace.id)  参数2:sql语句的参数
	List<User> list = session.selectList("UserMapper.selectUserByName", "王");
	for(User u : list) {
		System.out.println(u);
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

测试新增用户

由于对数据库进行了更新，session必须commit才能使事务提交，数据库才会更新

@Test
//通过新增用户
public void Test3() throws IOException {
	String resource = "sqlMapConfig.xml";
	//读取配置文件
	InputStream in = Resources.getResourceAsStream(resource);
	//需要sqlSessionFactoryBulider
	SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
	//创建sqlSessionFactory
	SqlSessionFactory ssf = ssfb.build(in);
	//生产一个sqlSession
	SqlSession session = ssf.openSession();
	//操作数据库
	//参数1:要操作的sql语句(对应UserMapper.xml里面的namespace.id)  参数2:sql语句的参数
	User user = new User();
	user.setU_username("小明");
	user.setU_password("123456789");
	user.setU_sex("1");
	user.setU_createTime(new Date());
	user.setU_cid(1);
	session.insert("UserMapper.insertUser", user);
	session.commit();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

测试修改用户

@Test
//修改用户
public void Test4() throws IOException {
	String resource = "sqlMapConfig.xml";
	//读取配置文件
	InputStream in = Resources.getResourceAsStream(resource);
	//需要sqlSessionFactoryBulider
	SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
	//创建sqlSessionFactory
	SqlSessionFactory ssf = ssfb.build(in);
	//生产一个sqlSession
	SqlSession session = ssf.openSession();
	//操作数据库
	//参数1:要操作的sql语句(对应UserMapper.xml里面的namespace.id)  参数2:sql语句的参数
	User user = new User();
	user.setU_id(13);
	user.setU_username("小刚");
	session.update("UserMapper.updateUser", user);
	session.commit();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

测试删除用户

@Test
//删除用户
public void Test5() throws IOException {
	String resource = "sqlMapConfig.xml";
	//读取配置文件
	InputStream in = Resources.getResourceAsStream(resource);
	//需要sqlSessionFactoryBulider
	SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
	//创建sqlSessionFactory
	SqlSessionFactory ssf = ssfb.build(in);
	//生产一个sqlSession
	SqlSession session = ssf.openSession();
	//操作数据库
	//参数1:要操作的sql语句(对应UserMapper.xml里面的namespace.id)  参数2:sql语句的参数
	session.delete("UserMapper.deleteUserById", 13);
	session.commit();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

---

以上就是MyBatis的简单使用。