Java学习笔记-JDBC 2 博客分类： Java学习笔记 JDBCJavaSQLperformance

Executing SQL Statements

 

Then call the executeUpdate method of the Statement class:

 

stat.executeUpdate(command);

The executeUpdate method returns a count of the rows that were affected by the SQL statement, or zero for statements that do not return a row count.

 

The executeQuery method returns an object of type ResultSet that you use to walk through the result one row at a time.

ResultSet rs = stat.executeQuery("SELECT * FROM Books")

 

When inspecting an individual row, you will want to know the contents of the fields. A large number of accessor methods give you this information.

 

String isbn = rs.getString(1);
double price = rs.getDouble("Price");

 

rs.getString(1) returns the value of the first column in the current row. Using the numeric argument is a bit more efficient, but the string arguments make the code easier to read and maintain.

 

boolean execute(String sqlStatement)

 

executes the SQL statement specified by the string. Multiple result sets and update counts may be produced. Returns true if the first result is a result set, false otherwise. Call getResultSet or getUpdateCount to retrieve the first result.

 

Prepared Statements

 

PreparedStatement publisherQueryStat = conn.prepareStatement(publisherQuery);

 Rather than build a separate query statement every time the user launches such a query, we can prepare a query with a host variable and use it many times, each time filling in a different string for the variable. That technique benefits performance. Whenever the database executes a query, it first computes a strategy of how to efficiently execute the query. By preparing the query and reusing it, you ensure that the planning step is done only once.

 

Reading and Writing LOBs

 

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

 

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