reportquerydialog.java

来自「优秀的打印控件全源代码,类似水晶表的设计器!」· Java 代码 · 共 1,003 行 · 第 1/3 页

/*
 * ReportQueryFrame.java
 *
 *  iReport  --  Visual designer for generating JasperReports Documents
 *  Copyright (C) 2002-2003  Giulio Toffoli [e-mail]
 *
 *  This program is free software; you can redistribute  and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 *
 *  Giulio Toffoli
 *  Via T.Aspetti, 233
 *  35100 Padova ITALY
 *  [e-mail]
 *
 *
 * Created on 27 maggio 2003, 19.47
 */

package it.businesslogic.ireport.gui;
import it.businesslogic.ireport.*;
import it.businesslogic.ireport.util.*;
import javax.swing.table.*;
import javax.swing.*;
import java.util.*;
import java.sql.*;

import dori.jasper.engine.*;
import dori.jasper.engine.design.*;
import dori.jasper.engine.fill.*;
import bsh.Interpreter;
import java.io.*;

import javax.swing.event.*;


/** 
 * A dialog which allows the user to enter a SQL query and then choose the
 * fields to use in the report.
 *
 * @author <a href="mailto:[e-mail]">Giulio Toffoli</a>
 * @author <a href="mailto:[e-mail]">Peter Henderson</a>
 */
public class ReportQueryDialog extends javax.swing.JDialog {
    
    protected static String standard_types[]= new String[]{
        "java.lang.String",
	"java.lang.Object",
	"java.lang.Boolean",
	"java.lang.Byte",
	"java.util.Date",
	"java.sql.Timestamp",
	"java.sql.Time",
	"java.lang.Double",
	"java.lang.Float",
	"java.lang.Integer",
	"java.io.InputStream",
	"java.lang.Long",
	"java.lang.Short",
	"java.math.BigDecimal"    
    };
    
    /** Creates new form ReportQueryFrame */
    public ReportQueryDialog(java.awt.Frame parent, boolean modal) {
        super(parent, modal);
        initComponents();
        this.setSize(520, 500);
        Misc.centerFrame(this);
        
        stoppedChanging.setRepeats(false);
        
        jRSQLExpressionArea1.getDocument().addDocumentListener( new DocumentListener() {
            public void changedUpdate(DocumentEvent e) {
                if(isSettingSQLExpression)return;
                okButton.setEnabled(false);
                stoppedChanging.restart();
            }
            public void insertUpdate(DocumentEvent e) {
                if(isSettingSQLExpression)return;
                okButton.setEnabled(false);
                stoppedChanging.restart();
            }
            public void removeUpdate(DocumentEvent e) {
                if(isSettingSQLExpression)return;
                okButton.setEnabled(false);
                stoppedChanging.restart();
            }
        } );
        
        setColumnsError( "Please open a report." );
    }
    
    /**
     * A timer to detect when the SQL expression area has not been changed, for
     * a short moment. This is to prevent the database being hit with every
     * with every key press.
     */
    javax.swing.Timer stoppedChanging = new javax.swing.Timer( 500, new java.awt.event.ActionListener() {
        public void actionPerformed(java.awt.event.ActionEvent evt) {
            if( automaticlyReadFieldsCheckBox.isSelected() ) {
                processQueryChanged( jRSQLExpressionArea1.getText().trim() );
            }
        }
    } );
    
    
    /**
     * Given a database query string, extract the database columns, then display
     * them. If there was a problem loading the list of columns, show an error
     * panel which contains the reason why.
     *
     * @param query The SQL query string, which can contain JasperReports parameters. 
     */
    private void processQueryChanged( String query ) {
        if (jReportFrame == null) {
            setColumnsError( "Please open a report." );
            return;
        }
        
        if (query.length() == 0) {
            setColumnsError( "You must insert a valid query first" );
            return;
        }

        IReportConnection conn = (IReportConnection)this.getJReportFrame().getMainFrame().getProperties().get("DefaultConnection");
        if (!conn.isJDBCConnection()) {
            setColumnsError( "The active connection is not of type JDBC. Activate a JDBC connection first." );
            return;
        }
        
        // Run the query in the backgroud as it is not quick.
        Thread t = new FieldReader(query, conn);
        t.start();
    }
    
    
    /**
     * A Thread class to extract field names from a SQL query.
     *
     */
    class FieldReader extends Thread {
        String src_query;
        IReportConnection conn;
        
        /**
         * ctor.
         * @param query The query to read the field from
         * @param conn The IRport DB connection to use.
         */
        public FieldReader(String query, IReportConnection conn) {
            src_query=query;
            this.conn=conn;
        }
        
        /**
         * Set the fields table data to the supplied data.
         * This is called from a none swing thread, hence all the invoke and
         * wait magic.
         * The columns are only set if the query string matches the one the 
         * results are for.
         *
         * @param columns The list of columns to set.
         */
        private void setColumnsFromWorker( final List columns ) {
            try {
                SwingUtilities.invokeAndWait( new Runnable() {
                    public void run() {
                        String str = jRSQLExpressionArea1.getText().trim();
                        if( str.compareTo(src_query)==0 ) {
                            setColumns( columns );
                        }
                    }
                } );
            } catch(Exception e) {
                // oh well we got interrupted.
            }
        }

