黑马程序员 java基础 连接数据库学习日志

学习笔记 专栏收录该内容
12 篇文章 0 订阅

黑马程序员   java基础  连接数据库学习日志

JDBC访问数据库示例


例12-1:实现对仓库货物的添加、查询等操作。


本章的例子实现对仓库货物的添加、查询等操作,使用SQL server2000数据库。数据库名为dbGoods,用户名为:sa,密码为空,货物表名为Goods。Goods表的结构如表12-1所示。涉及的字段包括货物编号、货物名称、货物数量三个字段。

列名称
 数据类型
 描述
 
GoodsNO
 Varchar
 货物编号,主键
 
GoodsName
 Varchar
 货物名称
 
GoodsQuantity
 Numeric
 货物数量
 


向表中添加数据:

GoodsNO
 GoodsName
 GoodsQuantity
 
A001
 watch
 10
 
A002
 book
 20
 
A003
 knife
 15
 


 


本例使用SQL Server2000的JDBC驱动程序访问数据库,所以首先从网上下载msbase.jar、mssqlserver.jar、msutil.jar三个jar文件,
将这三个jar文件加入到环境变量中。也可以将以上三个文件拷贝至jvm机所在的JAVA_HOME\jre\lib\ext目录下,不过不建议这样操作。

源程序如下:

import java.awt.*;

import java.awt.event.*;

import javax.swing.*;

import javax.swing.table.*;

import java.util.*;

import java.sql.*;

 

public class GoodsManager

{

    public static void main(String[] args)

    {

           new GoodsFrame();

    }

}

/******************主窗口*************************/

class GoodsFrame extends JFrame implements ActionListener  

{

    JMenuBar menuManagerBar;

    JMenu menuManager;

    JMenuItem menuSelect,menuAdd,menuDelete,menuModify,menuExit;

   

    String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

    String dbURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbGoods";

    String userName = "sa";

    String userPwd = "";

    Connection dbConn;

    Statement stmt;

 

    GoodsFrame()

    {

           createMenu();

           connetionDB();

           setTitle("货物管理");

           getContentPane().setLayout(null);

           setLocation(200,200);

           setSize(400,300);

           setVisible(true);

           setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

    }

   

    void createMenu()

    {

           menuManagerBar=new JMenuBar();

           menuManager=new JMenu("货物管理");

           menuSelect=new JMenuItem("查询货物");

           menuAdd=new JMenuItem("添加货物");

           menuDelete=new JMenuItem("删除货物");

           menuModify=new JMenuItem("修改货物信息");

           menuExit=new JMenuItem("退出");

          

           menuManager.add(menuSelect);

           menuManager.add(menuAdd);

           menuManager.add(menuDelete);

           menuManager.add(menuModify);

           menuManager.addSeparator();

           menuManager.add(menuExit);

           menuManagerBar.add(menuManager);

           setJMenuBar(menuManagerBar);

          

           menuSelect.addActionListener(this);

           menuAdd.addActionListener(this);

           menuDelete.addActionListener(this);

           menuModify.addActionListener(this);

           menuExit.addActionListener(this);

    }

 

    public void connetionDB()

    {

        try

{

            Class.forName(driverName);       //装载驱动程序

            //连接数据库

dbConn = DriverManager.getConnection(dbURL, userName, userPwd);

            stmt=dbConn.createStatement();   //创建Statement对象

 

        }

catch (Exception e)

{

            e.printStackTrace();

        }

    }

   

    public void actionPerformed(ActionEvent e)

    {

           if(e.getSource()==menuSelect)

           {

                  SelectGoods selectFrame=new SelectGoods(stmt);

                  selectFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuAdd)

           {

                  AddGoods addFrame=new AddGoods(stmt);

                  addFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuDelete)

           {

                  DeleteGoods deleteFrame=new DeleteGoods(stmt);

                  deleteFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuModify)

           {

                  ModifyGoods modifyFrame=new ModifyGoods(stmt);

                  modifyFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuExit)

           {

                  System.exit(0);

           }

    }

}

/******************选择窗口*************************/

class SelectGoods extends JFrame implements ActionListener

{

       Statement selectStmt;

       ResultSet rs;

      

       SelectGoods(Statement stmt)

       {

              DefaultTableModel tableModel = new DefaultTableModel();             

              String[] tableHeads = {"货物编号", "货物名称", "数量"};

    Vector cell ;

    Vector row = new Vector();

    Vector tableHeadName = new Vector();

   

    selectStmt=stmt;

   

    for (int i = 0; i < tableHeads.length; i++)

    {

      tableHeadName.add(tableHeads[i]);

    }

   

    try

    {

      rs=selectStmt.executeQuery("select * from Goods");//查询语句

      while(rs.next())//操作结果集

      {

          cell=new Vector();

          cell.add(rs.getString("GoodsNO"));

          cell.add(rs.getString("GoodsName"));

          cell.add(rs.getInt("GoodsQuantity"));

          row.add(cell);

      }

    }   

    catch (Exception e)

    {

      e.printStackTrace();

    }

   

    tableModel.setDataVector(row, tableHeadName);

    JTable table = new JTable(tableModel);

    table.setRowHeight(20);

    table.setCursor(new Cursor(12));

    getContentPane().setLayout(null);

    JScrollPane scrollPane = new JScrollPane(table);

    scrollPane.setBounds(10,10,380,250);

    scrollPane.setCursor(new Cursor(12));

    this.getContentPane().add(scrollPane);

   

              setTitle("查询货物");

    getContentPane().setLayout(null);

    setLocation(220,220);

    setSize(400,300);

       }

      

