(五) 执行查询操作

五. 执行查询操作
5.1 预备语句
预备语句(prepared statement) : 一个带有宿主变量的查询语句，每次查询的时候时只需要为该变量填入不同的字符串就可以反复多次地使用该语句。
在PreparedStatement预备语句中，每个宿主变量都用“？”来表示。如果存在一个以上的变量，那么在设置变量值时必须注意"?"的位置。
e.g. 

 

String publishQuery = "SELECT * FROM table WHERE name=?"
PreparedStatement publishQueryStat = conn.prepareStatement(publishQuery);

 

(1)在执行预备语句之前，必须使用setXXX方法将变量绑定到实际值上。和ResultSet方法中得get方法类似。第一个参数是需要赋值的变量的位置，位置1标示第一个"?",第二个参数是赋予宿主变量的值。
(2)重用预备语句，需要通过set或调用clearParameters方法，否则所有宿主的变量的绑定都不会改变。也就是说，想要在一个查询到另一个查询中，重用预备语句，只需要使用setXXX方法。
(3)执行execute/executeQuery/executeUpdate
e.g.  Result rs = publishQueryStmt.executeQuery();
注意：
(1)因为效率问题，当能通过复杂的SQL进行查询的情况下，不要通过JAVA代码来遍历结果集
(2)executeUpdate方法中的sql可以使用select子句。
e.g. String sql = "UPDATE books SET books.price=? where books.id=(select id from publish where publish.name=?)"

5.2 读写LOB
在SQL中二进制大对象称为BLOB，字符串大对象称为CLOB

(1)读取LOB,需要执行SELECT语句，然后在ResultSet上调用getBlob和getClob方法，这样就可以获得Blob和Clob对象。
要从Blob中获取二进制数据，可以调用getBytes或getInputStream。
要从Clob中获取字符数据，可以调用getSubString或getCharacterStream
e.g.

 

PreparedStatement stat = conn.prepareStatement("SELECT cover FROM book_covers WHERE isbn=?")
stmt.setInt(1, isbn);
ResultSet result = stmt.executeQuery();
if(result.next){
     Blob coverBlob = result.getBlob(1);
     Image coverImage = ImageIO.read(coverBlob.getInputStream());
}

 

(2)将LOB置于数据库中，需要在Connection对象上调用调用createBlob或createClob，然后获取一个用于该LOB的输出流或写出器，并将该对象存储到数据库中。
e.g. 

 

        Blob coverBlob = connection.createBlob();
        int offset = 0;
        OupputStream out = coverBlob.setBinaryStream(offset);
        ImageIO.write(coverImage, "PNG", out);
        PreparedStatement stat = conn.prepareStatement("INSERT INTO cover VALUES(?,?)")
        stmt.set(1, isbn);
        stmt.set(2, coverBlob);
        stmt.executeUpdate();

 

5.3 SQL转义
转义主要用于特征:
(1)日期和时间字面常量
日期和时间字面常量在随数据库的不同而变化很大，要嵌入日期或时间字面常量，需要按照ISO 8601格式指定它的值。之后驱动程序会将其转换为本地格式。
其中d-DATE {d '2011-11-11'}, t-TIME {t '23:59:59'}, ts-TIMESTAMP {ts '2011-11-11 23:59:59'}
e.g.MySQL环境下例子
1)建表
String sql = String createTable = "CREATE TABLE test_table(d DATE, t TIME, ts TIMESTAMP)";
2)输入

 

String sql = "INSERT INTO test_table VALUES({d '2001-11-11'},{t '23:59:59'},{ts '2011-11-11 23:59:59'})";
connection.createStatement().executeUpdate(sql);

 

或

String sql = "INSERT INTO test_table values(?,?,?)";
PreparedStatement prepStat = conn.prepareStatement(sql);
long dateNumber = new java.util.Date().getTime();
for(int i=0;i<ch.length&&i<str.length;i++){
    prepStmt.setDate(1, new Date(dateNumber));
    prepStmt.setTime(2, new Time(dateNumber));
    prepStmt.setTimestamp(3, new Timestamp(dateNumber));
    prepStmt.executeUpdate();
}
prepStmt.close();

 

