英 文: 190623-SpringBoot series tutorial JPA update posture

Above two blog posts for jpa using posture in the corner of the veil, and then we continue to pull down, insert data into the db, is not to say that a layer of the same, just as I opened the door, in the bank is prepared to deposit money, of course, the deposit is the (especially the present bank interest rates so low, it is better to buy the currency the chariot by the 19th June 22, BTC has broken through 1.1w$, unfortunately there is no money to buy 😭) This is our topic today, data update – the use of the position of update

With this post, you can at least get it

  • save()Modify records directly based on the ID
  • usingjplTo achieve the use of the query modification posture
  • The mystery of the first thing

I. Environment preparation

Before you get started, of course, you need to prepare the infrastructure, such as installing and testing mysql, creating a SpringBoot project, setting up configuration information, and so on. For details on setting up the project, see the previous article

  • 190612-SpringBoot series tutorial JPA infrastructure
  • 190614- New record posture for SpringBoot series JPA

Here’s a quick look at the configuration required to demonstrate adding records

1. The table prepared

Follow the table of the previous article, the structure is as follows

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username'.`money` int(26) NOT NULL DEFAULT '0' COMMENT 'money'.`is_deleted` tinyint(1) NOT NULL DEFAULT '0'.`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.`update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  PRIMARY KEY (`id`),
  KEY `name` (`name`))ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Copy the code

2. Configure the project

Configuration information, a little different from the previous, we added more detailed log printing; The main goal of this article is to focus on adding the posture of the record. The configuration instructions are described separately later

# # the DataSource spring. The DataSource. Url = JDBC: mysql: / / 127.0.0.1:3306 / story? useUnicode=true&characterEncoding=UTF-8&useSSL=false spring.datasource.driver-class-name=com.mysql.jdbc.Driver Spring. The datasource. The username = root spring. The datasource. The password = # # jpa configuration related spring. Jpa. Database = MYSQL spring.jpa.hibernate.ddl-auto=none spring.jpa.show-sql=true spring.jackson.serialization.indent_output=true spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImplCopy the code

3. Obtain data

Data modification, so we first insert two pieces of data into the table for later operations

INSERT INTO `money` (`id`.`name`.`money`.`is_deleted`.`create_at`.`update_at`)
VALUES
	(21.'JPA modification -> a gray'.1212.0.'the 2019-06-22 21:41:13'.'the 2019-06-22 21:41:13'),
	(22.'JPA modification -> a gray'.6666.0.'the 2019-06-22 21:41:13'.'the 2019-06-22 21:41:13');
Copy the code

Ii. Update using tutorials

The following begins to enter the main topic, for the convenience of the first time to see the students (not busy or not interested in the previous several blog students) there will be some content and the previous blog the same, read please ignore

1. Associate a POJO with a table

The previous insert introduced the step-by-step creation process of the POJO, and the corresponding annotation meaning is posted directly below

@Data
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")

    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    @CreatedDate
    private Timestamp createAt;

    @Column(name = "update_at")
    @CreatedDate
    private Timestamp updateAt;
}
Copy the code

A few of the annotations in the above class are described below

  • @DataLombok annotations, independent of JPA, are generated automaticallygetter/setter/equals/hashcode/tostringMethods such as
  • @Entity.@TableJpa annotation, indicating that this class is associated with a table in DB, specifically matching the tablemoney
  • @Id @GeneratedValueIt’s acting on the self-increasing primary bond
  • @ColumnIndicates that this attribute corresponds to a column in the table
  • @CreateDateGenerates a default timestamp based on the current time

Repository API declaration

Next, let’s create a new API inherited from The CurdRepository, and use that API to work with the database

public interface MoneyUpdateRepository extends CrudRepository<MoneyPO.Integer> {}Copy the code

3. Use posture

a. save

In the previous insertion, we learned that there are two possible scenarios for calling the save method when the POJO’s ID exists

  • If the field corresponding to this ID does not exist in db, insert
  • Update if a field corresponding to this ID exists in db

Let’s try this update. The following code shows what happens when all member values in the Po are valid and update one of them. Another demonstration is what happens with partial updates (name is empty, meaning I don’t want to update the name)

public void simpleUpdateById(a) {
    MoneyPO record = moneyUpdateRepository.findById(21).get();
    // Modify the content of the record directly
    record.setMoney(3333L);
    moneyUpdateRepository.save(record);

    record = moneyUpdateRepository.findById(21).get();
    System.out.println("after updateMoney record: " + record);


    record.setName(null);
    record.setMoney(6666L);
    moneyUpdateRepository.save(record);
    
    record = moneyUpdateRepository.findById(21).get();
    System.out.println("after updateMoney record: " + record);
}
Copy the code

In the output, it is found that the previous execution succeeds, and the subsequent execution fails

The first result is a full change. The output was as expected

