JDBC学习笔记（一）实现增删改查

开发工具：idem，数据库版本：5.5.60，maven项目

tbuser表：，

目录结构：

public class Testconn {

    private  Connection connection;
    private PreparedStatement pstmt;
    private ResultSet rs;

//从文件中获取内容连接数据库
    @Before                    
    public void init() throws Exception {
        InputStream inputStream = null;
        inputStream = Testconn.class.getResourceAsStream("/jdbc.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("jdbcUrl");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
        Class.forName(driverClass);
        connection = DriverManager.getConnection(url, username, password);
    }


//关闭操作
    @After
    public void  destory() throws Exception{
        if(rs!= null){
            rs.close();
        }
        if(pstmt!= null){
            pstmt.close();
        }
        if(connection!= null){
            connection.close();
        }


    }
//ctrl+p查看函数传什么参数
    @Test
    public void  textConnection2() throws Exception{
        String sql = "select * from course";
        pstmt = connection.prepareStatement(sql);
        rs = pstmt.executeQuery();
        while (rs.next()){
            int courseid = rs.getInt("courseid");
            String coursename = rs.getString("coursename");
            System.out.print(courseid);
            System.out.println(coursename);
        }

    }

    //查单条数据
    @Test
    public void testQueryUserById()throws Exception{
        int userid=1;
        String sql = "select *from tbuser where userid=?";
        pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1,userid);
        rs = pstmt.executeQuery();
        int index = 0;
        if(rs.next()){
            //采用无参构造方法
            Tbuser tbuser = new Tbuser();
            tbuser.setUserid(rs.getInt(++index));
            tbuser.setUsername(rs.getString(++index));
            tbuser.setPassword(rs.getString(++index));
            tbuser.setUserroles(rs.getString(++index));
            tbuser.setNickname(rs.getString(++index));
            tbuser.setRegtime(rs.getDate(++index));
            System.out.println(tbuser);
        }
    }

    //查多条
    @Test
    public void testQueryUsers() throws SQLException {
        String sql = "select * from tbuser order by userid desc";
        pstmt = connection.prepareStatement(sql);
        rs = pstmt.executeQuery();
        List<Tbuser> list = new ArrayList<>();
        int index = 0;
        while(rs.next()){
            //有参数的构造方法
            Tbuser tbuser = new Tbuser(rs.getInt(++index),rs.getString(++index),rs.getString(++index),rs.getString(++index),rs.getString(++index),rs.getDate(++index));
            list.add(tbuser);
            index=0;
        }
        for (Tbuser u :list){
            System.out.println(u);
        }
    }
    //增加一条记录
    @Test
    public void testAddUser() throws SQLException {
        String sql = "INSERT INTO tbuser VALUES (NULL,?,?,?,?,NOW())";
        pstmt = connection.prepareStatement(sql);
        pstmt.setString(1,"abc");
        pstmt.setString(2,"1234");
        pstmt.setString(3,"01");
        pstmt.setString(4,"呵呵");
        //不需要写rs对象，也不能调用executeQuery();
        int row = pstmt.executeUpdate();
        this.print(row);
    }

    @Test
    public void testUpdateUser() throws SQLException {
        String sql = "UPDATE tbuser SET username=?,PASSWORD=?,nickname=? WHERE userid=?";
        pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, "1210");
        pstmt.setString(2, "5678");
        pstmt.setString(3,"猪");
        pstmt.setInt(4,36);
        int row = pstmt.executeUpdate();
        this.print(row);

    }


    @Test
    public void testDelete() throws SQLException {
        String sql = "DELETE FROM tbuser WHERE userid=?";
        pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1,38);
        this.print(pstmt.executeUpdate());
    }
    //每次增删改都要显示成功或失败
    public void print(int row) {
        if(row>0){
            System.out.println("操作成功");
        }else{
            System.out.println("操作失败");
        }
    }

进一步优化代码，去除增删改操作的重复代码：

  //我们发现新增、修改、删除里有很多代码也是重复。写一个函数，让每次操作直接调用
    public void executeUpdate(String sql,Object objs[]) throws SQLException {
        pstmt = connection.prepareStatement(sql);
        for(int i=0;i<objs.length;i++){
            pstmt.setObject(i+1,objs[i]);
        }
        int row = pstmt.executeUpdate();
        this.print(row);
    }

//添加操作
    @Test
    public void testAddUser1() throws SQLException {
        String sql = "INSERT INTO tbuser VALUES (NULL,?,?,?,?,NOW())";
        Object objs[] = new Object[]{"asdf","1234","01","阿生宿舍"};
        this.executeUpdate(sql,objs);
    }

//修改操作
    @Test
    public void testUpdateUser1() throws SQLException {
        String sql = "UPDATE tbuser SET username=?,PASSWORD=?,nickname=? WHERE userid=?";
        Object objs[] = new Object[]{"qwr","5678","帅的想毁容",40};
        this.executeUpdate(sql,objs);
    }