Source: https://www.jianshu.com/p/1ee34c858cb9
Java's database uses jdbc natively, and there are many middleware, such as hibernate, mybatis, and jdbi, which are the most commonly used middleware.
How to choose- In general, if only one kind of database is always used, jdbc is the best choice. In this way, you can directly use the lowest level, and the location problem is very convenient.
- Database middleware is to save development manpower. It can be developed quickly with little code. But it will also introduce some complexity, especially if you are not familiar with middleware or there are bug s in middleware, which will lead to higher complexity.
- If you use database middleware, all kinds of middleware have their own advantages and disadvantages. You need to understand the relevant things clearly, otherwise it will lead to improper choice.
jdbi is a database middleware that I prefer. It is non ORM, especially suitable for the scenario where the database is fixed, that is, it does not correspond to multiple databases, and it will not change the database in the future. If it is not such a scenario, it is better to use jdbc or hibernate and other middleware that is compatible with multiple databases. Based on the above scenarios, the advantages of jdbi are as follows:
- It is close to JDBC I and is very easy to use and master.
- With the development of the times, for example, the latest jdbi3 has increased the programming styles such as stream programming and functional programming.
- The implementation of the source code is very clear and has a sense of beauty. The database code encapsulated by jdbi is also very clear.
Fluent Api
handle.createUpdate ("INSERT INTO user (id, name) VALUES (:id, :name)") .bind ("id", 2) .bind ("name", "Clarice") .execute ();
This is the flow style of java8, which strings an sql implementation with a coherent expression
Declarative Api
// Define your own declarative interface public interface UserDao { @SqlUpdate ("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)") void createTable (); @SqlUpdate ("INSERT INTO user (id, name) VALUES (?, ?)") void insertPositional (int id, String name); @SqlUpdate ("INSERT INTO user (id, name) VALUES (:id, :name)") void insertNamed (@Bind ("id") int id, @Bind ("name") String name); @SqlUpdate ("INSERT INTO user (id, name) VALUES (:id, :name)") void insertBean (@BindBean User user); @SqlQuery ("SELECT * FROM user ORDER BY name") @RegisterBeanMapper (User.class) List<User> listUsers (); }
Declarative is mainly implemented by annotations. In the actual object-oriented code, I personally think declarative is relatively simple, easy to read and maintain. So the following are in the way of Declarative Api.
Some key questionsFor the implementation of jdbi, you need to go to the homepage of jdbi, http://jdbi.org/ , here are some problems that need to be noticed in actual use
Return auto incrementing primary key
What does jdbc do?
try { Connection conn = DBUtil2.getConnection (); //PreparedStatement ps = // conn.prepareStatement (SAVE_SQL, new String [] { "id" }); // The generated primary key should be returned. You can use both methods PreparedStatement ps = conn.prepareStatement (SAVE_SQL,PreparedStatement.RETURN_GENERATED_KEYS); ps.setString (1, user.getName ()); ps.setFloat (2, user.getSalary ()); ps.setInt (3, user.getAge ()); if (ps.executeUpdate () > 0) { // Get primary key ResultSet rs = ps.getGeneratedKeys (); rs.next (); int i = rs.getInt (1); user.setId (i); ps.close (); return true; } ps.close (); } catch (SQLException e) { e.printStackTrace (); } finally { DBUtil2.closeConnection (); }
Mechanisms provided by jdbi:
public interface UserDao { @SqlBatch ("INSERT INTO users (name) VALUES (?)") @GetGeneratedKeys List<User> createUsers (String... names); }
Very intuitive and simple
query
In jdbc mode, when we query a large amount of data, we usually use database cursors to query one by one. What is the query of jdbi? As follows:
public interface UserDao { @SqlQuery ("select name from users") List<String> listNames (); }
But at this time, if you encounter a large amount of data, what about the solution provided by jdbi:
public interface UserDao { @SqlQuery ("select name from users") ResultIterator<String> getNamesAsIterator (); }
Here, iterators are used to query in turn, which is the same as jdbi cursors.
Bulk insert
For the insertion of jdbi, when a list is inserted, if you want to improve the efficiency, you need to split the insertion. The split insertion jdbi provides the mechanism SqlBatch
public interface UserDao { @SqlBatch ("insert into users (tenant_id, id, name)" + "values (:tenantId, :user.id, :user.name)") void bulkInsert (@Bind ("tenantId") long tenantId, @BindBean ("user") User... users); }Summary
jdbi is a very simple and elegant database middleware component. When it is the only database, especially pg database, I think it is the first choice. It effectively encapsulates jdbc, which is more suitable for development.
Resource back garden: https://gitee.com/alterem/picFB/raw/master/pics/2020/05/20/1569337870.png
