SpringBoot（5）SpringBoot2.0整合mybatis实现MySQL数据的增删改查并映射到wep层

SpringBoot（5）SpringBoot2.0整合mybatis实现MySQL数据的增删改查

（1）引入依赖

Mybatis的依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis‐spring‐boot‐starter</artifactId>
    <version>1.3.1</version>
</dependency>

MySQL的依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

（2）引入Mybatis的配置文件，放在resources目录下，这个去官网找一找，抄一抄

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

    <settings>
        <!-- Globally enables or disables any caches configured in any mapper under this configuration -->
        <setting name="cacheEnabled" value="true"/>
        <!-- Sets the number of seconds the driver will wait for a response from the database -->
        <setting name="defaultStatementTimeout" value="3000"/>
        <!-- Enables automatic mapping from classic database column names A_COLUMN to camel case classic Java property names aColumn -->
        <!--驼峰命名，就是head_url可以转化为headUrl-->
        <!--<setting name="mapUnderscoreToCamelCase" value="true"/>-->
        <!-- Allows JDBC support for generated keys. A compatible driver is required.
        This setting forces generated keys to be used if set to true,
         as some drivers deny compatibility but still work -->
        <setting name="useGeneratedKeys" value="true"/>
    </settings>

    <!-- Continue going here -->

</configuration>

（3）在application中配置读取Mybatis的配置文件,并配置和MySQL相关的配置，在这之前你要先设计你的数据库的字段，然后把你的数据库建立起来

spring.freemarker.suffix=.html
#设定freemarker读取文件的路径，默认也会到这里来找，，也可以设置别的路径
#spring.freemarker.template-loader-path=classpath:/templates
spring.freemarker.cache=false

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/wenda?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=*****
spring.datasource.password=*******
mybatis.config-location=classpath:mybatis-config.xml

到这里基本的配置都配置完了，接下来就实现数据库的访问

（4）持久层首先的当然是写DAO接口，在写DAO接口之前要先写一个与数据库表中内容对应的实现类，把表中每行的参数的get和set方法先写出来

package com.springboot.springboot.model;

public class User {
    private Integer id;
    private String name;
    private String password;
    private String salt;
    private String head_url;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSalt() {
        return salt;
    }

    public void setSalt(String salt) {
        this.salt = salt;
    }

    public String getHead_url() {
        return head_url;
    }

    public void setHead_url(String head_url) {
        this.head_url = head_url;
    }

    public User(){

    }
    public  User(String name){
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

   public String userDescription(){
        return "This is "+ name;
   }
}

然后DAO的接口，

package com.springboot.springboot.dao;

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

@Mapper
public interface userDAO {

    //这个地方注意前后加空格，后面不注意的话可能就出错了
    String TABLE_NAME = " user ";
    String TABLE_FIELDS = " name, password, salt, head_url ";
    String SELECT_FIELDS = " id " + TABLE_FIELDS;

    //增
    @Insert({"insert into ", TABLE_NAME,"(",TABLE_FIELDS,
            ") Values(#{name}, #{password}, #{salt}, #{head_url})"})
    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 deleteUserById(int id);
}

这里用的是注解的方式实现的

当然还可以编写.XML文件，在里面写数据库语句，然后在DAO接口中去对应的调用

如我现在写了一个：

<?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="com.springboot.springboot.dao.questionDAO">
    <sql id="table">question</sql>
    <sql id="selectFields">id, title, content, user_id, created_date, comment_count
    </sql>
    <select id="selectLatestQuestions" resultType="com.springboot.springboot.model.Question">
        SELECT
        <include refid="selectFields"/>
        FROM
        <include refid="table"/>

        <if test="user_id != 0">
            WHERE user_id = #{user_id}
        </if>
        ORDER BY id DESC
        LIMIT #{offset},#{limit}
    </select>
</mapper>

然后对应的DAO为：

//使用XML的方式完成数据库的操作
    List<Question> selectLatestQuestions(@Param("user_id") int userId, @Param("offset") int offset,
                                         @Param("limit") int limit);

这种实现方式注意的是要对应好

（5）测试类，测试好不好用

package com.springboot.springboot;

import com.springboot.springboot.dao.questionDAO;
import com.springboot.springboot.dao.userDAO;
import com.springboot.springboot.model.Question;
import com.springboot.springboot.model.User;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.junit4.SpringRunner;

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

//@RunWith(SpringRunner.class)
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
@Sql("/init-schema.sql")
public class InitDatabaseTests {

