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
3 Answers:
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
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,
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,