使用原生的jdbc 操作数据库实现增删改查

墨蓝 2022-01-29 22:39 225阅读 0赞

**Test1**

public class StockBillAndProduct \{  
@SuppressWarnings(\{ “unchecked” \})  
public static void main(String\[\] args) \{  
Connection connection = null;  
PreparedStatement preparedStatement = null;  
PreparedStatement preparedStatement2 = null;  
PreparedStatement preparedStatement3 = null;  
ResultSet rs2=null;  
ResultSet resultSet = null;  
List list = new ArrayList();  
try \{  
// 加载数据库驱动  
Class.forName(“com.mysql.jdbc.Driver”);  
// 通过驱动管理类获取数据库链接  
connection = (Connection) DriverManager.getConnection(  
“jdbc:mysql://114.116.148.197:3306/天地壹号?useSSL=false”,  
“root”, “Zhou572162677??”);

String  sql1="insert into stock_bill(code,source_type,create_time,terminal_id) values(?,?,?,?)";
    			String sql3 = "select * from _out_statistic_709 ";
    			connection.setAutoCommit(false);
    			preparedStatement = (PreparedStatement) connection
    					.prepareStatement(sql3);
    			ResultSet rs = preparedStatement.executeQuery();
    			while (rs.next()) {
    				
    					String usercode=rs.getString("_usercode");
    					int outType=rs.getInt("_out_type");
    					Date createTime=rs.getDate("_create_time");
    					int  terminalId=rs.getInt("_terminal_id");
    					
    					preparedStatement3 = (PreparedStatement) connection.prepareStatement(sql1,Statement.RETURN_GENERATED_KEYS);
    					
    					preparedStatement3.setString(1,usercode); // 用的是天地壹号表的productId
    					preparedStatement3.setInt(2, outType);//计划
    					preparedStatement3.setDate(3, createTime);//创建时间
    					preparedStatement3.setInt(4, terminalId);//success
    					int rows = preparedStatement3.executeUpdate();
    					rs2=preparedStatement3.getGeneratedKeys();
    					rs2.next();
    					System.out.println(rs2.getInt(1));
    					if (rows > 0) {
    						
    				String sql2 = "insert into stock_product_detail(bill_id,product_id,plan_single,actual_single,create_time,status) values(?,?,?,?,?,?)";
    						String sql4 = "select * from _out_statistic_709 ";
    						preparedStatement = (PreparedStatement) connection.prepareStatement(sql4);
    						ResultSet rss = preparedStatement.executeQuery();
    						while (rss.next()) {
    							
    								int productId1=rss.getInt("_product_id");
    								int planSingle=rss.getInt("_success_num");
    								Date createTime2=rss.getDate("_create_time");
    							
    							preparedStatement = (PreparedStatement) connection.prepareStatement(sql2);
    							preparedStatement.setInt(1, rs2.getInt(1));
    							preparedStatement.setInt(2,productId1); // 用的是天地壹号表的productId
    							preparedStatement.setInt(3, planSingle);//计划
    							preparedStatement.setInt(4, planSingle);//success
    							preparedStatement.setDate(6, createTime2);//创建时间
    							preparedStatement.setInt(6, 1);//状态
    							int rows1 = preparedStatement.executeUpdate();
    							if (rows1 > 0) {
    								System.out.println("operate successfully!");
    							} else {
    								System.out.println("失败");
    							}
    								}
    
    							
    						System.out.println("导入数据到bill表成功");
    					} else {
    						System.out.println("失败");
    					}
    					
    				}
    				
    
    					
    				
    	} catch (Exception e) {
    		try {
    			connection.rollback();
    		} catch (SQLException e1) {
    			// TODO Auto-generated catch block
    			e1.printStackTrace();
    		}
    		e.printStackTrace();
    	} finally {
    		// 释放资源
    		if (resultSet != null) {
    			try {
    				resultSet.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if (preparedStatement != null) {
    			try {
    				preparedStatement.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if (connection != null) {
    			try {
    				connection.commit();
    				connection.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    
    	}
    
    }

**Test2**  
public class StockCodeDetail \{  
private static final Logger logger = LoggerFactory.getLogger(StockCodeDetail.class);  
@SuppressWarnings(\{ “unchecked” \})  
public static void main(String\[\] args) \{  
List list = new ArrayList();  
List list2 = new ArrayList();  
Connection connection = null;  
PreparedStatement preparedStatement = null;  
PreparedStatement preparedStatement2 = null;  
ResultSet resultSet = null;  
try \{  
Class.forName(“com.mysql.jdbc.Driver”).newInstance();  
// mysql驱动  
connection = (Connection) DriverManager.getConnection(  
“jdbc:mysql://114.116.148.197:3306/天地壹号?useSSL=false”,  
“root”, “Zhou572162677??”);  
connection.setAutoCommit(false);  
Statement ps = (Statement) connection.createStatement();  
String sql = “select \* from \_out\_statistic\_709”;  
ResultSet rs = ps.executeQuery(sql);  
while (rs.next()) \{  
// 从数据库读取到的url  
String filePath = rs.getString("\_zip\_url");  
list.add(filePath);  
Date createTime = rs.getDate("\_create\_time");  
int terminal\_id = rs.getInt("\_terminal\_id");

for (int i = 0; i < list.size(); i++) {
    				String test = list.get(i).toString();
    				int one = test.lastIndexOf("/");
    				String s1 = test.substring((one + 1), test.length()); // 最后一个/后的内容
    				String str1 = s1.substring(0, s1.indexOf("."));// 截取. 的字符串
    				// 根据url 截取出来的o1002-20151214133947-qf3560l0 去查 盘符里的压缩包
    				String s2 = str1;
    				File file = new File("D:\\aaaaaaaaaaaaaaaaaaaaaaa" + "\\"
    						+ s2 + ".zip");
    				ZipFile zf = new ZipFile(file);
    				InputStream in = new BufferedInputStream(
    						new FileInputStream(file));
    				ZipInputStream zin = new ZipInputStream(in);
    				ZipEntry ze;
    				while ((ze = zin.getNextEntry()) != null) {
    					if (ze.isDirectory()) {
    					} else {
    						long size = ze.getSize();
    						if (size > 0) {
    							BufferedReader br = new BufferedReader(
    									new InputStreamReader(
    											zf.getInputStream(ze)));
    							String line;
    							while ((line = br.readLine()) != null) {
    								int pp = line.lastIndexOf(",");
    								// 最后一个,后的内容
    								String ss = line.substring((pp + 1),line.length()); 
    								// 截取第一个分号之前的字符串
    								String str12 = line.substring(0,line.indexOf(";"));
    				
    								// _barcode_b_level_709_1552表
    								// 如果最后一个.后面是1 截取最前面的数字去查		
    								if(ss.length() == 1) {
    									
    									// 定义sql语句 ?表示占位符
    					
    										//System.out.println(list2.get(j));
    										//去_barcode_b_level_709_1552表查 _encrypt_barcode_b_level=list2.getJ的数据
    										String sqlBbl = "select * from _barcode_b_level_709_1552 where _encrypt_barcode_b_level=? ";
    										preparedStatement2 = (PreparedStatement) connection.prepareStatement(sqlBbl);
    										preparedStatement2.setString(1,str12);
    										resultSet = preparedStatement2.executeQuery();
    										while (resultSet.next()) {
    											String file1 = resultSet.getString("_encrypt_barcode_b_level");
    											//productId  读的是_barcode_b_level_709_1552表的productId
    											int productDetail=resultSet.getInt("_product_id");
    											String sql2 = "insert into stock_code_detail(qr_code,level,status,create_time,terminal_id,product_detail) values(?,?,?,?,?,?)";
    													
    													preparedStatement = (PreparedStatement) connection
    															.prepareStatement(sql2);
    													preparedStatement.setString(1, file1);
    													preparedStatement.setInt(2, 2);
    													preparedStatement.setInt(3, 1);
    													preparedStatement.setDate(4, createTime);
    													preparedStatement.setInt(5, terminal_id);
    													 preparedStatement.setInt(6,productDetail); // 用的是天地壹号表的productId
    
    													int rows = preparedStatement.executeUpdate();
    													if (rows > 0) {
    														System.out.println("跺码数据的导入成功!");
    													} else {
    														System.out.println("失败");
    													}
    												
    												
    											}
    
    			
    
    								} else {
    									
    								String sql2 = "insert into stock_code_detail(qr_code,level,status,create_time,terminal_id,product_detail) values(?,?,?,?,?,?)";
    									String sql3 = "select * from _out_statistic_709 where _zip_url = ?";
    									preparedStatement = (PreparedStatement) connection
    											.prepareStatement(sql3);
    									preparedStatement.setString(1,filePath );
    									ResultSet rsL = preparedStatement.executeQuery();
    									while (rsL.next()) {
    										//查询productID
    											String zUrl=rsL.getString("_zip_url");
    											String productDetail2=rsL.getString("_product_id");
    											preparedStatement = (PreparedStatement) connection
    													.prepareStatement(sql2);
    											preparedStatement.setString(1, zUrl);
    											preparedStatement.setInt(2, 2);
    											preparedStatement.setInt(3, 1);
    											preparedStatement.setDate(4, createTime);
    											preparedStatement.setInt(5, terminal_id);
    											 用的是天地壹号表_out_statistic_709的productId
    											 preparedStatement.setString(6,productDetail2); 
    
    											int rows = preparedStatement.executeUpdate();
    											if (rows > 0) {
    												System.out.println("箱码也导入成功");
    											} else {
    												System.out.println("失败");
    											}
    										}
    								}
    
    							}
    							br.close();
    						}
    						System.out.println();
    					}
    				}
    				zin.closeEntry();
    
    			}
    		}
    		connection.commit();
    	} catch (Exception e) {
    		try {
    			connection.rollback();
    		} catch (SQLException e1) {
    			// TODO Auto-generated catch block
    			e1.printStackTrace();
    		}
    		logger.error("异常信息为:", e);
    
    	}finally{
    		
    		try {
    			connection.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    
    }

**CREATE TABLE `_barcode_b_level_709_1552` (**  
`_encrypt_barcode_b_level` varchar(30) NOT NULL COMMENT ‘二级码内容’,  
`_enterprise_id` int(11) DEFAULT NULL COMMENT ‘企业ID’,  
`_product_id` int(11) DEFAULT NULL COMMENT ‘产品ID’,  
`_encrypt_barcode_c_level` varchar(30) DEFAULT NULL COMMENT ‘三级码内容’,  
`_barcode_section_info_id` bigint(20) DEFAULT NULL COMMENT ‘子码段ID’,  
`_production_plan_code` varchar(50) DEFAULT NULL COMMENT ‘生产计划编号’,  
`_validation_code` varchar(30) DEFAULT NULL COMMENT ‘明码内容’,  
`_barcode` varchar(4) DEFAULT NULL COMMENT ‘涂层码内容’,  
`_barcode_a_level_number` int(11) DEFAULT NULL COMMENT ‘当前一级码个数’,  
`_validation_number` int(11) DEFAULT NULL COMMENT ‘已验证次数’,  
`_is_integral` int(11) DEFAULT NULL COMMENT ‘积分标识符’,  
`_first_validation_time` datetime DEFAULT NULL COMMENT ‘首次验证时间’,  
`_last_validation_time` datetime DEFAULT NULL COMMENT ‘最后验证时间’,  
`_continue_validation_number` int(11) DEFAULT NULL COMMENT ‘连续验证次数，根据最后验证时间，如两次验证不超过60秒，则累计连续验证次数，当连续验证次数超过30次，锁定此码10分钟’,  
`_activate_status` int(11) DEFAULT NULL COMMENT ‘激活状态，1已激活，2未激活’,  
`_status` int(11) DEFAULT NULL COMMENT ‘码状态，-1作废，0生产，1入库，2调拨入库，3调拨出库，4出库，5激活，6销售’,  
`_create_time` datetime DEFAULT NULL COMMENT ‘创建时间’,  
`_warehouse_id` int(11) DEFAULT NULL,  
`_dealers_id` int(11) DEFAULT NULL,  
`_out_in_status` int(11) DEFAULT NULL,  
`_out_work_id` varchar(32) DEFAULT NULL,  
`_in_time` datetime DEFAULT NULL,  
`_out_time` datetime DEFAULT NULL,  
`_packing_time` datetime DEFAULT NULL,  
`_all_lowerbarcode` varchar(2048) DEFAULT NULL,  
`_layer_num` int(11) DEFAULT NULL COMMENT ‘层号’,  
`_machine_code` varchar(50) DEFAULT NULL COMMENT ‘设备编号’,  
`_channel_num` varchar(32) DEFAULT NULL COMMENT ‘通道号’,  
`_current_location_type` int(11) DEFAULT NULL COMMENT ‘当前位置类型’,  
`_current_location_id` int(11) DEFAULT NULL COMMENT ‘当前位置ID’,  
`_last_order_id` bigint(20) DEFAULT NULL COMMENT ‘最后订单ID’,  
`_confirm_status` int(11) DEFAULT NULL COMMENT ’ 确认状态0待确认1确认’,  
`_first_operate_time` datetime DEFAULT NULL,  
PRIMARY KEY (`_encrypt_barcode_b_level`),  
KEY `idx_dealers_id_status` (`_dealers_id`,`_status`),  
KEY `_index_encrypt_barcode_c_level` (`_encrypt_barcode_c_level`),  
KEY `_index_last_order_id` (`_last_order_id`),  
KEY `_barcode_a_level_number` (`_barcode_a_level_number`) USING BTREE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**CREATE TABLE `stock_product_detail` (**  
`id` int(11) NOT NULL AUTO\_INCREMENT,  
`bill_id` int(11) DEFAULT NULL COMMENT ‘单据主键’, stock \_bill表的主键  
`product_id` int(11) DEFAULT NULL COMMENT ‘产品ID’, productId out\_static  
`plan_pack` int(10) DEFAULT NULL COMMENT ‘计划包装数量’, 不填  
`plan_single` int(10) DEFAULT NULL COMMENT ‘计划单品数量’, 计划数量 也填成功的箱数就是 \_success\_num  
`actual_pack` int(10) DEFAULT NULL COMMENT ‘实际包装数量’, 不填  
`actual_single` int(10) DEFAULT NULL COMMENT ‘实际单品数量’, \_success\_num  
`create_time` datetime DEFAULT NULL COMMENT ‘创建时间’, out\_static 表的createTime  
`status` smallint(6) DEFAULT NULL COMMENT ‘状态’, 1  
PRIMARY KEY (`id`),  
KEY `index_bill_id` (`bill_id`)  
) ENGINE=InnoDB AUTO\_INCREMENT=220 DEFAULT CHARSET=utf8 COMMENT=‘出入库产品明细’;

**CREATE TABLE `stock_code_detail` (**  
`id` int(11) NOT NULL AUTO\_INCREMENT,  
`qr_code` varchar(128) DEFAULT NULL COMMENT ‘码内容’,  
`level` smallint(6) DEFAULT NULL COMMENT ‘码级别’,  
`product_detail` int(11) DEFAULT NULL COMMENT ‘产品明细ID’,  
`create_time` datetime DEFAULT NULL COMMENT ‘创建时间’,  
`status` smallint(6) DEFAULT NULL COMMENT ‘状态,-1-删除,0-停用,1-启用,2-异常’,  
`terminal_id` int(11) DEFAULT NULL COMMENT ‘终端ID’,  
PRIMARY KEY (`id`),  
KEY `index_product_detail` (`product_detail`)  
) ENGINE=InnoDB AUTO\_INCREMENT=7889 DEFAULT CHARSET=utf8 COMMENT=‘出入库码明细’;

**CREATE TABLE `stock_bill` (**  
`id` int(11) NOT NULL AUTO\_INCREMENT,  
`code` varchar(40) DEFAULT NULL COMMENT ‘单据号’,  
`source_code` varchar(64) DEFAULT NULL COMMENT ‘第三方单号’,  
`source` int(11) DEFAULT NULL COMMENT ‘出库方’,  
`source_type` varchar(10) DEFAULT NULL COMMENT ‘出库方类型’,  
`target` int(11) DEFAULT NULL COMMENT ‘入库方’,  
`target_type` varchar(10) DEFAULT NULL COMMENT ‘入库方类型’,  
`stock_type` int(11) DEFAULT NULL COMMENT ‘单据类型ID’,  
`relative_bill` varchar(40) DEFAULT NULL COMMENT ‘单据号’,  
`operation_time` datetime DEFAULT NULL COMMENT ‘操作时间’,  
`type` varchar(10) DEFAULT NULL COMMENT ‘类型’,  
`factory_id` int(11) DEFAULT NULL COMMENT ‘工厂ID’,  
`status` smallint(6) DEFAULT NULL COMMENT ‘状态,-1-删除,0-停用,1-启用’,  
`creator_id` int(11) DEFAULT NULL COMMENT ‘创建人’,  
`create_time` datetime DEFAULT NULL COMMENT ‘创建时间’,  
`remark` varchar(500) DEFAULT NULL COMMENT ‘备注’,  
`terminal_id` int(11) DEFAULT NULL COMMENT ‘终端ID’,  
`tracking_number` varchar(20) DEFAULT NULL COMMENT ‘快递单号’,  
`courier_company` varchar(100) DEFAULT NULL COMMENT ‘快递公司’,  
PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO\_INCREMENT=320 DEFAULT CHARSET=utf8 COMMENT=‘出入库单据’;

**CREATE TABLE `_out_statistic_709` (**  
`_out_statistic_id` bigint(20) NOT NULL AUTO\_INCREMENT,  
`_status` int(11) DEFAULT NULL COMMENT ‘状态（0待轮询，1处理完成，2处理失败，3处理中）’,  
`_zip_url` varchar(512) DEFAULT NULL COMMENT ‘zip包地址’,  
`_success_num` int(11) DEFAULT NULL COMMENT ‘成功’,  
`_fail_num` int(11) DEFAULT NULL COMMENT ‘失败数’,  
`_total_num` int(11) DEFAULT NULL COMMENT ‘总数’,  
`_create_time` datetime DEFAULT NULL COMMENT ‘创建时间’,  
`_results` int(11) DEFAULT NULL COMMENT ‘处理结果1成功，2失败’,  
`_results_text` varchar(512) DEFAULT NULL COMMENT ‘处理结果详情zip解析失败的时候记录’,  
`_terminal_id` int(11) DEFAULT NULL COMMENT ‘终端ID’,  
`_work_id` varchar(128) DEFAULT NULL COMMENT ‘工号ID’,  
`_running_water` varchar(36) DEFAULT NULL COMMENT ‘流水号’,  
`_operation` int(11) DEFAULT NULL COMMENT ‘已操作’,  
`_dealers_id` int(11) DEFAULT NULL COMMENT ‘经销商ID’,  
`_enterprise_id` int(11) DEFAULT NULL COMMENT ‘企业ID’,  
`_success_url` varchar(256) DEFAULT NULL COMMENT ‘处理成功文件url’,  
`_success_path` varchar(256) DEFAULT NULL COMMENT ‘处理成功文件path’,  
`_fail_url` varchar(256) DEFAULT NULL COMMENT ‘处理失败文件url’,  
`_fail_path` varchar(256) DEFAULT NULL COMMENT ‘处理失败文件path’,  
`_server_id` int(11) DEFAULT NULL,  
`_out_type` int(11) DEFAULT NULL COMMENT ‘null:无类型\\r\\n1： 销售出库\\r\\n2： 其他出库（部门内部领用、检验出库）\\r\\n3： 车间返工出库\\r\\n4： 不合格品出库\\r\\n5： 调拨出库’,  
`_usercode` varchar(20) DEFAULT NULL,  
`_username` varchar(20) DEFAULT NULL,  
`_pinfo` int(1) DEFAULT NULL,  
`_pinfo_sucess` int(11) DEFAULT NULL,  
`_pinfo_fail` int(11) DEFAULT NULL,  
`_success_individual` int(11) DEFAULT NULL,  
`_fail_individual` int(11) DEFAULT NULL,  
`_success_box` int(11) DEFAULT NULL,  
`_fail_box` int(11) DEFAULT NULL,  
`_product_id` int(11) DEFAULT NULL COMMENT ‘产品id’,  
PRIMARY KEY (`_out_statistic_id`)  
) ENGINE=InnoDB AUTO\_INCREMENT=11075 DEFAULT CHARSET=utf8;