import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
public class MyQuery {
static Connection con = null; //Global Connection
static Statement smt = null; //Global Statement
Connection localCon = null; //Local Connection
Statement localSmt = null; //Local Statement
PreparedStatement localPrepSmt; //Prepared Statement
String attr = null; //Column names for Output
ResultSet erg = null; //Result Set
ResultSetMetaData meta = null; //Stores Meta Data
int rowCount; //rowCount for UPDATE/INSERT/DELETE Querys
char type; //Typ der Anweisung
/******************************************************* ESTABLISH GLOBAL CONNECTION */
public static void globalConnection(String server, String user, String pwd, String database){
try {
Class.forName( "com.mysql.jdbc.Driver" ); //Load Driver
}
catch ( ClassNotFoundException e ) {
e.printStackTrace();
System.exit(1);
}
try {
con = DriverManager.getConnection("jdbc:mysql://"+server+"/"+database, user, pwd);
smt = con.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
/******************************************************* ESTABLISH LOCAL CONNECTION */
public void localConnection(String server, String user, String pwd, String database){
try {
localCon = DriverManager.getConnection("jdbc:mysql://"+server+"/"+database, user, pwd);
localSmt = localCon.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
/******************************************************* CONSTRUCTOR TO BUILD PREPARED STATEMENT */
public MyQuery(String query){ this(query, null); }
public MyQuery(String query, String attr) {
//Use Global Connection first
localCon = con;
localSmt = smt;
try {
localPrepSmt = con.prepareStatement(query);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
this.attr = attr;
if(query.startsWith("SELECT")) type = 'S';
else if(query.startsWith("UPDATE")) type = 'U';
else if(query.startsWith("DELETE")) type = 'D';
else type = 'I';
}
/******************************************************* EXECUTE PREPARED STATEMENT */
public HashMap<String, ArrayList<Object>> execute(Object ...rest){
HashMap<String, ArrayList<Object>> result = null;
try{
//Alle übergebenen Objekte in das Prepared Statement schreiben
for (int i = 0; i < rest.length; i++) {
if(rest[i] != null) localPrepSmt.setObject(i+1, rest[i]); //Setzt den Parameter, wenn null: lassen wie bisher
}
if(type == 'S'){
erg = localPrepSmt.executeQuery(); //SELECT Query Results
result = parse(erg);
} else if(type == 'U' || type == 'I' || type == 'D'){
rowCount = localPrepSmt.executeUpdate(); //UPDATE/INSERT/DELETE Query Rowcount
System.out.println(rowCount);
}
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
return result;
}
/******************************************************* EXECUTE INSTANT STATEMENT */
public static Object instantExecute(String query, String attr){
try {
smt.execute(query);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private HashMap<String, ArrayList<Object>> parse(ResultSet r){ return parse(r, null); }
private HashMap<String, ArrayList<Object>> parse(ResultSet r, String attr){
int i=0;
HashMap<String, ArrayList<Object>> result = new HashMap<String, ArrayList<Object>>();
try {
//Wenn der attr-String nicht gesetzt ist selbst zusammenbasteln
if(attr == null){
meta = r.getMetaData();
int cols = meta.getColumnCount();
attr = "";
for (i=1; i <= cols; i++) {
attr += ","+meta.getColumnName(i);
}
attr = attr.substring(1);
}
//2D Array erstellen
String[] colNames = attr.split(",");
for (i = 0; i < colNames.length; i++) {
result.put(colNames[i], new ArrayList<Object>());
}
//Mit Inhalt füllen
while(r.next()){
for (i=0; i < colNames.length; i++) {
ArrayList<Object> list = result.get(colNames[i]);
list.add( r.getObject(i+1) );
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
