JAVA 控制台 数据库 物流表 增删改查 DBUtil工具类

DBUtil:

//DBUtil.java
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.mysql.jdbc.ResultSetMetaData;

public class DBUtil {
	
	private Connection conn;
	private PreparedStatement preStmt;
	private ResultSet rs;

	public static Connection getConnection() throws SQLException {
		Properties p=new Properties();
		File file=new File("config"+File.separator+"mysql.properties");
		try {
			p.load(new FileInputStream(file));
			String user=p.getProperty("user");
			String password=p.getProperty("password");
			String driver=p.getProperty("driver");
			String url=p.getProperty("url");
			
			Class.forName(driver);
			
			return DriverManager.getConnection(url, user, password);
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new SQLException("加载配置文件失败!");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new SQLException("数据库驱动程序未找到!");
		}
		
	}
	
	
	public void closeAll() {
		try {
			if(null!=rs)
				rs.close();
			if(null!=preStmt)
				preStmt.close();
			if(null!=conn)
				conn.close();
			
		}catch(Exception ex) {
			ex.printStackTrace();
		}
	}
	
	public ResultSet executeQuery(String sql, Object...params)throws Exception
	{
		conn=this.getConnection();
		if(null==sql)
			return null;
		
		preStmt=conn.prepareStatement(sql);
		if(null!=params && params.length>0) {
			for(int i=0;i<params.length;i++) {
				preStmt.setObject(i+1, params[i]);
			}
		}
		
		rs=preStmt.executeQuery();
		
		return rs;
	}
	

	
	public int executeUpdate(String sql, Object...params)throws SQLException
	{
		
		int result=0;
		
		
		
		if(null==sql)
			return 0;
		
		conn=this.getConnection();
		preStmt=conn.prepareStatement(sql);
		
		if(null!=params && params.length>0) {
			for(int i=0;i<params.length;i++) {
				preStmt.setObject(i+1, params[i]);
			}
		}
		
		result=preStmt.executeUpdate();
		
		return result;
	}
	
	
}

 Handle.java

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;

import javax.swing.plaf.synth.SynthSeparatorUI;

import com.mysql.jdbc.ResultSetMetaData;



/*
 *  Map集合中提供了get() 获取元素的方法， get()对应一个键取出其对应 的值，这种方式比较局限和单一，不能全部取出来 。
 *  要取出所有的元素（值），则必须要拿到所有的键，然后才能取到所有与其对应的值，针对这种新的需求，Java中提供了相应的解决方案
 *  
 *  方法是： keySet()   :返回值类型Set<k>    返回此映射中包含的键的 Set 视图
                               将map中所有的键存入到Set集合，因为set具备迭代器，所有迭代方式取出所有的键
                               再根据get()方法  ，获取每一个键对应的值
 *先获取map集合的所有键的set集合，keySet();
 *       Set<String> k = map.keySet(); 
 *	     Set<String>相当于返回值类型，此相当于Set集合加上了泛型，类型为String,k相当于变量名
 *       有了Set集合，就可以获取其迭代器.（注意Set集合的类型要和迭代器保持一致）
 *       Iterator<String> it = k.iterator(); 
 *      
 *       while(it.hasNext()){
 *          String key = it.next();
 *       有了键，就可以通过map集合的get方法获取对应的值
 *          String value =map.get(key);
 *          System.out.println("key:"+key+"---value:"+value);
 *      }

 */


public class Handle {
	DBUtil db=new DBUtil();
	Scanner sc=new Scanner(System.in);
	
