SQLTool (Christmas Release)

[QuietCrystal]

SQL Tool

Been having this idea for quite some time now, but I never got around to making it a reality. In light of a certain Christmas challenge, I've gotten around to doing it.

The idea behind this release is to simplify the execution of SQL statements. I'm sure it's not new to some, but to most this will make life easier. Here's an example:

Spoiler:

Original saveNewCharToDB (MapleCharacter.java)

Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("INSERT INTO characters (`level`, `str`, `dex`, `int`, `luk`, `hp`, `mp`, `maxhp`, `maxmp`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", DatabaseConnection.RETURN_GENERATED_KEYS);
ps.setInt(1, chr.level);
final PlayerStats stat = chr.stats;
ps.setInt(2, stat.getStr());
ps.setInt(3, stat.getDex());
ps.setInt(4, stat.getInt());
ps.setInt(5, stat.getLuk());
ps.setInt(6, stat.getHp());
ps.setInt(7, stat.getMp());
ps.setInt(8, stat.getMaxHp());
ps.setInt(9, stat.getMaxMp());
ResultSet rs = ps.getGeneratedKeys();
// Use ResultSet rs
rs.close(); 

New method

SQLTool st = new SQLTool(INSERT, "characters");
st.addValue("level", chr.getLevel());
final PlayerStats stat = chr.stats;
st.addValue("str", stat.getStr());
st.addValue("dex", stat.getDex());
st.addValue("int", stat.getInt());
st.addValue("luk", stat.getLuk());
st.addValue("hp", stat.getHp());
st.addValue("mp", stat.getMp());
st.addValue("maxhp", stat.getMaxHp());
st.addValue("maxmp", stat.getMaxMp());
ResultSet rs = st.execute();
// Use ResultSet rs
st.closeResultSet(); 

The pros? Clarity, less confusion, and should you choose to edit the columns in characters, you don't need to waste time counting the indexes of columns.

How to use this release? Add this file under the "Tools" folder in your source.

SQLTool.java

Spoiler:

package tools;

import database.DatabaseConnection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;
import static tools.SQLTool.SQLStatementType.*;

/**
 *
 * @author QuietCrystal [MENTION=1333426735]RaGEZONE[/MENTION]
 */
public class SQLTool {

    private String tableName;
    private final SQLStatementType statementType;
    private final HashMap<String, String> value;
    private final HashMap<String, String> condition;
    private final List<String> order;

    private ResultSet rs = null;

    public static enum SQLStatementType {
        SELECT,
        INSERT,
        UPDATE,
        DELETE;
    }

    public SQLTool(SQLStatementType type) {
        statementType = type;
        value = (statementType != DELETE) ? new HashMap<>() : null;
        condition = (statementType != INSERT) ? new HashMap<>() : null;
        order = (statementType == SELECT) ? new ArrayList<>() : null;
    }

    public SQLTool(SQLStatementType type, String table) {
        this(type);
        tableName = table;
    }

    public void setTable(String table) {
        rs = null;
        tableName = table;
    }

    public String getTable() {
        return tableName;
    }

    public void addValue(String colmName) {
        if (value == null) {
            return;
        }
        rs = null;
        value.put(colmName, statementType == SELECT ? "" : "DEFAULT");
    }

    public void addValue(String colmName, Object colmValue) {
        if (value == null || !canSql(colmValue)) {
            return;
        }
        rs = null;
        value.put(colmName, "'" + convertSql(colmValue) + "'");
    }

    public HashMap<String, String> getValues() {
        return value;
    }

    public void addCondition(String colmName, Object colmValue) {
        if (condition == null || !canSql(colmValue)) {
            return;
        }
        rs = null;
        condition.put(colmName, convertSql(colmValue));
    }

    public HashMap<String, String> getConditions() {
        return condition;
    }

    public void addOrder(String colmName) {
        addOrder(colmName, true);
    }

    public void addOrder(String colmName, boolean isAsc) {
        if (order == null) {
            return;
        }
        rs = null;
        order.add("`" + colmName + "` " + (isAsc ? "ASC" : "DESC"));
    }

    public List<String> getOrder() {
        return order;
    }

