Tag: h2

Interesting Links podcast, episode 2

Welcome to the second ##java podcast.
We have lots of interesting things to cover, so let’s dive in.

  1. Java EE development has moved to the Eclipse Foundation, under the project name “Eclipse Enterprise for Java“, or “EE4J.” Java EE is still the branding for enterprise Java. This move makes Java EE more open; we’ll have to see how well it works under the Eclipse Foundation. We’ll survive either way; it’s a good move for everyone.
  2. RebelLabs’ Developer Productivity Report 2017 is here, almost 72% of the developers said their main programming language is Java 8 – and about time, considering Java 7’s been dead for two years; IntelliJ IDEA is the most popular Java IDE at 54% with the respondents, and one of the survey questions says that 91% of the people who like it said it’s because of superior functionality, as compared to 13% of Eclipse users and 73% of NetBeans users. Some other things that stood out: small teams are the norm, with teams of three to nine people making up half the teams, with medium-sized teams (10-19) coming in at 22%. Hmm, maybe a team of nine people isn’t actually all that small. It’s a great report; you should check it out.
  3. Given Java 9’s release and new features, it’s expected that a lot of migration articles are coming up. Sure enough, DZone’s in play with one that shows migrating a Spring app to Java 9. It has some module-based concerns and walks through fixing them; it’s not exhaustive, but it’s likely to be representative of early adoption efforts.
  4. Nicholas Frankel discusses some clean coding standards around lambdas. It’s easy to decide that a tool is available and thus must be used everywhere, he says – actually, he says that developers act like children and we have to play with our new toys, which is probably a pretty appropriate description. He shows a fairly ugly way to use lambdas primitively, then shows how it can be made a lot more developer-friendly. It’s not exhaustive, but still worth looking at.
  5. According to InfoWorld, Java 9 is not going to receive long-term support. That doesn’t mean it’s not supported, but that the long-term support plans are different than what we’ve seen in the past. Long-term support releases are going to be made every three years, so that’s the baseline for support plans; we’ll have to see if (and how) this affects Java in the long run.
  6. Up next: another DZone link, this time on Java’s Optional. The author, Eugen Paraschiv (from Baeldung) offers Optional as a tool for functional programming, and I suppose he’s right, in a way. The article does a good job of walking through most, if not all, of what Optional can do for your code, including with Java 9, and he does say that Optional is meant as a return type and not a property type, which is … better than he could have done. The article’s worth reading, and is done at much more depth than many similar articles.
  7. We also saw mention of OpenTable’s embedded PostgreSQL container. This allows us to treat PostgreSQL as if it were an embedded database (well, sort of); considering that PostgreSQL is a lot stronger for production use than, say, H2 or Derby, this is a nice way to do database-oriented integration tests on a “real database.” That’s not to say that H2 or Derby aren’t real databases, but they’re anecdotally used in the Java ecosystem more as embedded databases to help with integration testing than as production databases. Of course, now that I’ve made that assertion, I expect RebelLabs to ask something about this on their next survey and completely demolish my statement. Thanks ahead of time, guys.
  8. A bit more on Java 9. RankRed has “What’s new in Java 9,” covering a bird’s-eye view of the changes: the module system, new versioning, the Java Shell, a better mechanism for compiling for older versions of Java, JLink, compact strings, high definition graphics, new factory methods for collections – catching up to Kotlin and Scala, better networking and serialization security, Nashorn changes, a new random generator, segmented code caches, dynamic linking of object models, and an enhanced garbage collector. Whew, that’s a lot – and I left some out. It gets better, though: The Java 9 readme points out that the default JCE policy files now allow for unlimited cryptographic strengths, a feature that the RankRed list left off.
  9. Spring 5.0 has gone to general availability – it’s been released, in other words. Support for Java 9, Java EE 8, functional variants, Kotlin, a new reactive web framework… all kinds of goodies for Spring fans.
  10. Kotlin 1.2 Beta is out. Kotlin is another JVM language; this one’s from IntelliJ, the people who bring you the IDEA editor family. There are a lot of little improvements here, including some things that can drive you crazy during normal development – there’s also multiplatform support, which is important even if you’re like me and only really deploy on the JVM.
  11. We mentioned ZeroTurnaround early in the podcast – the RebelLabs report – but it’s worth noting that in addition to the developer survey, they also released JRebel 7.1, with Java 9 support, Spring 5 support, and a bunch of other things too.

Okay, that’s this week’s podcast – thanks for listening.

Using SQL's "IN" in JDBC