3)输出

 

Statement stat = conn.createStatement();
boolean haveResult = stmt.execute("select * from test_table");
if(haveResult){
    do{
        ResultSet result = stmt.getResultSet();
        while(result.next()){
        System.out.println(result.getDate(1)+","+result.getTime(2)+","+result.getTimestamp(3));
    }while(stmt.getMoreResults());
}  

 

(2)调用标量函数(scalar function)
是指仅返回一个数值的函数。在不同数据库中函数名存在差异，JDBC规范提供了标准的名字，并将其转义为数据库相关的名字。
e.g.  {fn left(?,20)}
        {fn user()}
具体支持内容在JDBC规范中
(3)调用存储过程
是在数据库中执行的用数据库相关的语言编写的过程。要调用存储过程，需要使用call转义命令，其中在存储过程没有任何参数的时候，就不用加上括号。另外，应该用=来捕获存储过程的返回值。
e.g.  {call PROC1(?,?)}
        {call PROC2}
        {call ? = PROC3(?)}
(4)外连接(outer join)
两个表的外连接并不要求每个表的行都要根据连接条件进行匹配
e.g. SELECT * FROM {oj books LEFT OUTER JOIN publisher ON books.publisher_id = publisher.publisher_id}
这个查询的执行结果中包含有publisher_id在publisher表中没有任何匹配的书，其中publisher_id为null值的行,就表示不存在任何匹配。
如果使用RIGHT OUTER JOIN就可以包括没有任何匹配图书的出版商，而使用FULL OUTER JOIN可以同时返回这两类没有任何匹配的信息。
使用转义的原因 : 并非所有的数据库对于这些连接都使用标准写法，因此需要使用转义语法。
(5)在LIKE子句中的转义字符
由于在LIKE子句中_和%具有特殊含义(用来匹配一个字符或一个字符序列)，所以不存在任何在字面上使用他们的标准方式。
e.g. 匹配所有包含_字符的字符串的结构
  ... WHERE ? LIKE %!_%{escape '!'}
这里我们将!定义为转义字符，而!_组合表示字符常量下划线_。

5.4 多结果集
在执行存储过程，或在使用允许在单个查询中提交多个SELECT语句的数据库时，一个查询可能会返回多个结果集。
(1)使用execute方法来执行SQL语句。
(2)获取第一个结果集或更新计数。
(3)重复调用getMoreResult方法移动到下一个结果集(这个调用会自动关闭前一个结果集)。
(4)当不存在更多的结果集或更新计数时，完成操作。
注意 : 如果由多个结果集构成的链的下一个是结果集，execute方法和getMoreResult方法将返回true，如果在链的下一项不是更新计数，getUpdateCount方法将返回-1。

 

    try{
            PreparedStatement prepStat = null;
            try{
                String sql = "SELECT * FROM test_table where key=? ";
                prepStat = conn.prepareStatement(sql);
                prepStmt.setString(1, "a");
                boolean hasResult = prepStmt.execute();
                if(hasResult){
                    do{
                        ResultSet result = prepStmt.getResultSet();
                        while(result.next()){
                            System.out.println(result.getInt("id"));
                        }
                    }while(prepStmt.getMoreResults());
                }
            }finally{
                prepStmt.close();
            }
        }catch(SQLException e){
            for(Throwable t : e){
                e.printStackTrace();
            }
        }

 

5.5 获取自动生成键
大多数数据库都支持某种在数据库中对行自动计数的机制，不同提供商提供的数据库之间存在着很大的差异，而这些自动生成键经常用作主键。
JDBC中没有提供独立与提供商的自动生成键的解决方案，但是提供了获取自动生成键的有效途径。
e.g.  向数据库中插入新行，自动生成键的实现

        stmt.executeUpdate(insertStatement, Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = stmt.getGeneratedKeys();
        if(rs.next()){
            int key = rs.getInt(1);
        }