数据库操作——小型通讯录
1、实现一个小型通讯录
2、能够实现个人通讯录列表的显示,表格显示
3、能够实现通讯录信息的动态增加、修改和删除
4、多个账号注册登录之后,通讯录列表是独立的
实现数据库的连接,动态增、删、改、查功能
连接mysql 新建数据库code5 创建三张表 communiation(存储联系人信息) group(存储组id对用的分组名) user (记录当账号的信息)
最终效果
要有
与数据库交互的方法封装成一个工具类
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
public class SqlUtils {
public static Connection getCon() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/code5", "root", "1234");
return con;
}
public static Statement getSta() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/code5", "root", "1234");
return con.createStatement();
}
public static void closeCon(Statement statement, Connection connection) throws SQLException {
connection.close(); statement.close();
}
public static int getNum(int len) {
Random random = new Random();
StringBuilder str = new StringBuilder();
for (int i = 0; i < len; i++) {
str.append(random.nextInt(9));
}
return Integer.parseInt(str.toString());
}
public static Res<User> getUserById(int id) throws SQLException, ClassNotFoundException {
Connection con = getCon();
String sql = "select * from user where uid=?";
PreparedStatement prepareStatement = con.prepareStatement(sql);
prepareStatement.setInt(1, id);
ResultSet resultset = prepareStatement.executeQuery();
Res<User> res = new Res<>();
if (resultset.next()) {
res.setState(true);
} else {
res.setState(false);
res.setData(null);
return res;
}
User user = new User();
user.setName(resultset.getString(1));
user.setUid(resultset.getInt(2));
user.setPasswd(resultset.getString(3));
getCon();
res.setData(user);
return res;
}
public static Res<User> addUserById(User user) throws SQLException, ClassNotFoundException {
Connection con = getCon();
String sql = "insert into user(name,uid,passwd) values(?,?,?)";
PreparedStatement prepareStatement = con.prepareStatement(sql);
prepareStatement.setString(1, user.getName());
prepareStatement.setInt(2, getNum(6));
prepareStatement.setString(3, user.getPasswd());
int i = prepareStatement.executeUpdate();
con.close();
prepareStatement.close();
if (i > 0) {
return new Res<>(true, user);
} else {
return new Res<>(false, null);
}
}
public static Res delUserById(int id) throws SQLException, ClassNotFoundException {
Connection con = getCon();
String sql = "delete from user where uid=?";
PreparedStatement preparedStatement = con.prepareStatement(sql);
preparedStatement.setInt(1, id);
int i = preparedStatement.executeUpdate();
con.close();
preparedStatement.close();
if (i > 0) {
return new Res(true, null);
} else {
return new Res(false, null);
}
}
public static Res addCom(Communication communication, User user) throws SQLException, ClassNotFoundException {
Connection con = getCon();
String sql = "insert into communication(id,name,telnum,uid,groupid) values(?,?,?,?,?)";
PreparedStatement preparedstatement = con.prepareStatement(sql);
preparedstatement.setInt(1, getNum(6));
preparedstatement.setString(2, communication.getName());
preparedstatement.setInt(3, communication.getTelnum());
preparedstatement.setInt(4, user.getUid());
preparedstatement.setInt(5, communication.getGroupid());
int i = preparedstatement.executeUpdate();
if (i > 0) {
return new Res<>(true, communication);
} else {
return new Res(false, null);
}
}
public static List getAllByUid(int uid) throws SQLException, ClassNotFoundException {
Connection con = getCon();
String sql = "SELECT u.`name`,u.uid ,c.id,c.`name` comname,c.telnum ,c.groupid from `user`as u,`communication`as" +
" c WHERE u.uid=c.uid and c.uid=" + uid;
Statement statement = con.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
ArrayList<Total> list = new ArrayList<>();
while (resultSet.next()) {
Total total = null;
total = new Total();
total.setUname(resultSet.getString(1));
total.setUid(resultSet.getInt(2));
total.setCid(resultSet.getInt(3));
total.setComname(resultSet.getString(4));
total.setTelnum(resultSet.getInt(5));
total.setGid(resultSet.getInt(6));
list.add(total);
}
System.out.println(list);
return list;
}
public static int getGidByName(String gname) throws SQLException, ClassNotFoundException {
Connection con = getCon();
String sql="SELECT gid from `group` where gname="+"'"+gname+"'";
System.out.println(sql);
Statement statement = con.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
resultSet.next();
return resultSet.getInt(1);
}
public static int delByCid(int uid,int cid) throws SQLException {
Connection con=null;
try {
con= getCon();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql="DELETE from communication where id=? and uid=?";
PreparedStatement prepareStatement = con.prepareStatement(sql);
prepareStatement.setInt(1,cid);
prepareStatement.setInt(2,uid);
return prepareStatement.executeUpdate();
}
public static String getGnameByGid(int gid) throws SQLException, ClassNotFoundException {
Connection con = getCon();
String sql="SELECT gname from `group` where gid="+gid;
System.out.println(sql);
Statement statement = con.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
resultSet.next();
return resultSet.getString(1);
}
}
public class Communication {
private Integer id;
private String name;
private Integer telnum;
private Integer uid;
private Integer groupid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getTelnum() {
return telnum;
}
public void setTelnum(Integer telnum) {
this.telnum = telnum;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Integer getGroupid() {
return groupid;
}
public void setGroupid(Integer groupid) {
this.groupid = groupid;
}
@Override
public String toString() {
return "Communication{" +
"id=" + id +
", name='" + name + '\'' +
", telnum=" + telnum +
", uid=" + uid +
", groupid=" + groupid +
'}';
}
public Communication(Integer id, String name, Integer telnum, Integer uid, Integer groupid) {
this.id = id;
this.name = name;
this.telnum = telnum;
this.uid = uid;
this.groupid = groupid;
}
public Communication() {
}
}
public class Res<T> {
Boolean state;
T data;
public Res(Boolean state, T data) {
this.state = state;
this.data = data;
}
public Res() {
}
public Boolean getState() {
return state;
}
public void setState(Boolean state) {
this.state = state;
}
public T getData() {
return data;
}
public void setData(T data) {
this.data = data;
}
@Override
public String toString() {
return "Res{" +
"state=" + state +
", data=" + data +
'}';
}
}
public class Total {
String uname;
Integer uid;
Integer cid;
String cname;
String comname;
Integer telnum;
public Integer getGid() {
return gid;
}
public void setGid(Integer gid) {
this.gid = gid;
}
Integer gid;
public Total() {
}
public Total(String uname, Integer uid, Integer cid, String cname, String comname, Integer telnum, Integer gid) {
this.uname = uname;
this.uid = uid;
this.cid = cid;
this.cname = cname;
this.comname = comname;
this.telnum = telnum;
this.gid = gid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public String getComname() {
return comname;
}
public void setComname(String comname) {
this.comname = comname;
}
public Integer getTelnum() {
return telnum;
}
public void setTelnum(Integer telnum) {
this.telnum = telnum;
}
@Override
public String toString() {
return "Total{" +
"uname='" + uname + '\'' +
", uid=" + uid +
", cid=" + cid +
", cname='" + cname + '\'' +
", comname='" + comname + '\'' +
", telnum=" + telnum +
", gid=" + gid +
'}';
}
}
public class User {
private Integer uid;
private String name;
private String passwd;
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public User(Integer uid, String name, String passwd) {
this.uid = uid;
this.name = name;
this.passwd = passwd;
}
public User() {
}
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", name='" + name + '\'' +
", passwd='" + passwd + '\'' +
'}';
}
}
登录页面:
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
public class login {
public void loginUi(){
JFrame frame = new JFrame();
//设置窗体对象的属性值
frame.setTitle("Login");//设置窗体标题
frame.setSize(400, 250);//设置窗体大小,只对顶层容器生效
frame.setDefaultCloseOperation(3);//设置窗体关闭操作,3表示关闭窗体退出程序
frame.setLocationRelativeTo(null);//设置窗体相对于另一组间的居中位置,参数null表示窗体相对于屏幕的中央位置
frame.setResizable(false);//禁止调整窗体大小
frame.setFont(new Font("宋体",Font.PLAIN,14));//设置字体,显示格式正常,大小
FlowLayout fl = new FlowLayout(FlowLayout.CENTER,10,10);
//实例化流式布局类的对象
frame.setLayout(fl);
//实例化JLabel标签对象,该对象显示“账号”
JLabel labname = new JLabel("账号id:");
labname.setFont(new Font("宋体",Font.PLAIN,14));
frame.add(labname);
JTextField text_name = new JTextField();
Dimension dim1 = new Dimension(300,30);
text_name.setPreferredSize(dim1);//设置除顶级容器组件以外其他组件的大小
frame.add(text_name);
JLabel labpass = new JLabel("密码:");
labpass.setFont(new Font("宋体",Font.PLAIN,14));
frame.add(labpass);
JPasswordField text_password = new JPasswordField();
//设置大小
text_password.setPreferredSize(dim1);
frame.add(text_password);
JButton button1 = new JButton();
JButton button2 = new JButton("注册");
Dimension dim2 = new Dimension(100,30);
button1.setText("登录");
button1.setFont(new Font("宋体",Font.PLAIN,14));
button2.setFont(new Font("宋体",Font.PLAIN,14));
button1.setSize(dim2);
button2.setSize(dim2);
button2.addActionListener(
new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
}
}
);
frame.add(button1);
frame.add(button2);
frame.setVisible(true);
CListener listener = new CListener(frame, text_name, text_password);
button1.addActionListener(listener);
}
}
登录监听:
import code5.Utlis.SqlUtils;
import code5.entity.Res;
import code5.entity.User;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
public class CListener implements ActionListener {
JFrame login;
JTextField text_name;
JPasswordField text_passwd;
public CListener() {
}
public CListener(JFrame login, JTextField text_name, JPasswordField text_passwd) {
this.login = login;
this.text_name = text_name;
this.text_passwd = text_passwd;
}
@Override
public void actionPerformed(ActionEvent e) {
Dimension dim3 = new Dimension(300,30);
JFrame login2 = new JFrame();
login2.setSize(400,200);
login2.setDefaultCloseOperation(3);
login2.setLocationRelativeTo(null);
login2.setFont(new Font("宋体",Font.PLAIN,14)); //宋体,正常风格,14号字体
JPanel jp1 = new JPanel();
JPanel jp2 = new JPanel();
Res<User> userById=null;
System.out.println("所需"+userById);
try {
userById=SqlUtils.getUserById(Integer.parseInt(text_name.getText()));
System.out.println(userById);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException classNotFoundException) {
classNotFoundException.printStackTrace();
}
String passwd = userById.getData().getPasswd();
if(text_passwd.getText().equals(passwd)) {
JLabel message = new JLabel("登陆成功!");
message.setFont(new Font("宋体", Font.PLAIN, 14)); //宋体,正常风格,14号字体
message.setPreferredSize(dim3);
jp1.add(message);
login2.add(jp1, BorderLayout.CENTER);
login2.setResizable(false);
login2.setVisible(true);
login.dispose();
new Show().ui(userById.getData().getUid());
}
else {
JLabel message = new JLabel("账号或密码错误");
message.setFont(new Font("宋体",Font.PLAIN,14));
message.setPreferredSize(dim3);
jp1.add(message);
login2.add(jp1,BorderLayout.CENTER);
JButton close = new JButton("确定");
close.setFont(new Font("宋体",Font.PLAIN,14));
close.setSize(dim3);
jp2.add(close);
login2.add(jp2,BorderLayout.SOUTH);
close.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
login2.dispose();
}
});
login2.setResizable(false);
login2.setVisible(true);
login.dispose();
}
}
}
菜单展示
import code5.ui.friend.DelFriend;
import code5.ui.friend.SelectFriend;
import code5.ui.friend.AddFriend;
import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
public class Show {
public void ui(int uid) {
JFrame jframe = new JFrame("个人主页");
jframe.setBounds(300, 180, 650, 500);
JPanel jPanel = new JPanel();
JMenuBar jmenuBar=new JMenuBar();
JMenu sf = new JMenu("查询好友");
JMenu af = new JMenu("添加好友");
JMenu df = new JMenu("删除好友");
JMenuItem d1 = new JMenuItem("查询");
JMenuItem d2 = new JMenuItem("添加");
JMenuItem d3 = new JMenuItem("删除");
d1.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
try {
new SelectFriend().comTable(uid);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException classNotFoundException) {
classNotFoundException.printStackTrace();
}
}
});
d2.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
new AddFriend().add(uid);
}
});
d3.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
new DelFriend().del(uid);
}
});
af.add(d2);
df.add(d3);
sf.add(d1);
jmenuBar.add(sf);
jmenuBar.add(af);
jmenuBar.add(df);
jPanel.add(jmenuBar);
jframe.add(jPanel);
jframe.setVisible(true);
jframe.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
}
业务功能
添加
package code5.ui.friend;
import code5.Utlis.SqlUtils;
import code5.entity.Communication;
import code5.entity.Res;
import code5.entity.Total;
import code5.entity.User;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
public class AddFriend {
public static void add(int id){
JFrame f = new JFrame("添加好友");
JPanel jPanel = new JPanel();
JTextField textfield = new JTextField();
JTextField ttel = new JTextField();
Dimension dimension = new Dimension(100, 30);
JLabel lname= new JLabel("好友姓名");
JLabel ltel= new JLabel("电话号码");
JLabel lgroup= new JLabel("选择分组");
lname.setFont(new Font("宋体",Font.PLAIN,14));
ltel.setFont(new Font("宋体",Font.PLAIN,14));
lgroup.setFont(new Font("宋体",Font.PLAIN,14));
ttel.setPreferredSize(dimension);
textfield.setPreferredSize(dimension);
JButton jbutton = new JButton("添加");
JComboBox<String> box = new JComboBox<>();
box.addItem("亲属");
box.addItem("同事");
box.addItem("朋友");
box.addItem("无");
jbutton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String name=textfield.getText();
int telnum= Integer.parseInt(ttel.getText());
System.out.println(name);
System.out.println(telnum);
System.out.println();
int gid=4;
try {
gid = SqlUtils.getGidByName(box.getSelectedItem().toString());
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException classNotFoundException) {
classNotFoundException.printStackTrace();
}
Res<User> data=null;
try {
data= SqlUtils.getUserById(id);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException classNotFoundException) {
classNotFoundException.printStackTrace();
}
User user = new User(data.getData().getUid(),data.getData().getName(),null);
Communication communication = new Communication(null,name,telnum,data.getData().getUid(),gid);
try {
SqlUtils.addCom(communication,user);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException classNotFoundException) {
classNotFoundException.printStackTrace();
}
}
});
jPanel.add(lname);
jPanel.add(textfield);
jPanel.add(ltel);
jPanel.add(ttel);
jPanel.add(lgroup);
jPanel.add(box);
jPanel.add(jbutton);
f.add(jPanel);
f.setSize(600,200);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f.setVisible(true);
}
}
删除
import code5.Utlis.SqlUtils;
import code5.entity.Communication;
import code5.entity.Res;
import code5.entity.User;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
public class DelFriend {
public static void del(int id){
JFrame f = new JFrame("删除联系人");
JPanel jPanel = new JPanel();
JLabel lid= new JLabel("联系人id");
lid.setFont(new Font("宋体",Font.PLAIN,14));
JTextField tid = new JTextField();
Dimension dimension = new Dimension(100, 30);
tid.setPreferredSize(dimension);
JButton jbutton = new JButton("删除");
jbutton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
int uid= Integer.parseInt(tid.getText());
int i = 0;
try {
i = SqlUtils.delByCid(id, uid);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
if(i>0){
System.out.println("删除成功");
}
else {
System.out.println("删除失败");
}
}
});
jPanel.add(lid);
jPanel.add(tid);
jPanel.add(jbutton);
f.add(jPanel);
f.setSize(600,200);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f.setVisible(true);
}
public static void main(String[] args) {
new DelFriend().del(1);
}
}
查询
import code5.Utlis.SqlUtils;
import code5.entity.Res;
import code5.entity.Total;
import javax.swing.*;
import java.awt.*;
import java.sql.SQLException;
import java.util.List;
public class SelectFriend {
public static void comTable(int uid) throws SQLException, ClassNotFoundException {
JFrame f = new JFrame("我的联系人");
f.setSize(400, 300);
f.setLocation(200, 200);
f.setLayout(new BorderLayout());
String[] th = new String[]{ "分组", "联系人姓名", "电话号码","编号"};
List allByGid = SqlUtils.getAllByUid(uid);
String[][] td=new String[allByGid.size()][];
for (int i=0;i<td.length;i++){
td[i]=new String[4];
Total total = (Total) allByGid.get(i);
String gname = SqlUtils.getGnameByGid(total.getGid());
td[i][0]=gname;
td[i][1]=total.getComname();
td[i][2]=total.getTelnum().toString();
td[i][3]=total.getCid().toString();
}
JTable t = new JTable(td, th);
JScrollPane sp = new JScrollPane(t);
f.add(sp, BorderLayout.CENTER);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f.setVisible(true);
}
}
库表设计: