select

User user = new User();
user.setName("l2");
user.setPassword("444");
int i =  userMapper.addUser(user);
System.out.println(i);

<select id="getUserById" parameterType="Integer" resultType="User">
    select * from t_user where id = #{id};
</select>

There's nothing to say about the return value of select. It's written with resultType and resultMap. That's the return value.

User user = userMapper.getUserById(1);
System.out.println(user.toString());

The test data is also relatively simple. There's nothing to say about this.

insert

<insert id="addUser" parameterType="User">
    INSERT INTO `db_ssm`.`t_user` (`id`, `name`, `password`)
    VALUES
      (null, #{name}, #{password}) ;
</insert>

insert is also relatively simple, and the test is posted.

User user = new User();
user.setName("l2");
user.setPassword("444");
int i =  userMapper.addUser(user);
System.out.println(i);

It seems to be relatively simple, and there's nothing to say.

Wait, I seem to have forgotten the central idea of this article. Yes, it is insert.

If the insert is successful, the return value is 1, because I only inserted one piece of data. Why mention the data? It is said that the insert returns the number of inserted data. If you insert multiple pieces of data... Fantasize. Maybe it's not 1. I won't try this. I'll try again when I meet it.

This is the result of verification.

Some people have to ask. What if the insertion is wrong.

Re simulate insertion errors,

        User user = new User();
//        user.setName("l2222");
        user.setPassword("111");
        int i =  userMapper.addUser(user);
        System.out.println(i);

Comment out name,

name cannot be empty in my database.

So if you insist on inserting, you will make mistakes. The results are presented below.

Gorgeous made a mistake and smashed my idea that the result would be 0 in an instant.

That's what I want.

therefore

For the result of insert, if 1 is returned successfully (this 1 may be the number of affected items), exception is returned for failure, not 0.

As a knowledgeable youth in society, how can we tolerate abnormal. Let me deal with it briefly first.

User user = new User();
user.setPassword("33333");
int i = 0;
try {
    i = userMapper.addUser(user);
} catch (Exception e) {
    i = -1;
} finally {
}
System.out.println(i);

In this way, if the insertion is successful, it returns 1, and if it is unsuccessful, it returns - 1.

Isn't it wonderful? I admire my intelligence. However, I cannot rule out whether it is possible to return 0. If the returned number is not less than 1, it is determined that the insertion is successful.

But generally, if we insert successfully, we need to show it with ajax on the page immediately. But unfortunately, how can our user not have an id, how to check, delete and change without an id. So it would be better if we could get the id when inserting.

<insert id="addUser1" parameterType="User" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO `db_ssm`.`t_user` (`id`, `name`, `password`)
    VALUES
      (null, #{name}, #{password}) ;
</insert>

See the next two data?

(1) useGeneratedKeys = "true" this is required.
(2) keyProperty = "id" this is the id, which is the bound id, so I wonder which id is bound. It's a headache. Your return value is clearly int. is that the id you returned.

Look at the test file first

User user = new User();
user.setName("1222");
user.setPassword("44122224");
int i = 0;
try {
    i = userMapper.addUser1(user);
    sqlSession.commit();
} catch (Exception e) {
    System.out.println("add error");
} finally {
}
System.out.println(user.toString());
 
System.out.println(i);

After seeing this, I understand that it doesn't take the id as the return value. It turns out that it takes the id as the return value

parameterType = "user" this is the id of the user set to the inserted id.

It's amazing that users without IDS come in and users with IDS go out. I d id n't expect that not only the return value but also the parameters have been changed. The light blinded me. The level of this thought is so high that we can't catch up with it.

The return value is also the number of inserts.

Oh, here, I know almost the return value inserted by mybatis.

Error 1 in insert: foreign key constraint (there is no corresponding data in the master table when inserting the slave table)

Error: insert the slave table. There is no corresponding data in the master table
Correct posture: insert the master table first, and then the slave table

ica_ Primary key ICA in classify table_ classify_ ID is website_ commodity_ Foreign key of information table

Table structure omitted

When using mybatis to table website_ commodity_ When inserting data into information, the foreign key constraint reports an error

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`cweb/website_commodity_information`, CONSTRAINT `website_commodity_information_ibfk_3` FOREIGN KEY (`ica_classify_id`) REFERENCES `ica_classify` (`ica_classify_id`) ON UPDATE CASCADE)

This problem has been encountered twice. This time, keep a long memory

Web when inserting data_ commodity_ Information table ` ICA_ classify_ Value of ID

Must be with ICA_ The classify table corresponds to a primary key, otherwise an error will be reported.

There is a problem because only one table is noticed and no data is found in the associated table

delete

<delete id="deleteUser" parameterType="Integer" >
    delete from t_user where id = #{id}
</delete>

I'll embarrass you with something you can't delete first

int i = 0;
try {
    i = userMapper.deleteUser(222222);
    sqlSession.commit();
} catch (Exception e) {
    System.out.println("delete error");
} finally {
}
System.out.println(i);

I asked you to delete id 222222. I haven't created it yet. It depends on how you delete it

If you can't delete it, return a 0. It's so simple. Why don't you say This can't be deleted? It's not intelligent at all.

int i = 0;
try {
    i = userMapper.deleteUser(14);
    sqlSession.commit();
} catch (Exception e) {
    System.out.println("delete error");
} finally {
}
System.out.println(i);

Then try the normal id= 14

Oh, the normal return of deletion is 1.

An error is reported when deleting a foreign key constraint (delete the primary table and rely on this row of data from the table)

Error: delete the main table and depend on this row of data from the table
Correct posture: delete the record of the data row dependent on the slave table before deleting the data row in the main table

Example 1

I'll give you something that can't be deleted. What's the matter with you.

If you delete a foreign key constraint, an error will be reported. Ha ha ha.

FK_test FOREIGN KEY (name) REFERENCES t_user (name)

Example 2

A foreign key is defined to establish a relationship between two tables. When deleting, the database will check whether the deleted data will cause damage to the table structure. If it will be damaged, it cannot be deleted.

The solution is to shield the foreign key
The settings are as follows: SET FOREIGN_KEY_CHECKS = 0;
Specific examples:

This is the structure of two tables. test3 is associated with the id primary key of test2.

When test2 is deleted directly_ Data with id = 1, because test3 contains test2_ When deleting data with id = 1, an exception is reported.
You can set it before deleting it. Remember to set it back after deleting it.

update

I'm tired of writing this. I don't want to write, but there's one more thing. Finish it quickly.

<update id="updateUser" parameterType="User">
    update t_user set name = #{name}, password = #{password} where id = #{id}
</update>

Test successful

User user = new User();
user.setId(9);
user.setName("121111");
user.setPassword("11111");
int i = 0;
try {
    i = userMapper.updateUser(user);
    sqlSession.commit();
} catch (Exception e) {
    System.out.println("delete error");
} finally {
}
System.out.println(i);

Success is 1 and failure is 0

update modifies the foreign key constraint and reports an error (modify the main table and rely on this row of data from the table)

Error: modify the main table and depend on this row of data from the table
Correct posture: delete the record of the data row dependent on the slave table before modifying the data row in the main table

The error is an exception

summary

Success is 1, failure is 0, and error is exception. Be sure to catch exceptions. Or the project will collapse.

	select	insert	delete	update
Can find, meet the where condition	model	Return the number of affected rows 1	Return the number of affected rows 1	Return the number of affected rows 1
Not found, where condition not met	null	This is not the case	Return affected rows 0	Return affected rows 0
Error reporting (sql level error)	This is not the case	Primary key conflict, unique index conflict, non empty field not assigned, foreign key constraint error (insert the slave table, this value exists in the master table)	An error is reported for the foreign key constraint (the primary table is deleted, and the data row referenced in the secondary table exists)	Foreign key constraint error (modify the primary table and reference this data row from the table)