Using spring JdbcTemplate to implement CLOB and BLOB access

Overview

This paper describes the implementation of large-text reading and writing of database through Spring JdbcTemplate, based on a blog of a netizen, review the API documentation.

Write implementation

1JdbcTemplate JdbcTemplate =NewJdbcTemplate (DataSource);//Reusable Object2Lobhandler Lobhandler =NewDefaultlobhandler ();//Reusable Object3 4 Jdbctemplate.execute (5"INSERT into Imagedb (image_name, Content, description) VALUES (?,?,?)",6      NewAbstractlobcreatingpreparedstatementcallback (lobhandler) {7        protected voidSetvalues (PreparedStatement PS, Lobcreator lobcreator)throwsSQLException {8Ps.setstring (1, name);9Lobcreator.setblobasbinarystream (PS, 2, Contentstream, contentlength);TenLobcreator.setclobasstring (PS, 3, description); One        } A      } -);

Class Introduction

The following content does not seek precision, to add their own understanding and speculation, not for academic purposes.

• JdbcTemplate
  Responsible for translating, providing executable instructions to the JDBC interface
• Abstractlobcreatingpreparedstatementcallback
  Responsible for providing a method template for parameter settings and providing Lobcreator instances
• Lobhandler
  Responsible for providing Lobcreator-abstractlobcreatingpreparedstatementcallback with the help of it to provide lobcreator
• Lobcreator
  Responsible for simplifying the writing of large print segments

Lobcreator API

Lobcreator provides multiple interfaces to simplify the writing of large segments: for blobs, the content source can be InputStream, byte[], and for CLOB, the content source can be inputstream (self-assured ASCII characters), Reader, String.

Read implementation

1JdbcTemplate JdbcTemplate =NewJdbcTemplate (DataSource);//Reusable Object2  FinalLobhandler Lobhandler =NewDefaultlobhandler ();//Reusable Object3 4 Jdbctemplate.query (5"Select content from Imagedb WHERE image_name=?",Newobject[] {name},6                  NewAbstractlobstreamingresultsetextractor () {7                           Public voidStreamdata (ResultSet rs)throwsSQLException, IOException {8Filecopyutils.copy (Lobhandler.getblobasbinarystream (RS, 1), contentstream);9                          }Ten                  } One);

Bulk Read

The above implementation is only for a record of the read, if you want to read multiple records, you need to note that the ResultSet instance has pointed to the first record (the above code also does not call Rs.next ()), can refer to the implementation:

1  Publiclist<dfcl> batchread (String sql,FinalString Colnamefilename,2         FinalString colnamefilecontent) {3JdbcTemplate JT =jdbcservice.getinstance (). Getjdbctemplate ();4      5     FinalLobhandler Lobhandler =NewDefaultlobhandler ();6     FinalList<dfcl> dfcllist =NewArraylist<dfcl>();7Jt.query (SQL,NewAbstractlobstreamingresultsetextractor () {8         protected voidStreamdata (ResultSet rs)throwsSQLException,9 IOException, DataAccessException {Ten              Do{//sinobest File Download, here the RS initialization is already pointing to the first record OneString name =rs.getstring (colnamefilename); AString content =lobhandler.getclobasstring (RS, colnamefilecontent); -                  -DFCL cl =NewDFCL (name, content); the Dfcllist.add (CL); -} while(Rs.next ());  -         } -     }); +     returndfcllist; -}

Class Introduction

The following content does not seek precision, to add their own understanding and speculation, not for academic purposes.

• JdbcTemplate
  Ditto
• Abstractlobstreamingresultsetextractor
  Provides a method template for parsing data and provides a ResultSet object instance
• Lobhandler
  Simplified reading of large print segments
• Filecopyutils
  Spring provides a tool class that simplifies the transfer of file content

Lobhandler API

Lobhandler provides multiple interfaces to simplify the reading of large fields: for blobs, it can be read as InputStream, byte[], and for CLOB, it can be read as InputStream, Reader, String. and the write simplification of the lobcreator is corresponding.

Filecopyutils API

Using spring JdbcTemplate to implement CLOB and BLOB access