Doc issue? What is the proper way of using JDBC PreparedStatement with STRUCT and/or MAP? #208

Activity

1. staticlibs commented on May 2, 2025

   

   staticlibs

   on May 2, 2025

   Member

   More actions

   @wernerdaehn

   Thanks for the report! Yes, I agree that documentation about createMap() (and other DuckDB-specific extensions like createAppender()) is lacking. The intention about such extensions is to cover them with JavaDoc and publish it to be used in addition to generic JDBC JavaDoc, but this is not yet done.

   Full example with MAP insert/fetch just if someone stumble upon the same topic:

   try (DuckDBConnection conn = DriverManager.getConnection("jdbc:duckdb:").unwrap(DuckDBConnection.class)) {
       try (Statement stmt = conn.createStatement()) {
           stmt.execute("CREATE TABLE tab1 (col1 int, col2 MAP(INT, VARCHAR))");
       }
   
       try (PreparedStatement ps = conn.prepareStatement("INSERT INTO tab1 VALUES (?, ?)")) {
           Map<Integer, String> map = new HashMap<Integer, String>();
           map.put(41, "foo");
           map.put(42, "bar");
   
           Map<Integer, String> mapParam = conn.createMap("MAP(INT, VARCHAR)", map);
           ps.setObject(1, 1);
           ps.setObject(2, mapParam);
           ps.execute();
       }
   
       try (Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM tab1")) {
           while (rs.next()) {
               System.out.println(rs.getInt(1));
               Map<Integer, String> mapFetched = (Map) rs.getObject(2);
               System.out.println(mapFetched);
           }
       }
   }

   It is easier with createStruct and createArrayOf because these ones are generic JDBC methods. Example of their usage can be seen in spatial extension tests.

   PS: Appender improvements are planned, it is the next major task in JDBC, but it is not making it into upcoming 1.3 release.

   👍React with 👍1Reacted by wernerdaehn
2. staticlibs commented on Sep 25, 2025

   

   staticlibs

   on Sep 25, 2025

   Member

   More actions

   Just for the record, with #399 Appender now supports MAP and nested STRUCTs.