I finally found a workaround creating a database for sorting and using the
CSV-read and write. Furthermore, the database tables in my database got
order columns.
This is the algorithm if somone is interested - it is fast enough - for
6000 records it tkaes 1 second to update the tables.
SessionFactory sortFactory = null; // HIbernate
boolean ok = false;
String deleteCSVFile = null;
logger.trace("Use csv sorting for table " + hibernateTable);
final String csvFile = IO.createTempFile(hibernateTable.toLowerCase(),
".csv", ApplicationFolder.getInstance().getTempFolder()).getCanonicalPath();
final Session mediaSession =
SessionFactoryHelper.getMediaFactory().getCurrentSession();
sortFactory = SessionFactoryHelper.getSortFactory();
final Session sortSession = sortFactory.openSession();
final String charSet = "UTF-8";
final Csv csv = new Csv();
final String usedTable = dbTable == null ? hibernateTable : dbTable;
deleteCSVFile = csvFile;
// create db sort table
sortSession.doWork(new Work()
{ @Override
public void execute(Connection conn) throws SQLException
{ JdbcSql.executeDML(conn, "DROP TABLE IF EXISTS sorttable", false);
JdbcSql.executeDML(conn,
HDMediaContext.getCollator().getSQLCollator(), false);
JdbcSql.executeDML(conn, "CREATE TABLE sorttable(tableid BIGINT NOT
NULL, text VARCHAR_IGNORECASE(1024) NOT NULL); CREATE INDEX
i_sorttable_text ON sorttable (text);", false);
}
});
// read csv from table to be sorted
mediaSession.doWork(new Work()
{ @Override
public void execute(Connection conn) throws SQLException
{ // write to file
csv.write(conn, csvFile, "select " + idColumn + " as tableid, " +
textColumn + " as text from " + usedTable, charSet);
}
});
// read csv into sort database
sortSession.doWork(new Work()
{ @Override
public void execute(Connection conn) throws SQLException
{ JdbcSql.insertSQL(conn, "INSERT INTO sorttable SELECT * from
CSVREAD('" + csvFile + "', null, 'charset=" + charSet + "')", true, false);
csv.write(conn, csvFile, "select tableid, text from sorttable order
by text", charSet);
}
});
// read sorted data from csv into table to be sorted
mediaSession.doWork(new Work()
{ @Override
public void execute(Connection conn) throws SQLException
{ ResultSet rs = csv.read(csvFile, null, charSet);
long insertCount = 0;
String sql;
while (rs.next())
{ sql = "UPDATE " + usedTable + " SET " + sortColumn + '=' +
insertCount + " WHERE " + idColumn + '=' + rs.getLong("TABLEID");
insertCount += JdbcSql.updateSQL(conn, sql, false, false);
}
rs.close();
// test if the number of records are the same
if (tableCount != insertCount)
{ throw new SQLException("Count is different for table " +
hibernateTable + "(table count: " + tableCount + ", insert count: " +
insertCount);
}
JdbcSql.commit(conn, true);
}
});
Am Freitag, 2. Mai 2014 09:23:25 UTC+2 schrieb Thomas Mueller:
>
> Hi,
>
> > Using SET COLLATION is only possible once.
>
> Yes. The reason is that the indexes are persisted (stored) in the database
> file, and changing the collation later on would mean all those indexes need
> to be re-created.
>
> > Is there a way to change the result order depending on the current
> locale in JAVA ?
> > select * from a where ... order by (*user function*);
>
> That's complicated, and the index could not be used.
>
> Regards,
> Thomas
>
>
> On Thu, May 1, 2014 at 3:49 PM, Thomas <[email protected] <javascript:>>wrote:
>
>> I use H2 in a JAVA-Application whicht allows the user to change the
>> language at runtime.
>>
>> So the sort order (collator) should also changed at runtime. However,
>> that is not possible currently.
>>
>> A way around could be to create order colums. However, updating these
>> columns for every data change is expansive. Furthermore itt cannot be done
>> using a trigger.
>>
>> So my question ist: can I create a SQL-Statement like:
>>
>> select * from a where ... order by (*user function*);
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]<javascript:>
>> .
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
