Forem: Matthew J. Clemente

Notes on Migrating from MS SQL Server to PostgreSQL

Matthew J. Clemente — Tue, 04 Feb 2020 00:00:00 +0000

I'm not a database guru or SQL sherpa, but from time to time I do fill the role of de facto DBA. The following are some notes and observations from a recent, successful, migration from Microsoft SQL Server to PostgreSQL. Is it everything you need to know? Absolutely not. But there are some points and resources that will hopefully be helpful.

A Note on Versions
Moving the Database
Updating Application Code for PostgreSQL
- Lowercase table and column names
- Switch from TOP to LIMIT
- Use RETURNING with INSERT to retrieve identity
- Use CONCAT instead of +
- Accounting for the Boolean data type
- Different reserved keywords
- Formatting dates and times:
- Switch from STUFF... FOR XML PATH('') to string_agg
Final Notes

A Note on Versions

Many of the articles that I came across comparing SQL Server and Postgres, aside from those that were useless from a practical standpoint, were outdated. That is, comparing PG 9.3 with SQL Server 2014 just isn't going to paint an accurate picture of where they stand today (February 2020). So, for context, this was a migration from a dedicated instance of MS SQL Server 2016, as well as cloud-based Azure SQL Databases to DigitalOcean's managed PostgreSQL hosting, running PostgreSQL v11.

Moving the Database

I thought this would be the hard part, but the actual migration of the data from one database engine to the other turned out to be rather straightforward, thanks to the aptly named tool: MS SQL to PostgreSQL. The data transfers was fast, it retained foreign keys and indexes, and allowed a high degree of control over the operation and handling of tables and columns. A few dry runs to testing servers were necessary to work out the exact settings and process, but we were very happy with the results.¹ The more labor intensive part of changing database engines was updating our application code to be PostgreSQL compatible.

Updating Application Code for PostgreSQL

While not comprehensive, here are some of the differences between SQL Server and Postgres that we needed to account for in our applications.² In no particular order:

Lowercase table and column names

PostgreSQL handles the casing of identifiers differently than SQL Server, which is to say, case matters. There are numerous discussions about this online, so I won't repeat them. The approach we took was to lowercase all identifers. At the database level, this was done by the data transfer tool mentioned earlier, but within the application, we had to do it manually. No magic tool here. We worked our way through the code base, lowercasing as we went. Visual Studio Code's shortcut Command-K-L (⌘+K+L) came in very handy.

Switch from TOP to LIMIT

Postgres, like MySQL, applies limits to the result set at the end of the SQL statement using LIMIT, while SQL Server does this at the outset, using TOP. So we had to go through updating queries from SELECT TOP 1 to SELECT ... LIMIT 1. It wasn't hard; it just takes a while.

Use RETURNING with INSERT to retrieve identity

When an INSERT statement generates an identity value, applications frequently have need to retrieve and use that generated identifier. For example, if you insert a new client, you probably want to know the client's ID. MS SQL Server provides SCOPE_IDENTITY() for this purpose, and some programming languages will return the generated identifier value automatically following an INSERT query. With PostgreSQL, you need to modify your INSERT statement, adding a line that specifies the column value you need returned. Here's how that looks:

INSERT INTO client(firstname, lastname)
VALUES ('Testy', 'McTester')
RETURNING id;
-- returns the value generated for this record in the id column

The reason for this has to do with Postgres apparently not having a concept of a table's "identity". You can read more about it here on Stack Overflow.

Use `CONCAT` instead of `+`

Postgres does not allow the use of the + operator for string concatenation, so queries composed like the example here will fail:

SELECT firstname + ' ' + lastname AS fullname

This syntax will need to be rewritten, using CONCAT, as shown here:

SELECT CONCAT(firstname, ' ', lastname) AS fullname

