JDBC操作封装

这两天学习了一下jdbc的封装，依据的是下面这篇

http://wenku.baidu.com/link?url=FaFDmQouYkKO24ApATHYmA5QzUcj-UE-7RSSZaBWPqkKB8i13eYw2LGGEsgs_BRyBf7vB_zgB0vBxFXvhXhmLzBfBEPzPCvMvzMvesUwOzW

然后在它的基础上作了修改，简化参数，做了数据类型方面的休整。还不算完全成型，因为对“0”的处理还没有好的解决方案。下面贴出代码。

先贴一个Student实体类

package com.some.entity;

import java.io.Serializable;
import java.sql.Date;

public class Student implements Serializable {
    private int id;
    private String stuNumber;
    private String stuPassword;
    private String stuName;
    private String stuSex;
    private String stuIdentification;
    private String stuIsGat;
    private String stuPhone;
    private int departmentId;
    private int majorId;
    private String gradeNow;
    private int nationMark;
    private String bithday;// 格式为2015/10/13
    private String address;
    private String politics;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getStuNumber() {
        return stuNumber;
    }

    public void setStuNumber(String stuNumber) {
        this.stuNumber = stuNumber;
    }

    public String getStuPassword() {
        return stuPassword;
    }

    public void setStuPassword(String stuPassword) {
        this.stuPassword = stuPassword;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getStuSex() {
        return stuSex;
    }

    public void setStuSex(String stuSex) {
        this.stuSex = stuSex;
    }

    public String getStuIdentification() {
        return stuIdentification;
    }

    public void setStuIdentification(String stuIdentification) {
        this.stuIdentification = stuIdentification;
    }

    public String getStuIsGat() {
        return stuIsGat;
    }

    public void setStuIsGat(String stuIsGat) {
        this.stuIsGat = stuIsGat;
    }

    public String getStuPhone() {
        return stuPhone;
    }

    public void setStuPhone(String stuPhone) {
        this.stuPhone = stuPhone;
    }

    public int getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentId(int departmentId) {
        this.departmentId = departmentId;
    }

    public int getMajorId() {
        return majorId;
    }

    public void setMajorId(int majorId) {
        this.majorId = majorId;
    }

    public String getGradeNow() {
        return gradeNow;
    }

    public void setGradeNow(String gradeNow) {
        this.gradeNow = gradeNow;
    }

    public int getNationMark() {
        return nationMark;
    }

    public void setNationMark(int nationMark) {
        this.nationMark = nationMark;
    }

    public String getBithday() {
        return bithday;
    }

    public void setBithday(String bithday) {
        this.bithday = bithday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPolitics() {
        return politics;
    }

    public void setPolitics(String politics) {
        this.politics = politics;
    }
}

然后是数据库连接工具类

package com.sql.util;

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

import com.mysql.jdbc.PreparedStatement;

public class DatabaseConnection {
    private static final String driver = "com.mysql.jdbc.Driver";// 数据库驱动
    private static final String url = "jdbc:mysql://localhost:3306/sports_test";// 链接数据库名称
                                                                            // ,localhost替换成域名
    private static final String user = "root";// mysql的登陆用户名
    private static final String password = "1234";// mysql的登陆密码
    private Connection conn = null;

    public DatabaseConnection() throws Exception {// 在构造方法中进行数据库连接
        try {
            Class.forName(driver);//实现静态方法
            this.conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return this.conn;
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closePreparedStatement(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

顺便贴一个简易hash函数

package com.change.util;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

public final class GetHash {
    public static String getMD5(String str){
        String hashedStr=null;
        try{
            MessageDigest md=MessageDigest.getInstance("MD5");
            md.update(str.getBytes());
            byte ss[]=md.digest();
            hashedStr=bytes2String(ss);
        }catch(NoSuchAlgorithmException e){
            
        }
        return hashedStr;
    }
    
    private static String bytes2String(byte[] aa){
        String hash="";
        for(int i=0;i<aa.length;i++){
            int temp;
            if(aa[i]<0){
                temp=256+aa[i];
            }else{
                temp=aa[i];
            }
            if(temp<16){
                hash+="0";
            }
            hash+=Integer.toString(temp,16);
        }
        hash=hash.toUpperCase();
        return hash;
    }
    
//    public static void main(String[] args) {
//        String a="123456";
//        String b="1234567";
//        String c="fast";
//        String hashA=GetHash.getMD5(a);
//        String hashB=GetHash.getMD5(b);
//        String hashC=GetHash.getMD5(c);
//        System.out.println(hashA);
//        System.out.println(hashB);
//        System.out.println(hashC);
//        //E10ADC3949BA59ABBE56E057F20F883E
//        //FCEA920F7412B5DA7BE0CF42B8C93759
//        //31D4541B8E926A24F0C9B835B68CFDF3
//    }
}

顺便贴一个转换编码的类，有时做下载功能的时候需要用到

package com.change.util;

import java.io.UnsupportedEncodingException;

public class ChangeISO {
    public static String parseGBK(String sIn) {
        if ((sIn == null) || (sIn.equals(""))) {
            return sIn;
        }
        try {
            return new String(sIn.getBytes("GBK"), "ISO-8859-1");
        } catch (UnsupportedEncodingException usex) {
        }
        return sIn;
    }

    public static String parseUTF8(String sIn) {
        if ((sIn == null) || (sIn.equals(""))) {
            return sIn;
        }
        try {
            return new String(sIn.getBytes("UTF-8"), "ISO-8859-1");
        } catch (UnsupportedEncodingException usex) {
        }
        return sIn;
    }
}

最后就是jdbc增删查改的封装了，有了这个感觉方便很多，不需要像以前一个操作写一大坨函数，我真是太笨了= =

package com.sql.util;

import java.security.Timestamp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;

import com.change.util.GetHash;
import com.some.entity.Student;

/**
 * @see 这个类用于封装sql操作
 */
public class DatabaseOperator {

    /**
     * @param conn
     *            Connection对象
     * @param obj
     *            vo对象(实体)
     * @param sql
     *            StringBuilder 对象
     * @return List集合
     * @throws Exception
     */

    public static List<?> getSelectList(Connection conn, Object obj,
            StringBuilder sql) {
        int isFirstParameter = 0;
        // 用于装入vo对象中设置属性值的真实属性值
        List<Object> fieldValue = new ArrayList<>();
        // 返回一个list集合，装的是执行过查询的集合，集合resultList装的一般是vo对象
        List<Object> resultList = new ArrayList<>();

        try {
            // 获取对象的所属的类
            Class<?> c = obj.getClass();
            // 取得本类的全部属性
            Field[] f = c.getDeclaredFields();

            for (int i = 0; i < f.length; i++) {
                String name = f[i].getName();
                String get = DatabaseOperator.getGos("get", name);

                // Java 反射机制中 getMethod()和getDeclaredMethod()区别
                // getMethods()和getDeclaredMethods()区别
                // 前者只返回共有方法 后者返回全部3种（不包括继承）
                Method m = c.getDeclaredMethod(get);
                Object value = m.invoke(obj);
                if (value != null && value != ""
                        && !String.valueOf(value).equals("0")) {
                    System.out.println(value);
                    // 如果是第一个条件参数，不需要加上and
                    if (isFirstParameter == 0) {
                        sql.append(" " + name + " = ?");
                        isFirstParameter++;
                        // 填入要附加的条件参数
                        fieldValue.add(value);
                    } else {
                        sql.append(" and " + name + " = ?");
                        // 填入要附加的条件参数
                        fieldValue.add(value);
                    }

                }
            }
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < fieldValue.size(); i++) {
                // System.out.println(fieldValue.size());
                // System.out.println(fieldValue.get(i));
                ps.setObject(i + 1, fieldValue.get(i));
            }
            // 定义结果集
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Object o = c.newInstance();
                // f是本类全部属性
                for (int i = 0; i < f.length; i++) {
                    String name = f[i].getName();
                    // System.out.println(name);
                    String set = DatabaseOperator.getGos("set", name);
                    // System.out.println(set);
                    // 获取类的set方法
                    // System.out.println(f[i].getType());
                    Method m = c.getDeclaredMethod(set, f[i].getType());
                    m.setAccessible(true);
                    // System.out.println(m.getName());
                    Object value = rs.getObject(i + 1);
                    // System.out.println(value + "======");
                    if (value != null) {
                        // String r = f[i].getType().toString();
                        // System.out.println(r);
                        String rsType = value.getClass().getSimpleName();
                        // System.out.println(rsType + "++++");

                        // 判断从数据库读出的数据的数据类型，这里比较奇怪
                        if (rsType.equals("Long")) {
                            m.invoke(o, Integer.parseInt(value.toString()));
                        } else if (rsType.equals("Integer")) {
                            m.invoke(o, value);
                        } else if (rsType.equals("Double")) {
                            m.invoke(o, ((BigDecimal) value).doubleValue());
                        } else if (rsType.equals("Float")) {
                            m.invoke(o, ((BigDecimal) value).floatValue());
                        } else if (rsType.equals("Date")
                                || rsType.equals("Timestamp")) {
                            // System.out.println("da");
                            if (rsType.equals("Date")) {
                                m.invoke(o, ((Date) value).toString());
                            } else {
                                m.invoke(o, ((Timestamp) value).toString());
                            }
                        } else if (rsType.equals("String")) {
                            m.invoke(o, value);
                        } else {
                            // System.out.println("未知");
                        }
                    }
                }
                resultList.add(o);
            }
            return resultList;

        } catch (Exception e) {
            e.printStackTrace();
        }

        return resultList;
    }

    /**
     * 
     * @param conn
     *            Connection对象
     * @param sql1
     *            StringBuilder 可以增加长度，用于拼接指定的需要插入的列名
     * @param obj
     *            vo对象
     * @return int 0，1执行结果
     * @throws Exception
     */
    public static int doInsert(Connection conn, StringBuilder sql1, Object obj)
            throws Exception {
        Class<?> c = obj.getClass();
        Field[] f = c.getDeclaredFields();
        // StringBuilder 可以增加长度，用于拼接values后面的属性值
        StringBuilder sql2 = new StringBuilder();
        // 用于装入vo对象中设置属性值的真实属性值
        List<Object> fieldValue = new ArrayList<>();

        sql1.append("(");
        sql2.append("values(");
        for (int i = 0; i < f.length; i++) {
            String name = f[i].getName();
            String get = getGos("get", name);
            Method m = c.getDeclaredMethod(get);
            Object value = m.invoke(obj);

            if (value != null && value != ""
                    && !String.valueOf(value).equals("0")) {
                sql1.append(name + ",");
                sql2.append("?,");
                fieldValue.add(value);
            }
        }

        int j = sql1.length() - 1;
        if (sql1.lastIndexOf(",") == j) {
            int start = sql1.length() - 1;
            int end = sql1.length();
            sql1.replace(start, end, "");
        }
        int k = sql2.length() - 1;
        if (sql2.lastIndexOf(",") == k) {
            int start = sql2.length() - 1;
            int end = sql2.length();
            sql2.replace(start, end, "");
        }
        sql1.append(")");
        sql2.append(")");
        StringBuilder sql = sql1.append(sql2);
        System.out.println(sql);

        PreparedStatement ps = conn.prepareStatement(sql.toString());
        // System.out.println(fieldValue.size());
        for (int i = 0; i < fieldValue.size(); i++) {
            // System.out.println(fieldValue.get(i));
            ps.setObject(i + 1, fieldValue.get(i));
        }
        // 执行
        int result = ps.executeUpdate();
        return result;
    }

    /**
     * @param conn
     *            Connection对象
     * @param sql
     *            删除的sql语句(index:delete from student)
     * @param id
     *            删除的主键
     * @return int 0，1执行结果
     */
    public static int doDelete(Connection conn, String sql, String primaryKey,
            int id) {
        sql = sql + " where " + primaryKey + " = ?";
        int result = 0;
        try {
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param conn
     *            Connection对象
     * @param sql
     *            要执行的sql语句
     * @param obj
     *            一般是vo对象
     * @param primaryKey
     *            表中的主键， 更新是基于主键更新的
     * @return int 0，1执行结果
     * @throws
     */
    public static int doUpdate(Connection conn, StringBuilder sql, Object obj,
            String primaryKey, Integer id) throws Exception {
        Class<?> c = obj.getClass();
        Field[] f = c.getDeclaredFields();
        // list类型，用于在程序计数传入的属性值
        List<Object> fieldValue = new ArrayList<>();

        sql.append(" set");

        for (int i = 0; i < f.length; i++) {
            String name = f[i].getName();
            String get = getGos("get", name);
            Method m = c.getDeclaredMethod(get);
            Object value = m.invoke(obj);

            int j = name.toLowerCase().indexOf(primaryKey.toLowerCase());
            if (value != null && value != "" && j < 0) {
                // System.out.println(f[i].getName());
                sql.append(" " + name + " = ?,");
                fieldValue.add(value);
            }
        }

        // 去掉最后一个逗号
        int k = sql.length() - 1;
        if (sql.lastIndexOf(",") == k) {
            int start = sql.length() - 1;
            int end = sql.length();
            sql.replace(start, end, "");
        }

        if (id != null) {
            sql.append(" where " + primaryKey + " = ?");
        }

        System.out.println(sql);
        PreparedStatement ps = conn.prepareStatement(sql.toString());
        for (int i = 0; i < fieldValue.size(); i++) {
            // System.out.println(fieldValue.get(i));
            ps.setObject(i + 1, fieldValue.get(i));
        }
        if (id != null) {
            ps.setObject(fieldValue.size() + 1, id);
            System.out.println(id);
        }
        int result = ps.executeUpdate();
        System.out.println(result);
        return result;
    }

    /**
     *  @see getGos是获取get或者set，用于得到一个属性的get和set方法，例如getName()  
     *  @param s:set 或者 get    @param name:属性
     * 
     * @return String类型   
     */
    public static String getGos(String s, String name) {
        String str = s + name.substring(0, 1).toUpperCase() + name.substring(1);
        return str;
    }

    public static void main(String[] args) {
        DatabaseConnection dc = null;
        try {
            dc = new DatabaseConnection();
            Connection conn = dc.getConnection();

            // // 查询
            // Student student1 = new Student();
            // // student1.setStuNumber("14020031096");
            // StringBuilder sql1 = new StringBuilder();
            // // sql1.append("select * from student where");
            // sql1.append("select * from student");
            // @SuppressWarnings("unchecked")
            // List<Student> stuList = (List<Student>) DatabaseOperator
            // .getSelectList(conn, student1, sql1);
            // System.out.println(stuList.get(0).getStuNumber());
            // System.out.println(stuList.get(0).getId());
            // for (int i = 0; i < stuList.size(); i++) {
            // System.out.println(stuList.get(i).getStuName());
            // }

            // 增加
            // Student student2 = new Student();
            // student2.setStuName("萌重宝宝");
            // student2.setStuNumber("22222222222");
            // student2.setStuPassword(GetHash.getMD5("123456"));
            // StringBuilder sql2 = new StringBuilder();
            // sql2.append("insert into student");
            // int result=doInsert(conn, sql2, student2);
            // if(result==1){
            // System.out.println("insert成功");
            // }else{
            // System.out.println("insert失败");
            // }

            // 删除
            // String sql3 = "delete from student";
            // String primaryKey = "id";
            // int id = 15458;
            // int result=doDelete(conn, sql3, primaryKey, id);
            // if(result==1){
            // System.out.println("delete成功");
            // }else{
            // System.out.println("delete失败");
            // }

            // 更新
            // Student student4 = new Student();
            // student4.setStuName("萌重儿子");
            // StringBuilder sql4 = new StringBuilder();
            // sql4.append("update student");
            // String primaryKey = "id";
            // int id = 15456;
            // DatabaseOperator.doUpdate(conn, sql4, student4, primaryKey, id);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

主要是4个函数，还有一个拼接字符串函数，主函数是使用范例。

啦啦啦跑步去