I have a spring mvc project and connecting to Maria DB 10.11.0-MariaDB. I am able to connect to remote DB using mysql workbench, But not using spring mvc and c3p0. I am sure about the user name ,password,port and IP, as i am able to connect through the mysql work bench
Below are my DAO and pom xml configurations.
<bean id="Master" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://<DBIP>:3306/master_db"/>
<property name="user" value="xxxxxx"/>
<property name="password" value="xxxxxxx"/>
<property name="initialPoolSize" value="1"/>
<property name="maxPoolSize" value="100"/>
<property name="minPoolSize" value="2"/>
<property name="maxIdleTime" value="3600"/>
<!-- <property name="maxIdleTimeExcessConnections" value="30"/>-->
<property name="numHelperThreads" value="6"/>
</bean>
<dependencies>
<!-- Spring -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.4</version>
</dependency>
<dependency>
<groupId>org.hamcrest</groupId>
<artifactId>hamcrest-all</artifactId>
<version>1.3</version>
</dependency>
<!-- Core utilities used by other modules. Define this if you use Spring
Utility APIs (org.springframework.core.*/org.springframework.util.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Expression Language (depends on spring-core) Define this if you use
Spring Expression APIs (org.springframework.expression.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Bean Factory and JavaBeans utilities (depends on spring-core) Define
this if you use Spring Bean APIs (org.springframework.beans.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Aspect Oriented Programming (AOP) Framework (depends on spring-core,
spring-beans) Define this if you use Spring AOP APIs (org.springframework.aop.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Application Context (depends on spring-core, spring-expression, spring-aop,
spring-beans) This is the central artifact for Spring's Dependency Injection
Container and is generally always defined -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Various Application Context utilities, including EhCache, JavaMail,
Quartz, and Freemarker integration Define this if you need any of these integrations -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Transaction Management Abstraction (depends on spring-core, spring-beans,
spring-aop, spring-context) Define this if you use Spring Transactions or
DAO Exception Hierarchy (org.springframework.transaction.*/org.springframework.dao.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- JDBC Data Access Library (depends on spring-core, spring-beans, spring-context,
spring-tx) Define this if you use Spring's JdbcTemplate API (org.springframework.jdbc.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<!-- Object-to-Relation-Mapping (ORM) integration with Hibernate, JPA,
and iBatis. (depends on spring-core, spring-beans, spring-context, spring-tx)
Define this if you need ORM (org.springframework.orm.*) -->
<!-- <dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency> -->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.3.20</version>
</dependency>
<!-- Object-to-XML Mapping (OXM) abstraction and integration with JAXB,
JiBX, Castor, XStream, and XML Beans. (depends on spring-core, spring-beans,
spring-context) Define this if you need OXM (org.springframework.oxm.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Web application development utilities applicable to both Servlet and
Portlet Environments (depends on spring-core, spring-beans, spring-context)
Define this if you use Spring MVC, or wish to use Struts, JSF, or another
web framework with Spring (org.springframework.web.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Spring MVC for Servlet Environments (depends on spring-core, spring-beans,
spring-context, spring-web) Define this if you use Spring MVC with a Servlet
Container such as Apache Tomcat (org.springframework.web.servlet.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- Spring MVC for Portlet Environments (depends on spring-core, spring-beans,
spring-context, spring-web) Define this if you use Spring MVC with a Portlet
Container (org.springframework.web.portlet.*) -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc-portlet</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.2</version>
</dependency>
<!-- Support for testing Spring applications with tools such as JUnit and
TestNG This artifact is generally always defined with a 'test' scope for
the integration testing framework and unit testing stubs -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20141113</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.13</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity</artifactId>
<version>1.7</version>
</dependency>
<dependency>
<groupId>javax.mail</groupId>
<artifactId>javax.mail-api</artifactId>
<version>1.5.5</version>
</dependency>
</dependencies>
<build>
<finalName>myapp</finalName>
<sourceDirectory>src</sourceDirectory>
<resources>
<resource>
<directory>src</directory>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.4.2</version>
</plugin>
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.1</version>
<configuration>
<url>http://localhost:8080/manager/html</url>
<server>tomcat7</server>
<contextReloadable>true</contextReloadable>
<warSourceDirectory>WebContent/WEB-INF</warSourceDirectory>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.0</version>
<configuration>
<source>${jdk.version}</source>
<target>${jdk.version}</target>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<configuration>
<warSourceDirectory>WebContent</warSourceDirectory>
<failOnMissingWebXml>false</failOnMissingWebXml>
<!-- <webXml>WebContent\WEB-INF\web.xml</webXml> -->
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-javadoc-plugin</artifactId>
<version>2.10.3</version>
<configuration>
<show>private</show>
<nohelp>true</nohelp>
</configuration>
</plugin>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>findbugs-maven-plugin</artifactId>
<version>${findbugs.version}</version>
<configuration>
<!-- Enables analysis which takes more memory but finds more bugs. If
you run out of memory, changes the value of the effort element to 'Low'. -->
<effort>Max</effort>
<failOnError>false</failOnError>
<!-- Reports all bugs (other values are medium and max) -->
<threshold>max</threshold>
<!-- Produces XML report -->
<xmlOutput>true</xmlOutput>
<!-- Configures the directory in which the XML report is created -->
<findbugsXmlOutputDirectory>${project.build.directory}/findbugs</findbugsXmlOutputDirectory>
</configuration>
</plugin>
</plugins>
</build>
<reporting>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>findbugs-maven-plugin</artifactId>
<version>${findbugs.version}</version>
</plugin>
</plugins>
</reporting>
</project>
I am getting below exception:
18:28:04,626 DEBUG JdbcTemplate:435 - Executing SQL query [SELECT * FROM client`]
18:28:04,972 INFO AbstractPoolBackedDataSource:462 - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hgeknaat1k2sq9317m1swv|a737736, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hgeknaat1k2sq9317m1swv|a737736, idleConnectionTestPeriod -> 0, initialPoolSize -> 1, jdbcUrl -> jdbc:mysql://xx.xxx.xx.xx:3306/master_db, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 3600, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 6, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
18:29:00,596 DEBUG SqlUtils:99 - Converting Throwable to SQLException...
com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
detailed exception :
13:47:07,221 DEBUG BasicResourcePool:289 - com.mchange.v2.resourcepool.BasicResourcePool@b3937 config: [start -> 3; min -> 3; max -> 15; inc -> 3; 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]
13:47:07,222 INFO AbstractPoolBackedDataSource:462 - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hgekoiat1piitjos02ot4|2d7d25e2, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hgekoiat1piitjos02ot4|2d7d25e2, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://xx.xxx.xx.xx:3306/master_db, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
13:47:07,223 DEBUG BasicResourcePool:538 - acquire test -- pool size: 0; target_pool_size: 3; desired target? 1
13:47:07,224 DEBUG BasicResourcePool:1291 - awaitAvailable(): [unknown]
13:47:07,224 DEBUG BasicResourcePool:1644 - trace com.mchange.v2.resourcepool.BasicResourcePool@b3937 [managed: 0, unused: 0, excluded: 0]
13:47:08,532 DEBUG BasicResourcePool:1831 - An exception occurred while acquiring a poolable resource. Will retry.
java.sql.SQLException: java.lang.NullPointerException
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1074)
at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3590)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2531)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2301)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: java.lang.NullPointerException
at java.util.TreeMap.put(TreeMap.java:563)
at com.mysql.jdbc.Util.resultSetToMap(Util.java:508)
at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1004)
... 20 more
Solution 1 : Remove these codes, let C3P0 default values to test first. Then you update with the correct values
Solution 2 : You copy/paste this
in your Exception and you get this in your code
Different DB names : price_check_v2_master_db VS master_db. Maybe that's where the problem comes from