if (resul){
ResultSet rset = stmt.getResultSet();
}else{
int updateCount = stmt.getUpdateCount();
}
注意:boolean result = stmt.execute(sql);执行之后,就已经执行了语句了,所以后面不用再次执行,可以直接得到结果。
import java.sql.Connection; |
import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class db_select { public static int db_49(int id, FileWriter fw, String tablename, Connection con) { String b = ""; int num = 0; int one = 100; Long old_time = System.currentTimeMillis(); Long new_time = System.currentTimeMillis(); String sql = "select to_char(t.create_time,'yyyy/mm/dd hh24:mi:ss') 时间 ,t.desmobile 号码 ," + "replace(replace(replace(t.content,chr(9),' '),chr(10),' '),chr(13),' ') 内容 ," + "t.rcode 状态 from " + tablename + " t " + "where t.id=" + id + " "; try { PreparedStatement stmt = (PreparedStatement) con.prepareStatement( sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); //设置连接属性statement以TYPE_FORWARD_ONLY打开 stmt.setFetchSize(1000);//设置fetch size参数,表示采用服务器端游标,每次从服务器取fetch_size条数据。 stmt.setFetchDirection(ResultSet.FETCH_REVERSE); ResultSet rsc = stmt.executeQuery(); String create_times = ""; String desmobile = ""; String content = ""; String rcode = ""; while (rsc.next()) { num += 1; create_times = rsc.getString(1); desmobile = rsc.getString(2); content = rsc.getString(3); rcode = rsc.getString(4); b = b + create_times + "\t" + desmobile + "\t" + content + "\t" + rcode + "\r\n"; if (num % one == 0) { fw.write(b); fw.flush(); old_time = new_time; new_time = System.currentTimeMillis(); System.out.println("写入---------" + num + "===" + (new_time - old_time)); b = ""; } } fw.write(b); fw.flush(); rsc.close(); stmt.close(); } catch (SQLException e) { System.out.println("查询异常"); e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return num; } } |
import java.io.BufferedReader; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileWriter; import java.io.IOException; import java.io.InputStreamReader; import java.sql.Connection; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.Scanner; public class output_mingxi { /** * @param args */ public static void main(String[] args) { int username_id = "xxxx"; String username_49 = "xxxxx"; String passwd = "xxxx"; String driverClassName = "oracle.jdbc.driver.OracleDriver"; String dburl_49 = "jdbc:oracle:thin:@127.0.0.1:49:xxxx"; Connection oraConn49 = OracleDBa.getConnection(driverClassName, dburl_49, username_49, passwd); Long date1 = System.currentTimeMillis(); int num = 0; try { File newFile1 = new File("E:\\" + username_reg + "_mingxi.txt"); FileWriter fw = new FileWriter(newFile1, true); System.out.println("开始查询:"); num = db_select.db_49(username_id,fw, "1801", oraConn49); fw.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 关闭链接 OracleDBa.CloseConnection(oraConn49); Long date2 = System.currentTimeMillis(); System.out.println("共查询" + num + "个号码用时(秒)=" + ((date2 - date1) / 1000) + "折合分钟是:=" + ((date2 - date1) / 60000)); } } |
推荐另外两篇来自阿里巴巴叶正盛的文章我转载的:
http://blog.csdn.net/chenyechao/article/details/9303979
这篇文章是我解决问题以后才看到的,上面已经说明了MySQL JDBC的setFetchSize的使用。
另外一篇:面向程序员的数据库访问性能优化法则 http://blog.csdn.net/yzsind/article/details/6059209