Using a JDBC Connection inside a java class for POJO data source #1056

loopist612 · 2022-09-24T05:52:46Z

loopist612
Sep 24, 2022

I'm trying to write a complex report where I need to use some java code to fetch the data and then return it back into BIRT using the POJO data source. I've found a lot of great examples like the stock ticker example for how to actually use the POJO data source with no problems, but I can't seem to find any examples of how to make a database connection inside my class.

I've tried a few options

1 ) Created a class with basic JDBC connections. Works fine as a stand alone app. I can even create the POJO data source and data the data set sees my getters, but no matter what I do, it keeps throwing this exception (I'll save the stack trace for now)
org.eclipse.datatools.connectivity.oda.OdaException

Using this in my java, so not sure why I get an ODA Exception
String dbURL2 = "jdbc:oracle:thin:@//" + host+ ":" + port + "/" + service;
conn2 = DriverManager.getConnection(dbURL2, user, pass);

I'd be fine if I could also pass the results of a standard JDBC data set into my pojo data source. That way I could use the more standard mechinism to pull the data and have my java class do it's work.

Any suggestions?

Answered by hvbtup

Sep 27, 2022

To summarize, the answer for the intention behind the initial question is:
Use a JDBC data source and Oracle SQL instead of a POJO if your data is stored in an oracle database.

View full answer

speckyspooky · 2022-09-24T14:38:16Z

speckyspooky
Sep 24, 2022
Collaborator

Hello There,

I didn't unserstand correctly why you use an POJO data source to get an data set based on it and combine it with an JDBC-data set.
If I understand you then you try to fecth data from JDBC to get the results to POJO to be used in report like data source.

In my cases I use "Scripted Data Sources" and create the corresponding data set.
At the scripted data source I goes forward into 2 steps:
step 01: fetch the jdbc data at method "open()"
step 02: set the data into the result set at method "fetch()"

Perhapse the idea will help you a little bit. You can also give us a little bit mor ifnormation to assist you.

Regards

3 replies

loopist612 Sep 24, 2022
Author

Thanks for the quick reply. Yes, I could have done a better job detailing it, let me try it again.

The data I'm working with is time series data. In particular, it is for roughly 300 IOT devices that capture data every 5 minutes. This data is stored in an Oracle database, which doing the math would be 8640 data sets per device per month (My report would do a month at a time). I need to create a report that shows any threshold violations. While I don't agree with the algorithm to do it (defined by a customer), the process would be iterating through the 8640 measurements one by one, and comparing the current one to the previous one to see if it is above a certain threshold (This value is x% > then it's neighbor). Maybe this context helps?

I was thinking the easiest way to do this, since I know java 10x better than Birt is to somehow get the data from the database, walk through some Java data structure to do the analysis and return the POJO data set to Birt to report on the violations. That's where I'm trying to find the best way for the java side to get this data. I somehow need to query the DB, have the Java code do the analysis, and return the Pojo data set. Now maybe thats something you can do with a scripted data source. I didn't see how that was possible when I spent all of last day doing google searches :)

I'm open to ideas, but sticking with the Pojo datasource as a starting point, can I pass my java code an ODA data source to do it's queries against? I tried just embeding my own JDBC connection natively, but kept getting Birt Exceptions. In the meantime, I'm going to focus more time on the scripted data source since it sounds like a good and different angle.

wimjongman Sep 25, 2022
Maintainer

You should not do that in a pojo. You are working against the grain here. Your solution will be unnecessarily complex.

This is not about not knowing BIRT because you need to use BIRT either way. It is about using SQL or Java. SQL is by far the better solution for this problem.

See attached an example report that solves a similar problem using the provided database.

new_report.rptdesign.txt

loopist612 Sep 27, 2022
Author

Thanks for the example. Yes, I want back to doing it with SQL and birt java script.

krishnakittu · 2022-09-24T16:04:37Z

krishnakittu
Sep 24, 2022

Hi, I am using birt for almost 9 years, we are sending list of pojos/DTOs to the birt and the birt is displaying all in table format with formatting, is that what you are looking for?..

…

On Sat, 24 Sep, 2022, 21:27 loopist612, ***@***.***> wrote: Thanks for the quick reply. Yes, I could have done a better job detailing it, let me try it again. The data I'm working with is time series data. In particular, it is for roughly 300 IOT devices that capture data every 5 minutes. This data is stored in an Oracle database, which doing the math would be 8640 data sets per device per month (My report would do a month at a time). I need to create a report that shows any threshold violations. While I don't agree with the algorithm to do it (defined by a customer), the process would be iterating through the 8640 measurements one by one, and comparing the current one to the previous one to see if it is above a certain threshold (This value is x% > then it's neighbor). Maybe this context helps? I was thinking the easiest way to do this, since I know java 10x better than Birt is to somehow get the data from the database, walk through some Java data structure to do the analysis and return the POJO data set to Birt to report on the violations. That's where I'm trying to find the best way for the java side to get this data. *I somehow need to query the DB, have the Java code do the analysis, and return the Pojo data set.* Now maybe thats something you can do with a scripted data source. I didn't see how that was possible when I spent all of last day doing google searches :) I'm open to ideas, but sticking with the Pojo datasource as a starting point, can I pass my java code an ODA data source to do it's queries against? I tried just embeding my own JDBC connection natively, but kept getting Birt Exceptions. In the meantime, I'm going to focus more time on the scripted data source since it sounds like a good and different angle. — Reply to this email directly, view it on GitHub <#1056 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAUSMDZPPQMDWVMCG32D2ILV74QGZANCNFSM6AAAAAAQUP7MLI> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

0 replies

speckyspooky · 2022-09-24T16:46:53Z

speckyspooky
Sep 24, 2022
Collaborator

You have many options to do this.
The main fact is why you will fetch the data with BIRT -> send to Java -> send to BIRT for Layouting.

Option A) "Java&POJO": According to [krishnakittu] would it be make sense to fetch the data directly with Java, execute your analysis, cerate POJO for BIRT-layouting.

Option B) "BIRT&Rhino" You can add your java analysis package to BIRT and execute this with the Java-Rhino implementation to get your analysis results. The results could be added to an "scripted data set".

