Disclosure of data access middleware Das of Xinye Technology

Time:2020-5-26

Introduction to Das

DAS is a database access middleware developed by Xinye technology. It is an integrated relational database access solution that integrates database management, ORM, dynamic SQL construction and sub database and sub table support.

See here, you will say young! There are so many ORM frameworks and sub database and sub table components, such as hibernate, mybatis, MYCAT, sharding JDBC, and our favorite Ctrip DAL to choose from. Why do we need to build wheels again?

The answer is very simple, these tools are not easy to use! DAS is our latest high-tech wheel.

Warning! High energy ahead! Non senior developers should be evacuated as soon as possible. Please hold the mouse and the keyboard!

Das product positioning

You must wonder, since you want to do database middleware, why not start from JDBC or database protocol layer like other products, do database table splitting or redevelop database engine on traditional database? How awesome that is! Existing programs can be transplanted seamlessly without modification. Why does Das provide ORM?

Before answering this question, let’s briefly review the popular database programming process. This helps to understand Das’s product positioning.

Mybatis and Hibernate didn’t have the concept of vertical and horizontal expansion of database, such as sub database and sub table, which will not be considered in design. And now any Internet company, every day the data is a day. Therefore, a serious database project often uses ORM tools and sub database sub table components at the same time. No matter ORM or sub database and sub table components, they usually need tedious configuration. The only difference is whether the difficulty level is acceptable or dissuasive.

Take the most popular mybatis + any database and table component as an example. If you are a senior crud boy, you must be very familiar with the following routine. Before you start to write the actual Dao code in the middle of the figure below, you need to do four other things first:

Disclosure of data access middleware Das of Xinye Technology

When you do these configurations in a hurry, the first test will not succeed in nine out of ten. Don’t be discouraged at this time, because the worse is still to come. When you use the independent ORM and sub database sub table components in the project, you will be sad to tears:

  • Have you ever written code in XML? This is how mybatis stores table structures and dynamic SQL statements in the form of XML, which is separated from the Java code used for actual debugging. Please tell me how to debug in XML? Although mybatis’ design is out of date, its new design based on annotation is even more frustrating. Have you ever seen a cream cake with ten layers of cream on top and one layer of cake on the bottom? Mybatis’s comments seem like that
  • If you want to add a new method, you need to first change XML, then generate Dao interface, and finally use it. Although the ceremony is full, the programming efficiency is extremely low. Or you can try to write SQL in the annotation, which is pretty cool
  • The configuration of sub database and sub table is usually complex, and there is basically no support of automation tools, all by hand. This experience is as exciting as blindfolded hands catching loach in a jar full of pushpins. I still remember the frustration when I read the user manual of a sub database sub table component three times
  • At the end of the day, you will find that the data source configurations of different environments are often placed in the same project, which can be distinguished by profile or other means. It’s easy to make mistakes in the packaging phase. This error can only be found in the deployment phase. It’s extremely troublesome to download and unpack before troubleshooting. Not to mention the security risks of password disclosure in production database

With the growth of the database and the table, the pain index of doing these things has developed rapidly from pain to infinite pain. If you think it’s nothing, you must be the group who can enjoy the blessings

When the hard code finally works, you will find that compared with the overall configuration and code volume, the final Dao code only accounts for a small part. In this part of the code, only a very small part is really useful

public static void main(string[] args) throws IOException {
    Inputstrean resourceAsstream Resources ogetResourceasstrean("cc/sq1MupConfig.xml");
    sq1SessionFactory ssfenew sq1Session actoryBullder() .build(resourceAsstream);
    ///Mapper is the implementation class of usermapper interface
    UserMapper mapper = sqlSession. getMapper(UserMapper.class);
    User u = mapper.finduserById(10);
    system.out.print1n();
}

This is a typical mybatis code. What’s all this except the penultimate line of code? Just grab a two cent red bag, why do you have to kowtow so much? Remove comments and irrelevant code, which only accounts for one fifth of the real useful code. Don’t you think the proportion is ridiculous? So much for delivering this little bit of code,
Disclosure of data access middleware Das of Xinye Technology
Do you think it’s wrong?

In fact, to query, the real key information is the database name and query statement. To evaluate the quality of a design, we only need to provide necessary information to realize a requirement. The more additional steps, the more failed the design! For reference, think about the difference between dining in a restaurant and buying your own food and cooking.

