嘤语学习助手-数据库课设

sterne 发布于

夏日重现的ed是真的耐听(然而播放器还是失效了，悲)

做完了，见新一篇

1.数据库

0.连接

终于把sql server（以下sql均指sql server）修好了。。。

昨天弄了一下午，真的不想玩辣，觉得用mysql摆烂算了，然后发现mysql的触发器和tsql不会写，寄

前段时间需求分析差不多的时候发现它从我的电脑上直接消失（一个什么配置管理器），然后正确的修复方法应该不是重装，或者不只是重装，在sql安装程序->维护->修复的地方修一下之后我才看到TCP/IP端口重现天日。。然后SSL什么的报错，改了jdbc里面的一段话（改的是idea里面jdbc的）

D:\jdk\jre\lib\security下就会有这个java.security

直接在idea安装路径下搜java.security，记事本打开，搜jdk.tls，将3DES_EDE_CBC删除

我超，行了！

1.导入数据

使用unicode(utf-8)字符集才能正确导入音标，否则一片问号

xlsx文件用excel打开后另存为->用csv保存->工具->编码->选unicode(utf-8)！选utf-8不行

在datagrip中导入，选utf-8

然后终于开始写触发器了

2.数据库代码

同时也是备份

什么嘛，我这不是挺能写的，不要停下来啊

把type属性去掉了，拼字符串那种事情不要啊

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
create database EN_learning;
use EN_learning;
ALTER DATABASE EN_learning COLLATE Chinese_PRC_CI_AS
create table Admin
(
    admin_id char(10) primary key,
    admin_passwd varchar(16)
)

create table users
(
    user_id char(10) primary key,
    user_passwd varchar(16),
    user_name varchar(20),
    user_date date default(getdate()),--注册时间
    user_learn smallint default(0)
)

create table en_list
(
    word      char(40) primary key,
    symbol    nvarchar(max),
    trans     nvarchar(max),
    frequency int default(0),
    img       image,
    vocal     image
)
create table motto
(
    sentence nvarchar(max),
    trans    nvarchar(max)
)
create table jp_list --计划后续添加
(
    word        nvarchar(80) primary key ,
    symbol      nvarchar(80),
    trans       nvarchar(max),
    frequency   int default(0),
    img         image,
    vocal       image
)
------------------------------------------------------------
------------------------------------------------------------
--用户------------------------------------------------------
------------------------------------------------------------
--创建用户后触发：生成一张用户的新表
create trigger users_add on users after insert as
    declare @user_id varchar(10),
            @create_table varchar(300),
            @create_view varchar(300),
            @sel varchar(300)
    select @user_id=user_id from inserted
    set @create_table='create table user_'+@user_id+
                      ' (' +
    '
        rank      smallint default(0),
        word      char(40) primary key,
        symbol    nvarchar(max),
        trans     nvarchar(max),
        add_date  date default(getdate()),
        del_date  date
    )'
    exec(@create_table)

    --生词表（view）user_1001001001 默认按rank降序排序
    set @sel='select rank,word,symbol,trans,add_date' +
             ' from user_'+@user_id+' where rank>=0'
    set @create_view='create view user_1_'+@user_id+' as '+@sel
    exec (@create_view)

    --熟词表（view）
    set @sel='select word,symbol,trans,add_date,del_date' +
             ' from user_'+@user_id+' where rank<0'
    set @create_view='create view user_2_'+@user_id+' as '+@sel
    exec (@create_view)
go

insert into users(user_id, user_passwd, user_name) values ('1001001001','123','test')
insert into users(user_id, user_passwd, user_name) values ('1001001002','123','test2')
-------------------------------------------------------------------
--注销用户后触发：删除用户的表&视图
create trigger users_del on users after delete as
    declare @user_id char(10),
            @drop_table varchar(100),
            @drop_view varchar(100)
    select @user_id=user_id from deleted
    set @drop_table='drop table user_'+@user_id
    exec (@drop_table)

    set @drop_view='drop view user_1_'+@user_id
    exec (@drop_view)

    set @drop_view='drop view user_2_'+@user_id
    exec (@drop_view)
go

