//分类显示数据库里的内容
package mypkg;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.util.*;
import java.sql.*;
import java.sql.Statement;
import javax.swing.table.*;
import javax.swing.tree.*;
/**
*
* @author Administrator
*/
public class ClassBrowse extends JFrame implements TreeSelectionListener {
Statement stmt;
ResultSet rs,rsl;
DefaultTableModel dtm;//定义数据模型
String[]title;//表头数组
JTree tree;//树
JTable table;
DefaultTreeModel treeModel = null;//定义一个默认的树模型,用于建立树
JScrollPane scroll1,scroll2;//定义两个滚动面板
JSplitPane splitPane;//定义一个分割面板
private Object con;
public ClassBrowse(){
try{
stmt = ConnectServer1. con. createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
DefaultMutableTreeNode root = new DefaultMutableTreeNode("专业");//根
treeModel = new DefaultTreeModel(root);//建立树模型
initTree(root,0);//调用递归函数建立树模型
tree = new JTree(treeModel);//利用树模型建立树
scroll1 = new JScrollPane(tree);
tree.addTreeSelectionListener(this);
rs = stmt.executeQuery("select classclass.name as 班级,xsda.no as 学号,xsda.name as 姓名" +
" from xsda,classclass where xsda.classid = classclass.id");
ResultSetMetaData dbmd = rs.getMetaData();//得到记录集的元数据,建立表头数组
title = new String[dbmd.getColumnCount()];
for(int i=1;i <=dbmd.getColumnCount();i++)
title[i-1] = dbmd.getColumnName(i);
dtm = new DefaultTableModel(null,title);//建立表格数据模型
table = new JTable(dtm);//利用数据模型建立表格
scroll2 =new JScrollPane(table);
initTable();//建立表
splitPane = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT,scroll1,scroll2);
splitPane.setOneTouchExpandable(true);
splitPane.setDividerLocation(200);
this.getContentPane().add(splitPane,null);}
catch(SQLException e){System.out.println(e);}
setTitle("分类查询学生信息");
setSize(500,400);
setVisible(true);
}
void initTree(DefaultMutableTreeNode root,int id){//这是递归函数
DefaultMutableTreeNode node = null;//定义节点
try{
Statement stmt = ConnectServer1. con. createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select * from classclass where parent =" + id);//查询班级类表
while(rs.next()){
int nno = rs.getInt("id");//取得分类ID
String nname = rs.getString("name").trim();//取得分类名称
node = new DefaultMutableTreeNode(nname);//建立树的节点
treeModel.insertNodeInto(node, root, root.getChildCount());//插入节点
initTree(node,nno);}
}
catch(Exception e){}
finally{
try{
rs.close();
}catch(Exception e){}
}
}
public void valueChanged(TreeSelectionEvent e){
JTree tree=(JTree)e.getSource();
//利用JTree的getLastSelectedPathCoponent()方法取得目前选取的节点
DefaultMutableTreeNode selectionNode = (DefaultMutableTreeNode)tree.getLastSelectedPathComponent();
String name = ((String)selectionNode.getUserObject());
String sql = "selcet classclass.name as 班级,xsda.no as 学号,xsda.name as 姓名 from xsda,classclass where xsda.classid = classclass.id";
if(!name.equals("专业"))
sql=sql + "and classid in(select id from classclass where rtrim(ltrim(str(id)))like(select ltrim(rtrim(str(id)))+'%'from classclass where rtrim(ltrim(name))='"+name+"'))";
try{
rs = stmt.executeQuery(sql);//利用嵌套查询查出所有数据
initTable();
} catch(Exception ee){}
}
void initTable(){
dtm.setRowCount(0);
try{
rs.beforeFirst();
while(rs.next()){
Vector v1=new Vector();
for(int i=1;i <=title.length;i++)
v1.addElement(rs.getString(i));
dtm.addRow(v1);
}
}catch(SQLException e){e.printStackTrace();}
dtm.fireTableStructureChanged();
}
public static void main(String arg[]){
JFrame.setDefaultLookAndFeelDecorated(true);
Font font = new Font("JFrame",Font.PLAIN,14);
Enumeration keys = UIManager.getLookAndFeelDefaults().keys();
while(keys.hasMoreElements()){
Object key = keys.nextElement();
if(UIManager.get(key)instanceof Font)
UIManager.put(key, font);
}
if(!ConnectServer1.conn("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=xsgl","sa","")){
JOptionPane.showMessageDialog(null, "连接数据库失败");
System.exit(0);//关闭数据库
}
new ClassBrowse();
}
}