java移库数据同步,库存调拨 java 实现方式

项目开发中客户提出一个要求：

我只选择商品和数量，完成商品的移库操作

在开发中发现这里商品又有批次的问题，那么计算成本的时候又需要根据批次来计算他的成本，那么这样就产生了几个问题

1.当某个商品的这个批次所对应的库存数量足够他调拨时的数量

2.当某个商品的这个批次不够的他需要调拨的数量，我就必须累加下一个批次的数量在这里又发现几种情况

2.1当两个批次正好够的情况

2.2 当两个批次大于他的情况

这里就涉及到要修改最后一条数据的库存数量

2.3 当两个批次还不够的情况

3.修改库存数量

如果调入仓库没有该批次的商品数量，我会插入一条新纪录

这些所有情况都需要调拨完成之后修改对应的库存信息

下面是具体代码

数据库结构

下面是实现代码

通用的JDBC

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importjava.sql.ResultSetMetaData;public classDBUtil {private static String url = "jdbc:jtds:sqlserver://192.168.1.182:1433/erp";private static String driverClass = "net.sourceforge.jtds.jdbc.Driver";private static String userName = "sa";private static String pwd = "admin";public static finalConnection getConn() {

Connection conn= null;try{

Class.forName(driverClass);

}catch(ClassNotFoundException e2) {//TODO Auto-generated catch block

e2.printStackTrace();

}try{

conn=DriverManager.getConnection(url, userName, pwd);

}catch(SQLException e1) {//TODO Auto-generated catch block

e1.printStackTrace();

}returnconn;

}public static void beginTransaction(Connection conn) throwsSQLException{if(conn == null||conn.isClosed()){throw new SQLException("数据库连接异常");

}

conn.setAutoCommit(false);

}public static void commit(Connection conn) throwsSQLException{if(conn == null||conn.isClosed()){throw new SQLException("数据库连接异常");

}

conn.commit();

conn.setAutoCommit(true);

}public static boolean update(Connection conn,String sql) throwsException{

Statement state=conn.createStatement();int bs =state.executeUpdate(sql);return bs>0;

}public static List>query(Connection conn,String sql ){

List> restList = null;try{

restList= new ArrayList>();

PreparedStatement prepState=conn.prepareStatement(sql);

ResultSet result=prepState.executeQuery();

ResultSetMetaData metaData=result.getMetaData();int colCount =metaData.getColumnCount();while(result.next()){

Map map = new HashMap();for(int i = 1 ; i<=colCount; i++){

String colName=metaData.getColumnName(i);

Object value=result.getObject(i);

map.put(colName,value);

}

restList.add(map);

}

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}returnrestList;

}

}

实现的主函数

packagecom;importjava.sql.Connection;importjava.util.List;importjava.util.Map;public classWzdpService {public static voidmain(String args[]){try{

WzdpService wzdp= newWzdpService();

wzdp.one(20,"a","y","x");

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}public void one(int sl,String spmc,String kfmc,String toKf) throwsException{int sysl = 0;//剩余物资数量

int dbsl = 0;//调拨数量//查询总库总数量

String sql1 = "select sum(sl) cnt from test where ck = ‘"+kfmc+"‘ and sp = ‘"+spmc+"‘ order by cnt desc ";

Connection conn=DBUtil.getConn();

List> result2 =DBUtil.query(conn, sql1);

Map spxx2 = result2.get(0);if(spxx2 == null){

System.out.println("无相关物品！");return;

}int csl = Integer.valueOf(spxx2.get("cnt").toString());//判断库存数量能否满足调拨数量

if(csl

System.out.println("物品数量不足");return;

}

String sql= "select id,sp,pc,ck,sl from test where ck = ‘" + kfmc + "‘ and sp = ‘"

+ spmc + "‘ order by sl desc ";

List> result =DBUtil.query(conn, sql);

Map spxx = result.get(0);if (spxx == null) {

System.out.println("无相关物品！");return;

}int zkmxsl = Integer.valueOf(spxx.get("sl").toString());if (zkmxsl == 0) {return;

}

String sp= (String) spxx.get("sp");

String pc= (String) spxx.get("pc");

String ck= (String) spxx.get("ck");

DBUtil.beginTransaction(conn);//1、库房单批次数量满足 mxsl>=sl mxsl - sl = nsl//2、库当批次数量不满足 mxsl

if (zkmxsl >=sl) {

dbsl=sl;

}else{

sysl= sl -zkmxsl;

dbsl=zkmxsl;

}//入库

String cxsql = "select id,sp,pc,ck,sl from test where ck = ‘" +toKf+ "‘ and sp = ‘" + sp + "‘ and pc = ‘" + pc + "‘";

List> result1 =DBUtil.query(conn, cxsql);if(result1.isEmpty()) {

String insert= "insert into test(SP,PC,CK,SL) values (‘" +sp+ "‘,‘" + pc + "‘,‘" + toKf + "‘,‘" + dbsl + "‘)";

DBUtil.update(conn, insert);

}else{

Map spxx1 = result1.get(0);int nsl = Integer.valueOf(spxx1.get("sl").toString());

nsl= nsl +dbsl;

String update= "update test set sl = " + nsl + " where ck = ‘"

+ toKf + "‘ and sp = ‘" + sp + "‘ and pc = ‘" + pc + "‘";

DBUtil.update(conn, update);

}//出库

dbsl = zkmxsl -sl;if (dbsl < 0) {

dbsl= 0;

}

String updateZk= "update test set sl = " + dbsl + " where ck = ‘"

+ kfmc + "‘ and sp = ‘" + sp + "‘ and pc = ‘" + pc + "‘";

DBUtil.update(conn, updateZk);

DBUtil.commit(conn);if (sysl <= 0) {return;

}

one(sysl, spmc, kfmc, toKf);

}

}