当前位置: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   

人气教程排行