Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save gordthompson/434c4985a261321ae4650733151653b9 to your computer and use it in GitHub Desktop.
Save gordthompson/434c4985a261321ae4650733151653b9 to your computer and use it in GitHub Desktop.
dynamically create "linking database" to have UCanAccess only load selected tables
/*
* 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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment