如果你所需要的資料必須經過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
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
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
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 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
{
count = count + 1;
System.out.println("");
logmail.appendBody("\n");
pw.append("\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;
}
}
留言列表
