dynamically create "linking database" to have UCanAccess only load selected tables

ComExampleUcanaccessDynamicLinkMain.java

/*
 * Copyright 2017 Gordon D. Thompson
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.example.ucanaccess.dynamiclink;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import com.healthmarketscience.jackcess.CursorBuilder;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.IndexCursor;
import com.healthmarketscience.jackcess.Table;

/**
 * Loading only selected tables in UCanAccess
 * <p>
 * Example code showing how to use Jackcess calls to dynamically create 
 * a temporary "linking database" so UCanAccess will only load the specified 
 * tables, instead of loading every table in the database file.
 * <p>
 * <b>IMPORTANT:</b>
 * <p>
 * This code creates a new <i>temporary</i> database file and inserts just 
 * enough information into the {@code [MSysObjects]} table so UCanAccess can 
 * find and load the table in the "real" database.
 * <p>
 * <b><u>DO NOT</u></b> use this approach to try and create or alter table links 
 * in an existing Access database!
 * 
 * @version 1.1
 * @author Gord Thompson
 *
 */
public class ComExampleUcanaccessDynamicLinkMain {

	public static void main(String[] args) {
		// path to the real Access database that contains the tables
		String actualDbFilePath = "C:/Users/Public/MillionRows.accdb";
		
		File tempLinker = null;
		try {
			tempLinker = File.createTempFile("ucaTempLinker", ".tmp");
		} catch (IOException e) {
			e.printStackTrace(System.err);
			System.exit(1);
		}
		try (Database db = DatabaseBuilder.create(Database.FileFormat.V2010, tempLinker)) {
			Table mso = db.getSystemTable("MSysObjects");
			IndexCursor crsr = CursorBuilder.createCursor(mso.getPrimaryKeyIndex());
			crsr.findClosestRowByEntry(0);
			// get [ParentId] value from existing row
			int msoParentId = (int) crsr.getCurrentRowValue(mso.getColumn("ParentId"));
			// and get starting value for new [Id]s: highest available negative value
			crsr.moveToPreviousRow();
			int newMsoId = 1 + (int) crsr.getCurrentRowValue(mso.getColumn("Id"));
			
			Map<String, Object> newRow = new HashMap<>();
			//
			// first add the common "magic" values
			newRow.put("ParentId", msoParentId);
			newRow.put("Type", (short) 6);
			newRow.put("Flags", 2097152);
			//
			// now add values for a table we want to manipulate
			newRow.put("Id", newMsoId++);
			newRow.put("Database", actualDbFilePath);
			newRow.put("ForeignName", "Customer");  // table name in the real database
			newRow.put("Name", "cust");  // our local alias (can be different)
			mso.addRowFromMap(newRow);
			//
			// (optional) add another link for a second table
			newRow.put("Id", newMsoId++);
			newRow.put("Database", actualDbFilePath);
			newRow.put("ForeignName", "OrderHistory");  // table name in the real database
			newRow.put("Name", "ordhist");  // our local alias (can be different)
			mso.addRowFromMap(newRow);
		} catch (IOException e) {
			e.printStackTrace(System.err);
			System.exit(2);
		}
		
		String connectionUrl = "jdbc:ucanaccess://" + tempLinker.getAbsolutePath();
		// this will let us delete the tempLinker file later:
		connectionUrl += ";immediatelyReleaseResources=true";
		try (Connection conn = DriverManager.getConnection(connectionUrl)) {
			// to check the results, let's list the tables that UCanAccess loaded ...
			System.out.println("UCanAccess loaded the following tables:");
			try (ResultSet rsMD = conn.getMetaData().getTables(null, null, null, null)) {
				while (rsMD.next()) {
					String tblName = rsMD.getString("TABLE_NAME");
					System.out.printf("[%s]%n", tblName);
					// ... and list the columns in each of those tables
					try (
							Statement st = conn.createStatement();
							ResultSet rsTbl = st.executeQuery("SELECT * FROM [" + tblName + "] WHERE 0=1")) {
						ResultSetMetaData rsmd = rsTbl.getMetaData();
						for (int i = 1; i <= rsmd.getColumnCount(); i++) {
							System.out.printf("    [%s]%n", rsmd.getColumnLabel(i));
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace(System.err);
			System.exit(3);
		}
		
		tempLinker.delete();  // ... if possible
	}

}