    public ResultSet execute() {
        rs = null;
        StringBuilder statement = new StringBuilder();
        switch (statementType) {
            case SELECT:
                statement.append("SELECT");
                if (value.isEmpty()) {
                    statement.append(" *");
                } else {
                    boolean firstEntry = true;
                    for (String colm : value.keySet()) {
                        statement.append(firstEntry ? " " : ", ").append(colm);
                        firstEntry = false;
                    }
                }
                statement.append(" FROM ").append(tableName);
                if (!condition.isEmpty()) {
                    statement.append(" WHERE");
                    boolean firstEntry = true;
                    for (Entry<String, String> val : condition.entrySet()) {
                        statement.append(firstEntry ? "" : " AND").append(" ").append(val.getKey()).append(" = '").append(val.getValue()).append("'");
                        firstEntry = false;
                    }
                }
                if (!order.isEmpty()) {
                    statement.append(" ORDER BY");
                    boolean firstEntry = true;
                    for (String ord : order) {
                        statement.append(firstEntry ? " " : ", ").append(ord);
                    }
                }
                break;
            case INSERT:
                statement.append("INSERT INTO ").append(tableName).append(" ");
                List<String> values = new ArrayList<>();
                if (!value.isEmpty()) {
                    boolean firstEntry = true;
                    for (Entry<String, String> val : value.entrySet()) {
                        statement.append(firstEntry ? "(`" : ", `").append(val.getKey()).append("`");
                        values.add(val.getValue());
                        firstEntry = false;
                    }
                    statement.append(") ");
                }
                statement.append(value.isEmpty() ? "DEFAULT " : "").append("VALUES ");
                if (!value.isEmpty()) {
                    boolean firstEntry = true;
                    for (String val : values) {
                        statement.append(firstEntry ? "(" : ", ").append(val);
                        firstEntry = false;
                    }
                    statement.append(")");
                }
                break;
            case UPDATE:
                statement.append("UPDATE ").append(tableName);
                if (!value.isEmpty()) {
                    statement.append(" SET ");
                    boolean firstEntry = true;
                    for (Entry<String, String> val : value.entrySet()) {
                        statement.append(firstEntry ? "`" : ", `").append(val.getKey()).append("` = ").append(val.getValue());
                        firstEntry = false;
                    }
                }
                if (!condition.isEmpty()) {
                    statement.append(" WHERE");
                    boolean firstEntry = true;
                    for (Entry<String, String> val : condition.entrySet()) {
                        statement.append(firstEntry ? "" : " AND").append(" ").append(val.getKey()).append(" = '").append(val.getValue()).append("'");
                        firstEntry = false;
                    }
                }
                break;
            case DELETE:
                statement.append("DELETE FROM ").append(tableName);
                if (!condition.isEmpty()) {
                    statement.append(" WHERE");
                    boolean firstEntry = true;
                    for (Entry<String, String> val : condition.entrySet()) {
                        statement.append(firstEntry ? "" : " AND").append(" ").append(val.getKey()).append(" = '").append(val.getValue()).append("'");
                        firstEntry = false;
                    }
                }
                break;
        }
        try {
            PreparedStatement ps;
            switch (statementType) {
                case INSERT:
                case UPDATE:
                    ps = DatabaseConnection.getConnection().prepareStatement(statement.toString(), DatabaseConnection.RETURN_GENERATED_KEYS);
                    break;
                case SELECT:
                    ps = DatabaseConnection.getConnection().prepareStatement(statement.toString(), ResultSet.CONCUR_UPDATABLE);
                    break;
                default:
                    ps = DatabaseConnection.getConnection().prepareStatement(statement.toString());
                    break;
            }
            ps.execute();
            rs = ps.getGeneratedKeys();
        } catch (SQLException ex) {
            System.err.println("Error in executing SQL statement "" + statement.toString() + "": " + ex);
        }
        return rs;
    }

    public ResultSet getResultSet() {
        return (rs != null) ? rs : execute();
    }

    private static final Set<Class> WRAPPER_TYPES = new HashSet(Arrays.asList(
            Boolean.class, Character.class, Byte.class, Short.class, Integer.class, Long.class, Float.class, Double.class, Void.class)
    );

    private boolean canSql(Object obj) {
        return WRAPPER_TYPES.contains(obj.getClass());
    }

    private String convertSql(Object obj) {
        return String.valueOf(obj);
    }
} 

How do you use SQLTool?

Spoiler:

Step 1
Create a new SQLTool.
SQLTool([MODE: SELECT/INSERT/UPDATE/DELETE], [TABLE NAME])

SQLTool st = new SQLTool(UPDATE, "characters"); 

Step 2
Set the parameters.

Spoiler:

Values
[SELECT] : SQLTool.addValue([COLUMN NAME]) to specify which values to retrieve. Leave blank to get all columns
[INSERT] OR [UPDATE]: SQLTool.addValue([COLUMN NAME], [VALUE]) to set values.

