Comparing byte value with bytea column in postgresql

Question

I use PostgreSQL as my database and Java servlet to communicate with PostgreSQL. My application requires me to retrieve data from the database for the corresponding input file. All files are stored in the database in the bytea column. This is my code:

String checkdb="select * from key where certificate = '"+c+"';"; Statement stcheck=conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet newrs=stcheck.executeQuery(checkdb); newrs.last(); int rowcount=newrs.getRow(); if(rowcount!=0) throw new ArithmeticException(); 

where certificate is the name of the column containing bytea data, and c (loaded) is the certificate in the form of byte[] . So here I check to see if a certificate is already uploaded in the database. If he is present, I throw an exception.

But the problem is that downloading the same certificate several times does not throw an exception, it works as if both certificates were different.

I am comparing byte values ​​correctly. What should I do to fix this?
EDIT
I tried to set the certificate column as the primary key. But still, the certificate is inserted into the database. How could this happen? For some reason, the internal comparison also fails.
EDIT
I tried to find itl and converted it to a string:

 InputStream input13 = retrs.getBinaryStream("certificate"); ByteArrayOutputStream output13 = new ByteArrayOutputStream(); byte buf[]=new byte[1024]; int len; IOUtils.copy(input13, output13); input13.close(); byte[] retvalue=output13.toByteArray(); String s1=new String(retvalue); String s2=new String(c); System.out.println("in--------------"+s2); System.out.println("out----------------"+s1); 

retries is a result set that contains the certificate you just saved. c - input certificate in bytes []. The output that I get is:

 19:05:43,156 INFO [STDOUT] in-------------------BEGIN CERTIFICATE----- MIICIjCCAYsCAQMwDQYJKoZIhvcNAQEFBQAwYTELMAkGA1UEBhMCYXMxCzAJBgNV BAgMAmFzMQswCQYDVQQHDAJhczELMAkGA1UECgwCYXMxCzAJBgNVBAsMAmFzMQsw CQYDVQQDDAJjYTERMA8GCSqGSIb3DQEJARYCYXMwHhcNMTIwNDEwMDY0ODQ3WhcN MTMwNDEwMDY0ODQ3WjBSMQswCQYDVQQGEwJhczELMAkGA1UECBMCYXMxCzAJBgNV BAcTAmFzMQswCQYDVQQKEwJhczELMAkGA1UECxMCYXMxDzANBgNVBAMTBmNsaWVu dDCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAsfJq9TVgTLqz84nuWpDm+dvI do2HIWoHkmEPNowK4xn1+qWUPCy30aASWI92YMtE6njtQGyZBYij0iTAxldDktqd bMdpKAONDlSv71rn+fZPuyenbBFYZexybNVsRMFzTD/F/hiPPXT7E1j9CrgIMxVz ScZeNATh/dPLwuNQ0dsCAwEAATANBgkqhkiG9w0BAQUFAAOBgQDDUyeIWlKaMMkM 8VRGuDJyKFC8miQfmul6zE4+6RTswHKjcUoje2vhTQgV+GBfqx8ai+ziujx8CeyR eJWcb1G8gg+LMUNDcTOlXTeBG9L2c/gMOEu7BEjtpojSYmFyEvJEQ5Yep44HKZZb FSPFVNxGiXBhNxtKQdAu6/smQ/eBNg== -----END CERTIFICATE----- 19:05:43,156 INFO [STDOUT] out----------------[ B@17f8b39 

I am wrong. Because [ B@17f8b39 looks like a memory location or something like that. Answer
As @jarnbjo said, one thing to keep in mind. When you ever use a byte array in postgressql, or for retieving or inserting use it only with setBytes() .Else only the memory cell (hash code in java) byte [] is inserted.

Answer 1

Use PreparedStatement:

 PreparedStatement pstat = conn.prepareStatement( "select * from key where certificate = ?"); pstat.setBytes(1, c); ResultSet newrs = pstat.executeQuery(); 

Of course, you must do the same when inserting data.

Answer 2

Obviously, you are not doing the comparison correctly based on the results you see. You need to do some debugging to figure out where everything is going wrong. I would suggest a simple test in the following lines:

• start with a clean database
• get a certificate from a client
• insert it into the database
• immediately return it (it should be the only one) - compare the data that you inserted with the data that you are reading. They must be the same. If they do not match, find out why
• Once this happens, extend the test case to run a query similar to the one asked in your question. If this returns null entries, find out why.

From the point of view of clarifying the reasons for the differences, you may find it useful to start with a simple test case when you insert a simple array of bytes - for example, one of one byte.

Answer 3

I would recommend that you generate a checksum of the file and save in db and check the contents of the file based on the checksum instead of the bytes file

 table key{ id int, certificate blob, checksum varchar(32) } 

And use the following method to create a checksum byte[] buffer

 public String getCheckSum(byte[] buffer) { StringBuilder builder = new StringBuilder(); try { MessageDigest complete; complete = MessageDigest.getInstance("MD5"); byte[] rawCheckSum = complete.digest(buffer); for(byte b: rawCheckSum) { builder.append(Integer.toString( ( b & 0xff ) + 0x100, 16).substring( 1 )); } } catch (NoSuchAlgorithmException e) { throw new RuntimeException(e); } return builder.toString(); } 

Finally,

 String checkdb="select * from key where checksum = '" + getCheckSum(c) + "';"; 

Edit:

When inserting bytes into db, you should use:

 String query = "INSERT INTO key (certificate) VALUES (?)"; PreparedStatement pstmt = connection.prepareStatement(query); pstmt.setBytes(1, c); 

Instead

 String query = "INSERT INTO key (certificate) VALUES ('" + c + "')"; PreparedStatement pstmt = connection.prepareStatement(query); pstmt.execute();