JDBC连接数据库学习心得2

PrepareStatment:
1,可以防止SQL注入
2,采取预编译的方式,将SQL语句先交给数据库编译好
只需要等待执行就可以了,当多个重复语句被执行时效率会比Statement高,速度快

     @Test//关于PrepareStament的增删改查
    public void t1() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","newpass");
        //?就是一个占位符
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO stu VALUES (NULL,?,?);");
        //第一个参数是?的位置
        //有几个问号就传递几个参数
        //第几个问号,角标就是几
        pstmt.setString(1,"张三");
        pstmt.setObject(2,40);
        pstmt.executeUpdate();
        PreparedStatement pstmt1 = conn.prepareStatement("UPDATE stu SET `name` =? WHERE id=?;");
        //将参数传到SQL语句中
        pstmt1.setObject(1,"刘振洲");
        //查找id为1的数据,将该条数据的name字段改为刘振洲
        pstmt1.setObject(2,2);
        //执行语句
        pstmt1.executeUpdate();
        PreparedStatement pstmt2 = conn.prepareStatement("SELECT * FROM stu WHERE `name`=?;");
        //将参数传到SQL语句中
        pstmt.setObject(1,"刘振洲");
        //执行语句
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getObject(1) + " " + rs.getObject(2) + " " + rs.getObject(3));
        }
    }
        PreparedStatement pstmt3 = conn.prepareStatement("DELETE FROM stu WHERE `name`=?;");
        //将参数传到SQL语句中
        pstmt3.setObject(1,"刘振洲");
        //执行语句
        pstmt3.executeUpdate();
    }

Batch批处理

 @Test
    public void t1() throws SQLException {
        Connection conn = Jdbcutil.getConnection();
        PreparedStatement pstmt = conn.prepareStatement("UPDATE stu SET `name` = '嘿嘿' WHERE `name` = '哈哈'");
        //将上面的更新name为嘿嘿的sql语句加入到批处理缓存中
        pstmt.addBatch();
        //再添加一条删除语句到批处理缓存中
        pstmt.addBatch("DELETE FROM stu WHERE `name` = '哼哼'");
        pstmt.executeBatch();
    }//addBatch只是把操作先存起来,直到遇到executeBatch才会进行批处理一起按顺序处理

Transaction事务
1.把一部分操作包装成一个整体(事务)进行运行,如果中间有错误的话,那么之前的操作作废

public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = Jdbcutil.getConnection();

            //设置提交方式为手动提交
            //开启事务
            conn.setAutoCommit(false);
            //创建一个预处理对象
            PreparedStatement pstmt = conn.prepareStatement("UPDATE money SET m =? WHERE `name`=?");
            pstmt.setObject(1,700);
            pstmt.setObject(2,"曹云金");
            pstmt.executeUpdate();

            int a = 100/0;

            pstmt.setObject(1,1300);
            pstmt.setObject(2,"郭德纲");
            pstmt.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            //回滚到事务开始的状态
            try {
                conn.rollback();
                System.out.println("卧槽!");
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }//因为中间一个100/0会抛出算数异常,所以之前的数据库更新语句并不会写入数据库,而是会直接rollback回滚.只有中间没有错误运行到最后conn.commit();才算这个事务运行完成,才会真正的执行操作.有点类似于预处理,但是不会真的处理,只有运行到最后才会一起处理

DBUtils
DBUtils是Apache提供的一个开源的方便我们操作jdbc的jar包
QueryRunner类,是该jar包的核心类
所有的操作数据库的方法都被封装在这个类中
更新,查询都是使用QueryRunner类

@Test
    public void t1() throws SQLException {
        Connection conn = Jdbcutil.getConnection();
        //建立一个QueryRunner对象,用来执行SQL语句
        QueryRunner qr = new QueryRunner();
        //调用update方法,可以执行增,删,改等sql语句
        qr.update(conn, "insert into stu values(null,'张益达',55)");
        conn.close();
    }
    @Test
    public void t2() throws SQLException {
        Connection conn = Jdbcutil.getConnection();
        //建立一个QueryRunner对象,用来执行SQL语句
        QueryRunner qr = new QueryRunner();
        //调用update方法,可以执行增,删,改等sql语句
        String sql = "select * from stu where id =1";
        Stu query = qr.query(conn, sql, new BeanHandler<Stu>(Stu.class));
        System.out.println(query.toString());
    }
    /**
     * 演示BeanListHandler的使用
     * 使用BeanListHandler可以得到一个装载指定类型对象的集合
     *
     * @throws SQLException
     */
    @Test
    public void t3() throws SQLException {
        Connection conn = Jdbcutil.getConnection();
        //建立一个QueryRunner对象,用来执行SQL语句
        QueryRunner qr = new QueryRunner();
        //调用update方法,可以执行增,删,改等sql语句
        String sql = "select * from stu where id =1;";
        List<Stu> query = qr.query(conn, sql, new BeanListHandler<>(Stu.class));
        //手敲iter+Tab
        for (Stu stu : query) {
            System.out.println(stu.getName());
        }
    }
    @Test
    public void t4() throws SQLException {
        Connection conn = Jdbcutil.getConnection();
        //建立一个QueryRunner对象,用来执行SQL语句
        QueryRunner qr = new QueryRunner();
        //调用update方法,可以执行增,删,改等sql语句
        String sql = "select name,age from stu;";
        List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler());
        System.out.println(query);
    }
    @Test
    public void t5() throws SQLException {
        Connection conn = Jdbcutil.getConnection();
        //建立一个QueryRunner对象,用来执行SQL语句
        QueryRunner qr = new QueryRunner();
        //调用update方法,可以执行增,删,改等sql语句
        String sql = "select * from stu where id = 1;";
        List<Object[]> query = qr.query(conn, sql, new ArrayListHandler());
        for (Object[] objects : query) {
            for (Object object : objects) {
                System.out.print(object + "-");
            }
            System.out.println();
        }
    }
    /**
     * ResultSetHandler是接口
     * BeanHandler,BeanListHandler等类都实现了ResultSetHandler接口
     * 接口回调
     * @throws SQLException
     */
    @Test
    public void t6() throws SQLException {
        Connection conn = Jdbcutil.getConnection();
        //建立一个QueryRunner对象,用来执行SQL语句
        QueryRunner qr = new QueryRunner();
        //调用update方法,可以执行增,删,改等sql语句
        String sql = "select * from stu limit 1;";
        Stu query = qr.query(conn, sql, new ResultSetHandler<Stu>() {
            @Override
            public Stu handle(ResultSet resultSet) throws SQLException {

                while (resultSet.next()) {
                    Stu stu = new Stu();
                    stu.setName(resultSet.getString(2));
                    return stu;
                }
                return null;
            }
        });
    }