本帖最后由 liu871113 于 2016-2-1 09:33 编辑 package com.fr.data; import com.fr.data.DefinedSubmitJob; import com.fr.data.JobValue; import com.fr.script.Calculator; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class DemoSubmitJob1 extends DefinedSubmitJob { /** * 当模板自定义事件增加的属性 名称与下面变量有对应时,则会自动赋值于此对应变量 */ static final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=EasyERP"; // Database credentials static final String USER = "sa"; static final String PASS = "Hammsen1"; private JobValue ItemID; // 物料ID private JobValue shelfID;//货架ID private JobValue InStore;//单元格中入库数量 private int quantityInStore;// 非单元格,则对应具体类型值 private int quantityOnBoard; private int BatchID; /** * 每一条记录执行一次此方法 * 同一提交事件在一个处理事务内,此对象是唯一的 */ public void doJob(Calculator calculator) throws Exception { Connection conn = null; Statement stmt = null; //STEP 2: Register JDBC driver Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //STEP 3: Open a connection // System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 4: Execute a query //System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql; sql = "select quantityInStore from MM_Storage where ItemID ='" + ItemID.getValue() + "' AND shelfID ='" + shelfID.getValue() + "' "; ResultSet rs = stmt.executeQuery(sql); if(rs.next()){ int Num = rs.getInt(1); if(Num==0){ stmt.executeUpdate("insert into MM_Storage(ItemID,shelfID,quantityInStore) values("+ItemID.getValue()+","+shelfID.getValue()+","+InStore.getValue()+")"); }else{ quantityInStore = Num+InStore.getValue(); stmt.executeUpdate("update MM_Storage set quantityInStore ="+quantityInStore+" "+ "where ItemID ="+ItemID.getValue()+"AND"+"shelfID = "+shelfID.getValue()); } } rs.close(); stmt.close(); conn.close(); } } |