As an old programmer, I’m tired of using broken tools. People should be nice to themselves and honest. A personalized database access framework should look like this:

  • With simple and clear API, 99% of the operations can be done in one step, smooth and silky to use
  • Use the high-level abstraction of application-oriented and data object-oriented, rather than the underlying concepts of linking and transaction, which are easy to use and wrong
  • Provides a Java based dynamic SQL generator. Write SQL and code in the same context without cutting
  • Links, affairs and so on are all handled by the framework. You don’t need to worry about the opening, closing, leaking of resources. If you don’t use them, there will be no harm
  • The API is simple and complex. It can not only adapt to the general situation, but also deal with the special situation. Don’t ask, just
  • Built in sub database and sub table capability, no need to integrate third-party components separately. The configuration of sub database and sub table must be very simple, even I can learn, of course, it’s better not to learn. If you ask a researcher what’s the most painful thing in the world? He will surely answer you that learning new technology is the most painful. If we want to go to a higher level, we need to learn the technology that takes a lot of effort to learn but only uses it once or twice in a long time. Yes, it’s about the configuration of sub databases and sub tables
  • Don’t let me edit any XML or configuration file by hand. It’s 2020. Writing XML on tens of thousands of MACS is like squatting on a chair for dinner at a luxury dinner party. If configuration is unavoidable, it must provide the best editor that can match my finger zen.
  • Write only Dao related codes, and do nothing else

So in 2018, under the planning of the then CTO, the basic component team of Xinye technology decided to develop a set of database access middleware by themselves, which is in line with their own wishes

Shinya Das

DAS is the abbreviation of database access service. The goal of DAS is to provide a one-stop database access framework for R & D personnel to develop database access code in the simplest and most direct way, so as to realize all the above non divisible ideas
Disclosure of data access middleware Das of Xinye Technology

To achieve this, DAS provides:

  • Das client with ORM and table splitting capability
  • Das console: a database configuration management and code generation tool based on Web page
  • Optional Das server based on agent mode. No need to change code or know when switching between direct connection and agent

Disclosure of data access middleware Das of Xinye Technology

But the real core advantage of DAS is not these components. We have built a professional team to actively serve the programmers in 7 * 24 hours to help you solve any database problems from the atomic scale to the cosmic scale

In Xinye technology, the R & D personnel send e-mails to tell the DAS team about the database configuration and logical database information of each environment. After the DAS team is configured through the DAS console and automatically synchronized to the company’s configuration center, users can start to write code directly as long as they introduce the dependency of DAS client into their projects. Yes, you didn’t read it wrong. You just started to write code without any local configuration. We have upgraded the research and development of middleware products from delivery components to delivery services

This is the secret of our success! [scattering flowers]
Disclosure of data access middleware Das of Xinye Technology

Discussion on the core design of DAS

You must have a sneer in your heart. Blow it! Let’s see what the core Das client looks like from a technical point of view
Disclosure of data access middleware Das of Xinye Technology

The design of Das client follows the principle of layered abstraction, which is divided into:

  1. Dao layer, a complete ORM framework. All the good ideas about programming are here
  2. Sub warehouse and sub surface. Abstract the difference of database operation, and handle the routing and merging of data in a unified way
  3. Executive layer. Operate the underlying database to complete the actual work, encapsulate the data source, link and transaction

The DAO layer is the most frequently used part of programmers. Today, we will highlight this part. The rest will be provided one by one in the future. Please pay attention to our official account.

Introduction to Das ORM

Das ORM is mainly composed of predefined Dao class dasclient, SQL creation tool class sqlbuilder and special operation instruction class hints. Next, I will introduce them one by one.

DasClient

The core of Das ORM is the dasclient class. Let’s see what methods are provided:
Disclosure of data access middleware Das of Xinye Technology

Dasclient provides almost all common ORM operations, which are out of the box and do not require users to generate any Dao interface or implementation

Disclosure of data access middleware Das of Xinye Technology
Don’t talk to me, code!

OK! Guess how many lines of code are needed to implement a query operation with Das?

    Person pk = new Person();
    pk.setName("test"); 

    DasClient dao = DasClientFactory.getClient("logicDbName");
    List<Person> plist = dao.queryBySample(pk);

Is it very simple and crude for the client to create and use two lines of code? As I said, if you want to complete a query, you need to provide only the database name and SQL, where SQL is represented by sample data. In addition, there are no redundant actions. No session, no transaction, no connection. As long as I write enough code, bug will not catch up with me. This is the legend of minimalist programming