While putting this together, I learned that CONCAT is supported in SQL Server - I just hadn't ever used it. While a bit strange at first, I prefer the CONCAT syntax now; I find the resulting code to be cleaner - easier to read, write, and modify.

Accounting for the Boolean data type

In SQL Server, the bit data type is typically used as a stand in for boolean values. Postgres provides a true boolean data type, unsurprisingly named boolean. However, because they are fundementally different data types (though meant to convey the same thing), they behave in subtly different ways.

For example, the bit data type can be compared to integers, as well as boolean string values, as seen in these SQL Server examples:

-- SQL Server
-- bit to integer comparison (valid)
WHERE status = 1

-- bit to boolean string comparison (valid)
WHERE status = 'true'

However, SQL Server does not allow columns of the bit data type to be actually used as booleans:

-- SQL Server
-- bit to boolean comparison (not valid)
WHERE status = TRUE

-- bit as boolean implying truth (not valid)
WHERE status

On the other hand, the PostgreSQL boolean data type cannot be compared with integers; it results in the following error: ERROR: operator does not exist: boolean = integer (which we saw a lot of) - that is, the following is invalid in Postgres:

-- Postgres
-- boolean to integer comparison (not valid)
WHERE status = 1

However, as you would expect, the boolean data type in Postgres does function as a true boolean, so the following examples are valid in Postgres:

-- Postgres
-- boolean to boolean comparison (valid)
WHERE status = TRUE

-- boolean auto cast to boolean string (valid)
WHERE status = 'TRUE'
WHERE status = 'T'

-- boolean implying true/false (valid)
WHERE status
WHERE NOT status

All of which is a long-winded way to say that there were instances in our codebase where we were comparing boolean columns to 1 or 0, and we needed to update them to use the actual booleans TRUE or FALSE.

Different reserved keywords

I don't have a comprehensive list of the different reserved keywords - just a note that this difference can cause issues. In our case, we had a column named offset, which was acceptable in SQL Server, but which we needed to quote in order to use in Postgres, as the keyword is reserved. For reference, here's the list of SQL Server reserved keywords, and here are the Postgres SQL keywords.

Formatting dates and times:

This is a minor difference, but even minor differences need to be changed. When formatting dates, as in a SELECT statement, the function and syntax used are different. While SQL Server used FORMAT, Postgres uses to_char, as in this example:

-- SQL Server 
SELECT 
FORMAT(lastmodified,'M/d/yy h:mm tt') AS readabledatetime

-- Postgres
SELECT 
to_char(lastmodified,'FMMM/FMDD/YY HH12:MI AM') AS readabledatetime

-- Example Output: 8/1/19 8:21 PM

Here are the Postgres docs on formatting, which you'll need to review if this type of change impacts you. I was impressed with how configurable the formatting options were - for instance, note that in the example above, the FM prefix is used to supress leading 0s.

Switch from `STUFF... FOR XML PATH('')` to `string_agg`

If you've never needed to use SQL Server's STUFF with FOR XML PATH('') to concatenate the results of a subquery as a list... then count yourself lucky and just skip this section. If you have, then you know that the syntax is not straightforward and can be difficult to parse.³ Here's an example of what I'm talking about (along with a SQL fiddle if you want to follow along):

SELECT
  a.author,
  STUFF((SELECT ', ' + title
  FROM author_title
  WHERE authorid = a.authorid
  FOR xml PATH ('')), 1, 2, ''
  ) AS works
FROM author a

Thankfully, PostgreSQL uses a more intuitive function, string_agg to provide this functionality, so here's how you would rewrite the above example:

SELECT
  a.author,
  (SELECT string_agg(title, ', ')
  FROM author_title
  WHERE authorid = a.authorid) AS works
FROM author a

Additionally, the string_agg function actually allows you to dispense with the subquery and use a JOIN/GROUP BY instead, so you could also rewrite the example as:

SELECT
  a.author,
  string_agg(at.title, ', ') AS works
