JDBC的基本使用

导包

基础代码

        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=false";
        String username = "root";
        String password = "root123";
        Connection conn = DriverManager.getConnection(url,username,password);
        String sql = "update users set email = 'hello666' where name = 'zhangsan';";
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(sql);
        System.out.println(count);
        stmt.close();
        conn.close();

jdbc事务

connection

获取执行sql的对象，管理事务

conn.setAutoCommit(false);

        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=false";
        String username = "root";
        String password = "root123";
        Connection conn = DriverManager.getConnection(url,username,password);
        String sql = "update users set email = '66666' where name = 'zhangsan';";
        String sql2 = "update users set email = '666666' where name = 'wangwu';";
        Statement stmt = conn.createStatement();
        try{
            conn.setAutoCommit(false);
            int count1 = stmt.executeUpdate(sql);
            System.out.println(count1);
            int i = 3/0;
            int count2 = stmt.executeUpdate(sql2);
            System.out.println(count2);
            conn.commit();
        }catch (Exception e){
            conn.rollback();
            System.out.println(e);
        }finally {
            stmt.close();
            conn.close();
        }
    }

statement

执行sql语句

int executeUpdate(sql):执行DML、DDL语句

返回值：DML影响的行数，DDL执行成功后也可能返回0

ResultSet executeQuery(sql):执行DQL语句

返回值：ResultSet结果集对象

        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=false";
        String username = "root";
        String password = "root123";
        Connection conn = DriverManager.getConnection(url,username,password);
        //String ddl = "drop table  if exists book;";
        String ddl="create table book (\n" +
                "id int primary key auto_increment,\n" +
                "name varchar(20) not null,\n" +
                "price DECIMAL \n" +
                ")";//一个sql只能包含一个动作。如不能把删除表和创建表的动作写在一个sql中。
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(ddl);
        System.out.println(count);
        stmt.close();
        conn.close();
    }

ResultSet

boolean next();将光标向前移一行，判断当前行是否有效行

XXX get数据类型(int 列编号从1开始/String 列名称) 

        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=false";
        String username = "root";
        String password = "root123";
        Connection conn = DriverManager.getConnection(url,username,password);
        String dql = "select * from users";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(dql);
        while(rs.next()){
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String pwd = rs.getString("password");
            System.out.println("id:"+id+";name:"+name+";pwd:"+pwd);
        }
        stmt.close();
        conn.close();

 PrepareStatement

传统的statement执行sql占位的时候，会出现sql注入问题：

如当密码为   'or'1'='1  的时候就会拼接后变成了select * from users where name = 'zhangsan' and password = ''or'1'='1';

        String inpurUame = "zhangsan";
        String inputPassword ="'or'1'='1";//传统的statement执行sql容易出现sql注入问题，比如：密码为'or'1'='1
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=false";
        String username = "root";
        String password = "root123";
        Connection conn = DriverManager.getConnection(url,username,password);
        String sql = "select * from users where name = '"+inpurUame+"' and password = '"+inputPassword+"';";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
        stmt.close();
        conn.close();

PrepareStatement可预编译sql提升性能，还可对敏感字符转义来防止sql注入。

pstmt.setXxx(int index从1开始,Xxx x);

ResultSet rs = pstmt.executeQuery();

        String inpurUame = "zhangsan";
        String inputPassword ="123456";//传统的statement执行sql容易出现sql注入问题，比如：密码为'or'1'='1
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=false&useServerPreStmts=true";
        String username = "root";
        String password = "root123";
        Connection conn = DriverManager.getConnection(url,username,password);
        String sql = "select * from users where name = ? and password = ?;";
        System.out.println(sql);
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,inpurUame);
        pstmt.setString(2,inputPassword);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
        pstmt.close();
        conn.close();

PrepareStatement开启预编译的方式

先加上url后缀参数useServerPreStmts=true就可以开启了