时间:2021-07-01 10:21:17 帮助过:14人阅读
4.自定义C3P0工具类:导入包:mysql-connector-java-5.1.39-bin.jar,c3p0-0.9.1.2.jar
- <span style="color: rgb(0, 128, 128)"> 1</span> <span style="color: rgb(0, 128, 0)">/*配置文件必须到src目录下,且配置文件的名称(c3p0-config.xml)不能改变,程序中不会出现,但是会自动读取.
- <span style="color: rgb(0, 128, 128)"> 2</span> 如果报超时错误,有可能是1.包没导入/2.文件没找到/3.数据库服务未开启*/</span>
- <span style="color: rgb(0, 128, 128)"> 3</span>
- <span style="color: rgb(0, 128, 128)"> 4</span> <span style="color: rgb(0, 0, 255)">import</span> java.sql.Connection;
- <span style="color: rgb(0, 128, 128)"> 5</span> <span style="color: rgb(0, 0, 255)">import</span> javax.sql.DataSource;
- <span style="color: rgb(0, 128, 128)"> 6</span> <span style="color: rgb(0, 0, 255)">import</span> com.mchange.v2.c3p0.ComboPooledDataSource;
- <span style="color: rgb(0, 128, 128)"> 7</span>
- <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 {
- <span style="color: rgb(0, 128, 128)"> 9</span> <span style="color: rgb(0, 128, 0)">// 定义DataSource</span>
- <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>
- <span style="color: rgb(0, 128, 128)"> 11</span>
- <span style="color: rgb(0, 128, 128)"> 12</span> <span style="color: rgb(0, 128, 0)">// 获取Connection</span>
- <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 {
- <span style="color: rgb(0, 128, 128)"> 14</span> Connection c = ds.getConnection();
- <span style="color: rgb(0, 128, 128)"> 15</span> <span style="color: rgb(0, 0, 255)">return</span> c;
- <span style="color: rgb(0, 128, 128)"> 16</span> }
- <span style="color: rgb(0, 128, 128)"> 17</span> <span style="color: rgb(0, 128, 0)">// 返回连接池对象</span>
- <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() {
- <span style="color: rgb(0, 128, 128)"> 19</span> <span style="color: rgb(0, 0, 255)">return</span> ds;
- <span style="color: rgb(0, 128, 128)"> 20</span> }
- <span style="color: rgb(0, 128, 128)"> 21</span> }
- <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工具类)
- <span style="color: rgb(0, 128, 128)"> 1</span> <span style="color: rgb(0, 128, 0)">//定义一个类,要使用到BeanHandler类</span>
- <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 {
- <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;
- <span style="color: rgb(0, 128, 128)"> 4</span> <span style="color: rgb(0, 0, 255)">private</span> String name;
- <span style="color: rgb(0, 128, 128)"> 5</span> <span style="color: rgb(0, 128, 0)">//空参构造--Eclipse快捷键:alt+shift+c</span>
- <span style="color: rgb(0, 128, 128)"> 6</span> <span style="color: rgb(0, 0, 255)">public</span> Vegetables() {
- <span style="color: rgb(0, 128, 128)"> 7</span> <span style="color: rgb(0, 0, 255)">super</span>();
- <span style="color: rgb(0, 128, 128)"> 8</span> }
- <span style="color: rgb(0, 128, 128)"> 9</span> <span style="color: rgb(0, 128, 0)">//满参构造--Eclipse快捷键:alt+shift+o</span>
- <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) {
- <span style="color: rgb(0, 128, 128)"> 11</span> <span style="color: rgb(0, 0, 255)">super</span>();
- <span style="color: rgb(0, 128, 128)"> 12</span> <span style="color: rgb(0, 0, 255)">this</span>.id = id;
- <span style="color: rgb(0, 128, 128)"> 13</span> <span style="color: rgb(0, 0, 255)">this</span>.name = name;
- <span style="color: rgb(0, 128, 128)"> 14</span> }
- <span style="color: rgb(0, 128, 128)"> 15</span> <span style="color: rgb(0, 128, 0)">//覆写toString--Eclipse快捷键alt+shift+s</span>
- <span style="color: rgb(0, 128, 128)"> 16</span> @Override
- <span style="color: rgb(0, 128, 128)"> 17</span> <span style="color: rgb(0, 0, 255)">public</span> String toString() {
- <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>";
- <span style="color: rgb(0, 128, 128)"> 19</span> }
- <span style="color: rgb(0, 128, 128)"> 20</span> <span style="color: rgb(0, 128, 0)">//getter/setter--Eclipse快捷键alt+shift+r</span>
- <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() {
- <span style="color: rgb(0, 128, 128)"> 22</span> <span style="color: rgb(0, 0, 255)">return</span> id;
- <span style="color: rgb(0, 128, 128)"> 23</span> }
- <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) {
- <span style="color: rgb(0, 128, 128)"> 25</span> <span style="color: rgb(0, 0, 255)">this</span>.id = id;
- <span style="color: rgb(0, 128, 128)"> 26</span> }
- <span style="color: rgb(0, 128, 128)"> 27</span> <span style="color: rgb(0, 0, 255)">public</span> String getName() {
- <span style="color: rgb(0, 128, 128)"> 28</span> <span style="color: rgb(0, 0, 255)">return</span> name;
- <span style="color: rgb(0, 128, 128)"> 29</span> }
- <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) {
- <span style="color: rgb(0, 128, 128)"> 31</span> <span style="color: rgb(0, 0, 255)">this</span>.name = name;
- <span style="color: rgb(0, 128, 128)"> 32</span> }
- <span style="color: rgb(0, 128, 128)"> 33</span> }
- <span style="color: rgb(0, 128, 128)"> 34</span>
- <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>
- <span style="color: rgb(0, 128, 128)"> 36</span> <span style="color: rgb(0, 0, 255)">import</span> java.util.List;
- <span style="color: rgb(0, 128, 128)"> 37</span> <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.QueryRunner;
- <span style="color: rgb(0, 128, 128)"> 38</span> <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.handlers.BeanHandler;
- <span style="color: rgb(0, 128, 128)"> 39</span> <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.handlers.BeanListHandler;
- <span style="color: rgb(0, 128, 128)"> 40</span> <span style="color: rgb(0, 0, 255)">import</span> org.apache.commons.dbutils.handlers.ScalarHandler;
- <span style="color: rgb(0, 128, 128)"> 41</span>
- <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 {
- <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>
- <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 {
- <span style="color: rgb(0, 128, 128)"> 45</span> <span style="color: rgb(0, 128, 0)">// 获取QueryRunner对象</span>
- <span style="color: rgb(0, 128, 128)"> 46</span> QueryRunner qr = <span style="color: rgb(0, 0, 255)">new</span> QueryRunner(MyC3P0Utils.getDataSource());
- <span style="color: rgb(0, 128, 128)"> 47</span> <span style="color: rgb(0, 128, 0)">// 插入数据</span>
- <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>";
- <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>");
- <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>");
- <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>");
- <span style="color: rgb(0, 128, 128)"> 52</span> System.out.println(i1 + i2 + i3);
- <span style="color: rgb(0, 128, 128)"> 53</span>
- <span style="color: rgb(0, 128, 128)"> 54</span> <span style="color: rgb(0, 128, 0)">// 修改</span>
- <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>";
- <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>");
- <span style="color: rgb(0, 128, 128)"> 57</span> System.out.println(i4);
- <span style="color: rgb(0, 128, 128)"> 58</span>
- <span style="color: rgb(0, 128, 128)"> 59</span> <span style="color: rgb(0, 128, 0)">// 查询1--ScalarHandler</span>
- <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>";
- <span style="color: rgb(0, 128, 128)"> 61</span> <span style="color: rgb(0, 128, 0)">// 定义容器接收 ScalarHandler</span>
- <span style="color: rgb(0, 128, 128)"> 62</span> ScalarHandler sh = <span style="color: rgb(0, 0, 255)">new</span> ScalarHandler();
- <span style="color: rgb(0, 128, 128)"> 63</span> String s = qr.query(querySQL, sh);
- <span style="color: rgb(0, 128, 128)"> 64</span> System.out.println(s);<span style="color: rgb(0, 128, 0)">// 只返回一个值</span>
- <span style="color: rgb(0, 128, 128)"> 65</span>
- <span style="color: rgb(0, 128, 128)"> 66</span> <span style="color: rgb(0, 128, 0)">// 查询2--BeanHandler</span>
- <span style="color: rgb(0, 128, 128)"> 67</span> String querySQL2 = "<span style="color: rgb(139, 0, 0)">SELECT * FROM dbutil</span>";
- <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>);
- <span style="color: rgb(0, 128, 128)"> 69</span> Vegetables v = qr.query(querySQL2, bh);
- <span style="color: rgb(0, 128, 128)"> 70</span> System.out.println(v);<span style="color: rgb(0, 128, 0)">// 只返回一个实例对象</span>
- <span style="color: rgb(0, 128, 128)"> 71</span>
- <span style="color: rgb(0, 128, 128)"> 72</span> <span style="color: rgb(0, 128, 0)">// 查询3--BeanListHandler</span>
- <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>);
- <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>
- <span style="color: rgb(0, 128, 128)"> 75</span> <span style="color: rgb(0, 0, 255)">for</span> (Vegetables v1 : list) {
- <span style="color: rgb(0, 128, 128)"> 76</span> System.out.println(v1);
- <span style="color: rgb(0, 128, 128)"> 77</span> }
- <span style="color: rgb(0, 128, 128)"> 78</span> <span style="color: rgb(0, 128, 0)">// 删除</span>
- <span style="color: rgb(0, 128, 128)"> 79</span> String deleteSQL = "<span style="color: rgb(139, 0, 0)">DELETE FROM dbutil</span>";
- <span style="color: rgb(0, 128, 128)"> 80</span> <span style="color: rgb(0, 0, 255)">int</span> i5 = qr.update(deleteSQL);
- <span style="color: rgb(0, 128, 128)"> 81</span> System.out.println(i5);
- <span style="color: rgb(0, 128, 128)"> 82</span> }
- <span style="color: rgb(0, 128, 128)"> 83</span> }
- <span style="color: rgb(0, 128, 128)"> 84</span>
DataSource--DBCP--C3P0--DBUtils
标签:第一条 文件 注意 选项 database src 静态 pool set