@Override
public Set<String[]> keySet(String[] keyBase) throws DataSourceException {
String searchPrefix = StringUtils.Join(keyBase, ".");
try {
connect();
Set<String[]> set;
try (PreparedStatement statement =
connection.prepareStatement(
"SELECT `"
+ KEY_COLUMN
+ "` FROM `"
+ getEscapedTable()
+ "` WHERE `"
+ KEY_COLUMN
+ "` LIKE ?")) {
statement.setString(1, searchPrefix + "%");
set = new HashSet<>();
try (ResultSet result = statement.executeQuery()) {
while (result.next()) {
set.add(result.getString(KEY_COLUMN).split("\\."));
}
}
lastConnected = System.currentTimeMillis();
}
return set;
} catch (SQLException | IOException ex) {
throw new DataSourceException(ex.getMessage(), ex);
}
}
@Override
protected Map<String[], String> getValues0(String[] leadKey) throws DataSourceException {
try {
connect();
Map<String[], String> map;
try (PreparedStatement statement =
connection.prepareStatement(
"SELECT `"
+ KEY_COLUMN
+ "`, `"
+ VALUE_COLUMN
+ "` FROM `"
+ getEscapedTable()
+ "`"
+ " WHERE `"
+ KEY_COLUMN
+ "` LIKE ?")) {
statement.setString(1, StringUtils.Join(leadKey, ".") + "%");
map = new HashMap<>();
try (ResultSet results = statement.executeQuery()) {
while (results.next()) {
map.put(results.getString(KEY_COLUMN).split("\\."), results.getString(VALUE_COLUMN));
}
}
lastConnected = System.currentTimeMillis();
}
return map;
} catch (SQLException | IOException ex) {
throw new DataSourceException(ex.getMessage(), ex);
}
}
@Override
protected Application configure() {
faker = new Faker();
final Configuration configuration;
try {
configuration = new Configuration(CONFIG);
} catch (IOException | NumberFormatException e) {
System.out.println("Properties error:");
System.out.println(e.getMessage());
System.exit(1);
return null;
}
final AccountServiceImpl accountService;
try {
accountService =
new AccountServiceImpl(
configuration.getDbName(),
configuration.getDbHost(),
configuration.getDbPort(),
configuration.getDbUsername(),
configuration.getDbPassword());
} catch (SQLException | IOException e) {
e.printStackTrace();
System.exit(1);
return null;
}
final ResourceConfig config = new ResourceConfig(Session.class, Users.class);
config.register(new AccountServiceAbstractBinder(accountService));
final HttpServletRequest httpServletRequest = mock(HttpServletRequest.class);
final HttpSession httpSession = mock(HttpSession.class);
final String sessionId = faker.lorem().fixedString(15);
Mockito.when(httpServletRequest.getSession()).thenReturn(httpSession);
Mockito.when(httpSession.getId()).thenReturn(sessionId);
config.register(new ServletAbstractBinder(httpServletRequest));
return config;
}
private void generateRuneList() throws SQLException {
try {
Statement st = db.conn.createStatement();
String lowestsell = md.getColumnName(md.getColumnCount());
String highestbuy = md.getColumnName(md.getColumnCount() - 1);
ResultSet rs =
st.executeQuery(
"select name, "
+ lowestsell
+ ", "
+ highestbuy
+ ", rarity from "
+ DBNAME
+ " where name like '%Rune%' and rarity = 4 "
+ " and name not like '%Recipe%' "
+ " order by "
+ highestbuy
+ " asc;");
ResultSetMetaData metad = rs.getMetaData();
FileWriter fstream = new FileWriter("D:/gw2/static/runes.txt");
try (PrintWriter out = new PrintWriter(fstream)) {
while (rs.next()) {
out.println(
"lowestsell: "
+ rs.getString(metad.getColumnName(2))
+ " \thighestbuy: "
+ rs.getString(metad.getColumnName(3))
+ " \trarity: "
+ rs.getString(metad.getColumnName(4))
+ " \tname: "
+ rs.getString(metad.getColumnName(1)));
}
}
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
public void generateDyeList() throws SQLException {
getItemColumns();
try {
Statement st = db.conn.createStatement();
String lowestsell = md.getColumnName(md.getColumnCount());
String highestbuy = md.getColumnName(md.getColumnCount() - 1);
ResultSet rs =
st.executeQuery(
"select name, "
+ lowestsell
+ ", "
+ highestbuy
+ " from "
+ DBNAME
+ " where name like '%Dye%' and name not like '%Unidentified%' "
+ " order by name asc;");
ResultSetMetaData metad = rs.getMetaData();
new File("D:/gw2/dyes/").mkdirs();
FileWriter fstream = new FileWriter("D:/gw2/dyes/dyelist.txt");
try (PrintWriter out = new PrintWriter(fstream)) {
while (rs.next()) {
out.println(
"lowestsell: "
+ rs.getString(metad.getColumnName(2))
+ " \thighestbuy: "
+ rs.getString(metad.getColumnName(3))
+ " \tname: "
+ rs.getString(metad.getColumnName(1)));
}
Date e = new Date();
e.getTime();
out.println("Last updated: " + e.toString());
}
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
/** Creates new form DatosGasolinera */
public DatosGasolinera(String usuario, String password) {
USER = usuario;
PASSWORD = password;
DB = "adv_facturacion";
BufferedReader br;
try {
br = new BufferedReader(new FileReader("eds.adv"));
eds = br.readLine();
consulta =
"SELECT razon_social, ruc, direccion, email_estacion, despachadores_turno, tipo_ambiente, "
+ "obligado_llevar_contabilidad, nombre_comercial, contribuyente_especial, tipo_cierre_turnos, "
+ "AES_DECRYPT(contraseña_mail, 'thekey'), secuencia1_factura, certificado_digital "
+ "FROM datos_gasolinera";
conn = new ConnectionTableDB(USER, PASSWORD, DB, consulta, false);
initComponents();
setSize(900, 600);
frameEmision.setSize(900, 600);
frameEmision.setUndecorated(true);
txtEDS.setText(eds.toUpperCase());
if (conn.getRowCount() == 0) {
btnGuardar.setText("Guardar");
} else {
btnGuardar.setText("Editar");
txtRazonSocial.setText((String) conn.getValueAt(0, 0));
txtRUC.setText((String) conn.getValueAt(0, 1));
txtDireccion.setText((String) conn.getValueAt(0, 2));
txtPropietario.setText((String) conn.getValueAt(0, 3));
txtDespachadores.setText(String.valueOf(conn.getValueAt(0, 4)));
cbxAmbiente.setSelectedIndex(Integer.parseInt((String) conn.getValueAt(0, 5)) - 1);
chkContabilidad.setSelected(((String) conn.getValueAt(0, 6)).equalsIgnoreCase("SI"));
txtNombreComercial.setText((String) conn.getValueAt(0, 7));
txtContribuyente.setText((String) conn.getValueAt(0, 8));
txtEstablecimiento.setText((String) conn.getValueAt(0, 11));
if (conn.getValueAt(0, 12) != null) txtCertificado.setText("Certificado en Uso");
else txtCertificado.setText("Sin Certificado");
cbxTurno.setSelectedIndex((Integer) conn.getValueAt(0, 9) - 1);
conn.consulta("SELECT s2 FROM punto_emision ORDER BY s2");
for (int i = 0; i < conn.getRowCount(); i++) {
cbxEmision.addItem((String) conn.getValueAt(i, 0));
}
puntosEmision = cbxEmision.getItemCount();
java.sql.ResultSet rs =
conn.stSentencias.executeQuery(
"SELECT AES_DECRYPT(contraseña_mail, 'thekey'), "
+ "AES_DECRYPT(contraseña_certificado, 'thekey') "
+ "FROM datos_gasolinera");
java.sql.Blob mail = null, certificado = null;
if (rs.next()) {
mail = rs.getBlob(1);
certificado = rs.getBlob(2);
}
if (mail != null) {
passClaveEmail.setText(new String(mail.getBytes(1, (int) mail.length())));
}
if (certificado != null) {
passClave.setText(new String(certificado.getBytes(1, (int) certificado.length())));
}
}
} catch (SQLException | IOException ex) {
System.err.println(ex.getMessage());
}
}
/**
* Parse CSV file using OpenCSV library and load in given database table. href:
* http://viralpatel.net/blogs/java-load-csv-file-to-database/. Modified by rammar:
* https://github.com/BaderLab/pharmacogenomics/blob/master/src/CSVLoader/CSVLoader.java
*
* @param connection the {@link Connection} SQL.
* @param separator the {@link Character} separator.
* @param csvFile Input CSV InputStream
* @param tableName Database table name to import data
* @param truncateBeforeLoad Truncate the table before inserting new records.
* @throws SQLException if any error is occurred with the SQL Connection.
* @throws java.io.IOException if any error is occurred with the file.
*/
public static void loadCSVToSQLTable(
Connection connection,
char separator,
InputStream csvFile,
String tableName,
boolean truncateBeforeLoad)
throws SQLException, IOException {
String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
String TABLE_REGEX = "\\$\\{table\\}";
String KEYS_REGEX = "\\$\\{keys\\}";
String VALUES_REGEX = "\\$\\{values\\}";
CSVReader csvReader;
if (null == connection) {
throw new SQLException("Not a valid connection.");
}
try {
/* Modified by rammar.
*
* I was having issues with the CSVReader using the "\" to escape characters.
* A MySQL CSV file contains quote-enclosed fields and non-quote-enclosed NULL
* values written as "\N". The CSVReader was removing the "\". To detect "\N"
* I must remove the escape character, and the only character you can replace
* it with that you are pretty much guaranteed will not be used to escape
* text is '\0'.
* I read this on:
* http://stackoverflow.com/questions/6008395/opencsv-in-java-ignores-backslash-in-a-field-value
* based on:
* http://sourceforge.net/p/opencsv/support-requests/5/
*/
// PREVIOUS VERSION: csvReader = new CSVReader(new FileReader(csvFile), this.seprator);
csvReader = new CSVReader(new InputStreamReader(csvFile), separator, '"', '\0');
} catch (Exception e) {
throw new IOException("Error occured while executing file. " + e.getMessage());
}
String[] headerRow = csvReader.readNext();
if (null == headerRow) {
throw new FileNotFoundException(
"No columns defined in given CSV file." + "Please check the CSV file format.");
}
String questionmarks = StringUtils.repeat("?,", headerRow.length);
questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);
/* NOTE from Ron: Header column names must match SQL table fields */
String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
query = query.replaceFirst(VALUES_REGEX, questionmarks);
// System.out.println("Query: " + query); // Modified by rammar to suppress output
String[] nextLine;
Connection con = null;
PreparedStatement ps = null;
try {
con = connection;
con.setAutoCommit(false);
ps = con.prepareStatement(query);
if (truncateBeforeLoad) {
// delete data from table before loading csv
con.createStatement().execute("DELETE FROM " + tableName);
}
final int batchSize = 1000;
int count = 0;
Date date;
while ((nextLine = csvReader.readNext()) != null) {
int index = 1;
for (String string : nextLine) {
date = DateUtilities.convertToDate(string);
if (null != date) {
ps.setDate(index++, new java.sql.Date(date.getTime()));
} else {
/* Section modified by rammar to allow NULL values
* to be input into the DB. */
if (string.length() > 0 && !string.equals("\\N")) {
ps.setString(index++, string);
} else {
ps.setNull(index++, Types.VARCHAR);
// ps.setString(index++, null); // can use this syntax also - not sure which is better
}
}
}
ps.addBatch();
if (++count % batchSize == 0) {
ps.executeBatch();
}
}
ps.executeBatch(); // insert remaining records
logger.info(count + " records loaded into " + tableName + " DB table");
con.commit();
} catch (SQLException | IOException e) {
con.rollback();
throw new IOException(
"Error occured while loading data from file to database." + e.getMessage());
} finally {
if (null != ps) ps.close();
con.close();
csvReader.close();
}
}
/**
* Runs an SQL script (read in using the Reader parameter) using the connection passed in
*
* @param conn - the connection to use for the script
* @param reader - the source of the script
* @throws SQLException if any SQL errors occur
* @throws IOException if there is an error reading from the Reader
*/
private void runScript(Connection conn, Reader reader) throws IOException, SQLException {
StringBuffer command = null;
try {
LineNumberReader lineReader = new LineNumberReader(reader);
String line = null;
while ((line = lineReader.readLine()) != null) {
if (command == null) {
command = new StringBuffer();
}
String trimmedLine = line.trim();
if (trimmedLine.startsWith("--")) {
println(trimmedLine);
} else if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) {
// Do nothing
} else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")) {
// Do nothing
} else if (trimmedLine.toLowerCase().startsWith("delimiter ")) {
int delimiterPos = line.toLowerCase().indexOf("delimiter ") + "delimiter ".length();
setDelimiter(line.substring(delimiterPos).trim(), false);
} else if (!fullLineDelimiter && trimmedLine.endsWith(getDelimiter())
|| fullLineDelimiter && trimmedLine.equals(getDelimiter())) {
command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
command.append(" ");
Statement statement = conn.createStatement();
println(command);
boolean hasResults = false;
if (stopOnError) {
hasResults = statement.execute(command.toString());
} else {
try {
statement.execute(command.toString());
} catch (SQLException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
}
}
if (autoCommit && !conn.getAutoCommit()) {
conn.commit();
}
ResultSet rs = statement.getResultSet();
if (hasResults && rs != null) {
ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
for (int i = 0; i < cols; i++) {
String name = md.getColumnLabel(i);
print(name + "\t");
}
println("");
while (rs.next()) {
for (int i = 0; i < cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
println("");
}
}
command = null;
try {
statement.close();
} catch (Exception e) {
// Ignore to workaround a bug in Jakarta DBCP
}
Thread.yield();
} else {
command.append(line);
command.append("\n");
}
}
if (!autoCommit) {
conn.commit();
}
} catch (SQLException | IOException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
throw e;
} finally {
conn.rollback();
flush();
}
}
private void getFlippables() throws SQLException {
try {
Statement st = db.conn.createStatement();
String lowestsell = md.getColumnName(md.getColumnCount());
String highestbuy = md.getColumnName(md.getColumnCount() - 1);
ResultSet rs =
st.executeQuery(
"select name,( "
+ lowestsell
+ " * 0.85 - "
+ highestbuy
+ " ), "
+ lowestsell
+ ", "
+ highestbuy
+ " from "
+ DBNAME
+ " where ( "
+ lowestsell
+ " * 0.85 - "
+ highestbuy
+ " ) > 1000 and rarity between 2 and 6 "
+ " and "
+ lowestsell
+ "<200000 and name not like '%Recipe%' and name not like '%Satchel%' "
+ " and name not like '%Pot%' and name not like '%Tray%' and name not like '%Box%' and "
+ lowestsell
+ ">0 and "
+ highestbuy
+ ">0 "
+ " order by ( "
+ lowestsell
+ " * 0.85 -"
+ highestbuy
+ " ) desc;");
ResultSetMetaData metad = rs.getMetaData();
FileWriter fstream = new FileWriter("D:/gw2/static/flip.html");
try (PrintWriter out = new PrintWriter(fstream)) {
out.println(
"<!DOCTYPE HTML>\n<html lang = \"en\">\n<head>\n<title> flip </title>\n"
+ "<meta charset = \"UTF-8\" />\n"
+ "<style type = \"text/css\">"
+ "table\n{\nborder-collapse:collapse;\n}"
+ "\ntd, th {\nborder: 1px solid green;\npadding:5px;}"
+ "\n</style>"
+ "</head>\n<body>\n<table id = flip>\n<tr>\n<th>lowest sell</th>\n<th>highest buy</th>"
+ "\n<th>profit</th>\n<th>name</th></tr>\n");
while (rs.next()) {
out.println(
"<tr>\n<td>"
+ rs.getString(metad.getColumnName(3))
+ "</td>"
+ "\n<td>"
+ rs.getString(metad.getColumnName(4))
+ "</td>"
+ "\n<td>"
+ rs.getString(metad.getColumnName(2))
+ "</td>"
+ "\n<td>"
+ rs.getString(metad.getColumnName(1))
+ "</td></tr>");
}
Date e = new Date();
e.getTime();
out.println("</table>\n");
out.println("<h5><p>Last updated: " + e.toString() + "</p></h5>\n</body>\n</html>");
;
}
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
private void generateProfit() throws SQLException {
try {
Statement st = db.conn.createStatement();
String lowestsell = md.getColumnName(md.getColumnCount());
String highestbuy = md.getColumnName(md.getColumnCount() - 1);
ResultSet rs =
st.executeQuery(
"select name,( "
+ lowestsell
+ " * 0.85 - "
+ highestbuy
+ " ) , "
+ md.getColumnName(md.getColumnCount() - 2)
+ ", "
+ md.getColumnName(md.getColumnCount() - 3)
+ " from "
+ DBNAME
+ " where ( "
+ lowestsell
+ " *0.85 - "
+ highestbuy
+ " ) > 100 "
+ " and rarity between 2 and 6 and "
+ lowestsell
+ ">0 and "
+ highestbuy
+ ">0 and "
+ "( "
+ lowestsell
+ " > "
+ md.getColumnName(md.getColumnCount() - 4)
+ " and "
+ md.getColumnName(md.getColumnCount() - 1)
+ " < "
+ md.getColumnName(md.getColumnCount() - 5)
+ " ) "
+ " and "
+ lowestsell
+ "<200000 and name not like '%Recipe%' and name not like '%Satchel%' "
+ " and name not like '%Pot%' and name not like '%Tray%' "
+ " order by ( "
+ lowestsell
+ " * 0.85 -"
+ highestbuy
+ " ) desc;");
ResultSetMetaData metad = rs.getMetaData();
FileWriter fstream = new FileWriter("D:/gw2/static/valtozoflip.txt");
try (PrintWriter out = new PrintWriter(fstream)) {
while (rs.next()) {
out.println(
"price: "
+ rs.getString(metad.getColumnName(2))
+ " \teladodbszam: "
+ rs.getString(metad.getColumnName(3))
+ " \tmennyivesznek: "
+ rs.getString(metad.getColumnName(4))
+ " \tname: "
+ rs.getString(metad.getColumnName(1)));
}
Date e = new Date();
e.getTime();
out.println("Last updated: " + e.toString());
}
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
private void getWeaponPricesList() throws SQLException {
List<Integer> subtypes = Arrays.asList(0, 1, 2, 3, 4, 5, 6, 8, 10, 11, 12, 16);
List<String> names =
Arrays.asList(
"Sword",
"Hammer",
"Longbow",
"Shortbow",
"Axe",
"Dagger",
"Greatsword",
"Pistol",
"Rifle",
"Scepter",
"Staff",
"Shield");
List<String> szoveg = new ArrayList<>();
try {
Statement st = db.conn.createStatement();
String lowestsell = md.getColumnName(md.getColumnCount());
String highestbuy = md.getColumnName(md.getColumnCount() - 1);
int index = 0;
for (Integer i : subtypes) {
for (int j = 1; j < 8; j++) {
if (j != 6) {
ResultSet rs =
st.executeQuery(
"select name, "
+ lowestsell
+ ", "
+ highestbuy
+ " , ( "
+ lowestsell
+ " * 0.85 - "
+ highestbuy
+ " ), "
+ "restriction_level from "
+ DBNAME
+ " where rarity= "
+ j
+ " and type_id=18 and sub_type_id= "
+ i
+ " and "
+ highestbuy
+ " > 1 and "
+ lowestsell
+ ">0 "
+ " and restriction_level > 70 "
+ " order by ( "
+ lowestsell
+ " * 0.85 - "
+ highestbuy
+ " ) desc; ");
ResultSetMetaData metad = rs.getMetaData();
if (!new File("D:/gw2/static/weapons/rarity" + j).exists()) {
new File("D:/gw2/static/weapons/rarity" + j).mkdirs();
System.out.println("nem létezett, létrehoztam");
}
FileWriter fstream =
new FileWriter(
"D:/gw2/static/weapons/rarity" + j + "/" + names.get(index) + ".html");
try (PrintWriter out = new PrintWriter(fstream)) {
out.println(generateHeader(j, names.get(index)));
while (rs.next()) {
out.println(
"<tr>\n<td>"
+ rs.getString(metad.getColumnName(2))
+ "</td>"
+ "\n<td>"
+ rs.getString(metad.getColumnName(3))
+ "</td>"
+ "\n<td>"
+ rs.getString(metad.getColumnName(4))
+ "</td>"
+ "\n<td>"
+ rs.getString(metad.getColumnName(1))
+ "</td>"
+ "\n<td>"
+ rs.getString(metad.getColumnName(5))
+ "</td></tr>");
}
Date e = new Date();
e.getTime();
out.println("</table>\n");
out.println("<h5><p>Last updated: " + e.toString() + "</p></h5>\n</body>\n</html>");
}
}
}
index++;
}
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
