SoFunction
Updated on 2025-03-11

Mybatis dynamically generates SQL statement implementation example

In Java programming, IWe often need to pass data to SQL queries, especially when dynamically generating SQL statements, which are usually used.MapThis kind of collection class.MapMultiple key-value pairs can be passed to placeholders in SQL statements to complete the function of dynamic parameter binding.Let’s explain the knowledge points in the figure in detail below.

1. Basic usage of Map

Mapis a data structure in the Java collection framework, which uses key-value pairs (key-value) stores data in the form of .MapThe keys in   are unique and cannot be repeated, while the values ​​can be repeated. Commonly used implementation classes includeHashMap、TreeMapwait.

Sample code：

Map<String, Object> map = new HashMap<>();
("k1", "1111");
("k2", "BYD Han");
("k3", 10.0);
("k4", "2020-11-11");
("k5", "tram");

Here we created oneHashMap, and passputMethod to store some data intoMap. The key isStringType, value isObjectType means that different types of data can be stored (such as strings, numbers, dates, etc.).

2. Use of placeholder #{}

When executing SQL statements, in order to prevent SQL injection and simplify the code,We often use placeholders to pass SQL parameters dynamically in.（ ?is a placeholder in JDBC, used to precompile parameters in SQL statements. When using JDBC, SQL statements are usually precompiled.?Used to represent a position, replaced by specific parameters when executed.）Placeholder#{}This syntax is mainly used for some persistence frameworks, such as MyBatis.

SQL statement example：

