基于Javaweb的小项目

1.数据库连接与关闭

服务端使用Druid连接池进行数据库连接，主要分为两步：
1.配置jdbc.properties文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/qqzone?useUnicode=true&characterEncoding=utf-8
username=root
password=root

2.实现数据库的连接与关闭
【ConnectionUtil.java】

package com.javaweb.qqzone.myssm.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectionUtil {
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    public static Connection createConnection() {
        try {
            //1、加载配置文件
            InputStream is = ConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);

            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            return dataSource.getConnection();

            /*
            //2、读取配置文件
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");*/


            //3、加载驱动
            //Class.forName(driverClass);

            //获取连接
            //Connection connection = DriverManager.getConnection(url, user, password);
            //return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Connection getConnection(){
        Connection conn = threadLocal.get();
        if (conn == null) {
            conn = createConnection();
            threadLocal.set(conn);
        }
        return threadLocal.get();
    }

    //关闭数据库连接
    public static void closeConnection() throws SQLException {
        Connection conn = threadLocal.get();
        if (conn == null) {
            return;
        }
        if (!conn.isClosed()) {
            conn.close();
            //threadLocal.set(null);
            threadLocal.remove();
        }
    }
}

【JDBCUtil.java】

package com.javaweb.qqzone.myssm.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCUtil {
    /**
     * 获取数据库连接
     * @return 数据库的连接
     * @throws Exception
     */
    public static Connection getConnection() {
        /*try {
            //1、加载配置文件
            InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);

            //2、读取配置文件
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");

            //3、加载驱动
            Class.forName(driverClass);

            //获取连接
            Connection connection = DriverManager.getConnection(url, user, password);
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;*/
        return ConnectionUtil.getConnection();
    }


    /**
     * 关闭有结果集的数据库连接
     * @param connection
     * @param psm
     * @param rs
     */
    public static void closeResource(Connection connection, PreparedStatement psm, ResultSet rs) {
        //由于使用ThreadLocal,则不需要在这里关闭资源
    }
}

2.BaseDAO实现对数据库进行CRUD的通用 *** 作

【BaseDAO.java】

package com.javaweb.qqzone.myssm.basedao;

import com.javaweb.qqzone.myssm.exception.DAOException;
import com.javaweb.qqzone.myssm.util.JDBCUtil;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

;

/**
 * DAO:Data(base) Access Object
 * 封装了针对于数据表通用的 *** 作
 */
public abstract class BaseDAO<T> {
    private Class<T> clazz = null;

    /*public BaseDAO() {

    }*/

    {
        //获取当前BaseDAO的子类继承的父类中的泛型
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType type = (ParameterizedType) genericSuperclass;

        Type[] typeArguments = type.getActualTypeArguments();
        clazz = (Class<T>) typeArguments[0];//泛型的第一个参数
    }

    //通用的增删改 *** 作,使用可变形参-----version2.0 (考虑事务)
    public int update(String sql, Object... args) {
        Connection con = null;
        PreparedStatement psm = null;

        try {
            con = JDBCUtil.getConnection();
            System.out.println(con);
            //1、预编译SQL语句，返回PreparedStatement实例
            psm = con.prepareStatement(sql);

            //2、填充占位符
            for (int i = 0; i < args.length; i++) {
                //小心参数的声明错误
                psm.setObject(i + 1, args[i]);
            }

            //3、执行
            return psm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new DAOException("BaseDAO update出错了！");
        } finally {
            //4、资源的关闭
            JDBCUtil.closeResource(con, psm,null);
        }
    }

    /**
     * 通用查询 *** 作，返回表中的一条记录(version2.0 考虑上事务)
     *
     */
    public T getInstance(String sql, Object ... args) {
        Connection con = null;
        PreparedStatement psm = null;
        ResultSet rs = null;
        try {

            con = JDBCUtil.getConnection();

            psm = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                psm.setObject(i + 1, args[i]);
            }

            rs = psm.executeQuery();

            //获取结果集的元数据 String name = "Tom"; Tom是关键，String和name是修饰name的两个元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列表
            int columnCount = rsmd.getColumnCount();

            if (rs.next()) {
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的别名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //给t对象指定的columnName属性赋值columnValue,通过反射
                   /* Field field = clazz.getDeclaredField(columnLabel);

                    field.setAccessible(true);
                    field.set(t, columnValue);*/
                    setValue(t, columnLabel, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new DAOException("BaseDAO 查询出错了！");
        } finally {
            JDBCUtil.closeResource(con, psm, rs);
        }
        return null;
    }


    /**
     * 通过反射技术给obj对象的property属性赋propertyValue值
     * @param obj
     * @param property
     * @param propertyValue
     * @throws Exception
     */
    private void setValue(Object obj, String property, Object propertyValue) throws Exception{
        Class clazz = obj.getClass();

        //获取property这个字符传对应的属性名
        Field field = clazz.getDeclaredField(property);

        if (field != null) {
            //获取当前字段的类型名称
            String typeName = field.getType().getName();
            //判断如果是自定义类型，则需要调用这个自定义类的带一个参数的构造方法，创建出这个自定义的实例对象，然后将实例对象赋值给这个属性
            if (isMyType(typeName)) {
                //假设typeName是“com.javaweb.qqzone.zone.pojo.UserBasic”
                Class typeNameClass = Class.forName(typeName);
                Constructor constructor = typeNameClass.getDeclaredConstructor(java.lang.Integer.class);
                propertyValue = constructor.newInstance(propertyValue);
            }
        }
        field.setAccessible(true);
        field.set(obj, propertyValue);
    }

    private static boolean isMyType(String typeName) {
        return !isNotMyType(typeName);
    }

    private static boolean isNotMyType(String typeName) {
        return "java.lang.Integer".equals(typeName)
                || "java.lang.String".equals(typeName)
                || "java.util.Date".equals(typeName)
                || "java.sql.Date".equals(typeName)
                || "java.lang.Double".equals(typeName);
    }

    /**
     * 通用查询 *** 作，用于返回表中的多条记录构成的集合(version2.0,考虑上事务)
     */
    public List<T> getForList(String sql, Object ... args) {
        Connection con = null;
        PreparedStatement psm = null;
        ResultSet rs = null;
        try {
            con = JDBCUtil.getConnection();
            psm = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                psm.setObject(i + 1, args[i]);
            }

            rs = psm.executeQuery();

            //获取结果集的元数据 String name = "Tom"; Tom是关键，String和name是修饰name的两个元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列表
            int columnCount = rsmd.getColumnCount();

            //创建集合对象
            ArrayList<T> list = new ArrayList<>();

            while (rs.next()) {
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列;给t对象指定的属性赋值
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的别名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    //给t对象指定的columnName属性赋值columnValue,通过反射
                    /*Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);*/
                    setValue(t, columnLabel, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new DAOException("BaseDAO 查询列表出错了！");
        } finally {
            JDBCUtil.closeResource(con, psm, rs);
        }
    }

    /**
     * 用于查询特殊值的通用的方法
     */
    public <E>E getValue(String sql, Object ... args) {
        Connection con = null;
        PreparedStatement psm = null;
        ResultSet rs = null;
        try {
            con = JDBCUtil.getConnection();
            psm = con.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                psm.setObject(i + 1, args[i]);
            }
            rs = psm.executeQuery();

            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new DAOException("BaseDAO 查询特殊值出错了！");
        } finally {
            JDBCUtil.closeResource(con, psm, rs);
        }
        return null;
    }
}