使用eclipse搭建SpringBoot+Mysql+mybatis做一个简单的查询,前端分别使用jsp和html(+vue)做显示
其实本来的想把增删改查一起上的,又觉得会不会太复杂了,因为很多时候一旦东西多了,就会产生厌烦,就先来查询吧。
使用eclipse搭建一个简单的SpringBoot工程
这回就不重新搭新工程,直接使用之前搭的工程了
言归正传:
1、一个简单查询的项目结构大概如下图所示(一张图截不下,两张图src是重合连接的地方)
2、既然要用mysql和mybatis,那么首先考虑的肯定是依赖了。如下:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>hwq</groupId>
<artifactId>SpringBoot</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>SpringBoot Maven Webapp</name>
<url>http://maven.apache.org</url>
<profiles>
<profile>
<id>jdk-1.8</id>
<!-- 另外一种激活方式 -->
<activation>
<activeByDefault>true</activeByDefault>
<jdk>1.8</jdk>
</activation>
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<maven.compiler.compilerVersion>1.8</maven.compiler.compilerVersion>
</properties>
</profile>
</profiles>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.2.RELEASE</version>
</parent>
<dependencies>
<!-- Spring Boot web依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<!-- 与数据库操作相关的依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- Spring Boot 热部署 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<!-- Servlet和Tomcat -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jsp-api</artifactId>
</dependency>
<!-- MySql依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- Mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
<build>
<finalName>SpringBoot</finalName>
<plugins>
<!-- springframework插件配置 -->
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin </artifactId>
</plugin>
</plugins>
</build>
</project>
3、配置文件application.yml。如下:
spring.mvc.view.prefix: /WEB-INF/jsp/
spring.mvc.view.suffix: .jsp
#spring.mvc.view.prefix: /html/
#spring.mvc.view.suffix: .html
spring:
datasource:
url: jdbc:mysql://localhost:3306/childtrain
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
mybatis:
mapper-locations: classpath*:/mapper/*.xml
4、在包下创建controller、service、model和dao四个包,这是一个简单的分类,也可以自己扩展。
1)在controller包下创建SpringController.java和UserController.java。如果自己手写,记得别把@Controller注解忘了。
SpringController.java内容如下:
package pers.hwq.springboot.controller;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
@Controller
public class SpringController {
@RequestMapping(value = "/toFirst", method = RequestMethod.GET)
public String toFirst(){
System.out.println("toFirst");
return "first";
}
@RequestMapping(value = "/toTest", method = RequestMethod.GET)
public String toTest(){
System.out.println("toTest");
return "test";
}
}
UserController.java内容如下:
package pers.hwq.springboot.controller;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import pers.hwq.springboot.model.User;
import pers.hwq.springboot.service.UserService;
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@ResponseBody
@RequestMapping(value = "/findAllUser", method = RequestMethod.POST)
public ModelAndView findAllUser(HttpServletRequest request,HttpServletResponse response){
ModelAndView view = new ModelAndView("first");
List<User> list = new ArrayList<User>();
list = userService.findAllUser();
view.addObject("list", list);
System.out.println(view);
return view;
}
@ResponseBody
@RequestMapping(value = "/findAllUserJson", method = RequestMethod.POST)
public List<User> findAllUserJson(HttpServletRequest request,HttpServletResponse response){
List<User> list = new ArrayList<User>();
list = userService.findAllUser();
System.out.println(list);
return list;
}
}
其中上面一个方法返回的是一个视图,用于jsp显示查询出的数据;下面一个方法返回的是一个list,spring会自动将其转换成一个json字符串,用于在html显示。当然,这两个并不是一一对应关系,这里只是为了做两种不同的返回方式。
2)在service包下我们创建UserService.java和UserServiceImpl.java。其中UserService.java是接口,UserServiceImpl.java则是他的实现类。如果自己手写,记得别把UserServiceImpl.java实现类的@Service注解忘记了。
UserService.java内容如下:
package pers.hwq.springboot.service;
import java.util.List;
import pers.hwq.springboot.model.User;
public interface UserService {
//查询所有用户
public List<User> findAllUser();
}
UserServiceImpl.java
package pers.hwq.springboot.service;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Service;
import pers.hwq.springboot.model.User;
import pers.hwq.springboot.dao.UserDao;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public List<User> findAllUser() {
/*ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
userDao = (UserDao)context.getBean("userDao");*/
List<User> list = new ArrayList<User>();
list = userDao.findAllUser();
System.out.println("查询结果:" + list);
return list;
}
}
3)在model包下创建用户的实体类User.java。内容如下:
package pers.hwq.springboot.model;
import org.springframework.stereotype.Component;
@Component
public class User {
private int id;
private String account;
private int student_id;
private String username;
private String password;
private String phone;
private String address;
private String createdate;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public int getStudent_id() {
return student_id;
}
public void setStudent_id(int student_id) {
this.student_id = student_id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCreatedate() {
return createdate;
}
public void setCreatedate(String createdate) {
this.createdate = createdate;
}
}
4)在dao下创建接口UserDao.java。内容如下:
package pers.hwq.springboot.dao;
import java.util.List;
import pers.hwq.springboot.model.User;
public interface UserDao {
//查询所有用户
public List<User> findAllUser();
}
5)最后需要创建dao的映射文件,我们把包放在resource下面,创建mapper,并在其目录下创建UserMapper.xml。
UserMapper.xml内容如下:
<?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="pers.hwq.springboot.dao.UserDao">
<select id="findAllUser" resultType="pers.hwq.springboot.model.User">
select * from user
</select>
</mapper>
这里面需要记住的是,UserDao里面的方法名称需要和mapper里面标签中的id相一致,如UserDao里面的方法名称和select标签的id需要都为findAllUser。还有,mapper的namespace属性值需要为所对应dao的路径。返回值类型resultType也需要和UserDao的返回类型一致。如返回List或者User,那就需要将User的路径写在resultType。
还有一步很重要,我们需要在启动类SpringbootApplication.java里面使用@MapperScan注解配置我们的mapper映射路径,否则spring无法知道哪里有mapper去完成映射。
SpringbootApplication.java内容如下:
package pers.hwq.springboot;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@EnableAutoConfiguration
@MapperScan(value = {"pers.hwq.springboot.dao"})
public class SpringbootApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootApplication.class, args);
}
}
5、后端准备就绪之后,就剩页面了。
1)先展示jsp页面的显示方法。first.jsp内容如下:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>first</title>
</head>
<script type="text/javascript" src="/js/jquery.min.js"></script>
<!-- <script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js"> -->
<body>
<div id="loade">
<button id="getAllUser">获取数据</button>
<table class="table table-bordered table-hover">
<tr>
<th><input type="checkbox" id="checkall"/></th>
<th>账号</th>
<th>昵称</th>
<th>账号密码</th>
<th>联系电话</th>
<th>地址</th>
<th>创建时间</th>
</tr>
<c:forEach items="${list}" var="users">
<tr>
<td><input type="checkbox" name="checkone" value="${users.id }"/></td>
<td>${users.account }</td>
<td>${users.username }</td>
<td>${users.password }</td>
<td>${users.phone }</td>
<td>${users.address }</td>
<td>${users.createdate }</td>
</tr>
</c:forEach>
</table>
</div>
</body>
<script type="text/javascript">
$("#getAllUser").click(function(){
findAllUser(1);
});
function findAllUser(pageInteger) {
$.ajax({//这是$.ajax()方法
type: "post",
url: "/user/findAllUser",
data: {},//ajax提交表单
dataType: "html",//接受响应的数据类型,我的响应是一个页面,所以这里用"html"
success:function(data, status, xhr){
debugger;
$('#loade').html(data);
}
})
}
</script>
</html>
这里之所以使用按钮来获取数据,是因为采用的直接加载html,如果不使用按钮而直接执行方法,将会陷入死循环。当然也不是没有办法,这里只是简单的演示,就不做别的动作了,有兴趣的可以自己想。(如果没有下载jquery插件,那就将jsp中的<script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js">
标签取消注释放出来就行)
右键,run as -> maven install,成功后从启动类SpringbootApplication.java启动项目。打开浏览器,输入地址http://localhost:8080/toFirst,效果如下:
点击获取数据:
数据能加载出来,说明成功的使用spring对mysql进行了数据查询。
2)接下来是使用原生html,加上vue来显示数据。这里我们需要修改一下配置文件application.yml。如下,将上面两行注释,下面两行取消注释,其他不变。
#spring.mvc.view.prefix: /WEB-INF/jsp/
#spring.mvc.view.suffix: .jsp
spring.mvc.view.prefix: /html/
spring.mvc.view.suffix: .html
spring:
datasource:
url: jdbc:mysql://localhost:3306/childtrain
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
mybatis:
mapper-locations: classpath*:/mapper/*.xml
test.html内容如下:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>增删改查测试页面</title>
</head>
<!-- <script src="https://cdn.staticfile.org/vue/2.2.2/vue.min.js"></script> -->
<script type="text/javascript" src="/js/vue.min.js"></script>
<script src="https://cdn.staticfile.org/axios/0.18.0/axios.min.js"></script>
<body>
<div id="app">
<table>
<tr>
<th><input type="checkbox" id="checkall"/></th>
<th>账号</th>
<th>昵称</th>
<th>账号密码</th>
<th>联系电话</th>
<th>地址</th>
<th>创建时间</th>
</tr>
<tr id="" v-for="user in users">
<td><input type="checkbox" name="checkone" value="${user.id }"/></td>
<td>{{ user.account }}</td>
<td>{{ user.username }}</td>
<td>{{ user.password }}</td>
<td>{{ user.phone }}</td>
<td>{{ user.address }}</td>
<td>{{ user.createdate }}</td>
</tr>
</table>
</div>
</body>
<script type="text/javascript">
var app = new Vue({
el: '#app',
data : {
users : []
},
created() {
axios
.post('/user/findAllUserJson',{})
.then(function(resp){
console.log(resp.data);
app.users = resp.data;
})
.catch(function (error) { // 请求失败处理
console.log(error);
})
}
});
</script>
</html>
如果没有下载vue插件,那就将<script src="https://cdn.staticfile.org/vue/2.2.2/vue.min.js"></script>标签取消注释放出来就行。
重新启动项目(有热部署的话可以不用),打开浏览器,输入地址:http://localhost:8080/toTest,效果如下:
OK,到这就算完事了,查询算是比较简单的,细节处理好就行。如果有什么不足之处,欢迎各位指出。