Mybatis 2. SQL statement ing file (2) addition, deletion, modification, query, parameter, and Cache

Ii. SQL statement ing file (2) add, delete, modify, query, parameters, and Cache

 

 

Mybatis 1. Introduction and configuration of mybatis + spring + MySql

Mybatis 2. SQL statement ing file (1) resultmap

Mybatis 2. SQL statement ing file (2) addition, deletion, modification, query, parameter, and Cache

Mybatis 3. dynamic SQL statements

Mybatis learning 4. mybatis configuration file

 

2.2 select

A select element is very simple. For example:

<! -- Query students by id --> <select id = "getstudent" parametertype = "string" resultmap = "studentresultmap"> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. class_id from student_tbl st where St. student_id =#{ studentid} </SELECT>

 

This statement is called 'getstudent. It has a string parameter and returns a studententity object.
Note that the parameter ID is # {studentid }.

 

Select statement attribute configuration details:

Attribute	Description	Value	Default
ID	The unique identifier in this mode, which can be referenced by other statements.
Parametertype	Complete class name or alias of the parameter passed to this statement
Resulttype	The full name or alias of the return value type. Note: For a set, enter the full class name or alias of the Set item, instead of the Class Name of the set. (Resulttype and resultmap cannot be used together)
Resultmap	The name of the referenced external resultmap. Result set ing is the most powerful feature in mybatis. Many complex mappings can be easily solved. (Resulttype and resultmap cannot be used together)
Flushcache	If it is set to true, the cache is cleared every time the statement is called. The default value of the SELECT statement is false.	True | false	False
Usecache	If it is set to true, The result set of the statement will be cached. The default value of the SELECT statement is false. True | False false. Timeout sets the maximum time for the drive to wait for a response before an exception is thrown. The default value is no value, which is determined by the drive.	True | false	False
Timeout	Sets the maximum time for the drive to wait for a response before an exception is thrown. The default value is no value, which is determined by the drive.	Positive Integer	Not Set
Fetchsize	After a value is set, the drive returns the result after the number of result sets reaches this value. The default value is no value, which is determined by the driver.	Positive Integer	Depends on the drive
Statementtype	Statement, preparedstatement, callablestatement. Prepared and callable statements	Statement Prepared Callable	Prepared
Resultsettype	Forward_only, scroll_sensitive, scroll_insensitive Only forwarding, scroll sensitive, case-insensitive scrolling	Forward_only Scroll_sensitive Scroll_insensitive	Depends on the drive

 

 

2.3 Insert

A simple insert statement:

