JDBC实现MySQL数据库的增删改查

大家好，我是邵奈一，一个不务正业的程序猿、正儿八经的斜杠青年。
1、世人称我为：被代码耽误的诗人、没天赋的书法家、五音不全的歌手、专业跑龙套演员、不合格的运动员…
2、这几年，我整理了很多IT技术相关的教程给大家，爱生活、爱分享。
3、如果您觉得文章有用，请收藏，转发，评论，并关注我，谢谢！
博客导航跳转（请收藏）：邵奈一的技术博客导航
| 公众号 | 微信 | 微博 | CSDN | 简书 |

---

0x00 教程内容

1. 环境准备
2. 编写代码
3. 测试

如果没有基础，或者环境没有准备，请参考此两篇教程：
JDBC连接MySQL数据库（一）
JDBC连接MySQL数据库（二）

0x01 环境准备

1. 表数据准备

给数据库添加几条数据：

insert into student(name,age) values ("邵奈一",30);
insert into student(name,age) values ("邵奈一",28);

2. 项目结构准备

新建相应的包以及类（注意新建的文件类型）

0x02 编写代码

1. Student实体类代码

package com.shaonaiyi.domain;

/**
 * @Auther: shaonaiyi@163.com
 * @Date: 2021/1/13 15:17
 * @Description: 学生实体类
 */
public class Student {

    private Integer id;
    private String name;
    private Integer age;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

2. StudentDAO访问接口

package com.shaonaiyi.dao;

import com.shaonaiyi.domain.Student;
import java.util.List;

/**
 * @Auther: shaonaiyi@163.com
 * @Date: 2021/1/13 15:19
 * @Description: Student访问接口
 */
public interface StudentDAO {

    //1、查询所有的学生
    public List<Student> query();

    //2、新增学生
    public Integer save(Student student);

    //3、删除学生
    public Integer delete(Integer id);

    //4、修改学生
    public Integer update(Student student);

}

3. StudentDAOImpl访问接口实现类

package com.shaonaiyi.dao;

import com.shaonaiyi.domain.Student;
import com.shaonaiyi.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Auther: shaonaiyi@163.com
 * @Date: 2021/1/13 15:22
 * @Description: Student访问接口实现类
 */
public class StudentDAOImpl implements StudentDAO {

    @Override
    public List<Student> query() {

        List<Student> studentList = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("select * from student");
            resultSet = preparedStatement.executeQuery();

            Student student = null;
            while (resultSet.next()) {

                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");

                student = new Student();
                student.setId(id);
                student.setName(name);
                student.setAge(age);

                studentList.add(student);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }

        return studentList;
    }

    @Override
    public Integer save(Student student) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Integer result = 0;

        try {

            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("insert into student(name,age) values (?,?)");

            preparedStatement.setString(1, student.getName());
            preparedStatement.setInt(2,student.getAge());

            result = preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }

        return result;

    }

    @Override
    public Integer delete(Integer id) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Integer result = 0;

        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("delete from student where id = " + id);
            result = preparedStatement.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }

        return result;
    }

    @Override
    public Integer update(Student student) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Integer result = 0;

        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("update student set age = " + student.getAge() + " where name = '" + student.getName() + "'");
            result = preparedStatement.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }

        return result;
    }
}

0x03 测试

1. 增删改查测试类编写

package com.shaonaiyi.dao;

import com.shaonaiyi.domain.Student;
import org.junit.Test;

import java.util.List;

/**
 * @Auther: shaonaiyi@163.com
 * @Date: 2021/1/13 15:34
 * @Description: Student访问接口实现测试类
 */
public class StudentDAOImplTest {

    @Test
    public void testQuery() {
        StudentDAO studentDAO = new StudentDAOImpl();
        List<Student> studentList =  studentDAO.query();

        for (Student student : studentList) {
        	System.out.println("---------------------------");
            System.out.println("学生id：" + student.getId());
            System.out.println("学生name：" + student.getName());
            System.out.println("学生age：" + student.getAge());
        }

    }

    @Test
    public void testInsert() {

        StudentDAO studentDAO = new StudentDAOImpl();
        Student student = new Student();
        student.setName("邵奈二");
        student.setAge(18);
        Integer result = studentDAO.save(student);
        System.out.println("结果：" + result);
    }

    @Test
    public void testDelete() {

        StudentDAO studentDAO = new StudentDAOImpl();
        Integer result = studentDAO.delete(2);
        System.out.println("结果：" + result);

    }

    @Test
    public void testUpdate() {

        StudentDAO studentDAO = new StudentDAOImpl();
        Student student = new Student();
        student.setName("邵奈一");
        student.setAge(32);
        Integer result = studentDAO.update(student);
        System.out.println("结果：" + result);

    }

//    @Test
//    public void testSQL() {
//        Student student = new Student();
//        student.setName("邵奈一");
//        student.setAge(32);
//        String sql = "update student set age = " + student.getAge() + " where name = '" + student.getName() + "'";
//        System.out.println(sql);
//
//    }

}

温馨提示：为了方便，这里把SQL语句直接写死在了参数里，其实可以赋值给一个字符串，增加代码的可读性，并且方便打印出来看看，免得自己在拼接SQL语句的时候拼接错误。

当然，如果担心拼接错误，完全可以以下代码，通过设置坐标传值进去：

preparedStatement = connection.prepareStatement("insert into student(name,age) values (?,?)");

preparedStatement.setString(1, student.getName());
preparedStatement.setInt(2,student.getAge());

2. 测试结果

1、查

2、增

3、删

4、改

0xFF 总结

1. 请关注本博客，以后提供更多的教程，谢谢。

邵奈一 原创不易，如转载请标明出处，教育是一生的事业。

---