	private List convertList(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub

		List list = new ArrayList();
		ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
		int columnCount = md.getColumnCount();
		while (rs.next()) {
			Map rowData = new HashMap();
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}
	
	
	public void query() throws Exception {
		System.out.println("1：查询全部");
		System.out.println("2：根据物流Id查询");
		System.out.println("选择你要执行选项");
		int type2=sc.nextInt();
		switch(type2) {
		case 1:
			String Sql1="select id,"
					+ "time,address,type,handler,receiver,"
					+ "transporttype from transport0010 where 1=?";
			Object[] array1= {1};
			ResultSet rs=db.executeQuery(Sql1, array1);
			List list=convertList(rs);
			Map map2=(Map)list.get(0);
			Set set2=map2.keySet();
			Iterator it2=set2.iterator();
			while(it2.hasNext()) {
				System.out.println(it2.next());
			}
			System.out.println();
			
			for(Object object:list) {
				Map map=(Map) object;
				Set set=map.keySet();
				Iterator it=set.iterator();
				
				while(it.hasNext()) {
					Object key=it.next();
					System.out.println(map.get(key));
				}
				System.out.println();
			}
			break;
			
		case 2:
			System.out.println("输入物流id:");
			Object object=sc.nextInt();
			Object[] array= {object};
			String sql2="select id,"
					+ "time,address,type,handler,receiver,"
					+ "transporttype from transport0010 where id=?";
			
			ResultSet rs1=db.executeQuery(sql2, array);
			List list1=convertList(rs1);
			Map map3=(Map) list1.get(0);
			Set set3=map3.keySet();
			Iterator it3=set3.iterator();
			
			while(it3.hasNext()) {
				System.out.println(it3.next());
			}
			System.out.println();
			
			for(Object object2:list1) {
				Map map4=(Map) object2;
				Set set4=map4.keySet();
				Iterator it4=set4.iterator();
				
				while(it4.hasNext()) {
					Object key=it4.next();
					System.out.println(map4.get(key));
				}
				System.out.println();
			}
			break;
		}
	}

	public void update() throws SQLException {
		System.out.println("请输入想要修改的物流id:");
		Object id=sc.next();
		System.out.println("请输入type");
		Object type=sc.next();
		
		
		
		String sql="update transport0010 set type=?"
				+"where id=?";
		Object[] array= {type,id};
		int line=db.executeUpdate(sql, array);
		if(line>0) {
			System.out.println("信息更新成功");
		}
	}
	
	public void insert() throws SQLException {
		System.out.println("请输入物流Id");
		Object id=sc.next();
		System.out.println("请输入time");
		Object time=sc.next();
		System.out.println("请输入address");
		Object address=sc.next();
		System.out.println("请输入type");
		Object type=sc.next();
		System.out.println("请输入handler");
		Object handler=sc.next();
		System.out.println("请输入receiver");
		Object receiver=sc.next();
		System.out.println("请输入transporttype");
		Object transporttype=sc.next();
		
		Object[] array= {id,time,address,type,handler,receiver,
				transporttype};
		String sql="insert into transport0010 values(?,?,?,?,?,?,?)";
		int line=db.executeUpdate(sql, array);
		if(line>0) {
			System.out.println("插入成功");
		}
	}
	
	public void delete() throws SQLException {
		System.out.println("请输入想删除的物流号");
		Object id=sc.next();
		Object[] array= {id};
		String sql="delete from transport0010 where id=?";
		int line=db.executeUpdate(sql, array);
		if(line>0) {
			System.out.println("删除成功");
		}
	}
	
}

Test02.java

import java.util.Scanner;

public class Test02 {
	public static void main(String args[]) throws Exception {
		while(true) {
		System.out.println("欢迎光临");
		System.out.println("1:查询");
		System.out.println("2:更新");
		System.out.println("3：插入");
	    System.out.println("4：删除");
	    System.out.println("5：退出"+"\n");
	    System.out.print("请选择：");
		
		Scanner sc=new Scanner(System.in);
		Handle hd=new Handle();//实例化操作类 handle
		int type=sc.nextInt();
		
		switch(type) {
		case 1:
			hd.query();
			break;
		case 2:
			hd.update();
			break;
		case 3:
			hd.insert();
			break;
		case 4:
			hd.delete();
			break;
		case 5:
			System.out.println("程序退出");
			System.exit(0);
			break;
		}
	}
	}
}