In SQL, the IN operator is used to restrict columns to one of a set of values. Using IN in JDBC, though, is sometimes problematic because of the way different databases handle prepared statements.
With JDBC, prepared statements use ? to serve as markers for values in a SQL statement. Thus, you might see:

PreparedStatement ps=connection.prepareStatement("SELECT * FROM FOO WHERE BAR = ?");

This serves to help prevent SQL injection attacks; assigning a value of "'' or 1==1'" would check that actual value against BAR rather than return all rows.
Exploits of a Mom
The parameter number of each ? is an index, starting from 1, so to set the value against which to compare BAR we might see:

ps.setParameter(1, "BAZ");

The IN operator in SQL allows selection from a set of values. Thus, we might see:

SELECT * FROM FOO WHERE BAR IN ('BAZ', 'QUUX', 'CORGE')

If BAR is one of BAZ, QUUX, or CORGE, then the row matches the query and will be returned.
It would make sense to see a PreparedStatement declared as:

PreparedStatement ps=connection.prepareStatement("SELECT * FROM FOO WHERE BAR IN (?)");

However, this doesn’t work. (It gives you only one element to use for the IN selector.) You have two choices: you can write SQL against your specific database, or you can generate custom SQL for the query.
Let’s look at the most general form (the SQL customization) first, since that’s going to be supported best. We are assuming a simple table, created with:

create table if not exists information (id identity primary key, info integer)

Note that we’re presuming H2 at this stage. In PostgreSQL, an equivalent statement would be create table if not exists information (id serial primary key, info integer). With MySQL… oh, who cares, nobody should use MySQL.

Given an array of data to use for the IN clause of Integer[] data = {3, 4, 6, 11};, we can construct a viable (and general) SQL query like this:

StringJoiner joiner = new StringJoiner(
  ",",
  "select * from information where info in (",
  ")");
for (Object ignored : data) {
  joiner.add("?");
}
String query = joiner.toString();
try (PreparedStatement ps = conn.prepareStatement(query)) {
  for (int c = 0; c < data.length; c++) {
    ps.setObject(c + 1, data[c]);
  }
  try (ResultSet rs = ps.executeQuery()) {
    showResults(rs);
  }
}

This code isn’t complicated, although it looks like a lot for what it does. It first creates a SQL statement with a placeholder for every element in the data array, then sets each placeholder to the corresponding value in data, and then runs the query. The SQL has to be regenerated for every case where data has a different length. (We could potentially reuse the statement if data always has the same length.)
You can also generalize this, depending on your database. It requires custom SQL, though, and the code to use the SQL differs by database as well.

H2

For H2, we can use the ARRAY_CONTAINS function. Our SQL statement will look like "select * from information where array_contains(?, info)", and the code to use this statement looks like this:

try (PreparedStatement ps = conn.prepareStatement(query)) {
  ps.setObject(1,data);
  try (ResultSet rs = ps.executeQuery()) {
    showResults(rs);
  }
}

H2 can use setObject() and use that as the input for the ARRAY_CONTAINS function; this way, we have one placeholder and we don’t have to generate custom SQL for every different size of the input array.

PostgreSQL

In PostgreSQL, we can use the ANY function. Our SQL looks like "select * from information where info = ANY(?)". Our code to use the statement:

try (PreparedStatement ps = conn.prepareStatement(query)) {
  Array array=conn.createArrayOf("INTEGER", data);
  ps.setArray(1, array);
  try (ResultSet rs = ps.executeQuery()) {
    showResults(rs);
  }
}

MySQL

Nobody should use MySQL.

This is offered somewhat tongue-in-cheek, for a few reasons: one is that I genuinely dislike MySQL, another is that the SQL technique offered here probably isn’t needed very often in the first place (so doing an exhaustive solution is overkill), and the third is ironic: this site is hosted in WordPress, and uses MySQL as the backend database. Irony ftw, right?

Conclusion

We’ve shown a few possibilities for restricting the results of queries, using a general-purpose restriction (IN, with custom SQL generated for every query, still protected from SQL injection attacks), and custom SQL queries for both H2 and PostgreSQL. These are definitely not the only possibilities; feel free to show how you’d do it, or discuss potential optimizations. Some sample code for these examples can be found at https://github.com/jottinger/jdbc_contains – note that some of the code might require modification for each database, and the project doesn’t describe how to create the PostgreSQL database. (The project was written largely to prove the mechanisms described here, and wasn’t meant to be a one-size-fits-all solution.)