daoimpl.java

一个可以存储和打印成绩单的系统

package org.minjey.cjsjk.dao;

import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Queue;

import org.minjey.cjsjk.model.Course;
import org.minjey.cjsjk.model.Student;
import org.minjey.cjsjk.util.Reflector;

public class DaoImpl extends Dao {
	
	private Reflector reflector = null;
	
	private Connection conn;
	
	public DaoImpl() {
		reflector = new Reflector();
		conn = DBConnection.getConnection();
	}
	
	public DaoImpl(Connection conn) {
		reflector = new Reflector();
		this.conn = conn;
	}

	@Override
	public Integer delete(Object obj) {
		Class cla = obj.getClass();
		
		String tname = reflector.getClassName(cla);
		Integer id = (Integer)reflector.getValue(obj, "id");
		String sql = "DELETE FROM " + tname + " WHERE ID=" + id.intValue();

		System.out.println("Attempt To Delete " + cla.getSimpleName() + ": " + obj.toString() + ".");
		System.out.println("SQL: " + sql);
		try {
			conn.createStatement().execute(sql);
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		System.out.println(cla.getSimpleName() + ": " + obj.toString() + " Delete Successd.");
		return id;
	}

	@Override
	public Object load(Class cla, Integer id) {
		
		Object obj = null;
		
		Class voc = cla.getSuperclass();
		String tname = reflector.getClassName(voc);
		String sql = "SELECT " + makeCNames(voc) + " FROM " + tname + " WHERE ID=?";

		System.out.println("Attempt To Load " + cla.getSimpleName() + " Whose Id Is: " + id.toString() + ".");
		System.out.println("SQL: " + sql);
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, id.intValue());
			ResultSet rs = pst.executeQuery();
			if(rs.next()) {
				ResultSetUtil rsu = new ResultSetUtil();
				obj = rsu.newObject(cla, rs);
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(cla.getSimpleName() + ": " + obj.toString() + " Load Successd.");
		
		return toSubObject(obj);
	}
	
	@Override
	public HashMap<Integer, Object> load(Class cla, Object fobj) {
		String fcname = reflector.getClassName(fobj.getClass()) + "_id";
		int fid = (Integer)reflector.getValue(fobj, "id");
		
		Class voc = cla.getSuperclass();
		HashMap<Integer, Object> objs = null;
		String tname = reflector.getClassName(voc);
		
		String sql = "SELECT " + makeCNames(voc) + " FROM " + tname + " WHERE " + fcname + "=" + fid;
		
		System.out.println("Attempt To Load " + cla.getSimpleName() + " Whose " + fcname + " is: " + fid + ".");
		System.out.println("SQL: " + sql);
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			objs = new HashMap<Integer, Object>();
			while(rs.next()) {
				ResultSetUtil rsu = new ResultSetUtil();
				Object obj = rsu.newObject(cla, rs);
				
				obj = toSubObject(obj);
				attach(fobj, obj);
				Integer id = getId(fobj.getClass(), obj);
				objs.put(id, obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(cla.getSimpleName() + " Whose " + fcname + " is: " + fid + " Load Successd.");
		
		return objs;
	}
	
	@Override
	public HashMap<Integer, Object> loadall(Class cla) {
		
		HashMap<Integer, Object> objs = null;
		
		Class voc = cla.getSuperclass();
		String tname = reflector.getClassName(voc);
		
		String sql = "SELECT " + makeCNames(voc) + " FROM " + tname;

		System.out.println("Attempt To Load " + cla.getSimpleName() + " All.");
		System.out.println("SQL: " + sql);
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			objs = new HashMap<Integer, Object>();
			while(rs.next()) {
				ResultSetUtil rsu = new ResultSetUtil();
				Object obj = rsu.newObject(cla, rs);
				obj = toSubObject(obj);
				Integer id = (Integer)reflector.getValue(obj, "id");
				objs.put(id, obj);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		System.out.println("All " + cla.getSimpleName() + "s Load Successd.");
		return objs;
	}

	@Override
	public Object save(Object obj) {
		Class cla = obj.getClass();
		
		Class voc = cla.getSuperclass();
		String tname = reflector.getClassName(voc);
		
		Field[] fields = voc.getDeclaredFields();
		
		Queue<String> qcnames = new LinkedList<String>();
		String cnames = new String();
		String cvalues = new String();
		for(int i = 0; i < fields.length; i ++) {
			String cname = fields[i].getName();
			if(cname.equals("id")){
				continue;
			}
			qcnames.offer(cname);
			cnames = cnames + cname + ",";
			cvalues = cvalues + "?" + ",";
		}
		cnames = cnames.substring(0, cnames.length()-1);
		cvalues = cvalues.substring(0, cvalues.length()-1);

		String sql = "INSERT INTO " + tname + " (";
		sql = sql + cnames + ") VALUES (" + cvalues + ")";

		System.out.println("Attempt To Save New " + cla.getSimpleName() + ": " + obj.toString() + ".");
		System.out.println("SQL: " + sql);
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			
			PrepareStatementUtil psu = new PrepareStatementUtil();
			psu.prepareStatement(qcnames, obj, pst);
			
			pst.executeUpdate();
			
			Integer id = getId(conn);
			reflector.setValue(obj, "id", new Object[]{id});
			pst.close();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("New " + cla.getSimpleName() + ": " + obj.toString() + " Save Successd.");
		
		return obj;
	}

	@Override
	public void update(Object obj) {
		Class cla = obj.getClass();
		
		Integer id = (Integer)reflector.getValue(obj, "id");
		
		Class voc = cla.getSuperclass();
		
		String tname = reflector.getClassName(voc);
		Field[] fields = voc.getDeclaredFields();
		
		Queue<String> qcnames = new LinkedList<String>();
		String kvs = new String();
		for(int i = 0; i < fields.length; i++) {
			String cname = fields[i].getName();
			if(cname.equals("id") || (reflector.getValue(obj, cname) == null)) {
				continue;
			}
			qcnames.offer(cname);
			kvs = kvs + cname + "=?" + ",";
		}
		kvs = kvs.substring(0, kvs.length() - 1);

		String sql = "UPDATE " + tname + " SET ";
		sql = sql + kvs + " WHERE ID=" + id.intValue();

		System.out.println("Attempt To Save " + cla.getSimpleName() + ": " + obj.toString() + ".");
		System.out.println("SQL: " + sql);
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			PrepareStatementUtil psu = new PrepareStatementUtil();
			psu.prepareStatement(qcnames, obj, pst);
			
			pst.executeUpdate();
			pst.close();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		System.out.println(cla.getSimpleName() + ": " + obj.toString() + " Save Successd.");
	}
	
	private Integer getId(Class cla, Object obj) {
		Integer id = null;
		if(cla.equals(Student.class)) {
			id = (Integer)reflector.getValue(obj, "course_id");
		} else if(cla.equals(Course.class)) {
			id = (Integer)reflector.getValue(obj, "student_id");
		} else {
			id = (Integer)reflector.getValue(obj, "id");
		}
		return id;
	}
	
	private Integer getId(Connection connection) throws SQLException {   
		CallableStatement callableStatement = connection.prepareCall("values identity_val_local()");   
		ResultSet resultSet = callableStatement.executeQuery();   
		resultSet.next();   
		Integer id = resultSet.getInt(1);   
		resultSet.close();   
		callableStatement.close();
		return id;   
	}
	
	private void attach(Object one, Object many) {
		String oname = reflector.getClassName(one.getClass());	//获取一的名字
		reflector.setValue(many, oname, new Object[]{one});
	}
	
	private Object toSubObject(Object obj) {
		Class cla = obj.getClass();
		String claname = cla.getCanonicalName();
		claname = claname.replaceAll("vo", "model");
		try {
			cla = Class.forName(claname);
			cla.cast(obj);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	
	private String makeCNames(Class cla) {
		String sql = new String();
		Field[] fields = cla.getDeclaredFields();
		for(int i = 0; i < fields.length; i ++) {
			sql = sql + fields[i].getName() + ",";
		}
		sql = sql.substring(0, sql.length() - 1);
		return sql;
	}

	@Override
	public Connection getConnection() {
		return conn;
	}
	
}