idea 连接docker数据库mysql idea连接mysql数据库如何使用

转载

mob6454cc77b8eb 2024-08-26 12:40:36

文章标签 java idea c3p0 bc mysql 文章分类 Docker 云计算

文章目录

作者简介
引言
导航
热门专栏推荐
一、下载驱动并加入项目中
二、编写配置文件
三、编写工具类
四、编写测试类
五、测试运行
小结
导航
热门专栏推荐

作者简介

作者名：编程界明世隐

引言

我本来是一直用eclipse和myeclipse的老程序员了，很多我的粉丝小伙伴都说他们要用idea，问我怎么不用idea，其实明哥觉得用啥开发工具都不是重点，重点是要跟着明哥多学Java知识、多练习，但是作为一个宠粉的人，我怎么能拒绝粉丝的要求呢，于是我偷偷的去学习了一波（拿来吧你），然后就写了这个系列，希望小伙伴们能有所收获，明哥会努力更新的。

一、下载驱动并加入项目中

网上下载Mysql jar包和c3p0的jar包（总共有3个），需要jar包的可以到公众号“编程界明世隐”，回复：“mysql驱动”，下载这几个jar包
打开项目，依次打开目录，web–WEB-INF–lib 加入这几个驱动jar包。
我的jar包名称如下：

mchange-commons-java-0.2.3.4.jar
mysql-connector-java-5.0.8-bin.jar
c3p0-0.9.2.1.jar

引入jar包到项目中

File – Project Structure… – Libraies

idea 连接docker数据库mysql idea连接mysql数据库如何使用_mysql

点击“加号” – Java

idea 连接docker数据库mysql idea连接mysql数据库如何使用_java_02

选择好你项目中lib下的驱动jar包，点击OK

idea 连接docker数据库mysql idea连接mysql数据库如何使用_idea_03

加入后效果图如下，点击OK

idea 连接docker数据库mysql idea连接mysql数据库如何使用_c3p0_04

c3p0 jar包引入方式和mysql一样。

还有mchange-commons-java jar包

二、编写配置文件

在src下放入xml配置：c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <!--
  C3P0的缺省(默认)配置，
  如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource();”这样写就表示使用的是C3P0的缺省(默认)配置信息来创建数据源
  -->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/library?characterEncoding=utf8</property>
    <property name="user">root</property>
    <property name="password">root</property>

    <property name="acquireIncrement">5</property>
    <property name="initialPoolSize">10</property>
    <property name="minPoolSize">5</property>
    <property name="maxPoolSize">20</property>
  </default-config>

  <!--
  C3P0的命名配置，
  如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");”这样写就表示使用的是name是MySQL的配置信息来创建数据源
  -->
  <named-config name="MySQL">
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/library?characterEncoding=utf8</property>
    <property name="user">root</property>
    <property name="password">root</property>

    <property name="acquireIncrement">5</property>
    <property name="initialPoolSize">10</property>
    <property name="minPoolSize">5</property>
    <property name="maxPoolSize">20</property>
  </named-config>

</c3p0-config>

三、编写工具类

import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import com.mchange.v2.c3p0.ComboPooledDataSource;
 
 public class C3P0_Utils {
     
     private static ComboPooledDataSource dataSource = null;
     //在静态代码块中创建数据库连接池
     static{
         try{
        	 dataSource = new ComboPooledDataSource("MySQL");//使用C3P0的命名配置来创建数据源
             System.out.println(dataSource);
         }catch (Exception e) {
             throw new ExceptionInInitializerError(e);
         }
     }
     
     public static Connection getConnection() throws SQLException{
         //从数据源中获取数据库连接
         return dataSource.getConnection();
     }
     
     // 释放资源
     public static void release(Connection conn,Statement st,ResultSet rs){
         if(rs!=null){
             try{
                 //关闭存储查询结果的ResultSet对象
                 rs.close();
             }catch (Exception e) {
                 e.printStackTrace();
             }
             rs = null;
         }
         if(st!=null){
             try{
                 //关闭负责执行SQL命令的Statement对象
                 st.close();
             }catch (Exception e) {
                 e.printStackTrace();
             }
         }
         
         if(conn!=null){
             try{
                 //将Connection连接对象还给数据库连接池
                 conn.close();
             }catch (Exception e) {
                 e.printStackTrace();
             }
         }
     }
 }

四、编写测试类

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class DataSourceTest {

  public void c3p0DataSourceTest() {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    try {
      //获取数据库连接
      conn = C3P0_Utils.getConnection();
      String sql = "select * from user";     // 查询数据的sql语句
      st = (Statement) conn.createStatement();    //创建用于执行静态sql语句的Statement对象，st属局部变量

      rs = st.executeQuery(sql);    //执行sql查询语句，返回查询数据的结果集
      System.out.println("最后的查询结果为：");
      while (rs.next()) { // 判断是否还有下一个数据
        // 根据字段名获取相应的值
        String name = rs.getString("name");
        String no = rs.getString("no");

        //输出查到的记录的各个字段的值
        System.out.println("名字：" + name + "，账号 " + no);

      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      //释放资源
      C3P0_Utils.release(conn, st, rs);
    }
  }

  public static void main(String[] args) {
    new DataSourceTest().c3p0DataSourceTest();
  }
}

五、测试运行

可以在最后几行的位置看到查询结果。

MLog clients using log4j logging.
Initializing c3p0-0.9.2.1 [built 20-March-2013 11:16:28 +0000; debug? true; trace: 10]
MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=1okythyak1w154khck75wa|bebdb06,name=1okythyak1w154khck75wa|bebdb06 registered.
MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=1okythyak1w154khck75wa|bebdb06,name=1okythyak1w154khck75wa|bebdb06 unregistered, in order to be reregistered after update.
MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=1okythyak1w154khck75wa|bebdb06,name=1okythyak1w154khck75wa|bebdb06 registered.
MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=1okythyak1w154khck75wa|bebdb06,name=1okythyak1w154khck75wa|bebdb06 unregistered, in order to be reregistered after update.
MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=1okythyak1w154khck75wa|bebdb06,name=MySQL registered.
com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> MySQL, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1okythyak1w154khck75wa|bebdb06, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/library?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> MySQL, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1okythyak1w154khck75wa|bebdb06, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://localhost:3306/library?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
incremented pending_acquires: 1
Starting acquisition series. Incremented pending_acquires [1],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@5fe5c6f
incremented pending_acquires: 2
Starting acquisition series. Incremented pending_acquires [2],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6979e8cb
incremented pending_acquires: 3
Starting acquisition series. Incremented pending_acquires [3],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@763d9750
incremented pending_acquires: 4
Starting acquisition series. Incremented pending_acquires [4],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@5c0369c4
incremented pending_acquires: 5
Starting acquisition series. Incremented pending_acquires [5],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@2be94b0f
incremented pending_acquires: 6
Starting acquisition series. Incremented pending_acquires [6],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@d70c109
incremented pending_acquires: 7
Starting acquisition series. Incremented pending_acquires [7],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@17ed40e0
incremented pending_acquires: 8
Starting acquisition series. Incremented pending_acquires [8],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@50675690
incremented pending_acquires: 9
Starting acquisition series. Incremented pending_acquires [9],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@31b7dea0
incremented pending_acquires: 10
Starting acquisition series. Incremented pending_acquires [10],  attempts_remaining: 30
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3ac42916
com.mchange.v2.resourcepool.BasicResourcePool@47d384ee config: [start -> 10; min -> 5; max -> 20; inc -> 5; num_acq_attempts -> 30; acq_attempt_delay -> 1000; check_idle_resources_delay -> 0; mox_resource_age -> 0; max_idle_time -> 0; excess_max_idle_time -> 0; destroy_unreturned_resc_time -> 0; expiration_enforcement_delay -> 0; break_on_acquisition_failure -> false; debug_store_checkout_exceptions -> false]
Created new pool for auth, username (masked): 'ro******'.
acquire test -- pool size: 0; target_pool_size: 10; desired target? 1
awaitAvailable(): [unknown]
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 0, unused: 0, excluded: 0]
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 1, unused: 1, excluded: 0]
decremented pending_acquires: 9
Acquisition series terminated successfully. Decremented pending_acquires [9],  attempts_remaining: 30
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 2, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
decremented pending_acquires: 8
Acquisition series terminated successfully. Decremented pending_acquires [8],  attempts_remaining: 30
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 3, unused: 3, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
decremented pending_acquires: 7
Acquisition series terminated successfully. Decremented pending_acquires [7],  attempts_remaining: 30
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 4, unused: 4, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
decremented pending_acquires: 6
Acquisition series terminated successfully. Decremented pending_acquires [6],  attempts_remaining: 30
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 4, unused: 3, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 5, unused: 4, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
decremented pending_acquires: 5
Acquisition series terminated successfully. Decremented pending_acquires [5],  attempts_remaining: 30
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 6, unused: 5, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
decremented pending_acquires: 4
Acquisition series terminated successfully. Decremented pending_acquires [4],  attempts_remaining: 30
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 7, unused: 6, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
decremented pending_acquires: 3
Acquisition series terminated successfully. Decremented pending_acquires [3],  attempts_remaining: 30
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@3eba6356.acquireResource() returning. 
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 8, unused: 7, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)
decremented pending_acquires: 2
Acquisition series terminated successfully. Decremented pending_acquires [2],  attempts_remaining: 30
最后的查询结果为：
名字：超级管理，账号 sa
名字：student001，账号 001
名字：student002，账号 002
名字：管理员1，账号 admin
名字：管理员002，账号 admin2
com.mchange.v2.async.ThreadPoolAsynchronousRunner@2471cca7: Adding task to queue -- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@73035e27
trace com.mchange.v2.resourcepool.BasicResourcePool@47d384ee [managed: 8, unused: 7, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1886e3b)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。