remotefileclient.java

在机器上开一个端口,代理客户端的数据库请求,用在资源有限的终端访问大型数据库上使用,使用jdbc连接orcle, 客户端使用标准的socket即可

package athere.c;

import java.io.*;
import java.net.*;
import java.util.Date;

//客户端。通过socket和服务器交互

public class RemoteFileClient {

	private String hostIP;
	private int port;
	private BufferedReader socketReader;
	private PrintWriter socketWriter;
	private String serviceID = "0000";
	private Socket client;
	
	private int sqlcode = 0;
	private String sqlErrText = "";
	private InetAddress remote_host;
	
	public String getHostIP(){
		return this.hostIP;
	}
	public int getSqlCode(){
		return this.sqlcode;
	}
	public String getSqlErrtext(){
		return this.sqlErrText;
	}
	
	/**
	 * 构造函数，指定ip和端口
	 * @param hostIP
	 * @param port
	 */
	public RemoteFileClient(String hostIP, int port) {
		this.hostIP = hostIP;
		this.port = port;
		try{
			remote_host = InetAddress.getByName(hostIP);
		}catch(Exception e){}
	}

	/**
	 * 启动连接
	 */
	public void setupConnection() {
		try {
			//client = new Socket(hostIP, port);
			client = new Socket(remote_host, port);
			System.out.println(new Date());
			socketReader = new BufferedReader(new InputStreamReader(
					client.getInputStream()));
			socketWriter = new PrintWriter(client.getOutputStream());
		} catch (UnknownHostException e) {
			System.out.println("Unkown host IP."+ e.getMessage());
		} catch (IOException e) {
			System.out.println("Setting up connection error:" + e.getMessage());
		}
	}

	/**
	 * 
	 * 获取文件
	 * @param filename
	 * @return
	 */
	public String getFile(String filename) {
		StringBuffer content = new StringBuffer();
		try {
			socketWriter.println(filename);
			socketWriter.flush();
			String line = null;
			while ((line = socketReader.readLine()) != null)
			{
				content.append(line + "\n");
			}
		} catch (IOException e) {
			System.out.println("Error on reading file.");
		}
		return content.toString();
	}
	/* 将一个int转化成4char的字符串 0xFFFF = 32767 */
	private String int24char(int intVlaue){
		String ret = Integer.toHexString(intVlaue);
		int li_len = ret.length();
		if(li_len == 3){
			return "0"+ret;
		}else if (li_len == 2){
			return "00"+ret;
		}else if (li_len == 1){
			return "000"+ret;
		}
		return ret;
	}
	/**
	 * 执行一个sql,并返回一个结果字符串
	 * @param sql 要执行的sql
	 * @param curPage 当前页号,最大值为32767
	 * @param RowsPerPage 每页的记录数,最大值为32767
	 * @return 查询结果信息
	 */
	public String executeQueryForPage(String sql, int curPage, int RowsPerPage ) {
		InputStream s = null;
		try {
			socketWriter.print("1001");     //执行查询命令
			socketWriter.print(serviceID);  //服务号4char
			socketWriter.print("0");        //无服务号返回
			socketWriter.print(int24char(curPage));    //4char当前页号
			socketWriter.print(int24char(RowsPerPage));//4char每页的记录数
			socketWriter.println(sql);
			socketWriter.flush();
			
			InputStream in = client.getInputStream();
			System.out.println("executeQueryForPage 接收服务器返回");
			/*
			0000 指令标识
			XXXX 服务号
			XXXX sqlcode
			*/
			s = in;
			String cmd = null;
			
			byte[] tmpchar = new byte[4];
			
			s.read(tmpchar, 0, 4 ); // "0000 命令指示"
			cmd = new String(tmpchar);
			
			s.read(tmpchar, 0, 4 );
			serviceID = new String(tmpchar);  // 服务号
			
			s.read(tmpchar, 0, 4 );
			int ret = Integer.parseInt(new String(tmpchar), 16);  //
			
			System.out.println("返回协议前缀:" + cmd);
			System.out.println("返回服务号: " + serviceID);
			System.out.println("执行结果 sqlcode:" + ret);
			
			if(ret == 0){
				//有查询结果
				fetchDbRsSQLCODE0(in);
			}else if(ret == 100){
				//查询无记录
				//sqlErrText = (String) s.readObject();
				//return (String) s.readObject();
			}else{
				//查询错误
				//sqlErrText = (String) s.readObject();
				//return (String) s.readObject();
			}
			/*
			while ((line = socketReader.readLine()) != null)
			{
				content.append(line + "\n");
			}
			*/
		} catch (IOException e) {
			System.out.println("Error on reading file.");
		} catch (Exception e){
			e.printStackTrace();
			System.out.println("exception" + e.getMessage());
		}finally{
			System.out.println("关闭连接");
			if(s != null){
				try{
					s.close();
				}catch(Exception e){
					System.out.println( "关闭连接出错:"+e.getMessage());
				}
			}
		}
		return "";
	}
	
