concreatedataaccessor.java

短信发送

/**
 * @CreatedDate Nov 5, 2008
 */
package com.jdev.db;

/*
 * 数据访问器
 */
import java.sql.*;
import java.util.*;

import com.jdev.util.DbUtil;

/**
 * @author Lawrence
 * 
 */
public class ConcreateDataAccessor {
	private DataConnection con;

	private Connection accountingConnection;
	private Connection payrollConnection;
	private Connection otherConnection;
	private final String user = "gwtexpo";
	private final String password = "gwtexpo";

	//
	public ConcreateDataAccessor() throws Exception {
		try {
//			DbUtil.testConnection(accountingConnection);
			
			con = new DataConnection("");

			accountingConnection = DriverManager.getConnection(con.sConnStr,user,password);
			payrollConnection = DriverManager.getConnection(con.sConnStr,user,password);
			otherConnection = DriverManager.getConnection(con.sConnStr,user,password);
		} catch (SQLException e) {
			throw new Exception("不能建立使用数据访问器", e);
		}
	}

	// 根据条件读取数据返回List
	public List<Row> read(String table, String[] columns, Row selectionRow,
			String[] sortColumns, String order) throws Exception {
		try {
			StringBuffer buffer = new StringBuffer();
			buffer.append(" SELECT ");

			if (columns != null) {
				for (int i = 0; i < columns.length; ++i) {
					if (i > 0)
						buffer.append(",");
					buffer.append(columns[i]);
				}
			} else
				buffer.append(" * ");

			buffer.append(" FROM ");
			buffer.append(resolveQualifiedTable(table));

			if (selectionRow != null) {
				buffer.append(generateWhereClause(selectionRow));
			}

			if (sortColumns != null) {
				buffer.append(" ORDER BY ");
				for (int i = 0; i < sortColumns.length; ++i) {
					if (i > 0)
						buffer.append(",");
					buffer.append(sortColumns[i]);
					buffer.append(" ");
					buffer.append(order);
					buffer.append(" ");
				}
			}

			Connection connection = resolveConnection(table);
			System.out.println("start time:" + new String(new java.util.Date().toLocaleString()));
			synchronized (connection) {
				Statement statement = connection.createStatement();
				System.out.println(buffer.toString());
				ResultSet resultSet = statement.executeQuery(buffer.toString());

				ResultSetMetaData rsmd = resultSet.getMetaData();
				int columnCount = rsmd.getColumnCount();

				List<Row> resultRows = new LinkedList<Row>();
				while (resultSet.next()) {
					Row resultRow = new Row();
					for (int i = 1; i <= columnCount; ++i) {
						resultRow.addColumn(rsmd.getColumnName(i), resultSet
								.getObject(i));
					}
					resultRows.add(resultRow);
				}
				resultSet.close();
				statement.close();
				System.out.println("end time:" + new String(new java.util.Date().toLocaleString()));
				
				return resultRows;
			}
		} catch (SQLException e) {
			throw new Exception("不能够读取表" + table, e);
		}
	}

	// 插入数据
	public void insert(String table, List<Row> rows) throws Exception {
		try {

			for (Iterator<Row> i = rows.iterator(); i.hasNext();) {
				Row row = (Row) i.next();
				StringBuffer buffer = new StringBuffer();
				buffer.append("INSERT INTO ");
				buffer.append(resolveQualifiedTable(table));

				buffer.append("(");
				boolean firstColumn = true;
				for (Iterator<String> j = row.columns(); j.hasNext();) {
					if (!firstColumn)
						buffer.append(", ");
					else
						firstColumn = false;
					buffer.append(j.next());
				}

				buffer.append(") VALUES (");
				firstColumn = true;

				for (Iterator<String> j = row.columns(); j.hasNext();) {
					if (!firstColumn)
						buffer.append(", ");
					else
						firstColumn = false;

					String column = (String) j.next();
					Object columnValue = row.getColumnValue(column);
					buffer.append(generateLiteralValue(columnValue));
				}

				buffer.append(")");

				Connection connection = resolveConnection(table);
				synchronized (connection) {
					Statement statement = connection.createStatement();
					System.out.println(buffer.toString());
					statement.executeUpdate(buffer.toString());
					statement.close();
				}
			}
		} catch (SQLException e) {
			throw new Exception("不能将数据够插入该表" + table, e);
		}
	}

