使用JDBC完成数据的增删改查

这个例子是在test包下完成的

1.创建User类:创建一个用户信息的实体类

package test;
import java.util.Date;
public class User {
    private int id;
    private String username;
    private String password;
    private String email;
    private Date birthday;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

2.创建工具类（JDBCUtils类）：由于每次操作数据库时都需要加载数据库驱动、建立数据库连接和关闭数据库连接，为了避免重复书写代码，可以建立一个操作数据库的操作类。

package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
    // 加载驱动，并建立数据库连接
    public static Connection getConnection() throws SQLException,
            ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT%2B8";
        String username = "root";
        String password = "12345678";
        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);
    }
}

3.创建UsersDAO类：该类主要用于程序与数据库的交互，该类中封装了对数据库表users的添加、查询、删除和更新等操作。

package test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import test.User;
import test.JDBCUtils;
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;
            }
            return null;
        } 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;
    }
}

4.创建测试类(jdbcInsertTest):实现向users表中添加数据的操作。

package test;

import java.util.Date;
import test.UsersDao;
import test.User;
public class JdbcInsertTest{
    public static void main(String[] args) {
        // 向users表插入一个用户信息
        UsersDao ud = new UsersDao();
        User user=new User();
        user.setId(5);
        user.setUsername("hl");
        user.setPassword("123");
        user.setEmail("zl@sina.com");
        user.setBirthday(new Date());
        boolean b=ud.insert(user);
        System.out.println(b);
    }
}

创建查看全部用户类：

package test;
import java.util.ArrayList;
import test.UsersDao;
import test.User;
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());
        }
    }
}

创建查找指定ID类:该类会将ID为1的User对象的name打印出来；

package test;
import test.UsersDao;
import test.User;
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());
    }
}

创建修改用户数据类：实现对users表中数据修改操作；

package test;
import java.util.Date;
import test.UsersDao;
import test.User;
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("zhaoxiaoliu@sina.com");
        user.setBirthday(new Date());
        boolean b = usersDao.update(user);
        System.out.println(b);
    }
}

创建删除用户类：该类实现了对users表中数据的删除操作。

package test;
import test.UsersDao;
public class DeleteUserTest{
    public static void main(String[] args) {
        // 删除操作
        UsersDao usersDao = new UsersDao();
        boolean b = usersDao.delete(4);
        System.out.println(b);
    }
}

需要用到的jar包和sql的文件:

链接:https://pan.baidu.com/s/1RI7Vw5pHZf1uYzon2AoSDA

提取码:bky6

原文链接：https://blog.csdn.net/qq_56661788/article/details/124181187

你可能也喜欢