Tag: jdbc

Javachannel's interesting links podcast, episode 5

Welcome to the fifth ##java podcast. I’m Joseph Ottinger, dreamreal on the IRC channel, and it’s Monday, 2017 October 23.
This podcast covers news and interesting things from the ##java IRC channel on Freenode; if you see something interesting that’s related to Java, feel free to submit it to the channel bot, with ~submit and a URL to the interesting thing, or you can also write an article for the channel blog as well; I’m pretty sure that if it’s interesting enough to write about and post on the channel blog, it’s interesting enough to include in the podcast.

  1. First up, we have a DZone entry; DZone‘s actually really good at picking out content that’s interesting. However, sometimes you have to be fairly selective about what you read, because they end up like a lot of such sites and go for volume and consistency in publishing as opposed to being selective for stuff that’s truly relevant. That’s why you have things like this podcast, of course, because I clearly know what’s interesting and relevant more than they do! Anyhoo, the actual reference is for Eclipse: “Fifteen Productivity Tips for Eclipse Java IDE Users,” and they’re pretty good; none of them are what I would consider the most obvious (which is: “Use IDEA instead”). The truth is, Eclipse is very popular; anything that helps people use their tools more efficiently is a good thing. Some of the tips are fairly obvious (“use the most recent version of Eclipse”) and others are just things that experienced users might know and use already, but that’s the benefit of articles like this: they make sure that everyone has a baseline of competence. Other tips: switch editors with ctrl-tab; group related projects in working sets rather than using multiple workspaces (this is one of Eclipse’ better features, and I’m glad it’s here); download the sources of libraries; conditional breakpoints and watchpoints; leverage code coverage. There are more (nine more, making a total of fifteen, as the article title promises), and none of them are awful.
  2. Next up: Java 8 updates have an end-of-life: September 2018. Along the way, new versions of Java 9 and Java 8 have been released (9.0.1+11 and 8u151/8u152) – which is good, I suppose, although expected with a new major release – but the big news here is that Java 8 is going to see no more public updates after September 2018. Progress marches on, but I have a feeling this is going to be like the Java 7 migration – which is still ongoing. We aren’t seeing as many people saying they’re still on Java 7 – or Java 6 – as we used to, which is anecdotally a good signal that people are moving to Java 8 after all. So who knows? Maybe with such a recent mass migration to Java 8 there will be momentum to allow people to move to Java 9 – especially if they don’t have to use the module system yet – and people will stay more current.
  3. More DZone: they’re on a roll (and sneak preview: they have two more links after this one). The entry this time is “Artificial Intelligence: Machine Learning and Predictive Analytics.” It’s a fairly high-level guide, and being on artificial intelligence, it’s not just Java – and shouldn’t be. It’s a good reference, though. It’s well-done. I would love to see Java be more relevant in AI; it’s certainly relevant, and is a major player in the space, but the truth is that the starting point for AI is in Python, not Java. The same goes for natural language processing; you can find tools in Java, to be sure (Stanford NLP, for example), just like you can find AI resources in Java (WEKA, among others) but they’re typically trailing the cutting edge. Most data scientists would see a preference for Java as a bit of an affectation. (And I say that because I do prefer Java, and the data scientists I know think I’m a loon for that. They’re probably right.)
  4. This is an old link, but it showed up on my feeds recently, so I’m pretending the publication date of May 16, 2017, is badly inaccurate: Java 8u131 – and yes, 131, 151 is the current build – is transparently aware of Docker memory and CPU limits. Why is this important? It’s because older builds were, well, not aware of Docker‘s machine limitations. The idea is that Docker runs a constrained virtual machine; your actual machine might have 16Gb of RAM, but your Docker image might have 2Gb available to it, and only two of your eight cores. But if you ran an older build of Java in that Docker image, it would use the actual physical machine limits to gauge heap allocation limits and CPU core usage – which could obviously cause problems (your 2Gb image would allocate heap as if it were on a 16Gb machine, which would be incorrect). So… I guess, what with this information being fairly stale, it’s good that they fixed this. And if you happen to be running an older Java, update, please. Note that you do actually need to tell the JVM to use group memory for the heap. This is via two command line options: -XX:+UnlockExperimentalVMOptions -XX:+UseCGroupMemoryLimitForHeap.
  5. Another DZone article! This one is “Automata-Based Programming in Spring.” It really serves as a bare introduction for Spring Statemachine, which isn’t quite what the title led me to expect – I was thinking that I was going to get to read about how to apply cellular automata for problem solving, a la Wolfram Alpha, but instead it’s just a library that makes state transitions easy to manage. It’s a Finite State Machine, not Cellular Automata. This is on me for reading it wrongly, by the way; FSMs are automata, but not cellular in nature.
  6. Daniel Dietrich wrote an article called “Opinionated Database Access in Java” – because we all know that database access has no opinion involved at all, ever. In this case, he’s writing a library that provides yet another abstraction: this one leaves modeling to the database; complex queries are moved to the database; access should be simple and obvious. In other words, it’s one of the Java libraries that provides access to the database services, as opposed to backing up Java data structures with a database. It’s not mature yet (and he provides an example of the API using Scala, too, so it never will be mature.) The only thing is: the article doesn’t provide a reference to an actual project, so it’s all vaporware at this point. Plus, as the lively comment flow indicates, it’s another entry in a space that’s very crowded with possible implementations depending on what you want, from ORMs like Hibernate to JDBC layers like MyBatis and jOOQ.
  7. Java’s version numbers are likely to change. Java has generally followed a semantic versioning approach: you have a major version, a minor version, and a build number (sort of). However, there’s a proposal put together by Mark Reinhold (He Who Controlled Java 9’s Release) to go to a date-based release cycle, so the next release won’t be Java 10, but Java 18.3, meaning “released in the third month of 2018.” There are a few problems with this proposal, and I’m hardly alone in seeing them: one is that there’s not a “major release” associated with the build. With Java 8 versus Java 7, there’s a clear delineation of major versions; Java 8 is the one with streams. Java 9, likewise, has Jigsaw. But the next major feature – let’s say “value types” as an example – might be in Java 18.6 as opposed to Java 18.3, so we lose the ability to easily determine feature groups. Plus, Java applications will have a harder time determining the actual baseline versions they require; right now they can parse out the major version and say “Oh, I’m on Java 8 instead of Java 7” but now they’ll have to factor in the actual release year. Maybe it’s me being a curmudgeon, maybe it’s me resenting how Mark handled the Java 9 release, but I think semantic versioning is still better than the year/month release versioning. With Reinhold proposing it, it’s likely to be approved by fiat; I’m sure it’ll grow on me over time, like a fungus, but I still don’t have to like it. Now get off my lawn!
  8. Last week I highlighted Excelsior JET, which allows delivery of native binaries using Java 8 (so far). This week, we see Steve Perkins writing “Using Java 9 Modularization to Ship Zero-Dependency Native Apps“, using Java 17.10… yeah, the date-based versioning isn’t something I like at all yet. Anyway, it’s just a simple “Hello world” example, but it, like others, is a good start; I like seeing articles like this, because this is how we build a repository of knowledge concerning how to use these neat new features Java 9 provides.
  9. And now for the last of our links, this one also from DZone: “OpenLiberty.io: Java EE Microservices Done Right.” OpenLiberty is another microservice framework, like Spring Boot, DropWizard, or Vert.x, this one focusing fairly heavily on canonical Java EE APIs (as opposed to leveraging those APIs where appropriate as Spring Boot or DropWizard do.) It’s billed as a “deep dive into OpenLiberty,” but it’s really not; it’s really a cursory example with a single JAX-RS endpoint (although it does show live redeployment, which is neat.) The actual OpenLiberty sample application isn’t much to speak of; the redeployment is important, but the main thing the article shows is configuration of the OpenLiberty build, which is probably the most important thing it should want to show. It’s interesting; it’d be interesting to try out.

