package pac;
//import jdk.incubator.foreign.Addressable;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import java.util.Scanner;
// user 记录登录信息的；
//dormitory_grade 门牌号 , grade
//stu 记录学生信息 stuid name 门牌号
//dormitory_info 门牌号 ， 人数
class wind extends JFrame implements ActionListener {
JFrame jf=null,jf2=null,jf3=null,jadd,jquery,jdelete,jupdate;
JLabel jl=null,jl2=null,jl3=null;
JPanel jp=null,jp2=null,jp3=null,jpadd,jpquery,jpdelete,jpupdate;
JTable table;
DefaultTableModel model=null;//是一个表格模型
JScrollPane jsp=null;
JPanel jpq=null;
Scanner cin=new Scanner(System.in);
JTextArea jt2=null,jtquery,jtdelete,jtupdate,aa,bb;
JTextArea jta[]=new JTextArea[3],jtaa[]=new JTextArea[3];
JLabel success=null,unsuccess=null;
JPasswordField jt3=null;
JButton jb2,jb,jb3,jb4,jb5,jb6,jb7,jb8,jbadd,jbupdate,jbdelete,jbquery,jbupdate2;//增删改查
Connection connection;
public boolean find(String a,String b) throws SQLException {
//访问本机的mysql数据库,格式 jdbc:数据库://本地端口3306/数据库名?跨时区参数
Statement stat = connection.createStatement();
String sql="select * from user";
ResultSet rs=stat.executeQuery(sql);
while(rs.next()){
// System.out.println(rs.getString(1)+" "+rs.getString(2));
String c=rs.getString(1);
String d=rs.getString(2);
if(c.equals(a) && d.equals(b)) return true;
}
return true;
// return false;
}
public void updatedorm(int dormid,int ct)
{
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="update dormitory_info SET cnt=cnt+1 where dormid="+dormid+";";
if(ct==-1) sql="update dormitory_info SET cnt=cnt-1 where dormid="+dormid+";";
System.out.println(sql);
try {
stat.executeUpdate(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public void updategrade(int dormid,int grade)
{
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="update dormitory_grade SET grade="+grade+" where dormid="+dormid+";";
System.out.println(sql);
try {
stat.executeUpdate(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public void AddStu(int stuid,String name,int dormid) throws SQLException {
Statement stat = connection.createStatement();
String sql="insert into stu values ("+stuid+",'"+name+"',"+dormid+")";
System.out.println(sql);
stat.executeUpdate(sql);
}
public boolean DeleteStu(int stuid,String name,int dormid) throws SQLException {
Statement stat = connection.createStatement();
String sql="delete from stu where stuid = "+stuid+" and name = '" +name+ "' and dormid = "+dormid + " ;";
System.out.println(sql);
stat.executeUpdate(sql);
return true;
}
public int querydorm(int stuid)
{
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="select dormid from stu where stuid = "+stuid + " ;";
System.out.println(sql);
ResultSet rs=null;
try {
rs= stat.executeQuery(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String res="";
while(true)
{
try {
if (!rs.next()) break;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
res=rs.getString(1);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
System.out.println(res);
int ans=0;
for(int i=0;i<res.length();i++)
ans=ans*10+res.charAt(i)-'0';
return ans;
}
public void updateStu(String a)
{
}
public wind() throws SQLException {
super();
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai";
String username = "root";
String password = "root";
//获取到数据库链接
connection = DriverManager.getConnection(url, username, password);
jf2=new JFrame(); jadd=new JFrame(); jupdate=new JFrame();jdelete=new JFrame();jquery=new JFrame();
jp2=new JPanel(null);
jt2=new JTextArea("");//id
jp3=new JPanel(null);
jf3=new JFrame();
jbadd=new JButton("ADD!"); jbquery=new JButton("QR!");
JButton jbquery2=new JButton("QRD!");
JButton jbquery3=new JButton("QRDD!");
jbdelete=new JButton("DEL!");jbupdate=new JButton("UPD!");
jb4=new JButton("ADD");
jb5=new JButton("DELETE");jb6=new JButton("UPDATE");
jb7=new JButton("QUERY");
jb8=new JButton("RETURN2");
aa=new JTextArea();
bb=new JTextArea();
jbupdate2=new JButton("UPDD!");
// for(int i=100;i<=200;i++)
// {
// String sql="insert into dormitory_grade values ("+i+","+0+")";
// Statement stat = connection.createStatement();
// stat.executeUpdate(sql);
// sql="insert into dormitory_info values ("+i+","+0+")";
// stat = connection.createStatement();
// stat.executeUpdate(sql);
// }
jf3.add(jp3);jp3.add(jb4);jp3.add(jb5);jp3.add(jb6);jp3.add(jb7);jp3.add(jb8);
jtdelete=new JTextArea();
jtupdate=new JTextArea();
jtquery=new JTextArea();
jpadd=new JPanel(null);
jpquery=new JPanel(null);
jpupdate=new JPanel(null);
jpdelete=new JPanel(null);
for(int i=0;i<3;i++)
{
jta[i]=new JTextArea();
jtaa[i]=new JTextArea();
jpadd.add(jta[i]);
jpdelete.add(jtaa[i]);
jta[i].setBounds(100,20+30*i,100,25);
jtaa[i].setBounds(100,20+30*i,100,25);
}
JLabel t1=new JLabel("stuid: ");
JLabel t2=new JLabel("name:");
JLabel t3=new JLabel("dormid:");
JLabel t4=new JLabel("stuid: ");
JLabel t5=new JLabel("name:");
JLabel t6=new JLabel("dormid:");
aa.setBounds(110,70,100,20);
bb.setBounds(110,100,100,20);
jpq=new JPanel(null);
JLabel xx=new JLabel("first:");
JLabel yy=new JLabel("second:");
xx.setBounds(30,70,50,20);
yy.setBounds(30,100,50,20);
jpupdate.add(xx);
jpupdate.add(yy);
jpupdate.add(aa);
jpupdate.add(bb);
jpdelete.add(t4);
jpdelete.add(t5);
jpdelete.add(t6);
jpadd.add(t1);
jpadd.add(t2);
jpadd.add(t3);
t1.setBounds(60,20,40,20);
t2.setBounds(60,50,40,20);
t3.setBounds(55,80,50,20);
t4.setBounds(60,20,40,20);
t5.setBounds(60,50,40,20);
t6.setBounds(55,80,50,20);
jpdelete.add(jtdelete);jpupdate.add(jtupdate);jpquery.add(jtquery);
jadd.add(jpadd); jdelete.add(jpdelete);jupdate.add(jpupdate);jquery.add(jpquery);
jbadd.setBounds(100,150,100,100);
jbquery.setBounds(60,80,80,80);
jbquery2.setBounds(200,80,80,80);
jbquery3.setBounds(130,160,80,80);
jbupdate.setBounds(120,150,50,50);
jbupdate2.setBounds(50,150,50,50);
jbdelete.setBounds(100,150,100,100);
jadd.setBounds(200,200,300,300);
jupdate.setBounds(200,200,300,300);
jdelete.setBounds(200,200,300,300);
jquery.setBounds(200,200,300,300);
jpquery.add(jbquery);
jpquery.add(jbquery2);
jpquery.add(jbquery3);
jpupdate.add(jbupdate);
jpupdate.add(jbupdate2);
jpdelete.add(jbdelete);
jpadd.add(jbadd);
jbquery.addActionListener(this);
jbquery2.addActionListener(this);
jbquery3.addActionListener(this);
jf3.setBounds(200,200,300,300);
jf3.add(jp3);
jb4.setBounds(100,20,100,50);
jb5.setBounds(100,80,100,50);
jb6.setBounds(100,140,100,50);
jb7.setBounds(100,200,100,50);
jb8.setBounds(0,100,80,80);
Font a=new Font("宋体",Font.PLAIN,18);
jb8.setFont(new Font("宋体",Font.PLAIN,11));
success = new JLabel("登录成功！");
unsuccess=new JLabel("验证失败，请重试！");
unsuccess.setFont(new Font("宋体",Font.BOLD,18));
unsuccess.setForeground(Color.red);
jbdelete.addActionListener(this);
jbupdate.addActionListener(this);
jbupdate2.addActionListener(this);
jbquery.addActionListener(this);
jbadd.addActionListener(this);
jb8.addActionListener(this);
jb5.addActionListener(this);
jb6.addActionListener(this);
jb7.addActionListener(this);
jb4.addActionListener(this);
jt3=new JPasswordField("");//密码
jf2.setBounds(200,200,350,400);
jl2=new JLabel("用户名:");
jl3=new JLabel("密码：");
jb3=new JButton("RETURN");
jp2.add(jl2);
jp2.add(jl3);
jp2.add(jb3); jp2.add(jb3);
jl2.setBounds(50,50,100,50);
jl3.setBounds(50,150,100,50);
jb2=new JButton("CHECK");
jp2.add(jt2);
jp2.add(jt3);
jb2.setBounds(100,250,100,100);
jt2.setBounds(100,70,100,25);
jt3.setBounds(100,170,100,25);
jb3.setBounds(250,100,80,80);
jf2.add(jp2);
this.setSize(600,600);
this.setLocation(200,200);
jp=new JPanel(null);
jl=new JLabel();
jl.setLocation(-100,10);
ImageIcon im=new ImageIcon("C:\\Users\\MYQ\\Desktop\\yier.jpg");
jl.setIcon(im);
jl.setBounds(0,0,300,300);
jp.add(jl);
jb=new JButton("登陆");
jb.addActionListener(this);
jb3.addActionListener(this);
jb2.addActionListener(this);
jp2.add(jb2);
JTextArea ja=new JTextArea("WELCOME TO 宿舍管理系统");
ja.setBounds(0,450,500,150);
ja.setFont(new Font("宋体",Font.BOLD,35));
ja.setSelectedTextColor(Color.yellow);
jp.add(ja);
jp.add(jb);
jb.setBounds(100,325 ,100,100);
jb.setFont(new Font("宋体",Font.PLAIN,30));
this.setContentPane(jp);
this.setDefaultCloseOperation(EXIT_ON_CLOSE);
this.setVisible(true);
}
@Override
public void actionPerformed(ActionEvent e) {
System.out.println(e.getActionCommand());
if(e.getActionCommand()=="登陆")
{
this.setVisible(false);
jf2.setVisible(true);
}
else if(e.getActionCommand()=="RETURN"){
this.setVisible(true);
jf2.setVisible(false);
}
else if(e.getActionCommand()=="CHECK")
{
String a=jt2.getText();
char[] b= jt3.getPassword();
System.out.println();
String c=String.valueOf(b);
try {
if(find(a,c)) {
System.out.println("OK");
jf2.setVisible(false);
jf3.setVisible(true);
}
else
{
System.out.println("NULL");
jp2.add(unsuccess);
unsuccess.setBounds(220,250,120,120);
jf2.setVisible(true);
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
else if(e.getActionCommand()=="ADD"){
this.setVisible(false);
jadd.setVisible(true);
}
else if(e.getActionCommand()=="QUERY")
{
this.setVisible(false);
jquery.setVisible(true);
}
else if(e.getActionCommand()=="UPDATE"){
this.setVisible(false);
jupdate.setVisible(true);
}
else if(e.getActionCommand()=="DELETE")
{
this.setVisible(false);
jdelete.setVisible(true);
}
else if(e.getActionCommand()=="RETURN2")
{
jf3.setVisible(false);
jf2.setVisible(true);
}
else if(e.getActionCommand()=="ADD!")//加入学生
{
String tt[]=new String[3];
for(int i=0;i<3;i++)
{
tt[i]=jta[i].getText();
}
int num=0;
for(int i=0;i<tt[0].length();i++)
{
num=num*10+(int)(tt[0].charAt(i)-'0');
}
int num2=0;
for(int i=0;i<tt[2].length();i++)
{
num2=num2*10+(int)(tt[2].charAt(i)-'0');
}
try {
AddStu(num,tt[1],num2);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
updatedorm(num2,+1);
}
else if(e.getActionCommand()=="UPD!")//更新学生
{
int stuid=0;
int dormid=0;
String L,R;
L=aa.getText();
R=bb.getText();
for(int i=0;i<L.length();i++)
stuid=stuid*10+L.charAt(i)-'0';
for(int i=0;i<L.length();i++)
dormid=dormid*10+R.charAt(i)-'0';
int last=querydorm(stuid);
updatedorm(last,-1);
updatedorm(dormid,+1);
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="update stu SET dormid = "+dormid + " where stuid = "+stuid + " ;";
System.out.println(sql);
try {
stat.executeUpdate(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
else if(e.getActionCommand()=="UPDD!")//更新宿舍成绩
{
int dormid=0;
int grade=0;
String L,R;
L=aa.getText();
R=bb.getText();
for(int i=0;i<L.length();i++)
dormid=dormid*10+L.charAt(i)-'0';
for(int i=0;i<L.length();i++)
grade=grade*10+R.charAt(i)-'0';
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="update dormitory_grade SET grade = "+grade + " where dormid = "+dormid + " ;";
System.out.println(sql);
try {
stat.executeUpdate(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
else if(e.getActionCommand()=="DEL!")
{
String tt[]=new String[3];
for(int i=0;i<3;i++)
{
tt[i]=jtaa[i].getText();
}
int num=0;
for(int i=0;i<tt[0].length();i++)
{
num=num*10+(int)(tt[0].charAt(i)-'0');
}
int num2=0;
for(int i=0;i<tt[2].length();i++)
{
num2=num2*10+(int)(tt[2].charAt(i)-'0');
}
try {
DeleteStu(num,tt[1],num2);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
updatedorm(num2,-1);
}
else if(e.getActionCommand()=="QR!")
{
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="select * from stu ;";
ResultSet rs=null;
try {
rs=stat.executeQuery(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
Object[][] obj = new Object[20][3];
int cnt=0;
while(true)
{
try {
if (!rs.next()) break;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
obj[cnt][0]=rs.getInt(1);
System.out.println(obj[cnt][0]);
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
try {
obj[cnt][1]=rs.getString(2);
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
try {
obj[cnt][2]=rs.getInt(3);
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
cnt++;
}
System.out.println(cnt);
Object colnames[]={"stuid","name","dormid"};
JTable jt=new JTable(obj,colnames);
JFrame jff=new JFrame();
jff.setBounds(100,100,500,500);
jff.add(new JScrollPane(jt));
jff.setVisible(true);
}
else if(e.getActionCommand()=="QRD!")
{
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="select * from dormitory_info ;";
ResultSet rs=null;
try {
rs=stat.executeQuery(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
Object[][] obj = new Object[210][2];
int cnt=0;
while(true)
{
try {
if (!rs.next()) break;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
obj[cnt][0]=rs.getInt(1);
System.out.println(obj[cnt][0]);
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
try {
obj[cnt][1]=rs.getInt(2);
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
cnt++;
}
Object colnames[]={"dormid","cnt"};
JTable jt=new JTable(obj,colnames);
JFrame jff=new JFrame();
jff.setBounds(100,100,500,500);
jff.add(new JScrollPane(jt));
jff.setVisible(true);
}
else if(e.getActionCommand()=="QRDD!")
{
Statement stat = null;
try {
stat = connection.createStatement();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="select * from dormitory_grade ;";
ResultSet rs=null;
try {
rs=stat.executeQuery(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
Object[][] obj = new Object[210][2];
int cnt=0;
while(true)
{
try {
if (!rs.next()) break;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
obj[cnt][0]=rs.getInt(1);
System.out.println(obj[cnt][0]);
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
try {
obj[cnt][1]=rs.getInt(2);
} catch (SQLException sqlException) {
sqlException.printStackTrace();
}
cnt++;
}
System.out.println(cnt);
Object colnames[]={"dormid","grade"};
JTable jt=new JTable(obj,colnames);
JFrame jff=new JFrame();
jff.setBounds(100,100,500,500);
jff.add(new JScrollPane(jt));
jff.setVisible(true);
}
}
}
