如果你所需要的資料必須經過3到四個資料表查詢之後,才能找到你想要的資料內容時,這個方法就很適用



可以利用java的hashmap來建立與資料相對應的key跟value



custprofile中的addr_city(文字型態)要去找function01的descript所對應的no

再利用no去找funnomapping的old_no所對應的new_no

再利用new_no去找function01tree2006的fun_no所對應的fun_descript



可以分別先建立各自的haspmap

function01haspmap為key=descript,value=no

funnomappinghashmap為key=old_no,value=new_no

function01tree2006為key=fun_no,value=fun_descript

再利用haspmap提供的put與get方法去取得值



速度上比連接三到四個資料表的效率快很多喔^^



附上範例

import java.io.*;

import java.util.*;

import java.text.*;



import com.e104.db.E104Conn;

import com.e104.util.E104Data;

import com.e104.util.E104Mail;



/**

* @file_desc Custprofile_addr 類目轉檔

* @date 2006/11/7

*/



/**

* @author kevin.huang

* @email kevin.huang@104.com.tw

* @description 建檔

*/



public class Custprofile_addr {



/**

* @param args

*/

public static void main(String[] args) throws Exception{

// TODO 自動產生方法 Stub

String log = null;



Global g = new Global();

Local l = new Local();



//資料庫初始設定

//建hashmap用

E104Conn queryInfoDB = null; // 建立會用到的 connection

E104Data dataInfo = null; // 建立會用到的 query容器

String sqlCommand="";



//找custprofile 用

E104Conn fqueryInfoDB = null; // 建立會用到的 connection

E104Data fdataInfo = null; // 建立會用到的 query容器

String fsqlCommand="";







//更新custprofile用

E104Conn duqueryInfoDB = null; // 建立會用到的 connection

E104Data dudataInfo = null; // 建立會用到的 query容器

String dusqlCommand="";



HashMap hashFunNoMapping = new HashMap();

HashMap hashFun01tree = new HashMap();

HashMap hashFun01 = new HashMap();


//HashMap hashFun01Area = new HashMap();



//xml解析

log = g.startparse();

l.startparse();

System.out.println("\nlogpath="+log);





//建立資料夾

File myDir = new File(log);

myDir.mkdir();

System.out.println(myDir+(myDir.isDirectory()?" is":" is not")+" a directory.");



String dirName = null;

String fileName = null;

FileWriter fw = null;

PrintWriter pw = null;



// 初始化郵件

E104Mail logmail = null;



//定義寄送mail物件

//l.MailHost="ex01.e104.com.tw"; //設定SMTP



// 建檔

dirName = log;

fileName = "Hashmap_mapping_" + new SimpleDateFormat("yyyy.MM.dd-'T'HH.mm.ss").format(new Date()) + ".log";

File output = new File (dirName,fileName);

output.createNewFile();

//System.out.println(output.getPath());

fw = new FileWriter(output.getPath(),true);

pw = new PrintWriter(fw);



int count = 0;

int endcount = 500; //設定結束筆數

int chktotal = 0;







try

{

//資料庫連接





queryInfoDB = new E104Conn(g.driver_newfun104,g.database_newfun104,g.username_newfun104,g.password_newfun104,false);





//建立function01tree2006的hashmap

sqlCommand = "SELECT fun_no,fun_descript FROM function01tree2006 ORDER BY fun_no ";

queryInfoDB.setSql(sqlCommand);

dataInfo = queryInfoDB.getData();

for(int i = 0; i < dataInfo.getRowCount(); i++) {

hashFun01tree.put(dataInfo.getCell(i,"fun_no"), dataInfo.getCell(i,"fun_descript"));

}



//建立funnomapping的hashmap

sqlCommand = "SELECT old_no,new_no FROM funnomapping ORDER BY old_no ";

queryInfoDB.setSql(sqlCommand);

dataInfo = queryInfoDB.getData();

for(int i = 0; i < dataInfo.getRowCount(); i++) {

hashFunNoMapping.put(dataInfo.getCell(i,"old_no"), dataInfo.getCell(i,"new_no"));

}



//建立function01的hashmap,但因為會用到descript找代碼,所以descript當key

sqlCommand = "SELECT no,descript FROM function01 ORDER BY no ";

queryInfoDB.setSql(sqlCommand);

dataInfo = queryInfoDB.getData();

for(int i = 0; i < dataInfo.getRowCount(); i++) {

hashFun01.put(dataInfo.getCell(i,"descript"), dataInfo.getCell(i,"no"));

}

dataInfo=null;

} catch(Exception e) {

System.out.println("建構子發生問題: " + e.toString());



}









try

{

while(true)

{

if (count == 0)

{

//資料庫連接

fqueryInfoDB = new E104Conn(g.driver,g.database,g.username,g.password,false);

queryInfoDB = new E104Conn(g.driver,g.database,g.username,g.password,false);

duqueryInfoDB = new E104Conn(g.driver,g.database,g.username,g.password,false);



logmail = new E104Mail();

logmail.setHost(l.MailHost);

logmail.setFrom(l.MailFrom);

logmail.setTo(l.MailTo);

//logmail.setCc(l.MailCc);

logmail.setSubject("Hashmap_Sample...");

logmail.setBody("");



System.out.println("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"啟動,其完成動作如下:");

logmail.appendBody("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"啟動,其完成動作如下:\n");

pw.append("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"啟動,其完成動作如下:\n");



}

//組SQL Command

sqlCommand =" select custno,addr_city,addr_country,addr_no "+

" from custprofile "+

" where (addr_no_land=0 or addr_no_land is null)and rownum<101";
System.out.println(sqlCommand.toString());

fqueryInfoDB.setSql(sqlCommand);

fdataInfo = fqueryInfoDB.getData();



//判斷跑幾筆

int total=fdataInfo.getRowCount();





System.out.println("共"+total+"筆\n資料如下");

logmail.appendBody("共"+total+"筆\n資料如下\n");

pw.append("共"+total+"筆\n資料如下:\n");



for(int i = 0; i < fdataInfo.getRowCount(); i++)

{

count = count + 1;

System.out.println("<----------第"+(i+1)+"筆---------->");

logmail.appendBody("<----------第"+(i+1)+"筆---------->\n");

pw.append("<----------第"+(i+1)+"筆---------->\n");



StringBuffer updSqlCommand = new StringBuffer("");

StringBuffer olddataStr = new StringBuffer("");

StringBuffer errStr = new StringBuffer("");



try

{



//如果原代碼長度不為0,不為空白,長度不為10(表是新類目),才轉新代碼,但是addr_no必需用descript來找新類目



olddataStr.append(fdataInfo.getCell(i,"custno") + ","); //組log字串

//addr_city,addr_country,addr_no

olddataStr.append(fdataInfo.getCell(i,"addr_city") + "," +

fdataInfo.getCell(i,"addr_country") + "," +

fdataInfo.getCell(i,"addr_no") + ","); //組log字串



System.out.println("olddataStr:"+olddataStr.toString());

logmail.appendBody("olddataStr:"+olddataStr.toString()+"\n");

pw.append("olddataStr:"+olddataStr.toString()+"\n");

//判斷addr_city

if ( fdataInfo.getCell(i,"addr_city").trim().equals("") == false )

{

System.out.println("addr_city:"+fdataInfo.getCell(i,"addr_city").toString());

System.out.println("addr_country:"+fdataInfo.getCell(i,"addr_country").toString());

logmail.appendBody("addr_city:"+fdataInfo.getCell(i,"addr_city").toString()+"\n");

logmail.appendBody("addr_country:"+fdataInfo.getCell(i,"addr_country").toString()+"\n");

pw.append("addr_city:"+fdataInfo.getCell(i,"addr_city").toString()+"\n");

pw.append("addr_country:"+fdataInfo.getCell(i,"addr_country").toString()+"\n");



String len ="";





if (fdataInfo.getCell(i,"addr_country").toString().equals("") == true)

{

sqlCommand = " update custprofile set addr_no = 1 , addr_no_taiwan = 1, addr_no_land =1 where "+

" custno = "+fdataInfo.getCell(i,"custno");

System.out.println("sqlcommand:"+sqlCommand);

queryInfoDB.setSql(sqlCommand);

dataInfo = queryInfoDB.getData();

continue;

}

if (fdataInfo.getCell(i,"addr_country").toString().equals("") == false)

{

// System.out.println("chk:"+hashFun01.get(fdataInfo.getCell(i,"addr_country")).toString());

len=hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString();

}



//判斷addr_country

if(fdataInfo.getCell(i,"addr_country").equals("") == false)

{



//6001台灣

// System.out.println("hashfunnomapping:"+hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4));



if (hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4).equals("6001"))

{



System.out.println("6001addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length()));

logmail.appendBody("6001addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length())+"\n");

pw.append("6001addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length())+"\n");



//特殊地區

if(isSpecialCountry(fdataInfo.getCell(i,"addr_country"))) {

updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(0,3) + "',");

updSqlCommand.append("addr_country = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length()) + "',");

updSqlCommand.append("addr_no = " + getSpecialCountryNo(fdataInfo.getCell(i,"addr_country").substring(0,3), fdataInfo.getCell(i,"addr_country").substring(3,len.length())) + ",");

updSqlCommand.append("addr_no_taiwan = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,7) + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

}

else

{

updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(0,3) + "',");

updSqlCommand.append("addr_country = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length()) + "',");

updSqlCommand.append("addr_no = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString() + ",");

updSqlCommand.append("addr_no_taiwan = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,7) + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

}

}

else

{

//6002大陸



if (hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4).equals("6002"))

{

System.out.println("6002addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length()));

logmail.appendBody("6002addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length())+"\n");

pw.append("6002addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length())+"\n");



updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(0,3) + "',");

updSqlCommand.append("addr_country = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length()) + "',");

updSqlCommand.append("addr_no = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString() + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

}

else

//6003非台灣跟大陸

{

System.out.println("6003addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length()));

logmail.appendBody("6003addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length())+"\n");

pw.append("6003addr_country:"+hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3,len.length())+"\n");



updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString() + "',");

updSqlCommand.append("addr_country = '',");

updSqlCommand.append("addr_no = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString() + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

}



}



}





}else{

//判斷addr_city為null時處理

System.out.println("addr_city is null");

logmail.appendBody("addr_city is null\n");

pw.append("addr_city is null\n");



if (fdataInfo.getCell(i,"addr_country").toString().equals("") == true)

{

sqlCommand = " update custprofile set addr_no = 1 , addr_no_taiwan = 1, addr_no_land =1 where "+

" custno = "+fdataInfo.getCell(i,"custno");

System.out.println("sqlcommand:"+sqlCommand);

queryInfoDB.setSql(sqlCommand);

dataInfo = queryInfoDB.getData();

continue;

}



if(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4).equals("6001")) {

if(isSpecialCountry(fdataInfo.getCell(i,"addr_country"))) {

updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(0,3) + "',");

updSqlCommand.append("addr_country = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3) + "',");

updSqlCommand.append("addr_no = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString() + ",");

updSqlCommand.append("addr_no_taiwan = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,7) + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

} else {

updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(0,3) + "',");

updSqlCommand.append("addr_country = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3) + "',");

updSqlCommand.append("addr_no = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString() + ",");

updSqlCommand.append("addr_no_taiwan = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,7) + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

}

} else {

if(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4).equals("6002")) {

updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(0,3) + "',");

updSqlCommand.append("addr_country = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString().substring(3) + "',");

updSqlCommand.append("addr_no = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString() + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

} else {

updSqlCommand.append("addr_city = '" + hashFun01tree.get(hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country")))).toString() + "',");

updSqlCommand.append("addr_country = '',");

updSqlCommand.append("addr_no = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString() + ",");

updSqlCommand.append("addr_no_land = " + hashFunNoMapping.get(hashFun01.get(fdataInfo.getCell(i,"addr_country"))).toString().substring(0,4) + ",");

}

}





}



//update

System.out.println("updsqlcommand:"+updSqlCommand.toString().substring(0,updSqlCommand.toString().length()-1));

logmail.appendBody("updsqlcommand:"+updSqlCommand.toString().substring(0,updSqlCommand.toString().length()-1)+"\n");

pw.append("updsqlcommand:"+updSqlCommand.toString().substring(0,updSqlCommand.toString().length()-1)+"\n");





if(updSqlCommand.toString().trim().equals("")==false) {

System.out.println("UPDATE custprofile SET " +

updSqlCommand.toString().substring(0,updSqlCommand.toString().length()-1) + " " +

"WHERE custno = " + fdataInfo.getCell(i,"custno"));

logmail.appendBody("UPDATE custprofile SET " +

updSqlCommand.toString().substring(0,updSqlCommand.toString().length()-1) + " " +

"WHERE custno = " + fdataInfo.getCell(i,"custno")+"\n");

pw.append("UPDATE custprofile SET " +

updSqlCommand.toString().substring(0,updSqlCommand.toString().length()-1) + " " +

"WHERE custno = " + fdataInfo.getCell(i,"custno")+"\n");



duqueryInfoDB.setSql("UPDATE custprofile SET " +

updSqlCommand.toString().substring(0,updSqlCommand.toString().length()-1) + " " +

"WHERE custno = " + fdataInfo.getCell(i,"custno"));

dudataInfo = duqueryInfoDB.getData();



} else {

errStr.append("[SQL Command轉檔發生問題]");

}



}catch (Exception e)

{

System.out.println(fdataInfo.getCell(i,"custno") + "有問題");

pw.append(olddataStr.toString() + "\n");

System.out.println(e.toString());



sqlCommand = " update custprofile set addr_no = 2 , addr_no_taiwan = 2, addr_no_land =2 where "+

" custno = "+fdataInfo.getCell(i,"custno");

System.out.println("sqlcommand:"+sqlCommand);

queryInfoDB.setSql(sqlCommand);

dataInfo = queryInfoDB.getData();

}

}

//跑完固定筆數之後





pw.flush();

if (count == endcount)

{

System.out.println("第"+endcount+"筆");

logmail.appendBody("第"+endcount+"筆"+"\n");

pw.append("第"+endcount+"筆"+"\n");



System.out.println("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束");

logmail.appendBody("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束\n");

pw.append("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束\n");



logmail.send();

logmail.setBody("");





pw.close();

fw.close();

//建檔

dirName = log;

fileName = "Hashmap_mapping_" + new SimpleDateFormat("yyyy.MM.dd-'T'HH.mm.ss").format(new Date()) + ".log";

output = new File (dirName,fileName);

output.createNewFile();

//System.out.println(output.getPath());

fw = new FileWriter(output.getPath(),true);

pw = new PrintWriter(fw);

count = 0;

chktotal = 0;



queryInfoDB.close();

fqueryInfoDB.close();

duqueryInfoDB.close();

}



if (total == 0)

break;





}



}catch (Exception e)

{

System.out.println("組檔錯誤");

logmail.appendBody("組檔錯誤");

pw.append("組檔錯誤");



}

finally

{

System.out.println("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束");

logmail.appendBody("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束\n");

pw.append("機制於"+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"結束\n");



logmail.send();

logmail.setBody("");



queryInfoDB.close();

fqueryInfoDB.close();

duqueryInfoDB.close();



pw.flush();

pw.close();

}



}







static boolean isSpecialCountry(String country)

{

boolean isRight = false;

if(country.equals("中正區") || country.equals("南區") || country.equals("信義區") || country.equals("中區") ||

country.equals("北區") || country.equals("東區") || country.equals("西區") || country.equals("中山區"))

{

isRight = true;

}

return isRight;

}



static String getSpecialCountryNo(String city, String country) {

String specialNo = "";

if (country.equals("中正區")) {

if(city.equals("台北市")) {

specialNo = "6001001001";

} else {

specialNo = "6001004003";

}

} else if(country.equals("南區")) {

if(city.equals("台中市")) {

specialNo = "6001008003";

} else {

specialNo = "6001014003";

}

} else if(country.equals("信義區")) {

if(city.equals("台北市")) {

specialNo = "6001001007";

} else {

specialNo = "6001004002";

}

} else if(country.equals("中區")) {

if(city.equals("台中市")) {

specialNo = "6001008001";

} else {

specialNo = "6001014001";

}

} else if(country.equals("北區")) {

if(city.equals("台中市")) {

specialNo = "6001008005";

} else {

specialNo = "6001014004";

}

} else if(country.equals("東區")) {

if(city.equals("台南市")) {

specialNo = "6001014002";

} else {

specialNo = "6001008002";

}

} else if(country.equals("西區")) {

if(city.equals("台中市")) {

specialNo = "6001008004";

} else {

specialNo = "6001014001";

}

} else if(country.equals("中山區")) {

if(city.equals("台北市")) {

specialNo = "6001001003";

} else {

specialNo = "6001004004";

}

}

return specialNo;

}



}

狼翔月影 發表在 痞客邦 PIXNET 留言(0) 人氣()