MySQL's first experience of MyBatis 3

Keywords: Database MySQL Mybatis SQL

1, Annotation development

1.1 comparison between annotations and xml

xml: as a configuration file

  • Advantages: it can extract content (decoupling), clear configuration, convenient positioning and easy to find
  • Disadvantages: complicated configuration

Note: xml development can be replaced

  • Advantages: fast development speed and no cumbersome configuration
  • Disadvantages: scattered in all parts of java code, inconvenient to maintain

Spring boot uses pure annotation development.

1.2 complete the single table CRUD with notes

Manual mapping: manual mapping is required if the database and entity classes are inconsistent
@Results configuration of manual mapping
@Results (value = {@ result() array})


public interface UserDao {
    //Query all
    @Select("select * from tbl_user")
    List<User> findAll();
    //Save user
    @Insert("insert into tbl_user(id,username,password,gender,email,telephone) values(#{id},#{username},#{password},#{gender},#{email},#{telephone})")
    int saveUser(User user);
    //Modify user
    @Update("update tbl_user set username=#{username},password=#{password},gender=#{gender},email=#{email},telephone=#{telephone} where id=#{id}")
    int updateUser(User user);
    //delete user
    @Delete("delete from tbl_user where id = #{id}")
    int deleteUser(Integer id);

     * @Result Equivalent to a field
     * Primary key, common field, foreign key field
     * @Result( column = "" , property = "" , id=true), Primary key
     * column = ""  Represents the column of the database
     * property = "" Attribute value of entity class
     * id=true This configuration is a primary key configuration
     * javaType: Used to specify types in java
     * jdbcType: Specifies the type of database
     * one: Used to establish many to one relationships
     * many: Used to establish a one to many relationship
	 * @ResultMap("myResult")  Use mapping configuration elsewhere
    @Select("select id,username as name,password,gender,email,telephone from tbl_user")
    @Results( id="myResult",value={
            @Result( column = "id" , property = "id" , id=true),
            @Result( column = "name" , property = "username"),
            @Result( column = "password" , property = "password"),
            @Result( column = "gender" , property = "gender"),
            @Result( column = "email" , property = "email"),
            @Result( column = "telephone" , property = "telephone")
    List<User> findAll2();

    @Select("select id,username as name,password,gender,email,telephone from tbl_user")
    List<User> findAll3();


1.3 multi table mapping using annotations

1.3.1 one to one


public interface AccountDao {
     * @Result(property = "Attribute of entity class ", javaType = type of attribute of entity class",
     *                 one = @One(select = "")
     *                 one Indicates one-to-one
     *                      select: Locate the fully qualified class name and method name of the interface of another sql statement
     *             )
    //Query all accounts
    @Select("select * from tbl_account ")
            @Result(property = "id" , column = "id" , id = true),
            @Result(property = "username" , column = "username" ),
            @Result(property = "password" , column = "password" ),
            @Result(property = "gender" , column = "gender" ),
            @Result(property = "email" , column = "email" ),
            @Result(property = "telephone" , column = "telephone" ),
            @Result(property = "user" ,javaType = User.class ,
            one = @One(select = "") ,column = "uid")})
    List<Account> findAll();

1.3.2 one to many


public interface UserDao {
     * Multi table configuration
     *  @Result()
     *      property = "accounts" Encapsulated properties
     *      javaType = Type of collection
     *      There is no need to declare generic types. (xml can also be omitted) - > the underlying object can be instantiated through generic reflection
     *       many = @Many One to many configuration
     *       select : "" Fully qualified class name of the interface. Method name
     *       column = "id" id passed in when calling interface method
    //Method of querying users
    @Select("select * from tbl_user")
            @Result(column = "id", property = "id" , id = true),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(column = "gender", property = "gender"),
            @Result(column = "email", property = "email"),
            @Result(column = "telephone", property = "telephone"),
            @Result(property = "accounts" , javaType = List.class ,
            many = @Many(select = "") , column = "id")})
    List<User> findAll();

1.3.3 lazy loading

Local configuration:

one = @One(select = "") ,column = "uid",fetchType=FetchType.LAZY)

many = @Many(select = "") , column = "id",fetchType=FetchType.LAZY)

In the actual development, the choice of pure annotation development, xml development or mixed development is determined according to the actual needs.

2, MySQL experience summary

2.1 SQL statement error reporting


Query the name of each region whose nationality is China and the total number of students is greater than 2, the total number of students and mathematics( math)Total score,
Mathematics( math)Average score, and according to mathematics( math)Flashback of average scores

Known table creation statements and data are as follows:

