數(shù)據(jù)庫：
create database Instant;
use Instant
create table Product(
ProductId int not null primary key,
ProductName varchar(20) not null,
ProductPrice varchar(15) not null,
ProductDiscount varchar(15) not null
);
GUI界面上應(yīng)當(dāng)具有'第一條','最后一條','上一條','下一條','增加','刪除','修改','查找' 等按鈕和用于顯示結(jié)果的文本框
用java的事件處理和jdbc api完成按鈕相應(yīng)功能
連接數(shù)據(jù)庫類
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
private Connection conn;
private String driver = 'com.microsoft.jdbc.sqlserver.SQLServerDriver';
private String url = 'jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=Instant';
private String username = 'sa';
private String password = '';
public DBConnection() {
try {
Class.forName(driver);
this.conn = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public Connection getConnection(){
return this.conn;
}
}
界面和業(yè)務(wù)處理類
import java.awt.HeadlessException;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextField;
public class ProductText extends JFrame implements ActionListener {
private JLabel lblProductId;
private JLabel lblProductName;
private JLabel lblProductPrice;
private JLabel lblDiscount;
private JTextField tfProductId;
private JTextField tfPRoductName;
private JTextField tfPRoductPrice;
private JTextField tfDiscount;
private JButton btnFirst;
private JButton btnLast;
private JButton btnBefore;
private JButton btnNext;
private JButton btnAdd;
private JButton btnModify;
private JButton btnDel;
private JButton btnQuery;
private DBConnection dbconn = null;
private Connection conn = null;
private ResultSet rs = null;
private PreparedStatement pstmt = null;
private Statement stmt = null;
public ProductText() {
this.setLayout(null);
this.setBounds(200, 200, 400, 380);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
lblProductId = new JLabel('商品編號');
lblProductId.setBounds(70, 15, 60, 25);
this.add(lblProductId);
tfProductId = new JTextField();
tfProductId.setBounds(140, 15, 160, 25);
this.add(tfProductId);
lblProductName = new JLabel('商品名稱');
lblProductName.setBounds(70, 60, 60, 25);
this.add(lblProductName);
tfPRoductName = new JTextField();
tfPRoductName.setBounds(140, 60, 160, 25);
this.add(tfPRoductName);
lblProductPrice = new JLabel('商品價(jià)格');
lblProductPrice.setBounds(70, 105, 60, 25);
this.add(lblProductPrice);
tfPRoductPrice = new JTextField();
tfPRoductPrice.setBounds(140, 105, 160, 25);
this.add(tfPRoductPrice);
lblDiscount = new JLabel('商品數(shù)量');
lblDiscount.setBounds(70, 150, 60, 25);
this.add(lblDiscount);
tfDiscount = new JTextField();
tfDiscount.setBounds(140, 150, 160, 25);
this.add(tfDiscount);
btnAdd = new JButton('添加');
btnAdd.addActionListener(this);
btnAdd.setBounds(60, 250, 60, 30);
this.add(btnAdd);
btnDel = new JButton('刪除');
btnDel.addActionListener(this);
btnDel.setBounds(130, 250, 60, 30);
this.add(btnDel);
btnModify = new JButton('修改');
btnModify.addActionListener(this);
btnModify.setBounds(200, 250, 60, 30);
this.add(btnModify);
btnQuery = new JButton('查詢');
btnQuery.addActionListener(this);
btnQuery.setBounds(270, 250, 60, 30);
this.add(btnQuery);
btnFirst = new JButton('第一個(gè)');
btnFirst.addActionListener(this);
btnFirst.setBounds(15, 290, 80, 30);
this.add(btnFirst);
btnLast = new JButton('最后一個(gè)');
btnLast.addActionListener(this);
btnLast.setBounds(105, 290, 90, 30);
this.add(btnLast);
btnBefore = new JButton('上一個(gè)');
btnBefore.setBounds(205, 290, 80, 30);
btnBefore.addActionListener(this);
this.add(btnBefore);
btnNext = new JButton('下一個(gè)');
btnNext.addActionListener(this);
btnNext.setBounds(295, 290, 80, 30);
this.add(btnNext);
this.setTitle('商品信息');
this.setResizable(false);
this.setVisible(true);
}
public static void main(String[] args) {
new ProductText();
}
public void actionPerformed(ActionEvent e) {
if (e.getActionCommand().equals('添加')) {
if (tfProductId.getText().equals('')
|| tfPRoductName.getText().equals('')
|| tfPRoductPrice.getText().equals('')
|| tfDiscount.getText().equals('')) {
JOptionPane.showMessageDialog(this, '請?zhí)顚懴嚓P(guān)信息');
return;
} else {
add();
tfProductId.setText('');
tfPRoductName.setText('');
tfPRoductPrice.setText('');
tfDiscount.setText('');
}
} else if (e.getActionCommand().equals('刪除')) {
if (tfProductId.getText().equals('')) {
JOptionPane.showMessageDialog(this, '請選擇一條信息');
} else {
int n = JOptionPane.showConfirmDialog(this, '您確定要?jiǎng)h除此條記錄嗎？');
if (n == 0) {
del();
tfProductId.setText('');
tfPRoductName.setText('');
tfPRoductPrice.setText('');
tfDiscount.setText('');
}
}
} else if (e.getActionCommand().equals('修改')) {
if (tfProductId.getText().trim().equals('')
|| tfPRoductName.getText().trim().equals('')
|| tfPRoductPrice.getText().trim().equals('')
|| tfDiscount.getText().trim().equals('')) {
JOptionPane.showMessageDialog(this, '請?zhí)顚懴嚓P(guān)信息');
} else {
modify();
}
} else if (e.getActionCommand().equals('查詢')) {
if (tfProductId.getText().trim().equals('')) {
JOptionPane.showMessageDialog(this, '請選擇要查詢高品的ID');
} else {
query();
}
} else if (e.getActionCommand().equals('第一個(gè)')) {
first();
} else if (e.getActionCommand().equals('最后一個(gè)')) {
laster();
} else if (e.getActionCommand().equals('上一個(gè)')) {
if (tfProductId.getText().trim().equals('')) {
JOptionPane.showMessageDialog(this, '請您先選擇一條商品信息');
} else {
before();
}
} else if (e.getActionCommand().equals('下一個(gè)')) {
if (tfProductId.getText().equals('')) {
JOptionPane.showMessageDialog(this, '請您先選擇一條商品信息');
} else {
next();
}
}
}
public void add() {
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql1 = 'select ProductId from Product where ProductId='+tfProductId.getText();
String sql = 'insert into Product (ProductId,ProductName,ProductPrice,ProductDiscount) values(?,?,?,?)';
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql1);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if(!rs.next()){
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(tfProductId.getText()));
pstmt.setString(2, tfPRoductName.getText());
pstmt.setString(3, tfPRoductPrice.getText());
pstmt.setString(4, tfDiscount.getText());
pstmt.executeUpdate();
JOptionPane.showMessageDialog(this, '添加成功');
} catch (SQLException e) {
// e.printStackTrace();
JOptionPane.showMessageDialog(this, '添加失敗');
} finally {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} else {
JOptionPane.showMessageDialog(this, '商品編號重復(fù)');
}
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (HeadlessException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void del() {
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql = 'delete from Product where ProductId = '
+ tfProductId.getText();
try {
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
// JOptionPane.showMessageDialog(this, '刪除成功');
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void modify() {
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql1 = 'select ProductId from Product where ProductId='+tfProductId.getText();
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql1);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if(rs.next()){
String sql = 'update Product set ProductName = ?,ProductPrice = ?,ProductDiscount=? where ProductId = '
+ tfProductId.getText();
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, tfPRoductName.getText());
pstmt.setString(2, tfPRoductPrice.getText());
pstmt.setString(3, tfDiscount.getText());
pstmt.executeUpdate();
JOptionPane.showMessageDialog(this, '修改成功');
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} else {
JOptionPane.showMessageDialog(this, '沒有與此ID相對應(yīng)的記錄');
tfProductId.setText('');
tfPRoductName.setText('');
tfPRoductPrice.setText('');
tfDiscount.setText('');
}
} catch (HeadlessException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void query() {
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql = 'select ProductName,ProductPrice,ProductDiscount from Product where ProductId='
+ tfProductId.getText();
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
String ProductName = rs.getString('ProductName');
String ProductPrice = rs.getString('ProductPrice');
String ProductDiscount = rs.getString('ProductDiscount');
tfPRoductName.setText(ProductName);
tfPRoductPrice.setText(ProductPrice);
tfDiscount.setText(ProductDiscount);
} else {
JOptionPane.showMessageDialog(this, '查詢失敗,您可能沒有此信息');
tfProductId.setText('');
tfPRoductName.setText('');
tfPRoductPrice.setText('');
tfDiscount.setText('');
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void first() {
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql = 'select * from Product';
try {
// 1.TYPE_FORWORD_ONLY,只可向前滾動；
// 2.TYPE_SCROLL_INSENSITIVE,雙向滾動，但不及時(shí)更新，就是如果數(shù)據(jù)庫里的數(shù)據(jù)修改過，并不在ResultSet中反應(yīng)出來。
// 3.TYPE_SCROLL_SENSITIVE，雙向滾動，并及時(shí)跟蹤數(shù)據(jù)庫的更新,以便更改ResultSet中的數(shù)據(jù)。
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
if (rs.first()) {
//stmt = conn.createStatement();
String ProductId = rs.getString('ProductId');
String ProductName = rs.getString('ProductName');
String ProductPrice = rs.getString('ProductPrice');
String ProductDiscount = rs.getString('ProductDiscount');
tfProductId.setText(ProductId);
tfPRoductName.setText(ProductName);
tfPRoductPrice.setText(ProductPrice);
tfDiscount.setText(ProductDiscount);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void laster() {
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql = 'select * from Product';
try {
// 1.TYPE_FORWORD_ONLY,只可向前滾動；
// 2.TYPE_SCROLL_INSENSITIVE,雙向滾動，但不及時(shí)更新，就是如果數(shù)據(jù)庫里的數(shù)據(jù)修改過，并不在ResultSet中反應(yīng)出來。
// 3.TYPE_SCROLL_SENSITIVE，雙向滾動，并及時(shí)跟蹤數(shù)據(jù)庫的更新,以便更改ResultSet中的數(shù)據(jù)。
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
if (rs.last()) {
String ProductId = rs.getString('ProductId');
String ProductName = rs.getString('ProductName');
String ProductPrice = rs.getString('ProductPrice');
String ProductDiscount = rs.getString('ProductDiscount');
tfProductId.setText(ProductId);
tfPRoductName.setText(ProductName);
tfPRoductPrice.setText(ProductPrice);
tfDiscount.setText(ProductDiscount);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void before() {
ArrayList list = new ArrayList();
int index = 0;
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql = 'select * from Product';
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
Product p = new Product();
p.setProductId(rs.getInt(1));
p.setProductName(rs.getString(2));
p.setProductPrice(rs.getString(3));
p.setProductDiscount(rs.getString(4));
list.add(p);
}
int id = Integer.parseInt(tfProductId.getText());
for (int i = 0; i < list.size();="" i++)="">
Product pr = list.get(i);
if (pr.getProductId() == id) {
index = i;
break;
}
}
if (index >= 1) {
Product pro = list.get(--index);
String s = String.valueOf(pro.getProductId());
tfProductId.setText(s);
tfPRoductName.setText(pro.getProductName());
tfPRoductPrice.setText(pro.getProductPrice());
tfDiscount.setText(pro.getProductDiscount());
} else {
JOptionPane.showMessageDialog(this, '已經(jīng)是第一條記錄');
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void next() {
ArrayList list = new ArrayList();
int index = 0;
dbconn = new DBConnection();
conn = dbconn.getConnection();
String sql = 'select * from Product';
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
Product p = new Product();
p.setProductId(rs.getInt(1));
p.setProductName(rs.getString(2));
p.setProductPrice(rs.getString(3));
p.setProductDiscount(rs.getString(4));
list.add(p);
}
int id = Integer.parseInt(tfProductId.getText());
for (int i = 0; i < list.size();="" i++)="">
Product pr = list.get(i);
if (pr.getProductId() == id) {
index = i;
break;
}
}
if (index < list.size()-1)="">
Product pro = list.get(++index);
String s = String.valueOf(pro.getProductId());
tfProductId.setText(s);
tfPRoductName.setText(pro.getProductName());
tfPRoductPrice.setText(pro.getProductPrice());
tfDiscount.setText(pro.getProductDiscount());
} else {
JOptionPane.showMessageDialog(this, '已經(jīng)是最后一條記錄');
