JDBC completes the addition, inspection, deletion and modification of mysql

Create a new example package in the src directory under the project file

Insert picture description here


User:

package example.domain;

import java.util.Date;

//用于保存用户数据的User类
public class User {
    private int id;
    private String username;
    private String password;
    private String email;
    private Date birthday;

    public void setId(int id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public int getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public String getEmail() {
        return email;
    }

    public Date getBirthday() {
        return birthday;
    }
}

JDBCUtils (used to establish a connection with mysql):

package example.utils;

import java.sql.*;

//专门用于数据库相关操作的工具类
public class JDBCUtils {
    //加载驱动,并建立数据库连接
    public static Connection getConnection() throws SQLException, ClassNotFoundException{
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/myjdbc?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
        String username = "root";
        String password = "root";
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }
    //关闭数据库连接,释放资源
    public static void release(Statement stmt, Connection conn){
        if(stmt != null){
            try{
                stmt.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            stmt = null;
        }
        if(conn != null){
            try {
                conn.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            conn = null;
        }
    }

    public static void release(ResultSet rs, Statement stmt, Connection conn){
        if(rs != null){
            try{
                rs.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
            rs = null;
        }
        release(stmt, conn);
    }
}

UsersDao:

package example.dao;

import example.domain.User;
import example.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

//该类封装了对表Users的添加、查询、删除和更新等操作
public class UsersDao {
    //添加用户的操作
    public boolean insert(User user){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //获得数据的连接
            conn = JDBCUtils.getConnection();
            //获得Statement对象
            stmt = conn.createStatement();
            //发送SQL语句
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(user.getBirthday());
            String sql = "insert into users(id, name, password, email, birthday)"
                    + "values("
                    + user.getId() + ",'"
                    + user.getUsername() + "','"
                    + user.getPassword() + "','"
                    + user.getEmail() + "','"
                    + birthday + "')";
            int num = stmt.executeUpdate(sql);
            if(num > 0){
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return false;
    }

    //查询所有的User对象
    public ArrayList<User> findAll(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<User> list = new ArrayList<User>();
        try {
            //获得数据的连接
            conn = JDBCUtils.getConnection();
            //获得Statement对象
            stmt = conn.createStatement();
            //发送SQL语句
            String sql = "select * from users";
            rs = stmt.executeQuery(sql);
            //处理结果集
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setBirthday(rs.getDate("birthday"));
                list.add(user);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return null;
    }

    //根据id查找指定的user
    public User find(int id){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //获得数据的连接
            conn = JDBCUtils.getConnection();
            //获得Statement对象
            stmt = conn.createStatement();
            //发送SQL语句
            String sql = "select * from users where  id = " + id;
            rs = stmt.executeQuery(sql);
            //处理结果集
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setBirthday(rs.getDate("birthday"));
                return user;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return null;
    }

    //删除用户
    public boolean delete(int id){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //获得数据库的连接
            conn = JDBCUtils.getConnection();
            //获得Statement对象
            stmt = conn.createStatement();
            //发送SQL语句
            String sql = "delete from users where id = " + id;
            int num = stmt.executeUpdate(sql);
            if(num > 0){
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return false;
    }

    //修改用户
    public boolean update(User user){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //获得数据的连接
            conn = JDBCUtils.getConnection();
            //获得Statement对象
            stmt = conn.createStatement();
            //发送SQL语句
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(user.getBirthday());
            String sql = "update users set name = '" + user.getUsername()
                    + "', password = '" + user.getPassword()
                    + "', email = '" + user.getEmail()
                    + "', birthday = '" + birthday
                    + "' where id = " + user.getId();
            int num = stmt.executeUpdate(sql);
            if(num > 0){
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(rs, stmt, conn);
        }
        return false;
    }



}

Here are a few test classes:
JdbcInsertTest:

package example;

import example.dao.UsersDao;
import example.domain.User;

import java.util.Date;

//测试类,实现向users表中添加数据的操作
public class JdbcInsertTest {
    public static void main(String[] args) {
        //向users表插入一个用户信息
        UsersDao ud = new UsersDao();
        User user = new User();
        user.setId(5);
        user.setUsername("h1");
        user.setPassword("123");
        user.setEmail("[email protected]");
        user.setBirthday(new Date());
        boolean b = ud.insert(user);
        System.out.println(b);
    }
}

FIndAllUsersTest:

package example;

import example.dao.UsersDao;
import example.domain.User;

import java.util.ArrayList;

//测试类,实现读取users表中所有的数据
public class FIndAllUsersTest {
    public static void main(String[] args) {
        //创建一个名称为usersDao的对象
        UsersDao usersDao = new UsersDao();
        //将UsersDao对象的findAll()方法执行后的结果放入list集合
        ArrayList<User> list = usersDao.findAll();
        //循环输出集合中的数据
        for(int i = 0; i < list.size(); i ++ ){
            System.out.println("第" + (i + 1) + "条数据的username值为:"
            + list.get(i).getUsername());
        }
    }
}

FindUserByIdTest:

package example;

import example.dao.UsersDao;
import example.domain.User;

//测试类,实现读取users表中指定的数据
public class FindUserByIdTest {
    public static void main(String[] args) {
        UsersDao usersDao = new UsersDao();
        User user = usersDao.find(1);
        System.out.println("id为1的User对象的name值为:" + user.getUsername());
    }
}

UpdateUserTest:

package example;

import example.dao.UsersDao;
import example.domain.User;

import java.util.Date;

//测试类,实现修改users表中数据的操作
public class UpdateUserTest {
    public static void main(String[] args) {
        //修改User对象的数据
        UsersDao usersDao = new UsersDao();
        User user = new User();
        user.setId(4);
        user.setUsername("zhaoxiaoliu");
        user.setPassword("456");
        user.setEmail("[email protected]");
        user.setBirthday(new Date());
        boolean b = usersDao.update(user);
        System.out.println(b);
    }
}

DeleteUserTest:

package example;

import example.dao.UsersDao;

//测试类,实现删除users表中数据的操作
public class DeleteUserTest {
    public static void main(String[] args) {
        UsersDao usersDao = new UsersDao();
        boolean b = usersDao.delete(4);
        System.out.println(b);
    }
}