How much code can be saved through this predefined API approach? Take a real example to compare the amount of code between mybatis and Das to complete the same function:

Mybatis mapping:

  <select id="selectByExample" parameterType="com.ppdai.xxxxxxxxxxxxxxxxxxx.StrategyAccountDetailExample" resultMap="BaseResultMap">
    
    select
    <if test="distinct">
      distinct
    </if>
    'false' as QUERYID,
    <include refid="Base_Column_List" />
    from strategyaccountdetail${tableSuffix}
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>

Das corresponding code:

      public List<Strategyaccountdetail> selectByExample(Strategyaccountdetail detail) throws SQLException {
        return client.queryBySample(detail);
    }

See the difference? Without writing one line of XML, DAS can handle the functions that mybatis needs dozens of lines, even dozens of lines of configuration, with one line of code. In fact, the above display is only a small part of the complete mybatis configuration, but it’s enough to show that I’m not bragging

SqlBuilder

You will surely think that it is very easy to provide a general implementation for the example of query by example. What if you want to generate complex and dynamic SQL according to various conditions? Is it necessary to write a lot of if else statements and spell them by yourself? Pattern! At this time, sqlbuilder will come out. Let’s take a look at the actual code comparison:
Mybatis mapping:

  <select id="selectListByUserIdExample" parameterType="java.util.Map" resultMap="BaseResultMap">
    select * from (select ROW_NUMBER() OVER ( ORDER BY inserttime DESC ) rownum,  <include refid="Base_Column_List" />
    from strategyaccountdetail${tableSuffix} WITH(NOLOCK)
    where userid = #{userid,jdbcType=INTEGER}
    <if test="strategyid != null and strategyid != ''">
      and strategyid = #{strategyid,jdbcType=VARCHAR}
    </if>
    <if test="typeid != null">
      and typeid = #{typeid,jdbcType=INTEGER}
    </if>
    <if test="beginInserttime != null">
      and inserttime <![CDATA[>= ]]> #{beginInserttime,jdbcType=TIMESTAMP}
    </if>
    <if test="endInserttime != null">
      and inserttime <![CDATA[<= ]]> #{endInserttime,jdbcType=TIMESTAMP}
    </if>
    AND isactive=1) tpage WHERE tpage.rownum BETWEEN ${startPage} AND ${pageSize}
  </select>

Das corresponding code:

    public List<Strategyaccountdetail> selectListByUserIdExample(Long userId, String strategyid, Integer typeId,
        Date beginInserttime, Date endInserttime, Integer pageNum, Integer pageSize) throws SQLException {
        SqlBuilder builder = SqlBuilder.selectAllFrom(definition).where().allOf(definition.Userid.eq(userId),definition.Isactive.eq(1),
                definition.Strategyid.eq(strategyid).nullable(),
                definition.Typeid.eq(typeId).nullable(),definition.Inserttime.greaterThanOrEqual(beginInserttime).nullable(),
                definition.Inserttime.lessThanOrEqual(endInserttime).nullable()).
                orderBy(definition.Inserttime.desc()).into(Strategyaccountdetail.class).offset(pageNum, pageSize).withLock();
        return client.query(builder);
    }

Is the code of Das using sqlbuilder still as compact and smooth? Some people will say that the latest mybatis also has sqlcompiler. Let’s do the same. Don’t say I’m a liar:
Mybatis Sql builder:

    public string selectPersonLike(final String id, final String firstName, final string lastlame) 《
        return new SQL() {
            {
                SELECT("P. ID, P.USERNAIE, P.PASSHORD, P.FIRST _NANE, P.LAST NAME");
                FROM("PERSON P")
                if (id != null) {
                    WHERE("P.ID like#{id}");
                }
                if (firstlame != null) {
                    WHERE("P.FIRST MAE like #{firstliase}");
                }
                if (lastlame != null) {
                    WHERE("P.LAST NAMIE like #{lastName}");
                }
                ORDER BY("P.LAST. NAME");
            }
        }.toString();
    }

DAS SqlBuilder:

    public SqlBuilder seletPersonLike(final string id, final String firstlane, final string lastName) {
        Person.PersonDefinition P = Person.PERSON;
        return sqlBuilder.selectAllFrom(p) where().
            allOf(
                p.d.like(id).nullable(),
                p.firstName.like(firstNane).nullable(),
                p. lastNare .1ike(iastName).nullab1e()
            ).orderBy(p.lastName);
    }

