SqlUtils Usage

I. Preface

With the release of Solon 3.0, the newly added SqlUtils interface for manipulating databases, SqlUtils is a wrapper around the original Jdbc interface. SqlUtils is a wrapper around the original Jdbc interface. It is suitable for scenarios where SQL is minimal or complex, or where ORM is not suitable.

Second, SqlUtils use

1. Introducing dependencies

<dependency>
    <groupId></groupId>
    <artifactId>solon-data-sqlutils</artifactId>
</dependency>

2. New database table (for MySql)

CREATE TABLE `book`  (
  `id` bigint(20) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

3. Defining entity classes

Uses the lombok annotation.

@Data
public class Book {
    private Long id;
    private String name;
    private String author;
}

4. Add data source configuration

:
  book!: # ‘!’The end indicates the default
    class: ""
    jdbcUrl: jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true
    driverClassName:
    username: root
    password: 123456

5. Inject SqlUtils and use it.

pour into

@Component
public class BookDao {
    @Inject
    private SqlUtils sqlUtils;
}

query operation

public List<Book> getAllBooks() {
    return ("select * from book")
                   .queryRowList()
                   .toBeanList();
}

New operations

public Long addBook(Book book) {
    return ("INSERT INTO book (name , author) VALUES (?,?)", (), ())
                   .updateReturnKey();
}

Update Operation

public int updateBook(Book book) {
    return ("UPDATE book SET name=?, author=? WHERE id=?", (), (), ())
                   .update();
}

summarize

Using SqlUtils you can do basic database operations with better transparency. Of course, persistent database operations ORM is a better solution. It is up to the developer to decide.