import data from an external file(csv) into embedded sqlite database

Question

I am new to SQLite and I want to import data from an external file like csv into this embedded database rather than adding the data manually. For example in this case i added John McNeil and Paul Smith manually..is there a way to not add in manually by reading from a csv file with columns filled with first name and last name.

 public class SQLiteTest {
 private static Connection con;
 private static boolean hasData = false;
 
 private void getConnection() throws ClassNotFoundException, SQLException {
      // sqlite driver
      Class.forName("org.sqlite.JDBC");
      // database path, if it's new database, it will be created in the project folder
      con = DriverManager.getConnection("jdbc:sqlite:SQLiteTest1.db");
      initialise();
 }
 

public void addUser(String firstname, String lastname) throws ClassNotFoundException, SQLException {
     if(con == null) {
         // get connection
         getConnection();
     }
      PreparedStatement prep = con
              .prepareStatement("insert into user values(?,?,?);");
              prep.setString(2, firstname);
              prep.setString(3, lastname);
              prep.execute();
     
 }
 
 public ResultSet displayUsers() throws SQLException, ClassNotFoundException {
     if(con == null) {
         // get connection
         getConnection();
     }
     Statement state = con.createStatement();
     ResultSet res = state.executeQuery("select fname, lname from user");
     return res;
 }
 
 private void initialise() throws SQLException {
     if( !hasData ) {
         hasData = true;
         // check for database table
         Statement state = con.createStatement();
         ResultSet res = state.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='user'");
         if( !res.next()) {
             System.out.println("Building the User table with prepopulated values.");
             // need to build the table
              Statement state2 = con.createStatement();
              state2.executeUpdate("create table user(id integer,"
                + "fName varchar(60)," + "lname varchar(60)," + "primary key (id));");

              // inserting some sample data
              PreparedStatement prep = con.prepareStatement("insert into user values(?,?,?);");
              prep.setString(2, "John");
              prep.setString(3, "McNeil");
             
              prep.execute();
              
              PreparedStatement prep2 = con.prepareStatement("insert into user values(?,?,?);");
              prep2.setString(2, "Paul");
              prep2.setString(3, "Smith");
              prep2.execute();
         }
         
     }
 }

}

score 0 · Answer 1 · answered Jul 28 '20 at 12:02

In enterprise applications, they use database migration tools like liquibase to do that. It's well integrated with spring boot using auto configuration. but if you are not using spring you can configure it yourself. You will have a XML file like that.

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
         xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
         xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd
                    http://www.liquibase.org/xml/ns/dbchangelog-ext 
          http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<changeSet id="11111" author="me">
    <loadData
              file="data/users.csv"
              separator=";"
              tableName="user"/>

</changeSet>
</databaseChangeLog>

and you can have your data written in CSV file like that

id;login;first_name;last_name;email
1;John;John;John;John@localhost

Sorry I dont quite understand this method. Is there a way that i can edit my code into reading from csv file? — Zeng Xi, Jul 28 '20 at 12:18
you can check this https://www.baeldung.com/java-csv-file-array if you want to read a CSV file, but liquibase is a more generic way to solve this problem you can check this answer about how to integrate it in your code https://stackoverflow.com/questions/10620131/running-liquibase-within-java-code — Hesham Osman, Jul 28 '20 at 20:06

import data from an external file(csv) into embedded sqlite database

1 Answers1