MYSQL stored procedure accessing java object stored as a BLOB

I am storing a Java object as an byte in a blob of a table. The java object is customized object. How开发者_如何学运维 can I construct the java object and use it in the stored procedure?

---

Let the class implement java.io.Serializable so that you can get an InputStream of it which you can store in the DB using CallableStatement#setBinaryStream().

That said, this is usually considered a bad design. If the class is actually a Javabean class, you'd better create a table with columns which represents the Javabean properties. E.g. a public class User { private Long id; private String name; private Integer age; } should be mapped to a table like CREATE TABLE user ( id BIGINT AUTO_INCREMENT, name VARCHAR, age INTEGER )

Edit as a reply on your comment: you thus basically want to store an array as binary object. This is a very bad idea. This way you cannot search for the array's data in the database and the database would also not be portable anymore. Just create a new table which represents each of the array items. Add an extra column to it which represents the ID of the parent object (actually, it should be the PK of the table to which the parent object containing the array is been mapped.

Example:

public class Parent {
    private Long id;
    private String someData;
    private List<Child> children;
    // Add/generate public getters/setters.
}

public class Child {
    private Long id;
    private String someData;
    // Add/generate public getters/setters.
}

should be mapped to

CREATE TABLE parent (
    id BIGINT NOT NULL AUTO_INCREMENT,
    someData VARCHAR,

    PRIMARY KEY (id)
);

CREATE TABLE child (
    id BIGINT NOT NULL AUTO_INCREMENT,
    parent_id BIGINT NOT NULL,
    someData VARCHAR,

    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

this way you can just select all with help of a JOIN clause. Check the SQL tutorial at w3schools.com and the vendor-specific SQL documentation for examples.

---

How can I construct the java object and use it in the stored procedure?

This is not possible, at least not with MySQL. Unlike Oracle which supports Java Stored Procedures, MySQL's stored procedure syntax is based on plain ANSI SQL standard. So I don't see how you could construct a Java object from the stream stored in the BLOB. What you can do is acces to the BLOB, but this won't help you much IMHO.

Actually, I think you are totally on the wrong path here, using a BLOB is not the right way to go (at least not here). If you need to persist objects that have a 1:n relation between them, you need to model your database accordingly.

If your Record class has a one to many relation with the User class, which is my understanding, then you have something like this on the Java side:

public class Record {
    private Long id;
    private User[];
    //...
}

Then you need to create two tables at the database level, one for the records and another for the user(s), and model the relation between them using a foreign key (so you can "attach" a user to a record):

CREATE TABLE record
(
    record_id INT NOT NULL,
    ...,
    PRIMARY KEY (record_id)
) TYPE = INNODB;

CREATE TABLE user
(
    user_id INT NOT NULL,
    record_id INT,
    ...
    PRIMARY KEY (user_id),
    INDEX (record_id),
    FOREIGN KEY (user_id) REFERENCES record (record_id) 
) TYPE = INNODB;

Finally, when persisting a Record instance from Java, you'll need to write state in both tables.