Java connect to oracle

Java connect to oracle[@more@]

Fun : FB_DataSource:

-------------------------------

package rpt_finalbill;

import java.sql.*;

import oracle.jdbc.pool.*;

public class FB_DataSource{

private static OracleDataSource ds;
/*
private static final String DRIVER_TYPE = "thin";
private static final String SERVER_NAME = "172.20.105.101";
private static final int PORT_NUMBER = 31522;
private static final String DATABASE_NAME = "bill_bi";
*/
private static final String USER = "retbatch";
private static final String PASSWORD = "retbatch";

/*//Prod//private static final String CONNECTION_URL =
"jdbc:oracle:thin:@(DESCRIPTION ="
+"(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = billrpt1)(PORT = 31523))) (CONNECT_DATA = (SERVICE_NAME = gnvprd) ) "
+")";*/
private static final String CONNECTION_URL =
"jdbc:oracle:thin:@(DESCRIPTION ="
+"(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 175.30.100.58) (PORT = 1523))) (CONNECT_DATA = (SERVICE_NAME = bill_bi) ) "
+")";
public FB_DataSource () throws SQLException
{
ds = new OracleDataSource();
/*
ds.setDriverType(DRIVER_TYPE);
ds.setServerName(SERVER_NAME);
ds.setPortNumber(PORT_NUMBER);
ds.setDatabaseName(DATABASE_NAME);
*/
ds.setURL(CONNECTION_URL);
ds.setUser(USER);
ds.setPassword(PASSWORD);

}
/*
public int getPortNumber()
{
return PORT_NUMBER;
}

public String getServerName()
{
return SERVER_NAME;
}

public String getDatabaseName()
{
return DATABASE_NAME;
}
public String getDriverType()
{
return DRIVER_TYPE;
}
*/
public String getUserName()
{
return USER;
}

public Connection getConnection()
throws SQLException
{
return ds.getConnection();
}

public String getConnectionDetails()
{
StringBuffer sbuf = new StringBuffer(200);

sbuf.append("Class : ").append(this.getClass().getName())
.append("nURL: ").append(CONNECTION_URL);
/*
.append("nServer name: ").append(getServerName())
.append("nPort: ").append(String.valueOf(getPortNumber()))
.append("nDatabase name: ").append(getDatabaseName())
.append("nUser name: ").append(getUserName())
;
*/
return sbuf.toString();
}

public static void main(String args[]) throws SQLException
{
FB_DataSource dataSource = new FB_DataSource();
System.out.println(dataSource.getConnectionDetails());
dataSource.getConnection();
}

}

===================

public void preLoad(HttpServletRequest request, HttpServletResponse response,Hashtable requestValue,String userName)throws Exception{
BILLConnectionPool ds = null;
Connection conn = null;
String uploadMessage = null;
String uploadStatus = null;
String uploadFile = null;
try{
ds = new BILLConnectionPool();
conn = ds.getBill04Connection();
int total = 0;
String account_num = null;
String account_name = null;
boolean flag = false;
String mobile = null;
String psid = null;
String cost_centre = null;
String plan = null;
String ref_name = null;
String total_charge = null;
String invoice_date = null;
String invoice_num = null;

ArrayList a = (ArrayList)requestValue.get("uploadFile");
String ofileName =(String)requestValue.get("fileName");
request.setAttribute("uploadFile",ofileName);
String fileName = ofileName.substring(ofileName.lastIndexOf("")+1);
//System.out.println("fileName: "+ fileName);

String sql1 = "DELETE FROM GENEVA_ADMIN.SMC_COST_CENTRE_STATEMENT_RPT";
PreparedStatement pstmt = conn.prepareStatement(sql1);
//pstmt.execute();
//pstmt.close();

String sql3 = "COMMIT";
PreparedStatement pstmt3 = conn.prepareStatement(sql3);
//pstmt3.execute();

String sql2 = "INSERT INTO GENEVA_ADMIN.SMC_COST_CENTRE_STATEMENT_RPT(ACCOUNT_NUM,ACCOUNT_NAME,MOBILE,PSID,COST_CENTRE,PLAN,REF_NAME,TOTAL_CHARGE,INVOiCE_NUM,INVOICE_DATE,CREATED_BY)"+
" VALUES( ?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement pstmt2 = conn.prepareStatement(sql2);


for(int i=0;i String readLine = (String)a.get(i);
if(readLine.length() == 0){
continue;
}
String temp[] = readLine.split(",");

if(temp[0].equals("Account No.:")){
account_num = temp[1];
continue;
}
else if(temp[0].equals("Name:")){
account_name = temp[1];
continue;
}
else if(temp[0].equals("Invoice no.:")){
invoice_num = temp[1];
invoice_date = temp[1].substring(0,8);
boolean checkDateFlag = false;
checkDateFlag = checkDate(invoice_date);

String account_num_check = temp[1].substring(temp[1].length()-8);
if(!account_num.equals(account_num_check)){
uploadStatus = "False";
uploadMessage = "Error: Account No.("+account_num+") does not match the last eight digits of Invoice No.("+account_num_check+")";
break;
}
else if(! checkDateFlag){
uploadStatus = "False";
uploadMessage = "Error: Wrong format of Invoice Date("+invoice_date+")";
break;
}
else{
pstmt.execute();
pstmt.close();
pstmt3.execute();
continue;
}
}
else if(temp[0].equals("Mobile")){
flag = true;
continue;
}
else if(flag && (temp[0] != null && !"".equals(temp[0]))){
mobile = temp[0];
psid = temp[1];
cost_centre = temp[2];
plan = temp[3];
ref_name = temp[4];
total_charge = temp[5];

pstmt2.setString(1,account_num);
pstmt2.setString(2,account_name);
pstmt2.setString(3,mobile);
pstmt2.setString(4,psid);
pstmt2.setString(5,cost_centre);
pstmt2.setString(6,plan);
pstmt2.setString(7,ref_name);
pstmt2.setString(8,total_charge);
pstmt2.setString(9,invoice_num);
pstmt2.setString(10,invoice_date);
pstmt2.setString(11,userName);
pstmt2.execute();

total = total +1;
}
}
pstmt2.close();
pstmt3.execute();
pstmt3.close();
if(!"False".equals(uploadStatus)){
uploadStatus = "True";
uploadMessage = "'"+fileName+"' is uploaded successfully.
"+total+" records are uploaded.";
}
System.out.println("Total: "+total);
request.setAttribute("uploadMessage",uploadMessage);
request.setAttribute("uploadStatus",uploadStatus);
request.getRequestDispatcher("SCB/Cost_centre_statement.jsp").forward(request,response);
return;
}catch (Exception e){
System.out.print("Error: "+e.toString());
//throw new RuntimeException(e);
request.setAttribute("uploadMessage","Error: "+e.toString());
request.setAttribute("uploadStatus","False");
request.getRequestDispatcher("SCB/Cost_centre_statement.jsp").forward(request,response);
return;
}
finally{
conn.close();
}
}

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/640706/viewspace-1047869/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/640706/viewspace-1047869/