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 Show documentation
Show all versions of jt400 Show documentation
The Open Source version of the IBM Toolbox for Java
The newest version!
///////////////////////////////////////////////////////////////////////////////
//
// 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 © 2015 - 2025 Weber Informatics LLC | Privacy Policy