SCRIPTLETS GETTING THE CONNECTION JDBC FROM JASPER STUDIO TO JAVA CLASS

[aperez_6]

Hi,

I would like to know how I can get the JDBC connection, which we use in Jasper Studio in the Java class to execute a series of queries because I want to return some values. Next I indicate what I have:

 

JAVA CLASS/PACKAGE

 

package commonclient;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.sf.jasperreports.engine.JRDefaultScriptlet;
import net.sf.jasperreports.engine.JRScriptletException;
import net.sf.jasperreports.engine.fill.JRFillParameter;

public class Prueba1 extends JRDefaultScriptlet {

    private Connection conexion;
   
    public Prueba1() {
        super();
    }

    public String get_document_PE05_BIS( ) throws JRScriptletException, SQLException{
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        int dbsituacio=0;
        int dbidparte=0;
        Connection conn;
       
       try {         
          conn = (Connection)(this.getParameterValue(JRFillParameter.REPORT_CONNECTION));     --DOESN'T WORK

          if ( conn == null ) {
            return "Conexión nula";
          }
         
          if (conn.isReadOnly() == true) //make sure update can proceed
          {
             conn.setReadOnly(false);
          }         
                     
          String query = "select clase_situacio, id_parte from partes where sini_prov = ? and sini_of = ? and sini_any = ? and sini_numero = ? and sini_reca = ?";

          pstmt = conn.prepareStatement(query); // create a statement
          pstmt.setInt(1, 43); // set input parameter
          pstmt.setInt(2, 1); // set input parameter
          pstmt.setInt(3, 2017); // set input parameter
          pstmt.setInt(4, 23622); // set input parameter
          pstmt.setInt(5, 0); // set input parameter
           
          rs = pstmt.executeQuery();
          // extract data from the ResultSet
          while (rs.next()) {
            dbsituacio = rs.getInt(1);
            dbidparte = rs.getInt(2);
            //System.out.println(dbsituacio + "t" + dbidparte);             
          }
         
          return dbsituacio + "t" + dbidparte;
         
        } catch (Exception e) {
            return e.getMessage() + " " + e.getCause();           
        }

    }

}

 

I do not know if I should use any of the following methods I saw in the community that are used to develop the use of Scriptlets. And you can get what I look for the connection to the Database, which I use in Jasper Studio.

public void beforeReportInit()	Called before report initialization.
public void afterReportInit()	Called after report initialization.
public void beforePageInit()	Called before each page is initialized.
public void afterPageInit()	Called after each page is initialized.
public void beforeColumnInit()	Called before each column is initialized.
public void afterColumnInit()	Called after each column is initialized.
public void beforeGroupInit(String groupName)	Called before the group specified in the parameter is initialized.
public void afterGroupInit(String groupName)	Called after the group specified in the parameter is initialized.
public void beforeDetailEval()	Called before each record in the detail section of the report is evaluated.
public void afterDetailEval()	Called after each record in the detail section of the report is evaluated.

 

Thankful for who can help me.

 

Aranzazu Pérez

 

 

[aperez_6]

I have continued to try other options and finally I have seen what I was missing! It was to add the JDBC library to the Build Path.

And the code that worked for me is the following:

 

    public String get_document_PE05_BIS( ) throws JRScriptletException, SQLException{
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        int dbsituacio=0;
        int dbidparte=0;
        Connection conn;
       
       try {         

          Class.forName("oracle.jdbc.driver.OracleDriver");
          conn = DriverManager.getConnection("jdbc:oracle:thin:@xxx.xxx:xxxx:YYYY", "xxx", "xxx");           

          if ( conn == null ) {
            return "Conexión nula";
          }
         
          if (conn.isReadOnly() == true) //make sure update can proceed
          {
             conn.setReadOnly(false);
          }         
                     
          String query = "select clase_situacio, id_parte from partes where sini_prov = ? and sini_of = ? and sini_any = ? and sini_numero = ? and sini_reca = ?";

          pstmt = conn.prepareStatement(query); // create a statement
          pstmt.setInt(1, 43); // set input parameter
          pstmt.setInt(2, 1); // set input parameter
          pstmt.setInt(3, 2017); // set input parameter
          pstmt.setInt(4, 23622); // set input parameter
          pstmt.setInt(5, 0); // set input parameter
           
          rs = pstmt.executeQuery();

          // extract data from the ResultSet
          while (rs.next()) {
            dbsituacio = rs.getInt(1);
            dbidparte = rs.getInt(2);
            //System.out.println(dbsituacio + "t" + dbidparte);             
          }
         
          return dbsituacio + "t" + dbidparte;
         
        } catch (Exception e) {
            return e.getMessage() + " " + e.getCause();
           
        }

    }

 

I hope it will help someone in my situation. What I saw when you change the Java code and redo the .JAR. You must follow the following steps:

1. Close the Jasper Studio and reopen it
2. Refresh the Project
3. Compile the Report
4. Execute the result

 

Regards,

[mlopez_1]

If you want to retrieve the same connecation as Report Unit is using, I use this:

import net.sf.jasperreports.engine.JRDefaultScriptlet;

import net.sf.jasperreports.engine.JRParameter;

public class XXXXXXextends JRDefaultScriptlet {

..

java.sql.Connection report_conexion = (Connection) this.getParameterValue(JRParameter.REPORT_CONNECTION);

..

 

 

Mariano

[aperez_6]

Thank you for the answer.

I solved it by seeing in a forum an indication, which said to add the library ojdbc6.jar in the Build Path of the Jasper Studio. This is how I managed to execute the test method.

[aperez_6]

Hi,

How could I make a generic code to connect to the database according to the datasource in a Java class that I use in a scriptlet?

With this code works when I execute an statement. But it is not correctly to me because is not private the information:

public class ConnectionManager {
    private static String url = "jdbc:oracle:thin:@xxxx:1111:name";
    private static String driverName = "oracle.jdbc.driver.OracleDriver";       
    private static String username = "user";   
    private static String password = "pswd";
    private static Connection con;
    
    public static Connection getConnection() {
        try {
            Class.forName(driverName);
            try {
                con = DriverManager.getConnection(url, username, password);
            } catch (SQLException ex) {
                // log an exception. fro example:
                System.out.println("Failed to create the database connection."); 
            }
        } catch (ClassNotFoundException ex) {
            // log an exception. for example:
            System.out.println("Driver not found."); 
        }
        return con;
    }

}

 

With this code doesn't work:

 

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

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.faces.model.SelectItem;

import javax.naming.NamingException;

import net.sf.jasperreports.engine.JRDefaultScriptlet;
import net.sf.jasperreports.engine.JRParameter;
import net.sf.jasperreports.engine.JRScriptletException;
//import javax.faces.model.SelectItem;
//import net.sf.jasperreports.engine.JRParameter;

//Clase que contendrá las Utilidades Comunes para cualquier Report de Jasper
public class Utilidades_Principal extends JRDefaultScriptlet {

    public  List get_list_values(String sql) throws SQLException, NamingException, JRScriptletException {
        String result = "";
        Statement stmt = null;
        Connection conf = (Connection)this.getParameterValue(JRParameter.REPORT_CONNECTION);
        try {
            stmt = conf.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                result = result + rs.getString(1) + ",";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        
        return Arrays.asList(result.split(","));
    }

 

I would like to try to get the connection in a generic way. If someone has succeeded, I would appreciate it.

 

Thanks in advance,