        /**
         * Set the columns error message.
         * This is called from a none swing thread, hence all the invoke and
         * wait magic.
         * The message is only set if the query string matches the one the 
         * error message is for.
         *
         * @param columns The list of columns to set.
         */
        private void setColumnErrorFromWork( final String error_msg ) {
            try {
                SwingUtilities.invokeAndWait( new Runnable() {
                    public void run() {
                        String str = jRSQLExpressionArea1.getText().trim();
                        if( str.compareTo(src_query)==0 ) {
                            setColumnsError( error_msg );
                        }
                    }
                } );
            } catch(Exception e) {
                // oh well we got interrupted.
            }
        }
        
        
        public void run() {
            String error_msg = "";
            PreparedStatement ps = null;
            try {
                // Prepare the parameter expression evaluator.
                // Cannot cache this as user may have altered the parameters. (this 
                // dialog is not modal.
                Interpreter interpreter = prepareExpressionEvaluator();

                String query = src_query;
                // look for parameters in the query and replace them with default values.
                // parameters look something like 
                // $P{QuoteGroupID}
                // or 
                // $P!{OrderByClause}
                java.util.List queryParams = new ArrayList();
                Enumeration enumParams = getJReportFrame().getReport().getParameters().elements();
                while( enumParams.hasMoreElements() ) {
                    it.businesslogic.ireport.JRParameter parameter = (it.businesslogic.ireport.JRParameter)enumParams.nextElement();

                    String p1 = "$P{" + parameter.getName() + "}";
                    String p2 = "$P!{" + parameter.getName() + "}";

                    // evaluate the Default expression value
                    Integer expID = (Integer)parameterNameToExpressionID.get(parameter.getName());
                    Object defValue;
                    if( expID!=null ) {
                        defValue = interpreter.eval("bshCalculator.evaluate(" + expID.intValue() + ")");
                    } else {
                        // this param does not have a default value.
                        defValue = null;
                    }


                    int ip1 = query.indexOf(p1);
                    while( ip1!=-1 ) {
                        // add a query parameter
                        if( defValue==null ) {
                            throw new IllegalArgumentException("Please set a " +
                            "default value for the parameter '" + 
                            parameter.getName() + "'" );
                        }

                        String before = query.substring(0, ip1);
                        String after = query.substring(ip1+p1.length());
                        query = before + " ? " + after;
                        queryParams.add( defValue );
                        ip1 = query.indexOf(p1);
                    }

                    int ip2 = query.indexOf(p2);
                    while( ip2!=-1 ) {
                        // String replacement, Altering the SQL statement.
                        if( defValue==null ) {
                            throw new IllegalArgumentException("Please set a " +
                                "default value for the parameter '" 
                                + parameter.getName() + "'" );
                        }

                        String before = query.substring(0, ip2);
                        String after = query.substring(ip2+p2.length());
                        query = before + " " + defValue.toString() + " " + after;
                        ip2 = query.indexOf(p2);
                    }
                }

                ps = conn.getConnection().prepareStatement( query );
                for(int pc=0; pc<queryParams.size(); pc++ ) {
                    ps.setObject(pc+1, queryParams.get(pc) );
                }
                
                // Some JDBC drivers don't supports this method...
                try { ps.setFetchSize(0); } catch(Exception e ) {}
                
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();

                List columns = new ArrayList();
                for (int i=1; i <=rsmd.getColumnCount(); ++i) {
                    it.businesslogic.ireport.JRField field = 
                        new it.businesslogic.ireport.JRField(
                            rsmd.getColumnLabel(i), 
                            Misc.getJdbcTypeClass(rsmd.getColumnType(i) ) );
                    columns.add( new Object[]{field, field.getClassType()} );
                }

                setColumnsFromWorker(columns);
                
                return;
            } catch( IllegalArgumentException ie ) {
                error_msg = ie.getMessage();
            } catch (NoClassDefFoundError ex) {
                error_msg = "NoClassDefFoundError!!\nCheck your classpath!";
            } catch (java.sql.SQLException ex) {
                error_msg = "SQL problems:\n"+ex.getMessage();
            } catch (Exception ex) {
                ex.printStackTrace();
                error_msg = "General problem:\n"+ex.getMessage()+
                    "\n\nCheck username and password; is the DBMS active ?!";
            } finally {
                if(ps!=null) try { ps.close(); } catch(Exception e ) {}
            }
            
            setColumnErrorFromWork( error_msg );
        }
    }
    
    
    
    
    
    
    /**
     * Shows the list of columns.
     * If the column error message label is visible remove it first.
     *
     * @param cols A List Object[], for the fields.
     */
    private void setColumns( final List cols ) {
        
        columnsErrorMsgLabel.setText( "" );
        jPanel2.remove( columnsErrorScrollPane );