当前位置：Gxlcms > 数据库问题 > DataSource--DBCP--C3P0--DBUtils

DataSource--DBCP--C3P0--DBUtils

时间：2021-07-01 10:21:17 帮助过：14人阅读

//需要首先导入包:mysql-connector-java-5.1.39-bin.jar(mysql驱动),commons-dbcp-1.4.jar,commons-pool-1.5.6.jar 2 import java.io.FileInputStream; 3 import java.sql.Connection; 4 import java.sql.SQLException; 5 import java.util.Properties; 6 import javax.sql.DataSource; 7 import org.apache.commons.dbcp.BasicDataSourceFactory; 8 9 public class MyDBCPUtils { 10 private static DataSource ds = null; 11 //使用静态代码块技术初始化ds对象(成员变量) 12 static { 13 // 获取DataSource 14 try { 15 FileInputStream in = new FileInputStream("dbcp.properties"); 16 Properties p = new Properties(); 17 p.load(in); 18 ds = BasicDataSourceFactory.createDataSource(p);//面向DBCP核心类,调用createDataSource()方法,返回DataSource 19 } catch (Exception e) { 20 e.printStackTrace(); 21 } 22 } 23 // 获取Connection 24 public static Connection getConnection() { 25 Connection c = null; 26 try { 27 c = ds.getConnection(); 28 } catch (SQLException e) { 29 e.printStackTrace(); 30 } 31 return c; 32 } 33 }
三.C3P0
     1.介绍:
     C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。c3p0与dbcp区别:c3p0有自动回收空闲连接功能,dbcp做不到.
     2.使用步骤:
         1):下载并解压zip;
         2):复制核心jar包到工程中即可:c3p0-0.9.1.2.jar;
         3):添加到本地;
         4):复制配置文件必须到src目录下,且配置文件的名称(c3p0-config.xml)不能改变;
         5):直接创建ComboPooledDataSource核心类对象即可使用;
     3.核心类 ComboPooledDataSource 类(com.mchange.v2.c3p0)
         定义:public final class ComboPooledDataSource extends com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
                                                     implements PooledDataSource, java.io.Serializable, javax.naming.Referenceable
              public interface PooledDataSource extends javax.sql.DataSource
    
                 ComboPooledDataSource类实现了PooledDataSource接口,该接口继承了DataSource接口;
         构造方法:
                 public ComboPooledDataSource()
         配置文件选项(补充):                               

    4.自定义C3P0工具类:导入包:mysql-connector-java-5.1.39-bin.jar,c3p0-0.9.1.2.jar
    

  1. <span style="color: rgb(0, 128, 128)">  1</span>  <span style="color: rgb(0, 128, 0)">/*配置文件必须到src目录下,且配置文件的名称(c3p0-config.xml)不能改变,程序中不会出现,但是会自动读取.
  2. <span style="color: rgb(0, 128, 128)">  2</span>      如果报超时错误,有可能是1.包没导入/2.文件没找到/3.数据库服务未开启*/</span>
  3. <span style="color: rgb(0, 128, 128)">  3</span> 
  4. <span style="color: rgb(0, 128, 128)">  4</span>         <span style="color: rgb(0, 0, 255)">import</span> java.sql.Connection;
  5. <span style="color: rgb(0, 128, 128)">  5</span>          <span style="color: rgb(0, 0, 255)">import</span> javax.sql.DataSource;
  6. <span style="color: rgb(0, 128, 128)">  6</span>         <span style="color: rgb(0, 0, 255)">import</span> com.mchange.v2.c3p0.ComboPooledDataSource;
  7. <span style="color: rgb(0, 128, 128)">  7</span> 
  8. <span style="color: rgb(0, 128, 128)">  8</span>          <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">class</span> MyC3P0Utils {
  9. <span style="color: rgb(0, 128, 128)">  9</span>              <span style="color: rgb(0, 128, 0)">// 定义DataSource</span>
  10. <span style="color: rgb(0, 128, 128)"> 10</span>              <span style="color: rgb(0, 0, 255)">private</span> <span style="color: rgb(0, 0, 255)">static</span> DataSource ds = <span style="color: rgb(0, 0, 255)">new</span> ComboPooledDataSource(); <span style="color: rgb(0, 128, 0)">// 多态,子类实例赋值给父类的父类</span>
  11. <span style="color: rgb(0, 128, 128)"> 11</span> 
  12. <span style="color: rgb(0, 128, 128)"> 12</span>              <span style="color: rgb(0, 128, 0)">// 获取Connection</span>
  13. <span style="color: rgb(0, 128, 128)"> 13</span>              <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">static</span> Connection getConnection() <span style="color: rgb(0, 0, 255)">throws</span> Exception {
  14. <span style="color: rgb(0, 128, 128)"> 14</span>                  Connection c = ds.getConnection();
  15. <span style="color: rgb(0, 128, 128)"> 15</span>                  <span style="color: rgb(0, 0, 255)">return</span> c;
  16. <span style="color: rgb(0, 128, 128)"> 16</span>              }
  17. <span style="color: rgb(0, 128, 128)"> 17</span>              <span style="color: rgb(0, 128, 0)">// 返回连接池对象</span>
  18. <span style="color: rgb(0, 128, 128)"> 18</span>             <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">static</span> DataSource getDataSource() {
  19. <span style="color: rgb(0, 128, 128)"> 19</span>                  <span style="color: rgb(0, 0, 255)">return</span> ds;
  20. <span style="color: rgb(0, 128, 128)"> 20</span>              }
  21. <span style="color: rgb(0, 128, 128)"> 21</span>          }
  22. <span style="color: rgb(0, 128, 128)"> 22</span>

