# Mybatis开发流程

## 基本开发流程

1. 创建表数据
2. 创建pojo, pojo 作为mybatis进行sql映射使用，po类通常与数据库表对应。
3. 创建mapper （pojo类） 与POJO与数据库操作进行关联映射
4. 测试

### 1.创建数据表

```sql
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

```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

```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、接口注释配置方式
>
> ```java
> @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配置方式
>
> ```markup
> <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

```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

```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

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

}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://spring-boot.shujuwajue.com/mybatis/kai-fa-liu-cheng.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.