DbData

package dbconnection;

import java.sql.*;
import java.util.*;

public class DbData {
public static void main(String [] args) throws ClassNotFoundException, SQLException
{
//連接數據庫的驅動
String driver = "oracle.jdbc.driver.OracleDriver";
//連接數據的具體信息,如IP地址接口,SID
String url="jdbc:oracle:thin:@172.24.22.1:1521:g2shjdb";
//String url="jdbc:oracle:thin:@//172.24.22.1:1521/TESTS10";
Object factno = null,brandno = null,bname = null,mark = null;
DbData dbget = new DbData();
Connection conn = dbget.getconnection(driver,url,"nbmis","nbmis");
//設置連接數據庫的sql,? 表示需傳入參數
PreparedStatement pst = conn.prepareStatement("select * from brandwei where fact_no =? and brand_no =?");
//設定第一個參數值
pst.setString(1, "0236");
//設定第二個參數值
pst.setString(2, "NB");
//真正執行查詢語句,并把結果放入ResultSet游標中
ResultSet reader = pst.executeQuery();
while(reader.next())
{
//String bname = (String) ResultSet.getString(1);
factno = reader.getObject(1);
brandno = reader.getObject(2);
bname = reader.getObject(3);
mark = reader.getObject(4);
System.out.print((String)factno + ' ' + (String)brandno + ' ' + (String)bname + ' ' + (String)mark);
}
//關閉游標
reader.close();

//System.out.println("----------------------------");

//delete 掉數據庫中的資料
PreparedStatement pst2 = conn.prepareStatement("delete from brandwei where fact_no =? and brand_no =?");
pst2.setString(1, "0236");
pst2.setString(2, "NB");
//返回刪除了多少行,如沒有刪除到則返回0
int delcount = pst2.executeUpdate();
System.out.println("delete count=" + delcount);

//update資料到數據庫中
PreparedStatement pst3 = conn.prepareStatement("update brandwei set mark_no='W' where fact_no =? and brand_no =?");
pst3.setString(1, "0236");
pst3.setString(2, "WR");
int updcount = pst3.executeUpdate();
System.out.println("update count=" + updcount);

//insert into 資料到數據庫中
PreparedStatement pst4 = conn.prepareStatement("insert into brandwei(fact_no, brand_no, brand_nm, mark_no) values('0236', ?, ?, ?)");
pst4.setString(1, "NB");
pst4.setString(2, "NEW BALANCE");
pst4.setString(3, "test");
int intcount = pst4.executeUpdate();
System.out.println("insert into count=" + intcount);

//設置連接數據庫的sql,? 表示需傳入參數
PreparedStatement pst8 = conn.prepareStatement("select * from brandwei where fact_no =?");
//設定第一個參數值
pst8.setString(1, "0236");
//真正執行查詢語句,并把結果放入ResultSet游標中
ResultSet reader8 = pst8.executeQuery();
while(reader8.next())
{
//String bname = (String) ResultSet.getString(1);
factno = reader8.getObject(1);
brandno = reader8.getObject(2);
bname = reader8.getObject(3);
mark = reader8.getObject(4);
//System.out.print((String)factno + ' ' + (String)brandno + ' ' + (String)bname + ' ' + (String)mark);
}
//關閉游標8
reader8.close();

HashMap<String,Object> hm = new HashMap<String,Object>();
/*向集合中添加指定的键值对*/
hm.put("11", "test map1!");
hm.put("22", "test map222222!");

//遍歷方法1
Iterator<String> iterator = hm.keySet().iterator();
while(iterator.hasNext()) {
System.out.println(hm.get(iterator.next()));
}

//遍歷方法2
Collection<Object> c = hm.values();
for(Iterator<Object> it=c.iterator();it.hasNext();){
System.out.println(it.next());
}

//遍歷方法3
Set<Map.Entry<String, Object>> set=hm.entrySet();
for(Iterator<Map.Entry<String, Object>> it=set.iterator();it.hasNext();){
Map.Entry<String, Object> mapEnter=it.next();
System.out.println("key="+mapEnter.getKey()+",value="+mapEnter.getValue());
}

//試驗多態性
List list = new ArrayList();
ArrayList arrayList = new ArrayList();
List a=new ArrayList();
//list.trimToSize(); //错误，没有该方法。ArrayList特有的方法
list.clear();
arrayList.trimToSize();   //ArrayList里有该方法。

ArrayList<Integer> s = new ArrayList<Integer>();
s.add(600);
s.add(100);
s.add(200);
//使用for-each
for(int lta:s)
{
System.out.println(lta);
}

/*此為023A數據庫factory_old檔資料移到0236數據庫factory_old檔中*/
int li_count;
int li_map = 1;
//設置連接數據庫的sql,? 表示需傳入參數
PreparedStatement apst = conn.prepareStatement("select * from factory_old where trans_code =?");
//設定第一個參數值
apst.setString(1, "N");
//真正執行查詢語句,并把結果放入ResultSet游標中
ResultSet readera = apst.executeQuery();
//ResultSetMetaData類保存了所有ResultSet类对象中关于字段等元数据信息，并提供许多方法来取得这些信息。
ResultSetMetaData rsmd = apst.getMetaData();
    li_count = rsmd.getColumnCount();
    System.out.println("factory_old的列數=" + li_count);
ArrayList<HashMap<String, Object>> dlist = new ArrayList<HashMap<String, Object>>();
while(readera.next())
{
HashMap<String, Object> dmap = new HashMap<String, Object>();
//把資料寫入map當中
for(li_map= 1; li_map <= li_count; li_map++)
{
dmap.put(String.valueOf(li_map),readera.getObject(li_map));
System.out.print(String.valueOf(li_map) + "=" + dmap.get(String.valueOf(li_map)) + "  ");
}
dlist.add(dmap);
System.out.println("");
}
//關閉游標
readera.close();
//disconnection斷掉與數據庫的連接
dbget.disConnection(conn);


//連接第二個數據庫S10
String driver2 = "oracle.jdbc.driver.OracleDriver";
//連接數據的具體信息,如IP地址接口,SID
String url2="jdbc:oracle:thin:@172.24.22.1:1521:g2shjdb";
Connection conn2 = dbget.getconnection(driver2, url2, "s10mis", "tests10mis");
System.out.println("-------------遍歷ArrayList<HashMap<String, Object>> ------");
System.out.println("筆數:" + dlist.size());

//for-each方式处理方式
dbget.forEach(conn2, dlist, li_count);

//用iterator方式
long ll_st = System.currentTimeMillis();
Iterator<HashMap<String, Object>> itdl = dlist.iterator();
while(itdl.hasNext()) {
HashMap<String, Object> dlnMap = itdl.next();
PreparedStatement pstudb = conn2.prepareStatement("update factory_old set fact_nm =?,master_no =?,trans_code =?,stkdist_code =?,fact_f_nm =?" +
" where fact_no =?");
//insert into 資料到數據庫中
PreparedStatement pstdb = conn2.prepareStatement("insert into factory_old(fact_no, fact_nm,master_no,trans_code,stkdist_code,fact_f_nm) " +
   "values(?, ?, ?, ?, ?, ?)");
//把值依順序從dlnMap中取出來
for(li_map= 2; li_map <= li_count; li_map++)
{
System.out.print(dlnMap.get(String.valueOf(li_map)));
pstdb.setString(li_map, (String)dlnMap.get(String.valueOf(li_map)));
pstudb.setString(li_map-1, (String)dlnMap.get(String.valueOf(li_map)));
}
pstdb.setString(1, (String) dlnMap.get("1"));
pstudb.setString(li_count, (String) dlnMap.get("1"));
int updbcount = pstudb.executeUpdate();
System.out.println("update factory_old=" + updbcount);
//先update,無則insert into
if(updbcount <= 0 )
{
int dbintcount = pstdb.executeUpdate();
System.out.println("insert into factory_old=" + dbintcount);
}


/*Iterator<String> itlMap = dlnMap.keySet().iterator();
while(itlMap.hasNext())
{
System.out.println(dlnMap.get(itlMap.next()));
}*/
pstudb.close();
pstdb.close();
System.out.println("----------------------------------------");
}
System.out.println("直接insert使用毫秒=" + (System.currentTimeMillis() - ll_st));

/*long ll_stad = System.currentTimeMillis();
//批量 insert into 資料到數據庫中
PreparedStatement pstad = conn2.prepareStatement("insert into factory_old(fact_no, fact_nm,master_no,trans_code,stkdist_code,fact_f_nm) " +
   "values(?, ?, ?, ?, ?, ?)");
Iterator<HashMap<String, Object>> itdlab = dlist.iterator();
while(itdlab.hasNext()) {
HashMap<String, Object> dlnMap = itdlab.next();
//把值依順序從dlnMap中取出來
for(li_map= 1; li_map <= li_count; li_map++)
{
pstad.setString(li_map, (String)dlnMap.get(String.valueOf(li_map)));
}
//addBatch();执行后暂时记录此条插入
pstad.addBatch();
}
//执行后开始批量插入数据
pstad.executeBatch();
System.out.println("批處理使用毫秒=" + (System.currentTimeMillis() - ll_stad));*/
conn2.commit();
//disconnection斷掉與數據庫2的連接
dbget.disConnection(conn2);
}

public Connection getconnection(String driver, String url, String userid, String password) throws ClassNotFoundException, SQLException
{
/*格式一:  Oracle JDBC Thin using a ServiceName jdbc:oracle:thin:@//<host>:<port>/<service_name>
* Example: jdbc:oracle:thin:@//192.168.2.1:1521/xifenfei 
* 格式二: Oracle JDBC Thin using an SID jdbc:oracle:thin:@<host>:<port>:<SID>
* Example: jdbc:oracle:thin:192.168.2.1:1521:xff --注意这里的格式，@后面有//, 这是与使用SID的主要区别。  
* 格式三：Oracle JDBC Thin using a TNSName jdbc:oracle:thin:@<TNSName>
* Example: jdbc:oracle:thin:@GL
* --Support for TNSNames was added in the driver release 10.2.0.1
*/
//連接數據庫驅動
//String driver = "oracle.jdbc.driver.OracleDriver";
//連接數據的具體信息,如IP地址接口,SID
//String url="jdbc:oracle:thin:@172.24.22.1:1521:g2shjdb";
//String url2="jdbc:oracle:thin:@172.24.22.1:1521:g2shjdb";
Class.forName(driver);
//連接數據庫
Connection conn=DriverManager.getConnection(url, userid, password);
//取消自動提交功能
conn.setAutoCommit(false);
return conn;
}
public void disConnection(Connection conn) throws ClassNotFoundException, SQLException
{
//disconnection斷掉與數據庫的連接
conn.close();
}

public void forEach(Connection conn, ArrayList<HashMap<String, Object>> dlist, int li_count)
{
/*conn 传入的连接,
dlist为传入的数组,
li_count为传入的Table的列数*/
System.out.println("-------forEach-------");
for(HashMap<String, Object> flist:dlist)
{
//依顺序从HashMap中取出资料,但下标要与前面写入的一致
for(int col=1; col<= li_count; col++)
{
System.out.print(flist.get(String.valueOf(col)));
}
System.out.println("");
}
System.out.println("--end-----forEach-------");
}
}