正文
「会员卡管理系统」 · Java Swing + MySQL JDBC开发
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
目录
目录
一、语言和环境
二、实现功能
三、数据库设计
四、具体要求及推荐实现步骤
五、注意事项
六、评分标准
>>>实现代码：
数据库
com.ynavc.Bean
com.ynavc.Controller
com.ynavc.Dao
com.ynavc.Test
com.ynavc.Vive
一、语言和环境
1. 实现语言：Java。
2. 开发环境：MyEclipse+MySQL。
二、实现功能
使用 Java 技术开发一个会员卡管理系统，具体实现功能如下： 1. 程序启动显示主界面，点击查询按钮，显示所有会员卡信息，如图 1 所示
2. 选中某行会员卡信息，点击删除按钮，提示是否删除，如图 2 所示
3. 如果点击是按钮，则确认删除，如果点击否或取消，则取消删除操作。删除成功后 给出对应的提示信息，如图 3 所示。
三、数据库设计
1. 创建数据库（ MemberDB ）
2. 创建会员卡信息表( members )，结构如下：
四、具体要求及推荐实现步骤
1. 按以上数据库要求建库建表，并参考图 2 添加至少 、3 条测试数据
2. 搭建系统框架
（1）编写实体类。
（2）创建数据访问层，并编写 BaseDAO 和数据访问层代码。
（3）创建窗体。
3. 创建主窗体
（1）按照图 1，创建窗体 MainFrame ，并添加相应的组件
（2）表格中的数据均来源于数据库，点击查询按钮显示所有数据。
（3）实现点击“删除”按钮，删除一条选中的会员卡记录。
五、注意事项
1. 将数据库导出成 sql 脚本文件
2. 将整个项目打包提交
六、评分标准
>>>实现代码：
数据库
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for members
-- ----------------------------
DROP TABLE IF EXISTS `members`;
CREATE TABLE `members` (
`memberID` int(11) NOT NULL AUTO_INCREMENT,
`phone` varchar(32) NOT NULL,
`name` varchar(32) NOT NULL,
`sex` varchar(5) DEFAULT NULL,
`openDate` date DEFAULT NULL,
`balance` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`memberID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of members
-- ----------------------------
INSERT INTO `members` VALUES ('1', '18214217246', '杨明金', '男', '2020-10-21', '200.00');
INSERT INTO `members` VALUES ('2', '18214217244', '李韬', '男', '2020-10-21', '500.00');
com.ynavc.Bean
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for members
-- ----------------------------
DROP TABLE IF EXISTS `members`;
CREATE TABLE `members` (
`memberID` int(11) NOT NULL AUTO_INCREMENT,
`phone` varchar(32) NOT NULL,
`name` varchar(32) NOT NULL,
`sex` varchar(5) DEFAULT NULL,
`openDate` date DEFAULT NULL,
`balance` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`memberID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of members
-- ----------------------------
INSERT INTO `members` VALUES ('1', '18214217246', '杨明金', '男', '2020-10-21', '200.00');
INSERT INTO `members` VALUES ('2', '18214217244', '李韬', '男', '2020-10-21', '500.00');com.ynavc.Bean
member.java
package com.ynavc.Bean;
public class Members {
int memberID;
String phone;
String name;
String sex;
String openDate;
String balance;
public Members() {
super();
}
public Members(int memberID, String phone, String name, String sex, String openDate, String balance) {
super();
this.memberID = memberID;
this.phone = phone;
this.name = name;
this.sex = sex;
this.openDate = openDate;
this.balance = balance;
}
public int getMemberID() {
return memberID;
}
public void setMemberID(int memberID) {
this.memberID = memberID;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getOpenDate() {
return openDate;
}
public void setOpenDate(String openDate) {
this.openDate = openDate;
}
public String getBalance() {
return balance;
}
public void setBalance(String balance) {
this.balance = balance;
}
}
com.ynavc.Controller
Select.java
package com.ynavc.Controller;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ynavc.Bean.Members;
import com.ynavc.Dao.DbConnection;
public class Select {
public Object[][] getMebers(String sql) {
ResultSet resultSet = DbConnection.query(sql);
ArrayList<Members> list=new ArrayList<Members>();
try {
while (resultSet.next()) {
Members m=new Members();
m.setMemberID(resultSet.getInt(1));
m.setPhone(resultSet.getString(2));
m.setName(resultSet.getString(3));
m.setSex(resultSet.getString(4));
m.setOpenDate(resultSet.getString(5));
m.setBalance(resultSet.getString(6));
list.add(m);
}
} catch (SQLException e) {
e.printStackTrace();
}
Object[][] objects=new Object[list.size()][6];
for(int i=0;i<list.size();i++) {
objects[i][0]=list.get(i).getMemberID();
objects[i][1]=list.get(i).getPhone();
objects[i][2]=list.get(i).getName();
objects[i][3]=list.get(i).getSex();
objects[i][4]=list.get(i).getOpenDate();
objects[i][5]=list.get(i).getBalance();
}
return objects;
}
}Updata.java
package com.ynavc.Controller;
import com.ynavc.Dao.DbConnection;
public class Updata {
//添加数据
public int addData(String sql) {
return DbConnection.updataInfo(sql);
}
}com.ynavc.Dao
DbConnection.java
package com.ynavc.Dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;
import com.mysql.jdbc.Statement;
public class DbConnection {
//驱动类的类名
private static final String DRIVERNAME="com.mysql.jdbc.Driver";
//连接数据的URL路径
private static final String URL="jdbc:mysql://127.0.0.1:3306/memberdb?useSSL=true&serverTimezone=GMT";
//数据库登录账号
private static final String USER="root";
//数据库登录密码
private static final String PASSWORD="root";
//加载驱动
static{
try {
Class.forName(DRIVERNAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取数据库连接
public static Connection getConnection() {
try {
return DriverManager.getConnection(URL,USER,PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//查询
public static ResultSet query(String sql) {
System.out.println(sql);
//获取连接
Connection connection=getConnection();
PreparedStatement psd;
try {
psd = connection.prepareStatement(sql);
return psd.executeQuery();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null,"执行语句出错\n"+e.toString());
e.printStackTrace();
}
return null;
}
//增、删、改、查
public static int updataInfo(String sql) {
System.out.println(sql);
//获取连接
Connection connection=getConnection();
try {
PreparedStatement psd=connection.prepareStatement(sql);
return psd.executeUpdate();
} catch (SQLException e) {
JOptionPane.showMessageDialog(null,"执行语句出错\n"+e.toString());
e.printStackTrace();
}
return 0;
}
//关闭连接
public static void colse(ResultSet rs,Statement stmt,Connection conn) throws Exception{
try { if (rs != null){ rs.close(); }
if (stmt != null) { stmt.cancel(); }
if (conn != null) { conn.close(); }
} catch (Exception e) {
e.printStackTrace(); throw new Exception();
}
}
}com.ynavc.Test
Main.java
package com.ynavc.Test;
import com.ynavc.Vive.MainJframe;
public class Main {
public static void main(String[] args) {
new MainJframe().setVisible(true);
}
}
com.ynavc.Vive
MainJframe.java
package com.ynavc.Vive;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.ScrollPaneConstants;
import javax.swing.table.DefaultTableModel;
import com.ynavc.Controller.Select;
import com.ynavc.Controller.Updata;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
public class MainJframe extends JFrame {
Select select = new Select();
Updata updata = new Updata();
Object[] header= {"序号","手机","姓名","性别","会员日期","卡余额"};
String sql = "SELECT memberid,phone,name,sex,openDate,balance FROM members;";
Object[][] data= select.getMebers(sql);
DefaultTableModel df = new DefaultTableModel(data, header);
int v=ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED;
int h=ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED;
public MainJframe() {
super("会员卡管理系统");
this.setBounds(0, 0, 600, 400);
this.setLocationRelativeTo(null);//让窗口在屏幕中间显示
this.setResizable(false);//让窗口大小不可改变
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//用户单击窗口的关闭按钮时程序执行的操作
getContentPane().setLayout(null);
JTable jTable = new JTable(df);
JScrollPane jsp=new JScrollPane(jTable,v,h);
jsp.setBounds(10, 60, 574, 301);
getContentPane().add(jsp);
JButton btnNewButton_1 = new JButton("查询");
btnNewButton_1.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String sql = "SELECT memberid,phone,name,sex,openDate,balance FROM members;";
Object[][] data = select.getMebers(sql);
df.setDataVector(data, header);
}
});
btnNewButton_1.setBounds(301, 23, 93, 23);
getContentPane().add(btnNewButton_1);
JButton btnNewButton_2 = new JButton("删除");
btnNewButton_2.setBounds(414, 23, 93, 23);
getContentPane().add(btnNewButton_2);
btnNewButton_2.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
if (jTable.getSelectedColumn()<0) {
JOptionPane.showMessageDialog(null, "请选中要删除的数据！");
} else {
int ok = JOptionPane.showConfirmDialog(null,"您确定要删除该会员信息吗？!","删除提示",0,1);
if(ok == JOptionPane.OK_OPTION){
int id = Integer.parseInt(jTable.getValueAt(jTable.getSelectedRow(), 0).toString());
String sql="delete from members where memberid="+id;
int result = updata.addData(sql);
if (result>0) {
JOptionPane.showMessageDialog(null, "该会员删除成功！");
JOptionPane.showMessageDialog(null, "记得点一下查询哦！");
} else {
JOptionPane.showMessageDialog(null, "删除失败！");
}
}
}
}
});
}
public static void main(String[] args) {
new MainJframe().setVisible(true);
}
}