Obviously, DAS’s sqlbuilder design is better!

Hints

There is a design risk in providing API in one step, that is, there will be special circumstances in any operation. For example, a simple insert operation has many variations:

  1. Generate an AutoID when there is an AutoID
  2. Use a custom ID when there is an auto increment ID
  3. Generate auto ID in case of auto ID and set the generated ID to the input entity
  4. wait

The common method is to provide the overload method for each special method, and several methods are provided for several special cases. With the development of this idea, the number of methods will soon be out of control. How can you ensure that as many special operations as possible are provided on a compact API collection? It’s hints’s turn.

You may notice that in addition to the necessary parameters, dasclient’s method often comes with a hints. This hints exists either as a variable parameter or as an attribute of a required parameter. Das uses hints to deliver special instructions to help users deal with flexible scenarios. For example, to insert a single record, the API is as follows:

public <T> int insert(T entity, Hints...hints) throws SQLException

When calling, only entity can be passed:

dao.insert(p);

You can also pass up to one hint

dao.insert(p, hints.insertWithId());

In either case, there is only one way.
Although hints is not a very big invention of brain hole, it is so close and natural to ORM that there is no semicolon. The convenience of this design is enormous. I don’t believe you can refer to how to implement the independent sub database and sub table components:

    // Sharding database and table with using hintManager ,
    String sql = "SELECT * FROM t order";
    try (HintManager hintManager = HintManager.getInstance(;
        Connectlon conn = dataSource.getConnection();
        PreparedStatement preparedstatement conn. prepareStatement(sq1)) {
        hintManager.addDatabaseShardingValue("t_order", 1);
        hintManager.addTableShardingValue("t_order", 2);
        try (ResultSet rs = preparedStatement.executeQuery()) {
            while (rs.next()) {
                //...
            }
        }
    }

The above three lines of independent code are needed to complete the hints related work. This is not to say that the sub database and sub table components are not well designed. In addition to the real distributed database such as tidb or Amazon Aurora, most of the sub database and sub table components based on the traditional database are difficult to be completely transparent to the application code. In special situations, special instructions need to be passed in some way. If you rely on existing ORM tools or JDBC based, there will be unnatural code like the above.

Das solves the contradiction between special and general perfectly by combining hints and ORM interface. The same thing Das only needs one line:

List<Person) plist = dao.query(selectAllFrom(p). setHints(Hints.hints().shardValue(1).tableShardValue(2)));

We also found an interesting thing in the promotion process. We think that users like transparent sub database and sub table, but in fact, for various reasons, users use the most is to directly specify sub database and sub table. Of course, using hints can be very simple:

List<Person> plist = dao.query(selectAllFrom(p).setHints(Hints.hints(). inShard(1).inTableShard(2)));

There is an additional benefit of developing ORM by yourself. That is, although there is still a market for the technology of sub database and sub table in terms of cost and technology, in the long run, this high probability is a transitional technology. Even if the performance and consistency problems of distributed database are solved, some application-oriented ORM technology is still needed to realize the flexible requirements. So Das can continue to work. By today’s standards, the design of Das ORM has reached its limits in terms of ease of use and flexibility.

summary

DAS is a perfect combination of ORM and sub database and sub table functions. Its product positioning is to advance to attack and retreat to defend. According to the actual use effect in the company, using Das can greatly improve the R & D efficiency, reduce the amount of code and the probability of error, and there are no failures caused by configuration.

Once I passed by and heard a conversation between a director and the following tech leader. The director asked if our Das could not be used in the new code if the technology output. The leader smiled but replied firmly, no, DAS is very useful, I want to use it.

Is there anything better than a sentence that is easy to use for our framework programmers?

It’s a good thing to use together. DAS is open source and provides detailed documents for your reference. Please enjoy star

GitHub address: https://github.com/ppdaicorp/das

In addition to open source documents, we also provide online technical support, and interested friends can join the group for help or more activity information

Finally, don’t repeat the fallacy of making wheels. You don’t make it, you just give it to others.

Disclosure of data access middleware Das of Xinye Technology


About the author

Hejiehui, director of basic component Department of Xinye technology, product director and preacher of Xinye DAS. Author of the graphical build toolset X-Series. Once presided over the development of the open source database access framework DAL of Ctrip. Many years of research and accumulation on the improvement of application development efficiency and distributed database access mechanism