Groovy blob and mysql
Warning: count(): Parameter must be an array or an object that implements Countable in /www/webvol14/yj/kljuhyzt1cwiiug/expertaya.com/public_html/wp-content/plugins/wp-syntax/wp-syntax.php on line 76
In certain occasions e.g. when you want to save scrapped html page or just a specific part of it (e.g. div with its contents) the recommendation I found is to use the blob type and save xml document as binary stream.
In groovy, this is achieved in a rather simple way:
class Document { int id String title String xmlContent } Sql sql = Sql.newInstance( "jdbc:mysql://localhost:3306/documents_db?useUnicode=true&characterEncoding=UTF-8&useBlobToStoreUTF8OutsideBMP=true", "user", "pass", "com.mysql.jdbc.Driver") // We assume db table similar to: sql.execute(""" CREATE TABLE IF NOT EXISTS `document` ( `id` bigint(32) NOT NULL AUTO_INCREMENT, `title` varchar(500) NOT NULL, `xmlContent` blob, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; """) def saveDocuments(def docs) { println "Saving documents to database..." def documents = sql.dataSet("document") docs.each {Document doc -> documents.add(title: doc.title, xmlContent: doc.xmlContent) } }
When you want to retrieve data, with Groovy this is not much complicated either:
def loadDocuments() { def list = [] sql.eachRow("SELECT * FROM document") { Document doc = new Document(it.toRowResult()) if (it['xmlContent'] != null) { doc.xmlContent = new String(it['xmlContent']) } list.add(doc) } println "Loaded ${list.size()} documents from database" list }
I tried the code with mysql database but I assume that you can achieve the same with any other database.
One hint for mysql blobs and utf-8: although utf-8 charset behaves very well with other database table fields (if you add useUnicode=true and characterEncoding=UTF-8 parameters to your connection string), in order to work with utf-8 blob fields you should adjust one more parameter: useBlobToStoreUTF8OutsideBMP=true. With this parameter set, your utf8 encoded xml documents will behave well with mysql database.
