JDBC流程总结篇（模板）

1.在项目中加载架包

2.在数据库中创建数据库（db_notepad）和表（t_income）

create database db_notepad;
create table t_income(
    income_id int auto_increment primary key,
    income_type varchar(20) not null,
    income_description varchar(20) not null,
    income_amount double not null

);

3.配置文件（db.properties)

className:com.mysql.cj.jdbc.Driver
url:jdbc:mysql://localhost:3306/db_notepad
user:数据库用户名
password:数据库密码

4.JDBC工具类（JDBCUtil）

	public static Connection connection = null;
    //public static PreparedStatement preparedStatement = null;
    //public static ResultSet resultSet = null;
    public static Properties properties = null;

    //获取配置文件
    static {
        properties = new Properties();
        try {
            properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //封装链接
    public static Connection getConnection(){
        String className = properties.getProperty("className");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        try {
            //1.注册驱动
            Class.forName(className);
            //2.获取链接
            connection = DriverManager.getConnection(url, user, password);
        }catch (Exception e){
            e.printStackTrace();
        }
        return connection;

    }

    //关闭Connection
    public static void close(Connection connection){
        try {
            if (connection != null){
                connection.close();
            }
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    //关闭Connection和PreparedStatement
    public static void close(Connection connection, PreparedStatement preparedStatement){
        try {
            if (preparedStatement != null){
                preparedStatement.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        close(connection);
    }

    //关闭Connection、PreparedStatement和ResultSet
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        try {
            if (resultSet != null){
                resultSet.close();
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        close(connection,preparedStatement);
    }

5.封装表的CRUD（BaseDao）

		public int executeUpdate(String sql,Object...args) throws Exception {
        //获取链接
        Connection connection = JDBCUtil.getConnection();

        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //设值？占位符的值
        if (args != null && args.length > 0){
            for (int i = 0; i < args.length; i++){
                preparedStatement.setObject(i+1,args[i]);
            }
        }

        //执行sql
        int len = preparedStatement.executeUpdate();

        //关闭资源
        JDBCUtil.close(connection,preparedStatement);

        return len;

    }

    public <T> List<T> excuteQuery(Class<T> clazz, String sql, Object...args) throws Exception{
        //获取连接
        Connection connection = JDBCUtil.getConnection();

        //预编译
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //设置？占位符的值
        if (args != null && args.length > 0){
            for (int i = 0; i < args.length; i++){
                preparedStatement.setObject(i+1, args[i]);
            }
        }
        //提交sql并获得结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        //获取结果集元数据对象
        ResultSetMetaData metaData = resultSet.getMetaData();
        //获取结果集列数
        int columnCount = metaData.getColumnCount();

        ArrayList<T> list = new ArrayList<>();
        //遍历结果集
        while (resultSet.next()){
            //反射，一行代表一个对象
            T t = clazz.newInstance();

            //获取每个单元格中的值
            for (int i = 1; i <= columnCount; i++){
                //每一行的1个单元格值
                Object value = resultSet.getObject(i);

                //获取字段名或别名
                String columnName = metaData.getColumnLabel(i);
                Field field = clazz.getDeclaredField(columnName);
                //突破封装，可以获得private的属性
                field.setAccessible(true);
                field.set(t,value);
            }
            list.add(t);
        }
        JDBCUtil.close(connection,preparedStatement,resultSet);
        return list;

    }

6.封装JavaBean（IncomeModel）

private int incomeId;
    //类型
    private String incomeType;
    //描述
    private String incomeDescription;
    //金额
    private double incomeAmount;

    public IncomeModel() {
    }

    public IncomeModel(String incomeType, String incomeDescription, double incomeAmount) {
        this.incomeType = incomeType;
        this.incomeDescription = incomeDescription;
        this.incomeAmount = incomeAmount;
    }

    public IncomeModel(int incomeId, String incomeType, String incomeDescription, double incomeAmount) {
        this.incomeId = incomeId;
        this.incomeType = incomeType;
        this.incomeDescription = incomeDescription;
        this.incomeAmount = incomeAmount;
    }

    public int getIncomeId() {
        return incomeId;
    }

    public void setIncomeId(int incomeId) {
        this.incomeId = incomeId;
    }

    public String getIncomeType() {
        return incomeType;
    }

    public void setIncomeType(String incomeType) {
        this.incomeType = incomeType;
    }

    public String getIncomeDescription() {
        return incomeDescription;
    }

    public void setIncomeDescription(String incomeDescription) {
        this.incomeDescription = incomeDescription;
    }

    public double getIncomeAmount() {
        return incomeAmount;
    }

    public void setIncomeAmount(double incomeAmount) {
        this.incomeAmount = incomeAmount;
    }

    @Override
    public String toString() {
        return "IncomeModel{" +
                "incomeId=" + incomeId +
                ", incomeType='" + incomeType + '\'' +
                ", incomeDescription='" + incomeDescription + '\'' +
                ", incomeAmount=" + incomeAmount +
                '}';
    }

7.Dao接口（IncomeDao）

	//查询所有
    List<IncomeModel> selectAll();

    //添加数据
    int insert(IncomeModel incomeModel);

8.继承BaseDao类并实现Dao接口

	@Override
    public List<IncomeModel> selectAll() {
            String sql = "select income_id incomeId, income_type incomeType, income_description incomeDescription, income_amount incomeAmount from t_income";
        try {
            return excuteQuery(IncomeModel.class,sql,null);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public int insert(IncomeModel incomeModel) {
        String sql = "insert into t_income(income_type, income_description, income_amount) values (?,?,?) ";
        try {
            return executeUpdate(sql,incomeModel.getIncomeType(),incomeModel.getIncomeDescription(),incomeModel.getIncomeAmount());
        } catch (Exception e){
            throw new RuntimeException(e);
        }
    }

9.main测试运行

IncomeModel incomeModel = new IncomeModel("兼职","张三", 100);
        IncomeDao incomeDao = new IncomeDaoImpl();
        incomeDao.insert(incomeModel);

        List<IncomeModel> incomeModels = incomeDao.selectAll();
        for (IncomeModel income : incomeModels){
            System.out.println(income);
        }