JDBC操作数据库实现增删改查

写在文章的开头，需要导入jdbc的jar包，到Maven的中央仓库搜索mysql/j就可以

需要操作的数据库表

表名为student，表的属性为

一、首先根据student表创建Student实体类

import java.util.Date;

/**
 * @author: changqing
 * @date: 2021/3/6 12:41
 */
public class Student {
    private Long id;
    private String name;
    private Character sex;
    private Date birthday;

    public Student() {
    }

    public Student(Long id, String name, Character sex, Date birthday) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Character getSex() {
        return sex;
    }

    public void setSex(Character sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex=" + sex +
                ", birthday=" + birthday +
                '}';
    }
}

二、封装一个JDBC的工具类，用来获取连接对象和关闭资源

import java.sql.*;

/**
 * @author: changqing
 * @date: 2021/3/6 13:43
 */
public final class JDBCUtil {

    private JDBCUtil() {
    }

    private static final String url = "jdbc:mysql://localhost:3306/jdbctest";
    private static final String user = "root";
    private static final String password = "123456";


    //注册驱动
      static {
          try {
              Class.forName("com.mysql.cj.jdbc.Driver");
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
          }
      }

    /**
     * 获取数据连接对象
     * @return 数据库连接对象
     */
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;

    }


    public static void closeAll(ResultSet set, Statement statement,Connection connection){

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

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

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

    }


}

三、根据Student实体类创建StudentDao数据访问对象类，封装增删改查方法

import org.jgs2010entity.Student;
import org.util.JDBCUtil;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: changqing
 * @date: 2021/3/6 14:07
 */
public class StudentDao {

    /**
     *插入一个学生
     * @param student
     * @return 影响的行数
     */
    public int insert(Student student){

        String sql = "insert into student(`name`,sex,birthday) value ('"+
                     student.getName()+"','"+student.getSex()+"','"+
                     new SimpleDateFormat("yyyy-MM-dd").format(student.getBirthday())+"')";
        //注册驱动获取数据库连接对象
        Connection connection = JDBCUtil.getConnection();
        Statement statement = null;
        int i = 0;
        try {
            //获取语句执行者
            statement = connection.createStatement();
            i = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        JDBCUtil.closeAll(null,statement,connection);

        return i;
    }


    /**
     * 修改一名学生
     * @param student
     * @return 影响的行数
     */
    public int update(Student student){

        Connection connection = JDBCUtil.getConnection();
        String sql = "update student set name = '"+student.getName()+
                    "',birthday = '"+
                    new SimpleDateFormat("yyyy-MM-dd").format(student.getBirthday())
                    +"' where id = "+student.getId();

        Statement statement = null;
        int i = 0;
        try {
            statement = connection.createStatement();
            i = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return i;
    }


    /**
     * 删除一个学生
     * @param student
     * @return 影响的行数
     */
    public int delete(Student student){
        //获取连接对象
        Connection connection = JDBCUtil.getConnection();
        // 2.预编译sql语句
        // ?是占位符号，表示此处会拼接参数
        String sql = "delete from student where id = ?";

        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            //预编译sql语句
            preparedStatement = connection.prepareStatement(sql);
            //设置占位符的值
            preparedStatement.setLong(1,student.getId());
            //执行sql语句
            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //PreparedStatement继承了Statement，所以这里可以直接传入
        JDBCUtil.closeAll(null,preparedStatement,connection);

        return i;
    }

    /**
     * 查询学生表
     * @throws SQLException
     */
    public void query() throws SQLException {
        //获取连接对象
        Connection connection = JDBCUtil.getConnection();
        //确定sql语句
        String sql = "select*from student";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //预编译sql语句，也可以不使用预编译
            preparedStatement = connection.prepareStatement(sql);
            //执行sql语句，结果存在resultSet中
            resultSet = preparedStatement.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        //查询所有，查询一个的话可以使用if判断
        while (resultSet.next()){
            long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            char sex = resultSet.getString("sex").toCharArray()[0];
            Date birthday = resultSet.getDate("birthday");
            System.out.println(id);
            System.out.println(name);
            System.out.println(sex);
            System.out.println(birthday);
        }

    }

    /**
     * 查询学生表方法二，将结果存在list集合中
     * @return 学生信息的list集合
     * @throws SQLException
     */
    public List<Student> query02() throws SQLException {
        //获取连接对象
        Connection connection = JDBCUtil.getConnection();
        //确定sql语句
        String sql = "select*from student";
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            //获取语句执行者
            statement = connection.createStatement();
            //执行sql语句
            resultSet = statement.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //创建集合用于存储学生表信息
        List <Student> students = new ArrayList <>();
        while (resultSet.next()){
            //获取学生表的属性值并赋值给新创建的student对象
            Student student = new Student(resultSet.getLong("id"), resultSet.getString("name"),
                    resultSet.getString("sex").charAt(0), resultSet.getDate("birthday"));
            //将得到学生表属性值的student对象加入到list集合当中
            students.add(student);
        }
        //关闭资源
        JDBCUtil.closeAll(resultSet,statement,connection);
        //返回集合
        return students;
    }


}

四、测试
在使用测试类的时候可能要一个一个测试方法，不然可能会出现错误

import org.jgs2010dao.StudentDao;
import org.jgs2010entity.Student;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author: changqing
 * @date: 2021/3/6 14:18
 */
public class Demo02 {
    public static void main(String[] args) throws ParseException, SQLException {

        //插入数据
        StudentDao studentDao = new StudentDao();
        int insert = studentDao.insert(new Student(null, "王五", '男', new Date()));

        //修改数据
        studentDao.update(new Student((long) 4,"旺旺",'女',
                new SimpleDateFormat("yyyy-MM-dd").parse("1998-01-01")));

        //删除数据
        studentDao.delete(new Student((long)4,null,null,null));

        //查询数据方法一
        studentDao.query();

        //查询数据方法二
        List <Student> students = studentDao.query02();
        students.forEach(System.out::println);
        //System.out.println(students);
    }
}