一、场景

业务上某些场景需要将对象存入数据库中，处理方式为：将对象转为字节数组存储，读取时再将字节数组转为对象；

模拟的表结构如下：

create table resource(
	fid int primary key not null auto_increment,
	fname varchar(255),
	fbytes longtext
) charset=utf8;

以User对象为例：

public class User implements Serializable{

	private static final long serialVersionUID = 3125265779564966137L;
	
	private String name;
	private int age;

	public User(String name, int age) {
		super();
		this.name = name;
		this.age = age;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return String.format("User(%s, %s)", this.name, this.age);
	}
}

存储User对象：

User user = new User("albert", 30);

//将User对象转为字节数组
byte[] bytes = null;
try(ByteArrayOutputStream baos = new ByteArrayOutputStream()){
	try(ObjectOutputStream oos = new ObjectOutputStream(baos)){
		oos.writeObject(user);
		bytes = baos.toByteArray();
	}
}

//将数据存入数据库中
try(Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", username, password)){
	try(PreparedStatement statement = connection.prepareStatement("insert into resource(fname, fbytes) values(?,?);")){
		statement.setString(1, "A-001");
		statement.setBinaryStream(2, new ByteArrayInputStream(bytes), bytes.length);
		statement.execute();
	}
}

查询：

//从数据库中查询User对象数据
......

//将字节数组转为User对象
Object deserializedObject = null;
try(ByteArrayInputStream bais = new ByteArrayInputStream(bytes)){
	try(ObjectInputStream ois = new ObjectInputStream(bais)){
		deserializedObject = ois.readObject();
	}
}
System.out.println(deserializedObject);

在插入对象字节数组时报错java.sql.SQLException: Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'fbytes' at row 1：

Exception in thread "main" java.sql.SQLException: Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'fbytes' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995)
	at com.mysql.CharsetBinaryTest.main(CharsetBinaryTest.java:31)

二、解决方式

修改表字段类型，fbytes字段应该使用longblob类型：

ALTER TABLE resource MODIFY fbytes longblob;
其他：经测试，不修改字段类型，如果使用默认的字符集Latin1也可以处理这个问题（前提是此字符集满足业务需求）。ALTER TABLE resource MODIFY fbytes longtext CHARACTER SET latin1 COLLATE latin1_swedish_ci;
参考资料：