Loading external data for Tree

Support
#1

Hi to everyone,
I need to create rather complex tree with many levels. I tried to make script for db to add data for tree, but ID of elements are generated automatically and looks like “42c08b6e-0dd7-eccd-3157-2253fafb67bc” and I don’t know how to create such ID myself.
How can I load data for tree so that it builds automatically by this data ?

#2

Hey!

What kind of database do you use?

For example, for MSSQL, you can use db function NEWID()

#3

I use hsql.
Some more letters, because reply cannot be shorter then 20

#4

use smile :slight_smile:

#5

In HSQL, there is no internal function for creating a UUID.
You can create a groovy script to generate data, and use UuidProvider to generate new ids

#6

Are there any other way to build tree?
When I trying to change my db on MSSQL I get error.

#7

And what is this error?

#8

Trying to connect to jdbc:sqlserver://localhost;databaseName=trytree
[17:32:52.802] Connection failed
The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

#9

Hey!
Is the database instance deployed locally or on the network?
Instance named or default?

#10

BTW, CUBA init scripts create newid() function for all databases including HSQL. On MS SQL it is native indeed.

Also, if you create some data in scripts, you can write SQL INSERTs with literal values like

insert into SEC_GROUP (ID, CREATE_TS, VERSION, NAME, PARENT_ID)
values ('0fa2b1a5-1d68-4d69-9fbd-dff348347f93', current_timestamp, 0, 'Company', null)^

So you can easily model your tree if needed.

#11

many thanks, I understand you
But now I have another problem. If I insert all elements of tree in db, It will have more then 700 000 records(because tree have 12 levels). So is there any way to generate children for tree element when element is picked?

#12

At what moment do you generate the tree? Is it predefined or created by users?

#13

when app start or when user open window with tree, somewhere at that moment

#14

I mean data for the tree. Where it comes from? Do you have it in CSV or something?

#15

Sorry for misunderstanding, I have CSV file

#16

OK, now I think I understand your situation. You need to import some massive data into the system. Creating a SQL script from CSV with 700K rows looks too extravagant to me. I would suggest creating a data import component in your project:

  • Create a JMX bean with a method accepting the path to the CSV file

  • In this method, read the file and parse it using some library, e.g. Commons CSV – Home

  • Go through the rows, create/persist appropriate entities. Start/commit transactions for each row or for batches of moderate size.

1 Like
#17

Couldn’t you give me some code example for stage three?

#18

Something like this:

List<Map<String,String> rows;

for (Map<String,String> row : rows) {
    try (Transaction tx = persistence.createTransaction()) {
        EntityManager em = persistence.getEntityManager();
        
        Foo foo = metadata.create(Foo.class);
        foo.setName(row.get("name"));
        foo.setAddress(row.get("address"));

        em.persist(foo);
        tx.commit();
    }
}

See also Using JMX Beans cookbook section.

1 Like