Mybatis使用之环境搭建
最后更新于：2022-04-01 16:29:44
# Mybatis使用之环境搭建
### 一：简介
集成环境：IntellijIDEA 14.0+Maven+Mybatis3.2.8+mysql。
主要为后续Mybatis使用搭建运行环境。
### 二：前期准备
1、 主要是数据库对应测试表的创建、这些表是后面学习过程中使用的表。数据模型简要ER图如下：
2、 建表语句以及初始化数据、见补充部分。
### 三：具体步骤
1、步骤概览：
2、具体过程：
i、 Maven引入依赖
~~~
<properties>
<junit.version>4.1</junit.version>
<testng.version>6.8.8</testng.version>
<sources.plugin.verion>2.1.1</sources.plugin.verion>
<org.springframework>4.1.1.RELEASE</org.springframework>
<servlet-api>3.1.0</servlet-api>
<jsp-api>2.0</jsp-api>
<javax.inject>1</javax.inject>
<javax.el>2.2.4</javax.el>
<org.hibernate>5.0.2.Final</org.hibernate>
<org.aspectj>1.7.4</org.aspectj>
<c3p0.version>0.9.1.2</c3p0.version>
<mysql.version>5.1.34</mysql.version>
<slf4j.version>1.7.10</slf4j.version>
<spring.ldap.version>2.0.2.RELEASE</spring.ldap.version>
<commons.pool>1.6</commons.pool>
<mybatis.version>3.2.8</mybatis.version>
</properties>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
~~~
ii、 配置数据库连接资源文件
~~~
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/scattered-items
jdbc.username=root
jdbc.password=root
~~~
编写数据库映射接口及方法、以AuthorMapper为例：
~~~
public interface AuthorMapper {
int addAuthor(Author author);
int deleteAuthor(int id);
int updateAuthor(Author author);
List<Author> getAllAuthors();
int getAllAuthorsCount();
}
~~~
编写与数据库表对应实体、以Author为例：
~~~
@SuppressWarnings("unused")
public class Author {
private int id;
private String username;
private String password;
private String email;
private String bio;
private String favouriteSection;
//省略getter setter...
@Override
public String toString() {
return "Author{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", email='" + email + '\'' +
", bio='" + bio + '\'' +
", favouriteSection='" + favouriteSection + '\'' +
'}';
}
}
~~~
iii、 配置mybatis整体配置文件
~~~
<?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 -->
<properties resource="properties/jdbc.properties"/>
<!--settings
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="multipleResultSetsEnabled" value="true"/>
<setting name="useColumnLabel" value="true"/>
<setting name="useGeneratedKeys" value="false"/>
<setting name="autoMappingBehavior" value="PARTIAL"/>
<setting name="defaultExecutorType" value="SIMPLE"/>
<setting name="defaultStatementTimeout" value="25"/>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="false"/>
<setting name="localCacheScope" value="SESSION"/>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>-->
<!--A type alias is simply a shorter name for a Java type.-->
<typeAliases>
<!--<typeAlias type="org.alien.mybatis.samples.model.Employee" alias="Employee"/>-->
<!--<typeAlias type="org.alien.mybatis.samples.model.Department" alias="Department"/>-->
<!--That is domain.blog.Author will be registered as author. If the @Alias annotation is found its value will be
used as an alias.@Alias("author")-->
<package name="org.alien.mybatis.samples.model"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--<mapper resource="config/mappers/Department.xml"/>-->
<!--<mapper resource="config/mappers/Employee.xml"/>-->
<!--<mapper resource="org/alien/mybatis/samples/config/Department.xml"/>-->
<!--<mapper class="org.alien.mybatis.samples.dao.DepartmentMapper"/>-->
<!-- Register all interfaces in a package as mappers -->
<package name="org.alien.mybatis.samples.mapper"/>
</mappers>
</configuration>
~~~
iv、 配置mybatis映射文件
这里注意映射文件所放置的位置：resources/org/alien/mybatis/samples/mapper/AuthorMapper.xml。目的是为了在项目被编译之后AuthorMapper.xml文件是与AuthorMapper在同一文件夹内。至于为什么必须这样、后面会有。
~~~
<?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="org.alien.mybatis.samples.mapper.AuthorMapper">
<resultMap id="author" type="author">
<id property="id" column="author_id"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="email" column="email"/>
<result property="bio" column="bio"/>
<result property="favouriteSection" column="favouriteSection"/>
</resultMap>
<insert id="addAuthor" parameterType="author">
INSERT INTO author(id, username) VALUES (#{id}, #{username})
<selectKey keyProperty="id" resultType="int">
SELECT max(id) FROM author
</selectKey>
</insert>
<delete id="deleteAuthor" parameterType="int">
DELETE FROM author
WHERE id = #{id}
</delete>
<update id="updateAuthor" parameterType="author">
UPDATE author
SET username = #{username}
</update>
<select id="getAllAuthors" resultType="author">
SELECT
t.id,
t.username,
t.password,
t.email,
t.bio,
t.favourite_section favouriteSection
FROM author t
</select>
<select id="getAllAuthorsCount" resultType="int">
SELECT count(1)
FROM author
</select>
</mapper>
~~~
配置日志文件（主要用于打印sql语句）
~~~
### set log levels ###
log4j.rootLogger = , console
### output to the console ###
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [%l]-[%p] %m%n
log4j.logger.org.alien.mybatis.samples.mapper=debug
~~~
vi、 AuthorServiceImpl：
~~~
/**
* Created by andychen on 2015/5/18.<br>
* Version 1.0-SNAPSHOT<br>
*/
public class AuthorServiceImpl implements AuthorService {
/**
* Add author info.
*
* @param author author instance
* @return The key of current record in database.
*/
@Override
public int addAuthor(Author author) {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
AuthorMapper authorMapper = sqlSession.getMapper(AuthorMapper.class);
int authorId = authorMapper.addAuthor(author);
sqlSession.commit();
return authorId;
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
/**
* Delete author info by author's id.
*
* @param authorId author id
* @return int The number of rows affected by the delete.
*/
@Override
public int deleteAuthor(int authorId) {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
AuthorMapper authorMapper = sqlSession.getMapper(AuthorMapper.class);
int result = authorMapper.deleteAuthor(authorId);
sqlSession.commit();
return result;
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
/**
* update author info
*
* @param author Author instance
* @return int The number of rows affected by the update.
*/
@Override
public int updateAuthor(Author author) {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
AuthorMapper authorMapper = sqlSession.getMapper(AuthorMapper.class);
int result = authorMapper.updateAuthor(author);
sqlSession.commit();
return result;
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
/**
* Query all authors
*
* @return all authors
*/
@Override
public List<Author> getAllAuthors() {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
AuthorMapper authorMapper = sqlSession.getMapper(AuthorMapper.class);
return authorMapper.getAllAuthors();
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
/**
* Query all authors count
*
* @return all authors count
*/
@Override
public int getAllAuthorsCount() {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
AuthorMapper authorMapper = sqlSession.getMapper(AuthorMapper.class);
return authorMapper.getAllAuthorsCount();
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
}
~~~
vii、 测试AuthorService接口：
~~~
public class AuthorServiceImplTest {
private AuthorService authorService;
@Before
public void setUp() throws Exception {
authorService = new AuthorServiceImpl();
}
@Test
public void testGetAllAuthors() throws Exception {
Assert.assertEquals(true, authorService.getAllAuthors().size() > 0);
}
@Test
public void getAllAuthorsCount() throws Exception {
Assert.assertEquals(true, authorService.getAllAuthorsCount() > 0);
}
@Test
public void testAddAuthor() throws Exception {
Assert.assertEquals(true, authorService.addAuthor(new Author(3, "year")) > 0);
}
@Test
public void testDeleteAuthor() throws Exception {
Assert.assertEquals(true, authorService.deleteAuthor(3) > 0);
}
@Test
public void testUpdateAuthor() throws Exception {
Assert.assertEquals(true, authorService.updateAuthor(new Author(2, "star_year")) > 0);
}
}
~~~
### 四：补充
更多内容：[Mybatis 目录](http://blog.csdn.net/crave_shy/article/details/45825599)
github地址：https://github.com/andyChenHuaYing/scattered-items/tree/master/items-mybatis
源码下载地址：http://download.csdn.net/detail/chenghuaying/8713311
MybatisUtil类：
~~~
/**
* Loading mybatis config and mappers file.
* Created by andychen on 2015/5/7.<br>
* Version 1.0-SNAPSHOT<br>
*/
public class MybatisUtil {
private static SqlSessionFactory sqlSessionFactory;
/**
* Singleton model to get SqlSessionFactory instance.
*
* @return SqlSessionFactory instance
*/
public static SqlSessionFactory getSqlSessionFactory() {
String mybatisConfigPath = "config/mybatis/mybatis.xml";
try {
InputStream inputStream = Resources.getResourceAsStream(mybatisConfigPath);
if (sqlSessionFactory == null) {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
return sqlSessionFactory;
}
/**
* Open a SqlSession via SqlSessionFactory.
* By the way, you should close the SqlSession in your code.
*
* @return SqlSession sqlSession instance.
*/
public static SqlSession getSqlSession() {
return MybatisUtil.getSqlSessionFactory().openSession();
}
}
~~~
建表SQL语句：
~~~
/*
Navicat MySQL Data Transfer
Source Server : scattered-items
Source Server Version : 50096
Source Host : localhost:3306
Source Database : scattered-items
Target Server Type : MYSQL
Target Server Version : 50096
File Encoding : 65001
Date: 2015-05-16 12:30:10
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `author`
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
`ID` int(11) NOT NULL auto_increment,
`USERNAME` varchar(200) default NULL,
`PASSWORD` varchar(200) default NULL,
`EMAIL` varchar(200) default NULL,
`BIO` varchar(200) default NULL,
`FAVOURITE_SECTION` varchar(200) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `AUTHOR_INDEX` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of author
-- ----------------------------
INSERT INTO author VALUES ('1', 'alien', 'alien', '461857202@qq.com', null, 'java io');
-- ----------------------------
-- Table structure for `blog`
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
`ID` int(11) NOT NULL auto_increment,
`TITLE` varchar(200) default NULL,
`AUTHOR_ID` int(11) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `BLOG_INDEX` (`ID`),
KEY `BLOG_AUTHOR_FG` (`AUTHOR_ID`),
CONSTRAINT `BLOG_AUTHOR_FG` FOREIGN KEY (`AUTHOR_ID`) REFERENCES `author` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of blog
-- ----------------------------
INSERT INTO blog VALUES ('1', 'Mybatis tutorial', '1');
-- ----------------------------
-- Table structure for `post`
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
`ID` int(11) NOT NULL auto_increment,
`BLOG_ID` int(11) default NULL,
`AUTHOR_ID` int(11) default NULL,
`CREATED_ON` date default NULL,
`SECTION` varchar(200) default NULL,
`SUBJECT` varchar(200) default NULL,
`DRAFT` varchar(200) default NULL,
`BODY` varchar(200) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `POST_INDEX` (`ID`),
KEY `POST_BLOG_FG` (`BLOG_ID`),
CONSTRAINT `POST_BLOG_FG` FOREIGN KEY (`BLOG_ID`) REFERENCES `blog` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of post
-- ----------------------------
INSERT INTO post VALUES ('1', '1', '1', '2015-05-16', 'Mybatis introduction', 'Mybatis', 'Mybatis series draft', 'How to lean mybatis ?');
-- ----------------------------
-- Table structure for `post_comment`
-- ----------------------------
DROP TABLE IF EXISTS `post_comment`;
CREATE TABLE `post_comment` (
`ID` int(11) NOT NULL auto_increment,
`POST_ID` int(11) default NULL,
`NAME` varchar(200) default NULL,
`COMMENT_TEXT` varchar(200) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `POST_COMMENT_INDEX` (`ID`),
KEY `POST_COMMENT_POST_FG` (`POST_ID`),
CONSTRAINT `POST_COMMENT_POST_FG` FOREIGN KEY (`POST_ID`) REFERENCES `post` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of post_comment
-- ----------------------------
INSERT INTO post_comment VALUES ('1', '1', 'comment', 'Keep updating');
-- ----------------------------
-- Table structure for `post_tag`
-- ----------------------------
DROP TABLE IF EXISTS `post_tag`;
CREATE TABLE `post_tag` (
`ID` int(11) NOT NULL auto_increment,
`POST_ID` int(11) NOT NULL,
`TAG_ID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `POST_TAG_INDEX` (`ID`),
KEY `POST_TAG_INDEX2` (`POST_ID`),
KEY `POST_TAG_INDEX3` (`TAG_ID`),
CONSTRAINT `POST_TAG_TAG` FOREIGN KEY (`TAG_ID`) REFERENCES `tag` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `POST_TAG_POST` FOREIGN KEY (`POST_ID`) REFERENCES `post` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of post_tag
-- ----------------------------
INSERT INTO post_tag VALUES ('1', '1', '1');
INSERT INTO post_tag VALUES ('2', '1', '2');
INSERT INTO post_tag VALUES ('3', '1', '5');
-- ----------------------------
-- Table structure for `tag`
-- ----------------------------
DROP TABLE IF EXISTS `tag`;
CREATE TABLE `tag` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(200) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tag
-- ----------------------------
INSERT INTO tag VALUES ('1', 'Mybatis');
INSERT INTO tag VALUES ('2', 'Java');
INSERT INTO tag VALUES ('3', 'JavaScript');
INSERT INTO tag VALUES ('4', 'Web');
INSERT INTO tag VALUES ('5', 'ORM framework');
INSERT INTO tag VALUES ('6', null);
~~~
项目整体结构：
