在前三篇博文(《MyBatis(1)框架原理 》、《MyBatis (2)配置 》、《MyBatis(3)Mapper XML文件 》)的基础上,我们编写一个MyBatis的入门程序。
环境搭建
- IDE: IDEA 13
- DataBase: MySQL 5.5.43
- Java Version: JDK1.8.0_11
其他的库信息如下:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.32</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.6.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
Log4J日志配置:
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.indi.latch.mybatis=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
需求分析
对数据库中的用户表进行按ID查询、按用户名模糊查询、插入新用户、按ID删除用户、更新指定用户信息。
数据库
- 用户表
CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) COLLATE utf8_bin NOT NULL COMMENT '用户名称',
`pass` varchar(45) COLLATE utf8_bin NOT NULL COMMENT '用户密码',
`birthday` DATE COMMENT '生日',
`sex` char(1) COMMENT '性别',
`address` varchar(256) COMMENT '地址',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
MyBatis配置(mybatis-config.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>
<!--配置数据库访问环境,一般在集成环境中,都会用Spring代替 -->
<environments default="development">
<environment id="development">
<!-- JDBC事务控制 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 数据源 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
</configuration>
需求设计
1、根据ID查询用户
- 根据sys_user表结构创建User Pojo对象
package indi.latch.mybatis.pojo;
import java.util.Date;
/**
* Created by latch on 15-10-11.
*/
public class UserPojo {
private int id;
private String name;
private String pass;
private Date birthday;
private int sex;
private String address;
//getter setter
@Override
public String toString() {
return "UserPojo{" +
"id=" + id +
", name='" + name + '\'' +
", pass='" + pass + '\'' +
", birthday=" + birthday +
", sex=" + sex +
", address='" + address + '\'' +
'}';
}
}
- 创建映射文件UserMapper.xml,并将映射文件添加到MyBatis配置(mybatis-config.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="mybatisDemo1">
<select id="selectUserByID" parameterType="int" resultType="indi.latch.mybatis.pojo.UserPojo">
SELECT * FROM test.user WHERE id=#{id};
</select>
</mapper>
将UserMapper.xml添加到配置文件中
<!--mapper文件配置 -->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
- 实现根据ID查询用户信息
package indi.latch.mybatis.dao.impl;
import indi.latch.mybatis.pojo.UserPojo;
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.Test;
import java.io.IOException;
import java.io.InputStream;
public class TestUser {
@Test
public void testGetUserByID () throws IOException {
//MyBatis全局配置文件
String resource = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(resource);
//根据第一次的框架原理流程
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//通过sqlSession操作数据库
UserPojo user = sqlSession.selectOne("mybatisDemo1.selectUserByID", 1);
if (null != user) {
System.out.println(user.toString());
} else {
System.out.println("user is null");
}
//释放资源
sqlSession.close();
}
}
2、根据用户名模糊查询用户
- 在UserMapper.xml文件中,添加根据用户名模糊查询的映射语句
<!--根据名称模糊查询用户信息-->
<select id="selectUserByName" parameterType="String" resultType="indi.latch.mybatis.pojo.UserPojo">
SELECT * FROM test.user WHERE name LIKE '%${value}%'
</select>
- 实现根据用户名模糊查询
@Test
public void testGetUserByName() throws IOException {
//MyBatis全局配置文件
String resource = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(resource);
//根据第一次的框架原理流程
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//通过sqlSession操作数据库
List<UserPojo> userList = sqlSession.selectList("mybatisDemo1.selectUserByName", "张");
if (null != userList) {
System.out.println(userList.toString());
} else {
System.out.println("user is null");
}
//释放资源
sqlSession.close();
}
3、新增加用户
- 在UserMapper.xml文件中,添加新增加用户的映射语句
<!--新增加用户-->
<insert id="insertUser" parameterType="indi.latch.mybatis.pojo.UserPojo">
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO `test`.`user`
(
`name`,
`pass`,
`sex`,
`address`
)
VALUES
(
#{name},
#{pass},
#{sex},
#{address}
)
</insert>
- 测试新增用户
@Test
public void testInsertUser () throws IOException {
//MyBatis全局配置文件
String resource = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(resource);
//根据第一次的框架原理流程
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
UserPojo user = new UserPojo();
user.setName("王宝四");
user.setPass("Wang Baosi");
user.setSex(0);
user.setAddress("南京市");
//通过sqlSession操作数据库
sqlSession.insert("mybatisDemo1.insertUser", user);
sqlSession.commit();
System.out.println(String.format("新用户的ID为:%d", user.getId()));
//释放资源
sqlSession.close();
}
4、按ID删除用户
- 在UserMapper.xml文件中,添加根据ID删除用户的映射语句
<delete id="delUserByID" parameterType="int">
DELETE FROM test.user WHERE id = #{id};
</delete>
- 测试
@Test
public void testDelUserByID () throws IOException {
//MyBatis全局配置文件
String resource = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(resource);
//根据第一次的框架原理流程
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//通过sqlSession操作数据库
sqlSession.delete("mybatisDemo1.delUserByID", 9);
sqlSession.commit();
//释放资源
sqlSession.close();
}
5、更新指定用户信息
- 在UserMapper.xml文件中,添加更新指定用户信息的映射语句
<update id="updateUserByID" parameterType="indi.latch.mybatis.pojo.UserPojo">
UPDATE test.user SET name = #{name}, pass = #{pass}, sex=#{sex}, address=#{address} WHERE id = #{id};
</update>
- 测试
@Test
public void testUpdateUserByID () throws IOException {
//MyBatis全局配置文件
String resource = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(resource);
//根据第一次的框架原理流程
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//通过sqlSession操作数据库
UserPojo user = new UserPojo();
user.setId(10);
user.setName("王宝三");
user.setPass("Wang Baosan");
user.setSex(0);
user.setAddress("安徽省");
sqlSession.update("mybatisDemo1.updateUserByID", user);
sqlSession.commit();
//释放资源
sqlSession.close();
}
代码
1、git@code.csdn.net:xl890727/mybatisdemo.git
2、https://code.csdn.net/xl890727/mybatisdemo.git
中的master分支