從數(shù)據(jù)庫導出大量數(shù)據(jù)記錄保存到文件的方法和實例

數(shù)據(jù)庫腳本：

-- Table "t_test" DDL

CREATE TABLE `t_test` (
? `id` int(11) NOT NULL AUTO_INCREMENT,
? `title` varchar(255) DEFAULT NULL,
? `createTime` bigint(20) DEFAULT NULL,
? PRIMARY KEY (`id`)
) ENGINE=InnoDB? DEFAULT CHARSET=utf8;

?

代碼:

?

package?com.yanek.test;

import?java.io.BufferedReader;
import?java.io.File;
import?java.io.FileOutputStream;
import?java.io.FileReader;
import?java.io.IOException;
import?java.io.OutputStreamWriter;
import?java.sql.Connection;
import?java.sql.DriverManager;
import?java.sql.PreparedStatement;
import?java.sql.ResultSet;
import?java.sql.SQLException;
import?java.sql.Statement;


public?class?TestDB?{
	
	
	public?static?void?main(String[]?args)?{
		
		
		Test();??//?生成測試數(shù)據(jù)
		//Exp();
		//Exp(0);
		//System.out.println(readText("/opt/id.txt"));
	}
	
	/**
	?*?導出數(shù)據(jù)
	?*/
	?public?static?void?Exp()?{
		?
			Connection?Conn=null;

			try?{
				
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				String?jdbcUrl?=?"jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";
				String?jdbcUsername?=?"root";
				String?jdbcPassword?=?"root";
				Conn?=?DriverManager.getConnection(jdbcUrl,?jdbcUsername,?jdbcPassword);
				
				System.out.println("conn"+Conn);
				
				Exp(Conn);
				

			}?catch?(SQLException?e)?{
				e.printStackTrace();
			}
			catch?(InstantiationException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}?catch?(IllegalAccessException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}?catch?(ClassNotFoundException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}
			finally
			{
	
				try?{
					Conn.close();
				}?catch?(SQLException?e)?{
					//?TODO?Auto-generated?catch?block
					e.printStackTrace();
				}
			}
	
		?
		?
	?}
	?
	?public?static?void?Exp(int?startid)?{
		?
			Connection?Conn=null;

			try?{
				
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				String?jdbcUrl?=?"jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";
				String?jdbcUsername?=?"root";
				String?jdbcPassword?=?"root";
				Conn?=?DriverManager.getConnection(jdbcUrl,?jdbcUsername,?jdbcPassword);
				
				System.out.println("conn"+Conn);
				
				Exp(Conn,startid);
				

			}?catch?(SQLException?e)?{
				e.printStackTrace();
			}
			catch?(InstantiationException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}?catch?(IllegalAccessException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}?catch?(ClassNotFoundException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}
			finally
			{
	
				try?{
					Conn.close();
				}?catch?(SQLException?e)?{
					//?TODO?Auto-generated?catch?block
					e.printStackTrace();
				}
			}
	
		?
		?
	?}
	?
	?/**
	??*?導出從startid開始的數(shù)據(jù)
	??*?@param?conn
	??*?@param?start_id
	??*/
	?public?static?void?Exp(Connection?conn,int?start_id)?{
		?
			int?counter?=?0;
			int?startid=start_id;
			boolean?flag?=?true;
			while?(flag)?{
				flag?=?false;
				String?Sql?=?"SELECT?*?FROM?t_test?WHERE?id>"
						+?startid?+?"?order?by?id?asc?LIMIT?50";

				System.out.println("sql==="?+?Sql);
				try?{
					Statement?stmt?=?conn.createStatement();
					ResultSet?rs?=?stmt.executeQuery(Sql);

						while?(rs.next())?{
							flag?=?true;
							int?id?=?rs.getInt("id");
							String?title?=?rs.getString("title");
							startid?=?id?;

							counter++;
							
							writeContent(counter+"--id--"+id+"--title-"+title+"rn",?"/opt/","log.txt",true);?
							
							System.out.println("i="+counter+"--id--"+id+"--title-"+title);
							
						}
	
	
					
					rs.close();
					stmt.close();
				}?catch?(SQLException?e)?{
					e.printStackTrace();
				}
			}
			
			writeContent(""+startid,?"/opt/","id.txt",false);?

	?}
	
	?
	?
	?/**
	??*?導出一小時內(nèi)的數(shù)據(jù)
	??*?@param?conn
	??*/
	
	?public?static?void?Exp(Connection?conn)?{
		?
			int?counter?=?0;
			//一小時內(nèi)的數(shù)據(jù)
			Long?timestamp?=?System.currentTimeMillis()?-?(60?*?60?*?1000);
			boolean?flag?=?true;
			while?(flag)?{
				flag?=?false;
				String?Sql?=?"SELECT?*?FROM?t_test?WHERE?createTime>"
						+?timestamp?+?"?LIMIT?50";

				System.out.println("sql==="?+?Sql);
				try?{
					Statement?stmt?=?conn.createStatement();
					ResultSet?rs?=?stmt.executeQuery(Sql);
					while?(rs.next())?{
						flag?=?true;
						int?id?=?rs.getInt("id");
						String?title?=?rs.getString("title");
						Long?lastmodifytime?=?rs.getLong("createTime");
						timestamp?=?lastmodifytime;

						counter++;
						
						System.out.println("i="+counter+"--id--"+id+"--title-"+title);
						
					}
					rs.close();
					stmt.close();
				}?catch?(SQLException?e)?{
					e.printStackTrace();
				}
			}
		?
		?
		?
		?
	?}
	
	
	?public?static?void?Test()?{
	
			Connection?Conn=null;

			try?{
				
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				String?jdbcUrl?=?"jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";
				String?jdbcUsername?=?"root";
				String?jdbcPassword?=?"root";
				Conn?=?DriverManager.getConnection(jdbcUrl,?jdbcUsername,?jdbcPassword);
				
				System.out.println("conn"+Conn);
				
				for(int?i=1;i<=10000;i++)
				{
					add(Conn,"testTitle"+i+"-"+System.currentTimeMillis());
				}

			}?catch?(SQLException?e)?{
				e.printStackTrace();
			}
			catch?(InstantiationException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}?catch?(IllegalAccessException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}?catch?(ClassNotFoundException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}
			finally
			{
	
				try?{
					Conn.close();
				}?catch?(SQLException?e)?{
					//?TODO?Auto-generated?catch?block
					e.printStackTrace();
				}
			}
	
		}

	?public?static?void?add(Connection?conn,String?title)
	??{
	????	PreparedStatement?pstmt?=?null;
			String?insert_sql?=?"insert?into?t_test(title,createTime)?values?(?,?)";
			
			System.out.println("sql="+insert_sql);
			try?{
				pstmt?=?conn.prepareStatement(insert_sql);
				pstmt.setString(1,title);
				pstmt.setLong(2,System.currentTimeMillis());
				int?ret?=?pstmt.executeUpdate();
		
			}?catch?(SQLException?e)?{
				//?TODO?Auto-generated?catch?block
				e.printStackTrace();
			}
			finally{
				try?{
					pstmt.close();
				}?catch?(SQLException?e)?{
					//?TODO?Auto-generated?catch?block
					e.printStackTrace();
				}	
			}

	????}
	?
	?/**
		?*?寫入內(nèi)容到文件
		?*?
		?*?@param?number
		?*?@param?filename
		?*?@return
		?*/
		public?static?boolean?writeContent(String?c,?String?dirname,String?filename,boolean?isAppend)?{
			
			File?f=new?File(dirname);
			if?(!f.exists())
			{
					f.mkdirs();
			}
			
			try?{
				FileOutputStream?fos?=?new?FileOutputStream(?dirname+File.separator+filename,isAppend);
				OutputStreamWriter?writer?=?new?OutputStreamWriter(fos);
				writer.write(c);
				writer.close();
				fos.close();
			}?catch?(IOException?e)?{
				e.printStackTrace();
				return?false;
			}
			return?true;
		}
		
	?
		/**
		?*?從文件讀取內(nèi)容
		?*?
		?*?@param?filename
		?*?@return
		?*/
		public?static?String?readText(String?filename)?{
			String?content?=?"";
			try?{
				File?file?=?new?File(filename);
				if?(file.exists())?{
					FileReader?fr?=?new?FileReader(file);
					BufferedReader?br?=?new?BufferedReader(fr);
					String?str?=?"";
					String?newline?=?"";
					while?((str?=?br.readLine())?!=?null)?{
						content?+=?newline?+?str;
						newline?=?"n";
					}
					br.close();
					fr.close();
				}
			}?catch?(IOException?e)?{
				e.printStackTrace();
			}
			return?content;
		}
}

?

基本內(nèi)思想: 就是通過記錄開始記錄id,執(zhí)行多次sql來處理. 由于大數(shù)據(jù)量所以不能使用一條sql語句來輸出.否則會內(nèi)存不足導致錯誤.

?

主要用途: 可以使用在做接口開發(fā)時,給第三方提供數(shù)據(jù)增量輸出的場景使用.

?

有問題可以聯(lián)系qq: 1046011462