IESM项目实训七
更新成绩数据库相关操作
上一篇博客是功能实现,本篇博客是所用到的是数据库相关操作。
ScoresMapper.xml
数据库操作
<select id="updateUsualScores" resultType="org.jeecg.modules.demo.ScoresInput.entity.Scores">
update scores
set
usual_Scores = #{usual_Scores}
where
cs_id = #{cs_id} and stu_id=#{stu_id} and les_ord=#{les_ord}
</select>
<select id="updateExamScores" resultType="org.jeecg.modules.demo.ScoresInput.entity.Scores">
update scores
set
exam_Scores = #{exam_Scores}
where
cs_id = #{cs_id} and stu_id=#{stu_id} and les_ord=#{les_ord}
</select>
<select id="updateScores" resultType="org.jeecg.modules.demo.ScoresInput.entity.Scores">
update scores
set
scores = #{scores}
where
cs_id = #{cs_id} and stu_id=#{stu_id} and les_ord=#{les_ord}
</select>
<select id="updateTestScores" resultType="org.jeecg.modules.demo.ScoresInput.entity.Scores">
update scores
set
test_scores = #{test_scores}
where
cs_id = #{cs_id} and stu_id=#{stu_id} and les_ord=#{les_ord}
</select>
ScoresMapper
接口
public void updateUsualScores(@Param("usual_Scores" )Integer usual_Scores,@Param("cs_id") String cs_id,@Param("les_ord") String les_ord ,@Param("stu_id") String stu_id);
public void updateExamScores(@Param("exam_Scores" )Integer exam_Scores,@Param("cs_id") String cs_id,@Param("les_ord") String les_ord ,@Param("stu_id") String stu_id);
public void updateScores(@Param("scores" )String scores,@Param("cs_id") String cs_id,@Param("les_ord") String les_ord ,@Param("stu_id") String stu_id);
public void updateTestScores(@Param("test_scores" )Integer text_scores,@Param("cs_id") String cs_id,@Param("les_ord") String les_ord,@Param("stu_id") String stu_id);
定义接口 IScoresService
public boolean updateUsualScores(Integer usual_Scores, String cs_id,String les_ord ,String stu_id);
public boolean updateExamScores(Integer exam_Scores, String cs_id,String les_ord ,String stu_id);
public boolean updateScores(String scores, String cs_id,String les_ord,String stu_id );
public boolean updateTestScores(Integer test_scores,String cs_id,String les_ord,String stu_id );
ScoresServiceImpl extends ServiceImpl<ScoresMapper, Scores>
接口实现
@Override
@Transactional
public boolean updateUsualScores(Integer usual_Scores, String cs_id,String les_ord ,String stu_id){
scoresMapper.updateUsualScores(usual_Scores,cs_id,les_ord,stu_id);
return true;
}
@Override
@Transactional
public boolean updateExamScores(Integer exam_Scores, String cs_id,String les_ord ,String stu_id) {
scoresMapper.updateExamScores(exam_Scores,cs_id,les_ord,stu_id);
return true;
}
@Override
@Transactional
public boolean updateScores(String scores, String cs_id,String les_ord ,String stu_id) {
scoresMapper.updateScores(scores,cs_id,les_ord,stu_id);
return true;
}
@Override
@Transactional
public boolean updateTestScores(Integer test_scores, String cs_id, String les_ord, String stu_id) {
scoresMapper.updateTestScores(test_scores,cs_id,les_ord,stu_id);
return true;
}
自定义获取按班级获取学生分页列表
ScoresMapper.xml
数据库按照课程编号、课序号获取学生列表
<select id="getScoresList" parameterType="java.lang.String" resultType="org.jeecg.modules.demo.ScoresInput.entity.Scores">
SELECT *
FROM scores
WHERE
cs_id = #{cs_id} and les_ord=#{les_ord}
</select>
ScoresMapper
接口
public List<Scores> getScoresList(Page<Scores> page,@Param("cs_id") String cs_id,@Param("les_ord") String les_ord );
定义接口 IScoresService
public Page<Scores> getScoresList(Page<Scores> page, String cs_id, String les_ord);
ScoresServiceImpl extends ServiceImpl<ScoresMapper, Scores>
接口实现
@Override
@Transactional
public Page<Scores> getScoresList(Page<Scores> page, String cs_id, String les_ord) {
System.out.println(page.setRecords(scoresMapper.getScoresList(page,cs_id,les_ord)));
return page.setRecords(scoresMapper.getScoresList(page,cs_id,les_ord));
}
ScoresController
中重写url:list对应的方法
@ApiOperation(value = "课程成绩表-分页列表查询", notes = "课程成绩表-分页列表查询")
@GetMapping(value = "/list")
public Result<Page<Scores>> queryPageList(Scores scores,
@RequestParam(name="cs_id")String cs_id,
@RequestParam(name="les_ord")String les_ord,
@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
@RequestParam(name = "pageSize", defaultValue = "30") Integer pageSize,
HttpServletRequest req) {
Result<Page<Scores>> result = new Result<>();
Page<Scores> pageList = new Page<Scores>(pageNo, pageSize);
pageList=scoresService.getScoresList(pageList,cs_id,les_ord);
this.stuList=scoresService.getStuList(cs_id,les_ord);
this.temporaryScores=scoresService.getLessonInfo(cs_id,les_ord).get(0);
result.setSuccess(true);
result.setResult(pageList);
return result;
}
stuList
是当前需要录入学生列表,后续各种录入操作不需要再从数据库获取,直接使用该List;
temporaryScores
是当前录入课程,初始化默认是数据库查询结果的第一个课程。
前端初步设为固定数据,后期更改,重写加载数据方法,需要将必要条件传给后端:
//table列表
superFieldList: [],
//当前录入的课序号和课程编号
csId: '',
lesOrd: '',
//加载成绩初始化数据
loadData:function(arg) {
if (!this.url.list) {
this.$message.error("请设置url.list属性!")
return
}
//加载数据 若传入参数1则加载第一页的内容
if (arg === 1) {
this.ipagination.current = 1;
}
var params = this.getQueryParams();//查询条件
this.loading = true;
getAction(this.url.list, params).then((res) => {
if (res.success) {
this.dataSource = res.result.records || res.result;
if (res.result.total) {
this.ipagination.total = res.result.total;
} else {
this.ipagination.total = 0;
}
//update-end---for:适配不分页的数据列表------------
} else {
if (this.valueChange !== false) {
this.$message.warning(res.message)
}
}
}).finally(() => {
this.loading = false
})
},
//重写默认的获取查询条件方法
getQueryParams:function() {
//获取查询条件
let sqp = {}
console.log("课序号" + this.lesOrd);
console.log("课序号" + this.csId);
const les_ord = {les_ord: this.lesOrd};
const cs_id = {cs_id: this.csId};
if (this.superQueryParams) {
sqp['superQueryParams'] = encodeURI(this.superQueryParams)
sqp['superQueryMatchType'] = this.superQueryMatchType
}
var param = Object.assign(sqp, this.queryParam, this.isorter, this.filters, cs_id, les_ord);
param.field = this.getQueryField();
param.pageNo = this.ipagination.current;
param.pageSize = this.ipagination.pageSize;
return filterObj(param);
},
//a-table分页改变时,加载方法
handleTableChange:function(pagination, filters, sorter) {
if (Object.keys(sorter).length > 0) {
this.isorter.column = sorter.field;
this.isorter.order = "ascend" === sorter.order ? "asc" : "desc"
}
this.ipagination = pagination;
this.cTable = true;
this.loadData();
},
设置总成绩生成的各种参数,包括平时成绩比例、实验成绩比例和总成绩类型
为防止误触,需要点击按钮后才可以进行更改,默认显示为数据库已有数值。
前端组件:
<a-row :gutter="48">
<a-col :md="8" :sm="24">
<a-form-item label="平时成绩占比:">
<a-input-number v-model="usualPercent"
:min="0"
:max="100"
:formatter="numberRep" :parser="numberRep"
placeholder="0"
@change="onUsualPercentChange"
oninput="if(usualPercent>100)usualPercent=100;if(usualPercent.length>3)usualPercent=usualPercent.slice(0,3);if(usualPercent<0)usualPercent=0"
/>
</a-form-item>
</a-col>
<a-col :md="8" :sm="24">
<a-form-item label="实验成绩占比:">
<a-input-number v-model="testPercent"
:min="0"
:max="100"
:formatter="numberRep" :parser="numberRep"
placeholder="0"
@change="onTestPercentChange" oninput="if(testPercent>100)testPercent=100;if(testPercent.length>4)testPercent=testPercent.slice(0,4);if(testPercent<0)testPercent=0"
/>
</a-form-item>
</a-col>
<a-col :md="8" :sm="24">
<a-form-item label="成绩类型选择:">
<div id="scoresType">
<a-select v-decorator="['source', {initialValue:this.scoresTypeValue}]"
placeholder="请选择成绩类型"
@change="handleScoresTypeChange">
<a-select-option value="百分制">百分制</a-select-option>
<a-select-option value="评级制">评级制</a-select-option>
</a-select>
</div>
</a-form-item>
</a-col>
<div class="table-operator">
<a-button type="primary" @click="updateScoresPercent" >设置成绩录入参数</a-button>
</div>
</a-row>
输入时限制输入框只能为0-100的数字,如果超出100设为100,小于0设为0:
oninput="if(testPercent>100)testPercent=100;if(testPercent.length>4)testPercent=testPercent.slice(0,4);if(testPercent<0)testPercent=0"
//限制比例只能输入数字
numberRep: function(value){
if (typeof value === 'string') {
return !isNaN(Number(value)) ? value.replace(/^(0+)|[^\d]/g, '') : ''
} else if (typeof value === 'number') {
return !isNaN(value) ? String(value).replace(/^(0+)|[^\d]/g, '') : ''
} else {
return ''
}
},
下拉框中动态设置默认值使用如下形式:
v-decorator="['source', {initialValue:this.scoresTypeValue}]"
前端输入响应事件,所有值只是暂存,点击按钮后才会更改数据库:
//各类成绩占比
usualPercent:0,
testPercent:0,
scoresTypeValue:"",
//成绩比例变化,将其保存
onUsualPercentChange :function(value){
if(this.inputState==="已提交"){
alert("该课程成绩已提交不可进行修改");
}else {
this.usualPercentTemp = value;
console.log('changed', this.usualPercent)
}
},
onTestPercentChange: function(value){
if(this.inputState==="已提交"){
alert("该课程成绩已提交不可进行修改");
}else {
this.testPercentTemp = value;
console.log('changed', this.testPercent)
}
},
//总成绩类型修改
handleScoresTypeChange:function(inputValue){
console.log("inputValue"+inputValue);
this.scoresTypeValueTemp=inputValue;
},
按钮响应事件:
//设置成绩各类参数
updateScoresPercent:function(){
if(this.inputState==="已提交"){
alert("该课程成绩已提交不可进行修改");
}else {
let lesName = '';
for (let i = 0, len = this.lessonList.length; i < len; i++) {
const element = this.lessonList[i]
if (element.lesOrd === this.lesOrd && element.lesId === this.csId) {
lesName = element.lesName;
break;
}
}
if(this.scoresTypeValueTemp!=null) {
this.scoresTypeValue = this.scoresTypeValueTemp;
}
if(this.usualPercentTemp!=null){
this.usualPercent=this.usualPercentTemp;
}
if(this.testPercentTemp!=null){
this.testPercent=this.testPercentTemp;
}
console.log('testPercent', this.testPercent)
console.log('usualPercent', this.usualPercent)
alert("您是否确认将" + lesName + "课程的" +
"平时成绩比例设为" + this.usualPercent + "%," +
"将实验成绩比例设为" + this.testPercent + "%,"+
"将总成绩类型设为"+this.scoresTypeValue+",")
getAction(this.url.updatePercent, {
usual_percent: this.usualPercent,
test_percent: this.testPercent,
scoresType:this.scoresTypeValue,
les_id: this.csId,
les_ord: this.lesOrd
}).then((res) => {
if (res.result != null) {
this.superFieldList = res.result;
this.dataSource = this.superFieldList;
console.log("修改成绩比例成功!");
} else {
console.log("修改成绩比例失败!")
}
})
}
},
后期发现漏洞,将所有输入框数据暂存入备份中,点击按钮后才会将全局变量更改,因为全局变量会被其他录入成绩方式使用,不能直接改变。
后端代码:
TemporaryScoresController
中
@ResponseBody
@RequestMapping(value="/updatePercent")
public Result<List<Scores>> updatePercent(@RequestParam(name="usual_percent")int usual_percent,
@RequestParam(name="test_percent")int test_percent,
@RequestParam(name="les_id")String les_id,
@RequestParam(name="les_ord")String les_ord,
@RequestParam(name="scoresType")String scoresType){
temporaryScoresService.updatePercent(usual_percent, test_percent, les_id, les_ord,scoresType);
List<Scores> stuList=temporaryScoresService.getStuList(les_id,les_ord);
this.setStuList(stuList);
for(int i=0;i<stuList.size();i++){
this.updateScores(stuList.get(i),usual_percent,test_percent,scoresType);
}
System.out.println("更新了总成绩");
return Result.OK(this.stuList);
}
TemporaryScoresMapper.xml
数据库操作
<select id="updatePercent" resultType="org.jeecg.modules.demo.ScoresInput.entity.TemporaryScores">
update temporary_scores
set
usual_percent = #{usual_percent},
test_percent=#{test_percent},
scores_type=#{scores_type}
where
les_id = #{les_id} and les_ord=#{les_ord}
</select>
interface TemporaryScoresMapper
接口
public void updatePercent(@Param("usual_percent") int usual_percent,
@Param("test_percent") int test_percent,
@Param("les_id")String les_id,
@Param("les_ord")String les_ord,
@Param("scores_type")String scores_type);
interface ITemporaryScoresService
接口
public void updatePercent(int usual_percent,int test_percent,String les_id, String les_ord,String scores_type);
TemporaryScoresServiceImpl
接口实现方法
@Override
@Transactional
public void updatePercent(int usual_percent,int test_percent,String les_id, String les_ord,String scores_type) { temporaryScoresMapper.updatePercent(usual_percent,test_percent,les_id,les_ord,scores_type);
System.out.println("执行过了");
}