毓秀Java实验五（下）：数据库操作——小型通讯录
实验目的
熟悉数据库开发的基本操作
实验内容
实现数据库的连接，动态增、删、改、查功能
实验要求
1、实现一个小型通讯录
2、能够实现个人通讯录列表的显示，表格显示
3、能够实现通讯录信息的动态增加、修改和删除
4、多个账号注册登录之后，通讯录列表是独立的
提示：
1、主要有User表（用于保存使用者的个人信息）；Communication表（用于保存通讯录列表里的人的信息）；Group表（用于保存通讯录列表中的人的分组信息，如高中同学）
2、使用Statement和PreparedStatement类
实验代码
package experiment_five;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
class LoginFrame extends Frame {
private static String DBDRIVER = "com.mysql.cj.jdbc.Driver" ;
protected static String DBURL = "jdbc:mysql://数据库地址:3306/experiment? usessl=false" ;
protected static String DBUSER = "experiment" ;
protected static String DBPASS = "密码" ;
private static final long serialVersionUID = 1L;
private JFrame jFrame = new JFrame("登录窗口");
private Container c = jFrame.getContentPane();
private JLabel a1 = new JLabel("用户名");
// 账号
private JTextField username = new JTextField();
private JLabel a2 = new JLabel("密 码");
// 密码
private JPasswordField password = new JPasswordField();
private JButton okbtn = new JButton("登录");
private JButton regbtn = new JButton("注册");
public LoginFrame() {
try {
Class.forName(DBDRIVER);
} catch (ClassNotFoundException e2) {
// TODO Auto-generated catch block
System.out.println("加载驱动失败");
//e2.printStackTrace();
}
// 设置窗体的位置及大小
jFrame.setBounds(600, 200, 300, 220);
// 设置一层相当于桌布的东西
c.setLayout(new BorderLayout());// 布局管理器
// 设置按下右上角X号后关闭
jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
// 初始化--往窗体里放其他控件
jFrame.setLocationRelativeTo(null);// 窗
// 标题部分
JPanel titlePanel = new JPanel();
titlePanel.setLayout(new FlowLayout());
titlePanel.add(new JLabel("通讯录系统"));
c.add(titlePanel, "North");
// 输入部分-
JPanel fieldPanel = new JPanel();
fieldPanel.setLayout(null);
a1.setBounds(50, 20, 50, 20);
a2.setBounds(50, 60, 50, 20);
fieldPanel.add(a1);
fieldPanel.add(a2);
username.setBounds(110, 20, 120, 20);
password.setBounds(110, 60, 120, 20);
fieldPanel.add(username);
fieldPanel.add(password);
c.add(fieldPanel, "Center");
// 按钮部分
JPanel buttonPanel = new JPanel();
buttonPanel.setLayout(new FlowLayout());
buttonPanel.add(okbtn);
buttonPanel.add(regbtn);
c.add(buttonPanel, "South");
// 设置窗体可见
jFrame.setVisible(true);
// 确认按下去获取
okbtn.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String name = username.getText();
String pwd = String.valueOf(password.getPassword());
boolean flag = false;
try {
Connection con = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
//Statement stmt = con.createStatement();
//String login = "SELECT account,password FROM user where account = '" + name + "'and password = '" + pwd + "'";
String login = "SELECT account,password FROM user where account = ? and password = ?";
PreparedStatement sql = con.prepareStatement(login);
sql.setString(1, name);
sql.setString(2, pwd);
ResultSet rs = sql.executeQuery();
while (rs.next()) {
if(rs.getString("account").equals(name) && rs.getString("password").equals(pwd))
flag = true;
}
if (flag) {
jFrame.setVisible(false);
JOptionPane.showMessageDialog(null, "恭喜您,登录成功!");
MainMenuFrame m = new MainMenuFrame();
//m.setVisible(true);
// 窗口居中
// m.setLocationRelativeTo(null);
} else {
JOptionPane.showMessageDialog(null, "登录失败，账号或密码错误");
}
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
username.setText("");
password.setText("");
JOptionPane.showMessageDialog(null, "登录失败");
}
}
});
regbtn.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
// try {
String name = username.getText();
String pwd = String.valueOf(password.getPassword());
//String regist = "insert into user(account,password) "+ "values ('" + name + "','" + pwd + "')";
try {
Connection con=DriverManager.getConnection(DBURL,DBUSER,DBPASS);
String regist = "insert into user(account,password) values(?,?)";
PreparedStatement sql = con.prepareStatement(regist);
sql.setString(1, name);
sql.setString(2, pwd);
//int i = sql.executeUpdate();
if(sql.executeUpdate() > 0)
JOptionPane.showMessageDialog(null, "注册成功");
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
System.out.println("注册失败");
}
/* } catch (HeadlessException e1) {
username.setText("");
password.setText("");
JOptionPane.showMessageDialog(null, "注册失败");
}*/
}
});
}
}
/*class UserDao {
//String a = LoginFrame.DBURL;
//private static String DBURL = "jdbc:mysql://数据库地址:3306/experiment? usessl=false" ;
//private static String DBUSER = "experiment" ;
//private static String DBPASS = "密码" ;
public static boolean auth(String username, String password) {
String login = "SELECT account,password FROM user where account = '" + username + "'and password = '" + password + "'";
//Connection con;
try {
Connection con = DriverManager.getConnection(LoginFrame.DBURL,LoginFrame.DBUSER,LoginFrame.DBPASS);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(login);
while (rs.next()) {
if(rs.getString("account").equals(username) && rs.getString("password").equals(password))
return true;
}
rs.close();
stmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
}*/
class MainMenuFrame extends JFrame implements ActionListener{
/*
* 登陆成功后主界面
*/
private JFrame mainmenu;
//private JTextArea text;
private JMenuBar menuBar1;
public MainMenuFrame() {
mainmenu();
}
public void mainmenu() {
mainmenu = new JFrame("通讯录系统");
setLocationRelativeTo(null);// 将容器显示在屏幕中央
mainmenu.setSize(750, 650);
//mainmenu.getContentPane().add(new JScrollPane(text));
JPanel jContentPane = new JPanel();
jContentPane.setLayout(null);
// 添加学生按钮
JButton jButtonAdd = new JButton();
jButtonAdd.setBounds(new Rectangle(320, 100, 100, 52));
jButtonAdd.setText("添加学生");
// 查询学生按钮
JButton jButtonSelect = new JButton();
jButtonSelect.setBounds(new Rectangle(320, 200, 100, 52));
jButtonSelect.setText("查询学生");
//删除学生按钮
JButton jButtonDelete = new JButton();
jButtonDelete.setBounds(new Rectangle(320, 300, 100, 52));
jButtonDelete.setText("删除学生");
// 退出系统按钮
JButton jButtonExit = new JButton();
jButtonExit.setBounds(new Rectangle(320, 400, 100, 52));
jButtonExit.setText("退出系统");
jContentPane.add(jButtonAdd, null);
jContentPane.add(jButtonSelect, null);
jContentPane.add(jButtonDelete, null);
jContentPane.add(jButtonExit, null);
// 添加Label到Frame
mainmenu.getContentPane().add(jContentPane);
// 标题栏
menuBar1 = new JMenuBar();
// 添加学生
jButtonAdd.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
AddStudent addStudent = new AddStudent();
System.out.println("------添加学生-----");
}
});
// 查询学生
jButtonSelect.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
SelectStudent selectStudent = new SelectStudent();
System.out.println("------查询学生-----");
}
});
//删除学生
jButtonDelete.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
DeleteStudent deleteStudent = new DeleteStudent();
}
});
// 退出系统
jButtonExit.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
System.out.println("------退出系统-----");
System.exit(1);
}
});
// 关闭窗口监控
mainmenu.addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent arg0) {
System.exit(1);
}
});
mainmenu.setJMenuBar(menuBar1);
mainmenu.setVisible(true);
mainmenu.setLocation(250, 50);
}
@Override
public void actionPerformed(ActionEvent arg0) {
}
}
class AddStudent extends JFrame{
//private static String DBURL = "jdbc:mysql://数据库地址:3306/experiment? usessl=false" ;
//private static String DBUSER = "experiment" ;
//private static String DBPASS = "密码" ;
JTextField text,text2,text3;
JButton button;
JCheckBox checkBox1,checkBox2,checkBox3;
JRadioButton radio1,radio2;
ButtonGroup group;
JComboBox<String> comBox;
//JTextArea area;
public AddStudent(){
init();
setVisible(true);
setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
setBounds(100,100,400,240);
setTitle("添加页面");
}
void init() {
// TODO Auto-generated method stub
setLayout(new FlowLayout());
JLabel label = new JLabel("姓名：");
add(label);
text = new JTextField(10);
add(text);
JLabel label1 = new JLabel("电话：");
add(label1);
text3 = new JTextField(10);
add(text3);
radio1 = new JRadioButton("男");//单选按钮
radio2 = new JRadioButton("女");
group = new ButtonGroup();
group.add(radio1);
group.add(radio2);
add(radio1);
add(radio2);
checkBox1 = new JCheckBox("喜欢音乐");
checkBox2 = new JCheckBox("喜欢旅游");
checkBox3 = new JCheckBox("喜欢篮球");
add(checkBox1);
add(checkBox2);
add(checkBox3);
comBox = new JComboBox();//下拉列表
comBox.addItem("居住城市");
comBox.addItem("北京");
comBox.addItem("上海");
comBox.addItem("成都");
comBox.addItem("长春");
add(comBox);
JSlider slider = new JSlider(5, 25, 18);
slider.setMajorTickSpacing(5);
slider.setMinorTickSpacing(1);
slider.setPaintTicks(true);
slider.setPaintLabels(true);
add(slider);
slider.addChangeListener(new ChangeListener() {
@Override
public void stateChanged(ChangeEvent e) {
text2.setText(Integer.toString(slider.getValue()));
}
});
text2 = new JTextField("年龄",4);
add(text2);
text2.setEditable(false);
//area = new JTextArea(6,12);
//add(new JScrollPane(area));
button = new JButton("确定");
add(button);
button.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String[] allof = new String[6];
String name = text.getText();
allof[0] = name;
String phone = text3.getText();
allof[5] = phone;//后添加
String city = comBox.getSelectedItem().toString();
allof[1] = city;
String sex = null;
if (radio1.isSelected())
sex = radio1.getText();
else
sex = radio2.getText();
allof[2] = sex;
String like ="";
if(checkBox1.isSelected())
like = checkBox1.getText();
if(checkBox2.isSelected())
like = checkBox2.getText()+" "+like;
if(checkBox3.isSelected())
like = checkBox3.getText()+" "+like;
allof[3] = like;
String age = Integer.toString(slider.getValue());
allof[4] = age;
for(int i = 0;i<=5;i++){
System.out.println(allof[i]);
}
//String addstudent = "insert into communication(name,sex,age,phone,city,favorite) "
//+ "values ('" + name + "','" + sex + "','" + age + "','" + phone + "','" + city + "','" + like + "')";
try {
Connection con = DriverManager.getConnection(LoginFrame.DBURL,LoginFrame.DBUSER,LoginFrame.DBPASS);
//Statement stmt = con.createStatement();
String addstudent = "insert into communication(name,sex,age,phone,city,favorite) values (?,?,?,?,?,?)";
PreparedStatement sql = con.prepareStatement(addstudent);
sql.setString(1, name);
sql.setString(2, sex);
sql.setString(3, age);
sql.setString(4, phone);
sql.setString(5, city);
sql.setString(6, like);
//int i = sql.executeUpdate();
if(sql.executeUpdate() > 0)
JOptionPane.showMessageDialog(null, "添加成功");
sql.close();
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "添加失败");
}
System.out.println("------添加学生-----");
}
});
}
}
class SelectStudent extends JFrame implements ActionListener{
//private static String DBURL = "jdbc:mysql://数据库地址:3306/experiment? usessl=false" ;
//private static String DBUSER = "experiment" ;
//private static String DBPASS = "密码" ;
JTextField text;
DefaultTableModel model= new DefaultTableModel();
JButton button;
public SelectStudent() {
setTitle("查询窗口");
setVisible(true);
setBounds(100,100,460,260);
setLayout(new FlowLayout());
JLabel label = new JLabel("姓名:");
add(label);
text = new JTextField(10);
add(text);
button = new JButton("确认");
add(button);
JButton button1 = new JButton("查询所有");
add(button1);
button.addActionListener(this);
button1.addActionListener(this);
//model = new DefaultTableModel();
JTable table = new JTable(model);
add(new JScrollPane(table));
}
public void actionPerformed(ActionEvent e) {
try {
String name = text.getText();
String sql = null;
Connection con = DriverManager.getConnection(LoginFrame.DBURL,LoginFrame.DBUSER,LoginFrame.DBPASS);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
if(e.getSource()== button) {
sql = "select * from communication where name ='"+name+"'";
}else {
sql = "select * from communication";
}
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
String[] columnName = new String[columnCount];
for(int i = 1;i<=columnCount;i++) {
columnName[i-1] = metaData.getColumnName(i);
}
rs.last();
int recordAmount = rs.getRow();
String[][] record = new String[recordAmount][columnCount];
int i = 0;
rs.beforeFirst();
while(rs.next()) {
for(int j = 1;j<=columnCount;j++) {
record[i][j-1] = rs.getString(j);
}
i++;
}
if(recordAmount>0) {
JOptionPane.showMessageDialog(null, "查询成功");
}else
JOptionPane.showMessageDialog(null, "没有此联系人");
model.setDataVector(record, columnName);
stmt.close();
con.close();
//JTable table = new JTable(record,columnName);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "查询失败");
}
}
}
class DeleteStudent extends JFrame{
//private static String DBURL = "jdbc:mysql://数据库地址:3306/experiment? usessl=false" ;
//private static String DBUSER = "experiment" ;
//private static String DBPASS = "密码" ;
public DeleteStudent(){
//JFrame DeleteWin = new JFrame("删除窗口");
setTitle("删除窗口");
setVisible(true);
setBounds(100,100,300,260);
setLayout(new FlowLayout());
JLabel label = new JLabel("姓名:");
add(label);
JTextField text = new JTextField(10);
add(text);
JButton button = new JButton("确认");
add(button);
button.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e){
String name = text.getText();
Connection con;
try {
con = DriverManager.getConnection(LoginFrame.DBURL,LoginFrame.DBUSER,LoginFrame.DBPASS);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
String sql = "delete from communication where name = '" + name + "'";
int rs = stmt.executeUpdate(sql);
if(rs > 0)
JOptionPane.showMessageDialog(null, "删除成功");
else
JOptionPane.showMessageDialog(null, "删除失败");
setVisible(false);
stmt.close();
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
});
}
}
public class project5 {
public static void main(String[] args) {
LoginFrame loginFrame = new LoginFrame();
}
}
数据库表结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for Customer
-- ----------------------------
DROP TABLE IF EXISTS `Customer`;
CREATE TABLE `Customer` (
`ID` varchar(10) NOT NULL,
`Name` varchar(255) DEFAULT NULL,
`Age` int(4) DEFAULT NULL,
`Gender` varchar(255) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for communication
-- ----------------------------
DROP TABLE IF EXISTS `communication`;
CREATE TABLE `communication` (
`name` varchar(15) NOT NULL COMMENT '姓名',
`sex` varchar(4) DEFAULT NULL COMMENT '性别',
`age` int(3) DEFAULT NULL COMMENT '年龄',
`phone` varchar(11) DEFAULT NULL COMMENT '电话',
`city` varchar(16) DEFAULT NULL COMMENT '居住城市',
`favorite` varchar(50) DEFAULT NULL COMMENT '爱好',
PRIMARY KEY (`name`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for group
-- ----------------------------
DROP TABLE IF EXISTS `group`;
CREATE TABLE `group` (
`name` varchar(16) NOT NULL,
`grouping` varchar(30) NOT NULL COMMENT '分组信息',
PRIMARY KEY (`name`,`grouping`) USING BTREE,
CONSTRAINT `name_foreign` FOREIGN KEY (`name`) REFERENCES `communication` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`account` varchar(20) NOT NULL COMMENT '姓名',
`password` varchar(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`account`,`password`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = 1;
1.腾龙梦屋文章内容无特殊注明皆为源儿原创，转载请注明来源，谢谢！
2.若有相关文章侵犯您的权益，请联系源儿删除，谢谢！
3.相关软件、资料仅供学习参考使用，在24h内务必删除！
腾龙梦屋 » Java实验五（下）：数据库操作——小型通讯录
2.若有相关文章侵犯您的权益，请联系源儿删除，谢谢！
3.相关软件、资料仅供学习参考使用，在24h内务必删除！
腾龙梦屋 » Java实验五（下）：数据库操作——小型通讯录
加速支持
膜拜学长
哈哈