As java persistence api, we only need two abstract classes
1 AbstractTable<EntityBean>
2 AbstractQuery<ConditionBean, EntityBean>
AbstractTable class is all the base DAO class for single table operations, insert/update/delete/select/pagination etc.
and AbstractQuery process all query related functions,
for example:
SELECT
ab.*
FROM
(
SELECT
DISTINCT bb.pre_project_id,
bb.quotation_status,
project_code,
info_code,
project_name,
project_status,
design_status,
project_origination,
project_origination_remark,
province,
city,
detail_address,
info_register_time,
customer_name,
customer_address,
project_sales_team,
project_level,
business_type,
sign_company,
design_qualification,
project_base_on,
project_approval_time,
design_team,
design_cooperation_name,
is_high_rise_building,
project_total_investment,
total_building_areas,
fire_resistance_rating,
estimate_the_design_areas,
actual_design_areas,
building_height,
building_floors,
decorate_money_control,
architectural_design_units,
A.process_status,
contact_name,
contact_phone,
product_line,
bb.apply_employee_id,
e.plate_id,
e.employee_name,
bb.status,
bb.project_type,
bb.client_id,
bb.comparison_form,
bb.region,
bb.win_rate,
bb.remark,
bb.close_reason,
bb.close_remark,
bb.grade,
bb.is_original,
bb.is_sub_project,
bb.days,
bb.register_date,
bb.specialty,
CASE
WHEN bb.estimate_price IS NULL THEN 0
ELSE bb.estimate_price
END AS estimate_price,
CASE
WHEN bb.status = 5 OR bb.status = 2 OR bb.contract_status = 3 OR bb.project_status = '暂停' OR bb.project_status = '已完成' THEN 5
WHEN bb.days > 15 THEN 4
WHEN bb.days > 10 THEN 1
WHEN bb.days > 5 THEN 2
ELSE 3
END AS FLAG,
CASE
WHEN bb.status = 5 OR bb.status = 2 OR bb.contract_status = 3 OR bb.project_status = '暂停' OR bb.project_status = '已完成' THEN 'project_warn_grey.png'
WHEN bb.days > 15 THEN 'project_warn_black.png'
WHEN bb.days > 10 THEN 'project_warn_red.png'
WHEN bb.days > 5 THEN 'project_warn_yellow.png'
ELSE 'project_warn_green.png'
END AS warn_img,
bb.is_publicly_bidding,
bb.have_agents,
bb.create_time,
e.department_id,
bb.original_apply_employee,
bb.auto_transfer,
bb.forced_transfer
FROM
(
SELECT
aa.*,
ppr.register_date,
qtt.process_status AS quotation_status,
CASE
WHEN aa.info_register_time IS NULL THEN DATE_PART('day',NOW() - aa.create_time)
ELSE
CASE
WHEN ppr.register_date IS NULL THEN DATE_PART('day',NOW() - aa.info_register_time)
ELSE DATE_PART('day',NOW() - ppr.register_date)
END
END AS days
FROM
pre_projects aa
LEFT JOIN (
SELECT
pre_project_id,
MAX(register_date) AS register_date
FROM
pre_project_records
WHERE
information_type = 1
GROUP BY
pre_project_id
) ppr ON aa.pre_project_id = ppr.pre_project_id
LEFT JOIN (
SELECT
spi.process_status,
pb.*
FROM
(
SELECT
pb1.*,
pb2.personnel_business_id
FROM
(
SELECT
project_id,
MAX(create_time) AS create_time
FROM
personnel_business
WHERE
process_type = 61
GROUP BY
project_id
) pb1
LEFT JOIN personnel_business pb2 ON pb2.project_id = pb1.project_id
AND pb2.create_time = pb1.create_time
) pb
LEFT JOIN system_process_instances spi ON pb.personnel_business_id = spi.business_id
) qtt ON qtt.project_id = aa.pre_project_id
) bb
LEFT JOIN system_process_instances A ON bb.pre_project_id = A.business_id
AND A.process_type = 42
LEFT JOIN employees e ON bb.apply_employee_id = e.employee_id
LEFT JOIN main_project_employees mpe ON bb.pre_project_id = mpe.pre_project_id
LEFT JOIN (
SELECT
DISTINCT UNNEST(STRING_TO_ARRAY(relevant_employees,',')) AS employee_id,
pre_project_id
FROM
pre_project_records
WHERE
information_type = 1
) ppr ON bb.pre_project_id = ppr.pre_project_id
WHERE
A.process_status = :process_status
AND 2 = 2
AND product_line = :product_line
AND bb.info_register_time BETWEEN :start_data AND :end_data
AND bb.project_status = :project_status
AND bb.project_name LIKE :project_name
AND bb.status = :status
AND bb.customer_name LIKE :customer_name
AND e.department_id IN (
SELECT
child_id
FROM
department_ids
WHERE
department_id = :department_id
)
AND bb.project_level = :project_level
AND CAST(bb.win_rate AS INTEGER) > :win_rate
AND bb.is_marketing_department_project = :is_marketing_department_project
AND (mpe.employee_id = :employee_id
OR bb.original_apply_employee = :original_apply_employee
OR bb.apply_employee_id = :apply_employee_id
OR bb.drafter = :drafter)
AND bb.project_code LIKE :project_code
AND bb.info_code LIKE :info_code
) ab
ORDER BY
ab.FLAG ASC,
ab.pre_project_id DESC
There are so many condition in this select SQL
ConditionBean will wrap all condition of the SQL,
AbstractQuery will provide a executeQuery method to execute this SQL according to ConditionBean
AbstractQuery will parse the SQL into tree data structure and rewrite the SQL according to the parsed tree and ConditionBean
Programmers know how to write a sql and how to debug a sql well
with this function the programmers will not care about how to execute the SQL
For your refenerce.
Regard.
Peter
