/*
* Insert a PurchaseItem Returns true if the insert is successful; false
* otherwise.
*/
public boolean insertPurchaseItem(Integer pirid, Integer piupc, Integer piquantity) {
try {
ps = con.prepareStatement("INSERT INTO purchaseitem VALUES (?,?,?)");
ps.setInt(1, pirid.intValue());
ps.setInt(2, piupc.intValue());
ps.setInt(3, piquantity.intValue());
ps.executeUpdate();
if (commit) con.commit();
return true;
} catch (SQLException ex) {
ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
fireExceptionGenerated(event);
try {
con.rollback();
return false;
} catch (SQLException ex2) {
event = new ExceptionEvent(this, ex2.getMessage());
fireExceptionGenerated(event);
return false;
}
}
}
/**
* Used for looking up information in DB
*
* @param Query string
* @param what the user is searching for
* @return The result of the query in a ResultSet
* @throws SQLException - if you screwed up the query. Jerk.
*/
public ResultSet sql(String statement, SQLType type) throws SQLException {
try {
Statement stmt = con.createStatement();
ResultSet rs;
if (type == SQLType.query) {
rs = stmt.executeQuery(statement);
return rs;
} else if (type == SQLType.insert) {
stmt.executeUpdate(statement);
} else if (type == SQLType.delete) {
stmt.execute(statement);
}
return null;
} catch (SQLException ex) {
System.out.println("Message: " + ex.getMessage());
try {
// undo the insert
con.rollback();
} catch (SQLException ex2) {
System.out.println("Message: " + ex2.getMessage());
throw ex2;
}
throw ex;
}
}
/*
* Deletes a PurchaseItem tuple. Returns true if the delete is successful; false
* otherwise.
*/
public boolean deletePurchaseItem(Integer rid, Integer upc) {
try {
ps = con.prepareStatement("DELETE FROM purchaseitem WHERE receiptid = ? AND upc = ?");
ps.setInt(1, rid.intValue());
ps.setInt(2, upc.intValue());
ps.executeUpdate();
if (commit) con.commit();
return true;
} catch (SQLException ex) {
ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
fireExceptionGenerated(event);
try {
con.rollback();
return false;
} catch (SQLException ex2) {
event = new ExceptionEvent(this, ex2.getMessage());
fireExceptionGenerated(event);
return false;
}
}
}
/*
* Manager sets the delivered date for an order.
* Returns true if the change has been successfully made, returns false otherwise.
*/
public boolean setDeliveredDate(int receiptID, Date deliveredDate) {
try {
ps =
con.prepareStatement(
"UPDATE Purchase " + "SET deliveredDate = ? " + "WHERE receiptID = ?");
ps.setDate(1, deliveredDate);
ps.setInt(2, receiptID);
ps.executeUpdate();
con.commit();
return true;
} catch (SQLException ex) {
ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
fireExceptionGenerated(event);
try {
con.rollback();
return false;
} catch (SQLException ex2) {
event = new ExceptionEvent(this, ex2.getMessage());
fireExceptionGenerated(event);
return false;
}
}
}
@Override
public List<Ordine> getOrdiniPerCliente(Cliente cliente) throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
Ordine o = null;
List<Ordine> lo = new LinkedList<Ordine>();
try {
String str = "select codice,data,stato,id from ordini where cliente=?";
statement = connection.prepareStatement(str);
statement.setInt(1, cliente.getId());
ResultSet result = statement.executeQuery();
while (result.next()) {
o = new Ordine();
o.setCliente(cliente);
o.setCodiceOrdine(result.getString("codice"));
o.setData(result.getDate("data"));
o.setStato(result.getString("stato"));
o.setId(result.getInt("id"));
lo.add(o);
// da completare per restituire ordine completo di righe
}
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return lo;
}
@Override
public int registraOrdine(Ordine ordine) throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
int i;
if (this.getOrdineByCodice(ordine.getCodiceOrdine()) != null)
throw new PersistenceException("Ordine gia presente");
try {
String str = "insert into ordini (id,codice,data,stato,cliente) values (?,?,?,?,?)";
statement = connection.prepareStatement(str);
IdBroker id = new IdBrokerPostgres();
i = id.getId();
statement.setInt(1, i);
statement.setString(
2, new ClienteDAOImpl().getClienteById(ordine.getCliente().getId()).getNome() + i);
statement.setDate(3, new java.sql.Date(ordine.getData().getTime()));
statement.setString(4, ordine.getStato());
statement.setInt(5, ordine.getCliente().getId());
statement.executeUpdate();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return i;
}
/*
* Updates a purchaseItem tuple.
* Returns true if the update is successful; false otherwise.
*
* All arguments cannot be null.
*/
public boolean updatePurchaseItem(int receiptID, int upc, int quantity) {
try {
ps =
con.prepareStatement(
"UPDATE purchaseitem SET quantity = ? WHERE receiptID = ? AND upc = ?");
ps.setInt(1, quantity);
ps.setInt(2, receiptID);
ps.setInt(3, upc);
ps.executeUpdate();
if (commit) con.commit();
return true;
} catch (SQLException ex) {
ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
fireExceptionGenerated(event);
try {
con.rollback();
return false;
} catch (SQLException ex2) {
event = new ExceptionEvent(this, ex2.getMessage());
fireExceptionGenerated(event);
return false;
}
}
}
@Override
public void delete(Integer deptno) {
int updateCount_EMPs = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
// 1●設定於 pstm.executeUpdate()之前
con.setAutoCommit(false);
// 先刪除員工
pstmt = con.prepareStatement(DELETE_EMPs);
pstmt.setInt(1, deptno);
updateCount_EMPs = pstmt.executeUpdate();
// 再刪除部門
pstmt = con.prepareStatement(DELETE_DEPT);
pstmt.setInt(1, deptno);
pstmt.executeUpdate();
// 2●設定於 pstm.executeUpdate()之後
con.commit();
con.setAutoCommit(true);
System.out.println("刪除部門編號" + deptno + "時,共有員工" + updateCount_EMPs + "人同時被刪除");
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
if (con != null) {
try {
// 3●設定於當有exception發生時之catch區塊內
con.rollback();
} catch (SQLException excep) {
throw new RuntimeException("rollback error occured. " + excep.getMessage());
}
}
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
}
/**
* Obtenir une nouvelle connexion a la BD, en fonction des parametres contenus dans un fichier de
* configuration.
*
* @return une nouvelle connexion a la BD
* @throws ExceptionConnexion si la connexion a echoue
*/
public static Connection getConnexion(String login, String mdp) throws ExceptionConnexion {
Connection conn = null;
try {
// lecture des parametres de connexion dans connection.conf
Properties p = new Properties();
InputStream is = null;
is = new FileInputStream(utils.Constantes.Home + utils.Constantes.Config);
p.load(is);
String url = p.getProperty("url");
String driver = p.getProperty("driver");
Class.forName(driver);
// hopper@UFR, Oracle
conn = DriverManager.getConnection(url, login, mdp);
conn.setAutoCommit(false);
} catch (SQLException e) {
System.out.println("Connexion impossible : " + e.getMessage()); // handle any errors
System.out.println("SQLException: " + e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("VendorError: " + e.getErrorCode());
} catch (IOException e) {
throw new ExceptionConnexion("fichier conf illisible \n" + e.getMessage());
} catch (ClassNotFoundException e) {
throw new ExceptionConnexion("problème d'identification du pilote \n" + e.getMessage());
}
return conn;
}
@Override
public List<Ordine> listaOrdini() throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
List<Ordine> ordini = null;
Ordine ordine = null;
try {
String str =
"select ordini.codice as codice,stato,data,cliente,id "
+ "from ordini left outer join clienti on ordini.cliente=clienti.codice";
statement = connection.prepareStatement(str);
ResultSet result = statement.executeQuery();
ordini = new ArrayList<Ordine>();
while (result.next()) {
ordine = new Ordine();
ClienteDAOImpl cliente = new ClienteDAOImpl();
ordine.setCliente(cliente.getClienteById(result.getInt("cliente")));
ordine.setCodiceOrdine(result.getString("codice"));
ordine.setData(new java.util.Date(result.getDate("data").getDate()));
ordine.setStato(result.getString("stato"));
ordine.setId(result.getInt("id"));
ordini.add(ordine);
}
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return ordini;
}
/**
* @author gdulski
* @since nov 26, 2003
* @param ArrayList of updated CustomerValue objects containing new PIN, new OptInStatus, new date
* updated and new lastUpdateUserId.
* @return boolean true if update successful otherwise return false
* <p>Update the DAOptInCustomer table in Styx for each customer in the input ArrayList.
*/
protected boolean updateCustomers(ArrayList _rowset) throws OptInCustomerException {
boolean updateOK = false;
// dB connection
init();
// update each customer
for (int _inx = 0; _inx < _rowset.size(); _inx++) {
CustomerValue _customer = (CustomerValue) _rowset.get(_inx);
try {
updateOK = update(_customer);
if (!updateOK) break;
} catch (SQLException _e) {
AppLog.writeErrorLog(
"SQL Exception when preparing update SQL "
+ _customer.getCAPnumber()
+ " message : "
+ _e.getMessage());
System.out.println(
"SQL Exception when preparing update SQL "
+ _customer.getCAPnumber()
+ " msg "
+ _e.getMessage());
throw new OptInCustomerException(
"SQL Exception when preparing update SQL "
+ _customer.getCAPnumber()
+ " msg "
+ _e.getMessage());
}
_customer = null;
}
return true;
}
/**
* Description of the Method
*
* @param db Description of the Parameter
* @return Description of the Return Value
* @throws SQLException Description of the Exception
*/
public DependencyList processDependencies(Connection db) throws SQLException {
DependencyList dependencyList = new DependencyList();
// Check for orders associated with this customer product
try {
PreparedStatement pst =
db.prepareStatement(
"SELECT COUNT(DISTINCT(order_id)) as orderscount "
+ "FROM customer_product_history "
+ "WHERE customer_product_id = ? ");
pst.setInt(1, this.getId());
ResultSet rs = pst.executeQuery();
if (rs.next()) {
int orderscount = rs.getInt("orderscount");
if (orderscount != 0) {
Dependency thisDependency = new Dependency();
thisDependency.setName("orders");
thisDependency.setCount(orderscount);
thisDependency.setCanDelete(true);
dependencyList.add(thisDependency);
}
}
rs.close();
pst.close();
} catch (SQLException e) {
throw new SQLException(e.getMessage());
}
// check for quotes associated with this customer product
try {
PreparedStatement pst =
db.prepareStatement(
"SELECT COUNT(*) as quotescount FROM order_entry "
+ "WHERE quote_id > -1 AND order_id IN "
+ "(SELECT DISTINCT(order_id) "
+ " FROM customer_product_history "
+ " WHERE customer_product_id = ? )");
pst.setInt(1, this.getId());
ResultSet rs = pst.executeQuery();
if (rs.next()) {
int quotescount = rs.getInt("quotescount");
if (quotescount != 0) {
Dependency thisDependency = new Dependency();
thisDependency.setName("quotes");
thisDependency.setCount(quotescount);
thisDependency.setCanDelete(true);
dependencyList.add(thisDependency);
}
}
rs.close();
pst.close();
} catch (SQLException e) {
throw new SQLException(e.getMessage());
}
return dependencyList;
}
@RequestMapping(method = RequestMethod.POST)
public @ResponseBody DataShop getShopInJSON(
@RequestParam("lictype") String lictype, HttpSession session) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
DataShop dataShop = new DataShop();
List list = new ArrayList();
List listX = new ArrayList();
String s_drvschool = session.getAttribute("user").toString();
try {
Class.forName("org.postgresql.Driver").newInstance();
} catch (Exception e) {
System.out.print(e.getMessage());
}
DBInfo connstr = new DBInfo();
String url = connstr.getUrl();
String user = connstr.getUser();
String password = connstr.getPassword();
try {
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
int i_num_tj = 0;
String sql =
"select count(1) as num_tj " + " from work.qt_cont WHERE lictype='" + lictype + "'";
rs = stmt.executeQuery(sql);
while (rs.next()) {
i_num_tj = rs.getInt(1);
if (i_num_tj == 0 || rs.getString(1) == null) {
dataShop.setSuccess(true);
} else {
dataShop.setSuccess(false);
}
}
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
System.out.print(e.getMessage());
}
}
// dataShop.setList(list);
return dataShop;
}
protected SDMSObject rowToObject(SystemEnvironment env, ResultSet r) throws SDMSException {
Long id;
Long smeId;
Long trId;
Long nextTriggerTime;
Integer timesChecked;
Integer timesTriggered;
Long creatorUId;
Long createTs;
Long changerUId;
Long changeTs;
long validFrom;
long validTo;
try {
id = new Long(r.getLong(1));
smeId = new Long(r.getLong(2));
trId = new Long(r.getLong(3));
nextTriggerTime = new Long(r.getLong(4));
timesChecked = new Integer(r.getInt(5));
timesTriggered = new Integer(r.getInt(6));
creatorUId = new Long(r.getLong(7));
createTs = new Long(r.getLong(8));
changerUId = new Long(r.getLong(9));
changeTs = new Long(r.getLong(10));
validFrom = 0;
validTo = Long.MAX_VALUE;
} catch (SQLException sqle) {
SDMSThread.doTrace(null, "SQL Error : " + sqle.getMessage(), SDMSThread.SEVERITY_ERROR);
throw new FatalException(
new SDMSMessage(
env,
"01110182045",
"TriggerQueue: $1 $2",
new Integer(sqle.getErrorCode()),
sqle.getMessage()));
}
if (validTo < env.lowestActiveVersion) return null;
return new SDMSTriggerQueueGeneric(
id,
smeId,
trId,
nextTriggerTime,
timesChecked,
timesTriggered,
creatorUId,
createTs,
changerUId,
changeTs,
validFrom,
validTo);
}
/**
* [connect Metodo encargado de establecer la conexion con la base de datos.]
*
* @return Connection [Objeto de tipo conexion.]
* @throws MyException [Manejo de excepciones.]
*/
public Connection connect() throws MyException {
try {
Class.forName("org.postgresql.Driver");
conexion = DriverManager.getConnection(url, user, password);
return conexion;
} catch (SQLException ex) {
System.out.println("No se pudo conectar a la BD: " + ex.getMessage());
throw new MyException("No se pudo conectar a la BD: " + ex.getMessage());
} catch (ClassNotFoundException ex) {
throw new MyException("No se pudo conectar a la BD: " + ex.getMessage());
}
}
/**
* Control de transacción en el procedimiento almacenado
*
* @param prod
*/
@Override
public void create2(Producto prod) {
Connection cn = null;
try {
cn = AccesoDB.getConnection();
cn.setAutoCommit(true);
String query = "{call usp_crea_producto(?,?,?,?,?)}";
CallableStatement cstm = cn.prepareCall(query);
cstm.registerOutParameter(1, Types.INTEGER);
cstm.setInt(2, prod.getIdcat());
cstm.setString(3, prod.getNombre());
cstm.setDouble(4, prod.getPrecio());
cstm.setInt(5, prod.getStock());
cstm.executeUpdate();
prod.setIdprod(cstm.getInt(1));
cstm.close();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
throw new RuntimeException("No se puede crear el producto.");
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
}
@Override
public List<Producto> readForName(String name) {
List<Producto> lista = new ArrayList<Producto>();
Connection cn = null;
try {
cn = AccesoDB.getConnection();
String query =
"select idprod, idcat, nombre, precio, stock "
+ "from producto "
+ "where upper(nombre) like ?";
PreparedStatement pstm = cn.prepareStatement(query);
name = "%" + name.toUpperCase() + "%";
pstm.setString(1, name);
ResultSet rs = pstm.executeQuery();
while (rs.next()) {
Producto o = rsToBean(rs);
lista.add(o);
}
rs.close();
pstm.close();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
throw new RuntimeException("No se puede consultar la base de datos.");
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
return lista;
}
@Override
public Producto readForId(int id) {
Producto o = null;
Connection cn = null;
try {
cn = AccesoDB.getConnection();
String query =
"select idprod, idcat, nombre, precio, stock " + "from producto " + "where idprod = ?";
PreparedStatement pstm = cn.prepareStatement(query);
pstm.setInt(1, id);
ResultSet rs = pstm.executeQuery();
if (rs.next()) {
o = rsToBean(rs);
}
rs.close();
pstm.close();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
throw new RuntimeException("Error en la lectura del producto.");
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
return o;
}
@Override
public List<Producto> readAll() {
List<Producto> lista = new ArrayList<Producto>();
Connection cn = null;
try {
cn = AccesoDB.getConnection();
String query = "select idprod, idcat, nombre, precio, stock " + "from producto ";
Statement stm = cn.createStatement();
ResultSet rs = stm.executeQuery(query);
while (rs.next()) {
Producto o = rsToBean(rs);
lista.add(o);
}
rs.close();
stm.close();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
throw new RuntimeException("No se puede consultar la base de datos.");
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
return lista;
}
/**
* Attempts to add a new shipment to the table
*
* @param dto - the shipment to add to the database
* @return boolean value for whether the shipment was added
*/
public boolean newShipment(ShipmentDTO dto) {
boolean success;
String sqlQ =
"INSERT INTO gunnargo_cmsc495.Shipment SET "
+ "ItemID = '"
+ dto.getItemID()
+ "', "
+ "CustID = '"
+ dto.getCustID()
+ "', "
+ "Destination = '"
+ dto.getDestination()
+ "', "
+ "Weight = '"
+ dto.getWeight()
+ "', "
+ "NumItems = '"
+ dto.getNumItems()
+ "';";
try {
con = DriverManager.getConnection(url, userid, password);
Statement stmt = con.createStatement();
stmt.execute(sqlQ);
ResultSet rs = stmt.executeQuery("SELECT * FROM gunnargo_cmsc495.Shipment");
if (rs.last()) dto.setShipID(rs.getString("ShipID"));
success = !dto.getShipID().equals("");
con.close();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
success = false;
}
return success;
}
/** erstellt die nötigen Tabellen in der Datenbank, falls sie noch nicht exisiteren */
private void erstelleTabellen() {
try {
// erstellt die Benutzer-Tabelle
String dbQuery =
"CREATE TABLE IF NOT EXISTS benutzer ("
+ "bNr INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "benutzername VARCHAR(45) NOT NULL,"
+ "statusnachricht text,"
+ "statussymbol VARCHAR(4),"
+ "status_aktuell_seit INTEGER,"
+ "passwort VARCHAR(50) NOT NULL,"
+ "online TINYINT(1));";
con.createStatement().executeUpdate(dbQuery);
// erstellt die Kontaklisten-Tabelle
dbQuery =
"CREATE TABLE IF NOT EXISTS kontaktliste ("
+ "kId INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "besitzer INTEGER,"
+ "kontakt INTEGER,"
+ "foreign key (besitzer) references benutzer(bNr),"
+ "foreign key (kontakt) references benutzer(bNr))";
con.createStatement().executeUpdate(dbQuery);
} catch (SQLException e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(1);
}
}
/**
* Updating a given appointment with new values in the persistence.
*
* @param appointment which shall be updated must not be null, id must not be null and must not be
* changed
* @return given appointment with updated values
* @throws PersistenceException if there are complications with the persitance layer
*/
@Override
public Appointment update(Appointment appointment) throws PersistenceException {
LOGGER.info("Updating record in appointment table..");
try {
if (appointment == null) {
LOGGER.error("Update parameter (appointment) was null.");
throw new PersistenceException("Appointment to be updated must not be null");
}
updateStm.setDate(1, new java.sql.Date(appointment.getDatum().getTime()));
updateStm.setInt(2, appointment.getSession_id());
updateStm.setInt(3, appointment.getUser_id());
updateStm.setBoolean(4, appointment.getIsTrained());
updateStm.setBoolean(5, appointment.getIsDeleted());
updateStm.setInt(6, appointment.getId());
updateStm.executeUpdate();
} catch (SQLException e) {
LOGGER.error("Failed to update record in appointment table. - " + e.getMessage());
throw new PersistenceException("Failed to update record in appointment table.", e);
}
LOGGER.info("Record successfully updated in appointment table. " + appointment);
return appointment;
}
/**
* Method called by the Form panel to delete existing data.
*
* @param persistentObject value object to delete
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response deleteRecord(ValueObject persistentObject) throws Exception {
PreparedStatement stmt = null;
try {
EmpVO vo = (EmpVO) persistentObject;
// delete from WORKING_DAYS...
stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
stmt.close();
// delete from EMP...
stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
gridFrame.reloadData();
frame.getGrid().clearData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
// Yhteyden avaaminen
public boolean avaaYhteys() {
// Vaihe 1: tietokanta-ajurin lataaminen
try {
Class.forName(AJURI);
} catch (ClassNotFoundException e) {
System.out.println("Ajurin lataaminen ei onnistunut. Lopetetaan ohjelman suoritus.");
e.printStackTrace();
return false;
}
// Vaihe 2: yhteyden ottaminen tietokantaan
con = null;
try {
con =
DriverManager.getConnection(
PROTOKOLLA + "//" + PALVELIN + ":" + PORTTI + "/" + TIETOKANTA, KAYTTAJA, SALASANA);
stmt = con.createStatement();
// Toiminta mahdollisessa virhetilanteessa
} catch (SQLException e) {
System.out.println("Yhteyden avaamisessa tapahtui seuraava virhe: " + e.getMessage());
e.printStackTrace();
return false;
}
return true;
}
// Tulostaa resultSetin
public boolean tulostaRs(ResultSet rs) {
if (rs != null) {
try {
ResultSetMetaData rsmd = rs.getMetaData();
int columnit = rsmd.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= columnit; i++) {
if (i > 1) System.out.print(" | ");
System.out.print(rs.getString(i));
}
System.out.println("");
}
return true;
} catch (SQLException e) {
System.out.println("ResultSetin tulostuksessa tapahtui virhe: " + e.getMessage());
e.printStackTrace();
return false;
}
} else {
System.out.println("VIRHE: tulosjoukko on tyhjä.");
return false;
}
}
public void close() throws SQLException {
Throwable firstException = null;
if (closed) {
return;
}
try {
// close any statements that were created on this connection
try {
closeStatements();
} catch (SQLException se) {
firstException = se;
} finally {
this.serverConn.close();
if (firstException != null) throw (SQLException) firstException;
}
} catch (SQLException se) {
throw TeiidSQLException.create(
se,
JDBCPlugin.Util.getString(
"MMConnection.Err_connection_close", se.getMessage())); // $NON-NLS-1$
} finally {
logger.fine(
JDBCPlugin.Util.getString("MMConnection.Connection_close_success")); // $NON-NLS-1$
// set the status of the connection to closed
closed = true;
}
}
/**
* Close the real JDBC Connection
*
* @return
*/
public void close() {
try {
connection.close();
} catch (SQLException sqle) {
System.err.println(sqle.getMessage());
}
}
public static void execute(Object[] args, Hashtable aliases) throws UpdateException {
// System.out.println("______________EXECUTOR CALLED!!! ");
if (args == null) return;
String query = "begin ";
for (int i = 0; i < args.length; i++) {
query = query + (String) args[i] + ";";
if (GLOBAL.dstore_debug > 2) System.out.println(query);
}
query = query + " end;";
/*
for (int i=1;i<args.length;i++){
if (i!=args.length)
query = query +args[i]+",";
else query = query + args[i]+"); end;";
}
*/
ResultSet rset = null;
try {
rset = DATASTORE.executeQuery(query);
DATASTORE.conn.commit();
DSCollection.repeatLocks();
} catch (SQLException e) {
throw new UpdateException(e.getMessage(), 0);
}
}
/**
* Creates a new appointment.
*
* @param appointment which shall be inserted into the underlying persistance layer. must not be
* null, id must be null
* @return the given appointment for further usage
* @throws PersistenceException if there are complications with the persitance layer
*/
@Override
public Appointment create(Appointment appointment) throws PersistenceException {
LOGGER.info("Creating a new appointment in db.. " + appointment);
try {
if (appointment == null) {
LOGGER.error("Create parameter (appointment) was null.");
throw new PersistenceException("Appointment to be create must not be null");
}
Statement appointmentNextValStm = connection.createStatement();
ResultSet rs_appointmentNextVal =
appointmentNextValStm.executeQuery("SELECT NEXTVAL('appointment_seq')");
rs_appointmentNextVal.next();
appointment.setId(rs_appointmentNextVal.getInt(1));
createStm.setInt(1, appointment.getId());
createStm.setDate(2, new java.sql.Date(appointment.getDatum().getTime()));
createStm.setInt(3, appointment.getSession_id());
createStm.setInt(4, appointment.getUser_id());
createStm.setBoolean(5, appointment.getIsTrained());
createStm.setBoolean(6, appointment.getIsDeleted());
createStm.execute();
} catch (SQLException e) {
LOGGER.error("Failed to create record into appointment table. - " + e.getMessage());
throw new PersistenceException("Failed to create record into appointment table.", e);
}
LOGGER.info("Record successfully created in appointment table.");
return appointment;
}
/**
* fügt einen neuen Kontakt zur Kontaktliste hinzu
*
* @param besitzer Der Besitzer der Kontaktliste
* @param kontakt Der Kontakt, der hinzugefügt werden soll
*/
public void kontaktZurKontaktListeHinzufuegen(String besitzer, String kontakt) {
int kNr = bestimmeBNrBenutzer(kontakt);
int bNr = bestimmeBNrBenutzer(besitzer);
try {
// überprüft, ob der Konktakt noch nicht in der Kontaktliste ist
boolean gefunden = false;
ResultSet rueckgabewert = null;
String query1 = "select count(*) from kontaktliste where besitzer = ? and kontakt = ?";
PreparedStatement anweisung1 = con.prepareStatement(query1);
anweisung1.setString(1, besitzer);
anweisung1.setString(1, kontakt);
rueckgabewert = anweisung1.executeQuery();
// werdet den Rückgabewert aus
while (rueckgabewert.next()) {
gefunden = rueckgabewert.getBoolean(1);
}
if (!gefunden) {
// fügt den Kontakt zur Kontakliste hinzu
String query = "insert into kontaktliste (besitzer, kontakt) values(?, ?);";
PreparedStatement anweisung = con.prepareStatement(query);
anweisung.setInt(1, bNr);
anweisung.setInt(2, kNr);
anweisung.executeUpdate();
}
} catch (SQLException e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(1);
}
}
