public Connection getConnection() throws SQLException {
synchronized (pool) {
if (!pool.isEmpty()) {
int last = pool.size() - 1;
Connection pooled = (Connection) pool.remove(last);
boolean conn_ok = true;
String test_table = prop.getProperty("test_table");
if (test_table != null) {
Statement stmt = null;
try {
stmt = pooled.createStatement();
stmt.executeQuery("select * from " + prop.getProperty("test_table"));
} catch (SQLException ex) {
conn_ok = false; // 连接不可用
} finally {
if (stmt != null) {
stmt.close();
}
}
}
if (conn_ok == true) {
return pooled;
} else {
pooled.close();
}
}
}
Connection conn =
DriverManager.getConnection(
prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
return conn;
}
// ----------------------------------------------------------------------------------------------------------------
// Method: ListDataByGeoArea
// Inputs: Start Latitude, Start Longitude, End Latitude, End Longitude (create
// rectangle)
// Outputs: RF Data Entry (JSON) Collection
// Description: Get RF Data Entries
// ----------------------------------------------------------------------------------------------------------------
public String ListDataByGeoArea(
float StartLatitude, float StartLongitude, float EndLatitude, float EndLongitude) {
String json = ""; // Return JSON entry
try // Try to get JSON, and save data to database
{ //
Gson gson = new GsonBuilder().create(); // Create Gson builder
// Print debug information to port
System.out.println("Select RF Data from Table - GeoLocation");
String sql_string; // Build up SQL string
sql_string = "SELECT "; // Select SQL statement
sql_string += "intSampleNum,"; // Field: intSampleNum
sql_string += "intXbeeID,"; // Field: intXbeeID
sql_string += "intDeviceID,"; // Field: intDeviceID
sql_string += "fltRSSI,"; // Field: fltRSSI
sql_string += "fltLatitude,"; // Field: fltLatitude
sql_string += "fltLongitude,"; // Field: fltLongitude
sql_string += "fltYaw,"; // Field: fltYaw
sql_string += "fltPitch,"; // Field: fltPitch
sql_string += "fltRoll,"; // Field: fltRoll
sql_string += "dtSampleDate "; // Field: dtSampleDate
sql_string += "FROM RF_Fields "; // Table: RF_Fields
// If, 0,0,0,0 return all records
if ((StartLatitude != 0) | (StartLongitude != 0) | (EndLatitude != 0) | (EndLongitude != 0)) {
sql_string += "WHERE ("; // Where statement
sql_string += "fltLatitude >= " + StartLatitude + " AND "; // Field on Where and condition
sql_string += "fltLatitude >= " + StartLongitude + " AND "; // Field on Where and condition
sql_string += "fltLatitude <= " + EndLatitude + " AND "; // Field on Where and condition
sql_string += "fltLongitude <= " + EndLongitude + ")"; // Field on Where and condition
}
System.out.println("SQL: " + sql_string); // Debug print the SQL statement
Statement stmt = conn.createStatement(); // Build SQL statement
ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query
ArrayList records = new ArrayList(); // Build the Array List
ReadRecords(rs, records); // Read the records
json = gson.toJson(records); // Get JSON data from this record
rs.close(); // Close the record set
stmt.close(); // Close the statement
if (records.isEmpty() == true) // If empty then print out no records found
{ //
System.err.println("No Records Found!"); // Print the fact that no record was found
} //
} //
catch (Exception e) // Exception processing:
{ //
System.err.println(
"ListDataByGeoArea: " + e.getMessage()); // Print the exception data and exit
} //
return json; // Return JSON string
}
public static void setupDb() {
loadDriver();
Connection conn = null;
ArrayList statements = new ArrayList();
Statement s = null;
ResultSet rs = null;
try {
// database name
String dbName = "demoDB";
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
System.out.println("Creating database " + dbName);
boolean createTable = false;
s = conn.createStatement();
try {
s.executeQuery("SELECT count(*) FROM rssFeed");
} catch (Exception e) {
createTable = true;
}
if (createTable) {
// handle transaction
conn.setAutoCommit(false);
s = conn.createStatement();
statements.add(s);
// Create a contact table...
s.execute("create table rssFeed(id int, title varchar(255), url varchar(600))");
System.out.println("Created table rssFeed ");
conn.commit();
}
shutdown();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
close(rs);
// Statements and PreparedStatements
int i = 0;
while (!statements.isEmpty()) {
// PreparedStatement extend Statement
Statement st = (Statement) statements.remove(i);
close(st);
}
close(conn);
}
}
// ----------------------------------------------------------------------------------------------------------------
// Method: ListDataByRSSI
// Inputs: RSSI, GTE
// Outputs: RF Data Entry (JSON) Collection
// Description: Get RF Data Entries
// ----------------------------------------------------------------------------------------------------------------
public String ListDataByRSSI(float RSSI, boolean GTE) {
String json = ""; // Return JSON entry
try // Try to get JSON, and save data to database
{ //
Gson gson = new GsonBuilder().create(); // Create Gson builder
// Print debug information to port
if (GTE) System.out.println("Select RF Data from Table - RSSI >= " + RSSI);
else System.out.println("Select RF Data from Table - RSSI < " + RSSI);
String sql_string; // Build up SQL string
sql_string = "SELECT "; // Select SQL statement
sql_string += "intSampleNum,"; // Field: intSampleNum
sql_string += "intXbeeID,"; // Field: intXbeeID
sql_string += "intDeviceID,"; // Field: intDeviceID
sql_string += "fltRSSI,"; // Field: fltRSSI
sql_string += "fltLatitude,"; // Field: fltLatitude
sql_string += "fltLongitude,"; // Field: fltLongitude
sql_string += "fltYaw,"; // Field: fltYaw
sql_string += "fltPitch,"; // Field: fltPitch
sql_string += "fltRoll,"; // Field: fltRoll
sql_string += "dtSampleDate "; // Field: dtSampleDate
sql_string += "FROM RF_Fields "; // Table: RF_Fields
sql_string += "WHERE "; // Where statement
if (GTE) // Change condition based upon direction (GTE)
sql_string += "fltRSSI >= "; // Field on Where and condition
else sql_string += "fltRSSI < "; // Field on Where and condition
sql_string += RSSI; // Condition value
System.out.println("SQL: " + sql_string); // Debug print the SQL statement
Statement stmt = conn.createStatement(); // Build SQL statement
ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query
ArrayList records = new ArrayList(); // Build the Array List
ReadRecords(rs, records); // Read the records
json = gson.toJson(records); // Get JSON data from this record
rs.close(); // Close the record set
stmt.close(); // Close the statement
if (records.isEmpty() == true) // If empty then print out no records found
{ //
System.err.println("No Records Found!"); // Print the fact that no record was found
} //
} //
catch (Exception e) // Exception processing:
{ //
System.err.println("ListDataByRSSI: " + e.getMessage()); // Print the exception data and exit
} //
return json; // Return JSON string
}
@Override
public void actionPerformed(ActionEvent axnEve) {
Object obj = axnEve.getSource();
if (obj == selectAllCB) {
Boolean state;
if (selectAllCB.isSelected()) {
state = true;
deleteBut.setVisible(true);
if (Home.titlePan.getTitle().equals("Trash")) {
restoreBut.setVisible(true);
}
} else {
state = false;
deleteBut.setVisible(false);
if (Home.titlePan.getTitle().equals("Trash")) {
restoreBut.setVisible(false);
}
}
for (int i = 0; i < table.getRowCount(); i++) {
table.setValueAt(state, i, 0);
}
} else if (obj == refreshBut || obj == backBut) {
setContent(Home.titlePan.getTitle());
backBut.setVisible(false);
} else if (obj == deleteBut) {
ArrayList selectedMessages = getSelectedMessages();
if (selectedMessages.isEmpty()) {
FootPan.setMessage(FootPan.NO_SELECTION_MESSAGE);
} else {
int option =
JOptionPane.showConfirmDialog(
Home.home.homeFrame,
"Are You Sure?",
"DELETE",
JOptionPane.YES_NO_OPTION,
JOptionPane.WARNING_MESSAGE);
if (option == 0) {
Database.deleteMessages(selectedMessages, Home.titlePan.getTitle());
setContent(Home.titlePan.getTitle());
}
}
} else if (obj == restoreBut) {
ArrayList selectedMessages = getSelectedMessages();
if (selectedMessages.isEmpty()) {
FootPan.setMessage(FootPan.NO_SELECTION_MESSAGE);
} else {
int option =
JOptionPane.showConfirmDialog(
Home.home.homeFrame,
"Are You Sure?",
"RESTORE",
JOptionPane.YES_NO_OPTION,
JOptionPane.WARNING_MESSAGE);
if (option == 0) {
Database.restoreMessages(selectedMessages);
setContent(Home.titlePan.getTitle());
}
}
}
}
public static void main(String[] args) {
// DBResourcesManager.initHibernate();
OffertaEvento of = new OffertaEvento();
of.setNome("ofevent");
of.setTipologia("Concerto");
of.setPrezzo(69);
// of.setDataScadenza("21/08/2019");
of.setCittà("Roma");
Offerta offertaArray[] = {new OffertaEvento(), new OffertaPernotto(), new OffertaTrasporto()};
/*
for(Offerta o : offertaArray) {
System.out.println(o.getClass().getSimpleName() + "\n");
Method[] methods = o.getClass().getMethods();
for (Method m : methods) {
int l = m.getName().length();
if (m.getName().substring(0, 3).equals("set") && !m.getName().substring(l - 2, l).equals("ID"))
System.out.println(m.getName());
}
}*/
/*
OffertaBean object = new OffertaEventoBean();
String query = "from "+ object.getClass().getSimpleName() + " where ";
System.out.println(object.getClass().getSimpleName() + "\n");
Method[] methods = object.getClass().getMethods();
for (Method m : methods) {
int l = m.getName().length();
if (m.getName().substring(0, 3).equals("get") && !m.getName().substring(l - 2, l).equals("ID") && !m.getName().equals("getClass"))
{
String s = m.getName().substring(0,1).toLowerCase();
String attributeLower = s + m.getName().substring(1);
query = query + attributeLower + " = ";
}
}*/
String città = "Roma";
String dataScadenza = "11/07/2009";
String nome = "Bubu";
String prezzo = "4";
String tipologia = "Museo";
String query = "from OffertaEvento offertaEvento";
query = query + " where ";
if (città != null) query = query + "offertaEvento.città = '" + città + "' && ";
if (dataScadenza != null)
query = query + "offertaEvento.dataScadenza = '" + dataScadenza + "' && ";
if (nome != null) query = query + "offertaEvento.nome = '" + nome + "' && ";
if (prezzo != null) query = query + "offertaEvento.prezzo = '" + prezzo + "' && ";
if (tipologia != null) query = query + "offertaEvento.tipologia = '" + tipologia + "'";
System.out.println(query);
ArrayList<String> ls = new ArrayList<String>();
ls.add(città);
ls.add(dataScadenza);
ls.add(nome);
ls.add(prezzo);
ls.add(tipologia);
System.out.println("\nNuova query\n");
if (!ls.isEmpty()) query.join(" where");
for (int i = 0; i < ls.size(); i++) {
if (!ls.get(i).equals("") && i == 0)
query.join(" && offertaEvento.città = '" + ls.get(i) + "'");
if (!ls.get(i).equals("") && i == 1)
query.join(" && offertaEvento.dataScadenza = '" + ls.get(i) + "'");
if (!ls.get(i).equals("") && i == 2)
query.join(" && offertaEvento.nome = '" + ls.get(i) + "'");
if (!ls.get(i).equals("") && i == 3)
query.join(" && offertaEvento.prezzo = '" + ls.get(i) + "'");
if (!ls.get(i).equals("") && i == 4)
query.join(" && offertaEvento.tipologia = '" + ls.get(i) + "'");
}
System.out.println(query);
Offerta o = new OffertaEvento();
List<Method> fields = Arrays.asList(o.getClass().getDeclaredMethods());
Class c = o.getClass().getSuperclass();
List<Method> superfields = Arrays.asList(c.getDeclaredMethods());
List<Method> allfields = new ArrayList<Method>(fields);
allfields.addAll(superfields);
allfields.sort(
new Comparator<Method>() {
public int compare(Method o1, Method o2) {
return o1.getName().compareTo(o1.getName());
}
});
/*
for(Method f: allfields){
}*/
java.sql.Date date = Date.valueOf(LocalDate.now());
System.out.println(date);
String datestring = date.toString();
System.out.println(datestring);
Date data2 = Date.valueOf(datestring);
System.out.println(data2);
// OffertaEventoDAO.store(of);
// System.out.println(((List<OffertaEvento>)DAOFactory.getDAOFactory(TipoOfferta.OffertaEvento).getOffertaDAO().getList()).size());
// DBResourcesManager.shutdown();
// OffertaDaoAnnotations.findAllOffertaEntitysA("offertaevento","Concerto");
}
public static int saveRssFeed(RssFeed rssFeed) {
int pk = rssFeed.link.hashCode();
loadDriver();
Connection conn = null;
ArrayList statements = new ArrayList();
PreparedStatement psInsert = null;
Statement s = null;
ResultSet rs = null;
try {
// database name
String dbName = "demoDB";
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
rs =
conn.createStatement()
.executeQuery("select count(id) from rssFeed where id = " + rssFeed.link.hashCode());
rs.next();
int count = rs.getInt(1);
if (count == 0) {
// handle transaction
conn.setAutoCommit(false);
s = conn.createStatement();
statements.add(s);
psInsert = conn.prepareStatement("insert into rssFeed values (?, ?, ?)");
statements.add(psInsert);
psInsert.setInt(1, pk);
String escapeTitle = rssFeed.channelTitle.replaceAll("\'", "''");
psInsert.setString(2, escapeTitle);
psInsert.setString(3, rssFeed.link);
psInsert.executeUpdate();
conn.commit();
System.out.println("Inserted " + rssFeed.channelTitle + " " + rssFeed.link);
System.out.println("Committed the transaction");
}
shutdown();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
// release all open resources to avoid unnecessary memory usage
// ResultSet
close(rs);
// Statements and PreparedStatements
int i = 0;
while (!statements.isEmpty()) {
// PreparedStatement extend Statement
Statement st = (Statement) statements.remove(i);
close(st);
}
// Connection
close(conn);
}
return pk;
}
/**
* Talar om ifall användaren har några tabeller.
*
* @return true = användaren har tabeller, false = användaren saknar tabeller
*/
@Override
public boolean hasTables() {
return !cardCollections.isEmpty();
}
