package com.tly.dbutil;

import java.io.IOException;
import java.util.Properties;

public class Config {
  private static Properties prop = new Properties();  
  static{    
    try {
      //加載dbconfig.properties配置文件
      prop.load(Config.class.getResourceAsStream("dbconfig.properties"));
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
  
  //設(shè)置常量
  public static final String CLASS_NAME = prop.getProperty("CLASS_NAME");
  public static final String DATABASE_URL = prop.getProperty("DATABASE_URL");
  public static final String SERVER_IP = prop.getProperty("SERVER_IP");
  public static final String SERVER_PORT = prop.getProperty("SERVER_PORT");
  public static final String DATABASE_SID = prop.getProperty("DATABASE_SID");
  public static final String USERNAME = prop.getProperty("USERNAME");
  public static final String PASSWORD = prop.getProperty("PASSWORD");
  
}

CLASS_NAME=com.mysql.jdbc.Driver
DATABASE_URL=jdbc:mysql
SERVER_IP=localhost
SERVER_PORT=3306
DATABASE_SID=employees
USERNAME=root
PASSWORD=1

package com.employees.dbutil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class DBConn {
  //三屬性、四方法
  
  //三大核心接口
  private Connection conn = null;
  private PreparedStatement pstmt = null;
  private ResultSet rs = null;
  
  //四個(gè)方法
  //method1: 創(chuàng)建數(shù)據(jù)庫(kù)的連接
  public Connection getConntion(){    
    try {
      //1: 加載連接驅(qū)動(dòng)，Java反射原理
      Class.forName(Config.CLASS_NAME);
      //2:創(chuàng)建Connection接口對(duì)象，用于獲取MySQL數(shù)據(jù)庫(kù)的連接對(duì)象。三個(gè)參數(shù)：url連接字符串  賬號(hào) 密碼
      String url = Config.DATABASE_URL+"://"+Config.SERVER_IP+":"+Config.SERVER_PORT+"/"+Config.DATABASE_SID;
      conn = DriverManager.getConnection(url,Config.USERNAME,Config.PASSWORD);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }  
    return conn;
  }
  
  
  //method2：關(guān)閉數(shù)據(jù)庫(kù)的方法
  public void closeConn(){
    if(rs!=null){
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if(pstmt!=null){
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if(conn!=null){
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  
  //method3: 專(zhuān)門(mén)用于發(fā)送增刪改語(yǔ)句的方法
  public int execOther(PreparedStatement pstmt){
    try {
      //1、使用Statement對(duì)象發(fā)送SQL語(yǔ)句
      int affectedRows = pstmt.executeUpdate();
      //2、返回結(jié)果
      return affectedRows;
    } catch (SQLException e) {
      e.printStackTrace();
      return -1;
    }
  }


  //method4: 專(zhuān)門(mén)用于發(fā)送查詢(xún)語(yǔ)句
  public ResultSet execQuery(PreparedStatement pstmt){
    try {
      //1、使用Statement對(duì)象發(fā)送SQL語(yǔ)句
      rs = pstmt.executeQuery();
      //2、返回結(jié)果
      return rs;
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
  }

}

package com.employees.dao;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.employees.dbutil.DBConn;

public class BaseDAO<T> {
  
  DBConn conn = new DBConn();
  private Connection connection = null;
  
  @SuppressWarnings("unused")
  private Class<T> persistentClass;
  
  @SuppressWarnings("unchecked")
  public BaseDAO() {
    initConnection();
    //獲得參數(shù)化類(lèi)型    
    ParameterizedType type = (ParameterizedType)getClass().getGenericSuperclass();
    persistentClass = (Class<T>)type.getActualTypeArguments()[0];
  }
  
  
  /**
   * 獲得數(shù)據(jù)庫(kù)連接
   */
  public void initConnection() {
    connection = conn.getConntion();      
  }
  
  
  /**
   * 保存
   */
  public void save(T entity) throws Exception{
    //SQL語(yǔ)句,insert into table name (
    String sql = "insert into " + entity.getClass().getSimpleName().toLowerCase() + "(";
    
    //獲得帶有字符串get的所有方法的對(duì)象
    List<Method> list = this.matchPojoMethods(entity,"get");
    
    Iterator<Method> iter = list.iterator();
    
    //拼接字段順序 insert into table name(id,name,email,
    while(iter.hasNext()) {
      Method method = iter.next();
      sql += method.getName().substring(3).toLowerCase() + ",";
    }
    
    //去掉最后一個(gè),符號(hào)insert insert into table name(id,name,email) values(
    sql = sql.substring(0, sql.lastIndexOf(",")) + ") values(";
    
    //拼裝預(yù)編譯SQL語(yǔ)句insert insert into table name(id,name,email) values(?,?,?,
    for(int j = 0; j < list.size(); j++) {
      sql += "?,";
    }

    //去掉SQL語(yǔ)句最后一個(gè),符號(hào)insert insert into table name(id,name,email) values(?,?,?);
    sql = sql.substring(0, sql.lastIndexOf(",")) + ")";
    
    //到此SQL語(yǔ)句拼接完成,打印SQL語(yǔ)句
    System.out.println(sql);
    
    //獲得預(yù)編譯對(duì)象的引用
    PreparedStatement statement = connection.prepareStatement(sql);
    
    int i = 0;
    //把指向迭代器最后一行的指針移到第一行.
    iter = list.iterator();
    while(iter.hasNext()) {
      Method method = iter.next();
      //此初判斷返回值的類(lèi)型,因?yàn)榇嫒霐?shù)據(jù)庫(kù)時(shí)有的字段值格式需要改變,比如String,SQL語(yǔ)句是'"+abc+"'
      if(method.getReturnType().getSimpleName().indexOf("String") != -1) {
        statement.setString(++i, this.getString(method, entity));
      } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1){
        statement.setDate(++i, this.getDate(method, entity));
      } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) {
        statement.setAsciiStream(++i, this.getBlob(method, entity),1440);
      } else {
        statement.setInt(++i, this.getInt(method, entity));
      }
    }
    //執(zhí)行
    conn.execOther(statement);
    //關(guān)閉連接
    conn.closeConn();
  }
  
  
  /**
   * 修改
   */
  public void update(T entity) throws Exception{
    String sql = "update " + entity.getClass().getSimpleName().toLowerCase() + " set ";
    
    //獲得該類(lèi)所有g(shù)et方法對(duì)象集合
    List<Method> list = this.matchPojoMethods(entity,"get");
    
    //臨時(shí)Method對(duì)象,負(fù)責(zé)迭代時(shí)裝method對(duì)象.
    Method tempMethod = null;
    
    //由于修改時(shí)不需要修改ID,所以按順序加參數(shù)則應(yīng)該把Id移到最后.
    Method idMethod = null;
    Iterator<Method> iter = list.iterator();
    while(iter.hasNext()) {
      tempMethod = iter.next();
      //如果方法名中帶有ID字符串并且長(zhǎng)度為2,則視為ID.
      if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) {
        //把ID字段的對(duì)象存放到一個(gè)變量中,然后在集合中刪掉.
        idMethod = tempMethod;
        iter.remove();
      //如果方法名去掉set/get字符串以后與pojo + "id"想符合(大小寫(xiě)不敏感),則視為ID
      } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) {
        idMethod = tempMethod;
        iter.remove();        
      }
    }
    
    //把迭代指針移到第一位
    iter = list.iterator();
    while(iter.hasNext()) {
      tempMethod = iter.next();
      sql += tempMethod.getName().substring(3).toLowerCase() + "= ?,";
    }
    
    //去掉最后一個(gè),符號(hào)
    sql = sql.substring(0,sql.lastIndexOf(","));
    
    //添加條件
    sql += " where " + idMethod.getName().substring(3).toLowerCase() + " = ?";
    
    //SQL拼接完成,打印SQL語(yǔ)句
    System.out.println(sql);
    
    PreparedStatement statement = this.connection.prepareStatement(sql);
    
    int i = 0;
    iter = list.iterator();
    while(iter.hasNext()) {
      Method method = iter.next();
      //此初判斷返回值的類(lèi)型,因?yàn)榇嫒霐?shù)據(jù)庫(kù)時(shí)有的字段值格式需要改變,比如String,SQL語(yǔ)句是'"+abc+"'
      if(method.getReturnType().getSimpleName().indexOf("String") != -1) {
        statement.setString(++i, this.getString(method, entity));
      } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1){
        statement.setDate(++i, this.getDate(method, entity));
      } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) {
        statement.setAsciiStream(++i, this.getBlob(method, entity),1440);
      } else {
        statement.setInt(++i, this.getInt(method, entity));
      }      
    }
    
    //為Id字段添加值
    if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) {
      statement.setString(++i, this.getString(idMethod, entity));
    } else {
      statement.setInt(++i, this.getInt(idMethod, entity));
    }
    
    //執(zhí)行SQL語(yǔ)句
    statement.executeUpdate();
        
        //關(guān)閉預(yù)編譯對(duì)象
        statement.close();
        
        //關(guān)閉連接
        connection.close();
  }
  
  
  /**
   * 刪除
   */
  public void delete(T entity) throws Exception{
    String sql = "delete from " + entity.getClass().getSimpleName().toLowerCase() + " where ";
    
    //存放字符串為"id"的字段對(duì)象
    Method idMethod = null;
    
    //取得字符串為"id"的字段對(duì)象
    List<Method> list = this.matchPojoMethods(entity, "get");
    Iterator<Method> iter = list.iterator();
    while(iter.hasNext()) {
      Method tempMethod = iter.next();
      //如果方法名中帶有ID字符串并且長(zhǎng)度為2,則視為ID.
      if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) {
        //把ID字段的對(duì)象存放到一個(gè)變量中,然后在集合中刪掉.
        idMethod = tempMethod;
        iter.remove();
      //如果方法名去掉set/get字符串以后與pojo + "id"想符合(大小寫(xiě)不敏感),則視為ID
      } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) {
        idMethod = tempMethod;
        iter.remove();        
      }
    }
    
    sql += idMethod.getName().substring(3).toLowerCase() + " = ?";
    
    PreparedStatement statement = this.connection.prepareStatement(sql);
    
    //為Id字段添加值
    int i = 0;
    if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) {
      statement.setString(++i, this.getString(idMethod, entity));
    } else {
      statement.setInt(++i, this.getInt(idMethod, entity));
    }    
    
    //執(zhí)行
    conn.execOther(statement);
    //關(guān)閉連接
    conn.closeConn();
  }
  
  
  /**
   * 通過(guò)ID查詢(xún)
   */
  public T findById(Object object) throws Exception{
    String sql = "select * from " + persistentClass.getSimpleName().toLowerCase() + " where ";
    
    //通過(guò)子類(lèi)的構(gòu)造函數(shù),獲得參數(shù)化類(lèi)型的具體類(lèi)型.比如BaseDAO<T>也就是獲得T的具體類(lèi)型
    T entity = persistentClass.newInstance();
    
    //存放Pojo(或被操作表)主鍵的方法對(duì)象
    Method idMethod = null;
    
    List<Method> list = this.matchPojoMethods(entity, "set");
    Iterator<Method> iter = list.iterator();
    
    //過(guò)濾取得Method對(duì)象
    while(iter.hasNext()) {
      Method tempMethod = iter.next();
      if(tempMethod.getName().indexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) {
        idMethod = tempMethod;
      } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))){
        idMethod = tempMethod;
      }
    }
    //第一個(gè)字母轉(zhuǎn)為小寫(xiě)
    sql += idMethod.getName().substring(3,4).toLowerCase()+idMethod.getName().substring(4) + " = ?";
    
    //封裝語(yǔ)句完畢,打印sql語(yǔ)句
    System.out.println(sql);
    
    //獲得連接
    PreparedStatement statement = this.connection.prepareStatement(sql);
    
    //判斷id的類(lèi)型
    if(object instanceof Integer) {
      statement.setInt(1, (Integer)object);
    } else if(object instanceof String){
      statement.setString(1, (String)object);
    }
    
    //執(zhí)行sql,取得查詢(xún)結(jié)果集.
    ResultSet rs = conn.execQuery(statement);
    
    //記數(shù)器,記錄循環(huán)到第幾個(gè)字段
    int i = 0;
        
    //把指針指向迭代器第一行
    iter = list.iterator();
    
    //封裝
    while(rs.next()) {
      while(iter.hasNext()) {
        Method method = iter.next();
        if(method.getParameterTypes()[0].getSimpleName().indexOf("String") != -1) {
          //由于list集合中,method對(duì)象取出的方法順序與數(shù)據(jù)庫(kù)字段順序不一致(比如:list的第一個(gè)方法是setDate,而數(shù)據(jù)庫(kù)按順序取的是"123"值)
          //所以數(shù)據(jù)庫(kù)字段采用名字對(duì)應(yīng)的方式取.
          this.setString(method, entity, rs.getString(method.getName().substring(3).toLowerCase()));
        } else if(method.getParameterTypes()[0].getSimpleName().indexOf("Date") != -1){
          this.setDate(method, entity, rs.getDate(method.getName().substring(3).toLowerCase()));
        } else if(method.getParameterTypes()[0].getSimpleName().indexOf("InputStream") != -1) {
          this.setBlob(method, entity, rs.getBlob(method.getName().substring(3).toLowerCase()).getBinaryStream());
        } else {
          this.setInt(method, entity, rs.getInt(method.getName().substring(3).toLowerCase()));
        }  
      }
    }
    
    //關(guān)閉結(jié)果集
    rs.close();
        
    //關(guān)閉預(yù)編譯對(duì)象
    statement.close();
    
    return entity;
  }
  
  
  /**
   * 過(guò)濾當(dāng)前Pojo類(lèi)所有帶傳入字符串的Method對(duì)象,返回List集合.
   */
  private List<Method> matchPojoMethods(T entity,String methodName) {
    //獲得當(dāng)前Pojo所有方法對(duì)象
    Method[] methods = entity.getClass().getDeclaredMethods();
    
    //List容器存放所有帶get字符串的Method對(duì)象
    List<Method> list = new ArrayList<Method>();
    
    //過(guò)濾當(dāng)前Pojo類(lèi)所有帶get字符串的Method對(duì)象,存入List容器
    for(int index = 0; index < methods.length; index++) {
      if(methods[index].getName().indexOf(methodName) != -1) {
        list.add(methods[index]);
      }
    }    
    return list;
  }
  
  
  /**
   * 方法返回類(lèi)型為int或Integer類(lèi)型時(shí),返回的SQL語(yǔ)句值.對(duì)應(yīng)get
   */
  public Integer getInt(Method method, T entity) throws Exception{
    return (Integer)method.invoke(entity, new Object[]{});
  }
  
  /**
   * 方法返回類(lèi)型為String時(shí),返回的SQL語(yǔ)句拼裝值.比如'abc',對(duì)應(yīng)get
   */
  public String getString(Method method, T entity) throws Exception{
    return (String)method.invoke(entity, new Object[]{});
  }
  
  /**
   * 方法返回類(lèi)型為Blob時(shí),返回的SQL語(yǔ)句拼裝值.對(duì)應(yīng)get
   */
  public InputStream getBlob(Method method, T entity) throws Exception{
    return (InputStream)method.invoke(entity, new Object[]{});
  }
  
  
  /**
   * 方法返回類(lèi)型為Date時(shí),返回的SQL語(yǔ)句拼裝值,對(duì)應(yīng)get
   */
  public Date getDate(Method method, T entity) throws Exception{
    return (Date)method.invoke(entity, new Object[]{});
  }
  
  
  /**
   * 參數(shù)類(lèi)型為Integer或int時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set
   */
  public Integer setInt(Method method, T entity, Integer arg) throws Exception{
    return (Integer)method.invoke(entity, new Object[]{arg});
  }
  
  /**
   * 參數(shù)類(lèi)型為String時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set
   */
  public String setString(Method method, T entity, String arg) throws Exception{
    return (String)method.invoke(entity, new Object[]{arg});
  }
  
  /**
   * 參數(shù)類(lèi)型為InputStream時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set
   */
  public InputStream setBlob(Method method, T entity, InputStream arg) throws Exception{
    return (InputStream)method.invoke(entity, new Object[]{arg});
  }
  
  
  /**
   * 參數(shù)類(lèi)型為Date時(shí),為entity字段設(shè)置參數(shù),對(duì)應(yīng)set
   */
  public Date setDate(Method method, T entity, Date arg) throws Exception{
    return (Date)method.invoke(entity, new Object[]{arg});
  }
}