CREATE TABLE `student` (
`id` int(11) DEFAULT NULL COMMENT 'Primary key',
`name` varchar(20) DEFAULT NULL COMMENT 'name',
`age` int(11) DEFAULT NULL COMMENT 'Age',
`sex` varchar(5) DEFAULT NULL COMMENT 'Gender',
`address` varchar(100) DEFAULT NULL COMMENT 'region',
`math` int(11) DEFAULT NULL COMMENT 'Mathematics achievement',
`english` int(11) DEFAULT NULL COMMENT 'English achievement',
`nationality` varchar(10) DEFAULT NULL COMMENT 'nationality'

insert into
(1,'Jack Ma',55,'male','Hangzhou',66,78,'China'),
(3,'Jing Tao',55,'male','Hong Kong',56,77,'China'),
(5,'Liu Qing',20,'male','Hunan',86,NULL,'China'),
(6,'Lau Andy',57,'male','Hong Kong',99,99,'China'),
(7,'Madder',22,'female','Hong Kong',99,99,'China'),
(9,'Tang Monk',25,'male','Hangzhou',87,78,'China'),
(10,'fellow students',18,'male','Shenzhen',100,66,'China'),
(11,'Xiao Ming',22,'male','Nanjing',58,78,'China'),
(14,'Meng de',23,'female','Nanjing',88,88,'China'),
(15,'Tom',33,'male','New York',89,NULL,'U.S.A'),

Write SQL statements:

SELECT address, COUNT(*) AS total,SUM(math),AVG(math) AS avgmath
FROM student
GROUP BY address
WHERE nationality ='China' AND total> 2

Error message:

Problem analysis:

If you find conditions in your requirements, use them when you are used to them where For conditional filtering.
above SQL Used in statements where total>2 Total number of filters, and total Is the total number calculated after grouping according to regions.
Wrong use where The above error report is triggered

SQL syntax stipulates that the data obtained after grouping cannot be filtered by where, but by having


Change where to having and SQL to the following:

SELECT address, COUNT(*) AS total,SUM(math),AVG(math) AS avgmath
FROM student
WHERE nationality ='China'
GROUP BY address
HAVING total> 2


  1. First, execute the FROM clause to retrieve records FROM the student table;
  2. If there is a WHERE, execute the WHERE clause to filter out all qualified records in the student table;
  3. Execute the GROUP BY clause to group the student table by address;
  4. Calculate COUNT(*) to calculate the total number of records with the same address, SUM(math) to calculate the total score of mathematics in records with the same address, and AVG(math) to calculate the average score of mathematics in records with the same address;
  5. Execute the HAVING clause to filter out groups with COUNT(*) greater than 2;
  6. Extract the four fields of address, ` ` COUNT(*), SUM(math) and AVG (Math) to generate a new result set;
  7. Execute the ORDER BY clause and arrange the result set in step 6 in reverse ORDER BY AVG(math) field

2.1 jdbc error reporting

The Java code is as follows:

When executing the above code, the following error is reported on the console:

Through the error message above, we found an sql exception:

... MySQL server version for the right syntax to use near '?,'admin')' at line 1

You can also see that the location of the exception is in line 32 of the java code

Analyze the cause of the problem:

The process of executing sql statements using the Preparedstatement object:

  1. Register the driver and get the Connection object
    Connection connection = JdbcUtil.getConnection();
  2. Define sql
    It should be noted that the sql here has a placeholder, that is, the sql cannot be executed at present
    String sql = "insert into user values(null,?,?,?)";
  3. Gets the object PreparedStatement that executes the sql statement
    Note that after this sentence is executed, the sql statement has actually been sent to the database. The database will compile it (but not execute it), and then return the PreparedStatement object corresponding to the current sql statement
    PreparedStatement pstmt = connection.prepareStatement(sql);
  4. Assign a value to "in sql" in the PreparedStatement object
    Note that the position starts with 1
    pstmt.setString(1, "Xiaolan");
    pstmt.setString(2, "admin");
    pstmt.setFloat(3, 100f);
  5. Execute sql and accept the returned results
    When this method is executed, the database will actually execute sql statements
    int i = pstmt.executeUpdate();
    Note that the sql statement has been passed to the database and compiled, so there is no need to pass it again
    If the SQL statement is passed, it will directly execute the SQL statement passed here, and the SQL statement is marked with "and cannot be executed, so an error is reported. If you have to write SQL here, you can write a runnable SQL, as follows:
    pstmt.executeUpdate("insert into user values(null, 'Xiaolan', '22','admin22','100'));
    At this time, the database will directly execute the sql statement, and the above assignment is useless. The sql injection problem comes back
  6. Release resources

Final solution:

Modify line 32

int i = pstmt.executeUpdate();

Posted by aff on Tue, 30 Nov 2021 23:19:57 -0800