Explain a piece of SQL? Or a code segment ?, SQL code

Explain a piece of SQL? Or a code segment ?, SQL code

I remember that my R & D colleagues who just entered the company could write beautiful SQL statements, and search was accurate, fast to execute, and highly efficient.

To meet the requirement of querying and displaying data in a Web project, the task is completed in minutes.

During that time, I basically looked up and asked every day to learn the key points of handwriting SQL and look at some SQL optimization and adjustment techniques.

With the accumulation and practice, SQL level improved quickly, but also wrote a lot, interested can look at: http://www.cnblogs.com/

Then, after polishing several projects, we constantly adjusted the company's framework and found that the probability of SQL statements in the project was getting smaller and smaller.

I had to stop and start to reflect on and summarize the reasons for this phenomenon. If you are not busy and interested, please let me know.

The following is a classic system permission database design, which is discussed as an example.

The organization, user, role, and menu are the four major design objects. Three ing tables are added.

It can be well scaled horizontally and vertically. I only add a few required fields to the main design object.

This design can be completely introduced into your project, and necessary fields can be added according to the actual user group and needs of the project.

Then, we can work with Shiro or Spring-Security to perfectly solve the permissions of the User Role menu in the Organization.

To put it bluntly, there is a requirement in the project requirement that all menu items of the current user need to be pushed and written in SQL.

Select. uuid,. name from menu a left join role_menu B on. uuid = B. menuid left join role_user c on B. roleid = c. roleid where c. userid = 'user uid ';

You need to execute it in the database, paste it into your code, and use the data access object to go to the database and execute this SQL to obtain data.

The following describes the object-oriented code logic with the same logic.

RoleUserPO roleUserPO = roleService. findUserRoleByUserId ("User ID"); if (roleUserPO = null) {return "the current user has not set a role! ";}List <RoleMenuPO> roleMenuPOs = roleService. findRoleMenusByRoleId (roleUserPO. getRoleid (); if! ";}List <MenuPO> menuPOLis = new ArrayList <MenuPO> (); for (RoleMenuPO roleMenuPO: roleMenuPOs) {menuPOLis. add (menuService. findMenuById (roleMenuPO. getMenuid ();} return menuPOLis;

In the above example, do you feel like a comparison? If it is not strong enough, please look down.

There is also a requirement in the project requirement that users with specific roles under specific departments should be listed and written in SQL.

select a.*  from user a  LEFT JOIN role_user b    on a.UUID = b.userid  LEFT JOIN orga_user c    on a.uuid = c.userid where b.ROLEID = 'c9845b33973511e6acede16e8241c0fe'   and c.ORGAID = '75284c22973211e6acede16e8241c0fe'

Object-oriented code logic with the same logic in the same segment.

List <UserPO> userPO1s = roleService. findUsersByRoleId ("role ID"); if (userPO1s = null) {return "no user is added to the current role! ";}List <UserPO> userPO2s = orgaService. findUsersByOrgaId (" organization ID "); if (userPO2s = null) {return" no user is added to the current organization! ";}List <UserPO> userPOList = new ArrayList <UserPO> (); for (UserPO userPO1: userPO1s) {for (UserPO userPO2: userPO2s) {if (userPO1.getUuid (). equals (userPO2.getUuid () {userPOList. add (userPO1); break ;}} return userPOList;

Do you feel that the logic of the object code is not only easy to read, but also clearly shows the cause of the error.

In addition, the mainstream databases are relational-oriented, and the programming language has evolved from process-oriented to object-oriented.

That is to say, the two are totally different, that is, the reason why the ORM framework is growing. In programming, data tables must be treated and processed as objects.

The large SQL section in the Code is totally different from the object-oriented design idea.

If a problem occurs in the query SQL statement, paste the SQL statement printed in the background to the SQL Execution tool for execution, analyze the cause, and cut the two tools. Do you think it is difficult?

This should be the project I will be using later. The main reason for SQL reduction is that we like to program on an object-oriented channel.

Okay, that's it. All of the above are my personal thoughts and conclusions. If you have different opinions, please be prepared.