# SQLite



## niclasburger (28. Jan 2019)

Hallo liebe Mitglieder.
Ich Programmiere derzeit ein Programm, welches eine .csv Datei herunterlädt und dann in eine Datenbank importiert. Das Problem ist das die .csv Datei ca 5,5 Millionen Zeilen hat.

Das herunterladen und inserten klappt auch so weit, allerdings ist nach dem inserten immer meine SQLite Datenbank verschlüsselt. Schließe ich die Datenbank Verbindung so werden alle Daten in der Datenbank gelöscht.

Vielleicht könnt ihr mir ja Helfen. Denn ich weiß nicht mehr weiter.


```
package eliteDangerousPrice.csvHandler;

import static eliteDangerousPrice.utils.Constants.*;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;

import eliteDangerousPrice.functions.SystemLogger;
import eliteDangerousPrice.handler.DatabaseHandler;


public class csvReaderInsert
{
    SystemLogger systemLogger = SystemLogger.getInstance();

    DatabaseHandler databaseHandler = DatabaseHandler.getInstance();


    public void read(  )
    {
        Connection connection = databaseHandler.connect();


        PreparedStatement preparedStatement = null;
        try
        {
            preparedStatement = connection.prepareStatement( SQL );
            connection.setAutoCommit( false );
        }
        catch( SQLException e )
        {
            e.printStackTrace();
        }

        float count = 0;
        int batchSize = 25000;

        final long timeStart = System.currentTimeMillis();

        try
        {
            Reader reader = new FileReader( SAVE_PATH );
            Iterable<CSVRecord> records = CSVFormat.EXCEL.withFirstRecordAsHeader().parse( reader );
            for( CSVRecord record : records )
            {
                Long ID = Long.valueOf( record.get( "id" ) );
                Long station_ID = Long.valueOf( record.get( "station_id" ) );
                Long commodityID = Long.valueOf( record.get( "commodity_id" ) );
                Long supply = Long.valueOf( record.get( "supply" ) );
                Long buyPrice = Long.valueOf( record.get( "buy_price" ) );
                Long sellPrice = Long.valueOf( record.get( "sell_price" ) );
                Long demand = Long.valueOf( record.get( "demand" ) );
                Long collectedAt = Long.valueOf( record.get( "collected_at" ) );

                try
                {
                    preparedStatement.setLong( 1, ID );
                    preparedStatement.setLong( 2, station_ID );
                    preparedStatement.setLong( 3, commodityID );
                    preparedStatement.setLong( 4, supply );
                    preparedStatement.setLong( 5, buyPrice );
                    preparedStatement.setLong( 6, sellPrice );
                    preparedStatement.setLong( 7, demand );
                    preparedStatement.setLong( 8, collectedAt );
                    preparedStatement.addBatch();

                    count++;

                    if( count % batchSize == 0 )
                    {
                        systemLogger.info( "Commit the Batch!" + count );

                        preparedStatement.executeBatch();
                        preparedStatement.clearBatch();

                    }

                }
                catch( SQLException e )
                {
                    try
                    {
                        connection.rollback();
                        systemLogger.warning( "Rollback " + e.getMessage() );
                    }
                    catch( SQLException e1 )
                    {
                        e1.printStackTrace();
                        systemLogger.error( "Error: " + e.getMessage() );
                    }
                }
            }

            preparedStatement.executeBatch();

            systemLogger.info( "Inserted last Batch!" );
            systemLogger.info( "Inserted rows: " + count );



            reader.close();
            //connection.close(); Wenn ich das auskommentiere dann werden alle Daten gelöscht.
       

            final long timeEnd = System.currentTimeMillis();
            long time = ( timeEnd - timeStart ) / 1000;
            systemLogger.info( "Last " + time + " seconds to inserted all rows from csv file to database." );

        }
        catch( FileNotFoundException e )
        {
            systemLogger.error( e.getMessage() );
        }
        catch( IOException e )
        {
            systemLogger.error( e.getMessage() );
        }
        catch( NumberFormatException e )
        {
            systemLogger.error( e.getMessage() );
        }
        catch( SQLException e )
        {
            systemLogger.error( e.getMessage() );
        }
    }
}
```

Error Messages bekomme ich keine. Ein Rollback wird nicht gemacht. Laut meiner Logdatei wird alles richtig inserted.


