（3）JDBC——————回想大学写的JDBC，我酸了

大学期间自己写的jdbc和工作后写的jdbc看完后我酸了！接下来介绍一下这个模板的层次结构和代码的解析。

• 这是一套比较完整的JDBC模板，用的是德鲁伊连接池，项目结构为：
  
  
• 主要层次结构为：
  
• 代码如下
• 【BaseDAO.java】

package com.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 定义一个用来被继承的对数据库进行基本操作的Dao
 *
 * @author Tommey周
 * @create 2020-04-11 13:02
 */
public abstract class BaseDao<T> {

    private QueryRunner queryRunner = new QueryRunner();

    // 定义一个变量来接收泛型的类型
    private Class<T> type;

    // 获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
    public BaseDao() {
        // 获取子类的类型
        Class clazz = this.getClass();
        // 获取父类的类型，ParameterizedType表示的是带泛型的类型，getGenericSuperclass()用来获取当前类的父类的类型
        ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
        // 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型，这个方法会返回一个Type的数组
        Type[] types = parameterizedType.getActualTypeArguments();
        // 获取具体的泛型的类型
        this.type = (Class<T>) types[0];
    }

    /**
     * 通用的增删改操作
     */
    public int update(Connection conn, String sql, Object... params) {
        int count = 0;
        try {
            count = queryRunner.update(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 查询一个对象
     */
    public T queryBean(Connection conn, String sql, Object... params) {
        T t = null;
        try {
            t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return t;
    }

    /**
     * 查询所有对象
     */
    public List<T> queryBeanList(Connection conn, String sql, Object... params) {
        List<T> list = null;
        try {
            list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询单一值的方法，例如：count(1)这样的sql语句
     */
    public Object queryValue(Connection conn, String sql, Object... params) {
        Object count = null;
        try {
            count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}

• 【UserDao.java】

package com.dao;

import com.bean.Page;
import com.bean.User;

import java.sql.Connection;
import java.util.List;

/**
 * @author Tommey周
 * @create 2020-04-11 13:01
 */
public interface UserDao {

    /**
     * 根据User对象中的用户名和密码从数据库中获取一条记录
     */
    User queryUser(Connection conn, User user);

    /**
     * 根据User对象中的条件从数据库中获取多条记录
     */
    List<User> queryList(Connection conn, User user);

    /**
     * 根据User对象中的条件分页从数据库中获取多条记录
     */
    Page<User> queryPageList(Connection conn, Page<User> page, User user);

    /**
     * 根据User对象中的用户名从数据库中获取一条记录
     */
    boolean checkUsername(Connection conn, User user);

    /**
     * 向数据库中插入User对象
     */
    int saveUser(Connection conn, User user);

    /**
     * 向数据库中修改User对象
     */
    int updateUser(Connection conn, User user);

    /**
     * 向数据库中删除User对象
     */
    int deleteUser(Connection conn, User user);

}

• 【UserDaoImpl .java】

package com.dao.Impl;

import com.bean.Page;
import com.bean.User;
import com.dao.BaseDao;
import com.dao.UserDao;

import java.sql.Connection;
import java.util.List;

/**
 * @author Tommey周
 * @create 2020-04-11 13:01
 */
public class UserDaoImpl extends BaseDao<User> implements UserDao {
    @Override
    public User queryUser(Connection conn, User user) {
        User bean = null;
        String sql = "select id,password,name,address,phone from user where name = ? and password = ?";
        //调用BaseDao中查询一个对象的方法
        bean = queryBean(conn, sql, user.getName(), user.getPassword());
        return bean;
    }

    @Override
    public List<User> queryList(Connection conn, User user) {
        List<User> users = null;
        String sql = "select id,password,name,address,phone from user where name = ?";
        users = queryBeanList(conn, sql, user.getName());
        return users;
    }

    @Override
    public Page<User> queryPageList(Connection conn, Page<User> page, User user) {
        // 获取数据库中的总记录数
        String sql = "select count(*) from user where name like concat('%',?,'%')";
        // 调用BaseDao中获取一个单一值的方法
        long totalRecord = (long) queryValue(conn, sql, user.getName());
        // 将总记录数设置都page对象中
        page.setTotalRecord((int) totalRecord);
        // 获取当前页中的记录存放的List
        String sql2 = "select id,password,name,address,phone from  user where name like concat('%',?,'%') limit ?,?";
        // 调用BaseDao中获取一个集合的方法
        List<User> userList = queryBeanList(conn, sql2, user.getName(), page.getCurrent(), page.getSize());
        // 将这个List设置到page对象中
        page.setList(userList);
        return page;
    }

    @Override
    public boolean checkUsername(Connection conn, User user) {
        User bean = null;
        String sql = "select id,password,name,address,phone from user where name = ?";
        //调用BaseDao中检验用户是否存在的方法
        bean = queryBean(conn, sql, user.getName());
        return bean != null;
    }

    @Override
    public int saveUser(Connection conn, User user) {
        String sql = "insert into user(password,name,address,phone) values(?,?,?,?)";
        //调用BaseDao中通用的增删改的方法
        return update(conn, sql, user.getPassword(), user.getName(), user.getAddress(), user.getPhone());
    }

    @Override
    public int updateUser(Connection conn, User user) {
        String sql = "update user set password = ?,name = ?,address = ?,phone = ? where id = ?";
        //调用BaseDao中通用的增删改的方法
        return update(conn, sql, user.getPassword(), user.getName(), user.getAddress(), user.getPhone(), user.getId());
    }

    @Override
    public int deleteUser(Connection conn, User user) {
        String sql = "delete from user where id = ?";
        //调用BaseDao中通用的增删改的方法
        return update(conn, sql, user.getId());
    }
}

• 【User.java】

ackage com.bean;

import java.sql.Blob;

/**
 * @author Tommey周
 * @create 2020-04-09 22:15
 */
public class User {
    private Integer id;
    private String password;
    private String name;
    private String address;
    private String phone;
    private Blob photo;

    public User() {
    }

    public User(Integer id, String password, String name, String address, String phone) {
        this.id = id;
        this.password = password;
        this.name = name;
        this.address = address;
        this.phone = phone;
    }

    public Integer getId() {
        return id;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

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

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Blob getPhoto() {
        return photo;
    }

    public void setPhoto(Blob photo) {
        this.photo = photo;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", address='" + address + '\'' +
                ", phone='" + phone + '\'' +
                ", photo=" + photo +
                '}';
    }
}

• 【Page.java】

package com.bean;

import java.util.List;

/**
 * @author Tommey周
 * @create 2020-04-11 13:03
 */
public class Page<T> {

    private static final int PAGE_CURRENT = 1; // 当前页码
    public static final int PAGE_SIZE = 4; // 每页显示的记录数

    private List<T> list; // 每页查到的记录存放的集合
    private Integer current; // 当前页
    private Integer size; //每页显示的条数
    private int totalRecord; // 总记录数，通过查询数据库得到


    public Page(Integer current, Integer size) {
        this.current = (PAGE_CURRENT - 1) * PAGE_SIZE;
        this.size = PAGE_SIZE;
        if (current != null && size != null) {
            this.current = (current - 1) * size;
            this.size = size;
        }
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getCurrent() {
        if(this.current != null){
            return current;
        }else {
            return (PAGE_CURRENT - 1) * Page.PAGE_SIZE;
        }
    }

    public void setCurrent(Integer current) {
        this.current = current;
    }

    public Integer getSize() {
        if (this.size != null){
            return size;
        }else {
            return PAGE_SIZE;
        }

    }

    public void setSize(Integer size) {
        this.size = size;
    }

    public Integer getTotalRecord() {
        return totalRecord;
    }

    public void setTotalRecord(Integer totalRecord) {
        this.totalRecord = totalRecord;
    }

    @Override
    public String toString() {
        return "Page{" +
                "list=" + list +
                ", 当前页为：" + current +
                ", 每页显示的条数为：" + size +
                ", 总记录数为：" + totalRecord +
                '}';
    }
}

• 【DruidUtils.java】

package com.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author Tommey周
 * @create 2020-04-14 23:28
 */
public class DruidUtils {

    private static final String PROPERTIES_NAME = "druid.properties";
    private static DataSource source;

    static {
        try {
            Properties pro = new Properties();
            pro.load(ClassLoader.getSystemClassLoader().getResourceAsStream(PROPERTIES_NAME));
            source = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws Exception {
        return source.getConnection();
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs) {

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        try {
            System.out.println(DruidUtils.getConnection());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

• 【druid.properties】

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=root
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall

---

下一章，（1）H5+C3——————H5，选择器，盒子模型，布局，图标和文本样式