How can I create a persistent Database with DuckDB JDBC

[david99s]

Hello,
i am very new to DuckDB or programming with databases in connection to Java (JDBC) in general. So for a university project i have to run a few queries against TPC-H data. So far, i downloaded the tables from TPC-H and established a connection via JDBC. I dont know how to enter directly the database( TPC-H tables are .tbl files) so i just connected with
Connection conn = DriverManager.getConnection("jdbc:duckdb:");
Then i created all the tables with sql statements and copied the data from .tbl-files. This is actually really annoying since i have to repeat this every time i want to execute a query. I came across the documentation about import&export databases from duckdb https://duckdb.org/docs/sql/statements/export, but when i want reload my folder/database it doesnt work.

So I am looking for a possibility to create a database with the .tbl-files from the TPC-H data which i can past in the connection path or any other way to get a persistent database.

Sorry I am sure this is a very beginner problem but any help is much appreciated!

Thank you!
David

[Mytherin]

By default DuckDB is opened in in-memory mode. To open a persistent database you can provide the path to the database file after the colon, e.g.:

Connection conn = DriverManager.getConnection("jdbc:duckdb:/tmp/tpch.db");

[david99s]

Thank you for your reply! I did know that, but how can I create a database with only .tbl-files or should it be sufficient to provide the path to the dictionary in which these files are?

[Mytherin]

After opening a persistent database you can create tables and insert data using standard SQL statements. Any tables that are created are then preserved and stored inside the database file. When you re-open the database they will still be there.

For example, you could do e.g. CREATE TABLE lineitem AS SELECT * FROM read_csv_auto('lineitem.tbl'). The created "lineitem" table is then preserved and loaded again when you reload the tpch.db file without needing to run the CREATE TABLE statement again.

[david99s]

Connection conn = DriverManager.getConnection("jdbc:duckdb:/tmp/tpch.db");
It says path not found and when i create a db-file by my own in Explorer and link this path it says Acces denied

[Mytherin]

You need to replace /tmp/tpch.db with a path that exists on your computer. You can also use a relative path and use only tpch.db.

[david99s]

Okay thank you so much. My fault was that when i was entering the path i always kept the "/C:" from copying the path to the directory i want in the String which was the problem...
Thank you again you helped me a lot!!
Have a nice evening!