How to create custom INSERT INTO query in Ebean?

Question

I need to fill a table with large amount of data so I don't want to find related objects, but just put numeric values of them. For this I'd build a simple query ie:

INSERT INTO article_category (article_id, category_id) VALUES (2,12);

anyway can't find a way to do this with Ebean, I was trying:

RawSql rawSql = RawSqlBuilder
    .parse("INSERT INTO article_category (article_id, category_id) VALUES (2,12)")
    .create();

however that throws an exception:

[RuntimeException: Error parsing sql, can not find SELECT keyword in:INSERT INTO article_category (article_id, category_id) VALUES (2,12)]

How can I call really raw query with Ebean ?

Attenweiler

Answer 1

Actually I found a solution, it's com.avaje.ebean.SqlUpdate which can be used for DELETES, UPDATES and INSERTS statements:

SqlUpdate down = Ebean.createSqlUpdate("DELETE FROM table_name WHERE id = 123");
down.execute(); 

SqlUpdate insert = Ebean.createSqlUpdate("INSERT INTO article_category (article_id, category_id) VALUES (2,12)");
insert.execute(); 

etc. Of course it also allows to set named parameters in the queries (sample from its API):

String s = "UPDATE f_topic set post_count = :count where id = :id"
SqlUpdate update = Ebean.createSqlUpdate(s);
update.setParameter("id", 1);
update.setParameter("count", 50);

int modifiedCount = Ebean.execute(update);

Edit

There is also similar method for selecting rows without corresponding models from DB: com.avaje.ebean.SqlQuery

Attenweiler

One note of caution, at least with the version of ebean included in Play 2.3.x (which is 3.something), any query that consists of less than three words will fail with this solution (such as "LOCK <table>"). It seems that ebean is trying to break the query apart in it's code and find the 3rd word, which if it doesn't exist, throws an exception like, "String index out of range" – Jamila

Hmmm, ebean has caused me a lot of grief – for some reason, in my case, executing an INSERT … ON DUPLICATE KEY UPDATE … returns a value 1, which means a row was affected. But for some reason, the query gets rolled back and there's nothing in the table when I query it manually. Strange. – Possession

Answer 2

You can also use plain old JDBC connection and statement instead of Ebean.

Something similar to this:

Connection connection = play.db.DB.getConnection();

Statement stmt = connection.createStatement();
stmt.executeUpdate("INSERT INTO article_category (article_id, category_id) VALUES (2,12)");

stmt.close();

Monteiro

nico: the Ebean's methods did the trick and are quite clean approach, anyway thx for JDBC sample. – Attenweiler