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