格式转换查询数据库读写文件-优快云博客

本文介绍了一个Java程序，该程序实现从特定文件中读取数据，并利用Oracle数据库进行查询，随后将查询结果转换成特定格式的SQL插入语句并写入新文件。主要涉及文件读写、数据库连接与查询、SQL语句生成等技术。

转换前格式：1894297,716,荆州

转换后格式：insert into TA_INFO(ENTITY_ID,PREFIX)values('GUANGD', '861893425');

查询数据库的数据为：省份，区号

import java.io.File;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Statement;

import com.lj.oracle.vo.AreaRoute;

public class TxtWriter {
	 public static void main(String[] args)   
	    {   
		    String DBDRIVER = "oracle.jdbc.driver.OracleDriver" ;
			String DBURL = "jdbc:oracle:thin:@localhost:1521:ffcs" ;
			String DBUSER = "scott" ;
			String DBPASS = "tiger" ;
			Connection conn = null ;
			PreparedStatement psmt =null;
			ResultSet rs = null;
			StringBuffer sb=new StringBuffer("");   
	        String desFile = "C:\\route.txt";//转换后目录位置
	        AreaRoute ar =null;
				try{
					Class.forName(DBDRIVER);					
				}catch(Exception e){
					System.out.println("oracle驱动加载成功！");
					e.printStackTrace() ;
				}
				
				try {
					conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
					//Statement s = conn.createStatement();
					if (conn != null) {
						System.out.println("你已连接到数据库：" + conn.getCatalog());
						}
					//String sql = "select area_id,entity_id from  area_route where area_id =?";
					//psmt = conn.prepareStatement(sql);
					//ResultSet rs = psmt.executeQuery(sql);
					//1894297,716,荆州
			    	//insert into TA_INFO(ENTITY_ID,PREFIX)values('GUANGD', '861893425');			       
			        //目标文件---需要写入的文件
			        try{   
			            FileReader reader = new FileReader("C:"+File.separator+"189.txt");   
			            BufferedReader br = new BufferedReader(reader);   
			            PrintWriter out = new PrintWriter(
		            	     new FileWriter(
		            	       new File(desFile)));
			            String line = null;   
			           // int i=1,j=1;
			            while((line = br.readLine()) != null) {   
			            	String[] strs = line.split(",");
			            	String route ="86"+strs[0];
				    		String area ="0"+strs[1];
				    		String sql = "select area_id,entity_id from  area_route where area_id =?";
				    		try{
								psmt = conn.prepareStatement(sql);
					    		psmt.setString(1, area);
					    		rs = psmt.executeQuery();//查询结果
					    		if(rs.next()){
					    			ar = new AreaRoute();
					    			ar.setArea_id(rs.getString(1));
					    			ar.setEntity_id(rs.getString(2));
					    		}
					    		rs.close();
				    		}catch(Exception e){
				    			throw e;
				    		}finally{
				    			psmt.close();
				    		}			    		
			                sb.append("insert into TA_INFO(ENTITY_ID,PREFIX)values('"+ar.getEntity_id()+
			                "','"+route+"');" +'\n');  
			                System.out.println("查询数："+i++);
			                out.write("insert into TA_INFO(ENTITY_ID,PREFIX)values('"+ar.getEntity_id()+
			    	                "','"+route+"');" +"\r\n");
			               // System.out.println("写入数："+j++);
			                //System.out.println("测试2:---"+ar.getArea_id()+","+route+","+ar.getEntity_id());
			                ar = null;   			             
			            }  	
			            out.close();
			            br.close();   
			            reader.close(); 	           
			       }catch(Exception e){   
			            e.printStackTrace();   
			       }   			        	   
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					System.out.println("连接数据库失败！");
					e.printStackTrace();
				}				
	    }
}