Skip to content

Instantly share code, notes, and snippets.

@praseodym
Created April 26, 2020 12:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save praseodym/66a5b7af569968dc53ce6a35936d735b to your computer and use it in GitHub Desktop.
Save praseodym/66a5b7af569968dc53ce6a35936d735b to your computer and use it in GitHub Desktop.
Java tool to migrate JSR310 java.time.LocalDate serialized as bytea in PostgreSQL to proper date column
import java.io.ByteArrayInputStream;
import java.io.ObjectInputStream;
import java.sql.*;
import java.time.LocalDate;
public class Migration {
public static void main(String[] args) throws Exception {
String url = "jdbc:postgresql://joost.chnet/choice?ssl=true&sslrootcert=wisvch.crt";
Connection conn = DriverManager.getConnection(url, "user", "password");
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT id, date FROM exam;");
PreparedStatement up = conn.prepareStatement("UPDATE exam SET newdate = ? WHERE id = ?");
while (rs.next()) {
long id = rs.getLong("id");
byte[] ser = rs.getBytes("date");
try (ByteArrayInputStream bin = new ByteArrayInputStream(ser)) {
try (ObjectInputStream in = new ObjectInputStream(bin)) {
LocalDate d = (LocalDate) in.readObject();
up.setObject(1, d);
up.setLong(2, id);
up.executeUpdate();
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment