{"id":741,"date":"2015-12-24T11:08:59","date_gmt":"2015-12-24T04:08:59","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=741"},"modified":"2022-08-28T15:24:19","modified_gmt":"2022-08-28T08:24:19","slug":"sqlite-java-write-and-read-blob","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-java\/jdbc-read-write-blob\/","title":{"rendered":"SQLite Java: Write and Read BLOB"},"content":{"rendered":"\r\n

Summary<\/strong>: this tutorial shows you step by step how to update and select the BLOB from an SQLite database.<\/p>\r\n\r\n\r\n\r\n

For the demonstration, we will use the materials<\/code> table that we created in the creating table tutorial<\/a>.<\/p>\r\n\r\n\r\n\r\n

Suppose we have to store a picture for each material in the materials<\/code> table. To do this, we add a column whose data type<\/a> is BLOB to the materials<\/code> table.<\/p>\r\n\r\n\r\n\r\n

The following  ALTER TABLE<\/a><\/code> statement adds the picture<\/code> column to the materials<\/code> table.<\/p>\r\n\r\n\r\n

ALTER<\/span> TABLE<\/span> materials ADD<\/span> COLUMN<\/span> picture blob<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Insert BLOB data into SQLite database<\/h2>\r\n\r\n\r\n\r\n
First, query data<\/a> from the materials<\/code> table to view its content:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\tid<\/span>,\r\n\tdescription,\r\n\tpicture\r\nFROM<\/span>\r\n\tmaterials;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
The picture column is NULL<\/code>.<\/p>\r\n\r\n\r\n\r\n
Second, prepare a picture file and place it in a folder e.g., C:\\temp as follows:<\/p>\r\n\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
To update the picture<\/code> column with the data from the picture file:<\/p>\r\n\r\n\r\n\r\n
  1. First, prepare an UPDATE statement<\/a>.<\/li>
  2. Next, connect to the SQLite database<\/a> to get the Connection<\/code> object.<\/li>
  3. Then, create a PreparedStatement<\/code> object from the Connection<\/code> object.<\/li>
  4. After that, supply the values to the corresponding parameters using the set* methods of the PreparedStatement<\/code> object.<\/li>
  5. Finally, execute the UPDATE<\/code> statement by calling the executeUpdate()<\/code> method of the PreparedStatement<\/code> object.<\/li><\/ol>\r\n\r\n\r\n\r\n
    Notice that the SQLiteJDBC driver does not implement the setBinaryStream()<\/code> method, therefore, you must use the setBytes<\/code> method instead.<\/p>\r\n\r\n\r\n\r\n
    The following readFile<\/code> method reads a file and returns an array of bytes that we can pass to the setBytes<\/code> method.<\/p>\r\n\r\n\r\n
       \/**\r\n     * Read the file and returns the byte array\r\n     * @param<\/span> file\r\n     * @return<\/span> the bytes of the file\r\n     *\/<\/span>\r\n    private<\/span> byte<\/span>[] readFile(String file) {\r\n        ByteArrayOutputStream bos = null<\/span>;\r\n        try<\/span> {\r\n            File f = new<\/span> File(file);\r\n            FileInputStream fis = new<\/span> FileInputStream(f);\r\n            byte<\/span>[] buffer = new<\/span> byte<\/span>[1024<\/span>];\r\n            bos = new<\/span> ByteArrayOutputStream();\r\n            for<\/span> (int<\/span> len; (len = fis.read(buffer)) != -1<\/span>;) {\r\n                bos.write(buffer, 0<\/span>, len);\r\n            }\r\n        } catch<\/span> (FileNotFoundException e) {\r\n            System.err.println(e.getMessage());\r\n        } catch<\/span> (IOException e2) {\r\n            System.err.println(e2.getMessage());\r\n        }\r\n        return<\/span> bos != null<\/span> ? bos.toByteArray() : null<\/span>;\r\n    }<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    To connect to the test.db<\/code> database, you use the connect()<\/code> method as follows:<\/p>\r\n\r\n\r\n
        \/**\r\n     * Connect to the test.db database\r\n     *\r\n     * @return<\/span> the Connection object\r\n     *\/<\/span>\r\n    private<\/span> Connection connect<\/span>()<\/span> <\/span>{\r\n        \/\/ SQLite connection string<\/span>\r\n        String url = \"jdbc:sqlite:C:\/\/sqlite\/db\/test.db\"<\/span>;\r\n        Connection conn = null<\/span>;\r\n        try<\/span> {\r\n            conn = DriverManager.getConnection(url);\r\n        } catch<\/span> (SQLException e) {\r\n            System.out.println(e.getMessage());\r\n        }\r\n        return<\/span> conn;\r\n    }<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    The following updatePicture()<\/code> method updates a picture specified by the file name for a particular material.<\/p>\r\n\r\n\r\n
    \/**\r\n     * Update picture for a specific material\r\n     *\r\n     * @param<\/span> materialId\r\n     * @param<\/span> filename\r\n     *\/<\/span>\r\n    public<\/span> void<\/span> updatePicture<\/span>(int<\/span> materialId, String filename)<\/span> <\/span>{\r\n        \/\/ update sql<\/span>\r\n        String updateSQL = \"UPDATE materials \"<\/span>\r\n                + \"SET picture = ? \"<\/span>\r\n                + \"WHERE id=?\"<\/span>;\r\n\r\n        try<\/span> (Connection conn = connect();\r\n                PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {\r\n\r\n            \/\/ set parameters<\/span>\r\n            pstmt.setBytes(1<\/span>, readFile(filename));\r\n            pstmt.setInt(2<\/span>, materialId);\r\n\r\n            pstmt.executeUpdate();\r\n            System.out.println(\"Stored the file in the BLOB column.\"<\/span>);\r\n\r\n        } catch<\/span> (SQLException e) {\r\n            System.out.println(e.getMessage());\r\n        }\r\n    }<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    To update the picture for the material id 1, you use the following code:<\/p>\r\n\r\n\r\n
    package<\/span> net.sqlitetutorial;\r\n\r\nimport<\/span> java.io.ByteArrayOutputStream;\r\nimport<\/span> java.io.File;\r\nimport<\/span> java.io.FileInputStream;\r\nimport<\/span> java.io.FileNotFoundException;\r\nimport<\/span> java.io.FileOutputStream;\r\nimport<\/span> java.io.IOException;\r\nimport<\/span> java.io.InputStream;\r\nimport<\/span> java.sql.Connection;\r\nimport<\/span> java.sql.DriverManager;\r\nimport<\/span> java.sql.PreparedStatement;\r\nimport<\/span> java.sql.ResultSet;\r\nimport<\/span> java.sql.SQLException;\r\n\r\n\/**\r\n *\r\n * @author<\/span> sqlitetutorial.net\r\n *\/<\/span>\r\npublic<\/span> class<\/span> BLOBApp<\/span> <\/span>{\r\n    private<\/span> Connection connect<\/span>()<\/span> <\/span>{\r\n        \/\/...<\/span>\r\n    }\r\n \r\n    private<\/span> byte<\/span>[] readFile(String file) {\r\n       \/\/...<\/span>\r\n    }\r\n   \r\n    public<\/span> void<\/span> updatePicture<\/span>(int<\/span> materialId, String filename)<\/span> <\/span>{\r\n        \/\/...<\/span>\r\n    }\r\n\r\n     \/**\r\n     * @param<\/span> args the command line arguments\r\n     *\/<\/span>\r\n    public<\/span> static<\/span> void<\/span> main<\/span>(String[] args)<\/span> <\/span>{\r\n        BLOBApp app = new<\/span> BLOBApp();\r\n        app.updatePicture(1<\/span>, \"c:\\\\temp\\\\HP_Laptop.jpg\"<\/span>);\r\n    }\r\n}\r\n<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Note that we didn’t repeat the body of the methods that we already metioned.<\/p>\r\n\r\n\r\n\r\n
    Let’s execute the program and check the materials<\/code> table again.<\/p>\r\n\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    It works as expected.<\/p>\r\n\r\n\r\n\r\n
    Query BLOB data from SQLite database<\/h2>\r\n\r\n\r\n\r\n
    The following steps show you how to query BLOB data from the SQLite database:<\/p>\r\n\r\n\r\n\r\n
    1. First, prepare a SELECT<\/a> statement.<\/li>
    2. Next, create a Connection<\/code> object by connecting to the SQLite database.<\/li>
    3. Then, create an instance of the PreparedStatement<\/code> class from the Connection<\/code> object. Use the set* method of the PreparedStatement<\/code> object to supply values for the parameters.<\/li>
    4. After that, call the executeQuery<\/code> method of the PreparedStatement<\/code> object to get the ResultSet<\/code> object.<\/li>
    5. Finally, loop through the result set, use the getBinaryStream()<\/code> method to get data, and use the FileOutputStream<\/code> object to save data into a file.<\/li><\/ol>\r\n\r\n\r\n\r\n
      The following readPicture<\/code> method selects the BLOB data of a specific material and saves it to a file.<\/p>\r\n\r\n\r\n
      \/**\r\n     * read the picture file and insert into the material master table\r\n     *\r\n     * @param<\/span> materialId\r\n     * @param<\/span> filename\r\n     *\/<\/span>\r\n    public<\/span> void<\/span> readPicture<\/span>(int<\/span> materialId, String filename)<\/span> <\/span>{\r\n        \/\/ update sql<\/span>\r\n        String selectSQL = \"SELECT picture FROM materials WHERE id=?\"<\/span>;\r\n        ResultSet rs = null<\/span>;\r\n        FileOutputStream fos = null<\/span>;\r\n        Connection conn = null<\/span>;\r\n        PreparedStatement pstmt = null<\/span>;\r\n\r\n        try<\/span> {\r\n            conn = connect();\r\n            pstmt = conn.prepareStatement(selectSQL);\r\n            pstmt.setInt(1<\/span>, materialId);\r\n            rs = pstmt.executeQuery();\r\n\r\n            \/\/ write binary stream into file<\/span>\r\n            File file = new<\/span> File(filename);\r\n            fos = new<\/span> FileOutputStream(file);\r\n\r\n            System.out.println(\"Writing BLOB to file \"<\/span> + file.getAbsolutePath());\r\n            while<\/span> (rs.next()) {\r\n                InputStream input = rs.getBinaryStream(\"picture\"<\/span>);\r\n                byte<\/span>[] buffer = new<\/span> byte<\/span>[1024<\/span>];\r\n                while<\/span> (input.read(buffer) > 0<\/span>) {\r\n                    fos.write(buffer);\r\n                }\r\n            }\r\n        } catch<\/span> (SQLException | IOException e) {\r\n            System.out.println(e.getMessage());\r\n        } finally<\/span> {\r\n            try<\/span> {\r\n                if<\/span> (rs != null<\/span>) {\r\n                    rs.close();\r\n                }\r\n                if<\/span> (pstmt != null<\/span>) {\r\n                    pstmt.close();\r\n                }\r\n\r\n                if<\/span> (conn != null<\/span>) {\r\n                    conn.close();\r\n                }\r\n                if<\/span> (fos != null<\/span>) {\r\n                    fos.close();\r\n                }\r\n\r\n            } catch<\/span> (SQLException | IOException e) {\r\n                System.out.println(e.getMessage());\r\n            }\r\n        }\r\n    }<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
      For example, we can use the readPicture()<\/code> method to get the BLOB data that we updated for the material id 1 and save it into a file named HP_Laptop_From_BLOB.jpg<\/code> file.<\/p>\r\n\r\n\r\n
      app.readPicture(1<\/span>, \"c:\\\\temp\\\\HP_Laptop_BLOB.jpg\"<\/span>);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
      We execute the program and check the c:\\\\temp<\/code> folder:<\/p>\r\n\r\n\r\n\r\n
      \"SQLite<\/figure>\r\n\r\n\r\n\r\n
      In this tutorial, you have learned how to update and select BLOB data in the SQLite database using SQLite JDBC driver.<\/p>\r\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t