数据库上机作业3实现

1.连接与断开

    Connection conn;
    public void lianjie()
    {
         try
        {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn=DriverManager.getConnection("jdbc:sqlserver://47.93.184.169:1433;DatabaseName=LZY_SPJ_LAB3","sa","xxxxxxxxxxx");
            System.out.println("连接数据库成功");
            
        }
        catch(ClassNotFoundException | SQLException e)
        {
            e.printStackTrace();
            System.out.print("连接失败");
        }
    }
    public void guanbi() throws SQLException
    {
        conn.close();
        System.out.println("！！数据库断开！！");
    }

2.数据浏览

try{
            String text ="SNO\tSNAME\tSTATUS\tCITY\n";
            lianjie();
            Statement stat=conn.createStatement();
      
            String sqlspj="select * from SPJ";
            String sqls="select * from S";
            
            System.out.println("执行："+sqls);
            ResultSet rs=stat.executeQuery(sqls);
             while (rs.next()) {
                 text+=rs.getString(1).trim()+'\t'+rs.getString(2).trim()+'\t'+rs.getString(3).trim()+'\t'+rs.getString(4).trim()+'\t'+"\n";
            }
            text +="\nSNO\tPNO\tJNO\tQTY\n";
            rs=stat.executeQuery(sqlspj);
             while (rs.next()) {
                text+=rs.getString(1).trim()+'\t'+rs.getString(2).trim()+'\t'+rs.getString(3).trim()+'\t'+rs.getString(4).trim()+'\t'+"\n";
            }
            
            
            dakuang.setText(text);
            guanbi();
            
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("未执行");
            try {
                guanbi();
            } catch (SQLException ex) {
                Logger.getLogger(Database_3.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

3.信息插入

try {
            lianjie();
            // TODO add your handling code here:
            Statement stat=conn.createStatement();
            String sql="insert into S(SNO,SNAME,STATUS,CITY) values('"+t2.getText()+"','"+t3.getText()+"','"+t4.getText()+"','"+t5.getText()+"')";
            System.out.println(sql);
            stat.executeUpdate(sql);
            stat.close();
            Object[] options ={ "OK" };  //自定义按钮上的文字
            JOptionPane.showOptionDialog(null,"操作成功！", "success",JOptionPane.YES_OPTION, JOptionPane.PLAIN_MESSAGE, null, options, options[0]); 
            guanbi();
        } catch (SQLException ex) {
            try {
                guanbi();
            } catch (SQLException ex1) {
                Logger.getLogger(Database_3.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(Database_3.class.getName()).log(Level.SEVERE, null, ex);
            Object[] options ={ "OK" };  //自定义按钮上的文字
            JOptionPane.showOptionDialog(null,"操作失败！\nSNO重复或STATUS错误！", "error",JOptionPane.YES_OPTION, JOptionPane.ERROR_MESSAGE, null, options, options[0]); 
        }

4.删除

try{
            lianjie();
            Statement stat=conn.createStatement();
            String sqlspj="delete from SPJ where SNO='"+t8.getText()+"'";
            String sqls="delete from S where SNO='"+t8.getText()+"'";
            System.out.println("执行："+sqlspj);
            stat.executeUpdate(sqlspj);
            System.out.println("执行："+sqls);
            stat.executeUpdate(sqls);
            guanbi();
            Object[] options ={ "OK" };  //自定义按钮上的文字
            JOptionPane.showOptionDialog(null,"删除成功！", "success",JOptionPane.YES_OPTION, JOptionPane.PLAIN_MESSAGE, null, options, options[0]); 
        }catch(Exception e){
            try {
                guanbi();
            } catch (SQLException ex) {
                Logger.getLogger(Database_3.class.getName()).log(Level.SEVERE, null, ex);
            }
            e.printStackTrace();
            System.out.println("未执行");
            Object[] options ={ "OK" };  //自定义按钮上的文字
            JOptionPane.showOptionDialog(null,"操作失败！\n请检查SNO是否存在！", "error",JOptionPane.YES_OPTION, JOptionPane.ERROR_MESSAGE, null, options, options[0]); 
        }

5.修改

try{
            
            lianjie();
            Statement stat=conn.createStatement();
      
            String sqlspj="select * from SPJ where SNO='"+t13.getText()+"'";
            System.out.println("执行："+sqlspj);
            ResultSet rs=stat.executeQuery(sqlspj);
            while (rs.next())
            {
                int x=Integer.parseInt(rs.getString(4).trim());
                String xx=(int)(1.1*x)+"";
                Statement stat2=conn.createStatement();
                String sql = "update SPJ set QTY='"+xx+"' where SNO='"+rs.getString(1).trim()+"' and PNO='"+rs.getString(2).trim()+"' and JNO='"+rs.getString(3).trim()+"'";
                System.out.println(sql);
                stat2.executeUpdate(sql);
                
            }            
            Object[] options ={ "OK" };  //自定义按钮上的文字
            JOptionPane.showOptionDialog(null,"操作成功！", "success",JOptionPane.YES_OPTION, JOptionPane.PLAIN_MESSAGE, null, options, options[0]); 
            guanbi();
            
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("未执行");
            try {
                guanbi();
            } catch (SQLException ex) {
                Logger.getLogger(Database_3.class.getName()).log(Level.SEVERE, null, ex);
            }
        }