delete from users where user_id='1001001001'
delete from users where user_id='1001001002'
------------------------------------------------------------
------------------------------------------------------------
--词库-----------------------------------------------------
------------------------------------------------------------
--单词总量统计
create proc en_list_counter as
    select count(*) from en_list
go

exec en_list_counter
------------------------------------------------------------
--使用单词模糊查找，找到后词频+1
create proc EN_search @word varchar(40) as
    set @word='%'+@word+'%'
    select * from en_list where word like @word
    declare @w varchar(40),
            @frequency int
    declare csr cursor for
    select word,frequency from en_list
    where word like @word
    open csr
    fetch next from csr into @w,@frequency
    while @@FETCH_STATUS=0
    begin
        update en_list set frequency=@frequency+1 where word=@w
        fetch next from csr into @w,@frequency
    end
    close csr
    deallocate csr
go

exec EN_search 'cat'
-------------------------------------------------------------------
--使用中文或词性模糊查找，找到后词频+1
create proc CH_search @trans varchar(40) as
    declare @w char(40),
            @frequency int
    set @trans='%'+@trans+'%'
    select * from en_list
    where trans like @trans

    declare csr cursor for
        select word,frequency from en_list
        where trans like @trans
        open csr
        fetch next from csr into @w,@frequency
        while @@FETCH_STATUS=0
            begin
                update en_list set frequency=@frequency+1 where word=@w
                fetch next from csr into @w,@frequency
            end
        close csr
        deallocate csr
go

exec CH_search '好'
exec CH_search 'prep'
-------------------------------------------------------------------
--词库中单词按词频排序，输出
create proc order_en_list_fre @order varchar(10) as
    if(@order='ASC')
    begin
        select * from en_list
        order by frequency asc
    end
    else if(@order='DESC')
    begin
        select * from en_list
        order by frequency desc
    end
go