         public void actionPerformed(ActionEvent e)

    {

           dispose();

    }

}

/******************添加窗口*************************/

class AddGoods extends JFrame implements ActionListener

{

       Statement addStmt;

      

       JLabel goodsNOLabel;

       JLabel goodsNameLabel;

       JLabel goodsQuantityLabel;

       JTextField goodsNOTextField;

       JTextField goodsNameTextField;

       JTextField goodsQuantityTextField;

       JButton submitButton;

       JButton resetButton;

      

       AddGoods(Statement stmt)

       {

              addStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(110,30,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(190,30,100,20);

              this.getContentPane().add(goodsNOTextField);

              goodsNameLabel=new JLabel("货物名称:");

              goodsNameLabel.setBounds(110,70,80,20);

              this.getContentPane().add(goodsNameLabel);

              goodsNameTextField=new JTextField();

              goodsNameTextField.setBounds(190,70,100,20);

              this.getContentPane().add(goodsNameTextField);

              goodsQuantityLabel=new JLabel("货物数量:");

              goodsQuantityLabel.setBounds(110,110,80,20);

              this.getContentPane().add(goodsQuantityLabel);

              goodsQuantityTextField=new JTextField();

              goodsQuantityTextField.setBounds(190,110,100,20);

              this.getContentPane().add(goodsQuantityTextField);

             

              submitButton=new JButton("提交");

              submitButton.setBounds(110,180,60,30);

              this.getContentPane().add(submitButton);

              resetButton=new JButton("重置");

              resetButton.setBounds(230,180,60,30);

              this.getContentPane().add(resetButton);

             

              submitButton.addActionListener(this);

              resetButton.addActionListener(this);

             

              setTitle("添加货物");

    getContentPane().setLayout(null);

    setLocation(240,240);

    setSize(400,300);

 

       }

      

       public void actionPerformed(ActionEvent ae)

  {

      String strGoodsNO,strGoodsName;

      int intGoodsQuantity;

      strGoodsNO=goodsNOTextField.getText();

      strGoodsName=goodsNameTextField.getText();

      intGoodsQuantity=Integer.parseInt(goodsQuantityTextField.getText());

     

      if(ae.getSource()==submitButton)

    {

           try

           {

          //插入语句

          addStmt.executeUpdate("insert into goods values('"

+strGoodsNO+"','"+strGoodsName+"',"+intGoodsQuantity+")");

           }

           catch (Exception e)

           {

            e.printStackTrace();

           }        

    }

    else

    if(ae.getSource()==resetButton)

    {

           goodsNOTextField.setText("");

        goodsNameTextField.setText("");

        goodsQuantityTextField.setText("");

    }

  }

}

/******************删除窗口*************************/

class DeleteGoods extends JFrame implements ActionListener

{

       Statement deleteStmt;

      

       JLabel goodsNOLabel;

       JTextField goodsNOTextField;

       JButton deleteAllButton;

       JButton deleteOneButton;

      

       DeleteGoods(Statement stmt)

