basedao

一个基于数据库连接的模板（自己写的）

使用的是Druid连接池

配置文件

druid.properties

username = root
password = 123456
maxActive = 100
initialSize = 5
maxWait = 100
url = jdbc:mysql://localhost:3306/basedao
driverClassName = com.mysql.jdbc.Driver

注解ID

package demo.zlt.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Id {
    String value();
}

接口：BaseDao

package demo.zlt.base;

import java.sql.SQLException;
import java.util.List;

public interface BaseDao<T> {
    List<T> finAll() ;

    T findById(int id);

    int add(T entity);

    int delete(int id);

    int update(T entity);
}

实现类：BaseDaoImpl

package demo.zlt.base.impl;

import demo.zlt.annotation.Id;
import demo.zlt.base.BaseDao;
import demo.zlt.util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BaseDaoImpl<T> implements BaseDao<T> {

    //得到数据库对象
    private Connection conn;

    private QueryRunner qr;

    //获取字节码对象
    private Class<T> clazz;

    //获得数据库表
    private String tableName;

    public BaseDaoImpl() {
        conn = DruidUtil.getConnection();

        qr = new QueryRunner();

        Type type = this.getClass().getGenericSuperclass();
        ParameterizedType pt = (ParameterizedType) type;
        clazz = (Class<T>) pt.getActualTypeArguments()[0];

        tableName = clazz.getSimpleName();
    }

    @Override
    public List<T> finAll() {
        String sql = "select * from " + tableName;
        List<T> list = null;
        try {
            list = qr.query(conn, sql, new BeanListHandler<T>(clazz));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public T findById(int id) {
        String IdfieldName = null;

        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields
        ) {
            if (field.isAnnotationPresent(Id.class)) {
                IdfieldName = field.getName();
            }
        }
        String sql = "select * from " + tableName + " where " + IdfieldName + " = ?;";
        T entity = null;
        try {
            entity = qr.query(conn, sql, new BeanHandler<>(clazz), new Object[]{id});
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(sql);
        return entity;
    }

    @Override
    public int add(T entity) {
        String sql = "insert into " + tableName + " values(";
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            if (i != fields.length - 1) {
                sql += "?,";
            } else {
                sql += "?);";
            }
        }

        Object[] objects = new Object[fields.length];
        Object FieldValue = null;
        PropertyDescriptor pd = null;
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            try {
                pd = new PropertyDescriptor(field.getName(), clazz);
                FieldValue = pd.getReadMethod().invoke(entity, null);
                objects[i] = FieldValue;
            } catch (IntrospectionException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        int result = 0;
        try {
            result = qr.update(conn, sql, objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int delete(int id) {

        Field[] fields = clazz.getDeclaredFields();
        String IdFieldName = null;

        //System.out.println(fields[0].getName());一般都是默认id为第一个下标
        for (Field field : fields
        ) {
            if (field.isAnnotationPresent(Id.class)) {
                IdFieldName = field.getName();
            }
        }

        String sql = "delete from " + tableName + " where " + IdFieldName + " = ?;";
        int result = 0;
        try {
            result = qr.update(conn, sql, new Object[]{id});
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(sql);
        return result;
    }

    @Override
    public int update(T entity) {
        int result = 0;

        String IdfieldName = null;
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];

        }

        Object[] objects = new Object[fields.length];
        Object FieldValue = null;
        PropertyDescriptor pd = null;
        String sql = "update " + tableName + " set ";
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            if (field.isAnnotationPresent(Id.class)) {
                IdfieldName = field.getName();
            } else if (i != fields.length - 1) {
                sql += field.getName() + " = ? ,";
            } else {
                sql += field.getName() + " = ? ";
            }
            try {
                pd = new PropertyDescriptor(field.getName(), clazz);
                FieldValue = pd.getReadMethod().invoke(entity, null);
                objects[i] = FieldValue;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        //前面传的参数顺序一定要清楚，[user_id, user_name, user_pwd]
        //但事实上，我们需要的是[user_name, user_pwd, user_id]
        Object objs = objects[0];
        objects[0] = objects[objects.length - 1];
        objects[objects.length - 1] = objs;


        sql += "where " + IdfieldName + " = ?;";
        try {
            result = qr.update(conn, sql, objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(sql);
        return result;
    }
}

连接池DruidUtil

package demo.zlt.util;


import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;


import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DruidUtil {

    private static DruidDataSource dataSource;

    static {
        try {
            Properties pro = new Properties();
            pro.load(DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static DruidDataSource getDataSource() {
        return dataSource;
    }

    public static void close(ResultSet rs, Statement stat, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stat != null) {
                stat.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}