Interesting Links – 2017/Jun/12

Interesting Links – 2017/Jun/5

Interesting Links – 18 Nov 2016

Today’s apparently a Microsoft edition of the interesting links! Almost everything relates back to them this time…

  • From wyvern: “The Error Model” is an article discussing… exceptions. Exception handling and the “checked or unchecked” question is (still) controversial in Java; article provides some interesting context on error handling approaches, and explains how error handling evolved in Midori (Microsoft’s experimental research OS). A fundamental decision when choosing to make your API throw a checked or unchecked exception is whether or not a particular error is considered fatal or not. This is frequently a murky question: if you’re writing a one-off script, IOException may be fatal, but for a long-lived daemon, it should be handled safely and not rethrown to the top level. While we obviously can’t use Midori’s exact approach in Java, the thought process exhibited is helpful when deciding how to structure your APIs to allow tidy error handling. The whole Midori blog series is worth reading, if you’re looking for more.
  • Microsoft has announced that it is a Linux Foundation platinum member. My, how things have changed…
  • Speaking even more of Microsoft, it looks like they’ve finally open-sourced their SQL Server JDBC driver, and it’s hosted in a Maven repository. Typically, people use jTDS instead, but this is potentially great news; it’s not clear offhand what the advantages are compared to jTDS, but the more options you have, the better, right? Plus, this is another example of Microsoft actually contributing to the larger ecosystem, something many are still not used to. Now, if they could only make SQL Server less of a pain to work with…
  • Finally, stepping off the Microsoft train, user asgs pointed out Simon Ritter‘s “20 Years Of Java Deprecation,” which details the list of accumulated deprecated classes and methods in the Java runtime library. It’s actually a really low number given Java’s maturity (maybe that’s a sign of maturity?) — and it also points out that hardly anything is actually removed, although that will change with Java 9, with a whopping six methods being removed.

