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())));

Why don't you use bytea as the data type of the column instead? It's a much better choice for a "BLOB" column - especially when using JDBC. — user330315
– user330315, Commented Nov 14, 2014 at 10:43
I asked my DBA he is saying if we port to other databases OID is better choice than bytea,so that is reason he created columns type as OID — user3365200
– user3365200, Commented Nov 14, 2014 at 10:45
Your DBA is wrong. "Large Objects" (ala "oid") require a completely different programming model to read and write. bytea column can be accessed through JDBC's BLOB API the same way you would access a BLOB column in Oracle or a varbinary column in SQL Server. No Java framework that I know of supports Postgres' large objects, but most of them support bytea. — user330315
– user330315, Commented Nov 14, 2014 at 10:46

RBB · Accepted Answer · 2015-12-07 09:01:26Z

1

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();

answered Dec 7, 2015 at 9:01

RBB

8742 gold badges13 silver badges22 bronze badges

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

Comments

Stu Thompson · Accepted Answer · 2015-04-30 22:49:06Z

0

I have changed column type to bytea now its working

edited Apr 30, 2015 at 22:49

Stu Thompson

39k19 gold badges112 silver badges156 bronze badges

answered Dec 1, 2014 at 6:08

user3365200

2,3112 gold badges16 silver badges13 bronze badges

1 Comment

Spider Over a year ago

May i ask how did you change oid type to bytea?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related