I am able to insert the encoded file in my Postgres database properly when I am using @lob for encoded file while mapping my entity with database and using save() method to insert the data

@Lob
@Column(name="data", columnDefinition = "text")
private String fileContent;

When I am trying to insert the same encoded file using insert native query it is not inserting properly.

@Override
@Transactional
public void insertEncodeData(CustomerData customer) {
   entityManager.createNativeQuery("insert into customer (name,data) VALUES (?,?)")
                .setParameter(1, customer.getName())
                .setParameter(2, customer.getData())
                .executeUpdate();
    }

The encoded base 64 file is not storing in lob format, it is consider as simple string. Can anyone help me this?

Try 1

@Override
@Transactional
public void insertEncodeData(CustomerData customer) {
  Clob lob = entityManager.unwrap(Session.class).getLobHelper().createClob(customer.getData());
   entityManager.createNativeQuery("insert into customer (name,data) VALUES (?,?)")
                .setParameter(1, customer.getName())
                .setParameter(2, lob)
                .executeUpdate();
    }

I had converted the encoded file into clob and tried to insert it but still not working.

Thank you in advance!

CodePudding user response：

To insert a file using native query used the following approach:

1. Create a bean for your JdbcTemplate

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

2. Make use of JdbcTemplate and preparedStatement to insert the data

 @Autowired
 private JdbcTemplate jdbcTemplate;

@Override
@Transactional
public void insertEncodeData(CustomerData customer) {
  String insertStatement = "insert into customer (name,data) VALUES (?,?)";

        jdbcTemplate.update(insertStatement, ps -> {
            
            ps.setString(1,customer.getName());
            ps.setClob(2,org.hibernate.engine.jdbc.ClobProxy.generateProxy(customer.getContent()));
        });
 
}

The given approach insert the encoded file properly using native query.