Mybatis实现单表增删改查操作
mybatis是对持久层进行了封装、mybatis文档地址：https://mybatis.org/mybatis-3/zh/index.html
下面实现单表的增删改查操作。
1.新建maven项目命名为mybatis、并在pom.xml中引入相关依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.itheima</groupId> <artifactId>mybaits</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.12</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.10</version> </dependency> </dependencies> </project>
2.数据库命名为eesy_mybatis,执行下列sql语句
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL auto_increment, `username` varchar(32) NOT NULL COMMENT '用户名称', `birthday` datetime default NULL COMMENT '生日', `sex` char(1) default NULL COMMENT '性别', `address` varchar(256) default NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');
3编写实体类User与QV，包目录结构如下
public class User implements Serializable { //实体属性与数据库字段一一对应 private Integer id; private String username; private Date birthday; private String sex; private String address; //getter/setter【已省略】 }
public class QV { private User user; private List<Integer> ids; //getter/setter【已省略】 }
4.编写接口UserDao,包目录结构如下：
package com.dao; import com.domain.QV; import com.domain.User; import java.util.List; public interface userDao { //查询所有用户 List<User> findAll(); //保存单个用户 int saveUser(User user); //更新用户信息 int updateUser(User user); //删除用户信息 int deleteUser(int id); //根据用户姓名模糊查询用户 List<User> findUserByName(String name); // List<User> findByQV(String name); //多条件查询 List<User> findByUser(User user); //传入查询条件QV对多个ID进行范围查询 List<User> findByIds(QV qv); }
5.编写mybaits的主配置函数SqlMapConfig.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"> <!-- mybatis的主配置文件 --> <configuration> <!-- 配置环境 --> <environments default="mysql"> <!-- 配置mysql的环境--> <environment id="mysql"> <!-- 配置事务的类型--> <transactionManager type="JDBC"></transactionManager> <!-- 配置数据源（连接池） --> <dataSource type="POOLED"> <!-- 配置连接数据库的4个基本信息 --> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/eesy_mybatis?useUnicode=true&characterEncoding=utf8"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <!-- 配置子配置文件位置 --> <!-- 映射位置一一对应，包括包目录结构--> <mappers> <mapper resource="com/dao/userDao.xml"/> </mappers> </configuration>
6.编写子配置文件userDao.xml,包目录结构如下
<?xml version="1.0" encoding="UTF-8"?> <!--mybaits头约束 --> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- 配置映射，填写绝对路径 --> <mapper namespace="com.dao.userDao"> <!-- 了解的内容：抽取重复的sql语句--> <sql id="defaultUser"> select * from user </sql> <!--配置查询所有用户--> <select id="findAll" resultType="com.domain.User"> select * from user </select> <!--保存单个用户,属性需要与domain实体中的属性一一对应--> <insert id="saveUser" parameterType="com.domain.User"> insert into user(username,address,sex,birthday)values(#{username},#{address},#{sex},#{birthday}); </insert> <!--更新用户信息--> <update id="updateUser" parameterType="com.domain.User"> update user set username=#{username},sex=#{sex} where id=#{id}; </update> <!--删除用户--> <delete id="deleteUser"> delete from user where id=#{id}; </delete> <!--模糊查询--> <select id="findUserByName" parameterType="String" resultType="com.domain.User"> select * from user where username like #{username}; </select> <!--参数为对象类型 --> <select id="findByQV" parameterType="String" resultType="com.domain.User"> select * from user where username like #{user.username}; </select> <!--多条件查询--> <select id="findByUser" parameterType="com.domain.User" resultType="com.domain.User"> select * from user <where> <if test="username!=null and username != ''"> and username like #{username} </if> <if test="sex!=null and sex != ''"> and sex like #{sex} </if> </where> </select> <!--传入多个ID对用户进行范围查询--> <select id="findByIds" parameterType="com.domain.QV" resultType="com.domain.User"> <include refid="defaultUser"></include> <where> <if test="ids != null and ids.size()>0"> <foreach collection="ids" open="and id in (" close=")" item="id" separator=","> #{id} </foreach> </if> </where> </select> </mapper>
7.编写测试类
package com.dao; import com.domain.QV; import com.domain.User; 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 org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.ArrayList; import java.util.Date; import java.util.List; /** * @author admin * @version 1.0.0 * @ClassName mybaits.java * @Description TODO * @createTime 2020年01月10日 15:48:00 */ public class mybaits {
private InputStream in; private SqlSession sqlSession; private userDao userDao; @Before//用于在测试方法执行之前执行 public void init() throws Exception { //1.读取配置文件，生成字节输入流 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); //3.获取SqlSession对象 sqlSession = factory.openSession(); //4.获取dao的代理对象 userDao = sqlSession.getMapper(userDao.class); } @After//用于在测试方法执行之后执行 public void destroy() throws Exception { //提交事务 sqlSession.commit(); //6.释放资源 sqlSession.close(); in.close(); } @Test public void findAll(){ List<User> list = userDao.findAll(); for(User u : list){ System.out.println(u.toString()); } } @Test public void save() { User user = new User(); user.setUsername("modify User property"); user.setAddress("北京市顺义区"); user.setSex("男"); user.setBirthday(new Date()); //执行方法 userDao.saveUser(user); } @Test public void update() { User user = new User(); user.setId(51); user.setUsername("update UserName"); user.setSex("女"); //执行方法 userDao.updateUser(user); } @Test public void delete() { User user = new User(); user.setId(51); //执行方法 userDao.deleteUser(user.getId()); } @Test public void findByUserName() { //执行方法 List<User> list = userDao.findUserByName("小%"); for(User user:list){ System.out.println(user.toString()); } } @Test public void findByQV() { //执行方法 List<User> list = userDao.findByQV("小%"); for(User user:list){ System.out.println(user.toString()); } } //多条件查询 @Test public void findByUser() { User user = new User(); user.setUsername("小%"); user.setSex("男"); //执行方法 List<User> list = userDao.findByUser(user); for(User u:list){ System.out.println(u.toString()); } } //多个ID进行范围查询 @Test public void findByIds() { List<Integer> list = new ArrayList<Integer>(); list.add(42); list.add(43); list.add(45); QV qv = new QV(); qv.setIds(list); //执行方法 List<User> users = userDao.findByIds(qv); for(User u:users){ System.out.println(u.toString()); } } }
笔记：
无反射不框架，mybatis框架很多地方要求一一对应，按别人的规则来利用这种一一对应以及相应的getter/setter可帮助开发
动态SQL是mybatis的一大特点，上述简单使用了下,动态sql文档的说明：https://mybatis.org/mybatis-3/zh/dynamic-sql.html
尽量保证实体类的属性与数据库字段的列名一致，如果不一致需要自己在子配置文件中进行相关配置，用resultMap
<!-- 配置 查询结果的列名和实体类的属性名的对应关系 --> <resultMap id="userMap" type="uSeR"> <!-- 主键字段的对应 --> <id property="userId" column="id"></id> <!--非主键字段的对应--> <result property="userName" column="username"></result> <result property="userAddress" column="address"></result> <result property="userSex" column="sex"></result> <result property="userBirthday" column="birthday"></result> </resultMap>
