JDBC第二篇

PreparedStatement对象

PreparedStatement对象继承Statement对象，它比Statement对象更强大，使用起来更方便。

• Statement对象编译SQL语句时，如果SQL语句有变量，就需要使用分隔符来隔开，如果变量非常多，就会使SQL变得非常复杂。PreparedStatement可以使用占位符，简化sql的编写。
• Statement会频繁编译SQL。PreparedStatement可对SQL进行预编译，提高效率，预编译的SQL存储在PreparedStatement对象中。
• PreparedStatemente防止SQL注入。

Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;

try{
     connection=DBUtilts.getConnection();
     String sql="select * from userinfo where UserSex=?";
     preparedStatement=connection.preparedStatement(sql);
     preparedStatement.setString(1,"男");
     resultSet=preparedStatement.exeuteQuery();
}catch(SQLException e){
     e.printStackTrace();
}finally{
     DBUtils.close(resultSet,preparedStatement,connection);
}

批处理

当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条发送执行，采用批处理以提升执行效率。

批处理有两种方式：

• Statement
• PreparedStatement

通过执行executeBatch()方法批量处理执行SQL语句，返回一个int[]数组，该数组代表各句SQL的返回值。

Statement批处理

        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
           connection=DBUtils.getConnection();
           statement=connection.createStatement();
           String sql1="insert userinfo(userid,username,usersex,userage,useraddress)\n" +
                   "select 12,'卡哇伊','男','28','多伦多'";
           String sql2="update userinfo set UserAge='20' where UserName='张三'";
           //将sql添加到批处理
           statement.addBatch(sql1);
           statement.addBatch(sql2);
           //执行批处理 返回影响的行数
            int[] ints = statement.executeBatch();
            for(int i:ints){
                System.out.println(i);
            }
            //清理批处理的sql
            statement.clearBatch();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.close(resultSet,statement,connection);
        }

PreparedStatement对象

        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            connection=DBUtils.getConnection();
            String sql="insert userinfo(userid,username,usersex,userage,useraddress) select ?,?,?,?,?";
            preparedStatement=connection.prepareStatement(sql);
            for(int i=20;i<=40;i++){
                preparedStatement.setInt(1,i);
                preparedStatement.setString(2,"小卡"+i);
                preparedStatement.setString(3,"男");
                preparedStatement.setInt(4,i);
                preparedStatement.setString(5,"多伦多");
                //添加到批处理中
                preparedStatement.addBatch();
                if(i%2==15){
                    //分批执行批处理
                    preparedStatement.executeBatch();
                    //清空批处理【如果数据量太大，所有数据存入批处理，内存肯定溢出】
                    preparedStatement.clearBatch();
                }
            }
            //剩下的再执行一次批处理
            preparedStatement.executeBatch();
            //再清空
            preparedStatement.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet,preparedStatement,connection);
        }

获取数据库的自动主键列

Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            connection=DBUtils.getConnection();
            String sql="insert usertest(name) values(?)";
            //获取自增列 高版本mysql连接jar   需要加参数Statement.RETURN_GENERATED_KEYS
            preparedStatement=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1,"卡哇伊");
            //获取sql执行后影响行数
            int i = preparedStatement.executeUpdate();
            if(i>0){
                //获取主键列的值
                ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
                while(generatedKeys.next()){
                    int id = generatedKeys.getInt(1);
                    System.out.println(id);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet,preparedStatement,connection);
        }

调用数据库的存储过程

调用存储过程的语法

{call <procedure-name>[(<arg1>,<arg2>, ...)]}

调用函数的语法

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

如果是参数是Output类型的，那么JDBC调用的时候是要注册的。

        Connection connection=null;
        CallableStatement callableStatement=null;
        try {
            connection=DBUtils.getConnection();
            callableStatement= connection.prepareCall("{call demoSp(?,?)}");
            callableStatement.setString(1,"james");
            //注册第2个参数，类型是VARCHAR 
            callableStatement.registerOutParameter(2,Types.VARCHAR);
            callableStatement.execute();
            //获取传出参数【获取存储过程里的值】
            String result = callableStatement.getString(2);
            System.out.println(result);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
                callableStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

实例

----------------------------------------------------------------------------------过程

#修改mysql语句的结果符为//
mysql > delimiter //

#定义一个过程，获取users表总记录数，将10设置到变量count中
create procedure simpleproc(out count int)
begin
    select count(id) into count from users;
end
//

#修改mysql语句的结果符为;
mysql > delimiter ;

#调用过程，将结果覆给变量a，@是定义变量的符号
call simpleproc(@a);

#显示变量a的值
select @a;

//以下是Java调用Mysql的过程
    String sql = "{call simpleproc(?)}";
    Connection conn = JdbcUtil.getConnection();
    CallableStatement cstmt = conn.prepareCall(sql);
    cstmt.registerOutParameter(1,Types.INTEGER);
    cstmt.execute();
    Integer count = cstmt.getInt(1);
    System.out.println("共有" + count + "人");

----------------------------------------------------------------------------------函数

#修改mysql语句的结果符为//
mysql > delimiter //

#定义一个函数，完成字符串拼接
create function hello( s char(20) ) returns char(50) 
return concat('hello，',s,'!');
//

#修改mysql语句的结果符为;
mysql > delimiter ;

#调用函数
select hello('world');

//以下是Java调用Mysql的函数
    String sql = "{? = call hello(?)}";
    Connection conn = JdbcUtil.getConnection();
    CallableStatement cstmt = conn.prepareCall(sql);
    cstmt.registerOutParameter(1,Types.VARCHAR);
    cstmt.setString(2,"zhaojun");
    cstmt.execute();
    String value = cstmt.getString(1);
    System.out.println(value);
    JdbcUtil.close(cstmt);
    JdbcUtil.close(conn);