Mybatis开发流程

基本开发流程

1. 创建表数据

2. 创建pojo, pojo 作为mybatis进行sql映射使用，po类通常与数据库表对应。

3. 创建mapper （pojo类） 与POJO与数据库操作进行关联映射

4. 测试

1.创建数据表

DROP TABLE IF EXISTS `news`;
CREATE TABLE `news` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(128) NOT NULL DEFAULT '',
`link` varchar(256) NOT NULL DEFAULT '',
`image` varchar(256) NOT NULL DEFAULT '',
`like_count` int(11) NOT NULL,
`comment_count` int(11) NOT NULL,
`created_date` datetime NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT
CHARSET=utf8;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(64) NOT NULL DEFAULT '',
    `password` varchar(128) NOT NULL DEFAULT '',
    `salt` varchar(32) NOT NULL DEFAULT '',
    `head_url` varchar(256) NOT NULL DEFAULT '',
    PRIMARY KEY(`id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.创建pojo

创建src/main/java/model目录

src/main/java/model/User.java

package com.nowcoder.model;

public class User {
    private int id;
    private String name;
    private String password;
    private String salt;
    private String headUrl;

    public User() {

    }
    public User(String name) {
        this.name = name;
        this.password = "";
        this.salt = "";
        this.headUrl = "";
    }
    .... get set方法省略
}

src/main/java/model/News.java

package com.nowcoder.model;

import java.util.Date;

public class News {

  private int id;
  private String title;
  private String link;
  private String image;
  private int likeCount;
  private int commentCount;
  private Date createdDate;
  private int userId;

  .... get set方法省略

  }

3.创建mapper

提示：创建mapper可以使用两种方式

1、接口注释配置方式

@Mapper
public interface UserDAO {
    String TABLE_NAME = "user";
    String INSERT_FIELDS = " name, password, salt, head_url ";
    String SELECT_FIELDS = " id, name, password, salt, head_url ";
    @Insert({"insert into ", TABLE_NAME, "(", INSERT_FIELDS,
    ") Values (#{name}, #{password}, #{salt}, #{headUrl})"})
    int addUser(User user);
    
    @Select({"select ", SELECT_FIELDS, " from ", TABLE_NAME, " where id=#{id}"})
    User selectById(int id);
    
    @Update({"update ", TABLE_NAME, " set password = #{password} where id=#{id}"})
    void updatePassword(User user);
    
    @Delete({"delete from ", TABLE_NAME, " where id = #{id}"})
    void deleteById(int id);
}

2、XML配置方式

<mapper namespace="com.nowcoder.dao.NewsDAO">
<sql id="table">news</sql>
<sql id="selectFields">id,title, link, image, like_count,comment_count,created_date,user_id</sql>
<select id="selectByUserIdAndOffset" resultType="com.nowcoder.model.News">
    SELECT
<include refid="selectFields"/>
    FROM
<include refid="table"/>
<if test="userId != 0">
    WHERE user_id = #{userId}
</if>
    ORDER BY id DESC
    LIMIT #{offset},#{limit}
</select>
</mapper>

创建src/main/java/dao目录

UserDAO.java

package com.nowcoder.dao;

import com.nowcoder.model.User;
import org.apache.ibatis.annotations.*;

@Mapper
public interface UserDAO {
    String TABLE_NAME = "user";
    String INSERT_FIELDS = " name, password, salt, head_url ";

    String SELECT_FIELDS = " id, name, password, salt, head_url ";
    @Insert({"insert into ", TABLE_NAME, "(", INSERT_FIELDS,
        ") values (#{name},#{password}, #{salt}, #{headUrl})"})
    int addUser(User user);

    @Select({"select ", SELECT_FIELDS, " from ", TABLE_NAME, " where id=#{id}"})
    User selectById(int id);

    @Update({"update ", TABLE_NAME, " set password=#{password} where id=#{id}"})
    void updatePassword(User user);

    @Delete({"delete from ", TABLE_NAME, " where id=#{id}"})
    void deleteById(int id);
}

NewsDAO.java

package com.nowcoder.dao;

import com.nowcoder.model.News;
import com.nowcoder.model.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface NewsDAO {
    String TABLE_NAME = "news";
    String INSERT_FIELDS = " title, link, image, like_count, comment_count, created_date, user_id ";
    String SELECT_FIELDS = " id, " + INSERT_FIELDS;

    @Insert({"insert into ", TABLE_NAME, "(", INSERT_FIELDS,
            ") values (#{title},#{link},#{image},#{likeCount},#{commentCount},#{createdDate},#{userId})"})
    int addNews(News news);

    List<News> selectByUserIdAndOffset(@Param("userId") int userId, @Param("offset") int offset,
                                       @Param("limit") int limit);
}

param的值就是sql语句用到的值，一般和参数值保持一致。

4.测试

src/test/java/nowcoder/InitDatabaseTests.java

package com.nowcoder;

import com.nowcoder.dao.NewsDAO;
import com.nowcoder.dao.UserDAO;
import com.nowcoder.model.News;
import com.nowcoder.model.User;
import org.junit.Test;
import org.junit.Assert;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.Date;
import java.util.Random;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = ToutiaoApplication.class)
//@Sql("/init-schema.sql")
public class InitDatabaseTests {

    @Autowired
    UserDAO userDAO;

    @Autowired
    NewsDAO newsDAO;

    @Test
    public void contextLoads() {

        Random random = new Random();

        for (int i = 0; i < 11; ++i) {
            User user = new User();
            user.setHeadUrl(String.format("http://images.nowcoder.com/head/%dt.png", random.nextInt(100)));
            user.setName(String.format("USER%d", i));
            user.setPassword("");
            user.setSalt("");
            userDAO.addUser(user);

            News news = new News();
            news.setCommentCount(i);
            Date date = new Date();
            date.setTime(date.getTime() + 1000*3600*5*i);
            news.setCreatedDate(date);
            news.setImage(String.format("http://images.nowcoder.com/head/%dm.png", random.nextInt(1000)));
            news.setLikeCount(i+1);
            news.setUserId(i+1);
            news.setTitle(String.format("TITLE{%d}", i));
            news.setLink(String.format("http://www.nowcoder.com/%d.html", i));
            newsDAO.addNews(news);

            user.setPassword("newpassword");
            userDAO.updatePassword(user);
        }

        Assert.assertEquals("newpassword", userDAO.selectById(1).getPassword());
        userDAO.deleteById(1);
        Assert.assertNull(userDAO.selectById(1));
    }

}