java连接数据库对两张表进行查询对比的小项目

下面是客户端的启动代码

public static void main(String[] args) throws UnknownHostException, IOException {
		// TODO Auto-generated method stub
		//获取配置文件
		Properties pro = new Properties();
		pro.load(new FileInputStream("freshbin/freshbinIP.properties"));
		
		//通过配置文件中的ip与端口port连接server服务端
		String ip = pro.getProperty("ip");
		int port = Integer.parseInt(pro.getProperty("port"));
		
		Socket s = new Socket(ip, port);
		
		//创建一个客户端的读与写对象
		ClientInputStream inputThread = new ClientInputStream(s);
		ClientOutputStream outputThread = new ClientOutputStream(s);
		
//		inputThread.setPriority(1);
//		outputThread.setPriority(10);
		//启动客户端读与写的线程
		inputThread.start();
		outputThread.start();
	}

下面是客户端发送类中的线程run方法的代码

		OutputStream os = null;
		Scanner sc = null;
		try {
			os = this.socket.getOutputStream();
			sc = new Scanner(System.in);

			while (true) {
				
				//发送请求信息(查询或者更新)
				String message = sc.nextLine();

				os.write(message.getBytes());
				os.flush();

下面是客户端接收类的线程的run方法的代码

InputStream is = null;
		try {
			is = this.socket.getInputStream();

			byte[] buffer = new byte[1024];
			int len = 0;
			String message = null;

				while (-1 != (len = is.read(buffer))) {
					
					//把从服务端接收到的信息打印在控制台
					message = new String(buffer, 0, len);
					String s = "服务器端：" + message;
					System.out.println(s);

下面是服务端的启动代码

public static void main(String[] args) throws IOException {
		
		//获取配置文件
		Properties pro = new Properties();
		pro.load(new FileInputStream("freshbin/freshbinIP.properties"));
		
		//通过配置文件中的ip与端口port连接server服务端
		int port = Integer.parseInt(pro.getProperty("port"));
		
		ServerSocket ss = new ServerSocket(port);
		
		//进行监听
		Socket s = ss.accept();
		
		//创建一个服务端的读与写对象
		ServerInputStream inputThread = new ServerInputStream(s);
		ServerOutputStream outputThread = new ServerOutputStream(s);
		
//		inputThread.setPriority(1);
//		outputThread.setPriority(10);
		//启动服务端的读与写线程
		outputThread.start();
		inputThread.start();

//		s.close();
//		ss.close();
	}

下面是服务发送端的run方法的代码

	JdbcCommit jc = new JdbcCommit();
		OutputStream os = null;
		Scanner sc = null;
		try {
			os = this.socket.getOutputStream();
			sc = new Scanner(System.in);

			//发送功能
			while (true) {
				//主要代码,其实服务端这个发送功能的类,暂时就只有下面这条语句有用...
				os.write(("请输入1或2(1为查询,2为更新):\n").getBytes());
				String message = sc.nextLine();

下面是服务端接收端的run方法

	//主要核心功能代码
	@Override
	public void run() {
		JdbcCommit jc = new JdbcCommit();
		OutputStream os = null;
		InputStream is = null;
		try {
			is = this.socket.getInputStream();
			os = this.socket.getOutputStream();
			byte[] buffer = new byte[1024];
			int len = 0;
			String message = null;
//			if(ClientMain.flag) {
				while (-1 != (len = is.read(buffer))) {
					
					//接收客户端发送的数据,打印在控制台
					message = new String(buffer, 0, len);
					String s = "客户端：" + message;
					System.out.println(s);
					
					//当客户端发送的数据为1时进行查询操作
					if ("1".equals(message)) {
						//从mysql数据库仓库中查询数据
						jc.mysqlSelect("select * from cangku");
						
//						//从oracle数据库订单表中查询数据
//						jc.oracleSelect("select * from freshbin_dingdan");
						
						//当oracle数据中连接不上时候,就从本地的mysql数据库的订单表获取数据
						jc.mysqlSelectDingDan("select * from dingdan");
						
						//获取订单表与仓库表比较后,数量不同的结果
						jc.writeIO();
						
						//将两张表比较后的结果返回给客户端
						os.write(jc.writeIO().toString().getBytes());
						os.flush();
					}

下面是查询仓库表的方法

//查询mysql仓库表的数据
	public Map<Integer,Integer> mysqlSelect(String sql) {
		
		mysqlMap = new HashMap<Integer, Integer>();
		
		//调用MysqlPool的方法进行对mysql仓库表的查询
		msPool.getQPstmt(sql);
		ResultSet rs = msPool.getQRs();
		
		try {
			while(rs.next()) {
				mysqlMap.put(rs.getInt(1), rs.getInt(2));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//如果启用定时器执行查询任务的话，下面这条关闭语句必须注释掉，不然会有异常
	//	msPool.close();
		return mysqlMap;
	}

下面是查询订单表的方法

//查询mysql订单表的数据
	public Map<Integer,Integer> mysqlSelectDingDan(String sql) {
		
		mysqlDingDanMap = new HashMap<Integer, Integer>();
		
		//调用MysqlPool的方法进行对mysql仓库表的查询
		msPool.getQPstmt(sql);
		ResultSet rs = msPool.getQRs();
		
		try {
			while(rs.next()) {
				mysqlDingDanMap.put(rs.getInt(1), rs.getInt(2));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//如果启用定时器执行查询任务的话，下面这条关闭语句必须注释掉，不然会有异常
	//	msPool.close();
		return mysqlDingDanMap;
	}

下面是比较仓库表与订单表的结果

	//将仓库表与订单表的数量进行比较
	public StringBuffer writeIO() {
		sb = new StringBuffer();
		//获取仓库表的所有数据
		Set<Integer> mysqlSet = mysqlMap.keySet();
//		//获取订单表的所有数据
//		Set<Integer> oracleSet = oracleMap.keySet();
		//获取订单表的数据(代替oracle订单表)
		Set<Integer> mysqlDingDanSet = mysqlDingDanMap.keySet();
//		System.out.println("测试是否能得到mysqlMap与oracleMap的值");
		for(Integer mn : mysqlSet) {
			//定义一个boolean类型的变量来表示当仓库的商品编号在订单表中不存在时,就设置为true
			boolean flag = true;
			for(Integer on : mysqlDingDanSet) {
				if(mn.intValue() == on.intValue()) {
					sb.append("商品编号为：" + mn + ",仓库的数量为：" + mysqlMap.get(mn) + ",订单的数量为：" + mysqlDingDanMap.get(on) + "\r\n");
					flag = false;
				}
			}
			//当flag为true时,就把订单表中没有仓库表中的商品编号打印出来
			if(flag) {
				sb.append("商品编号为：" + mn + ",仓库的数量为：" + mysqlMap.get(mn) + ",订单的数量为：订单表中暂未有此商品编号的商品" + "\r\n");
			}
		}
		return sb;
	}

下面是进行数据库连接的方法

package com.freshbin.jdbcconn;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
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.util.Properties;

public class MysqlPool {
	
	private Connection conn = null;
	private PreparedStatement qpstmt = null;
	private PreparedStatement uPstmt = null;
	private ResultSet rs = null;
	private String url;
	private String user;
	private String password;
	private Properties pro = null;
	private static MysqlPool msPool = null;
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private MysqlPool() {
		
	}
	
	public static MysqlPool getInstance() {
		
		if(msPool == null) {
			synchronized(MysqlPool.class) {
				if(msPool == null) {
					msPool = new MysqlPool();
				}
			}
		}
		
		return msPool;
	}
	
	//连接数据库
	public Connection getConn() {
		
		if(conn != null) {
			return conn;
		}
		
		try {
			pro = new Properties();
			pro.load(new FileInputStream("properties/mysql.properties"));
			conn = DriverManager.getConnection(pro.getProperty("url"), pro.getProperty("user"), pro.getProperty("password"));
//			System.out.println("连接状态：" + !conn.isClosed());
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return conn;
	}
	
	//查询所有数据
	public PreparedStatement getQPstmt(String sql) {
		try {
			qpstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return qpstmt;
	}
	
	//执行查询语句
	public ResultSet getQRs() {
		
		try {
			rs = qpstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rs;
	}
	
	//执行更新仓库数据
		public PreparedStatement getUPstmt(String updateSql, int id, int mysqlNumber) {
			try {
				conn.setAutoCommit(false);
				uPstmt = conn.prepareStatement(updateSql);
				uPstmt.setInt(1, mysqlNumber);
				uPstmt.setInt(2, id);
				uPstmt.executeUpdate();
				conn.commit();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			
			return uPstmt;
		}
	
	
	//关闭数据库
	public void close() {
			
			try {
				if(rs != null) {
					rs.close();
				}
				if(qpstmt != null) {
					qpstmt.close();
				}
				if(conn != null) {
				conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

	}
	
}

最后上图,首先是仓库表与订单表在数据库中的情况

然后是客户端进行查询的结果

接着是数据库的两张表都修改后,客户端再次查询的结果

自此,对数据库的查询操作就算是搞定了,对数据库的插入与更新下次再写