package com.employees.dao;

import java.util.ArrayList;
import java.util.List;
import com.employees.po.Employees;

public class EmployeesDao extends BaseDAO<Employees> {

  // 添加員工信息的操作
  public boolean addEmployees(final Employees employees) throws Exception {
    save(employees);
    return true;
  }

  // 將員工信息添加到表格中
  public List<Employees> addEmployees(int id) throws Exception {
    List<Employees> lstEmployees = new ArrayList<Employees>();
    Employees employees = findById(id);
    // 將當(dāng)前封轉(zhuǎn)好的數(shù)據(jù)裝入對(duì)象中
    lstEmployees.add(employees);
    return lstEmployees;
  }

  public void deleteEmp(final Employees entity) throws Exception {
    this.delete(entity);
  }

  public void updateEmp(final Employees entity) throws Exception {
    this.update(entity);
  }


}

package com.employees.dao;

import org.junit.Test;

import com.employees.po.Employees;

public class EmployeesDaoTest {

  @Test
  public void testAdd() throws Exception {
    Employees emp = new Employees();
    emp.setPname("tly");
    emp.setPsex("男");
    emp.setPbeliefs("xxxxx");
    emp.setPaddr("天河");
    emp.setPhobby("打籃球");
    emp.setPsubject("計(jì)算機(jī)");
    emp.setPtel("123456");
    EmployeesDao dao = new EmployeesDao();
    dao.addEmployees(emp);
  }
  @Test
  public void testUpdate() throws Exception {
    EmployeesDao dao = new EmployeesDao();
    Employees emp = dao.findById(14);
    emp.setPtel("999999");
    dao.updateEmp(emp);
  }
  @Test
  public void testdelete() throws Exception {
    EmployeesDao dao = new EmployeesDao();
    Employees emp = dao.findById(15);
    dao.deleteEmp(emp);
  }

}