Chiggiddi Chiggiddi - 3 months ago 33
Java Question

Howto IMPORT Java objects with relationship from .csv file into MySQL database?

let say we have 2 entities:


  1. Person entity - has 2 attributes person_id (manual input) and firstname

  2. Address entity - has 2 attributes address_id (auto-increment) and streetname



Each Person has one Address (OneToOne relationship). So Address entity will have a foreign key (person_id) pointing to its associated Person
What will you suggest howto go about this?

Until now I have only found a way to import single entities into mysql without relationship management:

LOAD DATA INFILE 'c:/tmp/file.csv'
INTO TABLE person
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Answer

OK, unfortunately this is going to be a tough answer, but likely it's the only way without modifying your table for an insert (Which is highly not recommended).

  1. Create a temporary table
    CREATE TEMPORARY TABLE `import` (
        `person_name` varchar(300) CHARACTER SET latin1 NOT NULL,
        `person_age` varchar(300) CHARACTER SET latin1 NOT NULL,
        `address1` varchar(300) CHARACTER SET latin1 NOT NULL,
        `city` varchar(300) CHARACTER SET latin1 NOT NULL
    ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  1. Do bulk load (As you showed in your question)

  2. Do a select from your import table, and loop through each row

  3. Insert into address table, get last_insert_id, and then insert into user table with the last insert id

  4. Drop your temp table with: DROP TEMPORARY TABLE `import`;

If you consider dropping the auto incrementing id, and move toward a UUID() instead, you can do this with 3 queries.

  1. Create a temporary table
    CREATE TEMPORARY TABLE `import` (
        `uuid` char(36) CHARACTER SET latin1 NOT NULL,
        `person_name` varchar(300) CHARACTER SET latin1 NOT NULL,
        `person_age` varchar(300) CHARACTER SET latin1 NOT NULL,
        `address1` varchar(300) CHARACTER SET latin1 NOT NULL,
        `city` varchar(300) CHARACTER SET latin1 NOT NULL
    ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  1. Do bulk load:
    LOAD DATA INFILE 'c:/tmp/file.csv' 
    INTO TABLE person 
    (@dummy, person_name, person_age, address1, city)
    SET uuid = UUID()
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS;
  1. Do your inserts:
    INSERT INTO address (id, address1, city)
    SELECT uuid, address1, city
    FROM `import`;

    INSERT INTO person (address_id, person_name, person_age)
    SELECT uuid, person_name, person_age
    FROM `import`;
  1. Drop your temp table with: DROP TEMPORARY TABLE import;
Comments