Option C) "BIRT&PL/SQL" I work very strongly with PL/SQL because based on the kind of analysis the database in my environment is faster like the java servers. WIth "strongly" I mean, I work with stored procedures, functioncs, loops, recursive CTE-loops for trees and so on.
You are also able (advanced feature) to add Java-objects to Oracle.

All options are dependent on your kind of analysis and your report target.

0 replies

loopist612 · 2022-09-24T18:41:56Z

loopist612
Sep 24, 2022
Author

So I was trying to do option A, but I was questioning myself if BIRT was able to cleanly handle that. I created a standalone java app and it pulls the data fine, but when I run it in BIRT, it fails with an exception.

`package com.advanta.custom.jdbc;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.sql.*;
import oracle.jdbc.driver.OracleDriver;

public class PojoExample {

static Iterator<SdpInfo> iterator;

public static void main(String[] args)  {
	
	String user = "***";
	String pass = "***";
	String host = "***"; 
	String port = "***";
	String service = "***";
	
	Connection rptEipConn = getConnection(user, pass, host, port, service);
	
	List<SdpInfo> sdps = new ArrayList<SdpInfo>();
	String query = "select s.udc_id, s.id from svc_pt s";
	
	System.out.println(rptEipConn.toString());

	try (Statement stmt = rptEipConn.createStatement()) {
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			SdpInfo data = new SdpInfo();
			String udc_id = rs.getString("UDC_ID");
			String id = rs.getString("ID");
			data.setUdcID(udc_id);
			data.setRefId(id);
			sdps.add(data);
		}
		rptEipConn.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} 
	
	iterator = sdps.iterator();
	
	while(iterator.hasNext()) {
		System.out.println(iterator.next().getUdcID());
	}
	
}



public void open(Object appContext, Map<String, Object> dataSetParamValues) {
	//String user = (String) dataSetParamValues.get("user");
	//String pass = (String) dataSetParamValues.get("pass");
	//String host = (String) dataSetParamValues.get("host"); 
	//String port = (String) dataSetParamValues.get("port"); 
	//String service = (String) dataSetParamValues.get("service"); 
	//List<StockData> stockData = new StockDataSet().getStockValues((String) dataSetParamValues.get("testParam"));
	
	String user = "***";
	String pass = "***";
	String host = "***"; 
	String port = "***";
	String service = "***";
	
	Connection rptEipConn = getConnection(user, pass, host, port, service);

	List<SdpInfo> sdps = new ArrayList<SdpInfo>();
	String query = "select s.udc_id, s.id from svc_pt s";
	
	System.out.println(rptEipConn.toString());

	try (Statement stmt = rptEipConn.createStatement()) {
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			SdpInfo data = new SdpInfo();
			String udc_id = rs.getString("UDC_ID");
			String id = rs.getString("ID");
			String universal = rs.getString("UNIVERSAL_ID");
			data.setUdcID(udc_id);
			data.setRefId(id);
			data.setUdcID(universal);
			sdps.add(data);
		}
		rptEipConn.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} 
	
	iterator = sdps.iterator();

}

public Object next(){
	if (iterator.hasNext())
		return iterator.next();
	return null;
}

public void close() {
	iterator = null;
}

public static Connection getConnection(String user, String pass, String host, String port, String service) {
    Connection conn2 = null;
    
    try {
    	Class.forName("oracle.jdbc.driver.OracleDriver");  
  
        String dbURL2 = "jdbc:oracle:thin:@//" + host+ ":" + port + "/" + service;
        conn2 = DriverManager.getConnection(dbURL2, user, pass);
        if (conn2 != null) {
            //System.out.println("Connected with connection #2");
        }

    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} 
	return conn2;
}

}`

