|
数据库： 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界面上应当具有'第一条','最后一条','上一条','下一条','增加','删除','修改','查找' 等按钮和用于显示结果的文本框 用java的事件处理和jdbc api完成按钮相应功能 连接数据库类 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;
} } 界面和业务处理类 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('商品价格');
lblProductPrice.setBounds(70, 105, 60, 25);
this.add(lblProductPrice); tfPRoductPrice = new JTextField();
tfPRoductPrice.setBounds(140, 105, 160, 25);
this.add(tfPRoductPrice); lblDiscount = new JLabel('商品数量');
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('第一个');
btnFirst.addActionListener(this);
btnFirst.setBounds(15, 290, 80, 30);
this.add(btnFirst); btnLast = new JButton('最后一个');
btnLast.addActionListener(this);
btnLast.setBounds(105, 290, 90, 30);
this.add(btnLast); btnBefore = new JButton('上一个');
btnBefore.setBounds(205, 290, 80, 30);
btnBefore.addActionListener(this);
this.add(btnBefore); btnNext = new JButton('下一个');
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, '请填写相关信息');
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, '您确定要删除此条记录吗？');
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, '请填写相关信息');
} else {
modify();
}
} else if (e.getActionCommand().equals('查询')) {
if (tfProductId.getText().trim().equals('')) {
JOptionPane.showMessageDialog(this, '请选择要查询高品的ID');
} else {
query();
}
} else if (e.getActionCommand().equals('第一个')) {
first();
} else if (e.getActionCommand().equals('最后一个')) {
laster();
} else if (e.getActionCommand().equals('上一个')) {
if (tfProductId.getText().trim().equals('')) {
JOptionPane.showMessageDialog(this, '请您先选择一条商品信息');
} else {
before();
}
} else if (e.getActionCommand().equals('下一个')) {
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, '商品编号重复');
}
} 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相对应的记录');
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,双向滚动，但不及时更新，就是如果数据库里的数据修改过，并不在ResultSet中反应出来。
// 3.TYPE_SCROLL_SENSITIVE，双向滚动，并及时跟踪数据库的更新,以便更改ResultSet中的数据。
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,双向滚动，但不及时更新，就是如果数据库里的数据修改过，并不在ResultSet中反应出来。
// 3.TYPE_SCROLL_SENSITIVE，双向滚动，并及时跟踪数据库的更新,以便更改ResultSet中的数据。
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, '已经是第一条记录');
}
} 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, '已经是最后一条记录');
}
} catch (SQLException e) {
e.printStackTrace();
}
} }
|