FROM author a
LEFT OUTER JOIN author_title at
  ON a.authorid = at.authorid
GROUP BY a.author

While database incompatibilities are always work to resolve, cases like this are nice, in that the changes at least feel like improvements. That said, I also found out that string_agg is now available in SQL Server, as of SQL Server 2017.

Final Notes

Obviously, I haven't covered all of the differences between Microsoft SQL Server and PostreSQL. The process and issues you encounter will depend highly on your application architecture. For example, we don't use common table expressions (CTEs), which apparently can cause performance issues in all but the latest (v12) versions of Postgres. Bottom line - you can do all the reading you want about incompatibilities, but there's no substitute for testing, testing, testing your application code.

For a further discussion of this topic, I'd recommend this Reddit thread: Coming from SQL Server; it touches on some of the benefits of moving to Postgres as well, which I really don't have the space to explore here. If you've made a similar migration, or just have more familiarity with database engines, please share you insights in the comments!

The biggest issue that we encountered was actually resolved with the latest update to the app (v4.5) - in earlier versions, when transferring tables with a primary key comprised of multiple columns, the order of the columns within the key/index was sometimes changed. I should also note that it's not a free tool. But, at $49 (with a limited, free trial), we found it well worth the time it saved. ↩
And yes, I know that a query-builder library or ORM would mitigate much of this, but it's still good to know. ↩
And if you're looking for a little more clarity, here's a fairly detailed Stack Overflow explanation: How Stuff and 'For Xml Path' work in Sql Server ↩

TIL: Generating .xlsx Files with CFML - An Easy Way to Reduce File Size

Matthew J. Clemente — Fri, 11 Oct 2019 00:00:00 +0000

Just a quick note about generating XLSX (Excel) files with ColdFusion, which basically amounts to me regurgitating the documentation. It’s a small change in code, but the reduction in spreadsheet file size can be considerable.

Some Background

I’ve been under the (mistaken) impression that when generating spreadsheet files with ColdFusion, my options were limited to XLS or CSV. I would generate the former using the built-in spreadsheet functions (spreadsheetNew()) and the latter by manually assembling strings into a comma-delimited format.

While I’m a big fan of the plaintext, interoperable nature of the CSV, some clients explicitly request Excel sheets - so they get their XLS files. These were always “good enough” for the basic reports they were requesting.

Nevertheless, it bothered me that I was using an outdated format for Excel files. My core frustration was practical - XLS files are so much larger than their XLSX counterparts - but this was never enough of a bother to warrant further investigation.

The Code

Consequently, I was happily surprised to discover that spreadsheetNew() provides a second boolean argument, xmlFormat, which indicates if the spreadsheet should be created in the XLSX format - it’s right there in the docs - somehow I’ve just always missed it. Here’s the complete method signature:

spreadsheetNew( [sheetname] [, xmlFormat] );

The xmlFormat option defaults to false, generating an XLS file - and that’s how I’d been using it for all these years. Sigh.

Here’s an example generating a spreadsheet, first using the default XLS approach, and then setting xmlFormat to true, in order to generate an XLSX file:

// legacy approach
filePath = expandPath( '/' ) & 'test.xls';
sheet = spreadsheetNew( 'XLS Test' );

for (i=1; i<=100; i++) {
  spreadsheetAddRow( sheet, i );
}

spreadsheetWrite( sheet, filePath, true );

// XLSX approach
xlsxFilePath = expandPath( '/' ) & 'test.xlsx';
xlsxSheet = spreadsheetNew( 'XLSX Test', true );

for (i=1; i<=100; i++) {
  spreadsheetAddRow( xlsxSheet, i );
}

spreadsheetWrite( xlsxSheet, xlsxFilePath, true );

In order to save it as an XLSX file, the only difference was setting the xmlFormat to true (and updating the file extension).

To take a look at the benefit of this change, let’s compare file sizes:

