dbtablemodel.java

实习时做的人事系统

package com.personnel;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.swing.table.AbstractTableModel;

/**
 * 这个类是实际保存数据库数据的表格模型， 并重写了基本方法
 */
public class DBTableModel extends AbstractTableModel {

	private static final long serialVersionUID = 1L;

	private DbManager dbManager = null;
	private ResultSet rs = null;
	private ResultSetMetaData rsmd = null;
	private int columnCount = 0;
	private String[] columnNames = null;
	private String dbTableName = null;
	private String schemaName = null;
	private String query = null;

	/**
	 * 构造结果集模型
	 * 
	 * @param resultSet
	 *            从数据库返回的结果集
	 */
	public DBTableModel(String query, String schemaName, String tableName) {
		this.dbTableName = tableName;
		this.schemaName = schemaName;
		this.query = query;
		this.dbManager = new DbManager(DbManager.MYSQL_DRIVER,
				DbManager.MYSQL_URL, "root", "6581502",
				DbManager.TYPE_SCROLL_SENSITIVE, DbManager.CONCUR_UPDATABLE);
		this.rs = this.dbManager.executeQuery(query);

		try {
			this.rsmd = this.rs.getMetaData();
			this.columnCount = this.rsmd.getColumnCount();
			this.columnNames = new String[this.columnCount];
			for (int i = 0; i < this.columnCount; i++) {
				this.columnNames[i] = this.rsmd.getColumnName(i + 1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public boolean closeDB() {
		return this.dbManager.closeDb();
	}

	public String[] getColumnNames() {
		return this.columnNames;
	}

	public String getQuery() {
		return this.query;
	}

	private String getPrimaryKey() throws SQLException {
		DatabaseMetaData dbmd = this.dbManager.getConnection().getMetaData();
		ResultSet r = dbmd.getPrimaryKeys(null, this.schemaName,
				this.dbTableName);
		if (r.next()) {
			String p_key = r.getString(4);
			return p_key;
		}
		return null;
	}

	public boolean add(String[] values) {
		int column = this.columnCount;
		StringBuilder addQuery = new StringBuilder();
		addQuery.append("INSERT INTO ");
		addQuery.append(this.dbTableName);
		addQuery.append(" (");
		for (int i = 0; i < this.columnCount; i++) {
			if (!this.columnNames[i].equals("ID")) {
				addQuery.append(this.columnNames[i]);
				addQuery.append(",");
			} else {
				column = column - 1;

			}
		}
		addQuery.deleteCharAt(addQuery.length() - 1);
		addQuery.append(") VALUES( ");
		for (int i = values.length; i > 0; i--) {
			if (values[i - 1].equals("")) {
				addQuery.append("'',");
			} else {
				addQuery.append("'");
				addQuery.append(values[i - 1]);
				addQuery.append("',");
			}
		}
		addQuery.deleteCharAt(addQuery.length() - 1);
		addQuery.append(");");
		if (this.dbManager.executeUpdate(addQuery.toString()) != -1) {
			return true;
		}
		return false;
	}

	public boolean del(String primaryKeyValue) throws SQLException {
		String p_key = this.getPrimaryKey();
		StringBuilder delQuery = new StringBuilder();
		delQuery.append("DELETE FROM ");
		delQuery.append(this.dbTableName);
		delQuery.append(" WHERE ");
		delQuery.append(p_key);
		delQuery.append("=");
		delQuery.append(primaryKeyValue);
		delQuery.append(";");
		if (!(this.dbManager.executeUpdate(delQuery.toString()) == -1)) {
			return true;
		}
		return false;
	}

	public boolean update(String primaryKeyValue, String[] values)
			throws SQLException {
		String p_key = this.getPrimaryKey();
		StringBuilder updateQuery = new StringBuilder();
		updateQuery.append("UPDATE ");
		updateQuery.append(this.dbTableName);
		updateQuery.append(" SET ");
		for (int i = 0; i < this.columnCount; i++) {
			if (!this.columnNames[i].equals("ID")) {
				updateQuery.append(this.columnNames[i]);
				updateQuery.append("=");
				updateQuery.append(values[i]);
				updateQuery.append(",");
			}
		}
		updateQuery.deleteCharAt(updateQuery.length() - 1);
		updateQuery.append(" WHERE ");
		updateQuery.append(p_key);
		updateQuery.append("=");
		updateQuery.append(primaryKeyValue);
		updateQuery.append(";");
		if (!(this.dbManager.executeUpdate(updateQuery.toString()) == -1)) {
			return true;
		}
		return false;
	}

	public Object getValueAt(int r, int c) {
		try {
			/*
			 * if(rs.isClosed()){ rs = dbManager.executeQuery(query); }
			 */
			this.rs.absolute(r + 1);
			Object value = this.rs.getObject(c + 1);
			/*
			 * if(getColumnName(c+1).equals("Picture")){ ImageIcon image=new
			 * ImageIcon(
			 * ((Blob)value).getBytes(1,(int)((Blob)value).length())); return
			 * image; }
			 */
			return value;
		} catch (SQLException e) {
			try {
				this.rs.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return null;
		}
	}

	public int getRowCount() {
		try {
			/*
			 * if(rs.isClosed()){ rs = dbManager.executeQuery(query); }
			 */
			this.rs.last();
			return this.rs.getRow();
		} catch (SQLException e) {
			try {
				this.rs.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return 0;
		}
	}

	public int getColumnCount() {
		try {
			return this.rsmd.getColumnCount();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public String getColumnName(int c) {
		try {
			return this.rsmd.getColumnName(c + 1);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public Class getColumnClass(int c) {
		try {
			return Class.forName(this.rsmd.getColumnClassName(c + 1));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		}
	}

}