数据库操作——小型通讯录

数据库操作——小型通讯录
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);
}
    }

库表设计：