sheetSize = getFileInfo( filePath ).size;
xlsxSheetSize = getFileInfo( xlsxFilePath ).size;
bytesSaved = sheetSize - xlsxSheetSize;
change = ( bytesSaved / sheetSize )*100;
writeOutput( "File size reduced by #round( change )#% ");
// File size reduced by 54%

Sure enough, the resulting XLSX file is less than half the size of the identical XLS one. That’s a pretty big win for one very small change in code.

A Note for Lucee CFML Users

The Spreadsheet Functions provided by Adobe ColdFusion are not natively supported in Lucee. Fortunately, it’s not too much work to add them, as I’ve previously blogged: How Not To Use Spreadsheet Functions in Lucee.

Read that post for a more detailed exploration of using spreadsheet functions in Lucee, but the TLDR; is that there are two basic approaches:

Install the cfspreadsheet extension. This can be done programmatically or via the Lucee admin.
Take an altogether different approach and use Julian Halliwell’s cfsimplicity/lucee-spreadsheet standalone library for spreadsheet manipulation.

Happy spreadsheeting!

What is the Modulus Operator? A Short Guide with Practical Use Cases

Matthew J. Clemente — Fri, 12 Jul 2019 00:00:00 +0000

Addition, subtraction, multiplication, and division. These are the four mathematical operations I was taught during my childhood education, and their operators, +, -, *, /, are very familiar. I was not taught %, the modulus operator , which I recently discovered can be quite useful and interesting in its own right.

The modulus operator, written in most programming languages as % or mod, performs what is known as the modulo operation. You next response, understandably, might be, “That doesn’t clarify anything,” so let’s take a closer look:

How It Works
- The Modulo Operation Expressed As a Formula
Use Cases for the Modulo Operation
- Even / Odd and Alternating
- Restrict Number to Range
- Rotating Through Limited Options (Circular Array)
- Every Nth Operations in a Loop
- Converting Units of Measure
- Miscellany
Some Helpful Reading

How It Works

The modulus operator - or more precisely, the modulo operation - is a way to determine the remainder of a division operation. Instead of returning the result of the division, the modulo operation returns the whole number remainder.

Some examples may help illustrate this, as it’s not necessarily intuitive the first time you encounter it:

5 % 1 = 0
// 5 divided by 1 equals 5, with a remainder of 0

5 % 2 = 1
// 5 divided by 2 equals 2, with a remainder of 1

5 % 3 = 2
// 5 divided by 3 equals 1, with a remainder of 2

5 % 4 = 1
// 5 divided by 4 equals 1, with a remainder of 1

5 % 5 = 0
// 5 divided by 5 equals 1, with a remainder of 0

It may be helpful to think back to your early math lessons, before you learned fractions and decimals. Mathematics with whole numbers behaves differently - when dividing numbers that aren’t even multiples, there’s always some amount left over. That remainder is what the modulo operation returns.

If this seems strange, boring, or not particularly useful, bear with me a bit longer - or just skip ahead to the use cases.

The Modulo Operation Expressed As a Formula

As one final means of explication, for those more mathematically inclined, here’s a formula that describes the modulo operation:

a - (n * floor(a/n))

By substituting values, we can see how the modulo operation works in practice:

100 % 7 = 2
// a = 100, n = 7
100 - (7 * floor(100/7)) = 2

If you don’t find the formula helpful, don’t worry - I didn’t either at first. Some people find this abstract representation helps deepen or clarify their understanding of the operation, but you don’t need to know it.

A final note here - if you’re wondering how the modulo operation functions with negative numbers or decimals, that’s a bit outside the scope of this article. For our purposes here, we’ll only be dealing with positive integers.¹

Okay, enough with the math for now. While returning the remainder is what the modulo operation does, that’s not its only use; indeed we’ll see that it’s handy for a good deal more - but that was a necessary starting point.

Use Cases for the Modulo Operation

When I first encountered the it, the modulus operator seemed little more than a bit of mathematical trivia. I found it far more interesting as I started to learn its practical utility. I’ll discuss a few applications here.

Even / Odd and Alternating

One of the most basic use cases for the modulus operator is to determine if a number is even or odd.² This is possible because x % 2 always returns either 0 or 1. Even numbers, because they are evenly divisible by 2, always return 0, while odd numbers always return the remainder of 1. Here’s what I mean:

for( i=0; i <= 10; i++ ) {
  writeOutput( i%2 );
}
// 01010101010

So, what’s the use case? This technique is often used to alternate values within a loop. The first time I used the modulus operator, it was to manually zebra-stripe table rows, with the row’s background color based on whether it was even or odd. In a similar vein, I’ve used % to distribute the results of a web-form to two recipients, on an every-other basis; in pseudocode:

if( incrementalId % 2 == 1 )
  // send to one
else
  // send to the other

It’s a convenient hack any time you have a group or stream of records, widgets, leads, etc., that you want to handle on an alternating basis.

Restrict Number to Range

When you’re using the modulus operator for even/odd alternation, you’re actually taking advantage of one of its more helpful properties, though you might not realize it. Here’s the property: the range of x % n is between 0 and n - 1 , which is to say that the modulo operation will not return more than the divisor.³

Again, examples might help clarify this idea; in each instance here, the divisor is 5, so results will range from 0 - 4.

1 % 5 = 1
// 1 cannot be divided by 5, so the remainder is 1

4 % 5 = 4
// 4 cannot be divided by 5, so the remainder is 4

7 % 5 = 2
// 7 divided by 5 equals 1, with a remainder of 2

25 % 5 = 0
// 25 divided by 5 equals 5, with a remainder of 0

218 % 5 = 3
// 218 divided by 5 equals 43, with a remainder of 3

As you can see, regardless of the initial number, the modulus (divisor) limits the range of the result.

On its own, this property doesn’t seem useful, but when applied within a larger set, it can be used to create a pattern of circular repetition. Here’s an example of incrementing numbers with a modulus of 3:

0 % 3 = 0
1 % 3 = 1
2 % 3 = 2
3 % 3 = 0 // cycle back to 0
4 % 3 = 1
5 % 3 = 2
6 % 3 = 0 // cycle back to 0

Notice how the result of the modulo operation keeps repeating 0-1-2; the values wrap around. One way to describe these results is as a circular array, like numbers on the face of a clock. Let’s take a closer look at a practical application of this property.

Rotating Through Limited Options (Circular Array)

In a situation with a limited number of options - weekdays, primary colors, company projects, clients, etc. - we can use the modulo operation to walk through them in a repeating loop. That is, we can treat the array of options as a circle that we just keep cycling through.

Here’s a somewhat contrived example to illustrate this:

// array of options that we want to cycle through
weekdays = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri'];

// option count provides modulus (divisor)
dayCount = weekdays.len();

employeeCount = 14;

// loop over employees while rotating through days
for( i=0; i < employeeCount; i++ ) {

  // employee number mod option count
  dayIndex = i % dayCount;
  // adjust because CFML array indexed from 1
  dayIndex++;
  // use result to cycle through weekday array positions
  weekday = weekdays[dayIndex];

  writeOutput( "Scheduling employee on #weekday#. " );
}

As we step through a list of employees, we assign each to a weekday. Once we’ve scheduled five employees, we reach Friday. With no more options, assignment then loops back to Monday and continues the cycle until all employees are scheduled. This is possible because, as shown in the previous section, the modulus of 5 (the number of weekdays) returns a circular array of 0-4, that we can map to options in our weekday array. Seeing and understanding this was a major revelation for me.

Every Nth Operations in a Loop

Another application of the modulo operation is determining an interval within a loop; that is, calculating occurrences such as “every fourth time,” “once every ten,” etc.

The principle, in this case, is that if n is an even multiple of x, then x % n = 0. Consequently, x % 4 will return 0 every fourth time; x % 10 will return 0 every tenth time, and so on. One practical use of this is providing feedback within long or long running loops:

// given a list of widgets, files, people, etc.
longList = 10000; 
feedbackInterval = 100; // to be used as the modulus

// loop over the list to process each item
for( i=1; i <= longList; i++ ) {

  // perform some operation

  // mod operation gives feedback once every hundred loops
  if( i % feedbackInterval == 0 ) {
    percentCompleted = ( i / longList ) * 100;
    writeOutput( "#percentCompleted# percent complete. " );
  }

}

You could use a similar principle to trigger client/user interaction based on time or engagement. For example, send a promo every 90 days, or offer a feedback survey every 50 logins.

Keep in mind that time is also, in effect, a long running loop. Every hour has 60 minutes, repeating on a cycle. If you wanted to schedule a task to run four times an hour, you could achieve this using the modulo operation - just run it when minutes % 4 == 0.

Converting Units of Measure

Converting units of measure is common example of the modulo operation’s practical utility. The general use case is when you want to convert a smaller unit, such as minutes or inches/centimeters, to a larger unit, like hours or miles/kilometers; in these situations, decimals or fractions aren’t always helpful.

For example, if we wanted to know the number of hours in 349 minutes, having the result expressed as 5 hours 49 minutes may be more helpful than 5.8167 hours. Here’s a quick-and-dirty take at what that type of conversion function might look like:

function minutesToHours( m ) {
  hours = floor( m/60 );
  minutes = m%60;

  return "#hours# hours #minutes# minutes";
}
writeOutput( minutesToHours( 349 ) );

Standard division (rounded down to the nearest integer) determines the number of hours, while the modulo operation is used to keep track of the remaining minutes. Whether you’re dealing with time, distance, pressure, energy, or data storage, you can use this general approach for unit conversion.

Miscellany

You might think that I’ve exhausted all the situations in which you might use the modulus operator, but you’d be wrong. Here are a handful more that I found on Stack Overflow, Quora, and the internet at large:

Additionally, once you’re comfortable with the modulo operation, you shouldn’t have any trouble solving the FizzBuzz Question discussed here.

Some Helpful Reading

Credit where it’s due - I learned a lot from these posts while I was researching and writing this article:

Now you know it; go use it! If you’ve got other uses or examples that you’d like to share, I’d love to hear them.

Programming languages vary in their approach to supporting and handing floating point (decimal) modulo operations, as well as negative numbers. Working with these is outside the scope of this article, my concern, and most practical use cases that I’ve read about or encountered. ↩
Lest I get called out in the comments, I should note that bitwise operators offer another, even more efficient approach to determining if a number is even or odd, but that’s another topic for another day. ↩
I should note that the divisor in a modulo operation is also called the modulus. ↩

How to Install and Manage Multiple Versions of Java (hint: jabba and jEnv)

Matthew J. Clemente — Fri, 31 May 2019 00:00:00 +0000

One of my goals for the coming months is to improve my proficiency with Java; that is, to put some real knowledge behind my current patchwork understanding, pieced together over the years via trial-and-error.

Before diving into Java training courses on Udemy, Youtube, etc, I wanted to make sure that I had a way to use different versions of the JDK on my machine. This was due, at least in part, to the changes Oracle made to their Java licensing and release cycle. There are now a variety of Java versions and vendor distributions available, and I wanted to find a way to easily switch between different versions of the JDK without needing to actually update my system’s version of Java or manually set JAVA_HOME each time.

Basically, the same way I can use different versions of Ruby, Node, and CFML, thanks to rbenv, nvm, and CommandBox, respectively, it seemed I should be able to easily use different versions of Java, and not be tied to my system’s default version. So, like a good developer I did some Googling and stumbled across this helpful StackOverflow post, and ultimately settled on using both jabba and jEnv - the former to install versions and the latter to configure them on a per-directory basis. Here’s how it works:

jabba to install different JDK versions

jabba is a cross-platform Java Version Manager. I found that it really excels at streamlining the process of installing different versions of Java. You can find the installation instructions along with some very helpful documenation on the jabba Github repo.

Once installed, you can list available versions of the JDK via jabba ls-remote:

$ jabba ls-remote
1.12.0
1.12.0-1
1.6.65
adopt@1.12.33-0
...
zulu@1.7.95

Right now, there are over 100 results, literally from A-to-Z (that is, Adopt OpenJDK to Zulu OpenJDK).

To install a particular version, such as Corretto, you run:

$ jabba install amazon-corretto@1.11.0-3.7.1
#installed to ~/.jabba/jdk/amazon-corretto@1.11.0-3.7.1/

On Linux/Mac, the JDK version is downloaded/installed to ~/.jabba. Remember this, because you’ll need to know where these are when using jEnv.

Versions of the JDK not readily available, such as Oracle’s 11.0.3, which now requires an account to download, can be manually installed via a URL or file location. Here’s how I installed Oracle’s, after downloading it:

$ jabba install oracle@1.11.0-3=tgz+file:///Users/MYUSER/Downloads/jdk-11.0.3_osx-x64_bin.tar.gz

Using jabba with Intellij on Mac

When I tried to add the jabba-installed JDKs to Intellij as SDKs, I ran into a small issue - because they’re located in a hidden folder, they’re not readily selectable via the file browser. Unsurprisingly, I wasn’t the first person to have this issue, a Reddit post in r/java pointed me to two answers:

Within Intellij, when browsing for the JDKs in ~/.jabba, you can type Command-Shift-Period (⌘+⇧+.), which should reveal the hidden files/folders, making them possible to select. Apparently this doesn’t work in all Intellij versions, so there’s another option.
In regular Finder, navigate to the folder ~/.jabba/jdk/JDK@VERSION/Contents/Home - you should then be able to drag this into the Intellij Finder window, in order to use/select it.

jEnv for per-directory JDK assignment

With installing JDK versions sorted out, my next goal was to make managing and using them straightforward. A primary concern was the ability to assign a JDK version to a project/directory and then not have to think about it again. For me, jEnv nailed it.

So, what is jEnv? To pull a quote from its website:

jEnv is a command line tool to help you forget how to set the JAVA_HOME environment variable

So, while it doesn’t install different versions of Java, jEnv makes it much easier to manage the versions already on your machine (like those installed by jabba). Just a note that, unfortunately for Windows users, it is not currently cross-platform.

After installing jEnv, I needed to add the JDKs from jabba (and from my machine). This is done using the jenv add command and providing path to the JDK version:

# Add a jabba JDK
$ jenv add /Users/MYUSER/.jabba/jdk/oracle@1.11.0-3/Contents/Home/
oracle64-11.0.3 added
11.0.3 added
11.0 added

# Add the system JDK
$ jenv add /Library/Java/JavaVirtualMachines/jdk1.8.0_181.jdk/Contents/Home/
oracle64-1.8.0.181 added
1.8.0.181 added
1.8 added

# List JDK versions
$ jenv versions

Now - and this is what I was most excited about - I can set a Java version for a directory and then I don’t need to think about it again. Here’s how it works:

$ java -version
java version "1.8.0_181"

$ jenv local oracle64-11.0.3

$ java -version
java version "11.0.3" 2019-04-16 LTS

This works by adding a .java-version file to the directory, which contains the specified version. jEnv automatically ensures that this version is used within the folder (and subfolders). Set-it-and-forget-it.

Wrapping it up

I was ultimately very happy with this solution - it enables me to do some initial Java configuration for a project and then just forget about it and get back to the code. That said, I’m no Java guru. If you’ve got a better approach or see problems with this one, please let me know.