	private void fetchDbRsSQLCODE0(InputStream in) throws IOException{
		/*
XXXXXXXX 总行数
XXXX 每页的行数
XXXX 当前页
XXXX 总页数
XXXX 列数

XXXX[...........]  各列名
...

XXXX 行数
XXXX[...........]  各字段信息
...
		 */
		byte[] tmpchar4 = new byte[4];
		byte[] tmpchar8 = new byte[8];
		byte[] tmpchar1 = new byte[1];
		in.read(tmpchar8, 0, 8 );
		int rowCount = Integer.parseInt(new String(tmpchar8), 16);  //总行数
		System.out.println("rowCount is :" + rowCount);
		
		in.read(tmpchar4, 0, 4 );
		int rowsPerPage = Integer.parseInt(new String(tmpchar4), 16);  //每页的行数
		System.out.println("rowsPerPage is :" + rowsPerPage);
		
		in.read(tmpchar4, 0, 4 );
		int curPage = Integer.parseInt(new String(tmpchar4), 16);  //当前页
		System.out.println("curPage is :" + curPage);
		
		in.read(tmpchar4, 0, 4 );
		int maxPage = Integer.parseInt(new String(tmpchar4), 16);  //最大页号
		System.out.println("maxPage is :" + maxPage);
		
		in.read(tmpchar4, 0, 4 );
		int nCols = Integer.parseInt(new String(tmpchar4), 16);
		System.out.println("共" + nCols + "列");
		
		int colnamelen = 0;
		for(int i = 0; i < nCols; i++){
			in.read(tmpchar4, 0, 4 );
			colnamelen = Integer.parseInt(new String(tmpchar4), 16);
			byte[] tmpcolname = new byte[colnamelen];
			in.read(tmpcolname, 0, colnamelen );
			String colname = new String(tmpcolname);
			System.out.println("列" + i + "长度: " + colnamelen + " 值:" + colname);
		}
		
		in.read(tmpchar4, 0, 4 );
		int nRows = Integer.parseInt(new String(tmpchar4), 16);  //本记录集的行数
		System.out.println("共" + nRows + "行");
		
		int itemlen = 0; 
		String lenStr = null,itemStr = null;
		String itemtype="";
		for(int i =1; i<= nRows; i++){
			for(int j =1; j<=nCols; j++){
				in.read(tmpchar1, 0, 1);
				if(tmpchar1[0] == 'X'){
					//sql执行中出了问题
					break;
				}
				itemtype = new String(tmpchar1);
				in.read(tmpchar4, 0, 4);
				lenStr = new String(tmpchar4);
				itemlen = Integer.parseInt(lenStr, 16);
				if(itemlen==65535){
					System.out.println(i + "行" +j+ "列的数据类型:" + itemtype + "数据是:");
				}else{
					byte[] itembytes = new byte[itemlen];
					in.read(itembytes, 0, itemlen);
					itemStr = new String(itembytes);
					System.out.println(i + "行" +j+ "列的数据类型:" + itemtype + "数据是: "+ itemStr);
				}
				
			}
			System.out.println();
		}
		return ;
	}
	
	/**
	 * 执行数据库更新语句的操作，autoCommit确定是否释放服务号
	 * @param sql
	 * @param autoCommit
	 * @return
	 */
	public String executeUpdate(String sql, char autoCommit) {
		StringBuffer content = new StringBuffer();
		try {
			socketWriter.print("1002");     //执行查询命令
			socketWriter.print(serviceID);  //服务号4char
			socketWriter.print(autoCommit); //是否自动提交本服务号的操作，若
			socketWriter.println(sql);
			socketWriter.flush();
			String line = null;
			while ((line = socketReader.readLine()) != null)
			{
				content.append(line + "\n");
			}
		} catch (IOException e) {
			System.out.println("Error on reading file.");
		}
		return content.toString();
	}
	
	/**
	 * 关闭连接
	 */
	public void tearDownConnection() {
		try {
			socketReader.close();
			socketWriter.close();
		} catch (IOException e) {
			System.out.println("error on closeing socket connectiong.");
		}
	}

//	test start
    public static void main(String[] args){
    	String sql= null;
    	RemoteFileClient tmp = new RemoteFileClient("127.0.0.1",8059);
    	/*
    	tmp.setupConnection();
    	System.out.println(tmp.getFile("c:\\EHD_SJ.TXT"));
    	System.out.println("----------------------------------------------");
    	tmp.tearDownConnection();
    	//一次连接只能处理一个请求
    	tmp.setupConnection();
    	System.out.println(tmp.getFile("c:\\SCM表结构.TXT"));
    	//System.out.println(tmp.getFile("get /index.html http/1.0 \n\n"));
    	tmp.tearDownConnection();
    	*/
    	//执行查询语句!
    	System.out.println(new Date());
    	System.out.println("----------------------------------------------");
    	tmp.setupConnection();
    	sql = "select item, name, uom, class,add_date, bigqty comm from ehd.spda001 where item like '00000%' and item < '0000050' ";
    	System.out.println("执行查询语句:"+sql);
    	System.out.println("服务器返回:");
    	String temp = tmp.executeQueryForPage(sql, 1, 100);
    	System.out.println(temp);
    	System.out.println("-------------------------------------------------------");
    	tmp.tearDownConnection();
    }
    //test end
}