Tag: postgres

Javachannel's Interesting Links podcast, episode 8

Welcome to the eighth ##java podcast. I’m Joseph Ottinger, dreamreal on the IRC channel, and it’s Thursday, 2017 December 20. Andrew Lombardi from Mystic Coders is with me again.
Please don’t forget: this is your podcast, with your content too. You can contribute by using a carrier pigeon and sending us notes encoded with rot13 – twice if you want to be really secure – or by using javabot on the IRC channel, with ~submit and an http link, or you can also write content for the channel blog at javachannel.org, or you can even just tell us that something’s interesting… we’ll pick it up from there.

  1. “Non-Blocking vs. blocking I/O: Go with blocking.” is an article by ##java’s surial. In it, he’s talking about asynchronous code, especially with respect to I/O… and his assertion is that you really don’t want to do it. If you decide you do (and there are reasons to) then you should at least rely on some of the libraries that already exist to make it easier… but he mostly points out that it isn’t worth it for most programmers. Interesting read, especially when you consider that Python and Node.JS live and die on this programming model.

  2. “To self-doubting developers: are you good enough?” is an article meant to make you mediocre programmers feel better about yourselves. It talks about the processes and exercises that we all more or less had to go through to achieve competence. It’s not a long post, but it has some good points; programming is practice and art, just like athletics, really – and sometimes you lose, sometimes you plateau, sometimes you have to put in time that someone else might not have to put in. Sometimes the other guy is a natural at some things, and your effort is required to give you the edge… but the good news is that you can put in the effort.

  3. Jason Whaley posted a link called “Incident review: API and Dashboard outage on 10 October 2017” that went into a Postgres multinode deployment failure. It’s a payments company, so the outage is a pretty big deal for them; the short form is that they had a series of failures at the wrong time, and the postgres installation failed. That’s something we don’t hear about very often – either because people are ashamed of it, or hiding it, or some other more nefarious reason, perhaps. More reasons why I’m a developer and not in DevOps? Some pretty in-depth analysis on multi-master Postgres and unintended consequences of architecture. Appears that aside from the Postgres-specific things mentioned here, it probably is a good idea to regularly introduce fault into your infrastructure to test it, to see where the problems are you didn’t intend. And the automation erodes knowledge.

  4. “Want to Become the Best at What You Do? Read this.” goes over five steps to being all that you can be including quoting “Eye of the Tiger” for added insult. Several of the ideas in here are valid though, focusing on improving your skills / self-improvement and putting yourself out there in a vulnerable way. All the items have a “The Secret” type of vibe around them though, which is a bit of a turn off. Love the process, better yourself, make a positive impact on the world, sounds pretty good.

  5. A user on ##java posted a reference to zerocell – a simple open source library to read Excel spreadsheets into Java POJOs. Apache POI is the go-to for this, but POI is a little long in the tooth; it’s always nice to see people creating new solutions. I don’t have any Excel spreadsheets that I need converted into POJOs handy – and I don’t think I’ve EVER had them… except maybe once.

  6. “Understanding and Overcoming Coder’s Block” is YET ANOTHER lifestyle article for this podcast; it’s addressing those times when someone who might otherwise be a good coder – or writer, or anything – encounters the inability to write anything worthwhile. It’s focused on code, but it’s pretty general even so: reasons include a lack of clarity on what you’re trying to achieve, or a lack of decisiveness about how to solve a problem, or maybe the problem just seems too big to solve, or maybe even that you’re just not all that jazzed about the project you’re working on. It also addresses external factors – you know, real life – that might be getting in the way. Lastly, it includes some tips for each of those problems to perhaps point the way forward.

  7. “The Myth of the Interchangeable Developer” is yet another lifestyle article that points out what we all know but that recruiters and managers seem to be ignorant about: we all have specific skillsets. If I’m a good services developer, it doesn’t necessarily follow that I’m a good UI developer, for example… it doesn’t mean that I can’t learn, but it certainly implies that there’s an extra cost in time or aptitude for me to actually design a UI.

  8. “Understanding Monads: a guide for the perplexed” is an article trying to explain monads yet again. Maybe it’s me, but I’m thinking that monads might be one of those formal terms that’s useful but not useful enough, because they’ve been around forever but people still don’t get them. Maybe there’s a giant set of programmers who shouldn’t be allowed to program… but my feeling is that ‘monad’ is mostly jargon. To me it’s a stateless bit of code that defers state elsewhere, so it’s “functionally pure.” Lots of languages that rely on asynchronous programming have a similar concept, but they don’t necessarily call them “monads” (and they can store state elsewhere, too, so maybe they’re cheating.) It’s a decent article, but if you don’t understand monads, it may not .. actually change anything for you. But maybe it will.

  9. Ah, Project Valhalla. DZone has an article – pretty old now, actually, a month or two – that talks about Valhalla. No Valkyries, unfortunately, but value types instead: object references that are referred to just like primitives. This means that Java might get some forms of reification… but it’s hard to say. The main thing I wanted to see from the article was more clear example code; there’s one that boxes an integer in a generic class, without specifying the integer type, but I’m not actually seeing where there’s a real benefit in code yet.

  10. One of my favorite subjects is up next: “Why Senior Devs Write Dumb Code and How to Spot a Junior From A Mile Away.” Want to find a junior developer? Find someone who spends four hours tuning a bit of code that will run … once every four hours. Overexerting yourself trying to write the perfect bit of code every time… that’s a junior developer. Of course, we all know some senior developers who do the same sort of thing… and we tolerate them, but it’s just tolerance. I don’t write supercomplicated code if I can help it, and I’d rather provide simple code to get something simple done if I can, even if that means I’m wasting a few hundred K of RAM or a few dozen milliseconds. I mean, sure, if we need those milliseconds or that RAM, we can tune for that… but we do that when necessary and not otherwise. A summary might be: hesitate to wax locquacious when your innate desire is to extrude tendrils for others to admire your skill; alternatively, allow their senses to inhale your greatness despite their inability to immediately perceive how impressive your capabilities are, especially in comparison to their own.

