[FIXED] How to retrieve MySQL column content stored as mediumblob?

Issue

In a project that was passed on to me there is a MySQL database that is used to store lots of data. Each database has a table that contains lots of text snippets (4 chars) that is organized as follows:

id (long,increment), text(char(4)), count (varchar), and DATA (Mediumblob)

DATA stores an array of longs as binary, but it depicts as cryptic symbols if I select on the table.

I tried multiple CONVERT and CAST commands already, but it never yields something I could use in some way. I need the content which is encoded here, so any ideas what I could try to do?

I checked all of the Java code, but I can't really get how this column is filled with data in the first place. That seems to be a wonder of the Hibernate packages. The last actual call I found is

found.addSeq(seq.getId());

where found is the instanced entity=table and addSeq does the following:

public void addSeq(long id)
{
    ensure(seqsCount);
    seqs[seqsCount]=id;
    seqsCount++;
}

and ensure() this:

private void ensure(int minSize)
{
    if(seqs.length<=minSize)
    {
        long tmp[]=new long[minSize<20 ? minSize+5 : minSize+20];
        System.arraycopy(seqs, 0, tmp, 0, seqsCount);
        
        seqs=tmp;
    }
}

The only hints about the column DATA are these two functions, but they are never called in the code anywhere. Any idea how this is able to work?

@Column(name = "DATA", unique = false, nullable = false, length=8000000)
public byte[] getData()
{
    ByteBuffer b=ByteBuffer.allocate(seqsCount*8);
    for(int i=0; i<seqsCount; i++)
    {
        b.putLong(seqs[i]);
    }

    return b.array();
}

public void setData(byte[] data)
{
    seqs=new long[data.length/8];
    seqsCount=0;
    
    ByteBuffer b=ByteBuffer.wrap(data);
    while(b.hasRemaining())
    {
        ensure(seqsCount);
        seqs[seqsCount]=b.getLong();
        seqsCount++;
    }
}

Solution

Your code shows how to "decode" the DATA. found.getData() will return the binary content of the data of current entity: found. Then you can call the similar logic to show or get the data from the byte[] data.

public List<long> fetchData(byte[] data)
{
    long res=new long[data.length/8];
    int k=0;
    ByteBuffer b=ByteBuffer.wrap(data);
    while(b.hasRemaining())
    {
        res[k++] = b.getLong();
    }
}

Use the fetchData, and then you can show the List of long value as you expect.

Answered By - SeanH
Answer Checked By - Katrina (JavaFixing Volunteer)