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})

Interface:

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")
    @ResultMap("myResult")
    List<User> findAll3();

}

1.3 multi table mapping using annotations

1.3.1 one to one

Interface:

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 namespace.id interface of another sql statement
     *             )
     */
    //Query all accounts
    @Select("select * from tbl_account ")
    @Results(value={
            @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 = "com.baidu.dao.UserDao.findUserByUid") ,column = "uid")})
    List<Account> findAll();
}

1.3.2 one to many

Interface:

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 : "namespace.id" 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")
    @Results(value={
            @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 = "com.baidu.dao.AccountDao.findAccountByUid") , column = "id")})
    List<User> findAll();
}

1.3.3 lazy loading

Local configuration:

one = @One(select = "com.baidu.dao.UserDao.findUserByUid") ,column = "uid",fetchType=FetchType.LAZY)

many = @Many(select = "com.baidu.dao.AccountDao.findAccountByUid") , 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

Requirements:

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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into
`student`(`id`,`name`,`age`,`sex`,`address`,`math`,`english`,`nationality`)
values
(1,'Jack Ma',55,'male','Hangzhou',66,78,'China'),
(2,'Huateng',45,'female','Shenzhen',98,87,'China'),
(3,'Jing Tao',55,'male','Hong Kong',56,77,'China'),
(4,'Liuyan',20,'female','Hunan',76,65,'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'),
(8,'Demacia',18,'male','Nanjing',56,65,'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'),
(12,'Xingchi',50,'male','Shenzhen',77,88,'China'),
(13,'Menda',22,'female','Hangzhou',66,66,'China'),
(14,'Meng de',23,'female','Nanjing',88,88,'China'),
(15,'Tom',33,'male','New York',89,NULL,'U.S.A'),
(16,'jerry',25,'male','Houston',85,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
ORDER BY avgmath DESC

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

Solutions:

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
ORDER BY avgmath DESC

Summary:

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
   JdbcUtil.close(pstmt,connection)

Final solution:

Modify line 32

int i = pstmt.executeUpdate()；