Your browser was unable to load all of the resources. They may have been blocked by your firewall, proxy or browser configuration.
Press Ctrl+F5 or Ctrl+Shift+R to have your browser try again.

JasperReportsIntegration with PostgreSQL #13

ViktorAfnt · · author

Hi,
I tested JasperReportsIntegration 2.5.0.0 with Oracle, it was ok. But with PostgreSQL was no luck.
There's postgresql-42.2.14.jar in \lib directory.
application.properties:
#============================================================
[datasource:default]
type=jdbc
name=default
url=jdbc:oracle:thin:@x.x.x.x:1521/ordb
username=usr
password=pw
#============================================================ [datasource:postgresql]
type=jdbc
name=postgresql
url=jdbc:postgresql://x.x.x.x:5432/pgdb
username=user
password=pw
#============================================================

I start http://localhost:8090/JasperReportsIntegration/
and click on the line 'postgresql jdbc Test'
and receive the message:

java.lang.RuntimeException: Could not connect via JDBC: ????? ???????? URL Oracle
(Invalid Oracle URL specified)

Why Oracle URL? I use Postgresql.
Because Class.forName("oracle.jdbc.driver.OracleDriver")?
But With JDBC 4.0, applications no longer need to issue a Class.forName() on the driver name; instead, the DriverManager will find an appropriate JDBC driver when the application requests a Connection.

Can you help me, please?

  • replies 6
  • views 385
  • likes 0
Dietmar Aust daust · · moderators
#2

Hi Viktor,

this tool was specifically designed for Oracle and never intented for any other databases. But I do support two different approaches.

When you are using the type=jdbc, it will create an Oracle specific Connection Pool. This is how it is implemented. Not a single connection, but a managed connection pool.

When you are using the type=jndi you can configure it in Tomcat itself. You could use this approach.

E.g. in the application.properties:

#====================================================================
# Native JNDI datasource, to be configured in the application server
# name: jndi_test
#====================================================================
[datasource:jndi_test]
type=jndi
name=jndi_test

Then you also need to create a file called JasperReportsIntegration.xml and place it into the directory: $TomcatHome\conf\Catalina\localhost\JasperReportsIntegration.xml. It is explained in older versions how to do that. Most people have moved to the oracle specific jdbc connection approach: http://www.opal-consulting.de/downloads/free_tools/JasperReportsIntegration/2.0.0/doc/Installation-J2EE-Tomcat.html

<?xml version="1.0" encoding="UTF-8"?>

<Context path="/JasperReportsIntegration" debug="5" reloadable="true"
         crossContext="true">

    <!-- parameter definition: http://commons.apache.org/dbcp/configuration.html -->
    <!-- minimum connections in pool: 3 -->
    <!-- check valid session: each 5 minutes  -->
    <Resource name="jdbc/jndi_test" auth="Container" type="javax.sql.DataSource"
              driverClassName="oracle.jdbc.OracleDriver"
              maxActive="20" maxIdle="10" maxWait="-1"
              initialSize="4" minIdle="3" validationQuery="select user from dual"
              testWhileIdle="true" testOnBorrow="true" timeBetweenEvictionRunsMillis="300000"
              numTestsPerEvictionRun="100" minEvictableIdleTimeMillis="10000"

              url="jdbc:oracle:thin:@127.0.0.1:1521:XE" 
              username="my_oracle_user"
              password="my_oracle_user_pwd" 
              />
                            
</Context>

I use the following code to connect to the database:

/**
	 * Get a connection for a data source from the connection pool. First we try
	 * to get a connection from the JNDI datasources. If there is no
	 * configuration available for this datasource, then create a regular JDBC
	 * connection. The connection pool is created upon the first request.
	 * 
	 * @param dsName the name of the data source, e.g. "default"
	 * @return Connection
	 */
	public Connection getConnection(String dsName) {
		
		logger.trace("lookup dataSource:" + dsName);
		
		DataSourceDefinition dataSourceDef = AppConfig.getInstance()
				.getDataSourceDefinition(dsName);		
		Connection conn = null;
		
		if (dataSourceDef == null)
			Utils.throwRuntimeException("Datasource " + dsName + " could not be found.");
		
		if (dataSourceDef.type.equals("jndi")){
			// ----------------------------------------------------
			// use the JNDILookup first
			// ----------------------------------------------------

			if (initialContext != null) {
				String jndiName = this.jndiPrefix + dsName;
				logger.debug("use JNDI to lookup dataSource:" + jndiName);
				try {
					DataSource ds = (DataSource) initialContext.lookup(jndiName);

					if (ds != null) {
						conn = ds.getConnection();
					}
				} catch (Exception e) {
					logger.info("JNDI lookup failed for " + jndiName
							+ ", trying JDBC now...");
				}
			}		
		} else if (dataSourceDef.type.equals("jdbc")){
			// ----------------------------------------------------
			// use JDBC connection next
			// http://docs.oracle.com/cd/B19306_01/java.102/b14355/concache.htm
			// ----------------------------------------------------
			if (conn == null) {
				logger.debug("use JDBC to lookup dataSource:" + dsName);

				OracleConnectionPoolDataSource ocpds;
				PooledConnection pc;
								
				try {
					logger.trace("retrieve connectionPoolDataSource from HashMap first");
					ocpds = dataSources
							.get(dsName);

					if (ocpds == null) {
						logger.trace("dataSource not found in HashMap, initialize a new connection pool and store in HashMap");

						// set cache properties
					    java.util.Properties prop = new java.util.Properties();
					    prop.setProperty("InitialLimit", "3");
					    prop.setProperty("MinLimit", "3");
					    prop.setProperty("MaxLimit", "50");

					    ocpds = new OracleConnectionPoolDataSource();
						ocpds.setURL(dataSourceDef.url);
						ocpds.setUser(dataSourceDef.username);
						ocpds.setPassword(dataSourceDef.password);
						
						// set connection parameters
					    ocpds.setConnectionProperties(prop);
					 					
						dataSources.put(dsName, ocpds);
					}

					pc = ocpds.getPooledConnection();
					conn = pc.getConnection();

					logger.info("successfully connected to " + dataSourceDef.url
							+ " with user: " + dataSourceDef.username);
				} catch (SQLException e) {
					Utils.throwRuntimeException("Could not connect via JDBC: "
							+ e.getMessage());
				}
			}			
		}
		if (conn != null)
			logger.info("dataSource loaded:" + dsName);

		return conn;
	}
}

And I use for jndi the following prefix:

private String _jndiPrefix = "java:comp/env/jdbc/";

Hope that helps.
~Dietmar.

ViktorAfnt · · author
#3

Hi Dietmar,
I success with Tomcat.
Thanks for your help!

Best regards, Viktor.

1. Install Apache Tomcat ([Tomcat directory] for example = "E:\apache-tomcat-9.0.36")
2. Change the port for your local Tomcat installation (http://www.mkyong.com/tomcat/how-to-change-tomcat-default-port/) for example = 8181
	Once the basic Apache Tomcat installation works, stop the Apache Tomcat server.

3. Install the oracle JDBC drivers. Copy the files ojdbc8.jar, orai18n.jar and postgresql-42.2.14.jar into [Tomcat directory]\lib
4. Copy the file webapp\JasperReportsIntegration.war (version 2.5.0.1) into the directory [Tomcat directory]\webapps.
5. Start the Apache Tomcat server
    During startup, Tomcat will detect the new application and deploy the file automatically into the directoy [Tomcat directory]\webapps\JasperReportsIntegration.
6. Now you can test JasperReportsIntegration
	Start Tomcat (from command line at "[Tomcat directory]\bin"  enter "startup")
	Enter "http://localhost:8181/JasperReportsIntegration/" in brower address line
	Check "Basic connectivity tests" and "Basic report tests"
7. Stop Tomcat (from command line at "[Tomcat directory]\bin"  enter "shutdown")
8. For new datasource adding:
	add it in application.properties (with type = jdbc or jndi; "[Tomcat directory]\webapps\JasperReportsIntegration\WEB-INF\conf") 
		Example for JNDI case:
			[datasource:postgre]
			type=jndi
			name=postgre
	add the resource in context.xml ("[Tomcat directory]\webapps\JasperReportsIntegration\META-INF")
		Example for JNDI case:
			<Resource name="jdbc/postgre" auth="Container" type="javax.sql.DataSource"
					  driverClassName="org.postgresql.Driver"
					  maxActive="20"
					  initialSize="0"
					  minIdle="0"
					  maxIdle="8"
					  maxWait="10000"
					  timeBetweenEvictionRunsMillis="30000"
					  minEvictableIdleTimeMillis="60000"
					  testWhileIdle="true"
					  validationQuery="select user"
					  maxAge="600000"
					  rollbackOnReturn="true"
					  url="jdbc:postgresql://localhost:5432/statistics" 
					  username="stat"
					  password="stat" 
					  />
Dietmar Aust daust · · moderators
#4

Hi Victor,

this sounds great. Could you please share the exact configuration steps to make it work?
I am not using PostgreSQL ... but perhaps it could be valuable for other people.

Thank you
~Dietmar.

Dietmar Aust daust · · moderators
#5

I am so sorry, overlooked it. My first impression was that you quoted my description.
My bad.

Thanks a lot and glad it works :) .

Dietmar Aust daust · · moderators
#6

Hi @ViktorAfnt,

could you please close the issue? I believe you can mark it as "answer".

Thank you
~Dietmar.

daust added the waiting for reply from reporter label
#7