Foros de discusión
Custom Insert SQL?
Mike Harris, modificado hace 12 años.
Custom Insert SQL?
Junior Member Mensajes: 91 Fecha de incorporación: 28/03/11 Mensajes recientes
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!
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.
RE: Custom Insert SQL?
Liferay Master Mensajes: 993 Fecha de incorporación: 27/04/07 Mensajes recientes
Hi Mike,
you can leverage dynamic query feature in your plugins.
Jonas Yuan
you can leverage dynamic query feature in your plugins.
Jonas Yuan
Mike Harris, modificado hace 12 años.
RE: Custom Insert SQL?
Junior Member Mensajes: 91 Fecha de incorporación: 28/03/11 Mensajes recientes
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);
}
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);
}