Interesting Links, 1 Feb 2016

Editor’s note before we get to the links: I’ve been trying to keep the links’ length down to a manageable four or five, so the frequency’s been higher than I might otherwise have desired (roughly every two or three days). I’m going to try a longer list of interesting links, and move the frequency down – we’ll see if that’s useful and palatable. The problem is that ##java (and the Java community overall) just has a lot of interesting, relevant content that’s worth keeping! (Keep it up, guys. “Too much interesting stuff” is a good problem to have.)

  • WildFly 10 has been released. This is the latest version of the open source (community-supported) JBoss Application Server; it’s fully Java EE 7, and requires Java 8. Very cool stuff, congratulations to the WildFly team.
  • Neo4J has released milestone 1 of their object/graph mapping library’s second version. (Read it with me: “Neo4J has released OGM 2.0 m1.” Much simpler that way.) It sounds promising, especially since they seem to have straightened out the connection process to Neo4J instances such that both embedded and remote instances have similar capabilities.
  • “Implied Readability” uses readability as a term to address transitive dependencies in Java 9, more or less, and shows how a module can export visibility of a dependency to other modules. As stated: Java 9, not Java 8, so it’s a new feature – but it looks a little like how OSGi exports visibility rules. It might be really relevant as time goes on and Java 9 gets closer. (It’s based off of information in “Programming with Modularity and Project Jigsaw. A Tutorial Using the Latest Early Access Build” published on InfoQ, so there may be more interesting stuff in that article.)
  • “How we accidentally doubled our JDBC traffic with Hibernate” discusses an obvious issue (doubling JDBC traffic!), found when Hibernate logging was set to WARN – because Hibernate then re-executes every query in order to show the warnings associated with the query in question. The warnings can be useful, to be sure, but be wary!
  • PNG encoding from Java’s ImageIO can be slow, according to one ##java op. He said that he used ObjectPlanet‘s PngEncoder and got much better performance.
  • As an update to the process by which one can examine request headers (mentioned in “Interesting Links, 22 Jan 2016“) a ##java user mentioned RequestBin, which allows you to build a URL and issue requests against it, to examine the actual traffic data.
  • Moving to a Plugin-Free Web – by Oracle – says it point blank: “Oracle plans to deprecate the Java browser plugin in JDK 9. This technology will be removed from the Oracle JDK and JRE in a future Java SE release.” This makes sense – browsers are ignoring the java plugin (and should). If you’re still doing applets, stop. Oracle has spoken, and the technology is going away. (Now if we could get Oracle to get rid of Vector somehow…)
  • ZeroTurnaround – who gave the world JRebel – posted Java 8 Streams cheat sheet, which offers a one-page example of a lot of useful, relevant information around streams for handy, quick reference. I looked for a DZone Refcard on streams for a point of comparison, but they didn’t have one that I saw on first scan – which is surprising, since the Refcardz are actually done really well, in general.

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.)