<! -- Insert student --> <insert id = "insertstudent" parametertype = "studententity"> insert into student (student_id, student_name, student_sex, primary, class_id) values (# {studentid }, # {studentname}, # {studentsex}, # {studentbirthday}, # {classentity. classid}) </Insert>

 

 

 

Insert can use the automatically generated primary key policy supported by the database, set usegeneratedkeys = "true", and then set the keyproperty to the corresponding column. For example, the studententity above uses auto-generated to generate a primary key for the ID column.
You can also use the selectkey element. The following example uses MySQL database nextval ('student ') as a user-defined function to generate a key.

<! -- Insert a student's automatic primary key --> <insert id = "insertstudentautokey" parametertype = "studententity"> <selectkey keyproperty = "studentid" resulttype = "string" Order = "before"> select nextval ('student ') </selectkey> insert into values (student_id, student_name, student_sex, role, class_id) values (# {studentid}, # {studentname}, # {studentsex}, # {studentbirthday }, # {classentity. classid}) </Insert>

 

 

Insert statement attribute configuration details:

Attribute	Description	Value	Default
ID	The unique identifier in this mode, which can be referenced by other statements.
Parametertype	Complete class name or alias of the parameter passed to this statement
Flushcache	If it is set to true, the cache is cleared every time the statement is called. The default value of the SELECT statement is false.	True | false	False
Usecache	If it is set to true, The result set of the statement will be cached. The default value of the SELECT statement is false. True | False false. Timeout sets the maximum time for the drive to wait for a response before an exception is thrown. The default value is no value, which is determined by the drive.	True | false	False
Timeout	Sets the maximum time for the drive to wait for a response before an exception is thrown. The default value is no value, which is determined by the drive.	Positive Integer	Not Set
Fetchsize	After a value is set, the drive returns the result after the number of result sets reaches this value. The default value is no value, which is determined by the driver.	Positive Integer	Depends on the drive
Statementtype	Statement, preparedstatement, callablestatement. Prepared and callable statements	Statement Prepared Callable	Prepared
Usegeneratedkeys	Tell mybatis to use the getgeneratedkeys method of JDBC to obtain the primary key generated by the database itself (MySQL, sqlserver, etc. RDS automatically generates fields ). Default Value: false	True | false	False
Keyproperty	Identifies the value returned by the key set by mybatis to getgeneratedkeys, or use a selectkey for the insert statement Child element.

 

 

Selectkey statement attribute configuration details:

 

Attribute	Description	Value
Keyproperty	The attribute to be set for the selectkey statement to generate the result.
Resulttype	The type of the generated result. mybatis allows the use of basic data types, including string and Int.
Order	You can set it to before or after. If it is set to before, it selects the primary key, then sets the keyproperty, and then executes the insert statement. If it is set to after, it runs the insert statement before running the selectkey statement. Generally, the sequence mechanism embedded in the database (like Oracle) is called in the insert statement.	Before After
Statementtype	As shown in the preceding figure, mybatis supports statement, prepared, and callable statements, corresponding to statement, preparedstatement, and callablestatement responses.	Statement Prepared Callable

 

 

2.4 update and delete

A simple update:

<! -- Update student information --> <update id = "updatestudent" parametertype = "studententity"> Update student_tbl set student_tbl.student_name =#{ studentname}, student =#{ studentsex }, student_tbl.student_birthday =#{ studentbirthday}, student_tbl.class_id =#{ classentity. classid} Where student_tbl.student_id =#{ studentid}; </update>

 

A simple delete:

<! -- Delete student --> <Delete id = "deletestudent" parametertype = "studententity"> Delete from student_tbl where student_id =#{ studentid} </delete>

 

Configuration details of update and delete statement attributes:

 

Attribute	Description	Value	Default
ID	The unique identifier in this mode, which can be referenced by other statements.
Parametertype	Complete class name or alias of the parameter passed to this statement
Flushcache	If it is set to true, the cache is cleared every time the statement is called. The default value of the SELECT statement is false.	True | false	False
Usecache	If it is set to true, The result set of the statement will be cached. The default value of the SELECT statement is false. True | False false. Timeout sets the maximum time for the drive to wait for a response before an exception is thrown. The default value is no value, which is determined by the drive.	True | false	False
Timeout	Sets the maximum time for the drive to wait for a response before an exception is thrown. The default value is no value, which is determined by the drive.	Positive Integer	Not Set
Fetchsize	After a value is set, the drive returns the result after the number of result sets reaches this value. The default value is no value, which is determined by the driver.	Positive Integer	Depends on the drive
Statementtype	Statement, preparedstatement, callablestatement. Prepared and callable statements	Statement Prepared Callable	Prepared

 

SQL 2.5

The SQL element is used to define a reusable SQL statement segment for other statements to call. For example:

<! -- Reuse SQL statements to query all fields in the student table --> <SQL id = "selectstudentall"> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. class_id from student_tbl st </SQL>

 
In this way, the SELECT statement can be directly referenced and used, and the preceding SELECT statement is changed:

<! -- Query students by id --> <select id = "getstudent" parametertype = "string" resultmap = "studentresultmap"> <include refID = "selectstudentall"/> where St. student_id =#{ studentid} </SELECT>

 

2.6 Parameters

Parameters have been used in many areas above, such as query, modification, deletion conditions, insertion, and modification of data. The basic data types that mybatis can use and the complex data types in Java.
Basic data types, such as string, Int, and date.
However, the basic data type can only provide one parameter. Therefore, you must use the Java object class or map type as the parameter type. You can directly obtain its attributes through.

2.6.1 basic type parameters

Retrieve the student list based on the admission time:

<! -- Query the student list based on the admission time --> <select id = "getstudentlistbydate" parametertype = "date" resultmap = "studentresultmap"> select * From student_tbl st left join class_tbl CT on st. class_id = CT. class_id where Ct. class_year =#{ classyear}; </SELECT>

 

List<StudentEntity> studentList = studentMapper.getStudentListByClassYear(StringUtil.parse("2007-9-1"));for (StudentEntity entityTemp : studentList) {System.out.println(entityTemp.toString());}

 

2.6.2java object type parameters

The student list is retrieved Based on the name and gender. Use the object class for parameters:

<! -- Query the student list, like name, = gender, parameter entity type --> <select id = "getstudentlistwhereentity" parametertype = "studententity" resultmap = "studentresultmap"> select * From student_tbl st where St. student_name like Concat ('%', # {studentname}), '%') and St. student_sex =#{ studentsex} </SELECT>

 

Studententity entity = new studententity (); entity. setstudentname ("Li"); entity. setstudentsex ("male"); List <studententity> studentlist = studentmapper. getstudentlistwhereentity (entity); For (studententity entitytemp: studentlist) {system. out. println (entitytemp. tostring ());}

 

2.6.3map Parameters

The student list is retrieved Based on the name and gender. Use Map for parameters:

<! -- Query student list, = gender, parameter map type --> <select id = "getstudentlistwheremap" parametertype = "map" resultmap = "studentresultmap"> select * From student_tbl st where St. student_sex =#{ sex} and St. student_sex =#{ sex} </SELECT>

 

Map <string, string> map = new hashmap <string, string> (); map. put ("sex", "female"); map. put ("name", "Li"); List <studententity> studentlist = studentmapper. getstudentlistwheremap (MAP); For (studententity entitytemp: studentlist) {system. out. println (entitytemp. tostring ());}

 

 

 

 

2.6.4 multi-parameter implementation

To input multiple parameters, add the @ Param annotation to the interface parameters. An example is provided:
Interface Syntax:

public List<StudentEntity> getStudentListWhereParam(@Param(value = "name") String name, @Param(value = "sex") String sex, @Param(value = "birthday") Date birthdar, @Param(value = "classEntity") ClassEntity classEntity);

 

SQL statement:

<! -- Query the student list, like name, = gender, = birthday, = Class, multi-parameter method --> <select id = "getstudentlistwhereparam" resultmap = "studentresultmap"> select * From student_tbl st <where> <if test = "name! = NULL and name! = ''"> St. student_name like Concat ('%', # {name}), '%') </If> <if test = "sex! = NULL and sex! = ''"> And St. student_sex =#{ sex} </If> <if test = "Birthday! = NULL "> and St. student_birthday =#{ birthday} </If> <if test =" classentity! = NULL and classentity. classid! = NULL and classentity. classid! = ''"> And St. class_id =#{ classentity. classid} </If> </where> </SELECT>

 

 

Query:

List<StudentEntity> studentList = studentMapper.getStudentListWhereParam("", "",StringUtil.parse("1985-05-28"), classMapper.getClassByID("20000002"));for (StudentEntity entityTemp : studentList) {System.out.println(entityTemp.toString());}

 

 

 

2.6.5 string Generation Method

By default, the # {} syntax will prompt mybatis to generate the preparedstatement attribute and use the preparedstatement parameter (= ?) . It is quick and secure as much as possible and is often used. However, sometimes you may want to directly import unchanged strings into SQL statements. For example, for order by, you may use order by $ {columnname}, but mybatis will not modify or circumvent this string.
Note: It is very insecure to receive and apply a user input to a statement that has not been changed. This allows the user to implant and destroy the code. Therefore, either the field should not be allowed to be input by the customer, or you can directly check the validity of the field.

 

 

2.7 Cache

Mybatis contains a powerful, configurable, And customizable cache mechanism. The cache Implementation of mybatis 3 has been improved, which is both powerful and easy to configure. By default, the cache is not enabled. Besides session cache, it can improve performance and solve global dependencies. To enable Level 2 caching, you only need to add a simple line to the SQL ing file: <Cache/>

The function of this simple statement is as follows:

1. All select statements in the ing file will be cached.
2. All insert, update, and delete statements in the ing file will clear the cache.
3. cache is recycled using the "rarely used recently" Algorithm
4. the cache will not be cleared at the specified time.
5. Each cache can store 1024 lists or reference objects (no matter what the query results are ).
6. the cache will be used as a "read/write" cache, which means that the obtained objects are not shared and secure for callers. No other calls
7. Potential modification by the operator or thread.

 

For example, create a FIFO cache so that the cache is cleared once every 60 seconds, store the results of 512 objects or list references, and the returned results are read-only. Because modification by the two callers in the unused thread may cause a reference conflict.

<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"></cache>

 

You can also share the same cache configuration or instance in different namespaces. In this case, you can use cache-ref to reference another cache.

<cache-ref namespace="com.liming.manager.data.StudentMapper"/>

 

Cache statement attribute configuration details:

Attribute	Description	Value	Default Value
Eviction	Cache Policy: LRU-least recently used method: removes objects that have not been used for a long period of time. Fifi-first-in-first-out: removes an earlier object from the queue. Soft-soft reference: removes objects using the garbage collection mechanism based on soft reference rules. Weak-weak reference: based on weak reference rules, the garbage collection mechanism is used to forcibly remove objects.	LRU Fifi Soft Weak	LRU
Flushinterval	Represents a reasonable total time in milliseconds. By default, this parameter is not set. Therefore, you can only call the statement to clear the task with no interval.	Positive Integer	Not Set
Size	Size of the cached object	Positive Integer	1024
Readonly	The read-only cache returns the same instance to all callers. Therefore, it cannot be modified, which can greatly improve the performance. Writable cache will pass through the sequence Returns a copy of a cached object. This is slow but secure. Therefore, the default value is false.	True | false	False

 

 

 

Mybatis 1. Introduction and configuration of mybatis + spring + MySql

Mybatis 2. SQL statement ing file (1) resultmap

Mybatis 2. SQL statement ing file (2) addition, deletion, modification, query, parameter, and Cache

Mybatis 3. dynamic SQL statements

Mybatis learning 4. mybatis configuration file