黑马程序员 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();
}
}
}
}
}
3387
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
