import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.table.*;
import java.util.*;
import java.sql.*;
public class QueryBuilder implements ActionListener, ItemListener
{
JFrame frame;
JPanel panel;
QueryBuilder qb;
boolean flag= true;
public static final String type1Class="sun.jdbc.odbc.JdbcOdbcDriver";
public static final String type1Dsn="Jdbc:Odbc:";
public static final String oracle10gClass="oracle.jdbc.driver.OracleDriver";
public static final String oracle10gDsn="jdbc:oracle:thin:@localhost:1521:Xe";
public static String dsnText="";
public static String selectTableName="";
public static String className="";
public static String dsnName="";
public static Connection connection;
public static ResultSet resultSet;
public static ResultSetMetaData rstMetaData;
public static Statement statement;
public static DatabaseMetaData dbmd;
JButton connect,addAllFieldButton,addFieldButton,removeFieldButton,removeAllFieldButton;
JButton addButton,deleteAllButton,goButton,queryButton,exitButton,addOrder,okOrder,cancel;
JButton closeButton,okConnect;
JComboBox tableField,columnField,operatorField,cBoxOperator,orderCombo;
DefaultComboBoxModel cBoxFieldd;
JDialog dialog,tableDialog;
JCheckBox checkBoxOrderBy,loginCheckBox;
static JTextField textFieldValue,dsntextField,userName;
JTextArea queryArea;
JPasswordField passwordField;
JRadioButton rbNone,rbAnd,rbOr,ascending,descending,dsnrb,oraclerb,mysqlrb;
JTable table;
JList availList,selectList;
Vector<String> tableVector;
//Vector<String> fieldVector;
Vector availVector;
Vector selectVector=new Vector();
Vector availColumnTypeList=new Vector();
Vector availColumnList=new Vector();
Vector vList1=new Vector();
Vector vList2=new Vector();
public static LinkedHashSet<String> tableHashSet;
public static LinkedHashSet<String> fieldHashSet;
public static LinkedHashSet<String> availHashSet;
public static LinkedHashSet<String> selectHashSet;
public static Vector<String> orderVector;
Vector<String> temp;
Vector fieldVector=new Vector();
Vector operatorVector=new Vector();
Vector valuesVector=new Vector();
Vector relationVector=new Vector();
public QueryBuilder()
{
frame=new JFrame("Mini Query Builder");
frame.setSize(760,560);
panel=new JPanel(new BorderLayout());
queryArea=new JTextArea(3,20);
textFieldValue=new JTextField(5);
availList=new JList();
selectList=new JList();
//selectList.addActionListener(this);
checkBoxOrderBy=new JCheckBox("Order By");
checkBoxOrderBy.addItemListener(this);
addAllFieldButton=new JButton("<<<");
addAllFieldButton.addActionListener(this);
addFieldButton=new JButton("<<");
addFieldButton.addActionListener(this);
removeFieldButton=new JButton(">>");
removeFieldButton.addActionListener(this);
removeAllFieldButton=new JButton(">>>");
removeAllFieldButton.addActionListener(this);
addButton=new JButton("Add");
addButton.addActionListener(this);
deleteAllButton=new JButton("Delete All");
deleteAllButton.addActionListener(this);
goButton=new JButton("Go");
goButton.addActionListener(this);
queryButton=new JButton("Query");
queryButton.addActionListener(this);
exitButton=new JButton("Exit");
exitButton.addActionListener(this);
cBoxFieldd=new DefaultComboBoxModel();
columnField=new JComboBox();
columnField.addActionListener(this);
String st[]={"=","<","<=",">",">="};
operatorField=new JComboBox(st);
operatorField.addActionListener(this);
rbAnd=new JRadioButton("AND");
rbAnd.addActionListener(this);
rbOr=new JRadioButton("OR");
rbOr.addActionListener(this);
rbNone=new JRadioButton("None");
rbNone.addActionListener(this);
ButtonGroup bg=new ButtonGroup();
bg.add(rbNone);
bg.add(rbOr);
bg.add(rbAnd);
JPanel northPanel=new JPanel(new FlowLayout(FlowLayout.CENTER));
tableField=new JComboBox();
tableField.addActionListener(this);//new ActionListener()
connect=new JButton("Connect");
northPanel.add(connect);
connect.addActionListener(this);
northPanel.add(new JLabel("Select a Table :",JLabel.LEFT));
northPanel.add(tableField);
JPanel cPanel1=new JPanel(new BorderLayout());
cPanel1.add(new JLabel("Available Fields",JLabel.CENTER),BorderLayout.NORTH);
cPanel1.add(availList,BorderLayout.CENTER);
JPanel cPanel2=new JPanel(new GridLayout(6,1));
JPanel p6=new JPanel(new FlowLayout());
cPanel2.add(new JLabel());
JPanel p1=new JPanel(new FlowLayout());
p1.add(addAllFieldButton);
cPanel2.add(p1);
JPanel p2=new JPanel(new FlowLayout());
p2.add(addFieldButton);
cPanel2.add(p2);
JPanel p3=new JPanel(new FlowLayout());
p3.add(removeFieldButton);
cPanel2.add(p3);
JPanel p4=new JPanel(new FlowLayout());
p4.add(removeAllFieldButton);
cPanel2.add(p4);
JPanel p8=new JPanel(new FlowLayout());
JPanel p5=new JPanel(new FlowLayout());
p5.add(checkBoxOrderBy);
cPanel2.add(p5);
JPanel cPanel3=new JPanel(new BorderLayout());
cPanel3.add(new JLabel("Selected Fields",JLabel.CENTER),BorderLayout.NORTH);
cPanel3.add(selectList,BorderLayout.CENTER);
JPanel centerPanel1=new JPanel(new GridLayout(1,3));
centerPanel1.add(new JScrollPane(cPanel1));
centerPanel1.add(new JScrollPane(cPanel2));
centerPanel1.add(new JScrollPane(cPanel3));
JPanel pl1=new JPanel(new GridLayout(5,2));
pl1.add(new JLabel("Field : "));
pl1.add(columnField);
pl1.add(new JLabel("Operator : "));
pl1.add(operatorField);
pl1.add(new JLabel("Value : "));
pl1.add(textFieldValue);
JPanel rbPanel=new JPanel(new FlowLayout(FlowLayout.LEFT));
rbPanel.add(rbNone);
rbNone.setSelected(true);
rbPanel.add(rbAnd);
rbPanel.add(rbOr);
pl1.add(rbPanel);
JPanel bPanel=new JPanel(new FlowLayout());
bPanel.add(addButton);
bPanel.add(deleteAllButton);
pl1.add(bPanel);
JPanel pl2=new JPanel(new GridLayout(4,1));
JPanel pl2a=new JPanel(new FlowLayout());
pl2a.add(goButton);
pl2.add(pl2a);
JPanel pl2b=new JPanel(new FlowLayout());
pl2b.add(queryButton);
pl2.add(pl2b);
JPanel pl2c=new JPanel(new FlowLayout());
pl2c.add(exitButton);
pl2.add(pl2c);
JPanel pl2d=new JPanel(new FlowLayout());
pl2d.add(new JLabel());
pl2.add(pl2d);
JPanel labelPanel=new JPanel(new BorderLayout());
labelPanel.add(new JScrollPane(pl1),BorderLayout.CENTER);
labelPanel.add(new JScrollPane(pl2),BorderLayout.EAST);
JPanel centerPanel=new JPanel(new GridLayout(2,1));
centerPanel.add(centerPanel1);
centerPanel.add(labelPanel);
panel.add(new JScrollPane(northPanel),"North");
panel.add(new JScrollPane(centerPanel),"Center");
panel.add(new JScrollPane(queryArea),"South");
frame.add(panel);
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public static void main(String args[])
{
QueryBuilder qb=new QueryBuilder();
}
public void actionPerformed(ActionEvent e)
{
if(e.getSource()==connect)
{
connectDialog();
}
else if(e.getSource()==tableField)
{
addAvailColumnList();
}
else if(e.getSource()==addAllFieldButton)
{
if(vList1.isEmpty())
return;
else
{
vList2.clear();
vList2.addAll(vList1);
vList1.clear();
selectList.setListData(vList2);
availList.setListData(vList1);
//addAllFieldButton.setEnabled(false);
//addFieldButton.setEnabled(false);
//removeFieldButton.setEnabled(true);
//removeAllFieldButton.setEnabled(true);
}
}
else if(e.getSource()==addFieldButton)
{
if(vList1.isEmpty())
return;
else
{
int list[]=availList.getSelectedIndices();
for(int i=0;i<list.length;i++)
vList2.add(vList1.get(list[i]));
Object rem[]=availList.getSelectedValues();
for(int i=0;i<rem.length;i++)
vList1.remove(rem[i]);
availList.setListData(vList1);
selectList.setListData(vList2);
//removeAllFieldButton.setEnabled(true);
//removeFieldButton.setEnabled(true);
/*if(availList.isSelectionEmpty())
{
addFieldButton.setEnabled(false);
addAllFieldButton.setEnabled(false);
}*/
}
}
else if(e.getSource()==removeFieldButton)
{
if(vList2.isEmpty())
return;
else
{
int list[]=selectList.getSelectedIndices();
for(int i=0;i<list.length;i++)
vList1.add(vList2.get(list[i]));
Object rem[]=selectList.getSelectedValues();
for(int i=0;i<rem.length;i++)
vList2.remove(rem[i]);
availList.setListData(vList1);
selectList.setListData(vList2);
//addFieldButton.setEnabled(true);
//addAllFieldButton.setEnabled(true);
/*if(selectList.isSelectionEmpty())
{
removeFieldButton.setEnabled(false);
removeAllFieldButton.setEnabled(false);
}*/
}
}
else if(e.getSource()==removeAllFieldButton)
{
if(vList2.isEmpty())
return;
else
{
// vList1.clear();
vList1.addAll(vList2);
vList2.clear();
availList.setListData(vList1);
selectList.setListData(vList2);
//removeAllFieldButton.setEnabled(false);
//removeFieldButton.setEnabled(false);
//addAllFieldButton.setEnabled(true);
//addFieldButton.setEnabled(true);
}
}
else if(e.getSource()==addButton)
{
addField();
}
else if(e.getSource()==deleteAllButton)
{
clearAll();
}
else if(e.getSource()==queryButton)
{
queryArea.setText(getQuery());
}
else if(e.getSource()==addOrder)
{
temp=new Vector<String>();
temp.add((String)orderCombo.getSelectedItem());
}
else if(e.getSource()==okOrder)
{
orderVector=new Vector<String>();
Enumeration em=temp.elements();
while(em.hasMoreElements())
{
orderVector.add((String)em.nextElement());
}
orderVector.add((String)orderCombo.getSelectedItem());
temp.clear();
checkBoxOrderBy.setSelected(true);
dialog.setVisible(false);
System.out.println(orderVector);
}
else if(e.getSource()==exitButton)
{
try
{
connection.close();
System.exit(0);
}
catch(Exception et)
{
JOptionPane.showMessageDialog(frame,et);
}
}
else if(e.getSource()==goButton)
{
showTable();
}
else if(e.getSource()==closeButton)
{
tableDialog.setVisible(false);
}
else if(e.getSource()==cancel)
{
dialog.setVisible(false);
}
if(e.getSource()==loginCheckBox)
{
if(loginCheckBox.isSelected())
{
userName.setEnabled(true); passwordField.setEnabled(true);
}
else
{
userName.setEnabled(false); passwordField.setEnabled(false);
}
return;
}
if(e.getSource()==okConnect)
{
try{
if(dsnrb.isSelected())
{
className=type1Class;
Class.forName(className);
dsnName=type1Dsn+dsntextField.getText().trim();
}
else if(oraclerb.isSelected())
{
className=oracle10gClass;
dsnName=oracle10gDsn;
Class.forName(className);
//connection=DriverManager.getConnection(dsnName,userName.getText(),new String(passwordField.getPassword()));
}
setConnection(className,dsnName);
dialog.setVisible(false);
}
catch(Exception ex)
{
int col=ex.toString().indexOf(':');
String tp=ex.toString().substring(0,col);
String msg=ex.toString().substring(col+1)+"from Data Source";
JOptionPane.showMessageDialog(frame,msg,tp,JOptionPane.ERROR_MESSAGE);
}
}
}
public void itemStateChanged(ItemEvent e)
{
JCheckBox jc=(JCheckBox)e.getItem();
if(jc==checkBoxOrderBy)
{
flag=true;
dialog=new JDialog(frame,true);
dialog.setLocation(frame.getLocation());
dialog.getRootPane().setDefaultButton(new JButton("OK"));
dialog.setSize(250,250);
dialog.setTitle("Order By");
JPanel northPanel=new JPanel(new FlowLayout());
northPanel.add(new JLabel("Order By :",FlowLayout.LEFT));
orderCombo=new JComboBox(availVector);
northPanel.add(orderCombo,FlowLayout.CENTER);
JPanel rbPanel=new JPanel(new FlowLayout());
ascending=new JRadioButton("Ascending");
rbPanel.add(ascending);
descending=new JRadioButton("Descending");
rbPanel.add(descending);
ButtonGroup bg=new ButtonGroup();
bg.add(ascending);
bg.add(descending);
ascending.setSelected(true);
// checkOrderList.add(orderCombo.getSelectedItem());
JPanel bPanel=new JPanel(new FlowLayout());
addOrder=new JButton("Add");
addOrder.addActionListener(this);
bPanel.add(addOrder);
okOrder=new JButton("Ok");
okOrder.addActionListener(this);
bPanel.add(okOrder);
cancel=new JButton("Cancel");
bPanel.add(cancel);
cancel.addActionListener(this);
JPanel panel=new JPanel(new GridLayout(4,1));
panel.add(northPanel);
panel.add(new JLabel());
panel.add(rbPanel);
panel.add(bPanel);
dialog.add(panel);
dialog.setVisible(true);
}
}
private void clearAll()
{
fieldVector.clear();
operatorVector.clear();
valuesVector.clear();
relationVector.clear();
rbAnd.setEnabled(false);
rbNone.setEnabled(true);
rbNone.setSelected(true);
rbOr.setEnabled(false);
}
public void setConnection(String className,String dsnName)
{
try
{
System.out.println(className);
System.out.println(dsnName);
tableVector=new Vector<String>();
tableHashSet=new LinkedHashSet<String>();
Class.forName(className);
connection=DriverManager.getConnection(dsnName);
dbmd=connection.getMetaData();
resultSet=dbmd.getTables(null,null,null,new String[]{"Table"});
while(resultSet.next())
{
String str=resultSet.getString(3);
tableVector.add(str);
tableHashSet.add(str);
}
//add table in Table ComboBox
Iterator<String> it=tableHashSet.iterator();
while(it.hasNext())
{
tableField.addItem(it.next());
}
}
catch(Exception e)
{
JOptionPane jop=new JOptionPane();
jop.showMessageDialog(frame,e);
}
}
// Connect Dialog Box
public void connectDialog()
{
dialog=new JDialog(frame,"Connect");
dialog.setSize(350,250);
dsntextField=new JTextField(15);
dsnrb=new JRadioButton("Specify DSN");
dsnrb.setSelected(true);
oraclerb=new JRadioButton("Oracle 10g");
mysqlrb=new JRadioButton("My SQL Server");
mysqlrb.setEnabled(false);
ButtonGroup bg=new ButtonGroup();
bg.add(dsnrb);
bg.add(oraclerb);
bg.add(mysqlrb);
loginCheckBox=new JCheckBox("Login using");
loginCheckBox.setSelected(false);
loginCheckBox.addActionListener(this);
userName=new JTextField("system",6);
passwordField=new JPasswordField("oracle",6);
okConnect=new JButton("OK");
okConnect.addActionListener(this);
cancel=new JButton("Cancel");
cancel.addActionListener(this);
JPanel p1=new JPanel(new FlowLayout(FlowLayout.LEFT));
p1.add(dsnrb);
p1.add(dsntextField);
JPanel p2=new JPanel(new FlowLayout(FlowLayout.LEFT));
p2.add(oraclerb);
JPanel p3=new JPanel(new FlowLayout(FlowLayout.LEFT));
p3.add(mysqlrb);
JPanel p5=new JPanel(new FlowLayout(FlowLayout.LEFT));
p5.add(loginCheckBox);
JPanel p4=new JPanel(new FlowLayout());
p4.add(new JLabel("Username "));
p4.add(userName);
p4.add(new JLabel("Password"));
p4.add(passwordField);
JPanel centerPanel=new JPanel(new GridLayout(5,1));
centerPanel.add(p1);
centerPanel.add(p2);
centerPanel.add(p3);
centerPanel.add(p5);
centerPanel.add(p4);
JPanel southPanel=new JPanel(new FlowLayout());
southPanel.add(okConnect);
southPanel.add(cancel);
JPanel panel=new JPanel(new BorderLayout());
panel.add(new JLabel("<html><big>Select Data Source...</big></html>"),BorderLayout.NORTH);
panel.add(new JScrollPane(centerPanel),BorderLayout.CENTER);
panel.add(new JScrollPane(southPanel),BorderLayout.SOUTH);
dialog.add(panel);
dialog.setVisible(true);
dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
}
public void addAvailColumnList()
{
String tableName=(String)tableField.getSelectedItem();
availHashSet=new LinkedHashSet<String>();
fieldHashSet=new LinkedHashSet<String>();
availVector=new Vector();
try
{
String aa="Select * from ".concat(tableName);
Class.forName(className);
connection=DriverManager.getConnection(dsnName);
statement=connection.createStatement();
resultSet=statement.executeQuery(aa);
rstMetaData=resultSet.getMetaData();
int count=rstMetaData.getColumnCount();
vList1.clear();
availColumnTypeList.clear();
availColumnList.clear();
for(int i=1;i<=count;i++)
{
String str=rstMetaData.getColumnName(i);
availHashSet.add(str);
availVector.add(str);
fieldHashSet.add(str);
vList1.add(str);
availColumnTypeList.add(rstMetaData.getColumnType(i));
availColumnList.add(rstMetaData.getColumnName(i));
}
columnField.removeAllItems();
availList.setListData(availVector);
Iterator<String> it=fieldHashSet.iterator();
while(it.hasNext())
{
columnField.addItem(it.next());
}
}
catch(Exception e)
{
JOptionPane.showMessageDialog(frame,e);
}
}
public void addField()
{
if(!columnField.isEnabled())
return;
else
{
fieldVector.add((String)columnField.getSelectedItem());
operatorVector.add((String)operatorField.getSelectedItem());
valuesVector.add((String)textFieldValue.getText());
String op="";
if(rbOr.isSelected())
op="OR";
else if(rbAnd.isSelected())
op="AND";
else if(rbNone.isSelected())
op="";
System.out.println("Ramjee");
relationVector.add(op);
if(fieldVector.size()==1)
{
rbAnd.setEnabled(true);
rbOr.setEnabled(true);
rbNone.setEnabled(false);
}
}
}
public String getQuery()
{
String query="Select ";
if(vList2.isEmpty())
query+=" * ";
else
{
for(int i=0;i<vList2.size();i++)
{
if(i>0)
query+=" ,";
query+=vList2.get(i);
}
}
query+=" from "+tableField.getSelectedItem();
if(!fieldVector.isEmpty())
{
query+=" where ";
for(int i=0;i<fieldVector.size();i++)
{
query+=" "+(String)relationVector.get(i)+" "+fieldVector.get(i)+" "+operatorVector.get(i);
int t=(Integer)availColumnTypeList.get(availColumnList.indexOf(fieldVector.get(i)));
if(t==Types.VARCHAR || t==Types.DATE || t==Types.CHAR)
{
query+="'"+valuesVector.get(i)+"'";
}
else
{
query+=valuesVector.get(i);
}
}
}
return query;
}
public void showTable()
{
int p=0,k=0;
String str[][];
String strr[];
try
{
dbmd=connection.getMetaData();
statement=connection.createStatement();
Statement st1=connection.createStatement();
System.out.println(dbmd.getDriverName());
resultSet=dbmd.getTables(null,null,null,new String[]{"Table"});
resultSet=statement.executeQuery(getQuery());
ResultSet rs1=st1.executeQuery(getQuery());
rstMetaData=resultSet.getMetaData();
int count=rstMetaData.getColumnCount();
strr=new String[count];
for(int i=0;i<count;i++)
{
strr[i]=rstMetaData.getColumnName(i+1);
System.out.println(strr[i]);
}
while(resultSet.next())
{
k++;
for(int i=1;i<=count;i++)
System.out.println(resultSet.getString(i));
}
str=new String[k][count];
int j=0;
while(rs1.next())
{
for(int i=0;i<count;i++)
{
str[j][i]=rs1.getString(i+1);
System.out.print(str[j][i]+" ");
}
j++;
}
System.out.println("\n"+k);
// Table Box
tableDialog=new JDialog(frame,true);
tableDialog.setSize(450,350);
tableDialog.setTitle("Table");
JPanel tablePanel=new JPanel(new BorderLayout());
try {
table=new JTable(str,strr);
}
catch(Exception et)
{
JOptionPane jop=new JOptionPane();
jop.showMessageDialog(frame,et);
}
int v=ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED;
int h=ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED;
JScrollPane jsp=new JScrollPane(table,v,h);
tablePanel.add(jsp);
JPanel southPanel=new JPanel(new BorderLayout());
closeButton=new JButton("Close");
closeButton.addActionListener(this);
tablePanel.add(closeButton,BorderLayout.SOUTH);
tableDialog.add(tablePanel);
tableDialog.setVisible(true);
}
catch(Exception ex)
{
int col=ex.toString().indexOf(':');
String tp=ex.toString().substring(0,col);
String msg=ex.toString().substring(col+1)+"from Data Source";
JOptionPane.showMessageDialog(frame,msg,tp,JOptionPane.ERROR_MESSAGE);
}
}
}
You can request for source code to this e-mail
