[大数据]实现通用Dao（实现insert |update |selectByPrimaryKey）

实体类Student：

import java.util.Date;

public class Student {
    private String sno;
    private String sname;
    private String tel;
    private Double height;
    private int amt;
    private Date birthday;

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public Double getHeight() {
        return height;
    }

    public void setHeight(Double height) {
        this.height = height;
    }

    public int getAmt() {
        return amt;
    }

    public void setAmt(int amt) {
        this.amt = amt;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

?实现insert\update\selectByPrimaryKey

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.oracle.vo.User;
//实现一个通用Dao类 将SQL语句封装在Dao中
public class BaseDao {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public Connection getConnection() {
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sql0313?characterEncoding=utf8", "root","root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
/**
 *
 * @param obj 如果是Student对象，就向表名是Student的表进行插入操作
 * @return
 */
public int insert(Object obj,boolean autoincr,String pkName) {
    int result = 0;
    //1.获得obj的类对象
    Class cls = obj.getClass();
    //2.获得其类名
    String tableName = cls.getSimpleName();
    //3.获得所有的属性
    Field[] fields = cls.getDeclaredFields();
    //4.拼预编译的SQL语句
    String columnSQL = "";
    String valueSQL = "";
    List list = new ArrayList();
    conn = this.getConnection();
    try {
        for(Field field:fields) {
            field.setAccessible(true);
            //id是增长的，id不拼到columnSQL中 但是表的主键列名是不清楚的
            columnSQL+=field.getName()+",";
            if(autoincr&&field.getName().equals(pkName)) {
                valueSQL+= "default,";
            }else {
                valueSQL+= "?,";
                list.add(field.get(obj));
            }
        }
        columnSQL = columnSQL.substring(0, columnSQL.length()-1);
        valueSQL = valueSQL.substring(0, valueSQL.length()-1);
        String sql = "insert into "+tableName+"("+columnSQL+") values ("+valueSQL+")";
        stmt = conn.prepareStatement(sql);
        //5.设置预编译参数值
        for(int i = 0;i<list.size();i++) {
            stmt.setObject(i+1, list.get(i));
        }
        //6.执行SQL语句
        result = stmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (IllegalArgumentException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    }finally {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return result;
}
    public int update(Object obj,String pkName) {
        int result = 0;
        //1.获得obj的类对象
        Class cls = obj.getClass();
        //2.获得其类名
        String tableName = cls.getSimpleName();
        //3.获得所有的属性
        Field[] fields = cls.getDeclaredFields();
        StringBuilder sql = new StringBuilder("update "+tableName +" set ");
        List list = new ArrayList();
        Object pkValue = null;
        try {
            for(Field field:fields) {
                sql.append(field.getName()+"=?,");
                field.setAccessible(true);
                list.add(field.get(obj));
                if(field.getName().equalsIgnoreCase(pkName)) {
                    pkValue = field.get(obj);
                }
            }
            String strSQL = sql.substring(0,sql.length()-1);
            strSQL += " where "+pkName+"=?";
            conn = this.getConnection();
            stmt = conn.prepareStatement(strSQL);
            int i = 0;
            for(i = 0;i<list.size();i++) {
                stmt.setObject(i+1,list.get(i));
            }
            stmt.setObject(i+1, pkValue);
            result = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    /* public int delete(String tableName,Object pkValue,String pkName) {
    }*/
    public Object selectByPrimaryKey(Object obj,String pkName) {
        Class cls = obj.getClass();
        Field[] fields = cls.getDeclaredFields();
        StringBuilder columnSQL = new StringBuilder();
        Object pkValue = null;
        try {
            for(Field field:fields) {
                field.setAccessible(true);
                columnSQL.append(field.getName()).append(",");
                if(field.getName().equalsIgnoreCase(pkName)) {
                    pkValue = field.get(obj);
                }
            }
            String sql = "select " + columnSQL.substring(0,columnSQL.length()-1)+" from "+cls.getSimpleName()+" where "+pkName+"=?";
            conn = this.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setObject(1, pkValue);
            rs = stmt.executeQuery();
            if(rs.next()) {
                Object returnObj = cls.newInstance();
                for(Field field:fields) {
                    field.setAccessible(true);
                    field.set(returnObj, rs.getObject(field.getName()));
                }
                return returnObj;
            }
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    public static void main(String[] args) {
        BaseDao dao = new BaseDao();
        /*Student student = new Student();
        student.setSno("220314");
        student.setSname("张飞2");
        student.setTel("13311112222");
        student.setHeight(1.77);
        student.setAmt(1000);
        student.setBirthday(new Date());
        dao.insert(student,true,"id");*/
        /* User user = new User();
        user.setUserId(100);
        user.setUsername("tom");
        dao.insert(user, false, null);*/
        /* Books b = new Books();
        b.setBookName("abc");
        dao.insert(b, false, null);*/
        /* User user = new User();
        user.setUserId(100);
        user.setUsername("tom2");
        dao.update(user, "userid");*/
        /*Student student = new Student();
        student.setId(5);
        student = (Student)dao.selectByPrimaryKey(student, "id");
        System.out.println(student.getSname()+","+student.getAmt());*/
        User user = new User();
        user.setUserId(1);
        User user1 = (User)dao.selectByPrimaryKey(user, "userid");
        System.out.println(user1.getUsername());
    }
}