Как выбрать одиночный mysql для таблиц соединения с иерархическим фрагментом json?

Каким будет лучший и самый элегантный способ получить из одного запроса выбора MySQL всю информацию из двух соединенных таблиц, но иерархически?

У меня есть эти две таблицы:

-----------------         ------------------
| Table COMPANY |         | Table EMPLOYEE |
-----------------         ------------------
| id            |         | id             |
| companyName   |         | companyId      |
-----------------         | employeeName   |
                          ------------------

(for each company, many employees)

и я хочу вывести следующий иерархический фрагмент JSON:

[
    {"id": 1,
     "companyName": "Company A",
     "employees": [
         {"id": 1, "employeeName": "Employee 1"},
         {"id": 2, "employeeName": "Employee 2"}
    ]},
    {"id": 2,
     "companyName": "Company B",
     "employees": [
         {"id": 3, "employeeName": "Employee 3"},
         {"id": 4, "employeeName": "Employee 4"}
    ]}
]

«Решение» 1:

Сделайте полный выбор в соединенных таблицах и напишите код для создания фрагмента json:

select * from company, employee where employee.companyId = company.id;

Проблема: у меня осталось много уродливого кода типа foreach для создания фрагмента json.

«Решение» 2:

Сгруппировать по компании всех сотрудников в строку json:

select company.id, company.name,
   concat('[',
      group_concat('{"id": ', employee.id, ',
         "employeeName": "', employee.employeeName,'"}'), ']') as employees
from company, employee where company.id = employee.companyId
group by company.id

Здесь «сотрудники» — это уже фрагмент json, мне нужно только jsonify company.id и имя. Но создание фрагмента json в mysql кажется очень плохой практикой.

Любое понимание или другое решение?

Большое спасибо


mysql json sql hierarchical
person Ludo Ma Non Troppo    schedule 16.10.2012    source источник
comment
У меня осталось много уродливого кода типа foreach для создания фрагмента json. Какой язык программирования вы используете для преобразования результата MySQL в JSON?   -  person Carlos Quijano    schedule 26.10.2012
comment
Я использую java с Apache Common DbUtils для доступа к базе данных и Google Gson для JSON.   -  person Ludo Ma Non Troppo    schedule 26.10.2012

Ответы (2)


arrow_upward
2
arrow_downward

Я бы, вероятно, использовал запрос, подобный вашему решению 1, просто добавьте «сортировку по компании.id», а затем выполните один цикл по таблице, создав структуру данных для каждой строки, если компания такая же, как и предыдущая во-первых, вставьте данные о сотрудниках в компанию, в противном случае определите новую компанию и начните использовать ее для ввода данных о сотрудниках.

Как и Карлос Кихано, я не вижу здесь никакого беспорядка foreach. (но сортировка по упрощает работу с ней)

person MortenSickel    schedule 30.10.2012

arrow_upward
1
arrow_downward

Поскольку вам необходимо выполнить один запрос MySQL и не хотите возиться с уродливым кодом для каждого типа, вам, вероятно, следует вернуть no- иерархический результат JSON, вы можете сделать свой код более элегантным, сериализовав результаты запроса в коллекцию с помощью запроса DbUtils.

Запуск QueryRunner = новый QueryRunner (источник данных);

// Use the BeanListHandler implementation to convert all
// ResultSet rows into a List of Employees JavaBeans.
ResultSetHandler<List<Employee>> handler = new BeanListHandler<Employee>(Employee.class);

// Execute the SQL statement and return the results in a List of
// Person objects generated by the BeanListHandler.
List<Employee> employees = run.query("SELECT * FROM EMPLOYEE ORDER BY COMPANY_ID", handler);

Но если вам необходимо сгенерировать иерархический результат JSON, я согласен с приведенным ниже ответом MortenSickel, уменьшить эту коллекцию с помощью типизированного foreach и, наконец, закодировать JSON с помощью GSON, я думаю, что в этом нет абсолютно ничего плохого.

person Carlos Quijano    schedule 20.11.2012