文章目录
前端页面
<style>
p{text-align: right;position:relative;left:0%;width:100%;height:11%;top:11%;font-size: 25%}
input{width:70%;color:dimgrey;text-align: center;}
</style>
<div style="width: 40%;height: 85%;position: absolute;left:2%">
<div style="width: 100%;position: absolute;height: 70%;left: 0%;top: 0%">
<p> 化合物名称:<input id="cname" value="(必填)"></p>
<p> 分子式:<input id="formula"></p>
<p> CAS编号:<input id="otherDB"></p>
<p> 分子量:<input id="cweight"></p>
<p> 中文名称:<input id="chname"></p>
<p> 备注:<input id="spare"></p>
<p> 上传分子结构文件: <input type="file" style=""></p>
</div>
<div style="width: 100%;left: 0%;position: absolute;top: 70%;height: 30%">
<div style="display: inline;width: 30%;position: absolute;left: 0%;height: 100%;top:0%"></div>
<div style="display: inline;width: 70%;position: absolute;left: 30%;height: 100%;top:0%">
<div style="border: 1px solid #BBB;background: white;width:100%;">
<div id="drop_zone" style="margin: 10px 10px;border: 2px dashed #BBB;padding: 5% 5%;text-align: center;font-size: 20pt;color: #BBB;border-radius: 5px;">拖曳文件上传</div>
<div id="output_area"></div>
</div>
<div style="height: 5%"></div>
<button style="background:cornflowerblue;color:white;left:0%;width:100%;height:15%;border:0">上传化合物数据</button>
</div>
</div>
</div>
<script>
var output = document.getElementById('output_area');
var dropZone = document.getElementById('drop_zone');
if (!(('draggable' in dropZone) && ('ondragenter' in dropZone)
&& ('ondragleave' in dropZone) && ('ondragover' in dropZone)
&& window.File)) {
document.getElementById('error_msg').style.display = 'block';
document.getElementById('demo_area').style.display = 'none';
} else {
function handleFileDragEnter(e) {
e.stopPropagation();
e.preventDefault();
this.classList.add('hovering');
}
function handleFileDragLeave(e) {
e.stopPropagation();
e.preventDefault();
this.classList.remove('hovering');
}
function handleFileDragOver(e) {
e.stopPropagation();
e.preventDefault();
e.dataTransfer.dropEffect = 'copy';
}
function handleFileDrop(e) {
e.stopPropagation();
e.preventDefault();
this.classList.remove('hovering');
var files = e.dataTransfer.files;
var outputStr = [];
for (var i = 0, f; f = files[i]; i++) {
var lastModified = f.lastModifiedDate;
var lastModifiedStr = lastModified ? lastModified.toLocaleDateString() + ' ' + lastModified.toLocaleTimeString()
: 'n/a';
outputStr += '<li><strong>' + f.name + '</strong> ('
+ (f.type || 'n/a') + ')<br>大小:' + f.size
+ '字节<br>修改时间:' + lastModifiedStr + '</li>';
}
output.innerHTML = '<ul>' + outputStr + '</ul>';
}
dropZone.addEventListener('dragenter', handleFileDragEnter, false);
dropZone.addEventListener('dragleave', handleFileDragLeave, false);
dropZone.addEventListener('dragover', handleFileDragOver, false);
dropZone.addEventListener('drop', handleFileDrop, false);
}
</script>
SQL语句
根据设计数据库需要三条语句,一条用用户输入的cname判断数据是否存在的语句,一条是不存在之后写入的语句,一条是即使存在,用户依然强行写入的语句
SQL-用用户输入的cname判断数据是否存在
简易版
SELECT EXISTS(SELECT 1 FROM compound WHERE cname="h2o;; water")
可以看到因为数据库数据cname有多个值的原因,检索需要解决复杂检索的问题,只要其中一个存在且完全匹配才算检索到,检索之后返回cname实际值,方便强行写入的时候覆盖
所以根据设计我们使用模糊匹配,但是匹配是字符串的前后不可以出现字母或数字
多值匹配
like的三个情况
SELECT * FROM compound WHERE cname like '%;_water'
SELECT * FROM compound WHERE cname like 'h2o;%'
SELECT * FROM compound WHERE cname like 'H2O;; Water'
所以最后把三个语句结合
SELECT cname FROM compound WHERE cname like (#{c1}) or (#{c2}) or (#{cname})
c1=(’%;’+#{cname})
c2=(#{cname}+’;%’)
这个实际操作里的写法是在service层传的时候实现的
此时返回的就是cname键值了
之后看正则的三个情况
https://www.runoob.com/mysql/mysql-regexp.html
https://blog.csdn.net/qq_42231574/article/details/85253440
SELECT cname FROM compound WHERE cname regexp '^h2o;'
SELECT cname FROM compound WHERE cname regexp ';[^\w]water'
SELECT cname FROM compound WHERE cname regexp '^H2O;; Water$'
最后
SELECT cname FROM compound WHERE cname regexp '^H2O;; Water$' or '^h2o;' or ';[^\w]water'
最后使用
SELECT EXISTS(SELECT 1 FROM compound WHERE cname regexp '^H2O;; Water$' or '^h2o;' or ';[^\w]water')
只判断是否存在
或者
SELECT id FROM compound WHERE cname regexp '^H2O;; Water$' or '^h2o;' or ';[^\w]water'
不存在的时候–插入语句
insert into compound (cid,cname) values ('B00001','k')
存在的时候–更新语句
update compound set cid='B00001' where id=1
后端实现
先是根据搜索条件返回id的url接口
查询是否存在并返回id的语句
写的时候发现mvc的SQL里好像没有正则,所以还是使用模糊匹配
xml文件
<!-- 手动上传化合物-->
<select id="ifCompoundExit" resultType="Compound" parameterType="String">
SELECT * FROM compound WHERE (cname like #{c0}) or (cname like #{c1}) or (cname like #{c2})
</select>
<select id="insertCompound">
insert into compound (cname,cid,formula,OtherDBs,cweight,chname,spare) values (#{cname},#{cid},#{formula},#{otherDBs},#{cweight},#{chname},#{spare})
</select>
dao文件
// manual_upload
List<Compound> ifCompoundExit(@Param("c0")String c0,@Param("c1")String c1,@Param("c2")String c2);
void insertCompound(Compound compound);
service文件
让搜索到结果的时候返回id值,搜索不到的时候执行插入数据的任务,成功返回success,失败返回失败信息
// manual_upload
public String ifCompoundExit(String cname,String cid,String formula,String otherDBs,String cweight,String chname,String spare){
String c1=cname+";%";
String c2="%;_"+cname;
String r="";
List<Compound> list =HqDao.ifCompoundExit(cname,c1,c2);
if(list.size()!=0){
Compound compound= list.get(0);
int id=compound.getId();
// JOptionPane.showMessageDialog(null, compound);
r= String.valueOf(id);
}else {
try{
Compound newCompound=new Compound();
newCompound.setCname(cname);
newCompound.setCid(cid);
newCompound.setFormula(formula);
newCompound.setOtherDBs(otherDBs);
newCompound.setCweight(cweight);
newCompound.setChname(chname);
newCompound.setSpare(spare);
HqDao.insertCompound(newCompound);
r="success";
}catch (Exception e){
r="err:"+e;
}
}
return r;
}
controller文件
// manual_upload
@ResponseBody
@RequestMapping(value = "ifCompoundExit.do", method = RequestMethod.GET, produces = "text/html; charset=UTF-8")
public String ifCompoundExit(String cname,String cid,String formula,String otherDBs,String cweight,String chname,String spare){
JSONObject object = new JSONObject();
String r=hqService.ifCompoundExit(cname,cid,formula,otherDBs,cweight,chname,spare);
object.put("id",r);
return object.toJSONString();
}
测试结果
之后是
前端的onclick方法和ajax方法添加
<button id="upload" onclick="uploadCompound()" style="background:cornflowerblue;color:white;left:0%;width:100%;height:15%;border:0">上传化合物数据</button>
使用ajax之前别忘了先引入jquery
<script src="https://code.jquery.com/jquery-3.0.0.min.js"></script>
function ajaxupload(cname,cid="",formula,otherDBs,cweight,chname,spare){
var result;
$.ajaxSettings.async = false;
$.ajax({
type:"get",
url :"ifCompoundExit.do",
data : {cname:cname,cid:cid,formula:formula,otherDBs:otherDBs,cweight:cweight,chname:chname,spare:spare},
success :function(data) {
var r= $.parseJSON(data).id;
result=r;
},
error :function(e) {
layer.msg('An unknown error occurred !',{time:500});
result=e;
}
});
return result;
}
function uploadCompound(){
var cname=document.getElementById("cname").value;
var formula=document.getElementById("formula").value;
var otherDBs=document.getElementById("otherDBs").value;
var cweight=document.getElementById("cweight").value;
var chname=document.getElementById("chname").value;
var spare=document.getElementById("spare").value;
var r1=-1;
var r=ajaxupload(cname,formula,otherDBs,cweight,chname,spare);
console.log(r.toString());
if(r=="success"){
alert("上传化合物成功");
}else if(r.toString()!="NaN"){
// alert("化合物已经存在,是否更新化合物内容");
var ifupdate=window.confirm("化合物已经存在,是否更新化合物内容");
console.log(ifupdate);
if(ifupdate){
alert("开始更新");
}
r1= r.toString();
}else {
alert("上传化合物失败--"+r);
}
// alert(r1);
return r1;
}
接下来处理在化合物已经存在的情况下upload数据的问题
可以看到已经存在在数据库的化合物的id已经得到
更新化合物
后端
先测试一下update的sql语句
xml
<select id="updateCompound">
update compound set cname=#{cname},formula=#{formula},otherDBs=#{otherDBs},cweight=#{cweight},chname=#{chname},spare=#{spare} where id=#{id}
</select>
dao
void updateCompound(Compound compound);
service
public String updateCompound(String cname,int id,String formula,String otherDBs,String cweight,String chname,String spare){
String r="";
try{
Compound newCompound=new Compound();
newCompound.setCname(cname);
newCompound.setId(id);
newCompound.setFormula(formula);
newCompound.setOtherDBs(otherDBs);
newCompound.setCweight(cweight);
newCompound.setChname(chname);
newCompound.setSpare(spare);
HqDao.updateCompound(newCompound);
r="success";
}catch (Exception e){
r="err:"+e;
}
return r;
}
controller
@ResponseBody
@RequestMapping(value = "updateCompound.do", method = RequestMethod.GET, produces = "text/html; charset=UTF-8")
public String updateCompound(String cname,int id,String formula,String otherDBs,String cweight,String chname,String spare){
JSONObject object = new JSONObject();
String r=hqService.updateCompound(cname,id,formula,otherDBs,cweight,chname,spare);
object.put("ifsuccess",r);
return object.toJSONString();
}
前端
function ajaxupdate(cname,id,formula,otherDBs,cweight,chname,spare){
var result;
$.ajaxSettings.async = false;
$.ajax({
type:"get",
url :"updateCompound.do",
data : {cname:cname,id:id,formula:formula,otherDBs:otherDBs,cweight:cweight,chname:chname,spare:spare},
success :function(data) {
var r= $.parseJSON(data).ifsuccess;
result=r;
},
error :function(e) {
layer.msg('An unknown error occurred !',{time:500});
result=e;
}
});
return result;
}
function uploadCompound(){
var cname=document.getElementById("cname").value;
var formula=document.getElementById("formula").value;
var otherDBs=document.getElementById("otherDBs").value;
var cweight=document.getElementById("cweight").value;
var chname=document.getElementById("chname").value;
var spare=document.getElementById("spare").value;
var r1=-1;
var r=ajaxupload(cname,formula,otherDBs,cweight,chname,spare);
console.log(r.toString());
if(r=="success"){
alert("上传化合物成功");
}else if(r.toString()!="NaN"){
// alert("化合物已经存在,是否更新化合物内容");
var ifupdate=window.confirm("化合物已经存在,是否更新化合物内容");
console.log(ifupdate);
if(ifupdate){
alert("开始更新");
}
r1= r.toString();
var id=r1;
var r_up=ajaxupdate(cname,id,formula,otherDBs,cweight,chname,spare);
console.log(r_up);
if(r_up=="success"){
alert("更新化合物信息成功");
}else {
alert("更新化合物失败");
}
}else {
alert("上传化合物失败--"+r);
}
// alert(r1);
return r1;
}