insert into t_car(id,car_num,brand,guide_price,produce_time,car_type) 
values(null, #{k1}, #{k2}, #{k3}, #{k4}, #{k5});

In this SQL statement,#{k1}、#{k2}Such placeholders will be replaced withMapThe corresponding value in  . The frame will look for the placeholder keys.MapThe values ​​in  and dynamically replace them.If the key does not exist, returnnull。This statement is usually configured in an XML file that is usually used for SQL mappings for MyBatis

Notice：

  • ifMapNo value corresponding to a placeholder is provided in  , and a query may be inserted during execution.null, which can lead to unexpected behavior.
  • Use placeholders#{}It can prevent SQL injection because the framework will automatically preprocess when executed.

3. The correspondence between the Map key name and the database field name

In order to make the code easier to read and more convenient to maintain, we usually recommend that you putMapThe key name in the database field name is consistent. In this way, not only can parameters be mapped intuitively, but problems caused by inconsistent key names can also be avoided.

("carNum", "1111");
("brand", "BYD Han 2");
("guidePrice", 10.0);
("produceTime", "2020-11-11");
("carType", "tram");

In this example, we willMapChange the key name tocarNum、brandetc., which is consistent with the column name of the database table. This makes the code easier to understand and maintain.

SQL statements：

insert into t_car(id,car_num,brand,guide_price,produce_time,car_type) 
values(null, #{carNum}, #{brand}, #{guidePrice}, #{produceTime}, #{carType});

POJO class implements dynamic parameter transmission

1. POJO class and SQL placeholder pass

POJO（Plain Old Java Object）Refers to ordinary Java objects, which are usually used as data transfer objects. In Java applications, we can pass parameters in SQL statements through the properties of the POJO class.

Car car = new Car(null, "3333", "BYD Qin", 30.0, "2020-11-11", "New Energy");

In this example,CarIt is a POJO class that contains some attributes of vehicle information. By instantiating this class, we can pass specific values ​​into placeholders in SQL statements.

2. Use of placeholder #{}

In persistence frameworks such as MyBatis, use placeholders#{}To inject the POJO property value into the SQL statement.Braces{}The content filled in is the attribute name of the POJO class(Strictly speaking: if you use P0J0 object to pass the value, this {} writes the method name of the get method, then remove the get, and then put the remaining initial letters in the lowercase of the word.）

SQL statement example：

insert into t_car(id,car_num,brand,guide_price,produce_time,car_type) 
values(null, #{carNum}, #{brand}, #{guidePrice}, #{produceTime}, #{carType});

3. Rules for placeholders and attributes

MyBatis will automatically find the corresponding one based on the attribute name in the POJO class.gettermethod. If no corresponding one is provided in the POJO classgetterMethod, an error will be reported.

Error Example：

There is no getter for property named 'xyz' in 'class '

This is because MyBatisTry to passgetXyz()Method to obtainxyzThe value of the property, but this method is not defined in the POJO class. To avoid this error, we need to provide each attribute'sgetterandsettermethod.

4. How to solve the problem of not having a getter method?

If there is no correspondinggetterIn the case of methods, you can manually add the corresponding one to the POJO classgetterMethod to solve it. for example:

public String getXyz() {
    return xyz;
}

5. Rules for use of placeholder #{}

Through this error example, we can summarize the rules for using placeholders: MyBatis is called through reflection mechanism.gettermethod.That is, in#{}What is written in the placeholder (such as#{carNum}) does not directly correspond to the attribute name of the POJO, but the attribute namegettermethod.

rule：

  • Placeholder#{}The content in the first letter is lowercase.
  • Automatically call attributes within the placeholdergetterMethod, so naming should comply with Java's naming specifications. For example:
    • getUsername()The corresponding placeholder is#{username}
    • getEmail()The corresponding placeholder is#{email}

Placeholder#{}The content in  is indeed related to the properties of the POJO class, but MyBatis actually passesCalling the POJO classgettermethodto get these values ​​instead of directly accessing the properties themselves. Therefore, to ensure that MyBatis can correctly map SQL parameters, the POJO classgetterThe method must match the name in the placeholder.

Delete DELETE operation:

1. Implementation of deletion operation

int count = ("deleteById", 59);

This line of code is displayed through()Method to perform the delete operation, where:

  • sqlSession: Session object in MyBatis, allowing us to interact with the database.
  • "deleteById": This is a unique identifier for the SQL statement defined in the Mapper XML file and is used to specify the deletion operation.
  • 59: This is the parameter to be passed to the SQL statement, that is, the ID of the record to be deleted

2. SQL statements in MyBatis Mapper file

<delete >
    delete from t_car where id = #{fdsfd}
</delete>

This is the deletion operation defined in the Mapper XML file of MyBatis. The specific explanation is as follows:

  • <delete >: This is a method to define a delete operation.idis the unique identifier of this method, with the namedeleteById, with the call in Java codedeleteByIdCompliance.
  • delete from t_car where id = #{fdsfd}: This is a standard SQL delete statement, meaning from the tablet_carDelete inidfor#{fdsfd}record.
    • #{fdsfd}: This is the parameter placeholder of MyBatis, indicating the parameter to be passed (passed in Java code59) Injected into this position.fdsfdIt is a parameter name and can be defined as needed.

3. Description of placeholders

Note: If there is only one placeholder, then you can do whatever you want in the braces of #{}. But it is best to know the name.

This explains#{}is a placeholder for MyBatis, used to bind parameters. Although in some cases, parameter names can be used at will (e.g.fdsfd), but for the readability and maintenance of the code, it is recommended to use meaningful names, such asid。

For example, change to:

<delete >
    delete from t_car where id = #{id}
</delete>

This is more intuitive and easy to understand.

4. Overall process summary

  • In Java code, we pass("deleteById", 59)To call the defined in the Mapper filedeleteByIdDelete method and passid = 59。
  • MyBatis will59Replace todelete from t_car where id = #{id}In the SQL statement, generate the final SQL statementdelete from t_car where id = 59And execute, finally deletet_carIn the tableidfor59record.

Update operation:

1. SQL statement for update operation

<update >
    update t_car set
        car_num=#{carNum},
        brand=#{brand},
        guide_price=#{guidePrice},
        produce_time=#{produceTime},
        car_type=#{carType}
    where
        id = #{id}
</update>

This is an update operation defined in the Mapper XML file of MyBatis. The explanation is as follows:

  • <update >: Define an update operation,idIt is a unique identifier, used hereupdateById, consistent with the method name in Java code.
  • update t_car set ... where id = #{id}: This is an update statement for SQL, with the intention oft_carA record in the table is updated, specifically throughidTo locate the record and then update multiple fields in the table.

Used here#{}As a placeholder for MyBatis, it is similar to the previous deletion operation. It replaces the parameter with the actual value when executed. Specific field description:

  • #{carNum}: Car number
  • #{brand}:brand
  • #{guidePrice}: Guide price
  • #{produceTime}: Production date
  • #{carType}: Car type
  • #{id}: The unique identifier of the record, that is, the car record to be updatedid

2. Update operations in Java code

Car car = new Car(4L, "9999", "Camry", 30.3, "1999-11-10", "Fuel Car");
int count = ("updateById", car);

Pass herenew Car()Created a carCarObject, contains all attributes to be updated. Each attribute corresponds to the placeholder in the Mapper XML.

  • 4L: It means the carid, that is, in SQL statementidThe record to be updated in the field.（nullCannot assign values ​​to basic typeslong, but can be assigned toLongSuch a packaging class, so use)
  • "9999": CorrespondingcarNum, car number.
  • "Camry": Correspondingbrand, car brand.
  • 30.3: CorrespondingguidePrice, guide price.
  • "1999-11-10": CorrespondingproduceTime, production date.
  • "Fuel Car": CorrespondingcarType, car type.

Then call("updateById", car);Perform update operation. MyBatis willcarThe attributes of the object are passed to the placeholder in the corresponding SQL statement, generating the final SQL statement and performing updates.

3. Detailed process description

MyBatis will be based on the Java codecarThe property of the object, replaces the placeholder in the Mapper XML, and generates the final SQL statement.

AssumptioncarThe object'sidIt is 4, then the generated SQL statement is roughly as follows

update t_car set
    car_num='9999',
    brand='Camry',
    guide_price=30.3,
    produce_time='1999-11-10',
    car_type='Fuel Car'
where
    id = 4;

After executing this SQL statement,t_carIn the tableidA record of 4 will be updated to a new value.

After the update,()The method will return the number of affected rows, i.e.count。

select search operation

1. The select tag in MyBatis

effect: MyBatis useselectTags to write SQL query statements that define how to get data from the database. In this example, it is used toidField queryt_carRecords in the table.

SQL statement explanation：

<select  resultType="">
    select * from t_car where id = #{id}
</select>

id attribute：is a unique identifier that indicates the ID of this SQL statement in the MyBatis configuration. When you call in the code("selectById", parameter)When MyBatis will find the corresponding SQL statement based on this ID.

SQL Query：select * from t_car where id = #{id}is an actual SQL statement.#{id}is a dynamic SQL placeholder for MyBatis, which will pass the parameters you enter at runtime (such as1) Replace with the actual query conditions.

  • #{}is MyBatis syntax, used to get values ​​from passed parameters to prevent SQL injection.
  • select *It is to query all columns, you can replace them with specific column names as needed (for example:select id, name, brand）。

2. resultType attribute

effect：resultTypeThe attribute tells MyBatis what kind of Java object the result of the query should be mapped into. That is, after obtaining data from the database,MyBatis will be based onresultTypeThe type specified in  create an object of that type and encapsulate the query results into this object.

Use in this example：

resultType=""

hereresultType=""DesignatedCarThe class is the type of mapping object for the query result. That is, the queryt_carThe records in the table will be encapsulated asCarInstance of class.

Fully qualified class name:yesCarThe fully qualified class name of a class includes the package name and class name. This is recommended by MyBatis because this ensures that class name conflicts are avoided and that MyBatis can find the correct class.

The mechanism of action of resultType：

The query result (i.e. a record in the database) is converted into a Java object. MyBatis will be based on the column names in the database table andCarThe attribute name of the class is matched. If the column name is the same as the attribute name, MyBatis will automatically assign the column value in the query result to the corresponding attribute.

For example: Assumptiont_carThe table has the following columns:

  • id，name，brand
  • CorrespondingCarThe attributes of the class are:id，name，brand, MyBatis will automatically select theidAssign the value of the column toCarIn the classidAttributes, and so on.

Things to note：

If the column names in the database are inconsistent with the attribute names in the Java class, you need to use an alias (AS)orresultMapPerform manual mapping.

1. Assumption database tablet_car

CREATE TABLE t_car (
    car_id INT PRIMARY KEY,
    car_name VARCHAR(50),
    car_brand VARCHAR(50)
);

In this table, the column name iscar_id, car_name, andcar_brand。

Java ClassCar

public class Car {
    private int id;
    private String name;
    private String brand;

    // Getters and setters
    public int getId() {
        return id;
    }

    public void setId(int id) {
         = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
         = name;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
         = brand;
    }
}

In Java classCarIn  , the attribute names areid, name, andbrand, obviously with database tablet_carThe column names are inconsistent.

Solution: UseresultMapPerform manual mapping

Because the column name and attribute name are inconsistent, use it directlyresultTypeIt cannot be mapped automatically, and we can useresultMapPerform manual mapping.

3. MyBatis mapping configuration: alias

<select  resultType="">
    select car_id as id, car_name as name, car_brand as brand
    from t_car
    where car_id = #{id}
</select>

In this configuration:

  • car_id as id: maps the query results of the car_id column to id, so as to correspond to the id attribute of the Java class Car.
  • car_name as name: Map the query results of the car_name column into name, corresponding to the name attribute in the Car class.
  • car_brand as brand: Map the query results of the car_brand column into brand, corresponding to the brand attribute in the Car class.

SQL Query：select * from t_car where car_id = #{id},pass#{id}to pass parameters in Java methods.

so,By usingASSet an alias, you can directly correspond to the column name of the database with the attribute name of the Java class in SQL statements., without modifying the database or Java classes.

3. SelectOne method

effect：selectOneis a method provided by MyBatis to execute queries that return a single record. This method can be used when we determine that the result of the query is unique (for example, query the primary key).If the query result returns multiple records, MyBatis will throw an exception (TooManyResultsException）。

Usage explanation：

Car car = ("selectById", 1);

sqlSessionIt is the session object in MyBatis that operates the database, similar to the one in JDBCConnectionObject.

selectOne("selectById", 1): CalledselectByIdThis SQL query and passes parameters1. this1Will replace the SQL#{id}Placeholder, the final execution of SQL will be:

select * from t_car where id = 1;

The result returned will be aCarObjects, stored in variablescarmiddle.

select check all data

sql statement

<select  resultType="">
    select
        id,
        car_num as carNum,
        brand,
        guide_price as guidePrice,
        produce_time as produceTime,
        car_type as carType
    from t_car
</select>

In this query, an alias is used to map column names in the database table to the Java classCarAttribute name in  :

  • car_num as carNum: Map the car_num column in the database to the carNum property in the Car class.
  • guide_price as guidePrice: Map the guide_price column in the database to the guidePrice attribute in the Car class.
  • produce_time as produceTime: Map the produce_time column in the database to the produceTime property in the Car class.
  • car_type as carType: Map the car_type column in the database to the carType property in the Car class.

The Java code inselectListCall

List<Car> cars = ("selectAll");

  • selectListmethod: MyBatisselectListMethod is used to return aListCollection, each element in the collection is a Java object for every record queryed from the database. In this example, the query result will be encapsulated as oneCarList of objects.

  • resultTypeAttributes: In SQL configuration,resultTypeThe Java class type that is still used to specify encapsulated query results, here is, indicating that the query result will be encapsulated asCarObject of class.

Things to note

  • resultTypeSpecified type: It should be noted thatresultTypeIt is not a type that specifies the set, but a type of each element in the set.In this exampleresultType="", which means that MyBatis will encapsulate each record of the query result asCar Object.

  • selectListReturned results: CallselectList("selectAll")Will return oneListCollection, each element in the set corresponds to a record in the query result and will be encapsulated as a specifiedCarObject.

This is the end of this article about the implementation example of mybatis dynamically generate sql statements. For more related contents of mybatis dynamically generate sql statements, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!