jdbc baseDAO 以及 每個類的繼承

首先是baseDAO，用來作為DAO的父類

package dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import jdbc.utils.JDBCUtils;

public abstract class BaseDAO {
    //使用PreparedStatement實作對不同表的通用的傳回一個對象的查詢操作
    //使用泛型機制，參數裡先傳入一個類的類型
    public <T> T getInstance(Connection con,Class<T> clazz,String sql,Object...args) {
        PreparedStatement ps = null;
        ResultSet res = null;
        try {
            ps=con.prepareStatement(sql);
            for(int i=0;i<args.length;i++) {
                ps.setObject(i+1, args[i]);
            }
                        
            //獲得資料集和中繼資料集
            res = ps.executeQuery();
            ResultSetMetaData rsmd = res.getMetaData();
            int col = rsmd.getColumnCount();
            
            if(res.next()) {
                T t = clazz.newInstance();
                for(int i=0;i<col;i++) {
                    Object value = res.getObject(i+1);          //要獲得的資料值
                    String colLabel = rsmd.getColumnLabel(i+1);    //要獲得的中繼資料名稱
                    
                    //通過反射給t對象指定的colName屬性指派為value
                    Field field = clazz.getDeclaredField(colLabel);
                        
                    field.setAccessible(true);
                    field.set(t,value);
                }
                    
                System.out.println("執行成功");
                return t;
            }
            
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
        finally {
            JDBCUtils.closeResource(null, ps, res);
        }
        
        return null;
    }
        
    //傳回對不同表的通用的傳回多個對象的查詢操作 --考慮上事物的版本
    public <T> ArrayList<T> getForList(Connection con,Class<T> clazz,String sql,Object...args){
        PreparedStatement ps = null;
        ResultSet res = null;
        try {
            ps=con.prepareStatement(sql);
            for(int i=0;i<args.length;i++) {
                ps.setObject(i+1, args[i]);
            }
            
            //獲得資料集和中繼資料集
            res = ps.executeQuery();
            ResultSetMetaData rsmd = res.getMetaData();
            int col = rsmd.getColumnCount();
            
            ArrayList<T> list = new ArrayList<T>();
            while(res.next()) {
                T t = clazz.newInstance();
                for(int i=0;i<col;i++) {
                    Object value = res.getObject(i+1);          //要獲得的資料值
                    String colLabel = rsmd.getColumnLabel(i+1);    //要獲得的中繼資料名稱
                    
                    //通過反射給t對象指定的colName屬性指派為value
                    Field field = clazz.getDeclaredField(colLabel);
                    field.setAccessible(true);
                    field.set(t,value);
                }
                
                list.add(t);
            }
            System.out.println("執行成功");
            return list;
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
        finally {
            JDBCUtils.closeResource(null, ps, res);
        }
        return null;
    }
    
    //用來處理聚合函數等隻傳回一個值的sql語句
    public <T>T getValue(Connection con,String sql,Object...args){
        PreparedStatement ps = null;
        ResultSet res = null;
        try {
            ps = con.prepareStatement(sql);
            for(int i=0;i<args.length;i++) {
                ps.setObject(i+1, args[i]);
            }
            
            res = ps.executeQuery();
            System.out.println("查詢成功");
            if(res.next()) {
                return (T)res.getObject(1);
            }
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
        finally {
            JDBCUtils.closeResource(null, ps, res);
        }
        return null;
    }

    //通用增删改，參數為預編譯的sql，和可變形參args用來充當占位符 --可處理事物版本
    public int update(Connection con,String sql,Object ...args) {
        PreparedStatement ps=null;
        try {
            //2.預編譯sql，傳回ps執行個體
            ps=con.prepareStatement(sql);
            //填充占位符，sql中的占位符個數=args數組長度
            for(int i=0;i<args.length;i++) {
                ps.setObject(i+1, args[i]);
            }
            //4.執行, excute()方法如果執行查詢操作，則此方法傳回true，如果執行增删改，則傳回false
            //ps.execute();
            System.out.println("修改成功");
            return ps.executeUpdate();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            //5.關閉資源
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }    
}      

View Code

設有一個類是student類

package bean;

import java.sql.Date;

public class Student {
    private int studentId;
    private String studentName;
    private int studentAge;
    private boolean studentSexy;
    private Date studentBirthday;
    private String classId;
    public Student() {
        super();
    }
    public Student(int studentId, String studentName, int studentAge, boolean studentSexy, Date studentBirthday,
            String classId) {
        super();
        this.studentId = studentId;
        this.studentName = studentName;
        this.studentAge = studentAge;
        this.studentSexy = studentSexy;
        this.studentBirthday = studentBirthday;
        this.classId = classId;
    }
    public int getStudentId() {
        return studentId;
    }
    public void setStudentId(int studentId) {
        this.studentId = studentId;
    }
    public String getStudentName() {
        return studentName;
    }
    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }
    public int getStudentAge() {
        return studentAge;
    }
    public void setStudentAge(int studentAge) {
        this.studentAge = studentAge;
    }
    public boolean getStudentSexy() {
        return studentSexy;
    }
    public void setStudentSexy(boolean studentSexy) {
        this.studentSexy = studentSexy;
    }
    public Date getStudentBirthday() {
        return studentBirthday;
    }
    public void setStudentBirthday(Date studentBirthday) {
        this.studentBirthday = studentBirthday;
    }
    public String getClassId() {
        return classId;
    }
    public void setClassId(String classId) {
        this.classId = classId;
    }
    @Override
    public String toString() {
        return "Student [studentId=" + studentId + ", studentName=" + studentName + ", studentAge=" + studentAge
                + ", studentSexy=" + studentSexy + ", studentBirthday=" + studentBirthday + ", classId=" + classId
                + ", getStudentId()=" + getStudentId() + ", getStudentName()=" + getStudentName() + ", getStudentAge()="
                + getStudentAge() + ", getStudentSexy()=" + getStudentSexy() + ", getStudentBirthday()="
                + getStudentBirthday() + ", getClassId()=" + getClassId() + ", getClass()=" + getClass()
                + ", hashCode()=" + hashCode() + ", toString()=" + super.toString() + "]";
    }
    
}      

View Code

其對應的 sql中 表的格式為

 要實作student的DAO，先設計一個接口類，申明所有方法

package dao;

import java.sql.Connection;
import java.sql.Date;
import java.util.ArrayList;

import bean.Student;

public interface StudentDAO {
    void insert(Connection con,Student stu);
    
    void deleteById(Connection con,int id);
    //把标号為id的表記錄屬性改為stu
    void updateById(Connection con,int id,Student stu);
    
    Student getById(Connection con,int id);
    
    ArrayList<Student> getAll(Connection con);
    
    Long getCount(Connection con);

    String getMaxBirthday(Connection con);
}      

View Code

然後是實作以上方法的studentDAOImpl

package dao;

import java.sql.Connection;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Calendar;

import bean.Student;
import jdbc.utils.JDBCUtils;

public class StudentDAOImpl extends BaseDAO implements StudentDAO{

    @Override
    public void insert(Connection con, Student stu) {
        String sql = "insert into student values(?,?,?,?,?,?)";
        update(con,sql,stu.getStudentId(),stu.getStudentName(),stu.getStudentAge(),
                    stu.getStudentSexy(),stu.getStudentBirthday(),stu.getClassId());
        
    }

    @Override
    public void deleteById(Connection con, int id) {
        String sql = "delete from student where stuid = ?";
        update(con,sql,id);
    }

    @Override
    public void updateById(Connection con, int id, Student stu) {
        String sql = "update student set stuName = ?,stuAge = ?,stuSexy = ?,stuBirthday = ?,classId = ?";
        update(con,sql,stu.getStudentName(),stu.getStudentAge(),
                stu.getStudentSexy(),stu.getStudentBirthday(),stu.getClassId());
    }

    @Override
    public Student getById(Connection con, int id) {
        String sql = "select stuId as studentId,stuName as studentName,stuAge as studentAge from student where stuid = ?";
        Student stu = getInstance(con,Student.class,sql,id);
        return stu;
    }

    @Override
    public ArrayList<Student> getAll(Connection con) {
        String sql = "select stuId as studentId,stuName as studentName,stuAge as studentAge from student";
        ArrayList<Student> list= getForList(con,Student.class,sql);
        return list;
    }

    @Override
    public Long getCount(Connection con) {
        String sql = "select count(*) from student";
        Long res = Long.valueOf(getValue(con,sql).toString());
        return res;
    }

    @Override
    public String getMaxBirthday(Connection con) {
        String sql = "select max(stuBirthday) from student";
        return getValue(con,sql).toString();
    }
        
    static public void main(String []args) {
        Connection con = null;
        StudentDAOImpl ob = new StudentDAOImpl();
        try {
            con = JDBCUtils.getConnection();
            
            java.util.Date d = new java.util.Date(99,0,26);//從1900-1-1開始，
            java.sql.Date date = new java.sql.Date(d.getTime());
            
            Student stu = new Student(111,"周恩傑",20,true,date,"09031");
            //ob.insert(con, stu);
            
            //ob.deleteById(con, 111);
            stu.setStudentAge(21);
            //ob.updateById(con, 111, stu);
        
            Student stu1 = ob.getById(con, 101);
            System.out.println(stu1);
            
            ArrayList<Student> list = ob.getAll(con);
            list.forEach(System.out::println);
            
            Long res = ob.getCount(con);
            String ss = ob.getMaxBirthday(con);
            System.out.println(res);
            System.out.println(ss);
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
        finally {
            JDBCUtils.closeResource(con, null, null);
        }
    }
}      

View Code