package Studentss;//在SQL Server中定义的数据库
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.;
import javax.swing.;
public class Operation extends JFrame implements ActionListener {
private static final long serialVersionUID = 1L;
JButton add, select, del, update,sure;
JLabel tip=new JLabel("输入要操作的表");
JTextField chose= new JTextField();
JTable table1;
JTable table2;
Object body1[][] = new Object[50][6];
Object body2[][] = new Object[50][5];
String field1[] = { "sno", "sname", "sex","sage", "sdept" };
String field2[] = { "sno", "cno", "cname","grade" };
String choice=new String();
Connection conn;
/*建立了到特定数据库的连接之后,就可用该连接发送 SQL 语句。
//Statement 对象用 Connection 的方法createStatement 创建*/
Statement st;
/*结果集(ResultSet)是数据中查询结果返回的一种对象,可以说结果集是一个存储查询结果的对象,
但是结果集并不仅仅具有存储的功能,他同时还具有操纵数据的功能,可能完成对数据的更新等。 */
ResultSet rs;
JTabbedPane tp;
JTabbedPane tp2;
//
public Operation() {
super("数据库操作");
this.setSize(700, 500);
this.setLocation(400, 300);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
JPanel ps = new JPanel();
add = new JButton("插入");
select = new JButton("查询");
update = new JButton("更新");
del = new JButton("删除");
sure=new JButton("确定");
add.addActionListener(this);
select.addActionListener(this);
update.addActionListener(this);
del.addActionListener(this);
sure.addActionListener(this);
ps.add(sure);
ps.add(tip);
ps.add(chose);
ps.add(add);
ps.add(select);
ps.add(update);
ps.add(del);
this.connection();
table1 = new JTable(body1, field1);
table2 =new JTable(body2, field2);
tp = new JTabbedPane();
tp2 = new JTabbedPane();
tp.add("student表", new JScrollPane(table1));
tp2.add("course表",new JScrollPane(table2));
sure.setBounds(300, 35, 60,20);
chose.setBounds(140, 35, 100,20);
tip.setBounds(30, 20, 100,40);
tp.setBounds(30, 100, 300,200);
tp2.setBounds(360, 100, 300,200);
add.setBounds(150, 350, 60,40);
select.setBounds(250,350, 60,40);
del.setBounds(350,350, 60,40);
update.setBounds(450,350, 60,40);
ps.setLayout(null);
this.getContentPane().add(tp);
this.getContentPane().add(tp2);
this.getContentPane().add(ps);
this.setVisible(true);
}
public void connection() {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName = Studentss","sa","ab781228");
st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
} catch (Exception ex) {
ex.printStackTrace();
System.out.println("连接错误:" + ex.getMessage());
}
}
public static void main(String[] args) {
Operation dt = new Operation();
}
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
if (e.getSource() == add) {
add();
}
if (e.getSource() == select) {
select();
}
if (e.getSource() == update) {
update();
}
if (e.getSource() == del) {
del();
}
if (e.getSource() == sure) {
sure();
}
}
private void del() {
try {
if(choice=="student")
{
int row = table1.getSelectedRow();
//点击要删除的行
String str = "delete student where sno = '" + body1[row][0] + "'";
st.executeUpdate(str);
JOptionPane.showMessageDialog(null, "数据已成功删除");
this.select();
}
else
{
int row = table2.getSelectedRow();
//点击要删除的行
String str = "delete course where sno = '" + body2[row][0] + "'";
st.executeUpdate(str);
JOptionPane.showMessageDialog(null, "数据已成功删除");
this.select();
}
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "删除数据错误!");
}
}
private void update() {
try {
if(choice=="student")
{
int row = table1.getSelectedRow(); //得到表格下标
//点击要修改的行
JTextField t[] = new JTextField[10];
t[0] = new JTextField("输入学号:");
t[0].setEditable(false);
t[1] = new JTextField();
t[1].setText((String) body1[row][0]);
t[2] = new JTextField("输入姓名:");
t[2].setEditable(false);
t[3] = new JTextField();
t[3].setText((String) body1[row][1]);
t[4] = new JTextField("输入性别:");
t[4].setEditable(false);
t[5] = new JTextField();
t[5].setText((String) body1[row][2]);
t[6] = new JTextField("输入年龄:");
t[6].setEditable(false);
t[7] = new JTextField();
t[7].setText((String) body1[row][3]);
t[8] = new JTextField("输入专业:");
t[8].setEditable(false);
t[9] = new JTextField();
t[9].setText((String) body1[row][4]);
String but[] = { "确定", "取消" };
int go = JOptionPane.showOptionDialog(null, t, "修改信息", JOptionPane.YES_OPTION,JOptionPane.INFORMATION_MESSAGE, null, but, but[0]);
if (go == 0) {
String nsno = (String) t[1].getText();
String nsname = (String) t[3].getText();
String nsex = (String) t[5].getText();
int nage = Integer.parseInt(t[7].getText());
String ndept = (String) t[9].getText();
String str = "update student set sno = '" + nsno + "',sname='" + nsname + "',sex='" + nsex + "',sage='"+nage +"',sdept='" + ndept+ "'"+ "where sno='" + body1[row][0] + "' ";
st.executeUpdate(str);
JOptionPane.showMessageDialog(null, "修改数据成功!");
this.select();//将修改后的表打印出来
}
}
else
{
int row = table2.getSelectedRow(); //得到表格下标
//点击要修改的行
JTextField t[] = new JTextField[10];
t[0] = new JTextField("输入学号:");
t[0].setEditable(false);
t[1] = new JTextField();
t[1].setText((String) body2[row][0]);
t[2] = new JTextField("输入课程号:");
t[2].setEditable(false);
t[3] = new JTextField();
t[3].setText((String) body2[row][1]);
t[4] = new JTextField("输入课程名:");
t[4].setEditable(false);
t[5] = new JTextField();
t[5].setText((String) body2[row][2]);
t[6] = new JTextField("输入成绩:");
t[6].setEditable(false);
t[7] = new JTextField();
t[7].setText((String) body2[row][3]);
String but[] = { "确定", "取消" };
int go = JOptionPane.showOptionDialog(null, t, "修改信息", JOptionPane.YES_OPTION,JOptionPane.INFORMATION_MESSAGE, null, but, but[0]);
if (go == 0)
{
String nsno = (String) t[1].getText();
String ncno = (String) t[3].getText();
String ncname = (String) t[5].getText();
int ngrade = Integer.parseInt(t[7].getText());
String str = "update course set sno = '" + nsno + "',cno='" + ncno + "',cname='" + ncname + "',grade='"+ngrade +"'"+ "where sno='" + body2[row][0] + "' ";
st.executeUpdate(str);
JOptionPane.showMessageDialog(null, "修改数据成功!");
this.select();//将修改后的表打印出来
}
}
} catch (Exception ex) {
// TODO: handle exception
ex.printStackTrace();
JOptionPane.showMessageDialog(null, "更新数据失败!");
}
}
private void select() {
if(choice=="student")
{
String str = "select * from student";
filltable1(str);
}
else {
String str = "select * from course";
filltable2(str);
}
}
private void filltable1(String str) {
// TODO Auto-generated method stub
try {
for(int x=0;x<body1.length;x++){
body1[x][0]=null;
body1[x][1]=null;
body1[x][2]=null;
body1[x][3]=null;
body1[x][4]=null;
}
int i = 0;
rs = st.executeQuery(str);
//ResultSet 指针最初位于第一行之前;第一次调用 next 方法使第一行成为当前行;
//第二次调用使第二行成为当前行,依此类推。 如果新的当前行有效,则返回 true;如果不存在下一行,则返回 false.
while(rs.next()){
body1[i][0]=rs.getString("sno");
body1[i][1]=rs.getString("sname");
body1[i][2]=rs.getString("sex");
body1[i][3]=rs.getString("sage");
body1[i][4]=rs.getString("sdept");
i=i+1;
}
this.repaint(i);
} catch (Exception ex) {
// TODO: handle exception
ex.printStackTrace();
}
}
private void filltable2(String str) {
// TODO Auto-generated method stub
try {
for(int x=0;x<body2.length;x++){
body2[x][0]=null;
body2[x][1]=null;
body2[x][2]=null;
body2[x][3]=null;
body2[x][4]=null;
}
int i = 0;
rs = st.executeQuery(str);
//ResultSet 指针最初位于第一行之前;第一次调用 next 方法使第一行成为当前行;
//第二次调用使第二行成为当前行,依此类推。 如果新的当前行有效,则返回 true;如果不存在下一行,则返回 false.
while(rs.next()){
body2[i][0]=rs.getString("sno");
body2[i][1]=rs.getString("cno");
body2[i][2]=rs.getString("cname");
body2[i][3]=rs.getString("grade");
i=i+1;
}
this.repaint(i);
} catch (Exception ex) {
// TODO: handle exception
ex.printStackTrace();
}
}
private void add() {
// TODO Auto-generated method stub
try {
if(choice=="student")
{
JTextField t[] = new JTextField[10];
t[0] = new JTextField("输入学号:");
t[0].setEditable(false);
t[1] = new JTextField();
t[2] = new JTextField("输入姓名:");
t[2].setEditable(false);
t[3] = new JTextField();
t[4] = new JTextField("输入性别:");
t[4].setEditable(false);
t[5] = new JTextField();
t[6] = new JTextField("输入年龄:");
t[6].setEditable(false);
t[7] = new JTextField();
t[8] = new JTextField("输入专业:");
t[8].setEditable(false);
t[9] = new JTextField();
String but[] = {"确定","取消"};
int go = JOptionPane.showOptionDialog(null, t, "插入信息", JOptionPane.YES_OPTION, JOptionPane.INFORMATION_MESSAGE, null, but, but[0]);
if(go == 0){
try {
String nsno = (String) t[1].getText();
String nsname = (String) t[3].getText();
String nsex = (String) t[5].getText();
int nage = Integer.parseInt(t[7].getText());
String ndept = (String) t[9].getText();
String str = "insert into student values('"+nsno+"','"+nsname+"','"+nsex+"','"+nage+"','"+ndept+"')";
st.executeUpdate(str);
JOptionPane.showMessageDialog(null, "数据已经成功插入!");
this.select();
} catch (Exception ex) {
// TODO: handle exception
JOptionPane.showMessageDialog(null, "数据插入失败!");
}
}
}
else {
JTextField t[] = new JTextField[10];
t[0] = new JTextField("输入学号:");
t[0].setEditable(false);
t[1] = new JTextField();
t[2] = new JTextField("输入课程号:");
t[2].setEditable(false);
t[3] = new JTextField();
t[4] = new JTextField("输入课程名:");
t[4].setEditable(false);
t[5] = new JTextField();
t[6] = new JTextField("输入成绩:");
t[6].setEditable(false);
t[7] = new JTextField();
String but[] = { "确定", "取消" };
int go = JOptionPane.showOptionDialog(null, t, "修改信息", JOptionPane.YES_OPTION,JOptionPane.INFORMATION_MESSAGE, null, but, but[0]);
if (go == 0)
try{
String nsno = (String) t[1].getText();
String ncno = (String) t[3].getText();
String ncname = (String) t[5].getText();
int ngrade = Integer.parseInt(t[7].getText());
String str = "insert into course values('"+nsno+"','"+ncno+"','"+ncname+"','"+ngrade+"')";
st.executeUpdate(str);
JOptionPane.showMessageDialog(null, "插入数据成功!");
this.select();//将修改后的表打印出来
}catch (Exception ex) {
// TODO: handle exception
JOptionPane.showMessageDialog(null, "数据插入失败!");
}
}
}
catch (Exception ex) {
// TODO: handle exception
}
}
private void sure()
{
choice=chose.getText();
}
}