<title>SqlUpdate | Ebean</title>

<meta name="layout" content="_layout2/base-docs.html"/>

<meta name="bread1" content="Query" href="/docs/query/"/>

<meta name="bread2" content="SqlUpdate" href="/docs/query/sqlupdate"/>

<#assign n0_docs="true">

<#assign n1_query="true">

<#assign sqlupdate= "true">

<h2>SqlUpdate</h2>

<em>SqlUpdate</em> allows us to execute <em>sql insert</em>, <em>sql update</em> or <em>sql delete</em> statements.

<p>&nbsp;</p>

<h5>Example: simple update</h5>

```java

String sql = "update audit_log set description = description where id = ?";

int row = DB.sqlUpdate(sql)

.setParameter(1, 42)

.execute();

```

<p>&nbsp;</p>

<h5>Example: insert with getGeneratedKeys</h5>

```java

String sql = "insert into e_person_online (email, online_status, when_updated) " +

"values (:email, :online, current_time)";

SqlUpdate sqlUpdate = DB.sqlUpdate(sql)

.setGetGeneratedKeys(true)

.setParameter("email", email)

.setParameter("online", false);

sqlUpdate.execute();

Object key = sqlUpdate.getGeneratedKey();

```

<p>&nbsp;</p>

<h2 id="collections" class="art">Binding collections</h2>

To bind collections/arrays of values into an IN clause we need to use

indexed positioned parameters like <code>?1, ?2, ?3</code> etc or

named parameters like <code>:ids, :names</code> etc.

```java

// using ?1 index positioned parameter

String sql = "delete from customer where ref_id in (?1)";

int rows = DB.sqlUpdate(sql)

.setParameter(asList(9991, 9992, 9993))

.execute();

```

```sql

delete from customer where ref_id in (?,?,?)

```

```java

// using :ids named parameter

String sql = "delete from customer where ref_id in (:ids)";

int rows = DB.sqlUpdate(sql)

.setParameter("ids", asList(9991, 9992, 9993))

.execute();

```

```sql

delete from customer where ref_id in (?,?,?)

```

<p>&nbsp;</p>

<h5>Example: binding multiple lists</h5>

```java

String sql = "delete from customer where ref_id in (:ids) and name in (:names)";

int rows = DB.sqlUpdate(sql)

.setParameter("ids", asList(9991, 9992, 9993))

.setParameter("names", asList("rob", "jim"))

.execute();

```

```sql

delete from customer where ref_id in (?,?,?) and name in (?,?)

```

<h2 id="cte" class="art">CTE - Common table expression</h2>

When we want to perform a bulk update using a <em>common table expression</em> this is

easiest done using SqlUpdate.

<h5>CTE Example</h5>

```java

String sql = """

WITH t AS (

SELECT s.id AS _id

FROM store_price s

JOIN promotion_vw p ...

WHERE ...

)

UPDATE store_price

SET promotion_price = null, price = active_price, price_reason = ?,

version = version+1, when_modified = current_timestamp

FROM t

WHERE id = t._id

"""

int rows = DB.sqlUpdate(sql)

.setParameter(1, "Expired promotions")

.execute();

```

<h2 id="addBatch" class="art">AddBatch and ExecuteBatch</h2>

We use <em>addBatch()</em> and <em>executeBatch()</em> to explicitly use JDBC batching.

```java

String sql = "insert into audit_log (id, description, modified_description) values (?,?,?)";

SqlUpdate insert = DB.sqlUpdate(sql);

try (Transaction txn = DB.beginTransaction()) {

insert.setNextParameter(10000);

insert.setNextParameter("hello");

insert.setNextParameter("foo");

insert.addBatch();

insert.setNextParameter(10001);

insert.setNextParameter("goodbye");

insert.setNextParameter("bar");

insert.addBatch();

insert.setNextParameter(10002);

insert.setNextParameter("chow");

insert.setNextParameter("baz");

insert.addBatch();

int[] rows = insert.executeBatch();

txn.commit();

}

```

<h2 id="upsert" class="art">Upsert</h2>

We can execute upsert sql which is generally database specific.

<h4>Upsert - Postgres</h4>

```java

String sql =

"insert into e_person_online (email, online_status, when_updated) values (?, ?, now()) " +

"on conflict (email) do update set when_updated=now(), online_status = ?";

String email = "foo@one.com";

Object key = DB.sqlUpdate(sql)

.setGetGeneratedKeys(true)

.setParameter(1, email)

.setParameter(2, true)

.setParameter(3, true)

.executeGetKey();

```

<p>&nbsp;</p>

<h4>Upsert - MySql</h4>

```java

String email = "bar@one.com";

String sql =

"insert into e_person_online (email, online_status, when_updated) values (?, ?, current_time) " +

"on duplicate key update when_updated=current_time, online_status = ?";

Object key = DB.sqlUpdate(sql)

.setGetGeneratedKeys(true)

.setParameter(1, email)

.setParameter(2, true)

.setParameter(3, true)

.executeGetKey();

```

<h2 id="l2cache" class="art">L2 cache</h2>

If L2 caching is being used Ebean will by default automatically try and determine what

table modifications are performed and use this to invalidate appropriate parts of the L2 cache.

Use <code>setAutoTableMod(false)</code> to turn this off.

<@next_edit "CallableSql" "/docs/query/callablesql" "/docs/query/sqlupdate.html"/>