在公司项目开发中,使用Mybatis居多。在 SpringBoot:3.SpringBoot使用Spring-data-jpa实现数据库访问 中,这种jpa风格的把sql语句和java代码放到一起,总感觉分离的不够彻底。基于个人习惯,还是比较喜欢把代码和sql分开,sql语句在xml文件里不管多复杂,写到xml里看起来比较简单,不是那么臃肿。
主要就四个依赖:
spring-boot-starter-test、spring-boot-starter:用于做单元测试
mybatis-spring-boot-starter:Mybatis核心依赖
mysql-connector-java:Mysql依赖,访问数据库
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.1.1</version> </dependency> <!-- 引入MySQL连接的依赖包 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.21</version> </dependency>
在application.properties 配置相关mysql信息
#数据库配置 spring.datasource.url=jdbc:mysql://localhost:3306/test spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.jdbc.Driver #mybatis放置xml文件的地方,我们配置在classpath下的mapper文件夹下 mybatis.mapper-locations=classpath*:mapper/*.xml
通过下面的sql语句创建user表信息:包括id、name(名字)、age(年龄)。
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
创建user表的映射实体类。
package com.w3cjava.entity; public class User { private Long id; private String name; private Integer age; public User() { } public User(String name, int age) { this.name = name; this.age = age; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } }
创建User实体类的UserDao层,实现简单的增删改查操作。
package com.w3cjava.dao; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import com.w3cjava.entity.User; @Mapper public interface UserDao{ int insert(@Param("name") String name, @Param("age") Integer age); /** * * @author cos * @desc 通过Map<String, Object>对象来作为传递参数的容器 * @param map * @return */ int insertByMap(Map<String, Object> map); /** * * @author cos * @desc 使用对象 * @param user * @return */ int insert(User user); void update(User user); void delete(Long id); /** * * @author cos * @desc 返回结果的绑定 * @return */ List<User> findAll(); /** * * @author cos * @desc 使用@Param传参,@Param中定义的name对应了SQL中的#{name},age对应了SQL中的#{age} * @param name * @return */ User findByName(@Param("name") String name); }
通过mybatis.mapper-locations=classpath :mapper/ .xml配置,我们将在classpath路径的mapper文件夹下创建UserDao对应的映射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="com.w3cjava.dao.UserDao"> <sql id="testColumns"> a.id AS "id", a.name AS "name", a.age AS "age" </sql> <sql id="testJoins"> </sql> <!-- 查询所有user --> <select id="findByName" resultType="com.w3cjava.entity.User"> select <include refid="testColumns"/> from user a WHERE a.name = #{name} </select> <!-- 查询所有user --> <select id="findAll" resultType="com.w3cjava.entity.User"> select <include refid="testColumns"/> from user a </select> <insert id="insert"> INSERT INTO user( name, age ) VALUES ( #{name}, #{age} ) </insert> <insert id="insertByMap"> INSERT INTO user( name, age ) VALUES ( #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER} ) </insert> <update id="update"> UPDATE user SET age=#{age} WHERE name=#{name} </update> <delete id="delete"> DELETE FROM user WHERE id =#{id} </delete> </mapper>
创建应用主类。@EnableTransactionManagement之所以加上这个注解,是为了后面每一个单元测试时使用回顾注解,保证数据库数据测试完成后不被污染。以便开始下一个测试。
package com.w3cjava; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.transaction.annotation.EnableTransactionManagement; /** * * @class SpringBootMybatisApplication * @version SpringBoot 2.1.9 * @author cos * @desc 整合Mybatis * */ @SpringBootApplication @EnableTransactionManagement public class SpringBootMybatisApplication { public static void main(String[] args) { SpringApplication.run(SpringBootMybatisApplication.class, args); } }
测试基本逻辑:
@RunWith(SpringRunner.class) @SpringBootTest(classes = {SpringBootMybatisApplication.class}) public class UserServiceTest { @Autowired private UserDao userDao; @Test @Transactional @Rollback public void insert() throws Exception { userDao.insert("AAA", 20); } @Test @Transactional @Rollback public void findByName() throws Exception { userDao.insert("AAA", 20); User u = userDao.findByName("AAA"); Assert.assertEquals(20, u.getAge().intValue()); } @Test @Transactional @Rollback public void testMap() throws Exception{ Map<String, Object> map = new HashMap<>(); map.put("name", "CCC"); map.put("age", 40); userDao.insertByMap(map); } @Test @Transactional @Rollback public void testuserDao() throws Exception { // insert一条数据,并select出来验证 userDao.insert("AAA", 20); User u = userDao.findByName("AAA"); Assert.assertEquals(20, u.getAge().intValue()); // update一条数据,并select出来验证 u.setAge(30); userDao.update(u); u = userDao.findByName("AAA"); Assert.assertEquals(30, u.getAge().intValue()); // 删除这条数据,并select验证 userDao.delete(u.getId()); u = userDao.findByName("AAA"); Assert.assertEquals(null, u); } @Test @Transactional @Rollback public void testSelectMapper() throws Exception { List<User> userList = userDao.findAll(); for(User user : userList) { Assert.assertEquals(null, user.getId()); Assert.assertNotEquals(null, user.getName()); } } }