After we set name to null, we update it again and find that an exception is thrown, as follows. This is because our DB limit does not allow null fields to exist

From the perspective of SQL concatenation, we know that this is because each member is a member of the UPDATE SQL family. In insert, we also encountered a similar problem. At that time, we added @dynamicINSERT to the POJO and chose to insert according to actual needs. Is there a similar comment in the update

@Data
@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {}Copy the code

After adding the @dynamicUpdate annotation to the POJO, I tried again and the result was as follows

It still failed. The SQL output included name and money as part of the SQL, because we called the setter method. Let’s guess. Let’s do it again

MoneyPO toUpdate = new MoneyPO();
toUpdate.setId(21);
toUpdate.setMoney(6666L);
moneyUpdateRepository.save(toUpdate);
record = moneyUpdateRepository.findById(21).get();
System.out.println("after updateMoney record: " + record);
Copy the code

The output is as follows. It seems that the above guess is not correct. The concatenation SQL should be done according to which field has changed and which field is part of the SQL

After reading the above gesture, we will have an obvious feeling that the updated support must first obtain the target object and then modify it, which is difficult to meet our daily business scenario.

B. Query updates

It is common to update data based on a condition. In JPA, there is no way to support this scenario based on method names, but there is another interesting thing to find — JQL

Directly in the method, add annotations, annotations write SQL inside

/** * Modify state according to money **@param money
 * @param state
 */
@Modifying
@Query("update MoneyPO m set m.isDeleted=? 2 where m.money=? 1")
void updateStateByMoney(Long money, Byte state);

/** * Expression evaluation **@param id
 * @param money
 */
@Modifying
@Query("update MoneyPO m set m.money=m.money + ? 2 where m.id=? 1")
void addMoneyById(Integer id, Long money);
Copy the code

Above is a case where the query is updated. Note two annotations

  • @ModifyingThis must be there to tell the framework that we are performing an update/delete operation
  • @QueryThe inside is a normal SQL statement, but it is important to note that the table name is not the actual table, but the POJO we defined earlier

Then let’s test the use

public void updateByQuery(a) {
    // Modify by querying
    moneyUpdateRepository.updateStateByMoney(6666L, (byte) 0x01);

    MoneyPO record = moneyUpdateRepository.findById(21).get();
    System.out.println("after update record: " + record);


    moneyUpdateRepository.addMoneyById(21.3333L);
    record = moneyUpdateRepository.findById(21).get();
    System.out.println("after addMoney record: " + record);
}
Copy the code

Perform the above code, the discovery of miserable incredibly error under Caused by: javax.mail. Persistence. TransactionRequiredException: Executing an update/delete query

From the stack description, update/delete must start transaction, so what is a transaction? Here are a few blog posts

  • Mysql locks and transactions
  • Spring learned about the posture of transactions
  • Spring learning transaction management and propagation properties

More transactions-related in JPA will be introduced later, but back to the topic of this article, how to solve the problem: Just add transaction annotations to the above method call

@Transactional
public void testUpdate(a) {
    simpleUpdateById();
    updateByQuery();
}
Copy the code

Again, the result is as follows

Look at the above results, found money+3333 after the output is still 6666; But if we look at db, it’s 9999. Why is that?

Above this question is more interesting, preliminary speculation and things have jPA internal cache mechanism is related, as for the specific is not so, it is necessary to specifically open pit to verify one or two

4. Summary

Using JPA to achieve table data update, the above mainly introduced two ways, save + JQL

save

When updating with save, you need to specify the ID to modify a single record

jql

Grammar and SQL, with two annotations @ Modifying, @ the Query to use, below is an example of two points to note

  • The table name is the POJO we defined that is associated with the table in the DB
  • The parameter passing format is? index, index is the parameter position
@Modifying
@Query("update MoneyPO m set m.isDeleted=? 2 where m.money=? 1")
void updateStateByMoney(Long money, Byte state);
Copy the code

Add the @transactional annotation to the called method or add the @transactional annotation directly to the Repository API interface

II. The other

0. Source and related blog

The source code

  • Project: github.com/liuyueyi/sp…
  • The module: github.com/liuyueyi/sp…

Related blog

  • Mysql locks and transactions
  • Spring learned about the posture of transactions
  • Spring learning transaction management and propagation properties
  • 190612-SpringBoot series tutorial JPA infrastructure
  • 190614- New record posture for SpringBoot series JPA

1. A gray Blog

The above content is not as good as the letter, purely the words of a family, due to the limited personal ability, there are inevitably omissions and mistakes, such as found bugs or better suggestions, welcome criticism and correction, not grudging gratitude

Below a gray personal blog, record all study and work in the blog, welcome everyone to visit

  • A gray Blog personal Blog blog.hhui. Top
  • A Gray Blog-Spring feature Blog Spring.hhui. Top