```
-----------------------
Log start
Start time: 28/01/2019 17:03:23
------------------------

17:03:23 -INFO-    : Connection: org.sqlite.jdbc4.JDBC4Connection@545997b1
17:03:23 -INFO-    : The database driver name is: SQLite JDBC
17:03:23 -INFO-    : Database driver version: 3.25.2
17:03:23 -INFO-    : Database Path: jdbc:sqlite:./src/main/resources/database/ED_Database.db
17:03:23 -INFO-    : Table name: commodity_pricing
17:03:23 -INFO-    : Starting download from: https://eddb.io/archive/v6/listings.csv
17:03:23 -INFO-    : Download finished. Download last: 200 seconds.
17:03:23 -INFO-    : Successfully Droped Table: commodity_pricing Dropping last 0 seconds.
17:03:23 -INFO-    : Commit the Batch!25000.0
17:03:23 -INFO-    : Commit the Batch!50000.0
17:03:23 -INFO-    : Commit the Batch!75000.0
17:03:23 -INFO-    : Commit the Batch!100000.0
17:03:23 -INFO-    : Commit the Batch!125000.0
17:03:23 -INFO-    : Commit the Batch!150000.0
17:03:23 -INFO-    : Commit the Batch!175000.0
17:03:23 -INFO-    : Commit the Batch!200000.0
17:03:23 -INFO-    : Commit the Batch!225000.0
17:03:23 -INFO-    : Commit the Batch!250000.0
17:03:23 -INFO-    : Commit the Batch!275000.0
17:03:23 -INFO-    : Commit the Batch!300000.0
17:03:23 -INFO-    : Commit the Batch!325000.0
17:03:23 -INFO-    : Commit the Batch!350000.0
17:03:23 -INFO-    : Commit the Batch!375000.0
17:03:23 -INFO-    : Commit the Batch!400000.0
17:03:23 -INFO-    : Commit the Batch!425000.0
17:03:23 -INFO-    : Commit the Batch!450000.0
17:03:23 -INFO-    : Commit the Batch!475000.0
17:03:23 -INFO-    : Commit the Batch!500000.0
17:03:23 -INFO-    : Commit the Batch!525000.0
17:03:23 -INFO-    : Commit the Batch!550000.0
17:03:23 -INFO-    : Commit the Batch!575000.0
17:03:23 -INFO-    : Commit the Batch!600000.0
17:03:23 -INFO-    : Commit the Batch!625000.0
17:03:23 -INFO-    : Commit the Batch!650000.0
17:03:23 -INFO-    : Commit the Batch!675000.0
17:03:23 -INFO-    : Commit the Batch!700000.0
17:03:23 -INFO-    : Commit the Batch!725000.0
17:03:23 -INFO-    : Commit the Batch!750000.0
17:03:23 -INFO-    : Commit the Batch!775000.0
17:03:23 -INFO-    : Commit the Batch!800000.0
17:03:23 -INFO-    : Commit the Batch!825000.0
17:03:23 -INFO-    : Commit the Batch!850000.0
17:03:23 -INFO-    : Commit the Batch!875000.0
17:03:23 -INFO-    : Commit the Batch!900000.0
17:03:23 -INFO-    : Commit the Batch!925000.0
17:03:23 -INFO-    : Commit the Batch!950000.0
17:03:23 -INFO-    : Commit the Batch!975000.0
17:03:23 -INFO-    : Commit the Batch!1000000.0
17:03:23 -INFO-    : Commit the Batch!1025000.0
17:03:23 -INFO-    : Commit the Batch!1050000.0
17:03:23 -INFO-    : Commit the Batch!1075000.0
17:03:23 -INFO-    : Commit the Batch!1100000.0
17:03:23 -INFO-    : Commit the Batch!1125000.0
17:03:23 -INFO-    : Commit the Batch!1150000.0
17:03:23 -INFO-    : Commit the Batch!1175000.0
17:03:23 -INFO-    : Commit the Batch!1200000.0
17:03:23 -INFO-    : Commit the Batch!1225000.0
17:03:23 -INFO-    : Commit the Batch!1250000.0
17:03:23 -INFO-    : Commit the Batch!1275000.0
17:03:23 -INFO-    : Commit the Batch!1300000.0
17:03:23 -INFO-    : Commit the Batch!1325000.0
17:03:23 -INFO-    : Commit the Batch!1350000.0
17:03:23 -INFO-    : Commit the Batch!1375000.0
17:03:23 -INFO-    : Commit the Batch!1400000.0
17:03:23 -INFO-    : Commit the Batch!1425000.0
17:03:23 -INFO-    : Commit the Batch!1450000.0
17:03:23 -INFO-    : Commit the Batch!1475000.0
17:03:23 -INFO-    : Commit the Batch!1500000.0
17:03:23 -INFO-    : Commit the Batch!1525000.0
17:03:23 -INFO-    : Commit the Batch!1550000.0
17:03:23 -INFO-    : Commit the Batch!1575000.0
17:03:23 -INFO-    : Commit the Batch!1600000.0
17:03:23 -INFO-    : Commit the Batch!1625000.0
17:03:23 -INFO-    : Commit the Batch!1650000.0
17:03:23 -INFO-    : Commit the Batch!1675000.0
17:03:23 -INFO-    : Commit the Batch!1700000.0
17:03:23 -INFO-    : Commit the Batch!1725000.0
17:03:23 -INFO-    : Commit the Batch!1750000.0
17:03:23 -INFO-    : Commit the Batch!1775000.0
17:03:23 -INFO-    : Commit the Batch!1800000.0
17:03:23 -INFO-    : Commit the Batch!1825000.0
17:03:23 -INFO-    : Commit the Batch!1850000.0
17:03:23 -INFO-    : Commit the Batch!1875000.0
17:03:23 -INFO-    : Commit the Batch!1900000.0
17:03:23 -INFO-    : Commit the Batch!1925000.0
17:03:23 -INFO-    : Commit the Batch!1950000.0
17:03:23 -INFO-    : Commit the Batch!1975000.0
17:03:23 -INFO-    : Commit the Batch!2000000.0
17:03:23 -INFO-    : Commit the Batch!2025000.0
17:03:23 -INFO-    : Commit the Batch!2050000.0
17:03:23 -INFO-    : Commit the Batch!2075000.0
17:03:23 -INFO-    : Commit the Batch!2100000.0
17:03:23 -INFO-    : Commit the Batch!2125000.0
17:03:23 -INFO-    : Commit the Batch!2150000.0
17:03:23 -INFO-    : Commit the Batch!2175000.0
17:03:23 -INFO-    : Commit the Batch!2200000.0
17:03:23 -INFO-    : Commit the Batch!2225000.0
17:03:23 -INFO-    : Commit the Batch!2250000.0
17:03:23 -INFO-    : Commit the Batch!2275000.0
17:03:23 -INFO-    : Commit the Batch!2300000.0
17:03:23 -INFO-    : Commit the Batch!2325000.0
17:03:23 -INFO-    : Commit the Batch!2350000.0
17:03:23 -INFO-    : Commit the Batch!2375000.0
17:03:23 -INFO-    : Commit the Batch!2400000.0
17:03:23 -INFO-    : Commit the Batch!2425000.0
17:03:23 -INFO-    : Commit the Batch!2450000.0
17:03:23 -INFO-    : Commit the Batch!2475000.0
17:03:23 -INFO-    : Commit the Batch!2500000.0
17:03:23 -INFO-    : Commit the Batch!2525000.0
17:03:23 -INFO-    : Commit the Batch!2550000.0
17:03:23 -INFO-    : Commit the Batch!2575000.0
17:03:23 -INFO-    : Commit the Batch!2600000.0
17:03:23 -INFO-    : Commit the Batch!2625000.0
17:03:23 -INFO-    : Commit the Batch!2650000.0
17:03:23 -INFO-    : Commit the Batch!2675000.0
17:03:23 -INFO-    : Commit the Batch!2700000.0
17:03:23 -INFO-    : Commit the Batch!2725000.0
17:03:23 -INFO-    : Commit the Batch!2750000.0
17:03:23 -INFO-    : Commit the Batch!2775000.0
17:03:23 -INFO-    : Commit the Batch!2800000.0
17:03:23 -INFO-    : Commit the Batch!2825000.0
17:03:23 -INFO-    : Commit the Batch!2850000.0
17:03:23 -INFO-    : Commit the Batch!2875000.0
17:03:23 -INFO-    : Commit the Batch!2900000.0
17:03:23 -INFO-    : Commit the Batch!2925000.0
17:03:23 -INFO-    : Commit the Batch!2950000.0
17:03:23 -INFO-    : Commit the Batch!2975000.0
17:03:23 -INFO-    : Commit the Batch!3000000.0
17:03:23 -INFO-    : Commit the Batch!3025000.0
17:03:23 -INFO-    : Commit the Batch!3050000.0
17:03:23 -INFO-    : Commit the Batch!3075000.0
17:03:23 -INFO-    : Commit the Batch!3100000.0
17:03:23 -INFO-    : Commit the Batch!3125000.0
17:03:23 -INFO-    : Commit the Batch!3150000.0
17:03:23 -INFO-    : Commit the Batch!3175000.0
17:03:23 -INFO-    : Commit the Batch!3200000.0
17:03:23 -INFO-    : Commit the Batch!3225000.0
17:03:23 -INFO-    : Commit the Batch!3250000.0
17:03:23 -INFO-    : Commit the Batch!3275000.0
17:03:23 -INFO-    : Commit the Batch!3300000.0
17:03:23 -INFO-    : Commit the Batch!3325000.0
17:03:23 -INFO-    : Commit the Batch!3350000.0
17:03:23 -INFO-    : Commit the Batch!3375000.0
17:03:23 -INFO-    : Commit the Batch!3400000.0
17:03:23 -INFO-    : Commit the Batch!3425000.0
17:03:23 -INFO-    : Commit the Batch!3450000.0
17:03:23 -INFO-    : Commit the Batch!3475000.0
17:03:23 -INFO-    : Commit the Batch!3500000.0
17:03:23 -INFO-    : Commit the Batch!3525000.0
17:03:23 -INFO-    : Commit the Batch!3550000.0
17:03:23 -INFO-    : Commit the Batch!3575000.0
17:03:23 -INFO-    : Commit the Batch!3600000.0
17:03:23 -INFO-    : Commit the Batch!3625000.0
17:03:23 -INFO-    : Commit the Batch!3650000.0
17:03:23 -INFO-    : Commit the Batch!3675000.0
17:03:23 -INFO-    : Commit the Batch!3700000.0
17:03:23 -INFO-    : Commit the Batch!3725000.0
17:03:23 -INFO-    : Commit the Batch!3750000.0
17:03:23 -INFO-    : Commit the Batch!3775000.0
17:03:23 -INFO-    : Commit the Batch!3800000.0
17:03:23 -INFO-    : Commit the Batch!3825000.0
17:03:23 -INFO-    : Commit the Batch!3850000.0
17:03:23 -INFO-    : Commit the Batch!3875000.0
17:03:23 -INFO-    : Commit the Batch!3900000.0
17:03:23 -INFO-    : Commit the Batch!3925000.0
17:03:23 -INFO-    : Commit the Batch!3950000.0
17:03:23 -INFO-    : Commit the Batch!3975000.0
17:03:23 -INFO-    : Commit the Batch!4000000.0
17:03:23 -INFO-    : Commit the Batch!4025000.0
17:03:23 -INFO-    : Commit the Batch!4050000.0
17:03:23 -INFO-    : Commit the Batch!4075000.0
17:03:23 -INFO-    : Commit the Batch!4100000.0
17:03:23 -INFO-    : Commit the Batch!4125000.0
17:03:23 -INFO-    : Commit the Batch!4150000.0
17:03:23 -INFO-    : Commit the Batch!4175000.0
17:03:23 -INFO-    : Commit the Batch!4200000.0
17:03:23 -INFO-    : Commit the Batch!4225000.0
17:03:23 -INFO-    : Commit the Batch!4250000.0
17:03:23 -INFO-    : Commit the Batch!4275000.0
17:03:23 -INFO-    : Commit the Batch!4300000.0
17:03:23 -INFO-    : Commit the Batch!4325000.0
17:03:23 -INFO-    : Commit the Batch!4350000.0
17:03:23 -INFO-    : Commit the Batch!4375000.0
17:03:23 -INFO-    : Commit the Batch!4400000.0
17:03:23 -INFO-    : Commit the Batch!4425000.0
17:03:23 -INFO-    : Commit the Batch!4450000.0
17:03:23 -INFO-    : Commit the Batch!4475000.0
17:03:23 -INFO-    : Commit the Batch!4500000.0
17:03:23 -INFO-    : Commit the Batch!4525000.0
17:03:23 -INFO-    : Commit the Batch!4550000.0
17:03:23 -INFO-    : Commit the Batch!4575000.0
17:03:23 -INFO-    : Commit the Batch!4600000.0
17:03:23 -INFO-    : Commit the Batch!4625000.0
17:03:23 -INFO-    : Commit the Batch!4650000.0
17:03:23 -INFO-    : Commit the Batch!4675000.0
17:03:23 -INFO-    : Commit the Batch!4700000.0
17:03:23 -INFO-    : Commit the Batch!4725000.0
17:03:23 -INFO-    : Commit the Batch!4750000.0
17:03:23 -INFO-    : Commit the Batch!4775000.0
17:03:23 -INFO-    : Commit the Batch!4800000.0
17:03:23 -INFO-    : Commit the Batch!4825000.0
17:03:23 -INFO-    : Commit the Batch!4850000.0
17:03:23 -INFO-    : Commit the Batch!4875000.0
17:03:23 -INFO-    : Commit the Batch!4900000.0
17:03:23 -INFO-    : Commit the Batch!4925000.0
17:03:23 -INFO-    : Commit the Batch!4950000.0
17:03:23 -INFO-    : Commit the Batch!4975000.0
17:03:23 -INFO-    : Commit the Batch!5000000.0
17:03:23 -INFO-    : Commit the Batch!5025000.0
17:03:23 -INFO-    : Commit the Batch!5050000.0
17:03:23 -INFO-    : Commit the Batch!5075000.0
17:03:23 -INFO-    : Commit the Batch!5100000.0
17:03:23 -INFO-    : Commit the Batch!5125000.0
17:03:23 -INFO-    : Commit the Batch!5150000.0
17:03:23 -INFO-    : Commit the Batch!5175000.0
17:03:23 -INFO-    : Commit the Batch!5200000.0
17:03:23 -INFO-    : Commit the Batch!5225000.0
17:03:23 -INFO-    : Commit the Batch!5250000.0
17:03:23 -INFO-    : Commit the Batch!5275000.0
17:03:23 -INFO-    : Commit the Batch!5300000.0
17:03:23 -INFO-    : Commit the Batch!5325000.0
17:03:23 -INFO-    : Commit the Batch!5350000.0
17:03:23 -INFO-    : Commit the Batch!5375000.0
17:03:23 -INFO-    : Commit the Batch!5400000.0
17:03:23 -INFO-    : Commit the Batch!5425000.0
17:03:23 -INFO-    : Commit the Batch!5450000.0
17:03:23 -INFO-    : Commit the Batch!5475000.0
17:03:23 -INFO-    : Commit the Batch!5500000.0
17:03:23 -INFO-    : Commit the Batch!5525000.0
17:03:23 -INFO-    : Inserted last Batch!
17:03:23 -INFO-    : Inserted rows: 5546488.0
17:03:23 -INFO-    : Last 31 seconds to inserted all rows from csv file to database.
17:03:23 -INFO-    : Successfully deleted csv file. Path: C:/Users/nibu/Desktop/Me/Projects/EliteDangerous/EliteDangerousPrices/src/main/resources/downloads/price.csv
```


Ich hoffe auf eure Hilfe.
Wenn ihr alle Dateien benötigt ist das kein Problem.
Sorry für den nicht so aussagekräftigen Titel.

Angehängt ist ein Bild auf dem man das Fenster sieht welches kommt wenn man versucht die Datenbank zu öffnen.
	

	
	
		
		

		
			




https://www.bilder-upload.eu/bild-71446a-1548693409.png.html

Grüße

Niclas


----------



## fhoffmann (28. Jan 2019)

Du hast

```
connection.setAutoCommit( false );
```
Deshalb musst du am Ende schreiben

```
connection.commit();
```


----------



## niclasburger (29. Jan 2019)

Hey, 

vielen vielen Dank für deine Antwort. 
Das hat geholfen

Niclas


----------

