【版本】
当前版本号v20211012
版本 | 修改说明 |
---|
v20211012 | 修正 spring-mvc.xml 注释 |
v20211009 | 修正 SqlMapConfig.xml 内容,缺失了一个",新增resource目录相关说明 |
v20210927 | 初始化版本 |
【实验名称】 实验4.1 新建 MyBatis 项目
【实验目的】
- 掌握新建 Maven 项目
- 掌握 MyBatis 框架的开发
【实验环境】
- 内存:至少4G
- 硬盘:至少空余10G
- 操作系统: 64位 Windows系统。
【实验资源】
【实验步骤】
参考实验2.2,新建一个新的 Maven Web 开发项目MyBatis
。
在 POM.xml 文件中加入依赖包的配置。
| <dependencies> |
| |
| <dependency> |
| <groupId>junit</groupId> |
| <artifactId>junit</artifactId> |
| <version>4.13.1</version> |
| <scope>test</scope> |
| </dependency> |
| |
| <dependency> |
| <groupId>mysql</groupId> |
| <artifactId>mysql-connector-java</artifactId> |
| <version>5.1.38</version> |
| </dependency> |
| |
| <dependency> |
| <groupId>org.mybatis</groupId> |
| <artifactId>mybatis</artifactId> |
| <version>3.5.4</version> |
| </dependency> |
| </dependencies> |
复制
- 在项目下新建目录
src\main\resources\db\migration
,用于存放数据库 SQL 脚本文件。

