PDA

View Full Version : Simple SQL Class I made that might help you with sql querying



TheBuzzer
2 Sep 2008, 12:34 PM
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package org.ottoos.server;

import java.sql.*;
import java.util.*;
/**
*
* @author dlee
*/
public class SQLTable {

private Vector<TreeMap> vectortable = new Vector<TreeMap>();
private Connection myconnection;


public SQLTable() throws SQLException
{
try
{
// Driver mysqldriver = (Driver)Class.forName("org.gjt.mm.mysql.Driver").newInstance();
myconnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + SharedData.DATABASE,SharedData.DATABASEUSERNAME,SharedData.DATABASEPASSWORD);
}
catch (SQLException e)
{
throw e;
}
}

public Boolean makeTable(String sql) throws SQLException
{
Statement mystatement = myconnection.createStatement();
ResultSet myresult = mystatement.executeQuery(sql);

vectortable = new Vector<TreeMap>();
int i = 0;

try
{
while (myresult.next())
{
TreeMap table = new TreeMap();
for (int j = 1; j <= myresult.getMetaData().getColumnCount(); j++)
table.put(myresult.getMetaData().getColumnLabel(j), myresult.getObject(j));

vectortable.add(table);

i++;
}
}
catch (SQLException e)
{
throw e;
}

myresult.close();
mystatement.close();
return (i != 0);
}

public Vector<TreeMap> getTable()
{
return vectortable;
}

public TreeMap getRow()
{
return vectortable.get(0);
}

public Object getCell(String filename)
{
return vectortable.get(0).get(filename);
}

public Object getValue()
{
return vectortable.get(0).get(vectortable.get(0).firstKey());
}

public int insertRow(String table,TreeMap mykeys) throws SQLException
{
String query = "INSERT INTO `"+ table +"` (";

Object mykey[] = mykeys.keySet().toArray();
for (int i = 0; i < mykeys.size(); i++)
{
query += "`" + mykey[i] + "`,";
}
query = query.substring(0,query.length()-1);
query += ") VALUES (";
for (int i = 0; i < mykeys.size(); i++)
{
query += "?,";
}
query = query.substring(0,query.length()-1);
query += ")";

PreparedStatement mystatement = myconnection.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < mykeys.size(); i++)
{
mystatement.setObject(i+1, mykeys.get(mykey[i]));
}
mystatement.execute();
ResultSet myresult = mystatement.getGeneratedKeys();
if (myresult.next())
{
int index = myresult.getInt(1);
myresult.close();
mystatement.close();
return index;
}
else
return -1;
}

public Boolean checkExist(String sql) throws SQLException
{
Statement mystatement = myconnection.createStatement();
ResultSet myresult = mystatement.executeQuery(sql);
return (myresult.next());
}


public void insertUpdateRow(String table,TreeMap mykeys, TreeMap myfilter) throws SQLException
{
String filterstring = "";
Object myfilters[] = myfilter.keySet().toArray();
for (int i = 0; i < myfilter.size(); i++)
{
filterstring += "`" + myfilters[i] + "` = '" + myfilter.get(myfilters[i]) + "' AND";
}
filterstring = filterstring.substring(0,filterstring.length()-3);

if (checkExist("SELECT * FROM `" + table + "` WHERE " + filterstring))
{
String filler = "";
Object keyset[] = mykeys.keySet().toArray();
for (int i = 0; i < mykeys.size(); i ++)
{
filler += "`" + keyset[i] + "` = ?,";
}
filler = filler.substring(0,filler.length()-1);
String update = "UPDATE `" + table + "` SET " + filler + " WHERE " + filterstring;
PreparedStatement mystatement = myconnection.prepareStatement(update);
for (int i = 0; i < mykeys.size(); i++)
{
mystatement.setObject(i+1, mykeys.get(keyset[i]));
}
mystatement.execute();
mystatement.close();
}
else
{
insertRow(table, mykeys);
}
}

public void closeSQL() throws Exception
{
myconnection.close();
}


}



All this does is simple stuff.

Example:


// Start the sql connection
SQLTable mytable = new Table();
// Get A query of a table, row, value
mytable.makeTable("sqlcommand"); // also returns false if emply result set
// gets the table
Vector<TreeMap> mydata = mytable.getTable();
// gets the row
TreeMap mydata = mytable.getRow()
// gets the first row
TreeMap mydata = mytable.getRow()
// get the first value of first row
Object value = mytable.getValue();

// To insert a new row or update a row
TreeMap keys = new TreeMap();
keys.put(field,value);

// Filter keys are to match for existing keys to update a row
TreeMap filterKeys = new TreeMap();
filterkeys.put(field,value);

// To insert a new row or update a row if found
mytable.insertUpdateRow(tablename, keys , filterKeys );

// To insert a row only
mytable.insertRow(table, keys );

// close the sql connection
mytable.close();



This is how I interact with sql database. This way is simple and fast for me but I never used hibernate to try to make a table using that.