DBMS project

带界面的数据库数据查询

package panel;import java.awt.Component;import java.awt.Container;import java.awt.Dimension;import java.awt.FlowLayout;import java.awt.Frame;import java.awt.GridBagConstraints;import java.awt.GridBagLayout;import java.awt.GridLayout;import java.awt.Label;import java.awt.TextField;import java.awt.event.ActionEvent;import java.awt.event.ActionListener;import java.util.ArrayList;import java.util.Vector;import java.sql.*;import javax.swing.*;import javax.swing.table.AbstractTableModel;import javax.swing.table.DefaultTableModel;import javax.swing.table.TableColumn;import javax.swing.table.TableColumnModel;import javax.swing.table.TableModel;public class panelSQL extends JFrame {String[] coloumnNames ; Object[][] data; static int C_Width = 900;static ArrayList<String> paper_name = new ArrayList<String>();static ArrayList<String> year = new ArrayList<String>();static ArrayList<String> conference_name = new ArrayList<String>();static ArrayList<String> paper_author = new ArrayList<String>();static ArrayList<String> location = new ArrayList<String>();static ArrayList<String> affiliation =  new ArrayList<String>();static ArrayList<String> pc_name = new ArrayList<String>();static ArrayList<String> pc_affiliation = new ArrayList<String>();static ArrayList<String> pc_conference = new ArrayList<String>();static String Search_In_Table;static int count_radio1=0,count_radio2 = 0,count_radio3 =0;//单选按钮    JRadioButton radio1 = new JRadioButton("Author"); // paper_name,paper_author, year, conference_name, conference location      JRadioButton radio2 = new JRadioButton("PC member"); //name, affiliation, and conference name.     JRadioButton radio3 = new JRadioButton("Affiliation");  // paper name, paper's authors, year, conference name, and conference location.private static JTable table;private static DefaultTableModel model; public panelSQL(){Container contentPane=this.getContentPane();/* 创建一个面板对象,指定布局管理器为GridLayout,1行2列.Jpanel的默认版面管理为FlowLayout */final JPanel jPanel1=new JPanel(new FlowLayout(FlowLayout.CENTER,20,20));//利用String数组建立JComboBoxString[] jcomboBoxContain={"DEFAULT","sigmod 2012","sigmod 2013","sigmod 2014"};final JComboBox jComboBox1=new JComboBox(jcomboBoxContain);//jComboBox1.addItem("其他"); //在列表框选项的最后再添加一个"其他"选项//设置jList1对象的带标题边框jComboBox1.setBorder(BorderFactory.createTitledBorder("conference:   "));//文本框final TextField text1 = new TextField();text1.setColumns(20);//TablecoloumnNames = null;model = new DefaultTableModel(data, coloumnNames);table = new JTable(model);//JTable table = new JTable(new TbModel());table.setPreferredScrollableViewportSize(new Dimension(C_Width-50, 400));final JScrollPane pane = new JScrollPane(table);    ButtonGroup group = new ButtonGroup();    group.add(radio1);    group.add(radio2);    group.add(radio3);//按钮JButton jButton = new JButton("  search  "); // 按钮jButton.addActionListener(new ActionListener(){public void actionPerformed(ActionEvent e){if(radio1.isSelected()){Search_In_Table = "author";DefaultTableModel tableModel = (DefaultTableModel) table.getModel();//删除原有的表头if(tableModel.getColumnCount()!=0){//System.out.println("已经有\t "+tableModel.getColumnCount()+" 列了");int columncount = tableModel.getColumnCount()-1;while(columncount>=0){TableColumnModel columnModel = table.getColumnModel();TableColumn tableColumn = columnModel.getColumn(columncount);columnModel.removeColumn(tableColumn);tableModel.setColumnCount(columncount);columncount = tableModel.getColumnCount()-1;}//System.out.println("已经有\t "+tableModel.getColumnCount()+" 列了");}tableModel.addColumn("paper name");tableModel.addColumn("paper's author");tableModel.addColumn("year");tableModel.addColumn("conference name");tableModel.addColumn("conference location");//System.out.println("before delete\t"+ tableModel.getRowCount());tableModel.setRowCount(0);//删除原有数据paper_name.clear();year.clear();conference_name.clear();paper_author.clear();location.clear();tableModel.fireTableDataChanged();//System.out.println("after delete\t"+ tableModel.getRowCount());//第一次进这个表的时候会更改表的表头count_radio1++;String msg = text1.getText();//paper_name,paper_author, year, conference_nameString petName = (String) jComboBox1.getSelectedItem();//System.out.println(petName);String query = null;if(petName.equals("sigmod 2012")){System.out.println(petName);query ="select paper_name, author_name, conference, conference_name,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id "+ "and conference_id = 2012 ";if(msg!= null){String test = "and author.author_name like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}query = String.format("%s %s",query, "group by author.paper_id");System.out.println(query);}else if(petName.equals("sigmod 2013")){query ="select paper_name, author_name, conference, conference_name,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id "+ "and conference_id = 2013 ";//+ "group by author.paper_id";if(msg!= null){String test = "and author.author_name like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}//+ "group by author.paper_id";query = String.format("%s %s",query, "group by author.paper_id");}else if(petName.equals("sigmod 2014")){query ="select paper_name, author_name, conference, conference_name,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id "+ "and conference_id = 2014 ";//+ "group by author.paper_id";if(msg!= null){String test = "and author.author_name like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}//+ "group by author.paper_id";query = String.format("%s %s",query, "group by author.paper_id");}else{query ="select paper_name, author_name, conference, conference_name,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id ";//+ "group by author.paper_id";if(msg!= null){String test = "and author.author_name like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}//+ "group by author.paper_id";query = String.format("%s %s",query, "group by author.paper_id");}try {SQLsearch(query);} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}//String[] coloumnNames = {"paper name", "paper's author","year", "conference name","conference location"}; data = new Object[paper_name.size()][5];for(int i=0; i< paper_name.size();i++){String[] arr = new String[5];arr[0] = paper_name.get(i);arr[1] = paper_author.get(i);arr[2] = year.get(i);arr[3] = conference_name.get(i);arr[4] = location.get(i);tableModel.addRow(arr);}TableColumn column=null;    for (int i=0;i<5;i++){        column=table.getColumnModel().getColumn(i);        if (i == 0) column.setPreferredWidth(250);        else if(i == 2) column.setPreferredWidth(70);        else if(i == 3) column.setPreferredWidth(130);        else        column.setPreferredWidth(200);      }    table.invalidate();//model.fireTableStructureChanged();}if(radio2.isSelected()){Search_In_Table = "pc";DefaultTableModel tableModel = (DefaultTableModel) table.getModel();//删除原有的表头if(tableModel.getColumnCount()!=0){//System.out.println("已经有\t "+tableModel.getColumnCount()+" 列了");int columncount = tableModel.getColumnCount()-1;while(columncount>=0){TableColumnModel columnModel = table.getColumnModel();TableColumn tableColumn = columnModel.getColumn(columncount);columnModel.removeColumn(tableColumn);tableModel.setColumnCount(columncount);columncount = tableModel.getColumnCount()-1;}}tableModel.addColumn("name");tableModel.addColumn("affiliation");tableModel.addColumn("conference name");tableModel.setRowCount(0);//删除原有数据pc_name.clear();pc_affiliation.clear();pc_conference.clear();tableModel.fireTableDataChanged();//第一次进这个表的时候会更改表的表头count_radio2++;String msg = text1.getText();//paper_name,paper_author, year, conference_nameString petName = (String) jComboBox1.getSelectedItem();//System.out.println(petName);String query = null;if(petName.equals("sigmod 2012")){System.out.println(petName);query ="select distinct member_name,affilinaty, conference_name "+ "from pc,conference ";if(msg!= null){String test = "where conference.conference_id = 2012 and member_name like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}}else if(petName.equals("sigmod 2013")){query ="select distinct member_name,affilinaty, conference_name "+ "from pc,conference ";if(msg!= null){String test = "where conference.conference_id = 2013 and member_name like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}}else if(petName.equals("sigmod 2014")){query ="select distinct member_name,affilinaty, conference_name "+ "from pc,conference ";if(msg!= null){String test = "where conference.conference_id = 2014 and member_name like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}}else{query ="select distinct member_name,affilinaty, conference_name "+ "from pc,conference ";if(msg!= null){String test = "where  member_name like '%";String test1 ="%'";//System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);//System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//}System.out.println("2:\t"+query);}try {//连接mysql进行查询SQLsearch(query);} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}int cols =tableModel.getColumnCount();System.out.println("现在表里面的列数为\t"+cols);for(int i=0; i< pc_name.size();i++){String[] arr = new String[cols];arr[0] = pc_name.get(i);arr[1] = pc_affiliation.get(i);arr[2] = pc_conference.get(i);tableModel.addRow(arr);}//设置表每一列的宽度TableColumn column=null;    for (int i=0;i<cols;i++){        column=table.getColumnModel().getColumn(i);        if (i == 0) column.setPreferredWidth(250);        else        column.setPreferredWidth(200);      }table.invalidate();//model.fireTableStructureChanged();}if(radio3.isSelected()){////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////Search_In_Table = "affiliation";////////////////////////////////////////////////////////////////////////////DefaultTableModel tableModel = (DefaultTableModel) table.getModel();//删除原有的表头if(tableModel.getColumnCount()!=0){//System.out.println("已经有\t "+tableModel.getColumnCount()+" 列了");int columncount = tableModel.getColumnCount()-1;while(columncount>=0){TableColumnModel columnModel = table.getColumnModel();TableColumn tableColumn = columnModel.getColumn(columncount);columnModel.removeColumn(tableColumn);tableModel.setColumnCount(columncount);columncount = tableModel.getColumnCount()-1;}//System.out.println("已经有\t "+tableModel.getColumnCount()+" 列了");}tableModel.addColumn("paper name");tableModel.addColumn("paper's author");tableModel.addColumn("affiliation");tableModel.addColumn("year");tableModel.addColumn("conference name");tableModel.addColumn("conference location");tableModel.setRowCount(0);//删除原有数据paper_name.clear();year.clear();conference_name.clear();paper_author.clear();location.clear();affiliation.clear();tableModel.fireTableDataChanged();//第一次进这个表的时候会更改表的表头count_radio3++;String msg = text1.getText();//paper_name,paper_author, year, conference_nameString petName = (String) jComboBox1.getSelectedItem();//System.out.println(petName);String query = null;if(petName.equals("sigmod 2012")){System.out.println(petName);query ="select paper_name, author_name,affilinaty, conference, conference_name,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id "+ "and conference_id = 2012 ";if(msg!= null){String test = "and author.affilinaty like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}query = String.format("%s %s",query, "group by author.paper_id");}else if(petName.equals("sigmod 2013")){query ="select paper_name, author_name,affilinaty, conference, conference_name,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id "+ "and conference_id = 2013 ";//+ "group by author.paper_id";if(msg!= null){String test = "and author.affilinaty like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}//+ "group by author.paper_id";query = String.format("%s %s",query, "group by author.paper_id");}else if(petName.equals("sigmod 2014")){query ="select paper_name, author_name,affilinaty, conference, conference_name,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id "+ "and conference_id = 2014 ";//+ "group by author.paper_id";if(msg!= null){String test = "and author.affilinaty like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}//+ "group by author.paper_id";query = String.format("%s %s",query, "group by author.paper_id");}else{query ="select paper_name, author_name, affilinaty, conference, conference_name ,location "+ "from author, paper,conference "+ "where paper.conference = conference.conference_id and author.paper_id = paper.paper_id ";//+ "group by author.paper_id";if(msg!= null){String test = "and author.affilinaty like '%";String test1 ="%'";System.out.println("msg:\t"+msg);test = String.format("%s%s%s",test,msg,test1);System.out.println("test:\t"+test);query=String.format("%s %s", query,test);//System.out.println("2:\t"+query);}//+ "group by author.paper_id";query = String.format("%s %s",query, "group by author.paper_id");}try {SQLsearch(query);} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}//String[] coloumnNames = {"paper name", "paper's author","year", "conference name","conference location"}; data = new Object[paper_name.size()][6];for(int i=0; i< paper_name.size();i++){String[] arr = new String[6];arr[0] = paper_name.get(i);arr[1] = paper_author.get(i);arr[2] = affiliation.get(i);arr[3] = year.get(i);arr[4] = conference_name.get(i);arr[5] = location.get(i);tableModel.addRow(arr);}TableColumn column=null;    for (int i=0;i<6;i++){        column=table.getColumnModel().getColumn(i);        if (i == 0) column.setPreferredWidth(250);        else if(i == 3) column.setPreferredWidth(70);        else if(i == 4) column.setPreferredWidth(130);        else        column.setPreferredWidth(200);      }table.invalidate();//model.fireTableStructureChanged();}}});Label label1 = new Label("key word:");////////////////////////////////////////////////////////////////////////////////////////jPanel1.add(jComboBox1);jPanel1.add(label1);jPanel1.add(text1);//jPanel1.add(jButton1);//单选jPanel1.add(radio1);  jPanel1.add(radio2);  jPanel1.add(radio3);//buttomjPanel1.add(jButton);//tablejPanel1.add(pane);////////////////////////////////添加面板至父容器//contentPane.setLayout(new GridLayout(3,1));contentPane.add(jPanel1);//frame.pack();//设置本窗体的标题this.setTitle("search");//设置本窗体显示的初始大小this.setSize(C_Width,700);//设置本窗体初始可见this.setVisible(true); // this.setVisible(true);}public static void main( String args[]) throws SQLException  {panelSQL pane = new panelSQL();//pane.SQLsearch();}private void SQLsearch(String query) throws SQLException{// TODO Auto-generated method stubtry{Class.forName("com.mysql.jdbc.Driver");}catch(ClassNotFoundException e){e.printStackTrace();}Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sigmod","root","1926");java.sql.Statement stmt = conn.createStatement();java.sql.ResultSet rs = null;rs=stmt.executeQuery(query);//int i =0 ;// paper_name,paper_author, year, conference_nameif(Search_In_Table.equals("pc")){while(rs.next()){//Object[] raw = {rs.getString("paper_name"),rs.getString("author_name"),rs.getString("conference"),rs.getString("conference_name")};//System.out.println(rs.getString("member_name")+"\t"+rs.getString("affilinaty")+"\t"+rs.getString("conference_name") );pc_name.add(rs.getString("member_name"));pc_affiliation.add(rs.getString("affilinaty"));pc_conference.add(rs.getString("conference_name"));   }}else{while(rs.next()){  //Object[] raw = {rs.getString("paper_name"),rs.getString("author_name"),rs.getString("conference"),rs.getString("conference_name")};  paper_name.add(rs.getString("paper_name"));  paper_author.add(rs.getString("author_name"));  year.add(rs.getString("conference"));  conference_name.add(rs.getString("conference_name"));  location.add(rs.getString("location"));  if(Search_In_Table.equals("affiliation"))  {  affiliation.add(rs.getString("affilinaty"));  }    //System.out.print("<Information>\n");    //System.out.println();   // i++;   }}rs.close();stmt.close();conn.close();}}