Mybatis使用之环境搭建

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 目录

        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);


       项目整体结构:


  • 7
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值