注意这里 IDEA 显示的db.migration
,并不是目录名称为db.migration
,而是一个层级目录如下所示。
| resources |
| |- db |
| |- migration |
复制
- 新建2个 SQL 脚本文件。
- 创建新的库和数据库用户脚本
V001__CREATE_DB.sql
,内容如下
| create database mobile_shop; |
| create user 'mshop'@'localhost' identified by 'hudp3ymVq5pMU47h'; |
| create user 'mshop'@'%' identified by 'hudp3ymVq5pMU47h'; |
| grant all on mobile_shop.* to 'mshop'@'localhost'; |
| grant all on mobile_shop.* to 'mshop'@'%'; |
复制
- 创建用户表的脚本
V002__CREATE_USER.sql
,内容如下
| CREATE TABLE `ms_user` ( |
| `user_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY comment '用户ID', |
| `username` VARCHAR(50) NOT NULL comment '用户名', |
| `password` VARCHAR(50) NOT NULL comment '密码' |
| ) |
| COLLATE='utf8_general_ci' |
| ENGINE=InnoDB; |
| |
| insert into `ms_user` (`username`,`password`) values('zhangsan','123456'); |
复制
- 启动MySQL,使用 HeidiSQL 连接 MySQL,并依照序号,先后执行以上2个 SQL 脚本文件。正常执行以后,运行以下 SQL 脚本应该能够查询到一条记录。
| use mobile_shop; |
| select * from ms_user; |
复制
- 编写以下配置文件。请注意存放的目录路径,没有该目录可以自己创建。

| driver=com.mysql.jdbc.Driver |
| url=jdbc:mysql://localhost:3306/mobile_shop?useUnicode=true&characterEncoding=UTF-8 |
| user=mshop |
| pwd=hudp3ymVq5pMU47h |
复制
- MyBatis 配置文件
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"> |
| |
| <configuration> |
| |
| <properties resource="db.properties"/> |
| <environments default="environment"> |
| <environment id="environment"> |
| |
| <transactionManager type="JDBC" /> |
| |
| |
| <dataSource type="POOLED"> |
| |
| <property name="driver" value="${driver}" /> |
| <property name="url" value="${url}"/> |
| <property name="username" value="${user}" /> |
| <property name="password" value="${pwd}" /> |
| </dataSource> |
| </environment> |
| </environments> |
| |
| <mappers> |
| <mapper resource="mobileshop/mapper/UserDaoMapper.xml"/> |
| </mappers> |
| </configuration> |
复制
- UserDao类对应的 MyBatis 映射配置
UserDaoMapper.xml
| <?xml version="1.0" encoding="UTF-8" ?> |
| <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" |
| "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"> |
| <mapper namespace="mobileshop.dao.UserDao"> |
| <select id="findAll" resultType="mobileshop.entity.UserEntity"> |
| select * from ms_user |
| </select> |
| </mapper> |
复制
注意这里 IDEA 显示的mobileshop.mapper
,并不是目录名称为mobileshop.mapper
,而是一个层级目录如下所示。
| resources |
| |- mobileshop |
| |- mapper |
复制
- 编写以下3个类。请注意存放的目录路径,没有该目录可以自己创建。

| package mobileshop.dao; |
| |
| import mobileshop.entity.UserEntity; |
| import org.apache.ibatis.annotations.Param; |
| |
| import java.util.List; |
| |
| public interface UserDao { |
| List<UserEntity> findAll(); |
| } |
复制
- 装载用户信息的类
UserEntity.java
,注意观察 UserEntity 类与ms_user
表之间,属性和列的对应关系。
| package mobileshop.entity; |
| |
| public class UserEntity { |
| private Long userId; |
| private String username; |
| private String password; |
| public String getPassword() { |
| return password; |
| } |
| public void setPassword(String password) { |
| this.password = password; |
| } |
| public Long getUserId() { |
| return userId; |
| } |
| public void setUserId(Long userId) { |
| this.userId = userId; |
| } |
| public String getUsername() { |
| return username; |
| } |
| public void setUsername(String username) { |
| this.username = username; |
| } |
| } |
复制
- 获取 MyBatis 的 SqlSession 的类
MybatisUtils.java
| package mobileshop.utils; |
| import java.io.InputStream; |
| |
| import org.apache.ibatis.session.SqlSession; |
| import org.apache.ibatis.session.SqlSessionFactory; |
| import org.apache.ibatis.session.SqlSessionFactoryBuilder; |
| |
| public class MybatisUtils { |
| |
| public static SqlSession getSqlSession(){ |
| |
| SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder(); |
| |
| ClassLoader loader= MybatisUtils.class.getClassLoader(); |
| InputStream inStream=loader.getResourceAsStream("SqlMapConfig.xml"); |
| SqlSessionFactory factory=builder.build(inStream); |
| SqlSession session=factory.openSession(); |
| return session; |
| } |
| } |
复制
- 编写单元测试用例,运行
testFindAll
方法,测试能否正常调用UserDao
的findAll
方法。正常情况下测试用例会通过。

| package mobileshop.dao; |
| |
| import mobileshop.entity.UserEntity; |
| import mobileshop.utils.MybatisUtils; |
| import org.apache.ibatis.session.SqlSession; |
| import org.junit.After; |
| import org.junit.Before; |
| import org.junit.Test; |
| |
| import java.util.HashMap; |
| import java.util.List; |
| import java.util.Map; |
| |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertTrue; |
| |
| public class UserDaoTest { |
| |
| private SqlSession session; |
| private UserDao userDao; |
| |
| @Before |
| public void beforeTest(){ |
| session=MybatisUtils.getSqlSession(); |
| userDao=session.getMapper(UserDao.class); |
| } |
| |
| @Test |
| public void testFindAll(){ |
| List<UserEntity> list= userDao.findAll(); |
| assertTrue(list.size()>0); |
| } |
| |
| @After |
| public void closeSession(){ |
| if(session!=null) { |
| session.commit(); |
| session.close(); |
| } |
| } |
| } |
复制
【实验名称】 实验4.2 开发用户的增删查改功能
【实验目的】
【实验环境】
- 内存:至少4G
- 硬盘:至少空余10G
- 操作系统: 64位 Windows系统。
【实验资源】
【实验要求】
- 在
UserDao
类新增以下方法。
| package mobileshop.dao; |
| |
| import mobileshop.entity.UserEntity; |
| import org.apache.ibatis.annotations.Param; |
| |
| import java.util.List; |
| |
| public interface UserDao { |
| |
| |
| |
| |
| List<UserEntity> findAll(); |
| |
| |
| |
| |
| |
| UserEntity findByUserName(String username); |
| |
| |
| |
| |
| |
| int updatePassword(@Param("username")String username,@Param("password")String password); |
| |
| |
| |
| |
| |
| |
| int save(UserEntity ue); |
| |
| |
| |
| |
| |
| |
| int deleteByUsername(String username); |
| } |
| |
复制
- 测试用例修改如下,该测试用例会测试
UserDao
新增的所有方法。
| package mobileshop.dao; |
| |
| import mobileshop.entity.UserEntity; |
| import mobileshop.utils.MybatisUtils; |
| import org.apache.ibatis.session.SqlSession; |
| import org.junit.After; |
| import org.junit.Before; |
| import org.junit.Test; |
| |
| import java.util.HashMap; |
| import java.util.List; |
| import java.util.Map; |
| |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertTrue; |
| |
| public class UserDaoTest { |
| |
| private SqlSession session; |
| private UserDao userDao; |
| |
| @Before |
| public void beforeTest(){ |
| session=MybatisUtils.getSqlSession(); |
| userDao=session.getMapper(UserDao.class); |
| } |
| |
| @Test |
| public void testFindAll(){ |
| List<UserEntity> list= userDao.findAll(); |
| assertTrue(list.size()>0); |
| } |
| |
| @Test |
| |
| |
| |
| public void testFindAll2(){ |
| List<UserEntity> list= session.selectList("findAll"); |
| assertTrue(list.size()>0); |
| } |
| |
| @Test |
| |
| |
| |
| public void testFindByUserName(){ |
| String username="zhangsan"; |
| UserEntity ue=userDao.findByUserName(username); |
| assertEquals(username,ue.getUsername()); |
| } |
| |
| @Test |
| |
| |
| |
| public void testFindByUserName2(){ |
| String username="zhangsan"; |
| UserEntity ue=session.selectOne("findByUserName",username); |
| assertEquals(username,ue.getUsername()); |
| } |
| |
| @Test |
| |
| |
| |
| public void testUpdatePassword(){ |
| int rs=userDao.updatePassword("zhangsan","123456"); |
| assertEquals(1,rs); |
| } |
| |
| @Test |
| |
| |
| |
| public void testUpdatePassword2(){ |
| Map<String,String> map=new HashMap<>(); |
| map.put("username","zhangsan"); |
| map.put("password","123456"); |
| int rs=session.update("updatePassword",map); |
| assertEquals(1,rs); |
| } |
| |
| @Test |
| |
| |
| |
| public void testSave(){ |
| UserEntity u=new UserEntity(); |
| u.setUsername("lisi"); |
| u.setPassword("135790"); |
| int rs=userDao.save(u); |
| assertEquals(1,rs); |
| } |
| |
| @Test |
| |
| |
| |
| public void testSave2(){ |
| UserEntity u=new UserEntity(); |
| u.setUsername("lisi"); |
| u.setPassword("135790"); |
| int rs=session.insert("save",u); |
| assertEquals(1,rs); |
| } |
| |
| @Test |
| |
| |
| |
| public void testDeleteByUsername(){ |
| UserEntity u=new UserEntity(); |
| u.setUsername("wangwu"); |
| u.setPassword("135790"); |
| userDao.save(u); |
| int rs=userDao.deleteByUsername("wangwu"); |
| assertEquals(1,rs); |
| } |
| |
| @Test |
| |
| |
| |
| public void testDeleteByUsername2(){ |
| UserEntity u=new UserEntity(); |
| u.setUsername("wangwu"); |
| u.setPassword("135790"); |
| userDao.save(u); |
| int rs=session.delete("deleteByUsername","wangwu"); |
| assertEquals(1,rs); |
| } |
| |
| @After |
| public void closeSession(){ |
| if(session!=null) { |
| session.commit(); |
| session.close(); |
| } |
| } |
| } |
| |
复制
- 请你根据
UserDao
新增的方法,完善UserDaoMapper.xml
,并且能够让UserDaoTest
所有测试通过。
【实验名称】 实验4.3 集成 MyBatis 框架到 Web 项目
【实验目的】
【实验环境】
- 内存:至少4G
- 硬盘:至少空余10G
- 操作系统: 64位 Windows系统。
【实验资源】
【实验要求】
结合实验2.2和实验4.2,完善真实访问数据库的登录功能。
新建 Maven 项目,项目目录结构如下:

以下为代码提示:
| package mobileshop.controller; |
| |
| import mobileshop.entity.UserEntity; |
| import mobileshop.service.UserService; |
| import org.springframework.beans.factory.annotation.Autowired; |
| import org.springframework.stereotype.Controller; |
| import org.springframework.web.bind.annotation.RequestMapping; |
| |
| @Controller |
| public class LoginController { |
| |
| @Autowired |
| private UserService userService; |
| |
| @RequestMapping(value = "/login") |
| public String login(){ |
| return "login"; |
| } |
| |
| @RequestMapping(value = "/signin") |
| public String signIn(String username, String password){ |
| |
| } |
| } |
复制
| package mobileshop.dao; |
| |
| import mobileshop.entity.UserEntity; |
| |
| public interface UserDao { |
| UserEntity findByUserName(String username); |
| } |
复制
| package mobileshop.entity; |
| |
| public class UserEntity { |
| private Long userId; |
| private String username; |
| private String password; |
| public String getPassword() { |
| return password; |
| } |
| |
| public void setPassword(String password) { |
| this.password = password; |
| } |
| |
| public Long getUserId() { |
| return userId; |
| } |
| |
| public void setUserId(Long userId) { |
| this.userId = userId; |
| } |
| |
| public String getUsername() { |
| return username; |
| } |
| |
| public void setUsername(String username) { |
| this.username = username; |
| } |
| |
| } |
复制
| package mobileshop.service; |
| |
| |
| import mobileshop.entity.UserEntity; |
| |
| public interface UserService { |
| boolean validUserPwd(UserEntity ue); |
| |
| UserEntity getUserByUsername(String username); |
| } |
复制
| package mobileshop.service; |
| |
| import mobileshop.dao.UserDao; |
| import mobileshop.entity.UserEntity; |
| import org.apache.commons.lang3.StringUtils; |
| import org.springframework.beans.factory.annotation.Autowired; |
| import org.springframework.stereotype.Service; |
| |
| @Service |
| public class UserServiceImpl implements UserService{ |
| |
| @Autowired |
| private UserDao userDao; |
| |
| @Override |
| public boolean validUserPwd(UserEntity ue) { |
| |
| } |
| |
| @Override |
| public UserEntity getUserByUsername(String username) { |
| return userDao.findByUserName(username); |
| } |
| } |
复制
Application.java 同实验2.2
UserDaoMapper.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="mobileshop.dao.UserDao"> |
| <select id="findByUserName" parameterType="string" |
| resultType="mobileshop.entity.UserEntity"> |
| select * from ms_user where username=#{username} |
| </select> |
| </mapper> |
复制
db.properties 同实验4.1
spring-mvc.xml
| <?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:context="http://www.springframework.org/schema/context" |
| xmlns:mvc="http://www.springframework.org/schema/mvc" |
| xmlns:util="http://www.springframework.org/schema/util" |
| xsi:schemaLocation=" |
| http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd |
| http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd |
| http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd |
| http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd"> |
| |
| <context:component-scan base-package="mobileshop.controller,mobileshop.service"/> |
| |
| <mvc:annotation-driven/> |
| |
| <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> |
| <property name="prefix" value="/WEB-INF/html/" /> |
| <property name="suffix" value=".html" /> |
| </bean> |
| |
| <util:properties id="jdbc" location="classpath:db.properties"/> |
| |
| <bean id="dbcp" class="org.apache.commons.dbcp.BasicDataSource"> |
| <property name="driverClassName" value="#{jdbc.driver}"/> |
| <property name="url" value="#{jdbc.url}"/> |
| <property name="username" value="#{jdbc.user}"/> |
| <property name="password" value="#{jdbc.pwd}"/> |
| </bean> |
| |
| |
| |
| <bean id="ssf" class="org.mybatis.spring.SqlSessionFactoryBean"> |
| |
| <property name="dataSource" ref="dbcp"/> |
| |
| <property name="mapperLocations" |
| value="classpath:mobileshop/mapper/*.xml"/> |
| </bean> |
| |
| |
| |
| <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> |
| |
| <property name="basePackage" |
| value="mobileshop.dao"/> |
| |
| <property name="sqlSessionFactory" ref="ssf"/> |
| </bean> |
| </beans> |
复制