want to insert byte array to postgres column of type oid using jdbctemplate in spring

Question

I am using JdbcTemplate from Spring framework.

The database is Postgres.

Issue is I am reading .cer file in java and want to store the content of this certificate to postgress database of column type OID and using jdbctemplate but getting exception is

column "cert_file" is of type oid but expression is of type bytea

Below is code

final String sql1 = "INSERT INTO sp_certificate_detail " +
                            "(public_key,cert_file)values(?,?)";

                    status = jdbcTemplate.update(new PreparedStatementCreator() {

                        @Override
                        public PreparedStatement createPreparedStatement(
                                Connection connection) throws SQLException {

                            PreparedStatement ps = connection.prepareStatement(
                                    sql1, new String[] { "cert_id" });                                
                            ps.setString(1, spreg.getPublicKey());
                            ps.setBytes(2, spreg.getCertFileContent());//This is byte[] of type
                           return ps;
                        }
                    }, keyHolder);

To get certiticate content am using below code

cfb = CertificateFactory.getInstance("X.509");
X509Certificate certb = (X509Certificate) cfb.generateCertificate(fileInputStream);
spreg.setCertFileContent(certb.getEncoded());

Please anyone suggest the solution

I have tried converting byte[] to LOB then getting different exception

Large Objects may not be used in auto-commit mode.; nested exception is org.postgresql.util.PSQLException: Large Objects may not be use d in auto-commit mode.

Code is below

ps.setBlob(10, (new SerialBlob(spreg.getCertFileContent())));

Answer 1

I have changed column type to bytea now its working

Answer 2

Integrate below piece of code with your code, should work.

import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

(package available in postgresql jdbc jar)

LargeObjectManager largeObjectManager = null;

LargeObject largeObject = null;

Long largeObjectRef = null;

largeObjectManager = ((org.postgresql.PGConnection) connection).getLargeObjectAPI();

largeObjectRef = largeObjectManager.createLO(LargeObjectManager.READ |    LargeObjectManager.WRITE);

largeObject = largeObjectManager.open(largeObjectRef, LargeObjectManager.WRITE);

largeObject.write("string/file content to write into oid column".getBytes());

largeObject.close();

include your rest of code / parameter which needs to be set and along with below,

ps.setLong(2, largeObjectRef);

ps.executeUpdate();