Andrea Girardi - It's my blog!

Tag: mySql

Multiple COUNT select from same table

I fixed the issue in this way:

SELECT R.id_request,
    (SELECT COUNT(*) FROM Flow F 
        WHERE F.id_request = R.id_request AND processStatus = 1) AS flowTotal,
    (SELECT COUNT(*) FROM Flow F 
        WHERE F.id_request = R.id_request AND processStatus = 2) AS flowApproved
FROM Request R

No suitable driver found for jdbc:mysql://localhost:3306/schema

If the exception “Could not get JDBC Connection; nested exception is java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/schema” is raised probably you forgot to add the

<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>

property to your dataSource bean:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">		
	<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
	<property name="url"><value>jdbc:mysql://172.16.0.11:3306/test_vale</value></property>
	<property name="username"><value>root</value></property>
	<property name="password"><value>password</value></property>
</bean>

Row count for all tables in schema

Counting all of the rows in a schema can require code that actually counts the table rows so, how do you count up all of the rows for all tables in a schema?

For Oracle with system user:

SELECT TABLE_NAME, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = UPPER('{schema}');

For mySql:

SELECT TABLE_NAME, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}';

Copyright © 2017 Andrea Girardi – It's my blog!

Theme by Anders NorenUp ↑