database

DBOperation.java

package database；

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;


public class DBOperation {
public static final String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static final String url = "jdbc:sqlserver://localhost:1433;DatabaseName=school";
public static final String user = "sa";
public static final String pwd = "123456";
public static Connection conn = null;

static {
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url, user, pwd);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

public static ResultSet getStudentName() throws Exception {
String sql = "select student.student_name from student";
Statement stmt = conn.createStatement();
return stmt.executeQuery(sql);
}

public static ResultSet getCourseByName(String sname) throws Exception{
String sql = "select student.student_name,course.course_name from student,course,student_course where student.student_id = student_course.student_id and course.course_id = student_course.course_id and student.student_name = '"+sname+"' ;" ;
Statement stmt = conn.createStatement();
return stmt.executeQuery(sql);
}

public static ResultSet getCourseName() throws Exception{
String sql = "select course_name from course";
Statement stmt = conn.createStatement();
return stmt.executeQuery(sql);
}

public static ResultSet getCourseInformationByName(String courseName) throws Exception{
String sql = "select count(*) number,avg(student_course.score) average\r\n" + 
"from student \r\n" + 
"inner join student_course \r\n" + 
" on student.student_id = student_course.student_id\r\n" + 
"inner join course\r\n" + 
" on student_course.course_id = course.course_id\r\n" + 
"where course.course_name = '" + courseName + "'";
Statement stmt = conn.createStatement();
return stmt.executeQuery(sql);
}

}

Main.java

package view;


import javax.swing.JFrame;
import javax.swing.JTabbedPane;


public class Main extends JFrame{

JTabbedPane pane = new JTabbedPane(JTabbedPane.TOP);
Panel1 panel1 = new Panel1();
Panel2 panel2 = new Panel2();
public Main() {
pane.add("search1",panel1);
pane.add("seerch2",panel2);
this.add(pane);
this.setSize(600, 400);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setVisible(true);
}

public static void main(String[] args) {
new Main();
}

}

Panel1.java

package view;


import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.util.Vector;


import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import database.*;


public class Panel1 extends JPanel{
JPanel p_top = new JPanel();
DefaultComboBoxModel comModel = new DefaultComboBoxModel();
JComboBox com = new JComboBox(comModel);
JButton btn = new JButton("search");

JPanel p_center = new JPanel();
String[] names = {"name","course"};
DefaultTableModel tableModel = new DefaultTableModel(null, names);
JTable table = new JTable(tableModel);
JScrollPane pane = new JScrollPane(table);

public Panel1(){
super();
ResultSet rs;
try {
rs = DBOperation.getStudentName();
while(rs.next()) {
comModel.addElement(rs.getString(1));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
btn.addActionListener(new ActionListener() {

@Override
public void actionPerformed(ActionEvent e) {
String name = (String) com.getSelectedItem();
while(tableModel.getRowCount()>0) {
tableModel.removeRow(0);
}
try {
ResultSet rs = DBOperation.getCourseByName(name);
while(rs.next()) {
Vector v = new Vector();
v.addElement(rs.getString("student_name"));
v.addElement(rs.getString("course_name"));
tableModel.addRow(v);
}
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
});
p_top.add(com);
p_top.add(btn);
p_center.add(pane);
this.setLayout(new BorderLayout());
this.add(p_top,BorderLayout.NORTH);
this.add(p_center, BorderLayout.CENTER);
}
}