java mysql连接两张表_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 mysqlSelect(String sql) {

mysqlMap = new HashMap();

//调用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 mysqlSelectDingDan(String sql) {

mysqlDingDanMap = new HashMap();

//调用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 mysqlSet = mysqlMap.keySet();

获取订单表的所有数据

//Set oracleSet = oracleMap.keySet();

//获取订单表的数据(代替oracle订单表)

Set 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();

}

}

}

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

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

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

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