Insert Operations

EasyQuery provides methods for inserting single and batch records, returning the number of affected rows.

Database Table Script

create table t_topic
(
    id varchar(32) not null comment 'Primary Key' primary key,
    stars int not null comment 'Star Count',
    title varchar(50) not null comment 'Title',
    create_time datetime not null comment 'Create Time'
) comment 'Topic Table';

Java Entity

@Data
@Table("t_topic")
@EntityProxy
public class Topic implements ProxyEntityAvailable<Topic, TopicProxy> {

    @Column(primaryKey = true)
    private String id;
    private Integer stars;
    private String title;
    private LocalDateTime createTime;
}

// Prepare test data
List<Topic> topics = new ArrayList<>();
for (int i = 0; i < 10; i++) {
    Topic topic = new Topic();
    topic.setId(String.valueOf(i));
    topic.setStars(i + 100);
    topic.setTitle("Title" + i);
    topic.setCreateTime(LocalDateTime.now().plusDays(i));
    topics.add(topic);
}

1. Single Insert

long rows = easyEntityQuery.insertable(topics.get(0)).executeRows();
// Returns rows = 1

==> Preparing: INSERT INTO t_topic (`id`,`stars`,`title`,`create_time`) VALUES (?,?,?,?) 
==> Parameters: 0(String),100(Integer),Title0(String),2023-03-16T21:34:13.287(LocalDateTime)
<== Total: 1

If the table has an auto-increment ID, it will be filled back to the entity using executeRows(true).

2. Batch Insert

Note

For MySQL, add rewriteBatchedStatements=true to your JDBC connection string for significantly improved performance.

long rows = easyEntityQuery.insertable(topics).executeRows();
// Returns rows = 10

==> Preparing: INSERT INTO t_topic (`id`,`stars`,`title`,`create_time`) VALUES (?,?,?,?) 
==> Parameters: 0(String),100(Integer),Title0(String),2023-03-16T21:38:22.114(LocalDateTime)
==> Parameters: 1(String),101(Integer),Title1(String),2023-03-17T21:38:22.114(LocalDateTime)
==> Parameters: 2(String),102(Integer),Title2(String),2023-03-18T21:38:22.114(LocalDateTime)
...
<== Total: 10

3. Chained Insert

long rows = easyEntityQuery.insertable(topics.get(0))
    .insert(topics.get(1))
    .executeRows();
// Returns rows = 2

==> Preparing: INSERT INTO t_topic (`id`,`stars`,`title`,`create_time`) VALUES (?,?,?,?) 
==> Parameters: 0(String),100(Integer),Title0(String),2023-03-16T21:42:12.542(LocalDateTime)
==> Parameters: 1(String),101(Integer),Title1(String),2023-03-17T21:42:12.542(LocalDateTime)
<== Total: 2

4. Auto-Increment Key Fill

When using auto-increment IDs, easy-query can fill back the generated primary key:

@Data
@Table("t_topic_auto")
@EntityProxy
public class TopicAuto implements ProxyEntityAvailable<TopicAuto, TopicAutoProxy> {

    @Column(primaryKey = true, generatedKey = true) // Mark as auto-increment
    private Integer id;
    private Integer stars;
    private String title;
    private LocalDateTime createTime;
}

TopicAuto topicAuto = new TopicAuto();
topicAuto.setStars(999);
topicAuto.setTitle("title999");
topicAuto.setCreateTime(LocalDateTime.now().plusDays(99));
Assert.assertNull(topicAuto.getId());

// true indicates to fill back the primary key
long l = easyEntityQuery.insertable(topicAuto).executeRows(true);

Assert.assertEquals(1, l);
Assert.assertNotNull(topicAuto.getId()); // ID is now filled

==> Preparing: INSERT INTO `t_topic_auto` (`stars`,`title`,`create_time`) VALUES (?,?,?)
==> Parameters: 999(Integer),title999(String),2023-08-31T16:36:06.552(LocalDateTime)
<== Total: 1

5. Insert Strategy

insertStrategy controls which columns to insert. The default strategy is SQLExecuteStrategyEnum.ONLY_NOT_NULL_COLUMNS (only non-null columns).

// Only inserts non-null columns (default)
QueryLargeColumnTestEntity entity = new QueryLargeColumnTestEntity();
entity.setId("123");
long l = easyEntityQuery.insertable(entity).executeRows();

==> Preparing: INSERT INTO `query_large_column_test` (`id`) VALUES (?) 
==> Parameters: 123(String)


// Insert all columns
entity.setId("123");
l = easyEntityQuery.insertable(entity)
    .setSQLStrategy(SQLExecuteStrategyEnum.ALL_COLUMNS)
    .executeRows();

==> Preparing: INSERT INTO `query_large_column_test` (`id`,`name`,`content`) VALUES (?,?,?) 
==> Parameters: 123(String),null(null),null(null)


// Insert only null columns
entity.setId("123");
l = easyEntityQuery.insertable(entity)
    .setSQLStrategy(SQLExecuteStrategyEnum.ONLY_NULL_COLUMNS)
    .executeRows();

==> Preparing: INSERT INTO `query_large_column_test` (`name`,`content`) VALUES (?,?) 
==> Parameters: null(null),null(null)

6. Map Insert

Easy-query supports inserting Map structures where keys represent database column names:

Map<String, Object> data = new LinkedHashMap<>();
data.put("id", 123);
data.put("name", "John");
data.put("name1", "Doe");
data.put("name2", null);

easyEntityQuery.mapInsertable(data)
    .asTable("sys_table")
    .setSQLStrategy(SQLExecuteStrategyEnum.ONLY_NOT_NULL_COLUMNS)
    .executeRows();

// Generated SQL:
INSERT INTO `sys_table` (`id`,`name`,`name1`) VALUES (?,?,?)

7. Conflict Handling

Easy-query provides the conflictThen method for handling duplicate key conflicts. See Insert Or Update chapter for details.

Best Practices

Use Batch Insert for Large Data

// ✅ Good - batch insert
List<Topic> topics = createLargeTopicList();
easyEntityQuery.insertable(topics)
    .batch(true)
    .executeRows();

// ❌ Avoid - individual inserts
for (Topic topic : topics) {
    easyEntityQuery.insertable(topic).executeRows(); // Slow!
}

Selective Insert

// ✅ Good - only insert non-null fields
Topic topic = new Topic();
topic.setTitle("My Title");
// id and createTime are null, won't be inserted
easyEntityQuery.insertable(topic)
    .setSQLStrategy(SQLExecuteStrategyEnum.ONLY_NOT_NULL_COLUMNS)
    .executeRows();

Get Generated Keys

// ✅ Good - get auto-increment ID
Topic topic = new Topic();
topic.setTitle("New Topic");
easyEntityQuery.insertable(topic).executeRows(true);
System.out.println("Generated ID: " + topic.getId());

Common Issues

Issue: Batch Insert is Slow

Problem: Batch insert is as slow as individual inserts

Solution: Add rewriteBatchedStatements=true to your MySQL JDBC URL:

jdbc:mysql://localhost:3306/db?rewriteBatchedStatements=true

Issue: Primary Key Not Filled

Problem: Auto-increment ID is still null after insert

Solution:

1. Add generatedKey = true to the @Column annotation
2. Call executeRows(true) instead of executeRows()

See Also

• Update Operations
• Delete Operations
• Insert Or Update
• Batch Operations

---

If you encounter problems, feel free to join our QQ group: 170029046