Java对数据库的调用一些常用方法总结

本篇博客知识点
1.executeQuery:方法
2.execute方法
3.executeUpdate方法
4，两种获得自动增长的主键方法~
5.学习用jdbc执行批处理

本篇博客调用的数据库为book 表结构如下

executeQuery:专门用于查询的方法。返回值为查询结果

    /**
     * executeQuery:专门用于查询的方法。返回值为查询结果
     * @throws Exception 
     */
    @Test
    public void demo1() throws Exception{
        Connection con = ConnUtils.getConnection();
        Statement st = con.createStatement();
        String sql = "select * from book";
        ResultSet rs= st.executeQuery(sql);
        while(rs.next()){
            int id = rs.getInt(1);
            String name = rs.getString("name");
            Double price = rs.getDouble("price");

            String date = rs.getDate("birth")+" "+rs.getTime("birth");
            System.out.println("编号:"+id+",书名:"+name+",价格:"+price+",时间"+date);
        }
        con.close();
    }

细节：rs.getString(参数）,参数可以给序号，也可以给字段名字

execute：可以用于增、删、改、查。返回值为 boolean型 只有当执行查询时返回值为true

    /**
     * execute：可以用于增、删、改、查。返回值为 boolean型  只有当执行查询时返回值为true
     * @throws Exception
     */
    @Test
    public void demo2() throws Exception{
        Connection con = ConnUtils.getConnection();
        Statement st = con.createStatement();
        String sql_select = "select * from book where price>50";
        String sql_insert = "insert into book(name,price,birth) values('资本论','105.3','1883-5-6 13:11:11');";
        String sql_delete = "delete from book where id=1";
        //update 表名  set 字段名1=值 ，字段名2=值 where 子句[in 子句] [between子句]
        String sql_update = "update book set name='海燕',price=19.6 where id=1 ";
        boolean boo = st.execute(sql_update);
        if(boo){
            ResultSet rs = st.getResultSet();
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                Double price = rs.getDouble("price");
                String date = rs.getDate("birth")+""+rs.getTime("birth");
                System.out.println("编号:"+id+",书名:"+name+",价格:"+price+",时间"+date);
            }
        }
        con.close();
    }

executeUpdate: 除了不能用于查询外，其他增、删、改都能执行，返回值为整形 n– 表示影响的行数

@Test
    public void demo3() throws Exception{
        Connection con = ConnUtils.getConnection();
        Statement st = con.createStatement();
//      String sql_select = "select * from book where price>50";
        String sql_insert = "insert into book(name,price,birth) values('生物进化论','105.3','1883-5-6 13:11:11');";
        String sql_delete = "delete from book where id=1";
        //update 表名  set 字段名1=值 ，字段名2=值 where 子句[in 子句] [between子句]
        String sql_update = "update book set name='海燕',price=19.6 where id=1 ";
        int n = st.executeUpdate(sql_insert);
        System.out.println(n);
        con.close();
    }

通过statemnet获得自动增长的主键~ 插入的时候

    /**
     * 通过statemnet获得自动增长的主键~ 插入的时候
     * st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
     * st.getGeneratedKeys();
     * @throws Exception
     */
    @Test
    public void getAuto() throws Exception{
        Connection con  = ConnUtils.getConnection();
        Statement st = con.createStatement();
        String sql_insert = "insert into book(name,price,birth) values('达尔文','105.3','1883-5-6 13:11:11');";
        String sql = "insert into book(name,price,birth) values('海子诗集',79.8,'1996-4-8 8:30:5')";
        int n = st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = st.getGeneratedKeys();
        if(rs.next()){
            System.out.println("---------");
            int id = rs.getInt(1);
            System.out.println("插入之后获得自动增长ID:"+id);
        }
        con.close();

    }

通过PreparedStatement获得自动增长的主键

/**
     * 通过PreparedStatement获得自动增长的主键~ 插入的时候
     * PreparedStatement pst = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
     * pst.getGeneratedKeys();
     * @throws Exception
     */
    @Test
    public void getAuto2() throws Exception{
        Connection con  = ConnUtils.getConnection();
        String sql = "insert into book(name,price,birth) values(?,?,?)";
        PreparedStatement pst = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
        String name = "唐诗宋词300首";
        String price = "79.8";
        String date = "1996-4-8 8:30:5";
        pst.setString(1, name);
        pst.setString(2, price);
        pst.setString(3, date);
        int n = pst.executeUpdate();
        //***********
        ResultSet rs = pst.getGeneratedKeys();
        if(rs.next()){
            System.out.println("---------");
            int id = rs.getInt(1);
            System.out.println("插入之后获得自动增长ID:"+id);
        }
        con.close();

    }

jdbc执行批处理

方式一：

    /**
     * 学习用jdbc执行批处理 (把多条sql语句打包，一次性发给mysql数据库，让它执行。该方式能够减少客户端与MySQL服务器的通讯次数)
     * 先打包addBatch
     * 返回值为每一条sql语句影响的记录数的数组
     * @throws Exception
     */
    @Test
    public void batchDemo1() throws Exception{
        Connection con = ConnUtils.getConnection();
        Statement st = con.createStatement();
        String sql = "insert into book(name,price,birth) values('我的大学','18.5','2017-07-30 16:09:09')";
        for(int i=0;i<5;i++){
            // 打包到一起
            st.addBatch(sql);
        }
        sql = "update book set price=price*1.5 where price>100";
        st.addBatch(sql);
        int n[] = st.executeBatch();
        //n --- 1 1 1 1 1 7; 返回值为每一条sql语句影响的记录数
        for(int i=0;i<n.length;i++){
            System.out.println(n[i]);
        }
        con.close();
    }

方式二

/**
     * //           pst.addBatch(sql);---错误~  没有参数
     * @throws Exception
     */
    @Test
    public void batchDemo2() throws Exception{
        Connection con = ConnUtils.getConnection();
        String sql = "insert into book(name,price,birth) values(?,?,?)";
        PreparedStatement pst = con.prepareStatement(sql);
        for(int i=0;i<5;i++){
            pst.setString(1, "高等数学"+i);
            pst.setDouble(2, 35+i);
            pst.setString(3, "2017-07-30 16:09:09");

//          pst.addBatch(sql);---错误~  没有参数
            pst.addBatch();
        }
        sql = "update book set price=price*1.5 where price>100";
        pst.addBatch(sql);
        int n[] = pst.executeBatch();
        for(int i=0;i<n.length;i++){
            System.out.println(n[i]);
        }
        con.close();
    }