基于JDBC的一个简单的DAO实例

DAO模式可以帮助我们少些很多的数据库操作的代码，还能将对象直接进行持久化到数据库中，一个完整的DAO模式包含了五个部分，分别是:

1、数据库连接类，封装了对数据库的操作
2、VO类，对应的数据库中的表，每个bean的属性都是其中的元素
3、DAO接口，定义了数据库操作的接口
4、DAO实现类，实现DAO接口
5、DAO工厂类，用于获取DAO实现类的实例

下面我们来分别进行实现

1、数据库连接类

package com.fan.DB;

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

/**
 * mysql的连接获取和释放
 * @author Administrator
 *
 */
public class MysqlConnection {
    //数据库的URL
    private static String BaseURL = "jdbc:mysql://localhost:3306/testdb?";
    //名称
    private static String UserName = "root";
    //密码
    private static String Password = "*******";
    //连接
    private static Connection connection = null;
    
    static{
        try {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            } catch (InstantiationException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            // TODO: handle exception
            System.out.println("找不到驱动类");
            e.printStackTrace();
        }
    }
    
    /**
     * 获取数据库的连接
     * @return
     */
    public static Connection getConnection(){
        try {
            String link = BaseURL + 
                    "user=" + UserName + "&password=" + Password + "&useSSL=false&serverTimezone=GMT";
            System.out.println("link:" + link);
            connection = DriverManager.getConnection(link);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("数据库连接失败");
            e.printStackTrace();
        }
        return connection;
    }
    
