Daoレイヤ(汎用、jarパッケージを追加せず、データベースドライバをインポートするだけ)

JDBCUtilsクラス:データベースの接続&データベースのクローズ
1)接続を取得するにはsrcでjdbcを作成する必要がある.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
pwd=abc123

2)データベースの接続を取得する

 /*
 *           ：
 * 1.          。      ，     ：libs,       .jar      ，      :build path
 * 2.            。
 * 3.       4      ：driverClass、url 、 user 、 password
 *
 * Driver：  
 */
 /**
 *         
 * @return
 * @throws Exception
 */
 public static Connection getConnection() throws Exception {
      // 1.  Properties  ，            
      Properties pros = new Properties();
      pros.load(JDBCUtils.class.getClassLoader()
                .getResourceAsStream("jdbc.properties"));
 
      String driverClass = pros.getProperty("driverClass");
      String url = pros.getProperty("url");
      String user = pros.getProperty("user");
      String password = pros.getProperty("password");
 
      // 2.    
      Class.forName(driverClass);
 
      // 3.    
      Connection conn = DriverManager.getConnection(url, user, password);
      return conn;
 }

3)接続を閉じる

 /**
 *     
 * @param ps
 * @param conn
 */
 public static void close(ResultSet rs ,PreparedStatement ps,Connection conn){
      if(rs != null){
           try {
                rs.close();
           } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           }
      }
      if(ps != null){
           try {
                ps.close();
           } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           }
      }
      if(conn != null){
           try {
                conn.close();
           } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
           }
      }
 }
}

BaseDaoクラス:データベース関連アクション

 
class ReflectionUtil{
    
     //                  
     public static Class getSuperClassGeneric(Class clazz){//  clazz CustomerDAO
         
          Type superclass = clazz.getGenericSuperclass();//superclass : DAO
          ParameterizedType paramType = (ParameterizedType)superclass;
          Type[] types = paramType.getActualTypeArguments();
 
          return (Class)types[0];//Customer.class
     }
    
}
 
 
//DAO：      ：Data Access Object
public class DAO<T> {
     private Class<T> clazz = null;
    
     public DAO(){
          clazz = ReflectionUtil.getSuperClassGeneric(this.getClass());//this.getClass():CustomerDAO
     }
    
    
     public void m(){
          System.out.println(clazz);
     }
    
     //            
     public <E> E getValue(Connection conn,String sql,Object ...args){
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               //1.
               ps = conn.prepareStatement(sql);
               //2.
               for(int i = 0;i < args.length;i++){
                    ps.setObject(i + 1, args[i]);
               }
               //3.
               rs = ps.executeQuery();
               if(rs.next()){
                    return (E)rs.getObject(1);
               }
          } catch (SQLException e) {
               e.printStackTrace();
          }finally{
               //4.    
               JDBCUtils.close(rs, ps, null);
              
          }
         
          return null;
     }
    
     //     ，             
     public List<T> getForList(Connection conn,String sql,Object ... args){
          PreparedStatement ps = null;
          ResultSet rs = null;
          List<T> list = new ArrayList<T>();
          try {
               //1.   sql  ，    PreparedStatement   
               ps = conn.prepareStatement(sql);
               //2.     
               for(int i = 0;i < args.length;i++){
                    ps.setObject(i + 1, args[i]);
               }
              
               //3.  ，       ：ResultSet
               rs = ps.executeQuery();
              
               //4.     。(  )
               //       ：ResultSetMetaData
               ResultSetMetaData rsmd = rs.getMetaData();
               int columnCount = rsmd.getColumnCount();//         
              
               while(rs.next()){
                    //    Class          
                    T t = clazz.newInstance();// new Customer(); id=0,nume=null,...
                    for(int i = 0;i < columnCount;i++){
                         Object columnVal = rs.getObject(i + 1);//         
                        
                         String columnLabel = rsmd.getColumnLabel(i + 1);//      
                        
                         //          t  
                         Field field = clazz.getDeclaredField(columnLabel);
                         field.setAccessible(true);
                         field.set(t, columnVal);
                        
                    }
                    list.add(t);
               }
               return list;
              
              
          } catch (Exception e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
          }finally{
               //5.    
               JDBCUtils.close(rs, ps, null);
              
          }
          return null;
     }
    
     //     ，             
     public T getInstance(Connection conn,String sql,Object ... args){
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               //1.   sql  ，    PreparedStatement   
               ps = conn.prepareStatement(sql);
               //2.     
               for(int i = 0;i < args.length;i++){
                    ps.setObject(i + 1, args[i]);
               }
              
               //3.  ，       ：ResultSet
               rs = ps.executeQuery();
              
               //4.     。(  )
               //       ：ResultSetMetaData
               ResultSetMetaData rsmd = rs.getMetaData();
               int columnCount = rsmd.getColumnCount();//         
               //    Class          
               T t = clazz.newInstance();// new Customer(); id=0,nume=null,...
              
               if(rs.next()){
                    for(int i = 0;i < columnCount;i++){
                         Object columnVal = rs.getObject(i + 1);//         
                        
                         String columnLabel = rsmd.getColumnLabel(i + 1);//      
                        
                         //          t  
                         Field field = clazz.getDeclaredField(columnLabel);
                         field.setAccessible(true);
                         field.set(t, columnVal);
                        
                    }
                    return t;
               }
              
              
          } catch (Exception e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
          }finally{
               //5.    
               JDBCUtils.close(rs, ps, null);
              
          }
          return null;
     }
    
     //         
     public void update(Connection conn,String sql, Object... args) {
          PreparedStatement ps = null;
          try {
               // 1.   sql  ，    PreparedStatement   
               ps = conn.prepareStatement(sql);
               // 2.     ：？
               for (int i = 0; i < args.length; i++) {
                    ps.setObject(i + 1, args[i]);
               }
 
               // 3.  
               ps.executeUpdate();
          } catch (Exception e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
          } finally {
               // 4.    
               JDBCUtils.close(ps, null);
 
          }
 
     }
}    

