SpringBoot（5）SpringBoot2.0整合mybatis实现MySQL数据的增删改查并映射到wep层
程序员文章站
2022-05-06 20:56:58
...
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>