But when I run it, it just won't create an exception. I have the referenced ojdbc jar in the jar file with this code and in the classpath too. This is the exception I get.

org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot execute the statement. org.eclipse.datatools.connectivity.oda.OdaException ; java.lang.reflect.InvocationTargetException at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:53) at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:103) at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:83) at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:519) at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:870) at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:550) at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1074) at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:218) at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:170) at org.eclipse.birt.report.engine.api.impl.ExtractionResults.nextResultIterator(ExtractionResults.java:126) at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:81) at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:339) at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122) Caused by: org.eclipse.datatools.connectivity.oda.OdaException ; java.lang.reflect.InvocationTargetException at org.eclipse.birt.data.oda.pojo.impl.internal.PojoDataSetFromCustomClass.open(PojoDataSetFromCustomClass.java:122) at org.eclipse.birt.data.oda.pojo.impl.Query.executeQuery(Query.java:126) at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doExecuteQuery(OdaQuery.java:480) at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.executeQuery(OdaQuery.java:444) at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:509) ... 9 more Caused by: java.lang.reflect.InvocationTargetException at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:567) at org.eclipse.birt.data.oda.pojo.impl.internal.PojoDataSetFromCustomClass.open(PojoDataSetFromCustomClass.java:120) ... 13 more Caused by: java.lang.NullPointerException: Cannot invoke "Object.toString()" because "rptEipConn" is null at com.advanta.custom.jdbc.PojoExample.open(PojoExample.java:74) ... 18 more

0 replies

hvbtup · 2022-09-26T06:42:30Z

hvbtup
Sep 26, 2022
Collaborator

java.lang.NullPointerException: Cannot invoke "Object.toString()" because "rptEipConn" is null at ...

Remove your fake exception handling and let it crash to see what's really going on.
Test if getConnection returns a non-null object. Probably the class loader can't find your driver.

But - as others said - use the right tools.
You have an Oracle DB at hand.

What you want to do (detect if this value is x% > then it's neighbor) is almost trivial in standard SQL (you don't need PL/SQL at all).
Hint: Make use of the LAG analytic function and a CASE expression.

1 reply

loopist612 Sep 27, 2022
Author

Sorry, this might have turned to a java thread. I changed the try/catch to a throws and birt gave me better exceptions to nail down where I was not getting the connection. You are right, it was a clasdef not found. Thanks!

hvbtup · 2022-09-27T08:30:51Z

hvbtup
Sep 27, 2022
Collaborator

To summarize, the answer for the intention behind the initial question is:
Use a JDBC data source and Oracle SQL instead of a POJO if your data is stored in an oracle database.

1 reply

loopist612 Sep 27, 2022
Author

Yes, I'm going to back to using BIRT more as it is intended. JDBC Data Source and SQL. It's not to say, I don't see how using mainly java and just returning a POJO doesn't have some advantages...but I agree, that's necessarily the best way to do it :)

Gherbi2020 · 2024-12-20T16:40:35Z

Gherbi2020
Dec 20, 2024

Hi loopist612, I tried the same thing but I used mysql, I ask if that connection between Pojo and oracle and birt its succeeded for you .

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Using a JDBC Connection inside a java class for POJO data source #1056

{{title}}

Replies: 7 comments 5 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Using a JDBC Connection inside a java class for POJO data source #1056

loopist612 Sep 24, 2022

Replies: 7 comments · 5 replies

speckyspooky Sep 24, 2022 Collaborator

loopist612 Sep 24, 2022 Author

wimjongman Sep 25, 2022 Maintainer

loopist612 Sep 27, 2022 Author

krishnakittu Sep 24, 2022

speckyspooky Sep 24, 2022 Collaborator

loopist612 Sep 24, 2022 Author

hvbtup Sep 26, 2022 Collaborator

loopist612 Sep 27, 2022 Author

hvbtup Sep 27, 2022 Collaborator

loopist612 Sep 27, 2022 Author

Gherbi2020 Dec 20, 2024

loopist612
Sep 24, 2022

Replies: 7 comments 5 replies

speckyspooky
Sep 24, 2022
Collaborator

loopist612 Sep 24, 2022
Author

wimjongman Sep 25, 2022
Maintainer

loopist612 Sep 27, 2022
Author

krishnakittu
Sep 24, 2022

speckyspooky
Sep 24, 2022
Collaborator

loopist612
Sep 24, 2022
Author

hvbtup
Sep 26, 2022
Collaborator

loopist612 Sep 27, 2022
Author

hvbtup
Sep 27, 2022
Collaborator

loopist612 Sep 27, 2022
Author

Gherbi2020
Dec 20, 2024