Custom Insert SQL?

[Mike Harris, modificado hace 12 años.]

Hello,

I'd like to execute a "special" INSERT statement and I don't think I can use normal methods from my models... Is there a way to use a custom SQL for a statement like this :

INSERT IGNORE INTO my_table(id_, table2_id, amount)
SELECT 1, 2, 10
FROM my_table
WHERE NOT EXISTS (SELECT 1 FROM my_table WHERE amount >= 10 AND table2_id = 1)
LIMIT 1

Also, I need to know if a row was inserted or not.

Thank you!

[Jonas Yuan, modificado hace 12 años.]

Hi Mike,

you can leverage dynamic query feature in your plugins.

Jonas Yuan

[Mike Harris, modificado hace 12 años.]

I used something like that, but in a "Finder" :

String sql = "INSERT IGNORE INTO ... LIMIT 1";
Session session = null;

try {

session = openSession();

SQLQuery q = session.createSQLQuery(sql);

QueryPos qPos = QueryPos.getInstance(q);

qPos.add(param1);
qPos.add(param2);
etc..

nbAffectedRow = q.executeUpdate();

} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}