Sat Apr 09, 2011 5:04 am
Sat Apr 09, 2011 10:53 am
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package mailtest.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import mailtest.config.DateUtils;
import mailtest.customgui.GUILogger;
import mailtest.data.IReservation;
/**
*
* @author Mohamed.Sami
*/
public class DOA {
private static DOA doa;
private GUILogger gUILogger = GUILogger.getInstance();
private DOA() {
}
public void insertReservations(Vector<IReservation> reservationVector) {
if (reservationVector == null || reservationVector.size() == 0) {
JOptionPane.showMessageDialog(null,
"No reservations avaliable to insert to database.",
"Database Error Message",
JOptionPane.ERROR_MESSAGE);
return;
}
ConnectionManager connectionManager = ConnectionManager.getInstance();
Connection connection = connectionManager.getOracleConnection();
if (connection != null) {
try {
Statement statement = (Statement) connection.createStatement();
String query = "select max(ID) from TRKIN";
ResultSet resultSet = statement.executeQuery(query);
resultSet.next();
int success = 0;
int failure = 0;
int duplicate = 0;
long idmax = resultSet.getLong(1);
System.out.println("max = " + idmax);
for (IReservation iReservation : reservationVector) {
try {
resultSet = statement.executeQuery("select * from TRKIN where AIRCODE='" + iReservation.getAirlineCode() + "' and PNR='" +
iReservation.getReservationNumber() + "' ");
if (resultSet.next()) {
duplicate++;
continue;
}
query = "insert into TRKIN (ID,AIRCODE,PNR,ISS_DT,LOC_CD,PAX,SECTOR,FARE,TAX,COMM,NET,"
+ "ITINERARY,SELL_AMT,LPO,CUS_CODE,STATUS,ORIGINAL_DOC,CURRENCY) " + "VALUES (" + (++idmax) + ",'" +
iReservation.getAirlineCode() + "','" + iReservation.getReservationNumber() + "',"
+ "'" + DateUtils.formateDate(iReservation.getReservationDate(), "dd/MMM/yy") + "','0','" + iReservation.getClientName() + "','0'," +
((iReservation.getFare() == null) ? 0 :
iReservation.getFare().getPaymentAmount()) + ",0,0," + iReservation.getPaymentAmount().getPaymentAmount() + ","
+ "'From:" + iReservation.getItineraryFrom() + ((iReservation.getItineraryTo() == null) ? "" : ",To:" + iReservation.getItineraryTo()) + "',"
+ "'0','0','0','0','0','" + iReservation.getPaymentAmount().getCurrency() + "')";
System.out.println("Query to be excuated " + query);
statement.executeUpdate(query);
success++;
} catch (SQLException ex) {
failure++;
Logger.getLogger(DOA.class.getName()).log(Level.SEVERE, null, ex);
JOptionPane.showMessageDialog(null,
" Failed to insert due to SQL error." + ex,
"Database confirmation", JOptionPane.ERROR_MESSAGE);
gUILogger.logInfo("Failed to insert due to SQL error." + ex);
}
}
if(resultSet!=null){
resultSet.close();
}
if(statement!=null)
statement.close();
gUILogger.logInfo(
"Reservation inserting to database is compeleted. Success(" + success + "),Failure(" + failure + ")" + " ,Duplicate(" + duplicate + ").");
} catch (Exception ex) {
Logger.getLogger(DOA.class.getName()).log(Level.SEVERE, null, ex);
JOptionPane.showMessageDialog(null,
" Failed to insert due to error ." + ex,
"Database confirmation", JOptionPane.ERROR_MESSAGE);
gUILogger.logInfo("Failed to insert due to SQL error." + ex);
}
} else {
JOptionPane.showMessageDialog(null,
" couldn't connect to database .",
"Database confirmation", JOptionPane.ERROR_MESSAGE);
}
}
public static DOA getInstance() {
if (doa == null) {
doa = new DOA();
}
return doa;
}
}
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import mailtest.config.PropertiesUtility;
import mailtest.customgui.GUILogger;
/**
*
* @author Mohamed.Sami
*/
public class ConnectionManager {
private String url;
private String username;
private String password;
private String propertiesFile = "./props/database.properties";
private Properties properties = null;
private GUILogger gUILogger = GUILogger.getInstance();
private static Connection oracleConnection = null;
private static ConnectionManager connectionManager;
private ConnectionManager() {
}
public Connection getOracleConnection() {
try {
if (oracleConnection != null) {
return oracleConnection;
}
properties = PropertiesUtility.load(new File(propertiesFile));
if (properties == null) {
JOptionPane.showMessageDialog(null,
"Couldn't load database connection properties.",
"Database Error Message",
JOptionPane.ERROR_MESSAGE);
return null;
}
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver());
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
gUILogger.logInfo("Requesting database connection");
oracleConnection = DriverManager.getConnection(url, username, password);
if (oracleConnection != null) {
gUILogger.logInfo("Connection to database is opened.");
} else {
gUILogger.logInfo("Connection to database failed.\n"
+ "url = " + url + " \n"
+ "username=" + username + ""
+ "password=" + password);
JOptionPane.showMessageDialog(null,
"Connection to database failed.\n"
+ "url = " + url + " \n"
+ "username=" + username + ""
+ "password=" + password,
"Database Error Message.",
JOptionPane.ERROR_MESSAGE);
}
// conn.setAutoCommit(false);
// Statement stmt = conn.createStatement();
// ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
// while (rset.next()) {
// System.out.println(rset.getString(1));
// }
// stmt.close();
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null,
"Connection to database failed." + ex,
"Database Error Message :",
JOptionPane.ERROR_MESSAGE);
Logger.getLogger(ConnectionManager.class.getName()).log(Level.SEVERE, null, ex);
} catch (Exception ex) {
ex.printStackTrace();
JOptionPane.showMessageDialog(null,
"Connection to database failed." + ex,
"Database Error Message :",
JOptionPane.ERROR_MESSAGE);
Logger.getLogger(ConnectionManager.class.getName()).log(Level.SEVERE, null, ex);
}
return oracleConnection;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public void finalize() {
// if (oracleConnection != null) {
// try {
// oracleConnection.close();
//
//
//
// } catch (SQLException ex) {
// Logger.getLogger(ConnectionManager.class.getName()).log(Level.SEVERE, null, ex);
// }
//
//
// }
}
public static ConnectionManager getInstance() {
if (connectionManager == null) {
connectionManager = new ConnectionManager();
}
return connectionManager;
}
}
Codemiles.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com
Powered by phpBB © phpBB Group.