Joining Objects in MySQL Database

Question

I have stored objects in a database using tables, I retrieve data from objects in following way:

String query = "SELECT * FROM Librarian WHERE (id = ?)";
    Account ac = null;
    try {
        state = conn.prepareStatement(query);
        state.setString(1,passport);
        rs = state.executeQuery();

        if(rs.next()) {
            ByteArrayInputStream bais = new ByteArrayInputStream(rs.getBytes("Object"));
            try {
                ObjectInputStream ois = new ObjectInputStream(bais);
                ac = (Account) ois.readObject();

and then I can access values e.g ac.getName etc.. But this gives me only one object. In a table(given below) I have stored 3 foreign keys and I want to join 3 tables to get information from them, but each table has Object i.e I want to retrieve information from those Objects, hence I want to join tables and get information from objects.

 CREATE TABLE Orders
 (OrderID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 Passport VARCHAR(20) NOT NULL,
 FOREIGN KEY (Passport) REFERENCES Guest(Passport) ON DELETE CASCADE,
 ISBN INT NOT NULL,
 FOREIGN KEY (ISBN) REFERENCES Books(ISBN) ON DELETE CASCADE,
 LibPassport VARCHAR(20) NOT NULL,
 FOREIGN KEY (LibPassport) REFERENCES Account(LibPassport) ON DELETE CASCADE,
 Object LONGBLOB NOT NULL
 );

If you need more explanations or code please let me know.

Regards,

Why do you save serialized objects into the database and not the attributes in different columns? To your question, if I understand you correctly, you need aliases... — Meiko Rachimow, Apr 03 '16 at 13:27
I have objects not attributes because I was following objects oriented techniques. Basically this is my uni assignment and I was asked to store object and retrieve object. — nix, Apr 04 '16 at 04:51

score 0 · Answer 1 · answered Apr 03 '16 at 13:34

I must be missing something...

String query = "SELECT * 
                FROM ORDERS O 
                INNER JOIN GUEST G 
                   on O.Passport = G.Passport 
                INNER JOIN Books B 
                   on B.ISBN = O.ISBN 
                WHERE  (O.OrderID = ?)";

Assuming you only want records were all the keys match.

If you want all records from one table, and only those matching in others, then you have to use LEFT, Right or Full outer joins (later of which isn't supported in mySQL, so you have to use a left and right and a union)

score 0 · Answer 2 · edited May 23 '17 at 12:07

You have to use aliases for the columns you specify in the SELECT part. your query should look like this:

SELECT
    o.Id,
    o.Whatever,
    p.ID,
    p.Name,
    ...
    p.Object AS Passport_Object,
    b.ID,
    b.Name,
    ...
    b.Object AS Books_Object,
    a.ID,
    ...
    a.Object AS Account_Object
FROM
    Orders AS o
JOIN
    Passport AS p ON ...
JOIN
    Books AS b ON ...
JOIN
    Account AS a ON ...
WHERE
    -- some condition here

Do not use SELECT * in your queries, see the question What is the reason not to use select *?.

Joining Objects in MySQL Database

2 Answers2