    /**
     * 关闭数据库连接
     */
    public static void closeConnection(){
        if(connection == null){
            System.out.println("数据库连接为空，不能进行释放");
            return;
        }
        try {
            connection.close();
            System.out.println("数据库关闭完成");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    /**
     * 判断数据库连接是否存活
     */
    public static boolean isConnectionSuvivor(Connection connection){
        return connection != null;
    }
}

2、VO类

package com.fan.beans;

/**
 * 工程师类，数据库测试的VO
 * @author Administrator
 *
 */
public class Engineer {
    private int age;
    private String name;
    private int salary;
    
    public Engineer(){}
    
    public Engineer( int age, String name, int salary) {
        super();
        this.age = age;
        this.name = name;
        this.salary = salary;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getSalary() {
        return salary;
    }
    public void setSalary(int salary) {
        this.salary = salary;
    }
}

3、DAO接口

package com.fan.DB;

import java.util.List;

import com.fan.beans.PackageClass;

public interface DBHandleInterface<T> {
    //创建表
    public void createTable(Class<?> clazz) throws Exception;
    //删除表
    public void deleteTable(Class<?> clazz) throws Exception;
    //插入用户
    public void insert (PackageClass<T> t,Class<?> clazz) throws Exception;
    //更新用户
    public void update(PackageClass<T> t,Class<T> clazz) throws Exception;
    //删除用户
    public void delete(PackageClass<T> t,Class<T> clazz) throws Exception;
    //获取用户
    public T queryById(PackageClass<T> t,Class<T> clazz) throws Exception;
    //获取所有用户
    public String queryAll(Class<T> clazz) throws Exception;
}

4、DAO接口的实现类

package com.fan.DB;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import com.fan.EnumCheck.DBEnum;
import com.fan.Tools.ObjectTools;
import com.fan.beans.PackageClass;

/**
 * 数据库操作的实现
 * @author Administrator
 *
 * @param <T>
 */
public class DBHandleInterfaceImpl<T> implements DBHandleInterface<T> {
    //SQL语句对象初始长度
    private static final int STRING_BUILDER_CAPACITY = 400;
    
    /**
     * 插入对象
     */
    public void insert(PackageClass<T> t, Class<?> clazz) throws Exception {
        //获取定义的方法
        Method[] methods = clazz.getDeclaredMethods();
        //表名
        String TableName = clazz.getSimpleName();
        //定义一个获取方法名的变量
        String str = null;
        //创建一个insert的SQL对象
        StringBuilder insertSQL = new StringBuilder(STRING_BUILDER_CAPACITY);
        insertSQL.append("INSERT INTO ").append(TableName).append(" values(0,");
        //遍历方法
        for(Method method : methods){
            //获取方法名
            str = method.getName();
            //只筛选需要的方法
            if(!str.contains("throws") && str.contains("get") && !str.contains("Class")){
                //获取方法的值
                Object object = method.invoke(t.getData());
                insertSQL.append((object instanceof String) ? ("\'" + object + "\'") : object).append(",");
            }
        }
        //获取sql长度
        int len = insertSQL.length();
        insertSQL.delete(len - 1, len).append(");");
        String sql = insertSQL.toString();
        System.out.println(sql);
        //执行
        executeUpdateProcess(sql);
    }

    /**
     * 更新数据
     */
    public void update(PackageClass<T> t,Class<T> clazz) throws Exception {
        //先对对象进行查找
        T res = queryById(t, clazz);
        if(res != null)//找到先删除
            delete(t, clazz);
        //再插入
        insert(t, clazz);
    }

    /**
     * 删除数据
     */
    public void delete(PackageClass<T> t,Class<T> clazz) throws Exception {
        //获取表名
        String TableName = clazz.getSimpleName();
        //创建一个删除的sql
        StringBuilder deleteSQL = new StringBuilder(STRING_BUILDER_CAPACITY);
        //构建sql语句
        deleteSQL.append("DELETE FROM ").append(TableName).append(" WHERE ");
        //获取方法
        Method[] methods = clazz.getDeclaredMethods();
        //
        String str = null;
        for(Method method : methods){
            str = method.getName();
            if(!str.contains("throws") && str.contains("get") && !str.contains("Class")){
                str = str.replaceFirst("get", "");
                Object object = method.invoke(t.getData());
                deleteSQL.append(str).append("=").append(!method.getReturnType().getSimpleName().equals("int") ? ("\'" + object + "\'") : object).append(" and ");
            }
        }
        deleteSQL.append(";");
        String sql = deleteSQL.toString().replaceAll("and ;", ";");
        //执行sql
        executeUpdateProcess(sql);
        //
    }

    /**
     * 根据id进行查找
     */
    public T queryById(PackageClass<T> t,Class<T> clazz) throws Exception {
        //获取id
        int index = t.getIndex();
        //表名
        String TableName = clazz.getSimpleName();
        //SQL
        StringBuilder querySQL = new StringBuilder(STRING_BUILDER_CAPACITY);
        //
        querySQL.append("SELECT * FROM ").append(TableName).append(" WHERE id = ").append(index).append(";");
        //获取连接
        Connection conn = MysqlConnection.getConnection();
        //执行体
        Statement stmt = null;
        //结果
        ResultSet rs = null;
        //
        T obj = t.getData();
        try{
        //创建执行体
        stmt = conn.createStatement();
        //执行查询
        rs = stmt.executeQuery(querySQL.toString());
        //
        Method[] methods = clazz.getDeclaredMethods();
        //
        Pattern pattern = Pattern.compile("[a-zA-Z]+");
        //遍历结果并返回，在这里，结果只能是一位或者没有
        while(rs.next()){
            for(Method method : methods){
                if(method.getName().startsWith("set")){
                    String name = method.getName().replaceFirst("set", "");
                    String res = rs.getString(name);
//                  System.out.println(res);
                    method.invoke(obj, pattern.matcher(res).matches() ? res : Integer.parseUnsignedInt(res));
                }
            }
        }
        }catch(Exception e){
            System.out.println("执行中出现异常");
            e.printStackTrace();
        }finally{
            //释放执行体
            freeStateMemt(stmt, rs);
            //关闭连接
            MysqlConnection.closeConnection();
        }
        return obj;
    }

    /**
     * 查找所有的数据
     */
    public String queryAll(Class<T> clazz) throws Exception {
        //表名
        String TableName = clazz.getSimpleName();
        //查询语句
        String queryAll = "SELECT *  FROM " + TableName + ";";
        //获取连接
        Connection conn = MysqlConnection.getConnection();
        //执行体
        Statement stmt = null;
        //结果
        ResultSet rs = null;
        //
        StringBuilder res = new StringBuilder(10000);
        //类的方法
        Method[] methods = clazz.getDeclaredMethods();
        StringBuilder nameBuilder = new StringBuilder(STRING_BUILDER_CAPACITY);
        for(Method method : methods){
            if(method.getName().startsWith("get")){
                String name = method.getName().replaceFirst("get", "");
                nameBuilder.append(name).append(",");
            }
        }
        int len = nameBuilder.length();
        String[] names = nameBuilder.substring(0, len - 1).split(",");
        try{
            //获取执行体
            stmt = conn.createStatement();
            //执行查询
            rs = stmt.executeQuery(queryAll);
            while(rs.next()){
                int nameLen = names.length;
                for(int i = 0;i < nameLen;i++){
                    res.append(names[i]).append(":").append(rs.getString(names[i])).append(" ");
                }
                res.append("\n");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            //关闭执行体
            freeStateMemt(stmt, rs);
            //关闭连接
            MysqlConnection.closeConnection();
        }
        return res.toString();
    }

    /**
     * 创建表
     */
    public void createTable(Class<?> clazz) throws Exception {
        //删除表
        deleteTable(clazz);
        //创建数据库的SQL
        StringBuilder createSQL = new StringBuilder(STRING_BUILDER_CAPACITY);
        createSQL.append("CREATE TABLE ");
        //获取数据库名
        String TableName = clazz.getSimpleName();
        createSQL.append(TableName + "(id int not null AUTO_INCREMENT primary key,");
        //获取属性并拼接成SQL
        Method[] methods = clazz.getMethods();
        String PropName,PropType;
        for(Method method : methods){
            String str = method.toString();
            if(!str.contains("throws") && str.contains("get") && !str.contains("Class")){
                PropName = getPropValue(str,2);
                PropType = getPropValue(str,1);
                createSQL.append(PropName + " " + getMapString(PropType) + ",");
            }
        }
//      createSQL.append("primary key(index)");
        createSQL.deleteCharAt(createSQL.length() - 1);
        createSQL.append(")CHARACTER SET utf8;");
        //转化为sql并执行
        String sql = createSQL.toString();
        executeUpdateProcess(sql);
    }
    
    /**
     * 获取属性名称
     * @param str
     * @param type
     * @return
     */
    private String getPropValue(String str,int type){
        String[] args = str.split(" ");
        int len = args.length;
        if(1 == type){
            return args[len - 2].substring(args[len - 2].lastIndexOf('.') + 1);
        }else if(2 == type){
            String str1 = args[len - 1].substring(args[len - 1].lastIndexOf('.') + 1);
            return str1.substring(0, str1.lastIndexOf("(")).replaceFirst("get", "");
        }else{
            return null;
        }
    }

    /**
     * 获取映射的字符串
     * @return
     */
    private String getMapString(String str){
        String upstr = str.toUpperCase();
        for(DBEnum value : DBEnum.values()){
            if(upstr.equalsIgnoreCase(value.name()))
                return value.getValue();
        }
        return null;
    }
    
    /**
     * 删除表
     */
    public void deleteTable(Class<?> clazz) throws Exception {
        //获取表
        String TableName = clazz.getSimpleName();
        //创建执行删除数据库表的SQL
        String deleteSQL = "DROP TABLE IF EXISTS " + TableName;
        //执行
        executeUpdateProcess(deleteSQL);
    }
    
    /**
     * 释放执行体
     * @param stmt
     */
    private void freeStateMemt(Statement stmt,ResultSet rs){
        if(stmt != null)
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        if(rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
    
    /**
     * 执行更新流程
     */
    private void executeUpdateProcess(String sql){
        //获取连接
        Connection conn = MysqlConnection.getConnection();
        //执行体
        Statement stmt = null;
        try{
            //创建执行体
            stmt = conn.createStatement();
            //执行
            stmt.executeLargeUpdate(sql);
        }catch(Exception e){
            System.out.println("执行出现异常");
            e.printStackTrace();
        }finally{
            //释放执行体
            freeStateMemt(stmt,null);
            MysqlConnection.closeConnection();
        }
    }
}

5、DAO工厂类

package com.fan.DB;

/**
 * 获取操作
 * @author Administrator
 *
 */
public class DBHandleFactory{
    public static <T> DBHandleInterfaceImpl<T> getInstance(){
        return new DBHandleInterfaceImpl<T>();
    }
}