目录
4.Service层(service接口+servicelmpl实现类)
前言
这次的任务其实对我而言已经有点难了,因为毕竟目前我连Java基础都不是很扎实,对于看我文章的大佬们来说,肯定还是小事一桩了。具体功能的实现就是数据库查询某张表某个字段的值有相同的记录并统计个数,然后后端使用SSM框架获取查询结果,传递数据到前端,前端使用的是vue,最后成功的显示到页面上。
后端开发:IDEA Springboot
前端:VScode Vue
数据库:Navicat MySQL
数据库表:
前端最终显示图:
默认显示与数据库表内容一致(未设置显示id)上方可以根据条件显示搜索内容
点击统计按钮可以自动统计关键词出现的次数
数据库查询语句(统计功能实现)
参考博文:
Oralce数据库查询某张表某个字段的值有相同的记录并统计个数_lixinyao5281的专栏-CSDN博客_oracle统计相同的数据个数
select words,count(words) countnumber from tb_word group by words having(count(words)>=1)
order by countnumber desc;
后端
1.数据库配置application.yml
# 数据源配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
druid:
master:
url: jdbc:mysql://localhost:3306/tfs?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: root
# 从库数据源
slave:
# 从数据源开关/默认关闭
enabled: false
url: 1
username:
password:
# 初始连接数
initialSize: 5
# 最小连接池数量
minIdle: 10
# 最大连接池数量
maxActive: 2000
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
2.实体类
因为条件搜索查询和统计是两个业务逻辑,这里写两个实体类与查询的结果相对应。
TbWord实体类(实现条件搜索查询功能,包括增删改查)
import com.chery.common.annotation.Excel;
import com.chery.common.core.domain.BaseEntity;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
public class TbWord extends BaseEntity {
private static final long serialVersionUID = 1L;
private Long id;
@Excel(name = "关键词")
private String words;
@Excel(name = "词性")
private String flag;
public void setId(Long id)
{
this.id = id;
}
public Long getId() { return id; }
public String getWords() {
return words;
}
public void setWords(String words) {
this.words = words;
}
public String getFlag() {
return flag;
}
public void setFlag(String flag) {
this.flag = flag;
}
@Override
public String toString() {
return new ToStringBuilder(this, ToStringStyle.MULTI_LINE_STYLE)
.append("id", getId())
.append("words", getWords())
.append("flag", getFlag())
.toString();
}
}
TbWordCount实体类(实现统计功能)
import com.chery.common.annotation.Excel;
import com.chery.common.core.domain.BaseEntity;
public class TbWordCount extends BaseEntity {
private static final long serialVersionUID = 1L;
private Long id;
@Excel(name = "关键词")
private String words;
@Excel(name = "统计")
private String count;
public String getWords() {
return words;
}
public void setWords(String words) {
this.words = words;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getCount() {
return count;
}
@Override
public String toString() {
return "TbWordCount{" +
"id=" + id +
", words='" + words + '\'' +
", count='" + count + '\'' +
'}';
}
public void setCount(String count) {
this.count = count;
}
}
3.mapper层
配置文件TbWordMapper.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.chery.web.mychery.mapper.TbWordMapper">
<resultMap type="TbWord" id="TbWordResult">
<result property="id" column="user_ID" />
<result property="words" column="words" />
<result property="flag" column="flag" />
</resultMap>
<resultMap type="TbWordCount" id="TbWordCountResult">
<result property="words" column="words" />
<result property="count" column="countnumber" />
</resultMap>
<sql id="selectTbWordVo">
select user_ID,words,flag from tb_word
</sql>
<!--统计功能 -->
<select id="selectTbWordCount" resultMap="TbWordCountResult">
select words,count(words) countnumber from tb_word group by words having(count(words)>=1)
order by countnumber desc;
</select>
<!--增删改查 -->
<select id="selectTbWordList" parameterType="TbWord" resultMap="TbWordResult">
<include refid="selectTbWordVo"/>
<where>
<if test="words != null and words != ''"> and words like concat('%', #{words}, '%')</if>
<if test="flag != null and flag != ''"> and flag like concat('%', #{flag}, '%') </if>
and flag !=""
</where>
</select>
<select id="selectTbWordById" parameterType="Long" resultMap="TbWordResult">
<include refid="selectTbWordVo"/>
where uer_ID = #{id}
</select>
<insert id="insertTbWord" parameterType="TbWord" useGeneratedKeys="true" keyProperty="id">
insert into tb_word
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="words != null">words,</if>
<if test="flag != null">flag,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="words != null">#{words},</if>
<if test="flag != null">#{flag},</if>
</trim>
</insert>
<update id="updateTbWord" parameterType="TbWord">
update tb_word
<trim prefix="SET" suffixOverrides=",">
<if test="words != null">words = #{words},</if>
<if test="flag != null">userName = #{flag},</if>
</trim>
where uer_ID = #{id}
</update>
<delete id="deleteTbWordById" parameterType="Long">
delete from tb_word where user_ID = #{id}
</delete>
<delete id="deleteTbUserByIds" parameterType="String">
delete from tb_word where user_ID in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
</mapper>
TbWordMapper
import com.chery.web.mychery.domain.TbWordCount;
import java.util.List;
/**
* 关键词查询Mapper接口
*
* @author chery
* @date 2021-12-15
*/
public interface TbWordMapper<TbWord> {
//统计
public List<TbWordCount> selectTbWordCount();
/**
* 关键词信息查询
* @param id 车主信息查询主键
* @return 车主信息查询
*/
public TbWord selectTbWordById(Long id);
/**
* 查询关键词列表
* @param tbWord 关键词查询
* @return 关键词查询集合
*/
public List<TbWord> selectTbWordList(TbWord tbWord);
/**
* 新增关键词查询
*
* @param tbWord 关键词信息查询
* @return 结果
*/
public int insertTbWord(TbWord tbWord);
//修改
public int updateTbWord(TbWord tbWord);
//删除
public int deleteTbWordById(Long id);
}
4.Service层(service接口+servicelmpl实现类)
ITbWordService接口
import com.chery.web.mychery.domain.TbWord;
import com.chery.web.mychery.domain.TbWordCount;
import java.util.List;
public interface ITbWordService
{
public List<TbWordCount> selectTbWordCount();//统计
public TbWord selectTbWordById(Long id);
public List<TbWord> selectTbWordList(TbWord tbWord);
int insertTbUser(TbWord tbWord);
int updateTbUser(TbWord tbWord);
public int insertTbWord(TbWord tbWord);
public int updateTbWord(TbWord tbWord);
public int deleteTbWordById(Long id);
}
TbWordServiceImpl实现类
import java.util.List;
import com.chery.web.mychery.domain.TbWordCount;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.chery.web.mychery.mapper.TbWordMapper;
import com.chery.web.mychery.domain.TbWord;
import com.chery.web.mychery.service.ITbWordService;
@Service
public class TbWordServiceImpl implements ITbWordService
{
@Autowired
private TbWordMapper tbWordMapper;
//统计
@Override
public List<TbWordCount> selectTbWordCount(){ return tbWordMapper.selectTbWordCount(); }
/**
* 查询关键词信息
*
* @param id 关键词信息查询主键
* @return 关键词信息查询
*/
@Override
public TbWord selectTbWordById(Long id)
{
return (TbWord) tbWordMapper.selectTbWordById(id);
}
/**
* 查询关键词列表
*
* @param tbWord 关键词查询
* @return 关键词查询
*/
@Override
public List<TbWord> selectTbWordList(TbWord tbWord)
{
return tbWordMapper.selectTbWordList(tbWord);
}
@Override
public int insertTbUser(TbWord tbWord) {
return 0;
}
@Override
public int updateTbUser(TbWord tbWord) {
return 0;
}
/**
* 新增关键词信息查询
*
* @param tbWord 关键词信息查询
* @return 结果
*/
@Override
public int insertTbWord(TbWord tbWord)
{
return tbWordMapper.insertTbWord(tbWord);
}
/**
* 修改关键词查询
*
* @param tbWord 车主信息查询
* @return 结果
*/
@Override
public int updateTbWord(TbWord tbWord)
{
return tbWordMapper.updateTbWord(tbWord);
}
/**
* 删除关键词查询信息
*
* @param id 车主信息查询主键
* @return 结果
*/
@Override
public int deleteTbWordById(Long id)
{
return tbWordMapper.deleteTbWordById(id);
}
}
5.Controller层
TbWordController
import com.chery.common.annotation.Log;
import com.chery.common.core.controller.BaseController;
import com.chery.common.core.domain.AjaxResult;
import com.chery.common.core.page.TableDataInfo;
import com.chery.common.enums.BusinessType;
import com.chery.common.utils.poi.ExcelUtil;
import com.chery.web.mychery.domain.TbWord;
import com.chery.web.mychery.domain.TbWordCount;
import com.chery.web.mychery.service.ITbWordService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/mychery/word")
public class TbWordController extends BaseController
{
@Autowired
private ITbWordService tbWordService;
/**
* 统计关键词信息列表
*/
@PreAuthorize("@ss.hasPermi('mychery:word:analyse')")
@GetMapping("/analyse")
public List<TbWordCount> listWordCount(TbWordCount tbWordCount)
{
List<TbWordCount> countList = tbWordService.selectTbWordCount();
return countList;
}
/**
* 查询关键词信息列表
*/
@PreAuthorize("@ss.hasPermi('mychery:word:list')")
@GetMapping("/list")
public TableDataInfo list(TbWord tbWord)
{
startPage();
List<TbWord> list = tbWordService.selectTbWordList(tbWord);
return getDataTable(list);
}
/**
* 导出关键词信息列表
*/
@PreAuthorize("@ss.hasPermi('mychery:word:export')")
@Log(title = "关键词信息查询", businessType = BusinessType.EXPORT)
@GetMapping("/export")
public AjaxResult export(TbWord tbWord)
{
List<TbWord> list = tbWordService.selectTbWordList(tbWord);
ExcelUtil<TbWord> util = new ExcelUtil<TbWord>(TbWord.class);
return util.exportExcel(list, "关键词信息查询数据");
}
/**
* 获取关键词详细信息
*/
@PreAuthorize("@ss.hasPermi('mychery:word:query')")
@GetMapping(value = "/{id}")
public AjaxResult getInfo(@PathVariable("id") Long id)
{
return AjaxResult.success(tbWordService.selectTbWordById(id));
}
/**
* 新增关键词信息查询
*/
@PreAuthorize("@ss.hasPermi('mychery:word:add')")
@Log(title = "关键词信息查询", businessType = BusinessType.INSERT)
@PostMapping
public AjaxResult add(@RequestBody TbWord tbWord)
{
return toAjax(tbWordService.insertTbUser(tbWord));
}
/**
* 修改关键词信息查询
*/
@PreAuthorize("@ss.hasPermi('mychery:word:edit')")
@Log(title = "关键词信息查询", businessType = BusinessType.UPDATE)
@PutMapping
public AjaxResult edit(@RequestBody TbWord tbWord)
{
return toAjax(tbWordService.updateTbWord(tbWord));
}
/**
* 删除关键词信息查询
*/
@PreAuthorize("@ss.hasPermi('mychery:word:remove')")
@Log(title = "关键词信息查询", businessType = BusinessType.DELETE)
@DeleteMapping("/{id}")
public AjaxResult remove(@PathVariable Long id)
{
return toAjax(tbWordService.deleteTbWordById(id));
}
}
后端部分代码的编写感谢我的牛批clus室友@w7486,Mybatis方面的学习可以参考他的博文Mybatis入门(实现简单的CRUD)_w7486的博客-CSDN博客Mybatis执行流程、缓存机制、插件_w7486的博客-CSDN博客Mybatis整合Spring的原理以及整合一个案例_w7486的博客-CSDN博客
前端
word.js
import request from '@/utils/request'
//统计
export function listWordCount(analyse) {
return request({
url: '/mychery/word/analyse',
method: 'get',
params: analyse
})
}
// 查询关键词查询列表
export function listWord(query) {
return request({
url: '/mychery/word/list',
method: 'get',
params: query
})
}
// 查询关键词查询详细
export function getWord(id) {
return request({
url: '/mychery/word/' + id,
method: 'get'
})
}
// 新增关键词查询
export function addWord(data) {
return request({
url: '/mychery/word',
method: 'post',
data: data
})
}
// 修改关键词查询
export function updateWord(data) {
return request({
url: '/mychery/word',
method: 'put',
data: data
})
}
// 删除关键词查询
export function delWord(id) {
return request({
url: '/mychery/word/' + id,
method: 'delete'
})
}
// 导出关键词查询
export function exportWord(query) {
return request({
url: '/mychery/word/export',
method: 'get',
params: query
})
}
主页面index.vue
<template>
<div class="app-container">
<el-form :model="queryParams" ref="queryForm" :inline="true" v-show="showSearch" label-width="68px">
<el-form-item label="关键词" prop="words">
<el-input
v-model="queryParams.words"
placeholder="关键词"
clearable
size="small"
@keyup.enter.native="handleQuery"
/>
</el-form-item>
<el-form-item label="词性" prop="flag">
<el-select v-model="queryParams.flag" placeholder="请选择词性" clearable size="small">
<el-option
v-for="dict in flagOptions"
:key="dict.dictValue"
:label="dict.dictLabel"
:value="dict.dictValue"
/>
</el-select>
</el-form-item>
<el-form-item>
<el-button type="primary" icon="el-icon-search" size="mini" @click="handleQuery">搜索</el-button>
<el-button icon="el-icon-refresh" size="mini" @click="resetQuery">重置</el-button>
<el-button
type="warning"
icon="el-icon-download"
size="mini"
@click="handleExport"
v-hasPermi="['mychery:word:export']"
>导出</el-button>
</el-form-item>
</el-form>
<el-form :model="analyseParams" ref="analyseForm" :inline="true" v-show="showSearch" label-width="68px">
<!-- <el-form-item label="次数" prop="count">
<el-input
v-model="analyseParams.count"
placeholder="次数111"
clearable
size="small"
@keyup.enter.native="handleAnalyse"
/>
</el-form-item> -->
<el-form-item>
<el-button
type="warning"
icon="el-icon-search"
size="mini"
@click="handleAnalyse"
v-hasPermi="['mychery:word:analyse']"
>统计</el-button>
</el-form-item>
</el-form>
<el-table v-loading="loading" :data="wordList" @selection-change="handleSelectionChange">
<el-table-column type="selection" width="55" align="center" />
<el-table-column fixed="left" label="关键词" align="center" prop="words" width="110" />
<el-table-column fixed="left" label="词性" align="center" prop="flag" width="110"/>
<el-table-column fixed="left" label="次数" align="center" prop="count" width="110"/>
</el-table>
<pagination
v-show="total>0"
:total="total"
:page.sync="queryParams.pageNum"
:limit.sync="queryParams.pageSize"
@pagination="getList"
/>
<!-- 添加或修改关键词查询对话框 -->
<el-dialog :title="title" :visible.sync="open" width="500px" append-to-body>
<el-form ref="form" :model="form" :rules="rules" label-width="80px">
</el-form>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="submitForm">确 定</el-button>
<el-button @click="cancel">取 消</el-button>
</div>
</el-dialog>
</div>
</template>
<script>
import { listWordCount,listWord, getWord, delWord, addWord, updateWord, exportWord } from "@/api/mychery/word";
export default {
name: "Word",
data() {
return {
// 遮罩层
loading: true,
// 导出遮罩层
exportLoading: false,
// 选中数组
ids: [],
// 非单个禁用
single: true,
// 非多个禁用
multiple: true,
// 显示搜索条件
showSearch: true,
// 总条数
total: 0,
// 弹出层标题
title: "",
// 是否显示弹出层
open: false,
//关键词管理表格数据
flagOptions: [],
wordList:[],
countList:[],
// 查询参数
queryParams: {
pageNum: 1,
pageSize: 10,
words: undefined,
flag: undefined,
},
// 统计参数
analyseParams: {
pageNum: 1,
pageSize: 10,
words: undefined,
count: undefined,
},
// 表单参数
form: {},
// 表单校验
rules: {
}
};
},
created() {
this.getList();
this.getcountList(); //统计
this.getDicts("word_flag").then(response => {
this.flagOptions = response.data;
});
},
methods: {
/** 查询关键词查询列表 */
getList() {
this.loading = true;
listWord(this.queryParams).then(response => {
console.log(response)
this.wordList = response.rows;
this.total = response.total;
this.loading = false;
});
},
/** 统计关键词列表 */
getcountList() {
this.loading = true;
listWordCount(this.ananlyseParams).then(response => {
console.log(response)
this.wordList = response;
this.total = response.total;
this.loading = false;
});
},
// 取消按钮
cancel() {
this.open = false;
this.reset();
},
// 表单重置
reset() {
this.form = {
id: undefined,
words: undefined,
flag: undefined,
count: undefined,
};
this.resetForm("form");
},
/** 搜索按钮操作 */
handleQuery() {
this.queryParams.pageNum = 1;
this.getList();
},
/** 重置按钮操作 */
resetQuery() {
this.resetForm("queryForm");
this.handleQuery();
},
/** 统计按钮操作 */
handleAnalyse() {
this.analyseParams.pageNum = 1;
this.getcountList();
},
// 多选框选中数据
handleSelectionChange(selection) {
this.ids = selection.map(item => item.id)
this.single = selection.length!==1
this.multiple = !selection.length
},
/** 新增按钮操作 */
handleAdd() {
this.reset();
this.open = true;
this.title = "添加关键词查询";
},
/** 修改按钮操作 */
handleUpdate(row) {
this.reset();
const id = row.id || this.ids
getWord(id).then(response => {
this.form = response.data;
this.open = true;
this.title = "修改关键词查询";
});
},
/** 提交按钮 */
submitForm() {
this.$refs["form"].validate(valid => {
if (valid) {
if (this.form.id != null) {
updateWord(this.form).then(response => {
this.msgSuccess("修改成功");
this.open = false;
this.getList();
});
} else {
addWord(this.form).then(response => {
this.msgSuccess("新增成功");
this.open = false;
this.getList();
});
}
}
});
},
/** 删除按钮操作 */
handleDelete(row) {
const ids = row.id || this.ids;
this.$confirm('是否确认删除关键词为"' + ids + '"的数据项?', "警告", {
confirmButtonText: "确定",
cancelButtonText: "取消",
type: "warning"
}).then(function() {
return delWord(ids);
}).then(() => {
this.getList();
this.msgSuccess("删除成功");
}).catch(() => {});
},
/** 导出按钮操作 */
handleExport() {
const queryParams = this.queryParams;
this.$confirm('是否确认导出所有关键词数据项?', "警告", {
confirmButtonText: "确定",
cancelButtonText: "取消",
type: "warning"
}).then(() => {
this.exportLoading = true;
return exportWord(queryParams);
}).then(response => {
this.download(response.msg);
this.exportLoading = false;
}).catch(() => {});
}
}
};
</script>
个人总结
由于整个项目是已经实际存在的,我的任务也就是在已有的项目程序上添加新的功能及页面,这次的任务花了我数天的时间才得以实现,其中具体的业务逻辑我也讲不清楚,只能去贴这些实际的代码。基本都是我照葫芦画瓢,以及看了无数篇博文自己慢慢摸索做出来的。希望可以早点做到能手撕这些代码,同时理解其中的每一个业务逻辑,那时候才算真正可以了。
这是我室友的博客,他的努力同时也在不断激励着我去学习,可以的话,也可以多看看他的博文,给他点个赞。