import java.net.URL;
import java.io.*;
import java.sql.*;
import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
class QueryDB 
{
	public static void main(String[] args) 
	{
   
    
    
		JFrame myframe=new QueryFrame();
		myframe.show();
	
	}

}
class QueryFrame extends JFrame implements ActionListener
{
	private Connection con=null;
	private Statement stmt=null;
	private ResultSet rs=null;
	private JLabel conditionlabel=new JLabel("請填寫下列查尋條件",SwingConstants.CENTER);
	private JLabel namelabel=new JLabel("姓名",SwingConstants.RIGHT);
	private JTextField name=new JTextField(5);
	private JLabel sexlabel=new JLabel("性別",SwingConstants.RIGHT);
	private JTextField sex=new JTextField(3);
	private JLabel agelabel=new JLabel("年齡",SwingConstants.RIGHT);
	private JTextField age=new JTextField(3);
	private JLabel majorlabel=new JLabel("專業",SwingConstants.RIGHT);
	private JTextField major=new JTextField(8);
	private JButton commit=new JButton("查詢");
    private JButton in=new JButton("插入");
    private JButton xg=new JButton("修改");
	private JLabel resultlabel=new JLabel("查詢結果",SwingConstants.CENTER);
	private JTextArea resultarea=new JTextArea(10,28);
	private String command=null;
	public QueryFrame()
	{
		setTitle("數據庫查詢,插入,修改");
		setSize(600,500);
		addWindowListener(new WindowAdapter(){
		public void windowClosing(WindowEvent e)
			{ System.exit(0);}
		});
		getContentPane().setLayout(new GridBagLayout());
		GridBagConstraints gbc=new GridBagConstraints();
		gbc.fill=GridBagConstraints.NONE;
		gbc.anchor=GridBagConstraints.CENTER;
		gbc.weightx=100;
		gbc.weighty=100;
		add(conditionlabel,gbc,3,0,5,1);
		add(name,gbc,1,1,2,1);
		add(sex,gbc,4,1,1,1);
		add(age,gbc,6,1,1,1);
		gbc.anchor=GridBagConstraints.WEST;
		add(major,gbc,8,1,3,1);
		gbc.anchor=GridBagConstraints.CENTER;
		add(in,gbc,6,2,1,1);
        add(xg,gbc,5,2,1,1);
        add(commit,gbc,4,2,1,1);
        add(resultlabel,gbc,4,3,3,1);
		JScrollPane scrollpane =new JScrollPane(resultarea);
		add(scrollpane,gbc,3,4,5,3);
		resultarea.setEditable(false);
		resultarea.setLineWrap(true);
		gbc.anchor=GridBagConstraints.EAST;
		add(namelabel,gbc,0,1,1,1);
		add(sexlabel,gbc,3,1,1,1);
		add(agelabel,gbc,5,1,1,1);
		gbc.anchor=GridBagConstraints.CENTER;
		add(majorlabel,gbc,7,1,1,1);
		commit.addActionListener(this);
		commit.setNextFocusableComponent(name);
           
		 try{
			  DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
			  con=DriverManager.getConnection("jdbc:odbc:shujuku","scott","tiger");
			  stmt=con.createStatement();
		  }
			catch(Exception ex)
		    {
				resultarea.append(ex.getMessage()+"\n");
				return;
			}
	}
	public void add(Component c,GridBagConstraints gbc,int x,int y,int w,int h)
	{
		gbc.gridx=x;
		gbc.gridy=y;
		gbc.gridwidth=w;
		gbc.gridheight=h;
		getContentPane().add(c,gbc);
	}
	public void actionPerformed(ActionEvent evt)
	{
		try{
			String namevalue=name.getText().trim();
			String sexvalue=sex.getText().trim();
			String agevalue=age.getText().trim();
			String majorvalue=major.getText().trim();
			String sname,ssex,sage,smajor;
			sname=" LIKE '%"+namevalue+"%'";
			ssex=" LIKE '%"+sexvalue+"%'";
			if(agevalue.equals(""))
			   sage=" BETWEEN 15 AND 35";
			else sage="="+agevalue;
			   smajor=" LIKE '%"+majorvalue+"%'";
			  
			command="SELECT * FROM Student WHERE name" +sname+" AND sex"+ssex+" AND age"+sage+" AND major"+smajor;
			command=new String(command.getBytes(),"ISO-8859-1");
			rs=stmt.executeQuery(command);
			resultarea.setText("");
			if (!rs.next())
				resultarea.setText("找不到符合此條件的記錄");
			else 
			{
				do{
					String rename=rs.getString("name").trim();
					String resex=rs.getString("sex").trim();
					String reage=rs.getString("age").trim();
					String remajor=rs.getString("major").trim();
					rename=new String(rename.getBytes("ISO-8859-1"),"GB2312");
					resex=new String(resex.getBytes("ISO-8859-1"),"GB2312");
					reage=new String(reage.getBytes("ISO-8859-1"),"GB2312");
					remajor=new String(remajor.getBytes("ISO-8859-1"),"GB2312");
					resultarea.append(rename);
					int length=rename.length();
					for(int i=1;i<=(16-2*length);i++)
						resultarea.append("  ");
					resultarea.append(resex+"            ");
					resultarea.append(reage+"                 ");
					resultarea.append(remajor+"\n");

				}
				while (rs.next());
				
			}
			
		}
		catch (Exception ex)
		{
			resultarea.append(ex.getMessage()+"\n");
		}
	}
}