Magician Magician - 23 days ago 7
Java Question

Setting parameters dynamically for PreparedStatements

I am curious, can we just enter parameter dynamically? For example, I have an object like:

class Fields {
private Integer id;
private String name;

public Fields(Integer id, String name) {
this.id = id;
this.name = name;
}
//Getter - setter here
}


Now, I prepare statement for:

public void main() {
PreparedStatement ps = conn.PrepareStatement("SELECT * FROM table WHERE id = ? AND name = ?");`
}


then I set

Fields param = new Fields(2, "xxx");


Well.. call me lazy, how can I send param to PrepareStatement like

ps.setParameters(param)


or maybe:

ps.setParameters(param.getAsArray())


To illustrate my point better, I want to make something like:

for (Fields f: fieldList) {
sql += f + " = ?";
}


pardon my missing "AND" / "OR" operator for above codes. My ultimate goal, is so that I can create the whole fields and respective parameters by passing entity class by filling only partial field. The code will then skip null fields and put filled fields as parameter.

Well, just for curiosity.

Thank you

Answer

Ok.. this is a very crude solution. I still don't like the fact that I have to reiterate all fields twice to get field's type String or otherwise, but feel free to refine it further since this is still very crude. Also, you need to extend those ps.setSomething() parameter below before ps.setObject() failsafe. You also need to anticipate query without any criteria, like skipping those criteria object scanning and go straight to execute. But I hope you get the idea.

public static String addPrefix(String prefix, String field) {
    return new StringBuilder(prefix)
            .append(Character.toUpperCase(field.charAt(0)))
            .append(field.substring(1))
            .toString();
}

public static <T> List<T> query(Connection conn, T criteria, String operator) throws SQLException {
    List<T> list = null;
    Class<?> targetClass = criteria.getClass();
    if (targetClass.getAnnotation(Table.class) == null) throw new SQLException("ERROR: Table not defined at entity class " + targetClass.getName());
    StringBuilder SQL = new StringBuilder("SELECT * FROM ").append(targetClass.getAnnotation(Table.class).name());
    List<Object> parameters = new ArrayList<>();

    try {
        Field[] fields = targetClass.getDeclaredFields();
        for (Field field : fields) {
            if (field.getAnnotation(Column.class) == null) continue;
                Method m = targetClass.getMethod(addPrefix("get", field.getName()).toString());
                Object o = m.invoke(criteria);

                if (o == null) continue;

                if (parameters.isEmpty()) SQL.append(" WHERE"); else SQL.append(operator);
                SQL.append(" ").append(field.getAnnotation(Column.class).name()).append(" = ?");
                parameters.add(o);
        }

        try (Connection connection = IwiPrivate.getInstance().getConnection()) {
            try (PreparedStatement ps = connection.prepareStatement(SQL.toString())) {
                Integer x = 1;
                for (Field field : fields) {
                    String type = field.getType().getName();
                    Method m = targetClass.getMethod(addPrefix("get", field.getName()));
                    Object o = m.invoke(criteria);

                    if (o == null) continue;
                    if (type ==  "java.lang.String") ps.setString(x, (String) parameters.get(x));
                    else if (type == "java.lang.Integer") ps.setInt(x, (Integer) parameters.get(x));
                    else ps.setObject(x, parameters.get(x)); //Put more set traps here.
                }
                try (ResultSet rs = ps.executeQuery();) {
                    while (rs.next())
                        list.add((T) Database.mapSingle(rs, targetClass));
                }
            }
        }
    } catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException ex) {
        Logger.getLogger(QueryExperiment.class.getName()).log(Level.SEVERE, null, ex);
    }
    return list;
}

Now, to use it, simply create your entity object like this

@Table(name = "testTable")
public class Entity {
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Then put it as criteria

public void testQuery() {
    Entity criteria = new Entity();
    criteria.setId(7777);
    try (Connection connection = yourDatabase.getConnection()) {
        List<Entity> assets = QueryTest.query(connection, criteria, "AND");
    } catch (SQLException ex) {
        Logger.getLogger(IwiPrivateTest.class.getName()).log(Level.SEVERE, null, ex);
    }
}

It will create an SQL like: SELECT * FROM testTable WHERE id = ? and send 7777 as parameter using setInt

If you want to avoid ORM and create simple queries, I believe this method works fine.

Comments