下面是用戶端的啟動代碼
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();
}
}
}
最後上圖,首先是倉庫表與訂單表在資料庫中的情況
然後是用戶端進行查詢的結果
接着是資料庫的兩張表都修改後,用戶端再次查詢的結果
自此,對資料庫的查詢操作就算是搞定了,對資料庫的插入與更新下次再寫