JDBC系列流程代码

数据库工具类

public class DBUtil {
    //在mysql8.0版本以上需要在后面加上服务器时区
    public static final String URL = "jdbc:mysql://localhost:3306/数据库名?serverTimezone=UTC";
    public static final String USER_NAME = "用户名";
    public static final String PASSWORD = "密码";

    public static Connection connectionHelper() {
        Connection conn = null;
        try {
            //加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("驱动异常：" + e.getMessage());
        }
        try {
            //获取数据库连接
            conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            return conn;
        } catch (Exception e) {
            System.out.println("获取连接异常：" + e.getMessage());
            return null;
        }
    }
}

查询

public List<Student> queryAll() {
            Connection conn = null;
            String sql = "select * from student";
            //使用PreparedStatement对象可以防止sql注入
            PreparedStatement psmt = null;
            //查询数据返回结果集
            ResultSet rs =null;
            //获取工具类中的连接
            conn = DBUtil.connectionHelper();
            try{
                //执行SQL语句
                psmt = conn.prepareStatement(sql);
                //查询用executeQuery方法，增删改用executeUpdate方法
                rs = psmt.executeQuery();
                //创建List集合存放学生
                List<Student> stuList = new ArrayList<Student>();
                while(rs.next()){
                    //每读取一行数据就创建一个学生并赋值
                    Student student = new Student();
                    student.setStuno(rs.getString("stuId"));
                    student.setStuname(rs.getString("stuName"));
                    student.setStuage(Integer.parseInt(rs.getString("stuAge")));
                    student.setStusex(rs.getString("stuSex"));
                    //把学生添加到集合中
                    stuList.add(student);
                }
                //返回学生集合
                return stuList;
            }
            //捕获异常
            catch (Exception e ){
                System.out.println("异常"+e.getMessage());
                return null;
            }
            //必须释放资源
            finally {
                try{
                    if(conn!=null) conn.close();
                    if(psmt!=null) psmt.close();
                    if(rs!=null) rs.close();
                }
                catch(Exception e){
                    System.out.println("异常"+e.getMessage());
                }
            }
     }

增加

public boolean add(Student student) {
        Connection conn = null;
        PreparedStatement statement = null;
        conn = DBUtil.connectionHelper();
        try{
            String sql = "insert into student values(?,?,?,?)";
            statement = conn.prepareStatement(sql);
            //设置SQL语句中的?参数
            //从第一个问号开始对应
            statement.setString(1,student.getStuId());
            statement.setString(2,student.getStuName());
            statement.setInt(3,student.getStuAge());
            statement.setInt(4,student.getStuSex());
            //查询用executeQuery方法，增删改用executeUpdate方法
            statement.executeUpdate();
            //返回执行成功
            //如果没有执行到这则跳到异常中返回失败
            return true;
        }
        catch(Exception e){
            System.out.println("异常："+e.getMessage());
            return false;
        }
        finally {
            try {
                if (conn!=null) conn.close();
                if (statement!=null) statement.close();
            }
            catch (Exception e){
                System.out.println("异常："+e.getMessage());
            }
        }
    }

删除

public boolean deleteNo(int id) {
        Connection conn = null;
        PreparedStatement statement = null;
        conn = DBUtil.connectionHelper();
        try{
            String sql = "delete  from student where stuId = ?";
            statement = conn.prepareStatement(sql);
            //删除传入的id的学生
            statement.setInt(1,id);
            //查询用executeQuery方法，增删改用executeUpdate方法
            statement.executeUpdate();
            //返回执行成功
            //如果没有执行到这则跳到异常中返回失败
            return true;
        }
        catch(Exception e){
            System.out.println("异常："+e.getMessage());
            return false;
        }
        finally {
            try {
                if (conn!=null) conn.close();
                if (statement!=null) statement.close();
            }
            catch (Exception e){
                System.out.println("异常："+e.getMessage());
            }
        }
    }

修改

public boolean updateNo(Student student) {
        Connection conn = null;
        PreparedStatement statement = null;
        conn = DBUtil.connectionHelper();
        try{
            String sql = "update student set stuName=?,stuAge=?,stuSex=? where stuId = ?";
            statement = conn.prepareStatement(sql);
            //给SQL语句赋值
            statement.setString(1,student.getStuName());
            statement.setInt(2,student.getStuAge());
            statement.setString(3,student.getStuSex());
            statement.setInt(4,student.getStuId());
            //查询用executeQuery方法，增删改用executeUpdate方法
            statement.executeUpdate();
            //返回执行成功
            //如果没有执行到这则跳到异常中返回失败
            return true;
        }
        catch(Exception e){
            System.out.println("异常："+e.getMessage());
            return false;
        }
        finally {
            try {
                if (conn!=null) conn.close();
                if (statement!=null) statement.close();
            }
            catch (Exception e){
                System.out.println("异常："+e.getMessage());
            }
        }
    }