JAVA JDBC Driver PostgreSQL: Parse numbers encoded as BYTEA object

Question

I have relation where for each record there is BYTEA column (UTF-8) encoding 3 numbers in the following order:

bytes 0-1: number 1

bytes 2-3: number 2

bytes 4-6: number 3

How can I parse the binary data to readable numbers?

Currently I have this and don't know how to continue:

Class.forName(dbDriver);
Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass);
Statement st = connection.createStatement();
String query = "SELECT ...";
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
    byte[] data = rs.getBytes(1);
    //TODO Parse
}

Thanks,

You seem to use byte 4 twice. I assumed that's a typo, and that number 3 is from bytes 5-6, not 4-6, i.e. 2 bytes long, same as number 1 and 2. — Andreas
– Andreas, Commented May 28, 2017 at 13:35
@Andreas Hi, my mistake. I edited. the first 2 are 2 bytes, the third is 3 bytes and can be negative. — michael
– michael, Commented May 28, 2017 at 13:46
If only the third number can be negative, then you really need to define the number format better. E.g. are the numbers stored in binary? If yes, are signed numbers stored in two's complement? Or is the sign stored as a byte by itself? If not binary, are the numbers stored as text? Or BCD (packed or unpacked)? Or something else? — Andreas
– Andreas, Commented May 28, 2017 at 13:51
@Andreas unfortunately I don't have all these info in the spec. Is there a common practice here? — michael
– michael, Commented May 28, 2017 at 13:54
Not with a 3-byte number, no, there is no common practice. I'd suggest you get some examples of the byte[7] values, and look at the bytes. Helps if the examples also show what 3 numbers they represent. — Andreas
– Andreas, Commented May 28, 2017 at 13:55

Andreas · Accepted Answer · 2017-05-28 12:31:44Z

2

That depends on how the numbers are stored.
Are they binary?
Are they signed?
Are they big or little endian?

Assuming yes to the first two, you can use bit-manipulation, e.g.

// Little-endian
short num1 = (short) ((data[0] & 0xFF) | (data[1] & 0xFF) << 8);

// Big-endian
short num1 = (short) ((data[0] & 0xFF) << 8 | (data[1] & 0xFF));

But it's probably easier to use ByteBuffer:

ByteBuffer buf = ByteBuffer.wrap(data).order(ByteOrder.LITTLE_ENDIAN);
short num1 = buf.getShort();
short num2 = buf.getShort();
short num3 = buf.getShort();

ByteBuffer is BIG_ENDIAN by default.

answered May 28, 2017 at 12:31

Andreas

160k13 gold badges164 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

michael Over a year ago

using ByteBuffer, where I specify that the first 2 numbers consists out of 2 bytes and the third one out of 3? Also, the numbers can be negative, is it matters?

Andreas Over a year ago

ByteBuffer does not have a method for getting 3 bytes as a number. It can do 1 (get()), 2 (getShort()), 4 (getInt()), or 8 (getLong()), all decoded as signed two's-complement binary numbers ("signed" means value can be negative). If you need a 3-byte number, use bit-manipulation, but I doubt you do (see my comment).

Coder ACJHP · Accepted Answer · 2017-05-28 13:01:29Z

0

Can you try this :

For String :

    String tatto = "my tatto"; //for example
    byte[] array = tatto.getBytes(); // Or any bytes
    String s = new String(array);
    System.out.println(s);

For byte[] :

    byte[] data = new byte[]{ 1, 16, 84, 2, 101, 110, 83, 111};
    long val = 0;
    for (int i = 0; i < data.length; i++)
    {
       val = (val << 8) + (data[i] & 0xff);
    }
    System.out.println(val);

edited May 28, 2017 at 13:01

answered May 28, 2017 at 12:26

Coder ACJHP

2,2541 gold badge23 silver badges38 bronze badges

5 Comments

michael Over a year ago

Hi I get java.lang.NumberFormatException.forInputString

Andreas Over a year ago

Calling toString() on a byte[] will produce something like [B@15db9742.

Coder ACJHP Over a year ago

That's right you will get byte[] from db but we don't know what the type of data before storing.

Andreas Over a year ago

What does updated answer have to do with getting 3 numbers from a byte[6] array, stored as 2 bytes per number?

Andreas Over a year ago

What does "For byte[] :" section, which gets one number from a byte[8] array, have to do with getting three numbers from a byte[6] array?

Collectives™ on Stack Overflow

JAVA JDBC Driver PostgreSQL: Parse numbers encoded as BYTEA object

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related