How to create a h2 database from some fields of a CSV file

Question

i would like to create a new embedded h2 database from a CSV file. Here is the snippet of the csv file

Country,City,AccentCity,Region,Population,Latitude,Longitude

ad,aixovall,Aixovall,06,,42.4666667,1.4833333

ad,andorra,Andorra,07,,42.5,1.5166667

ad,andorra la vella,Andorra la Vella,07,20430,42.5,1.5166667

ad,andorra-vieille,Andorra-Vieille,07,,42.5,1.5166667

ad,andorre,Andorre,07,,42.5,1.5166667

I don't want to retrieve all the fields of the csv file. Actually, i want them all except the City and Region fields.

And further, i want to insert the content of the csv file into the database ONLY IF the content of POPULATION is not empty.

Thus, in the csv example above, we must only insert the 3rd row into the h2 table WORLDCITIES because its 'population' field is indicated.

Here is a snippet of code i wrote. But, as you can see, it is not enough yet:

conn = DriverManager.getConnection(connectionURL, connectionProps);
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE WORLDCITIES"
        + " AS SELECT COUNTRY, ACCENTCITY, POPULATION, LATITUDE, LONGITUDE"
        + " FROM CSVREAD('snippet.csv'));

And if i understand correctly, CSVREAD create the fields using the VARCHAR type, but i want the things like this:

COUNTRY VARCHAR(3), ACCENTCITY VARCHAR(40), POPULATION FLOAT, LATITUDE FLOAT, LONGITUDE FLOAT

Thanks in advance for helping.

Thomas Mueller · Accepted Answer · 2011-03-02 05:27:46Z

12

You can add the column definitions in the CREATE TABLE as documented and combine this with a WHERE clause. Please note using CREATE TABLE AS SELECT is a bit faster than separate CREATE TABLE and INSERT INTO statements (not sure if speed is very important for you):

CREATE TABLE WORLDCITIES(
  COUNTRY VARCHAR(3), 
  ACCENTCITY VARCHAR(40), 
  POPULATION FLOAT, 
  LATITUDE FLOAT, 
  LONGITUDE FLOAT)
AS SELECT 
  COUNTRY, 
  ACCENTCITY, 
  POPULATION, 
  LATITUDE, 
  LONGITUDE
FROM CSVREAD('snippet.csv') 
WHERE POPULATION IS NOT NULL;

answered Mar 2, 2011 at 5:27

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paissad Over a year ago

Thanks a lot, finally i did use used your solution !

paissad · Accepted Answer · 2011-03-02 05:02:20Z

finally, i proceeded like this as you adviced. I only put the parts of the code which i think is more related to the question :)

`

private final String createTableString = ""
    + "CREATE TABLE IF NOT EXISTS " + _tableName
    + " ("
    + "id INT UNSIGNED NOT NULL AUTO_INCREMENT, "
    + "country VARCHAR(3) NOT NULL, "
    + "city VARCHAR(40) NOT NULL, "
    + "region VARCHAR (5) NOT NULL, "
    + "population FLOAT NOT NULL, "
    + "latitude FLOAT NOT NULL, "
    + "longitude FLOAT NOT NULL, "
    + "PRIMARY KEY(id)"
    + " );";

private final String insertString = ""
    + "INSERT INTO " + _tableName
    + " (country, city, region, population, latitude, longitude) "
    + "VALUES (?,?,?,?,?,?)"
    + ";";

public void go() throws IOException, SQLException {

    loadDriver();
    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", "");
    connectionProps.put("password", "");
    String connectionURL = _protocol + _subprotocol + _dbName + _dbSettings;
    ResultSet rs = null;

    try {
        conn = DriverManager.getConnection(connectionURL, connectionProps);
        logger.info("Connected to {} database.", _dbName);

        conn.setAutoCommit(false);
        Savepoint savept1 = conn.setSavepoint();

        Statement stmt = conn.createStatement();
        try {
            stmt.execute(createTableString);
            logger.info("The table '{}' created successfully", _tableName);
        } catch (SQLException sqle) {
            logger.error("Error while creating the table '{}'", _tableName);
            printSQLException(sqle);
        }

        PreparedStatement pstmt = conn.prepareStatement(insertString);
        _allStatements.add(pstmt);
        /* rs:                           pstmt:
         * 1 -> COUNTRY                  
         * 2 -> CITY                     1 -> COUNTRY
         * 3 -> ACCENTCITY               2 -> CITY
         * 4 -> REGION                   3 -> REGION
         * 5 -> POPULATION               4 -> POPULATION
         * 6 -> LATITUDE                 5 -> LATITUDE
         * 7 -> LONGITUDE                6 -> LONGITUDE
         */
        rs = Csv.getInstance().read(_csvFileName, null, _csvCharset);
        int rowCount = 0;
        while (rs.next()) {
            if (rs.getFloat(5) != 0) { // If population is not null.
                pstmt.setString(1, rs.getString(1)); // country
                pstmt.setString(2, rs.getString(3)); // city (accentcity in reality)
                pstmt.setString(3, rs.getString(4)); // region
                pstmt.setFloat(4, rs.getFloat(5));   // population
                pstmt.setFloat(5, rs.getFloat(6));   // latitude
                pstmt.setFloat(6, rs.getFloat(7));   // longitude
                pstmt.addBatch();
                rowCount ++;
            }
        }
        int[] rowsUpdated = pstmt.executeBatch();
        for (int i=0; i<rowsUpdated.length; i++) {
            if (rowsUpdated[i] == -2) {
                logger.error("Execution {}: unknown number of rows inserted.", i);
                logger.error("Rolling back ...");
                conn.rollback(savept1);
            } else {
                logger.trace("Successful: execution {}, {} rows updated !", i, rowsUpdated[i]);
            }
        }
        conn.commit();
    }
    finally { // release all open resources to avoid unnecessary memory usage.

....`

Thanks!

@Thomas Mueller's example is more succinct. A similar approach can be used with INSERT, too.

trashgod · Accepted Answer · 2011-03-01 20:30:36Z

2

Use the read() method of the H2 class Csv, and iterate through the ResultSet, inserting the desired rows as you find them.

answered Mar 1, 2011 at 20:30

trashgod

206k33 gold badges264 silver badges1.1k bronze badges

1 Comment

trashgod Over a year ago

@Thomas Mueller: Thanks for commenting on this; your approach is better.

Collectives™ on Stack Overflow

How to create a h2 database from some fields of a CSV file

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related