	@Autowired
	userDAO uDAO;
	@Autowired
	questionDAO qDAO;

	@Test
	public void initDatabase() {
		Random random = new Random();

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

			user.setPassword("XXX");
			uDAO.updatePassword(user);

			Question question = new Question();
			question.setCommentCount(i);
			Date date = new Date();
			date.setTime(date.getTime() + 100*3600*i);
			question.setCreatedDate(date);
			question.setUserId(i+1);
			question.setTitle(String.format("Title%d",i));
			question.setContent(String.format("dgueuhdpwefpckaweni Content %d",i));
			qDAO.addQuestion(question);


		}

		Assert.assertEquals("XXX",uDAO.selectById(1).getPassword());
		uDAO.deleteUserById(1);
		Assert.assertNull(uDAO.selectById(1));

		System.out.println(qDAO.selectLatestQuestions(0,0,10));

	}

}

要是不想在所有的Mapper文件中都使用@Mapper注解

就在也就是你的启动或者测试文件中

使用MapperScan批量扫描所有的Mapper接口；

@MapperScan(value = "com.springbootdemo.springboot.mapper")

这样就完成了整个数据库的增删改查。

（6）那Web界面怎么去读取数据库中的数据呢？首先当然是编写service层

package com.springboot.springboot.service;

import com.springboot.springboot.dao.userDAO;
import com.springboot.springboot.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class userService {
    @Autowired
    userDAO uDAO;

    public User getUser(int id){
        return uDAO.selectById(id);
    }
}

然后写WEP层，就是Controller实现

package com.springboot.springboot.controller;

import com.springboot.springboot.model.Question;
import com.springboot.springboot.model.viewObject;
import com.springboot.springboot.service.questionService;
import com.springboot.springboot.service.userService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import java.util.ArrayList;
import java.util.List;

@Controller
public class homeController {
    private static final Logger logger = LoggerFactory.getLogger(homeController.class);

    @Autowired
    questionService qService;

    @Autowired
    userService uService;

    @RequestMapping(path = {"/user/{userId}","/index"},method = RequestMethod.GET)
    public String userIndex(Model model, @PathVariable("userId") int userId){

        model.addAttribute("vos",getQuestions(userId,0,10));
        return "index";
    }

    @RequestMapping(path = {"/","/index"},method = RequestMethod.GET)
    public String home(Model model){

        model.addAttribute("vos",getQuestions(0,0,10));
        return "index";
    }

    private List<viewObject> getQuestions(int userId,int offset, int limit){
        List<Question> questionList = qService.selectLatestQuestions(userId,offset,limit);
        List<viewObject> vos = new ArrayList<>();
        for (Question question:questionList){
            viewObject vo = new viewObject();
            vo.set("question",question);
            vo.set("user", uService.getUser(question.getUserId()));
            vos.add(vo);
        }
        return vos;
    }

}

中间为了方便写了一个freemaker与controller之间传递参数的函数，要不然都写在controller中显的乱七八糟

package com.springboot.springboot.model;

import java.util.HashMap;
import java.util.Map;

//用来传递freemarker与Controller之间的参数的
public class viewObject {
    private Map<String, Object> objs = new HashMap<String, Object>();

     public void set(String key, Object values){
         objs.put(key, values);
     }

     public Object get(String key){
         return objs.get(key);
     }
}

对应的前端的页面