Javachannel’s interesting links podcast, episode 6

Welcome to the sixth ##java podcast. I’m Joseph Ottinger, dreamreal on the IRC channel, and it’s Monday, 2017 October 30. Scary! Tomorrow’s Halloween.
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. (And guess what? That happened this week. See entry #4!)

  1. First up we have two articles about NoSQL from Matt Raible and Okta.com – “NoSQL Options for Java Developers” and “NoSQL Options for Java Developers, Part II.” They were published almost a month apart. In Part I, Matt mostly focuses on a very general overview of the NoSQL landscape and how he chose what NoSQL products to focus on – which ended up being MongoDB, Redis, Cassandra, Neo4J, and Postgres JSON. In Part II he encourages more of a discussion from some NoSQL luminaries about those five. They’re sharing stories and anecdotes about the databases, and it’s interesting reading, even if a little light on content – but they do have some interesting quips to throw out. One in particular stood out to me: you really should justify using something other than Postgres these days, and while I’m a big proponent of NoSQL, I totally agree. They also point out that some people see NoSQL as a magic bandaid for performance, and that’s simply not the case, at all.
  2. Up next we have “A Gentle Introduction to the Bag-of-Words Model.” The Bag-of-Words is a very common way to help trim down inputs for automated learning, or artificial intelligence; one of the things about AI is that it’s painfully easy to overwhelm systems with irrelevant input. This article introduces one of the most common ways to trim input, by providing a focus on words used most commonly in a bit of text – a corpus – or maybe the least common words in that corpus, too, really. (Which set of words you choose depends somewhat on what you’re trying to do.) Interesting article, fairly well done, I thought. Not heavy on code – in fact, painfully light on code – but bag-of-words is actually really easy to write, so that’s not a big deal.
  3. Java 9 is all the rage these days, even though I don’t know anyone who’s actually deploying it yet. Our next article is “Making JSR 305 Work On Java 9,” which focuses on using the annotations for software defect detection – JSR 305 – along with annotations from javax.annotation, which apparently doesn’t work very well in Java 9, due to the packages being split across multiple jars. Part of the problem is the implementation of the JSR 305 annotations themselves; it’s not really been a problem in the non-modular world, but Java 9 is more strict (and should be) about how modules are implemented. The article shows some workarounds and actually does enable the use of the various annotations (and has some warnings along the way) but I think the greater takeaway is that Java 9 is a sea change for Java. It’s probably one that was necessary – sure, we had OSGi and Java EE to help modularize the JVM, but there were and are crucial limits to those approaches that Java 9 is actually trying to address – but even though it’s been necessary there’s still a lot of pain and technology transfer involved.
  4. We also have some content from ##java denizen yawkat: “How (not) to extend standard collection classes.” It came from a discussion on ##java about someone needing a bounded PriorityQueue. I was being really pragmatic and short-sighted, and suggested just overriding the add() to enforce a limit, but the truth is, that is short-sighted – and yawkat wrote this article discussing why and how to actually extend such classes properly. Very well done, and it’s much appreciated.
  5. Another note about future releases of Java – I was going to say “Java 9” but apparently that numbering system is endangered. There’s a JSR – JSR 383 – for Java 18.3, the release scheduled for March 2018. The main difference I see is in local variable type inference, JEP 286. Type inference is cool – Scala and Kotlin have it and rely on it – and it’s great that Java will get it soon too, but the “Java 18.3” name is going to take some getting used to. I still don’t like it. Maybe this is part of their Halloween pranking: “Boo! It’s Java 18.3!” to which my response is, well, “booooo.”
  6. WildFly 11 Final is available. There are lots of improvements here – not many of them are earth-shattering, I think (and I’m waiting for them to go to WildFly Orange or WildFly Jet Fuel, following Oracle’s ridiculous naming practices – okay, I’ll shut up about how I don’t like “18.3” from here on out.) Anyway, WildFly is the fully open source version of JBossAS; it really is a great product. I prefer it to all of the other Java EE containers these days, even though I usually work on distributed services and not monolithic servers any more. I use a lot of the Java EE APIs without using an actual traditional Java EE container, and that’s the current trend I’m seeing – but I’m pretty sure that my data set is pretty constrained. Congratulations on the release, WildFly people.
  7. InfoQ comes in with the JUnit 5 release announcement. JUnit5 changes some annotations and introduces lambdas into the testing mechanism. It also changes the runner mechanism. It requires Java 8, but that’s okay; everyone should be on Java 8 by now anyway. I really like that JUnit is moving forward; I’m mainly a TestNG user, but TestNG has been pushing JUnit to add features, and I imagine that JUnit will likewise push TestNG to add features. Everyone wins.
  8. Up next we have “Project Loom: Fibers and Continuations for the Java Virtual Machine.” This feels really familiar, actually – there used to be a Continuations feature as part of RIFE, and that was around years ago. But here we have an official continuations and fibers suggestion; fibers are lightweight threads, and continuations allow scheduling of tasks on those lightweight threads. I’m not doing it justice here, but it looks great; it’s a long article, worth the read, notable more for its existence than its actual technical content at this point (but the technical content is a good read. Many other platforms have features like this, so it’s good that Java might get them.)
  9. The Gradle project has published “The State of Gradle Java 9 Support,” an article going over the increasingly popular build tool’s support for and integration with Java 9. Multi-release jars still rely on an external plugin, but the module system is supported by Gradle’s dependency resolution, from the looks of it. This is an excellent practice to have, on Gradle’s part – let people know what is supported, how it works, how to use it, and set the expectation that changes will be discussed and documented. It’s almost like open source; open discussion and knowledge transfer.
  10. Lastly, there’s a call for votes on the OpenJDK mailing lists for a new garbage collector, ZGC. It’s currently a skunkworks project of sorts for Oracle, and is aimed at providing low latency for multi-terabyte heaps, a phrase that gives me the willies. I’ve worked with large heaps before – but mainly through things like Azul’s Zing platform. It’s really neat that the Java ecosystem keeps changing like this, though, in that we gain new feature sets all the time and Java just gets better and better. Now if we could only maintain sane version naming and remember to work together…

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