exec order_en_list_fre 'ASC'
exec order_en_list_fre 'DESC'
------------------------------------------------------------
------------------------------------------------------------
--用户表-----------------------------------------------------
------------------------------------------------------------
--用户手动向生词表添加，一个一个加
create proc en_list_to_user @user_id varchar(10),@word varchar(40) as
    declare @ins nvarchar(max)
    set @ins='insert into user_'+@user_id+'(word,symbol,trans)' +
             'select word,symbol,trans from en_list where word='''+@word+''''
    exec(@ins)
go

exec en_list_to_user '1001001001','accomplished'

------------------------------------------------------------------
--每日生成生词表（向其中添加一定数目的单词）
create proc day_en_word @user_id varchar(20),@num smallint,@val smallint as
    declare @word varchar(40),
            @csr varchar(300)
    if(@val=1)--根据词库word正序选取（从字典序最小开始）
        begin
            set @csr='declare csr cursor for ' +
                     'select top '+convert(char,@num)+' word from en_list ' +
                     'where word not in(select word from user_'+@user_id+')' +
                     'order by word asc'
        end
    else if(@val=2)--根据word倒序选取（从字典序最大开始）
        begin
            set @csr='declare csr cursor for ' +
                     'select top '+convert(char,@num)+' word from en_list ' +
                     'where word not in(select word from user_'+@user_id+')' +
                     'order by word desc'
        end
    else if(@val=3)--根据词频选取，最大词频优先
        begin
            set @csr='declare csr cursor for ' +
                     'select top '+convert(char,@num)+' word from en_list ' +
                     'where word not in(select word from user_'+@user_id+')' +
                     'order by frequency desc'
        end
    else if(@val=4)--随机抽选
        begin
            set @csr='declare csr cursor for ' +
                     'select top '+convert(char,@num)+' word from en_list ' +
                     'where word not in(select word from user_'+@user_id+')' +
                     'order by newid()'
        end
    exec (@csr)
    open csr
    fetch next from csr into @word
    while @@FETCH_STATUS=0
    begin
        exec en_list_to_user @user_id,@word
        fetch next from csr into @word
    end
    close csr
    deallocate csr
go

exec day_en_word '1001001001',10,1
exec day_en_word '1001001001',10,4
-------------------------------------------------------------------
--增加某个单词rank，上限为10（在前端检测并提示）
create proc rank_up @user_id varchar(10),@word varchar(40) as
    declare @update varchar(100)
    set @update='update user_'+@user_id+' set rank=rank+1 where word='''+@word+''''
    exec(@update)
go

exec rank_up '1001001001','a'
exec rank_up '1001001001','above'
-------------------------------------------------------------------
--降低某个单词rank，下限为0
create proc rank_down @user_id varchar(10),@word varchar(40) as
declare @update varchar(100)
    set @update='update user_'+@user_id+' set rank=rank-1 where word='''+@word+''''
    exec(@update)
go
exec rank_down '1001001001','a'
-------------------------------------------------------------------
--从生词表删除移到熟词表
create proc rank_move @user_id varchar(10),@word varchar(40) as
declare @update varchar(100)
    set @update='update user_'+@user_id+' set rank=-1,del_date=getdate() where word='''+@word+''''
    exec(@update)
go
exec rank_move '1001001001','accomplished'
-------------------------------------------------------------------
--按rank排序
create proc order_user1_rank @user_id varchar(10),@order varchar(10) as
    declare @sel varchar(100)
    if(@order='ASC')
    begin
        set @sel='select * from user_1_'+@user_id+' order by rank asc'
    end
    else if(@order='DESC')
    begin
        set @sel='select * from user_1_'+@user_id+' order by rank desc'
    end
    exec(@sel)
go

exec order_user1_rank '1001001001','desc'
-------------------------------------------------------------------
--按添加时间排序，方便看今天新添加的
create proc order_user1_date @user_id varchar(10),@order varchar(10) as
    declare @sel varchar(100)
    if(@order='ASC')
        begin
            set @sel='select * from user_1_'+@user_id+' order by add_date asc'
        end
    else if(@order='DESC')
        begin
            set @sel='select * from user_1_'+@user_id+' order by add_date desc'
        end
    exec(@sel)
go

exec order_user1_date '1001001001','desc'
-------------------------------------------------------------------
--报表：今日掌握的单词
create proc day_learn @user_id varchar(10) as
    update users set user_learn=user_learn+1 where user_id=@user_id
    declare @sel varchar(100)
    set @sel='select word,symbol,trans from user_2_'+@user_id+' where del_date=convert(date,getdate())'
    exec(@sel)
go

exec day_learn '1001001001'
-------------------------------------------------------------------
--根据已学单词随机生成测试题，每次10词（前提是熟词表中有这么多）
--底层实现为随机从熟词表中select10个单词，前端可以选择不显示单词或者释义之类的
create proc user_en_test @user_id varchar(10) as
    declare @sel varchar(100)
    set @sel='select top 10 word,symbol,trans from user_2_'+@user_id+
             ' order by newid()'
    exec(@sel)
go

exec user_en_test  '1001001001'
-------------------------------------------------------------------
--随机名言，用于各种界面
create proc day_motto as
    select top 1 sentence,trans from motto order by newid()
go

exec day_motto
-------------------------------------------------------------------
-------------------------------------------------------------------
-------------------------------------------------------------------
--图片&音频导入
update en_list set img=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\img\cat.png',single_blob) as blob
where word='cat'
update en_list set img=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\img\dog.png',single_blob) as blob
where word='dog'
update en_list set img=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\img\amphibian.png',single_blob) as blob
where word='amphibian'
update en_list set img=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\img\aback.png',single_blob) as blob
where word='aback'
exec EN_search 'amphibian'
-------------------------------------------------------------------
update en_list set vocal=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\vocal\aback.mp3',single_blob) as blob
where word='aback'
update en_list set vocal=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\vocal\cat.mp3',single_blob) as blob
where word='cat'
update en_list set vocal=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\vocal\dog.mp3',single_blob) as blob
where word='dog'
update en_list set vocal=BulkColumn from openrowset(bulk N'D:\#DownLoad\2.2\数据库\vocal\amphibian.mp3',single_blob) as blob
where word='amphibian'
exec EN_search 'dog'
-------------------------------------------------------------------

新增：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--查询前多少个单词
create proc order_en_list @top int as
    declare @sel varchar(100)
    set @sel='select top '+convert(varchar,@top)+' * from en_list'
    exec(@sel)

--熟词表按照日期倒序显示
create proc order_user2_date @user_id varchar(10) as
    declare @sel varchar(100)
    set @sel='select * from user_2_'+@user_id+' order by del_date desc '
    exec(@sel)

exec order_user2_date '1001001001'

--熟词表移回生词表
create proc rank_re @user_id varchar(10),@word varchar(40) as
declare @update varchar(100)
    set @update='update user_'+@user_id+' set rank=0,del_date=null where word='''+@word+''''
    exec(@update)

exec rank_re '1001001001','a'

2.web

1.问题

  • inStream parameter is null

    druid.properties要复制两份放在resources和target/classes两个目录下

  • 5月 07, 2022 3:30:50 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl error
    严重: testWhileIdle is true, validationQuery not set

    5月 07, 2022 3:30:51 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
    信息: {dataSource-1} inited

    https://blog.csdn.net/qq_45954145/article/details/111073583

  • BeanUtils.populate(loginuser,map);

使用时前端的name要和后端user类对象名一样，否则调试时卡死

  • 实例化Servlet类异常

    我真服了，真的，我改了一下包名（就是开头S统一成小写s结果找不到这个类。。。）

  • 找不到jstl包

    下载jakarta-taglibs-standard-1.1.0版本，将lib目录中两个jar都丢到项目lib目录

  • input找不到属性，属性值为空

    不要设置input标签为disabled，设置为readonly就好

2.web代码(复制用)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<%@ include file="bootstrap.jsp"%>
<title>Login -EN Learning Assistant-</title>
<script type="text/javascript" src="js/jquery.js"></script>
	<script type="text/javascript"></script>


<c:if test="${not empty user}"><%@include file="user_nav.jsp"%></c:if>
<c:if test="${not empty admin}"><%@include file="admin_nav.jsp"%></c:if>
<c:if test="${empty user && not empty admin}"><%@include file="nav.jsp"%></c:if>


<%@ include file="copyright.jsp"%>

3.进度统计

目前没做的功能：

  1. lib&用户表的分页(lib完成，用户表不想做了)

  2. 点开始之后(完成)

  3. 查询功能(终于写对了)(导航栏的也写好了)

  4. 注册(什么校验都没有版（好看版）)

  5. 用户表里边一堆小按钮(完成)

  6. 打卡(完成，可能有一些bug)

  7. 单词测试（没做表单校验）

  8. 单词表的vocal和img(实际试了，估计是不支持动态的路径吧，不行)(？！行了好耶，用了百词斩的一些图)

    突然想到办法去做这个，改成string类型存路径。输出之后放在img标签的src里就OK了；再简化一点甚至不用改底层，图片只要叫words.word.jpg就ok，好诶

完善&优化：

  1. 打卡界面优化，打卡后的操作（完成）
  2. 单词测试（完成，但是校验写的比较简单）
  3. 单词的vocal
  4. 数据库备份咋整（哇哦）
  5. 我超你管理员还一点没写吧（蠕动）
    1. 哼哼啊啊啊啊啊登录（ok）
    2. 个人信息（ok）
    3. 登出（ok）
    4. 单词修改（ok）
    5. 单词删除（ok）
    6. 单词添加（ok）
    7. 用户查看（ok）
    8. 用户修改（ko）
    9. 用户注销（ko）
  6. bug：用户从lib选取单词的时候没有校验？(好了)
  7. motto的随机

bug：

  1. 管理员信息没做返回键
  2. 单词测试没有返回
  3. 怎么会是呢
STERNE

STERNE

SDUer，大一入门，大二入土，大三老东西！

63
1
20
TOC
  1. 1. 1.数据库
    1. 1.1. 0.连接
    2. 1.2. 1.导入数据
    3. 1.3. 2.数据库代码
      1. 1.3.1. 新增：
  2. 2. 2.web
    1. 2.1. 1.问题
    2. 2.2. 2.web代码(复制用)
  3. 3. 3.进度统计
NOTICE

这里还什么都没有呢喵~

CATEGORYS
TAGS
ACM Arc CCPC HPC bot c cpp deeplearning games hexo java maths pyqt python qt recommends review sql srtp test