       {

              deleteStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(60,40,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(140,40,100,20);

              this.getContentPane().add(goodsNOTextField);

             

              deleteOneButton=new JButton("删除");

              deleteOneButton.setBounds(50,90,90,30);

              this.getContentPane().add(deleteOneButton);

              deleteAllButton=new JButton("全部删除");

              deleteAllButton.setBounds(150,90,90,30);

              this.getContentPane().add(deleteAllButton);

             

              deleteAllButton.addActionListener(this);

              deleteOneButton.addActionListener(this);

             

              setTitle("删除货物");

    getContentPane().setLayout(null);

    setLocation(260,260);

    setSize(300,200);

       }

      

       public void actionPerformed(ActionEvent ae)

  {

    String strGoodsNO;

    String message="确定删除"+goodsNOTextField.getText()+"的信息?";

      strGoodsNO=goodsNOTextField.getText();

     

      if(ae.getSource()==deleteOneButton)

    {

           int resOne=JOptionPane.showConfirmDialog(this,message,"删除信息",

JOptionPane.OK_CANCEL_OPTION);

      if(resOne==JOptionPane.OK_OPTION)

      {

        try

             {

                 //删除语句

               deleteStmt.executeUpdate("

delete from goods where GoodsNO='"+strGoodsNO+"'");

             }

             catch (Exception e)

             {

              e.printStackTrace();

             }

      }

    }

    else

    if(ae.getSource()==deleteAllButton)

    {

        int resAll=JOptionPane.showConfirmDialog(this,"是否全部删除?","删除信息",

JOptionPane.OK_CANCEL_OPTION);

        if(resAll==JOptionPane.OK_OPTION)

        {

          try

               {

                      //删除语句

              deleteStmt.executeUpdate("delete from goods");

               }

               catch (Exception e)

               {

                e.printStackTrace();

               }

       }                    

    }

  }

}

/******************修改窗口*************************/

class ModifyGoods extends JFrame implements ActionListener

{

       Statement modifyStmt;

       ResultSet rs;

      

       JLabel goodsNOLabel;

       JLabel goodsNameLabel;

       JLabel goodsQuantityLabel;

       JTextField goodsNOTextField;

       JTextField goodsNameTextField;

       JTextField goodsQuantityTextField;

       JButton selectButton;

       JButton modifyButton;

      

       ModifyGoods(Statement stmt)

       {

              modifyStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(110,30,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(190,30,100,20);

              this.getContentPane().add(goodsNOTextField);

              goodsNameLabel=new JLabel("货物名称:");

              goodsNameLabel.setBounds(110,70,80,20);

              this.getContentPane().add(goodsNameLabel);

              goodsNameTextField=new JTextField();

              goodsNameTextField.setBounds(190,70,100,20);

              this.getContentPane().add(goodsNameTextField);

              goodsQuantityLabel=new JLabel("货物数量:");

              goodsQuantityLabel.setBounds(110,110,80,20);

              this.getContentPane().add(goodsQuantityLabel);

              goodsQuantityTextField=new JTextField();

              goodsQuantityTextField.setBounds(190,110,100,20);

              this.getContentPane().add(goodsQuantityTextField);

             

              selectButton=new JButton("查询");

              selectButton.setBounds(110,180,60,30);

              this.getContentPane().add(selectButton);

              modifyButton=new JButton("修改");

              modifyButton.setBounds(230,180,60,30);

              this.getContentPane().add(modifyButton);

             

              selectButton.addActionListener(this);

              modifyButton.addActionListener(this);

             

              setTitle("修改货物信息");

    getContentPane().setLayout(null);

    setLocation(280,280);

    setSize(400,300);

       }

      

       public void actionPerformed(ActionEvent ae)

  {

    String strGoodsNO,strGoodsName;

      int intGoodsQuantity;

      strGoodsNO=goodsNOTextField.getText();

      if(ae.getSource()==selectButton)

    {

           try

      {

          //查询语句

        rs=modifyStmt.executeQuery("

select * from Goods where GoodsNO='"+strGoodsNO+"'");

        while(rs.next())//操作结果集

        {

            goodsNOTextField.setText(rs.getString("GoodsNO"));

            goodsNameTextField.setText(rs.getString("GoodsName"));

            goodsQuantityTextField.setText(rs.getString("GoodsQuantity"));

        }

      }

      catch (Exception e)

      {

        e.printStackTrace();

      }      

    }

    else

    if(ae.getSource()==modifyButton)

    {

           strGoodsNO=goodsNOTextField.getText();

        strGoodsName=goodsNameTextField.getText();

        intGoodsQuantity=Integer.parseInt(goodsQuantityTextField.getText());

        String message="确定修改"+strGoodsNO+"的信息?";

           int resOne=JOptionPane.showConfirmDialog(this,message,"修改信息",

JOptionPane.OK_CANCEL_OPTION);

          

      if(resOne==JOptionPane.OK_OPTION)

      {

        try

             {

                 //修改语句

            modifyStmt.executeUpdate("update  goods set GoodsName='"

+strGoodsName+"',GoodsQuantity='"+intGoodsQuantity+"'where GoodsNO='"+strGoodsNO+"'");

             }

             catch (Exception e)

             {

              e.printStackTrace();

             }

      }

    }

  }

}
 

 

JDBC访问数据库示例


例12-1:实现对仓库货物的添加、查询等操作。


本章的例子实现对仓库货物的添加、查询等操作,使用SQL server2000数据库。数据库名为dbGoods,用户名为:sa,密码为空,货物表名为Goods。Goods表的结构如表12-1所示。涉及的字段包括货物编号、货物名称、货物数量三个字段。

列名称
 数据类型
 描述
 
GoodsNO
 Varchar
 货物编号,主键
 
GoodsName
 Varchar
 货物名称
 
GoodsQuantity
 Numeric
 货物数量
 


向表中添加数据:

GoodsNO
 GoodsName
 GoodsQuantity
 
A001
 watch
 10
 
A002
 book
 20
 
A003
 knife
 15
 


 


本例使用SQL Server2000的JDBC驱动程序访问数据库,所以首先从网上下载msbase.jar、mssqlserver.jar、msutil.jar三个jar文件,
将这三个jar文件加入到环境变量中。也可以将以上三个文件拷贝至jvm机所在的JAVA_HOME\jre\lib\ext目录下,不过不建议这样操作。

源程序如下:

import java.awt.*;

import java.awt.event.*;

import javax.swing.*;

import javax.swing.table.*;

import java.util.*;

import java.sql.*;

 

public class GoodsManager

{

    public static void main(String[] args)

    {

           new GoodsFrame();

    }

}

/******************主窗口*************************/

class GoodsFrame extends JFrame implements ActionListener  

{

    JMenuBar menuManagerBar;

    JMenu menuManager;

    JMenuItem menuSelect,menuAdd,menuDelete,menuModify,menuExit;

   

    String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

    String dbURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbGoods";

    String userName = "sa";

    String userPwd = "";

    Connection dbConn;

    Statement stmt;

 

    GoodsFrame()

    {

           createMenu();

           connetionDB();

           setTitle("货物管理");

           getContentPane().setLayout(null);

           setLocation(200,200);

           setSize(400,300);

           setVisible(true);

           setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

    }

   

    void createMenu()

    {

           menuManagerBar=new JMenuBar();

           menuManager=new JMenu("货物管理");

           menuSelect=new JMenuItem("查询货物");

           menuAdd=new JMenuItem("添加货物");

           menuDelete=new JMenuItem("删除货物");

           menuModify=new JMenuItem("修改货物信息");

           menuExit=new JMenuItem("退出");

          

           menuManager.add(menuSelect);

           menuManager.add(menuAdd);

           menuManager.add(menuDelete);

           menuManager.add(menuModify);

           menuManager.addSeparator();

           menuManager.add(menuExit);

           menuManagerBar.add(menuManager);

           setJMenuBar(menuManagerBar);

          

           menuSelect.addActionListener(this);

           menuAdd.addActionListener(this);

           menuDelete.addActionListener(this);

           menuModify.addActionListener(this);

           menuExit.addActionListener(this);

    }

 

    public void connetionDB()

    {

        try

{

            Class.forName(driverName);       //装载驱动程序

            //连接数据库

dbConn = DriverManager.getConnection(dbURL, userName, userPwd);

            stmt=dbConn.createStatement();   //创建Statement对象

 

        }

catch (Exception e)

{

            e.printStackTrace();

        }

    }

   

    public void actionPerformed(ActionEvent e)

    {

           if(e.getSource()==menuSelect)

           {

                  SelectGoods selectFrame=new SelectGoods(stmt);

                  selectFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuAdd)

           {

                  AddGoods addFrame=new AddGoods(stmt);

                  addFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuDelete)

           {

                  DeleteGoods deleteFrame=new DeleteGoods(stmt);

                  deleteFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuModify)

           {

                  ModifyGoods modifyFrame=new ModifyGoods(stmt);

                  modifyFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuExit)

           {

                  System.exit(0);

           }

    }

}

/******************选择窗口*************************/

class SelectGoods extends JFrame implements ActionListener

{

       Statement selectStmt;

       ResultSet rs;

      

       SelectGoods(Statement stmt)

       {

              DefaultTableModel tableModel = new DefaultTableModel();             

              String[] tableHeads = {"货物编号", "货物名称", "数量"};

    Vector cell ;

    Vector row = new Vector();

    Vector tableHeadName = new Vector();

   

    selectStmt=stmt;

   

    for (int i = 0; i < tableHeads.length; i++)

    {

      tableHeadName.add(tableHeads[i]);

    }

   

    try

    {

      rs=selectStmt.executeQuery("select * from Goods");//查询语句

      while(rs.next())//操作结果集

      {

          cell=new Vector();

          cell.add(rs.getString("GoodsNO"));

          cell.add(rs.getString("GoodsName"));

          cell.add(rs.getInt("GoodsQuantity"));

          row.add(cell);

      }

    }   

    catch (Exception e)

    {

      e.printStackTrace();

    }

   

    tableModel.setDataVector(row, tableHeadName);

    JTable table = new JTable(tableModel);

    table.setRowHeight(20);

    table.setCursor(new Cursor(12));

    getContentPane().setLayout(null);

    JScrollPane scrollPane = new JScrollPane(table);

    scrollPane.setBounds(10,10,380,250);

    scrollPane.setCursor(new Cursor(12));

    this.getContentPane().add(scrollPane);

   

              setTitle("查询货物");

    getContentPane().setLayout(null);

    setLocation(220,220);

    setSize(400,300);

       }

      

         public void actionPerformed(ActionEvent e)

    {

           dispose();

    }

}

/******************添加窗口*************************/

class AddGoods extends JFrame implements ActionListener

{

       Statement addStmt;

      

       JLabel goodsNOLabel;

       JLabel goodsNameLabel;

       JLabel goodsQuantityLabel;

       JTextField goodsNOTextField;

       JTextField goodsNameTextField;

       JTextField goodsQuantityTextField;

       JButton submitButton;

       JButton resetButton;

      

       AddGoods(Statement stmt)

       {

              addStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(110,30,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(190,30,100,20);

              this.getContentPane().add(goodsNOTextField);

              goodsNameLabel=new JLabel("货物名称:");

              goodsNameLabel.setBounds(110,70,80,20);

              this.getContentPane().add(goodsNameLabel);

              goodsNameTextField=new JTextField();

              goodsNameTextField.setBounds(190,70,100,20);

              this.getContentPane().add(goodsNameTextField);

              goodsQuantityLabel=new JLabel("货物数量:");

              goodsQuantityLabel.setBounds(110,110,80,20);

              this.getContentPane().add(goodsQuantityLabel);

              goodsQuantityTextField=new JTextField();

              goodsQuantityTextField.setBounds(190,110,100,20);

              this.getContentPane().add(goodsQuantityTextField);

             

              submitButton=new JButton("提交");

              submitButton.setBounds(110,180,60,30);

              this.getContentPane().add(submitButton);

              resetButton=new JButton("重置");

              resetButton.setBounds(230,180,60,30);

              this.getContentPane().add(resetButton);

             

              submitButton.addActionListener(this);

              resetButton.addActionListener(this);

             

              setTitle("添加货物");

    getContentPane().setLayout(null);

    setLocation(240,240);

    setSize(400,300);

 

       }

      

       public void actionPerformed(ActionEvent ae)

  {

      String strGoodsNO,strGoodsName;

      int intGoodsQuantity;

      strGoodsNO=goodsNOTextField.getText();

      strGoodsName=goodsNameTextField.getText();

      intGoodsQuantity=Integer.parseInt(goodsQuantityTextField.getText());

     

      if(ae.getSource()==submitButton)

    {

           try

           {

          //插入语句

          addStmt.executeUpdate("insert into goods values('"

+strGoodsNO+"','"+strGoodsName+"',"+intGoodsQuantity+")");

           }

           catch (Exception e)

           {

            e.printStackTrace();

           }        

    }

    else

    if(ae.getSource()==resetButton)

    {

           goodsNOTextField.setText("");

        goodsNameTextField.setText("");

        goodsQuantityTextField.setText("");

    }

  }

}

/******************删除窗口*************************/

class DeleteGoods extends JFrame implements ActionListener

{

       Statement deleteStmt;

      

       JLabel goodsNOLabel;

       JTextField goodsNOTextField;

       JButton deleteAllButton;

       JButton deleteOneButton;

      

       DeleteGoods(Statement stmt)

       {

              deleteStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(60,40,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(140,40,100,20);

              this.getContentPane().add(goodsNOTextField);

             

              deleteOneButton=new JButton("删除");

              deleteOneButton.setBounds(50,90,90,30);

              this.getContentPane().add(deleteOneButton);

              deleteAllButton=new JButton("全部删除");

              deleteAllButton.setBounds(150,90,90,30);

              this.getContentPane().add(deleteAllButton);

             

              deleteAllButton.addActionListener(this);

              deleteOneButton.addActionListener(this);

             

              setTitle("删除货物");

    getContentPane().setLayout(null);

    setLocation(260,260);

    setSize(300,200);

       }

      

       public void actionPerformed(ActionEvent ae)

  {

    String strGoodsNO;

    String message="确定删除"+goodsNOTextField.getText()+"的信息?";

      strGoodsNO=goodsNOTextField.getText();

     

      if(ae.getSource()==deleteOneButton)

    {

           int resOne=JOptionPane.showConfirmDialog(this,message,"删除信息",

JOptionPane.OK_CANCEL_OPTION);

      if(resOne==JOptionPane.OK_OPTION)

      {

        try

             {

                 //删除语句

               deleteStmt.executeUpdate("

delete from goods where GoodsNO='"+strGoodsNO+"'");

             }

             catch (Exception e)

             {

              e.printStackTrace();

             }

      }

    }

    else

    if(ae.getSource()==deleteAllButton)

    {

        int resAll=JOptionPane.showConfirmDialog(this,"是否全部删除?","删除信息",

JOptionPane.OK_CANCEL_OPTION);

        if(resAll==JOptionPane.OK_OPTION)

        {

          try

               {

                      //删除语句

              deleteStmt.executeUpdate("delete from goods");

               }

               catch (Exception e)

               {

                e.printStackTrace();

               }

       }                    

    }

  }

}

/******************修改窗口*************************/

class ModifyGoods extends JFrame implements ActionListener

{

       Statement modifyStmt;

       ResultSet rs;

      

       JLabel goodsNOLabel;

       JLabel goodsNameLabel;

       JLabel goodsQuantityLabel;

       JTextField goodsNOTextField;

       JTextField goodsNameTextField;

       JTextField goodsQuantityTextField;

       JButton selectButton;

       JButton modifyButton;

      

       ModifyGoods(Statement stmt)

       {

              modifyStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(110,30,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(190,30,100,20);

              this.getContentPane().add(goodsNOTextField);

              goodsNameLabel=new JLabel("货物名称:");

              goodsNameLabel.setBounds(110,70,80,20);

              this.getContentPane().add(goodsNameLabel);

              goodsNameTextField=new JTextField();

              goodsNameTextField.setBounds(190,70,100,20);

              this.getContentPane().add(goodsNameTextField);

              goodsQuantityLabel=new JLabel("货物数量:");

              goodsQuantityLabel.setBounds(110,110,80,20);

              this.getContentPane().add(goodsQuantityLabel);

              goodsQuantityTextField=new JTextField();

              goodsQuantityTextField.setBounds(190,110,100,20);

              this.getContentPane().add(goodsQuantityTextField);

             

              selectButton=new JButton("查询");

              selectButton.setBounds(110,180,60,30);

              this.getContentPane().add(selectButton);

              modifyButton=new JButton("修改");

              modifyButton.setBounds(230,180,60,30);

              this.getContentPane().add(modifyButton);

             

              selectButton.addActionListener(this);

              modifyButton.addActionListener(this);

             

              setTitle("修改货物信息");

    getContentPane().setLayout(null);

    setLocation(280,280);

    setSize(400,300);

       }

      

       public void actionPerformed(ActionEvent ae)

  {

    String strGoodsNO,strGoodsName;

      int intGoodsQuantity;

      strGoodsNO=goodsNOTextField.getText();

      if(ae.getSource()==selectButton)

    {

           try

      {

          //查询语句

        rs=modifyStmt.executeQuery("

select * from Goods where GoodsNO='"+strGoodsNO+"'");

        while(rs.next())//操作结果集

        {

            goodsNOTextField.setText(rs.getString("GoodsNO"));

            goodsNameTextField.setText(rs.getString("GoodsName"));

            goodsQuantityTextField.setText(rs.getString("GoodsQuantity"));

        }

      }

      catch (Exception e)

      {

        e.printStackTrace();

      }      

    }

    else

    if(ae.getSource()==modifyButton)

    {

           strGoodsNO=goodsNOTextField.getText();

        strGoodsName=goodsNameTextField.getText();

        intGoodsQuantity=Integer.parseInt(goodsQuantityTextField.getText());

        String message="确定修改"+strGoodsNO+"的信息?";

           int resOne=JOptionPane.showConfirmDialog(this,message,"修改信息",

JOptionPane.OK_CANCEL_OPTION);

          

      if(resOne==JOptionPane.OK_OPTION)

      {

        try

             {

                 //修改语句

            modifyStmt.executeUpdate("update  goods set GoodsName='"

+strGoodsName+"',GoodsQuantity='"+intGoodsQuantity+"'where GoodsNO='"+strGoodsNO+"'");

             }

             catch (Exception e)

             {

              e.printStackTrace();

             }

      }

    }

  }

}
 

 

 

 

JDBC访问数据库示例


例12-1:实现对仓库货物的添加、查询等操作。


本章的例子实现对仓库货物的添加、查询等操作,使用SQL server2000数据库。数据库名为dbGoods,用户名为:sa,密码为空,货物表名为Goods。Goods表的结构如表12-1所示。涉及的字段包括货物编号、货物名称、货物数量三个字段。

列名称
 数据类型
 描述
 
GoodsNO
 Varchar
 货物编号,主键
 
GoodsName
 Varchar
 货物名称
 
GoodsQuantity
 Numeric
 货物数量
 


向表中添加数据:

GoodsNO
 GoodsName
 GoodsQuantity
 
A001
 watch
 10
 
A002
 book
 20
 
A003
 knife
 15
 


 


本例使用SQL Server2000的JDBC驱动程序访问数据库,所以首先从网上下载msbase.jar、mssqlserver.jar、msutil.jar三个jar文件,
将这三个jar文件加入到环境变量中。也可以将以上三个文件拷贝至jvm机所在的JAVA_HOME\jre\lib\ext目录下,不过不建议这样操作。

源程序如下:

import java.awt.*;

import java.awt.event.*;

import javax.swing.*;

import javax.swing.table.*;

import java.util.*;

import java.sql.*;

 

public class GoodsManager

{

    public static void main(String[] args)

    {

           new GoodsFrame();

    }

}

/******************主窗口*************************/

class GoodsFrame extends JFrame implements ActionListener  

{

    JMenuBar menuManagerBar;

    JMenu menuManager;

    JMenuItem menuSelect,menuAdd,menuDelete,menuModify,menuExit;

   

    String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

    String dbURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbGoods";

    String userName = "sa";

    String userPwd = "";

    Connection dbConn;

    Statement stmt;

 

    GoodsFrame()

    {

           createMenu();

           connetionDB();

           setTitle("货物管理");

           getContentPane().setLayout(null);

           setLocation(200,200);

           setSize(400,300);

           setVisible(true);

           setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

    }

   

    void createMenu()

    {

           menuManagerBar=new JMenuBar();

           menuManager=new JMenu("货物管理");

           menuSelect=new JMenuItem("查询货物");

           menuAdd=new JMenuItem("添加货物");

           menuDelete=new JMenuItem("删除货物");

           menuModify=new JMenuItem("修改货物信息");

           menuExit=new JMenuItem("退出");

          

           menuManager.add(menuSelect);

           menuManager.add(menuAdd);

           menuManager.add(menuDelete);

           menuManager.add(menuModify);

           menuManager.addSeparator();

           menuManager.add(menuExit);

           menuManagerBar.add(menuManager);

           setJMenuBar(menuManagerBar);

          

           menuSelect.addActionListener(this);

           menuAdd.addActionListener(this);

           menuDelete.addActionListener(this);

           menuModify.addActionListener(this);

           menuExit.addActionListener(this);

    }

 

    public void connetionDB()

    {

        try

{

            Class.forName(driverName);       //装载驱动程序

            //连接数据库

dbConn = DriverManager.getConnection(dbURL, userName, userPwd);

            stmt=dbConn.createStatement();   //创建Statement对象

 

        }

catch (Exception e)

{

            e.printStackTrace();

        }

    }

   

    public void actionPerformed(ActionEvent e)

    {

           if(e.getSource()==menuSelect)

           {

                  SelectGoods selectFrame=new SelectGoods(stmt);

                  selectFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuAdd)

           {

                  AddGoods addFrame=new AddGoods(stmt);

                  addFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuDelete)

           {

                  DeleteGoods deleteFrame=new DeleteGoods(stmt);

                  deleteFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuModify)

           {

                  ModifyGoods modifyFrame=new ModifyGoods(stmt);

                  modifyFrame.setVisible(true);

           }

           else

           if(e.getSource()==menuExit)

           {

                  System.exit(0);

           }

    }

}

/******************选择窗口*************************/

class SelectGoods extends JFrame implements ActionListener

{

       Statement selectStmt;

       ResultSet rs;

      

       SelectGoods(Statement stmt)

       {

              DefaultTableModel tableModel = new DefaultTableModel();             

              String[] tableHeads = {"货物编号", "货物名称", "数量"};

    Vector cell ;

    Vector row = new Vector();

    Vector tableHeadName = new Vector();

   

    selectStmt=stmt;

   

    for (int i = 0; i < tableHeads.length; i++)

    {

      tableHeadName.add(tableHeads[i]);

    }

   

    try

    {

      rs=selectStmt.executeQuery("select * from Goods");//查询语句

      while(rs.next())//操作结果集

      {

          cell=new Vector();

          cell.add(rs.getString("GoodsNO"));

          cell.add(rs.getString("GoodsName"));

          cell.add(rs.getInt("GoodsQuantity"));

          row.add(cell);

      }

    }   

    catch (Exception e)

    {

      e.printStackTrace();

    }

   

    tableModel.setDataVector(row, tableHeadName);

    JTable table = new JTable(tableModel);

    table.setRowHeight(20);

    table.setCursor(new Cursor(12));

    getContentPane().setLayout(null);

    JScrollPane scrollPane = new JScrollPane(table);

    scrollPane.setBounds(10,10,380,250);

    scrollPane.setCursor(new Cursor(12));

    this.getContentPane().add(scrollPane);

   

              setTitle("查询货物");

    getContentPane().setLayout(null);

    setLocation(220,220);

    setSize(400,300);

       }

      

         public void actionPerformed(ActionEvent e)

    {

           dispose();

    }

}

/******************添加窗口*************************/

class AddGoods extends JFrame implements ActionListener

{

       Statement addStmt;

      

       JLabel goodsNOLabel;

       JLabel goodsNameLabel;

       JLabel goodsQuantityLabel;

       JTextField goodsNOTextField;

       JTextField goodsNameTextField;

       JTextField goodsQuantityTextField;

       JButton submitButton;

       JButton resetButton;

      

       AddGoods(Statement stmt)

       {

              addStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(110,30,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(190,30,100,20);

              this.getContentPane().add(goodsNOTextField);

              goodsNameLabel=new JLabel("货物名称:");

              goodsNameLabel.setBounds(110,70,80,20);

              this.getContentPane().add(goodsNameLabel);

              goodsNameTextField=new JTextField();

              goodsNameTextField.setBounds(190,70,100,20);

              this.getContentPane().add(goodsNameTextField);

              goodsQuantityLabel=new JLabel("货物数量:");

              goodsQuantityLabel.setBounds(110,110,80,20);

              this.getContentPane().add(goodsQuantityLabel);

              goodsQuantityTextField=new JTextField();

              goodsQuantityTextField.setBounds(190,110,100,20);

              this.getContentPane().add(goodsQuantityTextField);

             

              submitButton=new JButton("提交");

              submitButton.setBounds(110,180,60,30);

              this.getContentPane().add(submitButton);

              resetButton=new JButton("重置");

              resetButton.setBounds(230,180,60,30);

              this.getContentPane().add(resetButton);

             

              submitButton.addActionListener(this);

              resetButton.addActionListener(this);

             

              setTitle("添加货物");

    getContentPane().setLayout(null);

    setLocation(240,240);

    setSize(400,300);

 

       }

      

       public void actionPerformed(ActionEvent ae)

  {

      String strGoodsNO,strGoodsName;

      int intGoodsQuantity;

      strGoodsNO=goodsNOTextField.getText();

      strGoodsName=goodsNameTextField.getText();

      intGoodsQuantity=Integer.parseInt(goodsQuantityTextField.getText());

     

      if(ae.getSource()==submitButton)

    {

           try

           {

          //插入语句

          addStmt.executeUpdate("insert into goods values('"

+strGoodsNO+"','"+strGoodsName+"',"+intGoodsQuantity+")");

           }

           catch (Exception e)

           {

            e.printStackTrace();

           }        

    }

    else

    if(ae.getSource()==resetButton)

    {

           goodsNOTextField.setText("");

        goodsNameTextField.setText("");

        goodsQuantityTextField.setText("");

    }

  }

}

/******************删除窗口*************************/

class DeleteGoods extends JFrame implements ActionListener

{

       Statement deleteStmt;

      

       JLabel goodsNOLabel;

       JTextField goodsNOTextField;

       JButton deleteAllButton;

       JButton deleteOneButton;

      

       DeleteGoods(Statement stmt)

       {

              deleteStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(60,40,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(140,40,100,20);

              this.getContentPane().add(goodsNOTextField);

             

              deleteOneButton=new JButton("删除");

              deleteOneButton.setBounds(50,90,90,30);

              this.getContentPane().add(deleteOneButton);

              deleteAllButton=new JButton("全部删除");

              deleteAllButton.setBounds(150,90,90,30);

              this.getContentPane().add(deleteAllButton);

             

              deleteAllButton.addActionListener(this);

              deleteOneButton.addActionListener(this);

             

              setTitle("删除货物");

    getContentPane().setLayout(null);

    setLocation(260,260);

    setSize(300,200);

       }

      

       public void actionPerformed(ActionEvent ae)

  {

    String strGoodsNO;

    String message="确定删除"+goodsNOTextField.getText()+"的信息?";

      strGoodsNO=goodsNOTextField.getText();

     

      if(ae.getSource()==deleteOneButton)

    {

           int resOne=JOptionPane.showConfirmDialog(this,message,"删除信息",

JOptionPane.OK_CANCEL_OPTION);

      if(resOne==JOptionPane.OK_OPTION)

      {

        try

             {

                 //删除语句

               deleteStmt.executeUpdate("

delete from goods where GoodsNO='"+strGoodsNO+"'");

             }

             catch (Exception e)

             {

              e.printStackTrace();

             }

      }

    }

    else

    if(ae.getSource()==deleteAllButton)

    {

        int resAll=JOptionPane.showConfirmDialog(this,"是否全部删除?","删除信息",

JOptionPane.OK_CANCEL_OPTION);

        if(resAll==JOptionPane.OK_OPTION)

        {

          try

               {

                      //删除语句

              deleteStmt.executeUpdate("delete from goods");

               }

               catch (Exception e)

               {

                e.printStackTrace();

               }

       }                    

    }

  }

}

/******************修改窗口*************************/

class ModifyGoods extends JFrame implements ActionListener

{

       Statement modifyStmt;

       ResultSet rs;

      

       JLabel goodsNOLabel;

       JLabel goodsNameLabel;

       JLabel goodsQuantityLabel;

       JTextField goodsNOTextField;

       JTextField goodsNameTextField;

       JTextField goodsQuantityTextField;

       JButton selectButton;

       JButton modifyButton;

      

       ModifyGoods(Statement stmt)

       {

              modifyStmt=stmt;

             

              goodsNOLabel=new JLabel("货物编号:");

              goodsNOLabel.setBounds(110,30,80,20);

              this.getContentPane().add(goodsNOLabel);

              goodsNOTextField=new JTextField();

              goodsNOTextField.setBounds(190,30,100,20);

              this.getContentPane().add(goodsNOTextField);

              goodsNameLabel=new JLabel("货物名称:");

              goodsNameLabel.setBounds(110,70,80,20);

              this.getContentPane().add(goodsNameLabel);

              goodsNameTextField=new JTextField();

              goodsNameTextField.setBounds(190,70,100,20);

              this.getContentPane().add(goodsNameTextField);

              goodsQuantityLabel=new JLabel("货物数量:");

              goodsQuantityLabel.setBounds(110,110,80,20);

              this.getContentPane().add(goodsQuantityLabel);

              goodsQuantityTextField=new JTextField();

              goodsQuantityTextField.setBounds(190,110,100,20);

              this.getContentPane().add(goodsQuantityTextField);

             

              selectButton=new JButton("查询");

              selectButton.setBounds(110,180,60,30);

              this.getContentPane().add(selectButton);

              modifyButton=new JButton("修改");

              modifyButton.setBounds(230,180,60,30);

              this.getContentPane().add(modifyButton);

             

              selectButton.addActionListener(this);

              modifyButton.addActionListener(this);

             

              setTitle("修改货物信息");

    getContentPane().setLayout(null);

    setLocation(280,280);

    setSize(400,300);

       }

      

       public void actionPerformed(ActionEvent ae)

  {

    String strGoodsNO,strGoodsName;

      int intGoodsQuantity;

      strGoodsNO=goodsNOTextField.getText();

      if(ae.getSource()==selectButton)

    {

           try

      {

          //查询语句

        rs=modifyStmt.executeQuery("

select * from Goods where GoodsNO='"+strGoodsNO+"'");

        while(rs.next())//操作结果集

        {

            goodsNOTextField.setText(rs.getString("GoodsNO"));

            goodsNameTextField.setText(rs.getString("GoodsName"));

            goodsQuantityTextField.setText(rs.getString("GoodsQuantity"));

        }

      }

      catch (Exception e)

      {

        e.printStackTrace();

      }      

    }

    else

    if(ae.getSource()==modifyButton)

    {

           strGoodsNO=goodsNOTextField.getText();

        strGoodsName=goodsNameTextField.getText();

        intGoodsQuantity=Integer.parseInt(goodsQuantityTextField.getText());

        String message="确定修改"+strGoodsNO+"的信息?";

           int resOne=JOptionPane.showConfirmDialog(this,message,"修改信息",

JOptionPane.OK_CANCEL_OPTION);

          

      if(resOne==JOptionPane.OK_OPTION)

      {

        try

             {

                 //修改语句

            modifyStmt.executeUpdate("update  goods set GoodsName='"

+strGoodsName+"',GoodsQuantity='"+intGoodsQuantity+"'where GoodsNO='"+strGoodsNO+"'");

             }

             catch (Exception e)

             {

              e.printStackTrace();

             }

      }

    }

  }

}
 

 

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

xiaweiaixiaocui

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值