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

快来打我* 2021-09-25 02:32 396阅读 0赞

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

--------------------

### 教程目录 ###

*  0x00 教程内容
 *  0x01 环境准备
 *   *   *   *   *  1. 表数据准备
                 *  2. 项目结构准备
 *  0x02 编写代码
 *   *   *   *   *  1. Student实体类代码
                 *  2. StudentDAO访问接口
                 *  3. StudentDAOImpl访问接口实现类
 *  0x03 测试
 *   *   *   *   *  1. 增删改查测试类编写
                 *  2. 测试结果
 *  0xFF 总结

# 0x00 教程内容 #

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

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

# 0x01 环境准备 #

##### 1. 表数据准备 #####

给数据库添加几条数据：

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

##### 2. 项目结构准备 #####

新建相应的包以及类（注意新建的文件类型）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW9jazIwMTg_size_16_color_FFFFFF_t_70]

# 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、查  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW9jazIwMTg_size_16_color_FFFFFF_t_70 1]  
2、增  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW9jazIwMTg_size_16_color_FFFFFF_t_70 2]  
3、删  
![在这里插入图片描述][20210113175958337.png]  
4、改  
![在这里插入图片描述][20210113180109568.png]

# 0xFF 总结 #

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

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

--------------------

[Link 1]: https://blog.csdn.net/shaock2018/article/details/89349249
[Link 2]: https://img-blog.csdnimg.cn/20190608000417972.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW9jazIwMTg=,size_16,color_FFFFFF,t_70
[Link 3]: https://img-blog.csdnimg.cn/20190514084602748.png
[Link 4]: https://weibo.com/5680168183/
[CSDN]: https://blog.csdn.net/shaock2018
[Link 5]: https://www.jianshu.com/u/6f033c6a3b90
[JDBC_MySQL]: https://blog.csdn.net/shaock2018/article/details/112555621
[JDBC_MySQL 1]: https://blog.csdn.net/shaock2018/article/details/112563832
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW9jazIwMTg_size_16_color_FFFFFF_t_70]: /images/20210923/a5c807b90f714dc3a02f6e6ed896726c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW9jazIwMTg_size_16_color_FFFFFF_t_70 1]: /images/20210923/7d8293a82fb04831aedc6efa08d038c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYW9jazIwMTg_size_16_color_FFFFFF_t_70 2]: /images/20210923/a6d075d1ca1c484c895c0b7722fd43a1.png
[20210113175958337.png]: /images/20210923/3f753a304a3e456ab7f5258610ef2916.png
[20210113180109568.png]: /images/20210923/df48396ef5384c1894cc6f7515014df4.png