学习IDEA连接MySQL数据库（实现增删改查）

1、在测试类testDbUtil.java编写测试方法:

增加：

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

在运行代码前这是之前添加的两条记录：

运行后，添加了跳记录：

修改（按i条件为d进行修改）：

@Test
public void testUpdate(){
    try {
        Connection conn = MySqlDbUtil. getConnection();
        String sql = "UPDATE band set name=?,remark=? WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);//sql语句预处理
        pstmt.setString(1,"软工2班");
        pstmt.setString(2,"备注信息2");
        pstmt.setInt(3,5);
        int result = pstmt.executeUpdate();
        if(result>0){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }
        pstmt.close();
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    }

运行代码后id为5的记录被修改了：


删除（按条件id进行删除）：

 @Test
    public void testDelete(){
        try {
            Connection conn = MySqlDbUtil. getConnection();
            String sql = "DELETE FROM band WHERE id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);//sql语句预处理
            pstmt.setInt(1,5);
            int result = pstmt.executeUpdate();
            if(result>0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

运行代码后id为5的记录被删除了：


查询（查询全部记录）：

@Test
   public void testFindAll(){
       try {
           Connection conn = MySqlDbUtil. getConnection();
           String sql = "SELECT * FROM band";
           PreparedStatement pstmt = conn.prepareStatement(sql);//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();
       }
   }

运行代码后我们可以看到全部记录：


查询（按id进行查询）：

@Test
   public void testFindById(){
       try {
           Connection conn = MySqlDbUtil. getConnection();
           String sql = "SELECT * FROM band WHERE id=?";
           PreparedStatement pstmt = conn.prepareStatement(sql);//sql语句预处理
           pstmt.setInt(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();
       }
   }

查询id=1，运行代码后如下：


查询（按通配字符%惊醒查询）：

@Test
    public void testFindByName(){
        try {
            Connection conn = MySqlDbUtil. getConnection();
            String sql = "SELECT * FROM band WHERE name LIKE ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);//sql语句预处理
            pstmt.setString(1,"%2%");
            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();
        }
    }

按name=%2%，精心查询，运行代码后如下：