Getting the insert SQL statement string

[buck...@gmail.com]

Hi:

Is there a way to extract the sql statements ebean generates when the following code is executed:

Collection<?> records; // large collection of records
database.insertAll(records);


We'd like to have a ability to bulk insert large number of records without going outside of the ebean orm.
Ideally, we want to be able to do database.insertBulk(records) which would utilize the specific database's bulk insert
capabilities.  The bulk insert capabilities in postgres we'd like to use is CopyManager.  Using CopyManager to bulk insert
a collection of records is shown in the class below.  The "generateInputStream" method could use ebean's entity to sql mapping
so that we don't have to reinvent the wheel.

```java
public class PgBulkInserter
{
private DataSource dataSource;

public PgBulkInserter(DataSource dataSource)
{
this.dataSource = dataSource;
}

public void insertBulk(Collection<?> records)
{
if (records == null || records.isEmpty())
{
return;
}

Object firstRecord = records.iterator().next();
Class<?> recordClass = firstRecord.getClass();

Table table = recordClass.getAnnotation(Table.class);
if (table == null)
{
throw new IllegalArgumentException("Record class must be annotated with @Table");
}

String tableName = table.name();
Field[] fields = recordClass.getDeclaredFields();

StringJoiner columnNames = new StringJoiner(", ");
StringJoiner valuePlaceholders = new StringJoiner(", ");

for (Field field : fields)
{
Column column = field.getAnnotation(Column.class);
if (column != null)
{
columnNames.add(column.name());
valuePlaceholders.add("?");
}
}

String copySql = "COPY " + tableName + " (" + columnNames.toString() + ") FROM STDIN WITH (FORMAT csv)";

try (Connection connection = dataSource.getConnection())
{
CopyManager copyManager = new CopyManager((BaseConnection) connection);
InputStream inputStream = generateInputStream(records, fields);
copyManager.copyIn(copySql, inputStream);
}
catch (SQLException | IOException e)
{
e.printStackTrace();
throw new RuntimeException("Failed to execute bulk insert", e);
}
}

private InputStream generateInputStream(Collection<?> records, Field[] fields) throws IOException
{
StringBuilder sb = new StringBuilder();

DateTimeFormatter instantFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSS'Z'");
DateTimeFormatter localDateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSS");
DateTimeFormatter localDateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

for (Object record : records)
{
StringJoiner valueJoiner = new StringJoiner(", ");

for (Field field : fields)
{
Column column = field.getAnnotation(Column.class);
if (column != null)
{
field.setAccessible(true);
Object value = null;
try
{
value = field.get(record);
}
catch (IllegalAccessException e)
{
e.printStackTrace();
throw new RuntimeException("Failed to access field value", e);
}

if (value == null)
{
valueJoiner.add("");
}
else if (value instanceof String || value instanceof Character)
{
valueJoiner.add("\"" + value.toString().replace("\"", "\"\"").replace(",", "\\,") + "\"");
}
else if (value instanceof byte[])
{
valueJoiner.add(new String((byte[]) value));
}
else if (value instanceof Instant)
{
valueJoiner.add("\"" + instantFormatter.format((Instant) value) + "\"");
}
else if (value instanceof LocalDateTime)
{
valueJoiner.add("\"" + localDateTimeFormatter.format((LocalDateTime) value) + "\"");
}
else if (value instanceof LocalDate)
{
valueJoiner.add("\"" + localDateFormatter.format((LocalDate) value) + "\"");
}
else
{
valueJoiner.add(value.toString());
}
}
}

sb.append(valueJoiner.toString()).append("\n");
}

return new ByteArrayInputStream(sb.toString().getBytes());
}
}

```

[Rob Bygrave]

Just to say I see this and pondering it. The sql generation doesn't match as is because this is really writing csv content using sql literal values (vs sql with bind placeholders ?). In that sense I think this is a new feature / might require a toLiteral() method on ScalarType or something like that / can't really use the existing format() method that is there.

Cheers, Rob.