jdbc连接进阶_基础_线程池_事务

最新推荐文章于 2023-10-24 16:38:12 发布

Java天命人

最新推荐文章于 2023-10-24 16:38:12 发布

阅读量399

点赞数 3

分类专栏：后端文章标签： jdbc 线程池 filter 事务 threadlocal

本文链接：https://blog.csdn.net/XiaHeShun/article/details/80641465

版权

后端专栏收录该内容

20 篇文章 0 订阅

订阅专栏

00 连接之基

导入mysql-connector-java-5.1.12-bin.jar包

package d0608;

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

/**
 * 文件：Test.java
 * 描述：数据连接
 * 换行符：Unix
 * 字符格式：UTF-8
 */
public class Test {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, user, password);
        PreparedStatement pst = conn.prepareStatement("select * from tree");
        ResultSet rs = pst.executeQuery();
        while(rs.next()) {
            System.out.println(rs.getString(3));
        }
        rs.close();
        pst.close();
        conn.close();   
    }

}

01 提取工具

导入mysql-connector-java-5.1.12-bin.jar包

编写配置文件jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=root

编写工具类JdbcUtil.java

package d0608;

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

/**
 * 文件：JdbcUtil.java
 * 描述：编写工具类
 * 换行符：Unix
 * 字符格式：UTF-8
 */

