早在一个月前就写了一个webservice接口,让VB程序员调用,刚开始写就一个插入语句,可是随着客户的要求,以及数据库设计的问题,还有数据问题,等诸多问题,我几乎是每隔3天老板都让该一下接口,以符合VB程序的调用。
代码如下
public String getLastId() {
tbl_waste_disposal_info
String SQL = "SELECT id FROMORDER BY id DESC LIMIT 1";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = ResourceManager.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(SQL);
String id="";
while (rs.next()) {
id= rs.getString("id");
}
return id;
} catch (SQLEXCEPTION e) {
e.printStackTrace();
return "";
} finally {
ResourceManager.close(rs);
ResourceManager.close(stmt);
ResourceManager.close(conn);
}
}
刚开始是这样写的,可是,等数据多了就不是这样的了,最后的一个id是9,因为mysql数据库排序的id是varchar类型,所以不能像数字排序那样,头疼,又该了代码,代码如下:
ArrayList<Integer> idList = new ArrayList<Integer>();
public int getAllIdToInteger() {
String sql = "SELECT id FROM tbl_waste_disposal_info;";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = ResourceManager.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
String id = rs.getString("id");
idList.add(Integer.parseInt(id));
}
return idList.size();
} catch (SQLException e) {
e.printStackTrace();
WriteLogUtil util=new WriteLogUtil();
String str=util.setTime(System.currentTimeMillis())+" 查找id时异常 "+e.toString();
util.writeSaveWeightUtil(str);
return 1;
} finally {
ResourceManager.close(rs);
ResourceManager.close(stmt);
ResourceManager.close(conn);
}
}
public String getLastId() {
int result = getAllIdToInteger();
if (result == 0) {
return 1 + "";
} else if (result == 1) {
System.out.println("id错误");
Object[] array = idList.toArray();
Arrays.sort(array);
List<Object> asList = Arrays.asList(array);
Integer i = (Integer) asList.get(asList.size() - 1);
i = i + 1;
return i + "";
} else {
Object[] array = idList.toArray();
Arrays.sort(array);
List<Object> asList = Arrays.asList(array);
Integer i = (Integer) asList.get(asList.size() - 1);
i = i + 1;
return i + "";
}
}
代码该成如此的后,运行了十天左右,问题又出来了,VB程序分布在不同的机构里面运行,同时调用这个接口,结果造成安全问题。
问题描述:向数据库中插入数据的时候,是先进行其他操作的,第一个操作是根据传进来的车牌号查找系统是是否又该车,如果有找到该车的部门id,还有该车的设备号,然后再根据订单号还有垃圾场判断,该垃圾场的该订单号是否已经存在。由于有上两步操作,造成数据不统一。就是当查到该车的部门id的时候,还没有来的及插入数据库,又有程序调用该接口来查询又一个车牌号的部门id,这样部门id就改变了。
最后我决定了接口方法用了synchronized关键字。但是效率可能会降低,但是安全了。
这里有一篇博客讲的synchronized很详细,有需要的可以看看:
全部代码如下:
package cn.com.dekn.client.webservice.tblsavevehicleweight.service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import javax.swing.plaf.basic.BasicScrollPaneUI.VSBChangeListener;
import org.json.JSONArray;
import org.json.JSONObject;
import cn.com.dekn.b2c.util.JsonUtil;
import cn.com.dekn.b2c.util.ResourceManager;
import cn.com.dekn.client.webservice.fileupload.service.FileUploadImpl;
import cn.com.dekn.client.webservice.tblsavevehicleweight.util.WriteLogUtil;
import cn.com.dekn.common.exception.DataAccessException;
import cn.com.dekn.common.util.DateUtil;
import cn.com.dekn.common.util.StrUtil;
public class SaveVehicleWeightImpl implements SaveVehicleWeigthService {
// public String devID;
// public String departmentid;
@Override
public String vbSaveVehicleWeight(String elements, byte[] inimg,
byte[] inimg2, byte[] outimg, byte[] outimg2)
throws DataAccessException {
try {
elements = new String(elements.getBytes(), "UTF-8");
} catch (Exception e) {
}
String orderno = "";//
String license_plate_number = "";//
String card_number = "";//
String dumpname = "";//
String departmentid = "";//
String source = "";//
String driver = "";//
String inpeople_num = "";//
String intime = "";//
String gross_weight = "";//
String outpeople_num = "";//
String outtime = "";//
String tare_weight = "";//
String net_weight = "";//
String weighman = "";//
String weighman2 = "";//
String content = "";//
String devID="";//设备id,根据sql查找出来的
synchronized (elements) {
String[] objects = elements.split("#\\|#");
System.out.println("objects=" + Arrays.asList(objects));
for (int i = 0; i < objects.length; i++) {
String[] keyValues = objects[i].split(":\\|:");
System.out.println("key=" + Arrays.asList(objects));
if (keyValues[0].equals("orderno")) {
try {
orderno = keyValues[1];
} catch (Exception e) {
orderno = "";
}
continue;
}
if (keyValues[0].equals("license_plate_number")) {
try {
license_plate_number = keyValues[1];
} catch (Exception e) {
license_plate_number = "";
}
continue;
}
if (keyValues[0].equals("card_number")) {
try {
card_number = keyValues[1];
} catch (Exception e) {
card_number = "";
}
continue