src.com.ibm.as400.vaccess.SQLQueryWherePane Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of jt400-jdk8 Show documentation
Show all versions of jt400-jdk8 Show documentation
The Open Source version of the IBM Toolbox for Java
///////////////////////////////////////////////////////////////////////////////
//
// JTOpen (IBM Toolbox for Java - OSS version)
//
// Filename: SQLQueryWherePane.java
//
// The source code contained herein is licensed under the IBM Public License
// Version 1.0, which has been approved by the Open Source Initiative.
// Copyright (C) 1997-2000 International Business Machines Corporation and
// others. All rights reserved.
//
///////////////////////////////////////////////////////////////////////////////
package com.ibm.as400.vaccess;
import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.FocusAdapter;
import java.awt.event.FocusEvent;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Types;
import javax.swing.Box;
import javax.swing.ButtonGroup;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JComponent;
import javax.swing.JDialog;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JRadioButton;
import javax.swing.JTextField;
import javax.swing.border.CompoundBorder;
import javax.swing.border.EmptyBorder;
import javax.swing.border.LineBorder;
/**
The SQLQueryWherePane class represents a panel which allows a
user to dynamically build the SELECT portion of an SQL query.
This panel is used for a page of the SQLQueryBuilderPane notebook.
**/
class SQLQueryWherePane
extends SQLQueryFieldsPane
{
private static final String copyright = "Copyright (C) 1997-2000 International Business Machines Corporation and others.";
// The variables and methods which have private commented out
// had to be made package scope since currently Internet Explorer
// does not allow inner class to access private items in their
// containing class.
// GUI components
/*private*/ DoubleClickList notList_;
/*private*/ DoubleClickList functionList_;
/*private*/ DoubleClickList testList_;
/*private*/ DoubleClickList otherList_;
/*private*/ SQLQueryClause clause_;
// Constants for SQL syntax. Because these strings represent
// SQL syntax, they are not translated.
// Constants for functions.
private static final String FCN_CAST_ = "CAST";
private static final String FCN_CHAR_ = "CHAR";
private static final String FCN_CURRENT_ = "CURRENT";
private static final String FCN_DATE_ = "DATE";
private static final String FCN_DAY_ = "DAY";
private static final String FCN_HOUR_ = "HOUR";
private static final String FCN_LENGTH_ = "LENGTH";
private static final String FCN_MINUTE_ = "MINUTE";
private static final String FCN_MONTH_ = "MONTH";
private static final String FCN_SECOND_ = "SECOND";
private static final String FCN_SUBSTR_ = "SUBSTRING";
private static final String FCN_TIME_ = "TIME";
private static final String FCN_TIMESTAMP_ = "TIMESTAMP";
private static final String FCN_UPPER_ = "UPPER";
private static final String FCN_YEAR_ = "YEAR";
// Constants for tests.
private static final String TEST_BETWEEN_ = "BETWEEN";
private static final String TEST_IN_ = "IN";
private static final String TEST_NOT_NULL_ = "IS NOT NULL";
private static final String TEST_NULL_ = "IS NULL";
private static final String TEST_LIKE_ = "LIKE";
// Is NOT in affect for this expression.
private boolean notInEffect_ = false;
// Private variables used by internal methods. These are
// instance variables because they are used in several places.
/*private*/ JDialog dialog;
/*private*/ boolean pane1Active;
/*private*/ JComboBox list1, list2, list3;
/*private*/ JTextField textField1, textField2;
private static final String [] notChoices = {"NOT"};
private static final String [] functionChoices = {FCN_CAST_, FCN_CHAR_, FCN_CURRENT_, FCN_DATE_,
FCN_DAY_, FCN_HOUR_, FCN_LENGTH_, FCN_MINUTE_, FCN_MONTH_, FCN_SECOND_,
FCN_SUBSTR_, FCN_TIME_, FCN_TIMESTAMP_, FCN_UPPER_, FCN_YEAR_};
private static final String [] testChoices = {"=", "<>", "<", ">", "<=", ">=",
TEST_BETWEEN_, TEST_IN_, TEST_NOT_NULL_, TEST_NULL_, TEST_LIKE_};
private static final String [] otherChoices = {"AND", "OR"};
/**
Constructs a SQLQueryWherePane object.
Note init must be called to build the GUI contents.
@param parent The parent of this panel.
**/
public SQLQueryWherePane (SQLQueryBuilderPane parent)
{
super(parent);
}
/**
Enables the appropriate controls after a function is chosen.
**/
void functionComplete()
{
// Make appropriate controls available.
fields_.setEnabled(false);
notList_.setEnabled(false);
functionList_.setEnabled(false);
testList_.setEnabled(true);
}
/**
Called when an item in the function list is double-clicked on.
The request is processed, requesting additional information
from the user if needed, and the completed item is added
to the clause.
@param item The item that was chosen.
**/
/*private*/ void functionPicked(String item)
{
// Variables used when putting up an additional dialog.
String[] choices; // choices for the user
Object choice; // what the user selected
// -------------------------
// FCN_CURRENT_
// -------------------------
// Current requires a second value for date, time or timestamp.
if (item.equals(FCN_CURRENT_))
{
choices = new String[]{"DATE", "TIME", "TIMESTAMP"};
choice = JOptionPane.showInputDialog(this, // parent
item, // message
item, // title
JOptionPane.QUESTION_MESSAGE, // message type
null, // icon
choices, // choices
choices[0]); // initial choice
if (choice == null) // null means they cancelled
return;
String text = "(CURRENT " + choice ;
clause_.appendText(text);
functionComplete();
}
// -------------------------
// FCN_CAST_
// -------------------------
// On AS400, cast is only valid at V4R2 or later.
// Cast requires a field name and an SQL type.
else if (item.equals(FCN_CAST_))
{
choices = getFieldNames();
if (choices.length ==0)
{
// put up error message and return
noFields(item);
return;
}
list1 = new JComboBox();
for (int i=0; i< choices.length; ++i)
list1.addItem(choices[i]);
JLabel asLabel = new JLabel("AS");
list2 = new JComboBox();
list2.setEditable(true); // allow users to change type - ie CHAR(10)
list2.addItem("CHARACTER()");
list2.addItem("DATE");
list2.addItem("DECIMAL(,)");
list2.addItem("DOUBLE");
list2.addItem("FLOAT()");
list2.addItem("GRAPHIC()");
list2.addItem("INTEGER");
list2.addItem("NUMERIC(,)");
list2.addItem("REAL");
list2.addItem("SMALLINT");
list2.addItem("TIME");
list2.addItem("TIMESTAMP");
list2.addItem("VARCHAR()");
list2.addItem("VARGRAPHIC()");
JPanel choicePanel = new JPanel();
choicePanel.add(list1);
choicePanel.add(asLabel);
choicePanel.add(list2);
// Create buttons
JButton okButton = new JButton(ResourceLoader.getQueryText("DBQUERY_BUTTON_OK"));
final String fitem = item;
okButton.addActionListener(
new ActionListener(){
public void actionPerformed(ActionEvent ev)
{
String choice1 = (String)list1.getSelectedItem();
String choice2 = (String)list2.getSelectedItem();
if (choice2 == null || choice2.equals(""))
{
// put up error message and return (leave dialog up)
JOptionPane.showMessageDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_MESSAGE_VALUE_MISSING") + " AS", // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
return;
}
if (choice2.endsWith("()"))
{
// Create dialog for getting one length
String type = choice2.substring(0,choice2.length()-2);
boolean required = type.startsWith("VAR");
String prompt2;
if (required)
prompt2 = ResourceLoader.getQueryText("DBQUERY_TEXT_LENGTH_REQ");
else
prompt2 = ResourceLoader.getQueryText("DBQUERY_TEXT_LENGTH");
boolean error = true;
while (error)
{
String result = JOptionPane.showInputDialog(parent_, // parent
prompt2, // message
type + " " + ResourceLoader.getQueryText("DBQUERY_TITLE_LENGTH"), // title
JOptionPane.QUESTION_MESSAGE ); // message type
if (result.equals(""))
{
if (required)
{
// put up error message
JOptionPane.showMessageDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_MESSAGE_INVALID_INT_VALUE3"), // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
}
else
{
choice2 = type; // use default length, remove parens
error = false;
}
}
else
{
// verify input is a number
try
{
int i = Integer.parseInt(result);
if (i > 0) error = false;
}
catch(NumberFormatException e) {}
if (error)
{
// put up error message
JOptionPane.showMessageDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_MESSAGE_INVALID_INT_VALUE3"), // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
}
else
choice2 = type + "(" + result + ")"; // add length
}
}
}
else if (choice2.endsWith("(,)"))
{
// Create dialog for getting the total length (precision).
String type = choice2.substring(0,choice2.length()-3);
boolean error = true;
while (error)
{
String result = JOptionPane.showInputDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_TEXT_LENGTH_TOTAL"), // message
type + " " + ResourceLoader.getQueryText("DBQUERY_TITLE_LENGTH"), // title
JOptionPane.QUESTION_MESSAGE ); // message type
if (result.equals(""))
{
choice2 = type; // use default length, remove parens
error = false;
}
else
{
// verify input is a number
try
{
int i = Integer.parseInt(result);
if (i >= 0) error = false;
}
catch(NumberFormatException e) {}
if (error)
{
// put up error message
JOptionPane.showMessageDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_MESSAGE_INVALID_INT_VALUE3"), // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
}
else
{
choice2 = type + "(" + result + ")"; // add length
// Put up second dialog asking for scale (decimal positions)
error = true;
while (error)
{
String result2 = JOptionPane.showInputDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_TEXT_LENGTH_DECIMAL"), // message
type + " " + ResourceLoader.getQueryText("DBQUERY_TITLE_LENGTH"), // title
JOptionPane.QUESTION_MESSAGE ); // message type
if (result2 == null)
{
choice2 = type + "(" + result + ")"; // add one length
error = false;
}
else if (result2.equals(""))
{
choice2 = type + "(" + result + ")"; // add one length
error = false;
}
else
{
// verify input is a number
try
{
int i = Integer.parseInt(result2);
if (i >= 0) error = false;
}
catch(NumberFormatException e) {}
if (error)
{
// put up error message
JOptionPane.showMessageDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_MESSAGE_INVALID_INT_VALUE3"), // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
}
else
choice2 = type + "(" + result + "," + result2 + ")"; // add two lengths
}
}
}
}
}
}
String text;
/*if (choice2.indexOf("GRAPHIC") == 0 ||
choice2.indexOf("VARGRAPHIC") == 0)
// cast to graphic requires CCSID 13488
text = "(" + fitem + "(" + choice1 + " AS " + choice2 + " CCSID 13488)";
else*/
text = "(" + fitem + "(" + choice1 + " AS " + choice2 + ")";
clause_.appendText(text);
dialog.dispose();
functionComplete();
}
} // end of ActionListenerAdapter
);
JButton cancelButton = new JButton(ResourceLoader.getQueryText("DBQUERY_BUTTON_CANCEL"));
cancelButton.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent ev)
{
dialog.dispose();
}
}
);
JPanel buttonPanel = new JPanel();
buttonPanel.add(okButton);
buttonPanel.add(cancelButton);
dialog = new JDialog(VUtilities.getFrame(this), item, true);
dialog.getContentPane().setLayout(new BorderLayout());
dialog.getContentPane().add("Center",new LabelledComponent(ResourceLoader.getQueryText("DBQUERY_TEXT_CHOOSE2") + " " + item + "()", choicePanel, false));
dialog.getContentPane().add("South", buttonPanel);
dialog.addWindowListener(
new WindowAdapter() {
public void windowClosing(WindowEvent ev)
{
dialog.dispose();
}
}
);
dialog.pack();
dialog.setLocationRelativeTo(this);
dialog.setVisible(true);
}
// -------------------------
// FCN_CHAR_
// -------------------------
// Char requires a date, time or timestamp field, and then an
// optional SQL datetime format.
else if (item.equals(FCN_CHAR_))
{
choices = getDateTimeFieldNames();
if (choices.length ==0)
{
// put up error message and return
noFields(item);
return;
}
list1 = new JComboBox();
for (int i=0; i< choices.length; ++i)
list1.addItem(choices[i]);
list2 = new JComboBox();
list2.addItem(" "); // Blank for no choice
list2.addItem("ISO");
list2.addItem("USA");
list2.addItem("EUR");
list2.addItem("JIS");
// Create buttons
JButton okButton = new JButton(ResourceLoader.getQueryText("DBQUERY_BUTTON_OK"));
final String fitem = item;
okButton.addActionListener(
new ActionListener(){
public void actionPerformed(ActionEvent ev)
{
String choice1 = (String)list1.getSelectedItem();
String choice2 = (String)list2.getSelectedItem();
String text;
if (choice2.equals(" "))
text = "(" + fitem + "(" + choice1 + ")";
else
text = "(" + fitem + "(" + choice1 + ", " + choice2 + ")";
clause_.appendText(text);
dialog.dispose();
functionComplete();
}
} // end of ActionListenerAdapter
);
JButton cancelButton = new JButton(ResourceLoader.getQueryText("DBQUERY_BUTTON_CANCEL"));
cancelButton.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent ev)
{
dialog.dispose();
}
}
);
JPanel buttonPanel = new JPanel();
buttonPanel.add(okButton);
buttonPanel.add(cancelButton);
dialog = new JDialog(VUtilities.getFrame(this), item, true);
dialog.getContentPane().setLayout(new BorderLayout());
JPanel listPanel = new JPanel();
listPanel.add(list1);
listPanel.add(list2);
dialog.getContentPane().add("Center", new LabelledComponent(ResourceLoader.getQueryText("DBQUERY_TEXT_CHOOSE2") + " " + item + "()", listPanel, false));
dialog.getContentPane().add("South", buttonPanel);
dialog.addWindowListener(
new WindowAdapter() {
public void windowClosing(WindowEvent ev)
{dialog.dispose();}
}
);
dialog.pack();
dialog.setLocationRelativeTo(this);
dialog.setVisible(true);
}
// -------------------------
// FCN_SUBSTR_
// -------------------------
// Substring requires a character field name, start index, and stop index.
else if (item.equals(FCN_SUBSTR_))
{
// Substring requires a character field name and then two numbers
// for the start and end. We use the FROM/FOR syntax, so it
// looks like this: "SUBSTRING( FROM 2 FOR 6)
// The FOR part is optional.
choices = getCharacterFieldNames();
if (choices.length ==0)
{
// put up error message and return
noFields(item);
return;
}
list1 = new JComboBox();
for (int i=0; i< choices.length; ++i)
list1.addItem(choices[i]);
final String fieldFROM = "FROM";
final String fieldFOR = "FOR";
JLabel fromLabel = new JLabel(fieldFROM);
JLabel forLabel = new JLabel(fieldFOR);
textField1 = new JTextField("1", 3);
textField2 = new JTextField(3);
JPanel choicePanel = new JPanel();
choicePanel.add(list1);
choicePanel.add(fromLabel);
choicePanel.add(textField1);
choicePanel.add(forLabel);
choicePanel.add(textField2);
// Create buttons
JButton okButton = new JButton(ResourceLoader.getQueryText("DBQUERY_BUTTON_OK"));
final String fitem = item;
okButton.addActionListener(
new ActionListener(){
public void actionPerformed(ActionEvent ev)
{
String choice1 = (String)list1.getSelectedItem();
String choice2 = textField1.getText().trim();
String choice3 = textField2.getText().trim();
String text;
if (choice2.equals(""))
{
// put up error message and return (leave dialog up)
JOptionPane.showMessageDialog(parent_, // parent
ResourceLoader.getQueryText("DBQUERY_MESSAGE_VALUE_MISSING") + " " + fieldFROM, // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
return;
}
else
{
// verify input is a number
boolean error = false;
try
{
int i = Integer.parseInt(choice2);
if (i<1) error = true;
}
catch(NumberFormatException e) {error = true;}
if (error)
{
// put up error message and return (leave dialog up)
JOptionPane.showMessageDialog(parent_, // parent
fieldFROM + " " + ResourceLoader.getQueryText("DBQUERY_MESSAGE_INVALID_INT_VALUE2"), // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
return;
}
}
if (choice3.equals(""))
text = fitem + "(" + choice1 + " FROM " + choice2 + ")";
else
{
// verify input is a number
boolean error = false;
try
{
int i = Integer.parseInt(choice3);
if (i<0) error = true;
}
catch(NumberFormatException e) {error = true;}
if (error)
{
// put up error message and return (leave dialog up)
JOptionPane.showMessageDialog(parent_, // parent
fieldFOR + " " + ResourceLoader.getQueryText("DBQUERY_MESSAGE_INVALID_INT_VALUE"), // message
fitem + "() " + ResourceLoader.getQueryText("DBQUERY_TITLE_ERROR"), // title
JOptionPane.ERROR_MESSAGE ); // message type
return;
}
text = fitem + "(" + choice1 + " FROM " + choice2 + " FOR " + choice3 + ")";
}
clause_.appendText("(" + text);
dialog.dispose();
functionComplete();
}
} // end of ActionListenerAdapter
);
JButton cancelButton = new JButton(ResourceLoader.getQueryText("DBQUERY_BUTTON_CANCEL"));
cancelButton.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent ev)
{
dialog.dispose();
}
}
);
JPanel buttonPanel = new JPanel();
buttonPanel.add(okButton);
buttonPanel.add(cancelButton);
dialog = new JDialog(VUtilities.getFrame(this), item, true);
dialog.getContentPane().setLayout(new BorderLayout());
dialog.getContentPane().add("Center",new LabelledComponent(ResourceLoader.getQueryText("DBQUERY_TEXT_CHOOSE2") + " " + item + "()",
choicePanel, false));
dialog.getContentPane().add("South", buttonPanel);
dialog.addWindowListener(
new WindowAdapter() {
public void windowClosing(WindowEvent ev)
{dialog.dispose();}
}
);
dialog.pack();
dialog.setLocationRelativeTo(this);
dialog.setVisible(true);
}
// -------------------------
// FCN_TIMESTAMP_
// -------------------------
// Timestamp requires either a timestamp field or a date field and
// a time field.
else if (item.equals(FCN_TIMESTAMP_))
{
// Make two panels, one for choosing a 1 date and 1 time field,
// the other for choosing a timestamp field. Have radio
// buttons to switch between the panes. Disable the pane and
// button if the appropriate fields do not exist.
// Get fields for each list.
String[] dateFields = getFieldNamesOfType(Types.DATE);
String[] timeFields = getFieldNamesOfType(Types.TIME);
String[] timestampFields = getFieldNamesOfType(Types.TIMESTAMP);
// Verify there are fields appropriate for this function
boolean pane1valid = !(dateFields.length == 0 || timeFields.length == 0);
boolean pane2valid = !(timestampFields.length == 0);
if (!pane1valid && !pane2valid)
{
// put up error message and return
noFields(item);
return;
}
dialog = new JDialog(VUtilities.getFrame(this), item, true);
dialog.getContentPane().setLayout(new BorderLayout());
JPanel choicePane = new JPanel(new BorderLayout());
pane1Active = pane1valid; // switch for which pane active;
JRadioButton pane1Button=null, pane2Button;
// Make first panel for date and time fields
if (pane1valid)
{
JPanel pane1 = new JPanel(new BorderLayout());
pane1.setBorder(new CompoundBorder(
new EmptyBorder(10,10,10,10),
new CompoundBorder(LineBorder.createBlackLineBorder(),
new EmptyBorder(10,10,10,10))));
if (pane2valid) // need buttons only if both panes valid
{
pane1Button = new JRadioButton(
ResourceLoader.getQueryText("DBQUERY_BUTTON_TIMESTAMP_2_FIELDS"), true);
pane1Button.setBorder(new EmptyBorder(0,10,10,10));
pane1Button.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent ev)
{
list1.setEnabled(true);
list2.setEnabled(true);
list3.setEnabled(false);
pane1Active = true;
}
}
);
pane1.add("North", pane1Button);
}
list1 = new JComboBox();
for (int i=0; i