Skip to content

Instantly share code, notes, and snippets.

@renzoster renzoster/wpdb.java
Created Jul 6, 2019

Embed
What would you like to do?
WP-DB port to Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class WPDB {
// Connection Vars
protected Connection con;
protected String url = "";
// Table Data
protected String table_name = null;
protected String primary_key = null;
protected int last_id = 0, num_results = 0, rows_affected = 0;
// Data Sets
protected LinkedHashMap<String, String> columns;
protected ArrayList<DBEntry> results;
protected static enum Format {
SQL, SSQL
};
// Private Vars
private String quotes = "\"";
protected Object format = Format.SQL;
public boolean connect_to(String url) {
try {
con = DriverManager.getConnection(url);
} catch (Exception e) {
}
return connected();
}
public boolean conectar_sqlite(String archivo) {
return connect_to(String.format("jdbc:sqlite:%s", archivo));
}
public boolean conectar_mysql(String host, String db, String usuario) {
return conectar_mysql(host, db, usuario, "");
}
public boolean conectar_mysql(String host, String db, String usuario, String pass) {
url = String.format("jdbc:mysql://%s/%s?user=%s&password=%s", host, db, usuario, pass);
return connect_to(url);
}
public boolean conectar_ssql(String host, String db) {
url = String.format("jdbc:sqlserver://%s;databaseName=%s;integratedSecurity=true;", host, db);
format = Format.SSQL;
quotes = "'";
return connect_to(url);
}
public boolean conectar_ssql(String host, String db, String usuario, String pass) {
url = String.format("jdbc:sqlserver://%s;databaseName=%s;user=%s;password=%s;", host, db, usuario, pass);
format = Format.SSQL;
quotes = "'";
return connect_to(url);
}
public void close() {
try {
con.close();
} catch (Exception e) {
}
}
public boolean isClosed() {
try {
return con.isClosed();
} catch (Exception e) {
}
return false;
}
public boolean reconnect() {
return connect_to(url);
}
public boolean connected() {
try {
return (con instanceof Connection) || con.isClosed();
} catch (Exception e) {
return false;
}
}
public int query(String query) {
if (!connected()) {
return -1;
}
try {
if (preg_match(query, "(create|alter|truncate|drop)")) {
Statement st = con.createStatement();
last_id = 0;
num_results = 0;
st.execute(query);
st.close();
} else if (preg_match(query, "(insert|delete|update|replace)")) {
PreparedStatement st = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
int rows_num = st.executeUpdate();
num_results = 0;
if (preg_match(query, "(insert|replace)")) {
ResultSet rs = st.getGeneratedKeys();
while (rows_num != 0 && !rs.next()) {
last_id = rs.getInt(1);
}
rs.close();
}
rows_affected = st.getUpdateCount();
st.close();
return rows_affected;
} else {
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query);
format_columns(rs);
format_results(rs);
num_results = results.size();
rs.close();
st.close();
return num_results;
}
return 0;
} catch (Exception e) {
System.out.println("====================================");
System.out.println("ALERTA: " + e.getMessage());
System.out.println(query);
System.out.println("====================================");
last_id = 0;
num_results = 0;
return -1;
}
}
public int insert(String tabla, DBEntry data) {
String query;
if (format == Format.SSQL) {
query = String.format("INSERT INTO %s %s", tabla, data.insertString());
} else {
query = String.format("INSERT INTO `%s` %s", tabla, data.insertString());
}
return 0;
}
public int update(String table, DBEntry data) {
return update(table, data, null);
}
public int update(String table, DBEntry data, DBWhere where) {
if (!connected()) {
return -1;
}
String query = String.format("UPDATE %s SET %s", table, data.updateString());
if (where != null) {
query += " " + where.toString();
}
return query(query);
}
private void format_columns(ResultSet rs) {
try {
columns = new LinkedHashMap<>();
ResultSetMetaData md = rs.getMetaData();
int total = md.getColumnCount();
for (int i = 1; total >= i; i++) {
columns.put(md.getColumnLabel(i), md.getColumnClassName(i));
}
} catch (Exception e) {
columns = null;
}
}
private void format_results(ResultSet rs) {
try {
results = new ArrayList<>();
while (rs.next()) {
DBEntry entry = new DBEntry();
for (String column : columns.keySet()) {
entry.add(column, rs.getObject(column));
}
results.add(entry);
}
} catch (Exception e) {
results = null;
}
}
public ArrayList<DBEntry> get_results() {
return results;
}
public ArrayList<DBEntry> get_results(String query) {
if (query != null) {
query(query);
}
return results;
}
public Object get_var() {
return get_var(null);
}
public Object get_var(String query) {
if (query != null) {
query(query);
}
try {
return (results != null) ? results.get(0).get() : null;
} catch (Exception e) {
return null;
}
}
public DBEntry get_row(String query) {
if (query != null) {
query(query);
}
try {
return results.get(0);
} catch (Exception e) {
return null;
}
}
public DBEntry get_row(int index) {
return get_val(index);
}
public Object get_val() {
return get_val(0, 0);
}
public DBEntry get_val(int index) {
return (results != null) ? results.get(index) : null;
}
public Object get_val(int index, int column) {
try {
return results.get(index).get(column);
} catch (Exception e) {
return null;
}
}
public Object get_val(int index, String column) {
try {
return results.get(index).get(column);
} catch (Exception e) {
return null;
}
}
public int get_num_results() {
try {
return results.size();
} catch (Exception ex) {
return 0;
}
}
public int get_last_id() {
return last_id;
}
public int get_rows_affected() {
return rows_affected;
}
public DBEntry create_entry() {
return new DBEntry();
}
public DBWhere create_where() {
return new DBWhere();
}
private boolean preg_match(String source, String regex) {
Pattern pattern = Pattern.compile(regex, Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(source);
return matcher.find();
}
public class DBWhere {
protected ArrayList<Object> data = new ArrayList<>();
public DBWhere add(String column, String operator, Object value) {
data.add(new DBWhereCondition(column, operator, value));
return this;
}
public DBWhere and(String union) {
data.add(union);
return this;
}
public DBWhere and() {
return and("AND");
}
public DBWhere or() {
return and("OR");
}
public DBWhere group() {
return and("(");
}
public DBWhere closeGroup() {
return and(")");
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("WHERE ");
for (int i = 0; data.size() > i; i++) {
if (data.get(i) instanceof String) {
try {
if ((data.get(i - 1) instanceof DBWhereCondition && data.get(i).equals("("))
|| (data.get(i - 1).equals(")") || data.get(i).equals("("))) {
sb.append(" AND ");
}
} catch (Exception e) {
}
sb.append(" ").append(data.get(i)).append(" ");
} else {
try {
if ((data.get(i - 1) instanceof DBWhereCondition)
|| (data.get(i - 1).equals(")"))) {
sb.append(" AND ");
}
} catch (Exception e) {
}
sb.append(data.get(i).toString());
}
}
return sb.toString();
}
}
protected class DBWhereCondition {
protected String column;
protected String operator = "=";
protected Object value;
public DBWhereCondition(String column, String operator, Object value) {
this.column = column;
this.operator = operator;
this.value = value;
}
@Override
public String toString() {
switch ((String) operator) {
case "=":
case "LIKE":
case "!=":
case ">":
case "<":
return String.format("%s %s %s%s%s", column, operator, isNumeric() ? "" : quotes, value, isNumeric() ? "" : quotes);
case "IN":
case "NOT IN":
case "EXISTS":
return String.format("%s %s (%s)", column, operator, join());
case "IS":
case "IS NOT":
if (value == null || value.equals("NULL")) {
return String.format("%s %s NULL", column, operator);
} else {
return String.format("%s %s (%s)", column, operator, join());
}
}
return "";
}
private String join() {
String output = "";
if (value.getClass().isArray()) {
int counter = 0;
for (Object item : (Object[]) value) {
if (item == null) {
continue;
}
if (counter > 0) {
output += ", ";
}
output += String.format("%s %s %s%s%s", column, operator, isNumeric() ? "" : quotes, value, isNumeric() ? "" : quotes);
}
} else {
output += value.toString();
}
return output;
}
private boolean isNumeric() {
return (value instanceof Double || value instanceof Float || value instanceof Integer || value instanceof Long);
}
}
public class DBEntry {
protected LinkedHashMap<String, Object> data = new LinkedHashMap<>();
public DBEntry() {
}
public DBEntry add(String column, Object value) {
data.put(column, value);
return this;
}
public Object get() {
return get(0);
}
public Object get(int column_index) {
try {
return data.get(data.keySet().toArray(new String[0])[column_index]);
} catch (Exception ex) {
return null;
}
}
public Object get(String column) {
try {
return data.get(column);
} catch (Exception ex) {
return null;
}
}
public Object[] toValuesArray() {
return data.values().toArray();
}
public Object[] toKeysArray() {
return data.keySet().toArray();
}
public int size() {
return data.size();
}
public String insertString() {
StringBuilder keys = new StringBuilder(), vals = new StringBuilder();
int colnum = 0;
for (Entry<String, Object> entry : data.entrySet()) {
if (colnum != 0) {
keys.append(", ");
vals.append(", ");
}
keys.append(entry.getKey());
vals.append(quotes).append(entry.getValue()).append(quotes);
colnum++;
}
return String.format("(%s) VALUES (%s)", keys.toString(), vals.toString());
}
public String updateString() {
StringBuilder output = new StringBuilder();
int num = 0;
for (Entry<String, Object> entry : data.entrySet()) {
if (num != 0) {
output.append(", ");
}
output.append(entry.getKey()).append(" = ").append(quotes).append(entry.getValue()).append(quotes);
num++;
}
return output.toString();
}
@Override
public String toString() {
StringBuilder output = new StringBuilder();
data.entrySet().stream().forEach((entry) -> {
output.append(entry.getKey()).append(" = ").append(entry.getValue()).append("\n");
});
return output.toString();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.