Jdbc的简单封装

由于操作Jdbc的代码重复度过高，所以抽取Jdbc工具类:JDBCUtils

*目的:简化后期的操作

*分析:

1.抽取方法获取注册和连接对象

*需求:不想传递参数(麻烦)，还得保证工具类的通用性。

*解决方案:配置文件

通过定义一个jdbc.properties来放置文件

//jdbc.properities
url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL
user=root
password=123456
driver=com.mysql.cj.jdbc.Driver

操作1:通过静态代码来读取资源文件

static {
    //读取资源文件，获取值

    try {
        //1.创建Properites集合类
        Properties mPro = new Properties();
        //2.加载文件
       ClassLoader mCls=JDBCUtils.class.getClassLoader();
//以src为相对的根路径
 URL res=mCls.getResource("jdbc.properties");
 String path=res.getPath();
 System.out.println(path);
        //3.获取数据，赋值
        //mPro是k_v结构
        url = mPro.getProperty("uri");
        user = mPro.getProperty("user");
        pwd = mPro.getProperty("pwd");
        driver = mPro.getProperty("driver");
        //注册驱动
        Class.forName(driver);
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }

}

操作2:获取连接

  public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, pwd);
    }

操作3:释放资源（实现方法的重载）

  /**
     * 释放资源
     */
    public static void close(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


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

    }

    /**
     * 释放资源 方法的重载
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


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


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

    }

JDBCUtils代码一览和代码使用

package cn.liz.util;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC的工具类
 */
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String pwd;
    private static String driver;

    /**
     * 获取连接
     * @return 连接对象
     * @author Liz
     * 采用配置文件进行文件的读取，文件的读取只需要一次即可，使用静态代码块
     *
     */
    static {
        //读取资源文件，获取值

        try {
            //1.创建Properites集合类

            Properties mPro = new Properties();
            //2.加载文件
            //获取src路径下文件的方式-->ClassLoader
           ClassLoader mCls=JDBCUtils.class.getClassLoader();
           //以src为相对的根路径
            URL res=mCls.getResource("jdbc.properties");
            String path=res.getPath();
            System.out.println(path);
            mPro.load(new FileReader(path));
            //3.获取数据，赋值
            //mPro是k_v结构
            url = mPro.getProperty("url");
            user = mPro.getProperty("user");
            pwd = mPro.getProperty("pwd");
            driver = mPro.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, pwd);
    }

    /**
     * 释放资源
     */
    public static void close(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


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

    }

    /**
     * 释放资源 方法的重载
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


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


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

    }


}

 

package cn.liz.jdbc;

import cn.liz.util.JDBCUtils;

import java.sql.*;

/**
 * 事务操作
 */
public class JdbcTestWork {
    //转账

    public static void main(String[] args) {
        Connection mConn = null;
        PreparedStatement mPstmt_drop = null;
        PreparedStatement mPstmt_add = null;
        try {
            //1.获取连接对象
            mConn = JDBCUtils.getConnection();
            //开启事务
            mConn.setAutoCommit(false);
            //2.定义sql,获取执行对象
            String sql_drop = "update account set balance=balance-? where id =?";
            String sql_add = "update account set balance=balance+ ? where id =?";
            mPstmt_drop = mConn.prepareStatement(sql_drop);
            mPstmt_add = mConn.prepareStatement(sql_add);
            //3.设置参数
            //张三-500
            mPstmt_drop.setDouble(1, 500);
            mPstmt_drop.setInt(2, 1);
            //set bug

            //李四+500
            mPstmt_add.setDouble(1, 500);
            mPstmt_add.setInt(2, 2);
            //4.执行参数
            mPstmt_add.executeUpdate();
            mPstmt_drop.executeUpdate();
            //事务的提交
            mConn.commit();

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if(mConn!=null){
                    //如果一旦出现任何问题，回滚
                    mConn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            JDBCUtils.close(mPstmt_add, mConn);
            JDBCUtils.close(mPstmt_drop, null);
        }
    }
}