通过IDEA操作数据库，进行业务逻辑管理---by wjf（2020.4.29）

此前我们已经顺利的通过JDBC连接到数据库
测试代码：

    @Test
    public void testConnection(){
        try {
            MySqlDbUtil.getConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

测试结果：

测试成功，我们已经顺利的连接到MySQL数据库

那么接下来我们就可以通过代码来操作数据库，进行业务逻辑管理

①实现添加数据的功能

@Test
    public void testInsert(){
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "INSERT INTO band(name,remark) VALUES (?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"软工test班");
            pstmt.setString(2,"备注信息test");
            int result = pstmt.executeUpdate();
            System.out.println(result);
            if (result > 0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            pstmt.close();
            conn.close();

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

测试结果：

②修改数据

 @Test
    public void testUpdate(){
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "UPDATE band SET name =?, remark=? WHERE id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"软工1班");
            pstmt.setString(2,"备注信息1");
            pstmt.setInt(3,8);
            int result = pstmt.executeUpdate();
            if (result > 0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            pstmt.close();
            conn.close();

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

通过此代码可以将id为8的修改为相应内容

测试结果：

③删除数据

@Test
    public void testDelete(){
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "DELETE FROM band WHERE id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,8);
            int result = pstmt.executeUpdate();
            if (result > 0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            pstmt.close();
            conn.close();

        }catch (Exception e){
            e.printStackTrace();
        }
    }
    @Test

此代码可以删除id为8的数据

④查询功能（查询全部字段）

 public void testFindAll(){
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs  = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String remark = rs.getString("remark");
                System.out.println(id + "," + name + "," + remark);
            }
            rs.close();
            pstmt.close();
            conn.close();

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

测试结果：

⑤查询功能（通过字段值查询）

@Test
    public void testFindById(){
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,6);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String remark = rs.getString("remark");
                System.out.println(id + "," + name + "," + remark);
            }
            rs.close();
            pstmt.close();
            conn.close();

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

用于查询id为6的记录

测试结果：

⑥查询功能（实现模糊查询）

@Test
    public void testFindByName(){
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE name LIKE ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"%1%");
            ResultSet rs  = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String remark = rs.getString("remark");
                System.out.println(id + "," + name + "," + remark);
            }
            rs.close();
            pstmt.close();
            conn.close();

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

此代码用于查询带有“1”的记录

测试结果：