四.DBUtils--工具类,commons-dbutils-1.6.jar
     1.概述:Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。
     2.使用步骤:
         1):下载并解压zip;
         2):复制核心jar包到工程中即可:commons-dbutils-1.6.jar; 
         3):添加到本地;
         4):直接创建核心类对象即可使用;
     3.核心类3个:
         1):QueryRunner,用于封装sql执行对象;
         2):ResultSetHandler用于封装ResultSet;
         3):DButils, 用于事务的控制;
     4.QueryRunner
         定义:public class QueryRunner extends AbstractQueryRunner
         构造方法:
             public QueryRunner()
             public QueryRunner(DataSource ds)
         常用方法:
         public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)throws SQLException{}:
         public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)throws SQLException{}:
        
         public int update(Connection conn, String sql, Object... params)throws SQLException{}:Execute an SQL INSERT, UPDATE, or DELETE query.返回值为数据表里受影响的行数(int类型);
         public int update(String sql, Object... params)throws SQLException{}:Executes the given INSERT, UPDATE, or DELETE SQL statement. The Connection is retrieved from the DataSource set in the constructor. This Connection must be in auto-commit mode or the update will not be saved.
     5.ResultSetHandler
         定义:public interface ResultSetHandler<T>
         子类:根据取出数据的不同选择不同的容器接收.前三个较为常用.
             BeanHandler:将结果集中第一条记录封装到一个指定的javaBean中.
             BeanListHandler:将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中.
             ScalarHandler:它是用于单个数据。例如select count(*) from 表操作.
             ArrayHandler:将结果集中的第一条记录封装到一个Object[]数组中,数组中的每一个元素就是这条记录中的每一个字段的值.
             ArrayListHandler:将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中.
             ColumnListHandler:将结果集中指定的列的字段值，封装到一个List集合中.
             MapHandler:将结果集中第一条记录封装到了Map<    String,Object>集合中,key就是字段名称,value就是字段值.
             MapListHandler:将结果集中每一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值，在将这些Map封装到List集合中.
             KeyedHandler:将结果集中每一条记录封装到Map<String,Object>,在将这个map集合做为另一个Map的value,另一个Map集合的key是指定的字段的值.

    6.DbUtils
         定义:public final class DbUtils extends Object
         构造方法:public DbUtils()
         静态方法:
         public static void close(Connection conn/ResultSet rs/Statement stmt)throws SQLException{}:此方法有重载(共3个),可以关闭Connection/Statement/ResultSet.
         public static void closeQuietly(Connection conn,Statement stmt,ResultSet rs){}:关闭资源,避免异常,此方法有重载(共4个),可以一次关闭3个,也可以分别关闭.

        public static void commitAndClose(Connection conn)throws SQLException{}:Commits a Connection then closes it, avoid closing if null.
         public static void commitAndCloseQuietly(Connection conn){}:Commits a Connection then closes it, avoid closing if null and hide any SQLExceptions that occur.

        public static boolean loadDriver(ClassLoader classLoader,String driverClassName){}:Loads and registers a database driver class. If this succeeds, it returns true, else it returns false.有重载,可以直接传入driverClassName.

        public static void rollback(Connection conn)throws SQLException{}:Rollback any changes made on the given connection.
         public static void rollbackAndClose(Connection conn)throws SQLException{}:Performs a rollback on the Connection then closes it, avoid closing if null.
         public static void rollbackAndCloseQuietly(Connection conn){}:Performs a rollback on the Connection then closes it, avoid closing if null and hide any SQLExceptions that occur.
     7.代码演示:导包mysql-connector-java-5.1.39-bin.jar,commons-dbutils-1.6.jar,c3p0-0.9.1.2.jar(使用了前面自定义的MyC3P0工具类)

  1. <span style="color: rgb(0, 128, 128)">  1</span> <span style="color: rgb(0, 128, 0)">//定义一个类,要使用到BeanHandler类</span>
  2. <span style="color: rgb(0, 128, 128)">  2</span> <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">class</span> Vegetables {
  3. <span style="color: rgb(0, 128, 128)">  3</span>      <span style="color: rgb(0, 0, 255)">private</span> <span style="color: rgb(0, 0, 255)">int</span> id;
  4. <span style="color: rgb(0, 128, 128)">  4</span>      <span style="color: rgb(0, 0, 255)">private</span> String name;
  5. <span style="color: rgb(0, 128, 128)">  5</span>      <span style="color: rgb(0, 128, 0)">//空参构造--Eclipse快捷键:alt+shift+c</span>
  6. <span style="color: rgb(0, 128, 128)">  6</span>      <span style="color: rgb(0, 0, 255)">public</span> Vegetables() {
  7. <span style="color: rgb(0, 128, 128)">  7</span>          <span style="color: rgb(0, 0, 255)">super</span>();
  8. <span style="color: rgb(0, 128, 128)">  8</span>      }
  9. <span style="color: rgb(0, 128, 128)">  9</span>      <span style="color: rgb(0, 128, 0)">//满参构造--Eclipse快捷键:alt+shift+o</span>
  10. <span style="color: rgb(0, 128, 128)"> 10</span>      <span style="color: rgb(0, 0, 255)">public</span> Vegetables(<span style="color: rgb(0, 0, 255)">int</span> id, String name) {
  11. <span style="color: rgb(0, 128, 128)"> 11</span>          <span style="color: rgb(0, 0, 255)">super</span>();
  12. <span style="color: rgb(0, 128, 128)"> 12</span>          <span style="color: rgb(0, 0, 255)">this</span>.id = id;
  13. <span style="color: rgb(0, 128, 128)"> 13</span>          <span style="color: rgb(0, 0, 255)">this</span>.name = name;
  14. <span style="color: rgb(0, 128, 128)"> 14</span>      }
  15. <span style="color: rgb(0, 128, 128)"> 15</span>      <span style="color: rgb(0, 128, 0)">//覆写toString--Eclipse快捷键alt+shift+s</span>
  16. <span style="color: rgb(0, 128, 128)"> 16</span>      @Override
  17. <span style="color: rgb(0, 128, 128)"> 17</span>      <span style="color: rgb(0, 0, 255)">public</span> String toString() {
  18. <span style="color: rgb(0, 128, 128)"> 18</span>          <span style="color: rgb(0, 0, 255)">return</span> "<span style="color: rgb(139, 0, 0)">Vegetables [id=</span>" + id + "<span style="color: rgb(139, 0, 0)">, name=</span>" + name + "<span style="color: rgb(139, 0, 0)">]</span>";
  19. <span style="color: rgb(0, 128, 128)"> 19</span>      }
  20. <span style="color: rgb(0, 128, 128)"> 20</span>      <span style="color: rgb(0, 128, 0)">//getter/setter--Eclipse快捷键alt+shift+r</span>
  21. <span style="color: rgb(0, 128, 128)"> 21</span>      <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">int</span> getId() {
  22. <span style="color: rgb(0, 128, 128)"> 22</span>          <span style="color: rgb(0, 0, 255)">return</span> id;
  23. <span style="color: rgb(0, 128, 128)"> 23</span>      }
  24. <span style="color: rgb(0, 128, 128)"> 24</span>      <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">void</span> setId(<span style="color: rgb(0, 0, 255)">int</span> id) {
  25. <span style="color: rgb(0, 128, 128)"> 25</span>          <span style="color: rgb(0, 0, 255)">this</span>.id = id;
  26. <span style="color: rgb(0, 128, 128)"> 26</span>      }
  27. <span style="color: rgb(0, 128, 128)"> 27</span>      <span style="color: rgb(0, 0, 255)">public</span> String getName() {
  28. <span style="color: rgb(0, 128, 128)"> 28</span>          <span style="color: rgb(0, 0, 255)">return</span> name;
  29. <span style="color: rgb(0, 128, 128)"> 29</span>      }
  30. <span style="color: rgb(0, 128, 128)"> 30</span>      <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">void</span> setName(String name) {
  31. <span style="color: rgb(0, 128, 128)"> 31</span>          <span style="color: rgb(0, 0, 255)">this</span>.name = name;
  32. <span style="color: rgb(0, 128, 128)"> 32</span>      }
  33. <span style="color: rgb(0, 128, 128)"> 33</span>  }
  34. <span style="color: rgb(0, 128, 128)"> 34</span> 
  35. <span style="color: rgb(0, 128, 128)"> 35</span> <span style="color: rgb(0, 0, 255)">import</span> huguangqin.com.cnblogs_MyC3P0Utils.MyC3P0Utils;<span style="color: rgb(0, 128, 0)">//使用了前面自定义的工具类</span>
  36. <span style="color: rgb(0, 128, 128)"> 36</span> <span style="color: rgb(0, 0, 255)">import</span> java.util.List;
  37. <span style="color: rgb(0, 128, 128)"> 37</span>  <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.QueryRunner;
  38. <span style="color: rgb(0, 128, 128)"> 38</span>  <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.handlers.BeanHandler;
  39. <span style="color: rgb(0, 128, 128)"> 39</span>  <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.handlers.BeanListHandler;
  40. <span style="color: rgb(0, 128, 128)"> 40</span>  <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.handlers.ScalarHandler;
  41. <span style="color: rgb(0, 128, 128)"> 41</span> 
  42. <span style="color: rgb(0, 128, 128)"> 42</span> <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">class</span> Demo {
  43. <span style="color: rgb(0, 128, 128)"> 43</span>      @SuppressWarnings("<span style="color: rgb(139, 0, 0)">all</span>")<span style="color: rgb(0, 128, 0)">//压制黄线警告</span>
  44. <span style="color: rgb(0, 128, 128)"> 44</span>     <span style="color: rgb(0, 0, 255)">public</span> <span style="color: rgb(0, 0, 255)">static</span> <span style="color: rgb(0, 0, 255)">void</span> main(String[] args) <span style="color: rgb(0, 0, 255)">throws</span> Exception {
  45. <span style="color: rgb(0, 128, 128)"> 45</span>          <span style="color: rgb(0, 128, 0)">// 获取QueryRunner对象</span>
  46. <span style="color: rgb(0, 128, 128)"> 46</span>         QueryRunner qr = <span style="color: rgb(0, 0, 255)">new</span> QueryRunner(MyC3P0Utils.getDataSource());
  47. <span style="color: rgb(0, 128, 128)"> 47</span>          <span style="color: rgb(0, 128, 0)">// 插入数据</span>
  48. <span style="color: rgb(0, 128, 128)"> 48</span>         String inertSQL = "<span style="color: rgb(139, 0, 0)">INSERT INTO dbutil(id,name) VALUES(?,?)</span>";
  49. <span style="color: rgb(0, 128, 128)"> 49</span>          <span style="color: rgb(0, 0, 255)">int</span> i1 = qr.update(inertSQL, <span style="color: rgb(0, 0, 255)">null</span>, "<span style="color: rgb(139, 0, 0)">白菜</span>");
  50. <span style="color: rgb(0, 128, 128)"> 50</span>          <span style="color: rgb(0, 0, 255)">int</span> i2 = qr.update(inertSQL, <span style="color: rgb(0, 0, 255)">null</span>, "<span style="color: rgb(139, 0, 0)">豆腐</span>");
  51. <span style="color: rgb(0, 128, 128)"> 51</span>          <span style="color: rgb(0, 0, 255)">int</span> i3 = qr.update(inertSQL, <span style="color: rgb(0, 0, 255)">null</span>, "<span style="color: rgb(139, 0, 0)">磨菇</span>");
  52. <span style="color: rgb(0, 128, 128)"> 52</span>          System.out.println(i1 + i2 + i3);
  53. <span style="color: rgb(0, 128, 128)"> 53</span> 
  54. <span style="color: rgb(0, 128, 128)"> 54</span>         <span style="color: rgb(0, 128, 0)">// 修改</span>
  55. <span style="color: rgb(0, 128, 128)"> 55</span>         String updateSQL = "<span style="color: rgb(139, 0, 0)">UPDATE dbutil SET name = ? WHERE id = 1</span>";
  56. <span style="color: rgb(0, 128, 128)"> 56</span>          <span style="color: rgb(0, 0, 255)">int</span> i4 = qr.update(updateSQL, "<span style="color: rgb(139, 0, 0)">小白菜</span>");
  57. <span style="color: rgb(0, 128, 128)"> 57</span>          System.out.println(i4);
  58. <span style="color: rgb(0, 128, 128)"> 58</span> 
  59. <span style="color: rgb(0, 128, 128)"> 59</span>         <span style="color: rgb(0, 128, 0)">// 查询1--ScalarHandler</span>
  60. <span style="color: rgb(0, 128, 128)"> 60</span>          String querySQL = "<span style="color: rgb(139, 0, 0)">SELECT name FROM dbutil WHERE name Like ‘___‘</span>";
  61. <span style="color: rgb(0, 128, 128)"> 61</span>          <span style="color: rgb(0, 128, 0)">// 定义容器接收 ScalarHandler</span>
  62. <span style="color: rgb(0, 128, 128)"> 62</span>          ScalarHandler sh = <span style="color: rgb(0, 0, 255)">new</span> ScalarHandler();
  63. <span style="color: rgb(0, 128, 128)"> 63</span>          String s = qr.query(querySQL, sh);
  64. <span style="color: rgb(0, 128, 128)"> 64</span>          System.out.println(s);<span style="color: rgb(0, 128, 0)">// 只返回一个值</span>
  65. <span style="color: rgb(0, 128, 128)"> 65</span> 
  66. <span style="color: rgb(0, 128, 128)"> 66</span>         <span style="color: rgb(0, 128, 0)">// 查询2--BeanHandler</span>
  67. <span style="color: rgb(0, 128, 128)"> 67</span>          String querySQL2 = "<span style="color: rgb(139, 0, 0)">SELECT * FROM dbutil</span>";
  68. <span style="color: rgb(0, 128, 128)"> 68</span>          BeanHandler bh = <span style="color: rgb(0, 0, 255)">new</span> BeanHandler(Vegetables.<span style="color: rgb(0, 0, 255)">class</span>);
  69. <span style="color: rgb(0, 128, 128)"> 69</span>          Vegetables v = qr.query(querySQL2, bh);
  70. <span style="color: rgb(0, 128, 128)"> 70</span>          System.out.println(v);<span style="color: rgb(0, 128, 0)">// 只返回一个实例对象</span>
  71. <span style="color: rgb(0, 128, 128)"> 71</span> 
  72. <span style="color: rgb(0, 128, 128)"> 72</span>         <span style="color: rgb(0, 128, 0)">// 查询3--BeanListHandler</span>
  73. <span style="color: rgb(0, 128, 128)"> 73</span>          BeanListHandler blh = <span style="color: rgb(0, 0, 255)">new</span> BeanListHandler(Vegetables.<span style="color: rgb(0, 0, 255)">class</span>);
  74. <span style="color: rgb(0, 128, 128)"> 74</span>          List<Vegetables> list = qr.query(querySQL2, blh);<span style="color: rgb(0, 128, 0)">// 返回List</span>
  75. <span style="color: rgb(0, 128, 128)"> 75</span>          <span style="color: rgb(0, 0, 255)">for</span> (Vegetables v1 : list) {
  76. <span style="color: rgb(0, 128, 128)"> 76</span>              System.out.println(v1);
  77. <span style="color: rgb(0, 128, 128)"> 77</span>          }
  78. <span style="color: rgb(0, 128, 128)"> 78</span>          <span style="color: rgb(0, 128, 0)">// 删除</span>
  79. <span style="color: rgb(0, 128, 128)"> 79</span>         String deleteSQL = "<span style="color: rgb(139, 0, 0)">DELETE FROM dbutil</span>";
  80. <span style="color: rgb(0, 128, 128)"> 80</span>          <span style="color: rgb(0, 0, 255)">int</span> i5 = qr.update(deleteSQL);
  81. <span style="color: rgb(0, 128, 128)"> 81</span>          System.out.println(i5);
  82. <span style="color: rgb(0, 128, 128)"> 82</span>      }
  83. <span style="color: rgb(0, 128, 128)"> 83</span>  }
  84. <span style="color: rgb(0, 128, 128)"> 84</span>

DataSource--DBCP--C3P0--DBUtils

标签：第一条   文件   注意   选项   database   src   静态   pool   set   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！