public class JdbcUtil {

    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {

        Properties prop = new Properties();
        try {
            prop.load(JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        driver = prop.getProperty("driver");
        url = prop.getProperty("url");
        user = prop.getProperty("user");
        password = prop.getProperty("password");

        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

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

    public static PreparedStatement getPrepaedStatement(Connection conn, String sql) throws SQLException {
        PreparedStatement pst = conn.prepareStatement(sql);
        return pst; 
    }

    public static void close(ResultSet rs, PreparedStatement pst, Connection conn) throws SQLException {
        if (null != rs) {
            rs.close();
        }
        if (null != pst) {
            pst.close();
        }
        if (null != conn) {
            conn.close();
        }
    }

}

测试类Test.java

package d0608;
/**
 * 工具类使用说明 
 * 换行符：Unix
 * 字符格式：UTF-8
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {

    public static void main(String[] args) throws SQLException {
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement pst = JdbcUtil.getPrepaedStatement(conn, "select * from tree");
        ResultSet rs = pst.executeQuery();
        while(rs.next()) {
            System.out.println(rs.getString(3));
        }
        JdbcUtil.close(rs, pst, conn);
    }

}

02 精简冗余代码·dbutils

导相应的包

mysql-connector-java-5.1.12-bin.jar
commons-logging-1.1.3.jar
commons-dbutils-1.6.jar

编写配置文件jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=root

工具类编写：

package d0608;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbutils.QueryRunner;

/**
 * 工具类使用说明 
 * 换行符：Unix
 * 字符格式：UTF-8
 */

public class JdbcUtil {

    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {

        Properties prop = new Properties();
        try {
            prop.load(JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        driver = prop.getProperty("driver");
        url = prop.getProperty("url");
        user = prop.getProperty("user");
        password = prop.getProperty("password");

        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

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

    public static QueryRunner getQueryRunner() {
        return new QueryRunner();
    }

}

数据库对应pojo类：

package d0608;

public class Tree {

    private Integer id;
    private Integer pId;
    private String name;
    public Tree() {
        super();
        // TODO Auto-generated constructor stub
    }
    public Tree(Integer id, Integer pId, String name) {
        super();
        this.id = id;
        this.pId = pId;
        this.name = name;
    }
    @Override
    public String toString() {
        return "Tree [id=" + id + ", pId=" + pId + ", name=" + name + "]";
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Integer getpId() {
        return pId;
    }
    public void setpId(Integer pId) {
        this.pId = pId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

}

测试使用类：

package d0608;

import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

/**
 * 测试获取数据
 * 换行符：Unix
 * 字符格式：UTF-8
 */
public class Test {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        /**
         * QueryRunner的一些用法
         * ArrayHandler：把结果集中的第一行数据转成对象数组。
         * ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。   
         * BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
         * BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
         * ColumnListHandler：将结果集中某一列的数据存放到List中。
         * KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
         * MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
         * MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
         */

        // 返回集合
        List<Tree> list = JdbcUtil.getQueryRunner().query(JdbcUtil.getConnection(),"select * from tree", new BeanListHandler<>(Tree.class));

        for (Tree tree : list) {
            System.out.println(tree.getName());
        }

        // 返回单个对象
        Tree tree = JdbcUtil.getQueryRunner().query(JdbcUtil.getConnection(), "select * from tree where id = 1", new BeanHandler<>(Tree.class));
        System.out.println(tree.getName());

        // 返回基本的数据类型
        Object[] id = JdbcUtil.getQueryRunner().query(JdbcUtil.getConnection(), "select id from tree where id = 1", new ArrayHandler());
        System.out.println((Integer)id[0]);

        // 带参数的修改,返回受影响的行数
        int update = JdbcUtil.getQueryRunner().update(JdbcUtil.getConnection(), "update tree set name=? where id=?", "test", 100);
        System.out.println(update);

    }

}

03 线程池·c3p0

如果自己写模拟写一下线程池的话，事先生成多个connection，保存到队列里，然后出队入队，就基本是线程池复用资源的思想了，这里直接使用c3p0

导入相关的包文件：

c3p0-0.9.1.2.jar
commons-dbutils-1.6.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.12-bin.jar

配置文件c3p0-config.xml：

<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test
        </property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">6</property>
        <property name="maxIdleTime">1000</property>
    </default-config>

    <named-config name="oracle_config">
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_demo</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">scot</property>
        <property name="password">tiger</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">6</property>
        <property name="maxIdleTime">1000</property>
    </named-config>

</c3p0-config>

编写工具类：

package d0608;

import org.apache.commons.dbutils.QueryRunner;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtil {

    public static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    public static QueryRunner getQueryRunner() {
        return new QueryRunner(dataSource);
    }

}

测试类：

package d0608;

import java.sql.SQLException;

import org.apache.commons.dbutils.handlers.ArrayHandler;

/**
 * 测试获取数据
 * 换行符：Unix
 * 字符格式：UTF-8
 */
public class Test {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        Object[] query = JdbcUtil.getQueryRunner().query("select count(*) from tree", new ArrayHandler());
        System.out.println(query[0]);

    }

}

04 事务处理·service层·c3p0&ThreadLocal

在开发中，对数据库的多个表或者对一个表中的多条数据执行更新操作时要保证对多个更新操作要么同时成功，要么都不成功，这就涉及到对多个更新操作的事务管理问题了。在开发中，DAO层的职责应该只涉及到基本的CRUD，不涉及具体的业务操作，所以在开发中DAO层出现事务处理方法是一种不好的设计。故，在此提取工具类，使其方便在service层进行包裹dao服务来处理事务。

在此，使用ThreadLocal类进行改造，ThreadLocal一个容器，向这个容器存储的对象，在当前线程范围内都可以取得出来，向ThreadLocal里面存东西就是向它里面的Map存东西的，然后ThreadLocal把这个Map挂到当前的线程底下，这样Map就只属于这个线程了。

导入相关的包文件：

c3p0-0.9.1.2.jar
commons-dbutils-1.6.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.12-bin.jar

配置文件c3p0-config.xml：

<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test
        </property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">6</property>
        <property name="maxIdleTime">1000</property>
    </default-config>

    <named-config name="oracle_config">
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_demo</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">scot</property>
        <property name="password">tiger</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">6</property>
        <property name="maxIdleTime">1000</property>
    </named-config>

</c3p0-config>

编写工具类：

package d0608;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtil {

    public static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    public static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    // 简化事务方法
    public static void startTransaction(TransactionProcess transactionProcess ) {
        try {
            start();
            // 调用包裹事务过程的接口的process方法
            transactionProcess.process();
            commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            try {
                rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    // 包裹事务的过程接口
    public interface TransactionProcess{
        void process();
    }

    // 获取查询
    public static QueryRunner getQueryRunner() {
        return new QueryRunner();
    }

    // 获取Connection
    public static Connection getConnection() throws SQLException {
        Connection conn = threadLocal.get();
        if (conn == null) {
            conn = dataSource.getConnection();
            threadLocal.set(conn);
        }
        return conn;
    }

    // 开启事物
    public static void start() throws SQLException {
        getConnection().setAutoCommit(false);
    }

    // 提交事务
    public static void commit() throws SQLException {
        getConnection().commit();
    }

    // 回滚事务
    public static void rollback() throws SQLException {
        getConnection().rollback();
    }

    // 关闭资源
    public static void close() throws SQLException {
        threadLocal.remove();
    }

}

测试使用类：

package d0608;

import java.sql.SQLException;

/**
 * 测试获取数据
 * 换行符：Unix
 * 字符格式：UTF-8
 * 使用说明：如果需要使用事务，则直接调用工具类中的方法，将事务的过程包裹即可
 */
public class Test {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        JdbcUtil.startTransaction(new JdbcUtil.TransactionProcess() {
            @Override
            public void process() {
                // TODO Auto-generated method stub
                // 将需要事务处理的过程包裹在这里
                try {
                    // 正常的插入操作
                    JdbcUtil.getQueryRunner().update(JdbcUtil.getConnection(),"insert into tree(id,pId,name) values(123,123,'123')");
                    // 模拟异常的产生
                    int a = 1/0;
                    // 正常的插入操作
                    JdbcUtil.getQueryRunner().update(JdbcUtil.getConnection(),"insert into tree(id,pId,name) values(234,234,'234')");
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        });

        // 结果，由于非零的的异常，事务进行回滚，未插入任何一条数据，符合事务的要求

    }

}

05 事务处理·filter层·c3p0&ThreadLocal&Filter

放一个网络图（不是我画的）：

在这里我首先说一下，为什么要使用这么麻烦的拦截器，而不在service层直接使用对需要处理的事物进行包裹起来处理。

第一：我的第一感觉是，我看到教学视频上是说事务处理放在filter中，就像处理response和request的字符过滤器一样，为什么我觉得这样写的代码更难受啊，感觉怎想不通了，这样每次的操作都会事务过滤一下，不是会很麻烦嘛。我很疑问为什么不是写在业务处理层，我给写成了工具类，直接在业务处理层进行使用，这样就可以该事务的时候，包裹一下事务的过程就好了。

第二：经过网络的查询，我发现，什么样的都有，有dao层的，有service层的，有filter层的。然后我去问一些群里的同学，他们说在公司中，比较多的是在service层的，但是这样确实是可以在filter里面做多数据源事务切换，很方便。

导入相关的包文件：

c3p0-0.9.1.2.jar
commons-dbutils-1.6.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.12-bin.jar

配置文件c3p0-config.xml：

<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test
        </property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">6</property>
        <property name="maxIdleTime">1000</property>
    </default-config>

    <named-config name="oracle_config">
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_demo</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">scot</property>
        <property name="password">tiger</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">6</property>
        <property name="maxIdleTime">1000</property>
    </named-config>

</c3p0-config>

编写工具类：

package d0608;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtil {

    public static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    public static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    // 简化事务方法
    public static void startTransaction(TransactionProcess transactionProcess ) {
        try {
            start();
            // 调用包裹事务过程的接口的process方法
            transactionProcess.process();
            commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            try {
                rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    // 包裹事务的过程接口
    public interface TransactionProcess{
        void process();
    }

    // 获取查询
    public static QueryRunner getQueryRunner() {
        return new QueryRunner();
    }

    // 获取Connection
    public static Connection getConnection() throws SQLException {
        Connection conn = threadLocal.get();
        if (conn == null) {
            conn = dataSource.getConnection();
            threadLocal.set(conn);
        }
        return conn;
    }

    // 开启事物
    public static void start() throws SQLException {
        getConnection().setAutoCommit(false);
    }

    // 提交事务
    public static void commit() throws SQLException {
        getConnection().commit();
    }

    // 回滚事务
    public static void rollback() throws SQLException {
        getConnection().rollback();
    }

    // 关闭资源
    public static void close() throws SQLException {
        threadLocal.remove();
    }

}

编写过滤器filter：

package d0608;

import java.io.IOException;
import java.sql.SQLException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

import d0608.JdbcUtil.TransactionProcess;

/**
 * Servlet Filter implementation class TransactionFilter
 */
public class TransactionFilter implements Filter {

    public void destroy() {
        // TODO Auto-generated method stub
        System.out.println("filter...destroy()...");
    }

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        // TODO Auto-generated method stub
        JdbcUtil.startTransaction(new TransactionProcess() {

            @Override
            public void process() {
                // TODO Auto-generated method stub
                try {
                    chain.doFilter(request, response);
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (ServletException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        });

    }


    public void init(FilterConfig fConfig) throws ServletException {
        // TODO Auto-generated method stub
        System.out.println("filter...init()...");
    }

}

编写servlet测试类：

package d0608;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class TestServlet
 */
public class TestServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) {
        // TODO Auto-generated method stub

        try {
            JdbcUtil.getQueryRunner().update(JdbcUtil.getConnection(), "insert into tree(id,pId,name) values(123,123,'jjj')");
            //  模拟异常的操作
            int a = 1/0;
            JdbcUtil.getQueryRunner().update(JdbcUtil.getConnection(), "insert into tree(id,pId,name) values(234,234,'hhh')");
            System.out.println("testServlet...");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            throw new RuntimeException("出错了");
        }

    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

编写servlet和filter的web.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>d0608</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <filter>
    <display-name>TransactionFilter</display-name>
    <filter-name>TransactionFilter</filter-name>
    <filter-class>d0608.TransactionFilter</filter-class>
  </filter>
  <filter-mapping>
    <filter-name>TransactionFilter</filter-name>
    <!--这里是过滤的规则-->
    <url-pattern>/*</url-pattern>
  </filter-mapping>
  <servlet>
    <description></description>
    <display-name>TestServlet</display-name>
    <servlet-name>TestServlet</servlet-name>
    <servlet-class>d0608.TestServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>TestServlet</servlet-name>
    <url-pattern>/testServlet</url-pattern>
  </servlet-mapping>
</web-app>

关于filter的过滤规则url-pattern的填写：

过滤器的xml匹配写法：

1.完全匹配
  <url-pattern>/test/list.do</url-pattern>  

2.路径匹配
  <url-pattern>/*</url-pattern> 匹配根路径下的全部请求

3.扩展名匹配
  <url-pattern>*.do</url-pattern> struts1
  <url-pattern>*.html</url-pattern> 匹配全部html结尾的请求
  <url-pattern>*</url-pattern> 不能用*，否则报错

Java天命人

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
jdbc连接进阶_基础_线程池_事务

00 连接之基导入mysql-connector-java-5.1.12-bin.jar包package d0608;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sq...
复制链接

扫一扫

专栏目录