准备数据库(mysql)
mysql安装,可以参考菜鸟教程Mysql安装
当然也可以使用宝塔面板(免费版)傻瓜式安装
这里需要帮助的话,可以私聊我,如果我有时间的话一定会帮忙的
为什么使用mysql,当然是因为开源啦,而且社区文档成熟,有很多坑别人都帮你踩过啦。
新建单表
这里先不做用户权限管理了,直接演示简单表的增删改查。
CREATE TABLE `book` (
`ID` varchar(60) NOT NULL COMMENT '主键',
`CODE` varchar(20) NOT NULL COMMENT '书本编号',
`NAME` varchar(150) NOT NULL COMMENT '书本名称',
`CREATE_TIME` datetime NOT NULL COMMENT '创建时间',
`UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
`REMARK` varchar(1500) DEFAULT NULL COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='书本表'
直接使用MP(MyBatis-Plus)
PS: 个人JPA用的很少,就不介绍了,喜欢的可以自己查阅哦。都是好工具,但是会用用好才是王道。
- 增加以下maven依赖
<!-- mysql连接 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
- 配置MP和数据库连接,新增MybatisPlusConfig.java,配置application.properties(分页下一章演示)
package com.demo.bootweb.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@EnableTransactionManagement
@Configuration
@MapperScan({"com.demo.bootweb.**.mapper"})
public class MybatisPlusConfig {
/**
* 配置分页插件, 注意设置相应数据库类型
* @return MybatisPlusInterceptor
*/
@Bean
public MybatisPlusInterceptor paginationInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
/**
* mybatisPlus 自定义配置
* @return ConfigurationCustomizer
*/
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new MybatisPlusCustomizers();
}
/**
* 自定义相关配置
*/
static class MybatisPlusCustomizers implements ConfigurationCustomizer {
@Override
public void customize(MybatisConfiguration configuration) {
configuration.setJdbcTypeForNull(JdbcType.NULL);
}
}
}
#端口配置 后面若部署至tomcat
server.port=8080
#项目路径配置 一般为根路径或项目名
server.servlet.context-path=/boot
#freemarker的默认配置 编码UTF-8 路径是templates下 我们直接使用默认配置即可
#但是如果需要访问request的对象,我们需要配置以下其引用名称 如页面可能会经常用到项目路径${request.contextPath}
spring.freemarker.request-context-attribute=request
#数据库配置
#Mysql root Ro0t!@#
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/frame?autoReconnect=true&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=Ro0t!@#
实现增删改查功能
- 后台增加以下代码
- BookController.java
package com.demo.bootweb.book.controller;
import com.demo.bootweb.book.entity.Book;
import com.demo.bootweb.book.service.IBookService;
import org.springframework.web.bind.annotation.*;
import java.time.LocalDateTime;
import java.util.List;
import java.util.UUID;
@RestController
@RequestMapping("/book")
public class BookController {
private final IBookService bookService;
public BookController(IBookService bookService) {
this.bookService = bookService;
}
/**
* 根据ID查询书本详情
*/
@GetMapping("/get/{id}")
public Book hello(@PathVariable String id){
return bookService.getById(id);
}
/**
* 查询所有的书本信息列表
*/
@GetMapping("/queryList")
public List<Book> queryList(){
return bookService.list();
}
/**
* 新增书本信息
*/
@PostMapping
public void save(Book book){
book.setId(UUID.randomUUID().toString().replace("-", ""));
book.setCreateTime(LocalDateTime.now());
bookService.save(book);
}
/**
* 修改书本信息
*/
@PutMapping
public void update(Book book){
book.setUpdateTime(LocalDateTime.now());
bookService.updateById(book);
}
/**
* 根据ID删除书本信息
*/
@DeleteMapping
public void delete(String id){
bookService.removeById(id);
}
}
- Book.java
package com.demo.bootweb.book.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import java.time.LocalDateTime;
/**
* 书本模型类
*/
@TableName("BOOK")
public class Book {
/**
* 主键
*/
private String id;
/**
* 书本编号
*/
private String code;
/**
* 书本名称
*/
private String name;
/**
* 备注
*/
private String remark;
/**
* 创建时间
*/
@TableField("CREATE_TIME")
private LocalDateTime createTime;
/**
* 更新时间
*/
@TableField("UPDATE_TIME")
private LocalDateTime updateTime;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public LocalDateTime getCreateTime() {
return createTime;
}
public void setCreateTime(LocalDateTime createTime) {
this.createTime = createTime;
}
public LocalDateTime getUpdateTime() {
return updateTime;
}
public void setUpdateTime(LocalDateTime updateTime) {
this.updateTime = updateTime;
}
}
- BookMapper.java & BookMapper.xml
package com.demo.bootweb.book.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.bootweb.book.entity.Book;
import org.springframework.stereotype.Repository;
/**
* 书本表 Mapper 接口
*/
@Repository
public interface BookMapper extends BaseMapper<Book> {
}
<?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.demo.bootweb.book.mapper.BookMapper">
</mapper>
- BookServiceImpl.java & IBookService.java
package com.demo.bootweb.book.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.demo.bootweb.book.entity.Book;
import com.demo.bootweb.book.mapper.BookMapper;
import com.demo.bootweb.book.service.IBookService;
import org.springframework.stereotype.Service;
/**
* 书本表 - 服务实现类
*/
@Service
public class BookServiceImpl extends ServiceImpl<BookMapper, Book> implements IBookService {
}
package com.demo.bootweb.book.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.demo.bootweb.book.entity.Book;
public interface IBookService extends IService<Book> {
}
- 前端的简单实现
<#assign root=request.getContextPath()>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>HelloWorld!</title>
<style>
.table{
border-collapse:collapse;
}
.table tr th, .table tr td{
border: 1px solid #f1f1f1;
padding: 8px 12px;
}
</style>
</head>
<body>
<h1>Hello World!</h1>
<h1>项目根目录:${root}</h1>
<#-- 使用绝对路径,这样可以避免产生相对路径问题 -->
<a href="${root}/hello/dj">进入hello dj</a>
<button id="QueryList" type="button">调用QueryList接口</button>
<div id="QueryListResult" style="width: 600px;border: 1px solid #dddddd; padding: 12px;"></div>
<div style="margin-top: 20px;">
</div>
<div style="display: flex;margin-top: 10px;">
<div style="flex: 2">
<table class="table">
<thead>
<tr>
<th style="width: 50px; text-align: center;">序号</th>
<th style="width: 80px; text-align: center;">书本编号</th>
<th style="width: 120px; text-align: left;">书本名称</th>
<th>书本备注</th>
<th style="width: 180px; text-align: center;">创建时间</th>
<th style="width: 180px; text-align: center;">更新时间</th>
<th style="width: 100px; text-align: center;">操作</th>
</tr>
</thead>
<tbody id="list">
</tbody>
</table>
</div>
<div style="padding-left: 20px;flex: 1">
<form id="form" style="margin-top:10px; padding: 20px; width: 400px;">
<input id="form_id" name="id" type="hidden" value="">
<div>
<label for="form_code">书本编号:</label>
<input id="form_code" name="code" value="">
</div>
<div>
<label for="form_name">书本名称:</label>
<input id="form_name" name="name" value="">
</div>
<div>
<label for="form_remark">备注:</label>
<input id="form_remark" name="remark" value="">
</div>
<button id="form_save" type="button">保存</button>
<button type="button" onclick="resetForm()">新增(清空)</button>
</form>
</div>
</div>
</body>
<#--这里只是做简单演示直接使用百度的cdn,如果没有外网环境可以直接将jquery.min.js复制到项目中直接引入静态资源(放在static下)-->
<script src="https://libs.baidu.com/jquery/1.10.2/jquery.min.js"></script>
<script>
function editData(e){
var row = $(e).parent().parent().data();
$('#form_id').val(row.id);
$('#form_code').val(row.code);
$('#form_name').val(row.name);
$('#form_remark').val(row.remark);
}
function deleteData(e){
var bool = confirm('确定要删除吗');
if(bool){
$.ajax('${root}/book?id='+$(e).parent().parent().data().id,{
type: 'DELETE',
success: function () {
alert('删除成功')
location.reload();
}
});
}
}
function resetForm(){
$('form')[0].reset();
$('#form_id').val('');
}
$(function (){
$('#QueryList').on('click',function () {
$.ajax('${root}/queryList',{
type: 'GET',
success: function (data) {
//可F12在控制台下查看返回数据
console.log(data);
$('#QueryListResult').text(JSON.stringify(data));
}
});
});
var $form = $('#form');
var $list = $('#list');
$('#form_save').on('click', function (){
if($('#form_name').val() === ''){
alert('书本名称必填');
return;
}
//判定是新增、编辑
var type = $('#form_id').val() === ''?'POST':'PUT';
$.ajax('${root}/book',{
type: type,
data: $form.serializeArray(),
success: function (data) {
alert('保存成功!');
//新增后清空表单
if(type === 'POST'){
resetForm();
}
freshList();
},
error: function (xhr , error){
alert(error)
}
});
});
var freshList = function (){
$.ajax('${root}/book/queryList',{
type: 'GET',
success: function (data) {
if(data && data.length > 0){
var tpl = '<tr data-id="{id}" data-code="{code}" data-name="{name}" data-remark="{remark}">' +
'<td style="text-align: center;">{index}</td>' +
'<td style="text-align: center;">{code}</td>' +
'<td style="text-align: left;">{name}</td>' +
'<td style="text-align: left;" title="{remark}"><div style="overflow: hidden;text-overflow: ellipsis;' +
'white-space: nowrap;word-break: break-all;width: 200px;">{remark}</div></td>' +
'<td style="text-align: center;">{createTime}</td>' +
'<td style="text-align: center;">{updateTime}</td>' +
'<td style="text-align: center;">' +
'<button type="button" οnclick="editData(this)">修改</button>' +
'<button type="button" style="margin-left: 10px" οnclick="deleteData(this)">删除</button>' +
'</td>' +
'</tr>';
var htm = '';
for(var i=0; i<data.length; i++){
var d = data[i];
htm += tpl
.replace('{index}', ''+(i+1))
.replace('{id}', d.id)
.replaceAll('{code}', d.code)
.replaceAll('{name}', d.name)
.replaceAll('{remark}', d.remark)
.replace('{createTime}', d.createTime)
.replace('{updateTime}', d.updateTime == null? '': d.updateTime);
}
$list.html(htm);
}
}
});
}
freshList();
})
</script>
</html>
效果演示
结语
以上就是数据库连与增删改查功能的简单实现。
如有疑问,欢迎留言。