Conditions
[SELECT], [UPDATE] OR [DELETE]: SQLTool.addCondition([COLUMN NAME], [VALUE]) to set conditions.

Order
[SELECT]: SQLTool.addOrder([COLUMN NAME], [OPTIONAL TRUE/FALSE: IS ASCENDING ORDER])
If not specified, the default order is ascending

Limit
[SELECT]: SQLTool.setLimit([LIMIT])

closeResultSet()
Execute

ResultSet rs = st.execute(); 

After using the ResultSet, don't forget to close it with

st.closeResultSet() 

Not convinced of the simplicity? More examples.

Spoiler:

Get the name and level of a character

Spoiler:

SQLTool st = new SQLTool(SELECT, "characters");
st.addValue("name");
st.addValue("level");
st.addCondition("id", id);
st.execute(); 

Result

SELECT level, name FROM `characters` WHERE `id` = '42'

Get the top 100 non-GM players based on level

Spoiler:

SQLTool st = new SQLTool(SELECT, "characters");
st.addValue("name");
st.addCondition("gmlevel", 0);
st.addOrder("level", false);
st.setLimit(100);
st.execute(); 

Result

SELECT name FROM `characters` WHERE `gmlevel` = '0' ORDER BY `level` DESC LIMIT 100

Wipe the server of levels, jobs and mesos

Spoiler:

SQLTool st = new SQLTool(UPDATE, "characters");
st.addValue("level", 1);
st.addValue("job", 0);
st.addValue("meso", 0);
st.execute(); 

Result

UPDATE `characters` SET `level` = '1', `meso` = '0', `job` = '0'

Set a player to GM

Spoiler:

SQLTool st = new SQLTool(UPDATE, "characters");
st.addValue("gmlevel", 1);
st.addCondition("name", chr.getName());
st.execute(); 

Result

UPDATE `characters` SET `gmlevel` = '1' WHERE `name` = 'Tectonic'

Delete level 1 beginner characters

Spoiler:

SQLTool st = new SQLTool(DELETE, "characters");
st.addCondition("level", 1);
st.addCondition("job", 0);
st.execute(); 

Result

DELETE FROM `characters` WHERE `level` = '1' AND `job` = '0'

Suggested DIYs:
- Improve on canSQL() and convertSQL() to include other types like dates, streams and what not
- Allow for greater complexity (Greater/less than conditions, OR conditionals, BETWEEN range, etc)

But for basic private servers, this will cover 99% of everything.

Enjoy (:

[MentaL]

[Eric]

Haven't seen a release from you in some time. I love this, though! This is soo much more easier than manually adding INSERT into lol VALUES (?, ?) blah blah. It'd also help noobs a lot too, I remember when I had troubles with the result sets.. ahh.

Nice release ;P

[sunnyboy]

Pretty neat idea, I just did the lazy way and i++

[QuietCrystal]

Haven't seen a release from you in some time. I love this, though! This is soo much more easier than manually adding INSERT into lol VALUES (?, ?) blah blah. It'd also help noobs a lot too, I remember when I had troubles with the result sets.. ahh.

Nice release ;P

It's been a while, yeah XD Been coding small things like NPCs and custom features, and those are the kind of stuff I'd prefer not to release. My releases are aimed more at providing ideas and structures than showing off something copy-paste-able.

Glad to see that you're looking out for my releases :D *flattered*

[Tsundere]

This is a very intuitive idea. Regardless if I use it or not, it looks like it could save a programmer a good bit of time if they're using SQL statements quite often.

[Utsukushi]

Awesum welise n Im drunk

[Luxray]

I like the idea, but is it more efficient?

[Novak]

Well, i've been messing around with MapleCharacter.java and therefore just as in the example saveNewCharToDB a lot lately, and this kind of removes the requirement of scrolling back up and checking if i put everything in the right order. So for me personally, it really does make my work more efficient. ;). Great x-mas release. Merry late-christmas~~

If it's indeed more efficient server performance-wise, I have no idea, but I like it anyways.

[SharpAceX]

I like the idea, but is it more efficient?

Well it obviously can't be more efficient than before because it's using the standard way in the background.

[Luxray]

Well it obviously can't be more efficient than before because it's using the standard way in the background.

Bad choice of words. Meant to ask if it was an efficient way of using it. Line-wise and it being noob-friendly.

[Zygon]

Bad choice of words. Meant to ask if it was an efficient way of using it. Line-wise and it being noob-friendly.

It is more efficient in terms of development time; but, execution-wise it would be slower because the full statement doesn't need to be built before execution. The difference is probably minimal enough if you don't care of course.