<#include "/header.html" encoding="UTF-8" parse=true>
    <div class="zg-wrap zu-main clearfix " role="main">
        <div class="zu-main-content">
            <div class="zu-main-content-inner">
                <div class="zg-section" id="zh-home-list-title">
                    <i class="zg-icon zg-icon-feedlist"></i>最新动态
                    <input type="hidden" id="is-topstory">
                    <span class="zg-right zm-noti-cleaner-setting" style="list-style:none">
                        <a href="https://nowcoder.com/settings/filter" class="zg-link-gray-normal">
                            <i class="zg-icon zg-icon-settings"></i>设置</a></span>
                </div>
                <div class="zu-main-feed-con navigable" data-feedtype="topstory" id="zh-question-list" data-widget="navigable" data-navigable-options="{"items":"> .zh-general-list .feed-content","offsetTop":-82}">
                    <a href="javascript:;" class="zu-main-feed-fresh-button" id="zh-main-feed-fresh-button" style="display:none"></a>
                    <div id="js-home-feed-list" class="zh-general-list topstory clearfix" data-init="{"params": {}, "nodename": "TopStory2FeedList"}" data-delayed="true" data-za-module="TopStoryFeedList">

                        <#list vos as vo>
                        <div class="feed-item folding feed-item-hook feed-item-2" feed-item-a="" data-type="a" id="feed-2" data-za-module="FeedItem" data-za-index="">
                            <meta itemprop="ZReactor" data-id="389034" data-meta="{"source_type": "promotion_answer", "voteups": 4168, "comments": 69, "source": []}">
                            <div class="feed-item-inner">
                                <div class="avatar">
                                    <a title="${vo.user.name!}" data-tip="p$t$amuro1230" class="zm-item-link-avatar" target="_blank" href="https://nowcoder.com/people/amuro1230">
                                        <img src="${vo.user.head_url!}" class="zm-item-img-avatar"></a>
                                </div>
                                <div class="feed-main">
                                    <div class="feed-content" data-za-module="AnswerItem">
                                        <meta itemprop="answer-id" content="389034">
                                        <meta itemprop="answer-url-token" content="13174385">
                                        <h2 class="feed-title">
                                            <a class="question_link" target="_blank" href="/question/${vo.question.id!}">${vo.question.title!}</a></h2>
                                        <div class="feed-question-detail-item">
                                            <div class="question-description-plain zm-editable-content"></div>
                                        </div>
                                        <div class="expandable entry-body">
                                            <div class="zm-item-vote">
                                                <a class="zm-item-vote-count js-expand js-vote-count" href="javascript:;" data-bind-votecount="">4168</a></div>
                                            <div class="zm-item-answer-author-info">
                                                <a class="author-link" data-tip="p$b$amuro1230" target="_blank" href="/user/${vo.user.id!}">${vo.user.name!}</a>
                                                ，${vo.question.createdDate?string('yyyy-MM-dd HH:mm:ss')!}</div>
                                            <div class="zm-item-vote-info" data-votecount="4168" data-za-module="VoteInfo">
                                                <span class="voters text">
                                                    <a href="#" class="more text">
                                                        <span class="js-voteCount">4168</span> 人赞同</a></span>
                                            </div>
                                            <div class="zm-item-rich-text expandable js-collapse-body" data-resourceid="123114" data-action="/answer/content" data-author-name="李淼" data-entry-url="/question/19857995/answer/13174385">
                                                <div class="zh-summary summary clearfix">${vo.question.content!}</div>
                                            </div>
                                        </div>
                                        <div class="feed-meta">
                                            <div class="zm-item-meta answer-actions clearfix js-contentActions">
                                                <div class="zm-meta-panel">
                                                    <a data-follow="q:link" class="follow-link zg-follow meta-item" href="javascript:;" id="sfb-123114">
                                                        <i class="z-icon-follow"></i>关注问题</a>
                                                    <a href="#" name="addcomment" class="meta-item toggle-comment js-toggleCommentBox">
                                                        <i class="z-icon-comment"></i>${vo.question.commentCount!} 条评论</a>


                                                    <button class="meta-item item-collapse js-collapse">
                                                        <i class="z-icon-fold"></i>收起</button>
                                                </div>
                                            </div>

                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        </#list>
                    </div>
                    <a href="javascript:;" id="zh-load-more" data-method="next" class="zg-btn-white zg-r3px zu-button-more" style="">更多</a></div>
            </div>
        </div>
    </div>
<#include "/footer.html" encoding="UTF-8" parse=true>