小型电话薄管理系统（Mysql数据库）

  学习完Java的基础内容与Mysql相关内容，用一个小型的电话薄管理系统来检验巩固一下所学的知识。
  
 
  首先分析一下电话薄管理系统，电话薄内存储了用户的姓名，电话号码，地址等信息，向数据库中写入或者操作这些数据就要先在数据库中建立一个自己要用的库和建相应的表来存储信息，建完数据库和表后就可以通过Java程序连接数据库进而来完成对数据库的相关操作（增删改查）

  我将小型的电话薄管理系统分为了3部分，分别为数据处理部分（DBoperater），用户类（User）以及用户操作部分（Main）
  1.DBoperation用来完成Java语句对数据库的连接以及数据往数据库进行增删改查的操作
  2.User是用来辅助完成之后信息处理的，建立这个User类就是为了让DBoperation有更好的封装性。试想，如果每次要插入一组用户数据，都要在对应方法中修改相对用的sql语句，显得很麻烦。
  3.Main类中是用户进行操作的，在这个类中，用户可以通过new对象来调用对应的方法进行对数据库的操作
 
 
 
  

1.DBoperation类

package com.school.DBOperater;


import com.school.User.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBoperater {
    private static String URL =
            "jdbc:mysql://localhost/telephone_system";
    private static String USER = "root";
    private static String PASSWORD = "";

    //获取数据源
    public static DataSource getDataSource() {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL(URL);
        dataSource.setUser(USER);
        dataSource.setPassword(PASSWORD);

        return dataSource;
    }

    //1.添加用户操作
    public static void insertintotable(User user) {
        //1.获取数据源，初始化
        Connection connection = null;
        PreparedStatement pstmt = null;


        try {
            //获取连接
            connection = getDataSource().getConnection();

            String sql = "insert into telelist values(?,?,?,?,?)";
            pstmt = connection.prepareStatement(sql);
            
            pstmt.setString(1, user.getTelenumber());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getAddress());
            pstmt.setInt(4, user.getCode());
            pstmt.setString(5, user.getEmail());
            pstmt.execute();     //执行


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //资源关闭
            try {
                if (pstmt != null) {
                    pstmt.close();
                }

                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    //2.查询所有用户操作
    public static List selectalluser(){
        //1.获取数据源
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;       //结果集

        List<User> list=new ArrayList<>();

        try {
            connection=getDataSource().getConnection();
            String sql="select * from telelist";
            pstmt=connection.prepareStatement(sql);

            rs=pstmt.executeQuery();    //查询

            while(rs.next()){
                User user=new User();
                user.setTelenumber(rs.getString("telenumber"));
                user.setName(rs.getString("name"));
                user.setAddress(rs.getString("address"));
                user.setCode(rs.getInt("code"));
                user.setEmail(rs.getString("email"));
                list.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭资源
            try {
                if(pstmt != null){
                    pstmt.close();
                }

                if(connection !=  null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }


    //3.按姓名查询
    public static User selectuser(String name){
        //1.获取资源
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        User user=new User();

        try {
            connection=getDataSource().getConnection();

            String sql="select name,address,telenumber,code from telelist where name=?";
            pstmt=connection.prepareStatement(sql);

            pstmt.setString(1,name);

            rs=pstmt.executeQuery();

            while(rs.next()){
                user.setName(rs.getString("name"));
                user.setAddress(rs.getString("address"));
                user.setTelenumber(rs.getString("telenumber"));
                user.setCode(rs.getInt("code"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭资源
            try {
                if(pstmt != null) {
                    pstmt.close();
                }

                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }
    

    //4.更新表中字段
    public static void update(User user,String name){
        Connection connection=null;
        PreparedStatement pstmt=null;

        try {
            connection=getDataSource().getConnection();

            String sql="update telelist set telenumber=?,address=?,code=?,email=?  where name=?";
            pstmt=connection.prepareStatement(sql);

            pstmt.setString(1, user.getTelenumber());
            pstmt.setString(2, user.getAddress());
            pstmt.setInt(3, user.getCode());
            pstmt.setString(4, user.getEmail());
            pstmt.setString(5,name);
            pstmt.execute();     //执行
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                if(pstmt != null){
                    pstmt.close();
                }

                if(connection !=  null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //5.按姓名排序
    public static List oroupbyname(){
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;

        List<User> list=new ArrayList<>();


        try {
            connection=getDataSource().getConnection();

            String sql="select * from telelist order by name";
            pstmt=connection.prepareStatement(sql);

            rs = pstmt.executeQuery();

            while (rs.next()){
                User user=new User();
                user.setTelenumber(rs.getString("telenumber"));
                user.setName(rs.getString("name"));
                user.setAddress(rs.getString("address"));
                user.setCode(rs.getInt("code"));
                user.setEmail(rs.getString("email"));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                if(pstmt != null){
                    pstmt.close();
                }

                if(connection !=  null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }


    //6.按邮箱排序
    public static List oroupbyemail(){
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;

        List<User> list=new ArrayList<>();


        try {
            connection=getDataSource().getConnection();

            String sql="select * from telelist order by email";
            pstmt=connection.prepareStatement(sql);

            rs = pstmt.executeQuery();

            while (rs.next()){
                User user=new User();
                user.setTelenumber(rs.getString("telenumber"));
                user.setName(rs.getString("name"));
                user.setAddress(rs.getString("address"));
                user.setCode(rs.getInt("code"));
                user.setEmail(rs.getString("email"));
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                if(pstmt != null){
                    pstmt.close();
                }

                if(connection !=  null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}

 
 
2.User类

package com.school.User;


public class User {
    private String telenumber;
    private String name;
    private String address;
    private int code;
    private String email;

    public String getTelenumber() {
        return telenumber;
    }

    public void setTelenumber(String telenumber) {
        this.telenumber = telenumber;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        if(email==null){
          return   "name:"+name+"   "+"telenumber:"+telenumber+"   "+"address:"+address+"   "+"code:"+code+"   "+"\n";
        }
        return "name:"+name+"   "+"telenumber:"+telenumber+"   "+"address:"+address+"   "+"code:"+code+"   "
                +"email:"+email+"\n";
    }
}

 
 

3.Main类

package com.school.Main;

import com.school.DBOperater.DBoperater;
import com.school.User.User;

import java.util.List;

public class Main {
    public static void main(String[] args) {
        //1.插入用户
       /* User user=new User();
        user.setTelenumber("18918982873");
        user.setName("天天");
        user.setAddress("海口");
        user.setCode(710099);
        user.setEmail("3241342341@qq.com");
        DBoperater.insertintotable(user);*/


        //2.查询所有用户a
       /* List userlist=DBoperater.selectalluser();
        System.out.println(userlist);*/


       //3.按要求查询用户
        /*  User user=DBoperater.selectuser("豆豆");
          System.out.println(user);
*/

        //4.排序
       /* List user1=DBoperater.oroupbyemail();
        System.out.println(user1);*/

       //5.更新表
       /* User user=new User();
        user.setTelenumber("12783746720");
        user.setAddress("巴黎");
        user.setCode(745342);
        user.setEmail("12435625162@qq.com");
        DBoperater.update(user,"墩墩");*/
    }
}

数据库建表语句

create table telelist(
  telenumber bigint,
  name varchar(20) primary key ,
  address varchar(30),
  code int,
  email varchar(20)
);

 
 
 
插入一个用户：
在Main主函数中写入以下代码：

        User user=new User();
        user.setTelenumber("18918982873");
        user.setName("蜜蜜");
        user.setAddress("海南");
        user.setCode(710012);
        user.setEmail("3241111341@qq.com");
        DBoperater.insertintotable(user);

可以发现用户已经成功写入数据库中
 
 
 
查询用户：

      //2.查询所有用户
        List userlist=DBoperater.selectalluser();
        System.out.println(userlist);

显示出了所有用户信息