java homework1

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);
        }
    }
}

package pac;

import javax.swing.*;
import java.sql.*;

public class mysqlconnect extends JFrame{
    String  col[]={"stuid","name","city"};
    JFrame jf =null;
    JTable jt=null;
    JScrollPane jsp=null;
    JButton jb3=null;
//    Object data[][];

    public static void main(String[] args)
            throws ClassNotFoundException, SQLException {
        wind tt = new wind();
    }
}