开发环境
IntelliJ IDEA 2020.1
MySQL8.0.24
Navicat12
SpringBoot 2.4.5
程序运行效果
数据接口运行效果
数据库设计
新建数据库和数据表，新建数据库，名字是datademo。
创建数据表并添加记录。
CREATE TABLE `t_msg` (
`id` int(11) NOT NULL,
`Message` varchar (255) default null comment 'message',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
MySQL数据库特点
MySQL的数据库名、表名、列名、别名大小写规则：
1、数据库名与表名区分大小写；
2、表的别名区分大小写；
3、列名与列的别名不区分大小写；
4、字段内容默认情况下不区分大小写。
MySQL查看数据库编码命令
show variables like '%char%';
show create database datademo;
项目实现
新建Springboot工程，选择Maven依赖（Springboot工程本来就是属于Maven
类型的工程）: Lombok, web, MySQL and Mybatis
选择Lombok
选择web
选择MySQL and Mybatis
项目结构
开发顺序
创建包和Java类、接口文件，创建MyBatis配置文件，项目配置文件，修改Maven依赖文件。
（1）新建application.yml，删除原来的properties文件
（2）检查修改pom文件
（3）实体层entity
（4）数据访问层mapper
（5）数据访问服务层service
（6）数据访问服务实现层impl
（7）Html静态页面，在resource-static文件夹下
程序源码
application.yml
server:
port: 80
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/datademo?characterEncoding=utf-8&useSSL=false
username: root
password: root1234
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
mapper-locations: classpath*:mapper/*Mapper.xml
type-aliases-package: com.example.demo0421.entity
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo0421</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo0421</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
创建entity包和TMsg.java
package com.example.demo0421.entity;
import lombok.Data;
import java.io.Serializable;
@Data
public class TMsg implements Serializable {
private Integer id;
private String message;
}
创建mapper包和TMsgMapper.java接口
package com.example.demo0421.mapper;
import com.example.demo0421.entity.TMsg;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
@Repository
public interface TMsgMapper {
public TMsg findById(Integer id); // id字段
public TMsg findByMessage(String Message);//Message字段
public List<TMsg> findTMsgAll();
}
在resource文件夹中创建mapper文件夹，在mapper文件夹中创建TMsgMapper.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="com.example.demo0421.mapper.TMsgMapper">
<select id="findById" resultType="com.example.demo0421.entity.TMsg">
SELECT id,message from t_msg WHERE id = #{id}
</select>
<select id="findByMessage" resultType="com.example.demo0421.entity.TMsg">
SELECT id,message from t_msg WHERE Message = #{Message}
</select>
<select id="findTMsgAll" resultType="com.example.demo0421.entity.TMsg">
select * from t_msg
</select>
</mapper>
创建service包和TMsgService.java接口
package com.example.demo0421.service;
import com.example.demo0421.entity.TMsg;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
public interface TMsgService {
public TMsg findById(Integer id);
public TMsg findByMessage(String Message); // 获取Message
public List<TMsg> findTMsgAll();
}
在service包下创建impl包，在impl包下创建TMsgServiceImpl类实现TMsgService接口
package com.example.demo0421.service.impl;
import com.example.demo0421.entity.TMsg;
import com.example.demo0421.mapper.TMsgMapper;
import com.example.demo0421.service.TMsgService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class TMsgServiceImpl implements TMsgService {
@Autowired
private TMsgMapper tMsgMapper;
@Override
public TMsg findById(Integer id) {
return tMsgMapper.findById(id);
}
@Override // 获取Message
public TMsg findByMessage(String Message) {
return tMsgMapper.findByMessage(Message);
}
@Override // 获取所有
public List<TMsg> findTMsgAll() {
return tMsgMapper.findTMsgAll();
}
}
创建controller包和TMsgController类
package com.example.demo0421.controller;
import com.example.demo0421.entity.TMsg;
import com.example.demo0421.service.TMsgService;
import com.google.gson.Gson;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/msg")
public class TMsgController {
@Autowired
private TMsgService tMsgService;
@GetMapping("/getMsg")
public String getMsg(@Param("id") Integer id){
TMsg tMsg = tMsgService.findById(id);
return tMsg.getMessage();
}
@GetMapping("/getId") // 通过Message获取id
public Integer getId(@Param("Message") String Message){
TMsg tMsg = tMsgService.findByMessage(Message);
return tMsg.getId();
}
@GetMapping("/getOne") // 通过Message获取id
public TMsg getAll(@Param("id") Integer id){
TMsg tMsg = tMsgService.findById(id);
return tMsg;
}
@GetMapping("/getJson") // 通过Message获取id
public String getJson(@Param("id") Integer id){
TMsg tMsg = tMsgService.findById(id);
Gson gson = new Gson();
return gson.toJson(tMsg);
}
@GetMapping("/getAll") // 通过Message获取id
public List<TMsg> getAll(){
List<TMsg> list = tMsgService.findTMsgAll();
return list;
}
@GetMapping("/getAllJson") // 通过Message获取id
public String getAllJson(){
List<TMsg> list = tMsgService.findTMsgAll();
Gson gson = new Gson();
return gson.toJson(list);
}
}
在resources文件夹中的static文件夹中创建index.html网页
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>珂码在前进</title>
<link rel="stylesheet" type="text/css" href="https://cdn.bootcss.com/bootstrap/4.0.0-beta.2/css/bootstrap.min.css">
<script type="text/javascript" src="https://cdn.bootcss.com/jquery/1.4.0/jquery.js"></script>
<script>
$(document).ready(function(){
$("#btn1").click(function(){
test1();
});
$("#btn2").click(function(){
$.test2();
});
});
function test1(){
//alert("Text1: " + $("#test").text());
$.ajax({
url:'/msg/getAllJson',
type:'get',
dataType:'json',
success:function(data){
$("#tabletest").find('tr').remove();
tr='<td>id</td><td>Message</td>'
$("#tabletest").append('<tr>'+tr+'</tr>')
//方法中传入的参数data为后台获取的数据
for(i in data) //data指的是数组，i为数组的索引
{
var tr;
tr='<td>'+data[i].id+'</td>'+'<td>'+data[i].message +'</td>'
$("#tabletest").append('<tr>'+tr+'</tr>')
}
}
});
}
$.test2 = function() {
//alert("Text2: " + $("#id").val());
$.ajax({
url:'/msg/getOne',
type:'get',
dataType:'json', //返回数据类型
//data:{id:parseInt($("#id").val())},
//data:{"id":1},
contentType: 'application/json', // 请求传递的参数格式
data:{"id":$("#id").val()}, // 请求传递的参数
success:function(data){
//alert("success");
$("#tabletest").find('tr').remove();
tr='<td>id</td><td>Message</td>'
$("#tabletest").append('<tr>'+tr+'</tr>')
//方法中传入的参数data为后台获取的数据
var tr;
tr='<td>'+data.id+'</td>'+'<td>'+data.message +'</td>'
$("#tabletest").append('<tr>'+tr+'</tr>')
},
error:function(XMLHttpRequest, textStatus, errorThrown){
alert("没有查询到数据！");
$("#tabletest").find('tr').remove();
}
});
}
</script>
<style type="text/css">
.center{
margin: auto;
text-align:center;
font-size: 24px;
width: 60%;
background: lightblue;
}
</style>
</head>
<body>
<div class="center">
<p id="test">Springboot整合MyBatis通过ajax查询MySQL数据库数据</b></p>
<button id="btn1">显示所有数据</button>
<button id="btn2">查询</button>
<input id="id" name="id" type="text" placeholder="请输入id值"/>
<br>
<table class="table table-bordered" id='tabletest'>
</table>
</div>
</body>
</html>
