dataaccess.java

短信发送

/**
 * Created at Nov 30, 2008
 */
package com.jdev.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import com.jdev.util.Debug;

/**
 * <p>Title: DataAccess</p>
 * <p>Description: </p>
 * @author Lawrence
 * @version 
 */
public class DataAccess implements DataAccessor {

	private DataConnection dbConn;
	private Connection conn;
	private final static String module = DataAccess.class.getName();
	
	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();
	}

	/* (non-Javadoc)
	 * @see com.jdev.db.DataAccessor#close()
	 */
	public void close() throws Exception {
		// TODO Auto-generated method stub

	}

	/* (non-Javadoc)
	 * @see com.jdev.db.DataAccessor#connect(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	public void connect(String DriverName, String connString, String user,
			String password) throws Exception {
		// 1. 注册驱动
		try {
			dbConn = new DataConnection(DriverName);
		} catch (Exception e) {
			Debug.logError("-->connect error:" + e, module);
			throw new Exception("不能建立使用数据访问器", e);
		}// Mysql 的驱动
		
		// 2. 获取数据库的连接
		try {
			conn = DriverManager.getConnection(
					connString,user,password);
		} catch (Exception e) {
			Debug.logError("-->connect error:" + e, module);
			throw new Exception("不能建立数据库连接", e);			
		}
	}

	/* (non-Javadoc)
	 * @see com.jdev.db.DataAccessor#delete(java.lang.String, com.jdev.db.Row)
	 */
	public void delete(String table, Row selectionRow) throws Exception {
		try {
			StringBuffer buffer = new StringBuffer();
			buffer.append("DELETE FROM ");
			buffer.append(table);

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

			Connection connection = conn;
			synchronized (connection) {
				Statement statement = connection.createStatement();
				Debug.logVerbose(buffer.toString());
				statement.executeUpdate(buffer.toString());
				statement.close();
			}
		} catch (SQLException e) {
			Debug.logError("-->delete error:" + e, module);
			throw new Exception("不能删除该表数据" + table, e);
		}
	}

	/* (non-Javadoc)
	 * @see com.jdev.db.DataAccessor#insert(java.lang.String, java.util.List)
	 */
	public void insert(String table, List<Object> rows) throws Exception {
		try {
			for (Iterator<Object> i = rows.iterator(); i.hasNext();) {
				Row row = (Row) i.next();
				StringBuffer buffer = new StringBuffer();
				buffer.append("INSERT INTO ");
				buffer.append(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 = conn;
				synchronized (connection) {
					Statement statement = connection.createStatement();
					Debug.logVerbose(buffer.toString());
					statement.executeUpdate(buffer.toString());
					statement.close();
				}
				
			}
		} catch (Exception e) {
			Debug.logError("-->insert error:" + e, module);
			throw new Exception("不能将数据够插入该表" + table, e);
		}
	}

	/* (non-Javadoc)
	 * @see com.jdev.db.DataAccessor#read(java.lang.String, java.lang.String[], com.jdev.db.Row, java.lang.String[], java.lang.String)
	 */
	public List<Object> 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 ");

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

			// sort column
			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(" ");
				}
			}

			// access tables
			Connection connection = conn;
			synchronized (connection) {
				Statement statement = connection.createStatement();
				Debug.logVerbose(buffer.toString());
				ResultSet resultSet = statement.executeQuery(buffer.toString());

				// 读取数据
				ResultSetMetaData rsmd = resultSet.getMetaData();
				int columnCount = rsmd.getColumnCount();

				List<Object> resultRows = new LinkedList<Object>();
				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();
				
				return resultRows;
			}
			
		} catch (Exception e) {
			Debug.logError("-->read error:" + e, module);
			throw new Exception("不能够读取表" + table, e);
		}
	}

	/* (non-Javadoc)
	 * @see com.jdev.db.DataAccessor#update(java.lang.String, com.jdev.db.Row, com.jdev.db.Row)
	 */
	public void update(String table, Row selectionRow, Row update)
			throws Exception {
		try {

			StringBuffer buffer = new StringBuffer();

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

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

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

			Connection connection = conn;
			synchronized (connection) {
				Statement statement = connection.createStatement();
				Debug.logVerbose(buffer.toString());
				statement.executeUpdate(buffer.toString());
				statement.close();
			}
		} catch (SQLException e) {
			Debug.logError("-->update error:" + e, module);
			throw new Exception("不能修改该表" + table, e);
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}