	// 修改数据
	public void update(String table, Row selectionRow, Row updateRow)
			throws Exception {
		try {

			StringBuffer buffer = new StringBuffer();

			buffer.append("UPDATE ");
			buffer.append(resolveQualifiedTable(table));
			buffer.append(" SET ");

			boolean firstColumn = true;
			for (Iterator<String> i = updateRow.columns(); i.hasNext();) {
				if (!firstColumn)
					buffer.append(", ");
				else
					firstColumn = false;
				String column = (String) i.next();
				buffer.append(column);
				buffer.append(" = ");
				Object columnValue = updateRow.getColumnValue(column);
				buffer.append(generateLiteralValue(columnValue));
			}

			if (selectionRow != null) {
				buffer.append(generateWhereClause(selectionRow));
			}

			Connection connection = resolveConnection(table);
			synchronized (connection) {
				Statement statement = connection.createStatement();
				System.out.println(buffer.toString());
				statement.executeUpdate(buffer.toString());
				statement.close();
			}
		} catch (SQLException e) {
			throw new Exception("不能修改该表" + table, e);
		}
	}

	// 删除数据
	public void delete(String table, Row selectionRow) throws Exception {
		try {
			StringBuffer buffer = new StringBuffer();
			buffer.append("DELETE FROM ");
			buffer.append(resolveQualifiedTable(table));

			if (selectionRow != null) {
				buffer.append(generateWhereClause(selectionRow));
			}

			Connection connection = resolveConnection(table);
			synchronized (connection) {
				Statement statement = connection.createStatement();
				// System.out.println(buffer.toString());
				statement.executeUpdate(buffer.toString());
				statement.close();
			}
		} catch (SQLException e) {
			throw new Exception("不能删除该表数据" + table, e);
		}

	}

	//	
	private Connection resolveConnection(String table) {
		if (table.startsWith("A"))
			return accountingConnection;
		else if (table.startsWith("P"))
			return payrollConnection;
		else
			return otherConnection;
	}

	private String resolveQualifiedTable(String table) {
		if (table.startsWith("A"))
			return "ACCTDATA." + table;
		else if (table.startsWith("P"))
			return "PAYROLL." + table;
		else
			return table;
	}

	private String generateLiteralValue(Object literalValue) {
		StringBuffer buffer = new StringBuffer();
		if (!(literalValue instanceof Number))
			buffer.append("'");
		buffer.append(literalValue);
		if (!(literalValue instanceof Number))
			buffer.append("'");
		return buffer.toString();
	}

	private String generateWhereClause(Row selectionRow) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(" WHERE ");
		boolean firstColumn = true;
		for (Iterator<String> i = selectionRow.columns(); i.hasNext();) {
			if (!firstColumn)
				buffer.append(" AND ");
			else
				firstColumn = false;
			String column = (String) i.next();
			buffer.append(column);
			buffer.append(" = ");
			Object columnValue = selectionRow.getColumnValue(column);
			buffer.append(generateLiteralValue(columnValue));
		}
		return buffer.toString();
	}

	/**
	 * @param args
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws Exception, SQLException {
//		int nCount = 10;
//		// 1. 注册驱动
//		try {
//			Class.forName("oracle.jdbc.driver.OracleDriver");
//		} catch (ClassNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}// Mysql 的驱动
//		
//		// 2. 获取数据库的连接
//		java.sql.Connection conn = DriverManager.getConnection(
//				"jdbc:oracle:thin:@192.168.168.121:1521:GWTWOC","gwtexpo","gwtexpo");
//		
//		// 3. 获取表达式
//		java.sql.Statement stmt = conn.createStatement();
//		
//		
//		// 4. 执行 SQL
//		
//		java.sql.ResultSet rs = stmt.executeQuery("select count(*) from MV_SMS_Receiver");
//		
//		if(rs.next()){
//		nCount = rs.getInt(1);
//		int nTimes = nCount / 10;
//		for(int i=0;i<nTimes;i++){
//			// 5. 显示结果集里面的数据
//			int j=0;
//			String strSql = "SELECT * FROM MV_SMS_Receiver ";
//			strSql += "where id>= (select min(id) from mv_sms_receiver)+" + 10*i;
//			strSql += " and id < (select min(id) from mv_sms_receiver)+"+ 10*(i+1);
//			System.out.println(strSql);
//			rs = stmt.executeQuery(strSql);
//			while(rs.next()) {
//				j++;
//				System.out.println("Record "+j+","+rs.getString(4)+","+rs.getString(7));
//	
//			}
//		}
//		}
//
//		// 6. 释放资源
//		rs.close();
//		stmt.close();
//		conn.close();
		
		ConcreateDataAccessor con;
		// 1. 注册驱动
		try {
			con = new ConcreateDataAccessor();
			// 4. 执行 SQL
			List<Row> rs = con.read("MV_Sms_Receiver", null, null, null, null);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}// Mysql 的驱动
		
		
		System.exit(0);
		
	}

}