BLOB - zhamri/MyClass-MySQL GitHub Wiki

BLOB = Binary Large Object

Step 1:

CREATE TABLE `stiw2024` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `image` LONGBLOB,
  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Step 2:

INSERT INTO `stiw2024` (`id`,`name`) VALUES (1,'Rahman');
INSERT INTO `stiw2024` (`id`,`name`) VALUES (2,'Razak');

---

Example of writing and reading image in MySQL

WriteImage.java

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class WriteImage {

	public static void main(String[] args) throws Exception {

		Connection myConn = null;
		PreparedStatement myStmt = null;
		FileInputStream input = null;

		try {
			// 1. Get a connection to database
			myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DB_Name", "userid", "password");

			// 2. Prepare statement
			String sql = "update stiw2024 set image=? where name='Razak'";
			myStmt = myConn.prepareStatement(sql);

			// 3. Set parameter for image file name
			File theFile = new File("/Users/zhamricheani/Desktop/Tomcat.png");
			input = new FileInputStream(theFile);
			myStmt.setBinaryStream(1, input);

			// 4. Execute statement
			myStmt.executeUpdate();

			System.out.println("Completed successfully!");

		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			if (input != null) {
				input.close();
			}
			close(myConn, myStmt);
		}
	}

	private static void close(Connection myConn, Statement myStmt) throws SQLException {

		if (myStmt != null) {
			myStmt.close();
		}

		if (myConn != null) {
			myConn.close();
		}
	}
}

ReadImage.java

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ReadImage {

	public static void main(String[] args) throws Exception {

		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;
		InputStream input = null;
		FileOutputStream output = null;

		try {
			// 1. Get a connection to database
			myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DB_Name", "userid", "password");

			// 2. Execute statement
			myStmt = myConn.createStatement();
			String sql = "select image from stiw2024 where name='Razak'";
			myRs = myStmt.executeQuery(sql);

			// 3. Set up a handle to the file
			File theFile = new File("/Users/zhamricheani/Desktop/TomcatDB.png");
			output = new FileOutputStream(theFile);

			if (myRs.next()) {
				input = myRs.getBinaryStream("image");
				byte[] buffer = new byte[1024];
				while (input.read(buffer) > 0) {
					output.write(buffer);
				}

				System.out.println("Saved to file: " + theFile.getAbsolutePath());
				System.out.println("Completed successfully!");
			}

		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			if (input != null) {
				input.close();
			}

			if (output != null) {
				output.close();
			}

			close(myConn, myStmt);
		}
	}

	private static void close(Connection myConn, Statement myStmt) throws SQLException {

		if (myStmt != null) {
			myStmt.close();
		}

		if (myConn != null) {
			myConn.close();
		}
	}
}