以上のテストクラスに対して

 
public class CustomerDAO extends DAO<Customer>{
    
    public CustomerDAO(){
         super();
    }
    /**
    *       birth    
    * @param conn
    * @return
    */
    public Date getMaxBirth(Connection conn){
         String sql = "select max(birth) from customers";
         return getValue(conn, sql);
         
    }
    
    /**
    *           
    * @param conn
    * @return
    */
    public long getCount(Connection conn){
         String sql = "select count(*) from customers";
         return getValue(conn, sql);
    }
    
    /**
    *          
    * @param conn
    * @return
    */
    public List<Customer> getAll(Connection conn){
         String sql = "select id,name,email,birth from customers";
         return getForList(conn, sql);
    }
    
    /**
    *          
    * @param conn
    * @param custId    id
    * @return
    */
    public Customer getInstance(Connection conn,int custId){
         String sql = "select id,name,email,birth from customers where id = ?";
         return getInstance(conn, sql, custId);
    }
    
    /**
    *        cust id        (name,email,birth)   cust     
    * @param conn      
    * @param cust
    */
    public void update(Connection conn,Customer cust){
         String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
         update(conn, sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
    }
    
    /**
    *     id   
    * @param conn      
    * @param custId        id
    */
    public void delete(Connection conn,int custId){
         String sql = "delete from customers where id = ?";
         
         update(conn, sql, custId);
    }
 
    /**
    *            
    * @param conn      
    * @param cust       
    */
    public void insert(Connection conn,Customer cust){
         String sql = "insert into customers(name,email,birth) values(?,?,?)";
         
         update(conn, sql,cust.getName(),cust.getEmail(),cust.getBirth());
    }
    
}
 
 
 
 
 
 

 
public class CustomerDAOTest {
     CustomerDAO dao = new CustomerDAO();
    
     @Test
     public void testGeneric(){
          dao.m();
     }
    
     //              DML  
     @Test
     public void testWithTransaction(){
          Connection conn = null;
          try {
               conn = JDBCUtils.getConnection();
              
               conn.setAutoCommit(false);
              
               //   DML          ，     ，      
               dao.delete(conn, 4);
              
               Customer cust = new Customer(8, "  1","[email protected]",new Date(new java.util.Date().getTime()));
               dao.update(conn, cust);
              
               dao.delete(conn, 5);
              
               conn.commit();
          } catch (Exception e) {
               e.printStackTrace();
              
               try {
                    conn.rollback();
               } catch (SQLException e1) {
                    e1.printStackTrace();
               }
          }finally{
               JDBCUtils.close(null, conn);
          }
         
     }
     //    birth    
     @Test
     public void testGetMaxBirth() throws Exception{
          Connection conn = JDBCUtils.getConnection();
          Date birth = dao.getMaxBirth(conn);
          System.out.println(birth);
         
          JDBCUtils.close(null, conn);
     }
     //        
     @Test
     public void testGetCount() throws Exception{
          Connection conn = JDBCUtils.getConnection();
          long count = dao.getCount(conn);
          System.out.println(count);
         
          JDBCUtils.close(null, conn);
     }
    
     //        
     @Test
     public void testGetAll() throws Exception{
          Connection conn = JDBCUtils.getConnection2();
          List<Customer> list = dao.getAll(conn);
          JDBCUtils.close(null, conn);
         
          for(Customer c : list){
               System.out.println(c);
          }
         
     }
    
     //     
     @Test
     public void testUpdate() throws Exception{
          Connection conn = JDBCUtils.getConnection2();
          Customer cust = new Customer(8, "  2","[email protected]",new Date(new java.util.Date().getTime()));
         
          dao.update(conn, cust);
         
          JDBCUtils.close(null, conn);
     }
    
     //     
     @Test
     public void testDelete() throws Exception{
          Connection conn = JDBCUtils.getConnection();
         
          dao.delete(conn, 20);
          JDBCUtils.close(null, conn);
     }
    
     //       
     @Test
     public void testInsert() throws Exception{
          Connection conn = JDBCUtils.getConnection();
          Customer cust = new Customer(1, "  ","[email protected]",new Date(new java.util.Date().getTime()));
         
          dao.insert(conn, cust);
         
          JDBCUtils.close(null, conn);
     }
    
}