tags: Mybatis

SQL > SQL > SQL > SQL

What is dynamic SQL? Review the case of multi-conditional queries in the SSH project we wrote earlier, as shown below

When we first started, we needed to determine whether the SQL was already conditional in the Controller, because the SQL statement needed to be concatenated…. It would be very easy to make mistakes.

The following code, if there are more than one condition, then the splicing is very easy to error!


  public String listUI(a) {

        // Query statement
        String hql = "FROM Info i ";
        List<Object> objectList  = new ArrayList<>();

        // Check whether the query is conditional based on whether info is null. If info is empty, all are queried.
        if(info ! =null) {
            if (StringUtils.isNotBlank(info.getTitle())) {
                hql += "where i.title like ?";
                objectList.add("%" + info.getTitle() + "%");
            }
        }
        infoList = infoServiceImpl.findObjects(hql,objectList);
        ActionContext.getContext().getContextMap().put("infoTypeMap", Info.INFO_TYPE_MAP);
        return "listUI";
    }
Copy the code

Later, we decided this was not a good idea, so we wrote a query helper class:


package zhongfucheng.core.utils;

import java.util.ArrayList;
import java.util.List;

/** * Created by ozc on 2017/6/7. */
public class QueryHelper {

    private String fromClause = "";
    private String whereClause = "";
    private String orderbyClause = "";
    private List<Object> objectList;

    public static String ORDER_BY_ASC = "asc";
    public static String ORDER_BY_DESC = "desc";



    // The FROM clause occurs only once
    /** * build the FROM clause and set which table to query *@paramAClass Specifies the type the user wants to operate on@paramAlias alias * /
    public QueryHelper(Class aClass, String alias) {
        fromClause = " FROM " + aClass.getSimpleName() + "" + alias;
    }
    // Multiple conditions can be added to a WHERE clause, but the WHERE keyword appears only once
    /** * Construct the WHERE clause *@param condition
     * @param objects
     * @return* /
    public QueryHelper addCondition(String condition, Object... objects) {
        // If there are already characters, then the WHERE keyword already exists
        if (whereClause.length() > 0) {
            whereClause += " AND " + condition;
        } else {
            whereClause += " WHERE" + condition;
        }
        // When adding a query condition,? Value of the query condition
        if (objects == null) {
            objectList = new ArrayList<>();
        }

        for (Object object : objects) {
            objectList.add(object);
        }

        return this;
    }
    / * * * *@paramProperty Specifies the property * to be sorted@paramOrder is in ascending or descending order@return* /
    public QueryHelper orderBy(String property, String order) {

        // If there are already characters, then the ORDER keyword already exists
        if (orderbyClause.length() > 0) {
            orderbyClause += "," + property +"" + order;
        } else {
            orderbyClause += " ORDER BY " + property+"" + order;
        }
        return this;
    }

    /** * Returns the HQL statement */
    public String returnHQL(a) {
        return fromClause + whereClause + orderbyClause;
    }

    /** * get the argument list *@return* /
    public List<Object> getObjectList(a) {
        returnobjectList; }}Copy the code

That way, we don’t have to do the concatenation ourselves, just give our query helper class the concatenation.

If we use Mybatis, we can dispense with the query helper class. Because Mybatis internal has dynamic SQL function [dynamic SQL is automatic join SQL statement]!

A dynamic query


	<! SQL > select * from 'SQL';
	<! -- will automatically combine into a normal WHERE sentence -->
	<! --name value will be found in map -->

	<select id="findByCondition" resultMap="studentMap" parameterType="map">

		select * from students

		<where>
			<if test="name! =null">
				and name=#{name}
			</if>
			<if test="sal! =null">
				and sal &lt; #{sal}
			</if>
		</where>

	</select>
Copy the code

I found someone with less than $9,000


   public List<Student> findByCondition(String name,Double sal) throws Exception {
        // Get the connection object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            // Map file namespace. The ID of the SQL fragment can call the SQL in the corresponding mapping file
            /** * Since we have more than two arguments and only one Object argument collection in the method *, we use the Map collection to load our arguments */
            Map<String, Object> map = new HashMap();
            map.put("name", name);
            map.put("sal", sal);
            return sqlSession.selectList("StudentID.findByCondition", map);
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{ MybatisUtil.closeSqlSession(); }}public static void main(String[] args) throws Exception {
        StudentDao studentDao = new StudentDao();
        List<Student> students = studentDao.findByCondition(null.9000D);
        for (Student student : students) {
            System.out.println(student.getId() + "-" + student.getName() + "--"+ student.getSal()); }}Copy the code

Dynamic update


	<! -- Dynamic update -->
	<! -- Don't forget the comma -->
	<update id="updateByConditions" parameterType="map">

		update students
		<set>
			<if test="name! =null">
				 name = #{name},
			</if>
			<if test="sal! =null">
				 sal = #{sal},
			</if>
		</set>
		where id = #{id}
	</update>
Copy the code

Three updated fields are given


    public void updateByConditions(int id,String name,Double sal) throws Exception {
        // Get the connection object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            // Map file namespace. The ID of the SQL fragment can call the SQL in the corresponding mapping file
            /** * Since we have more than two arguments and only one Object argument collection in the method *, we use the Map collection to load our arguments */
            Map<String, Object> map = new HashMap();
            map.put("id", id);
            map.put("name", name);
            map.put("sal", sal);
            sqlSession.update("StudentID.updateByConditions", map);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{ MybatisUtil.closeSqlSession(); }}public static void main(String[] args) throws Exception {
        StudentDao studentDao = new StudentDao();
        studentDao.updateByConditions(2."haha".500D);

    }
Copy the code

Dynamic delete

delete from students where id in (? ,? ,? ,?) ;Copy the code

And our Mybatis and support dynamic SQL, so delete it is very convenient!


	<delete id="deleteByConditions" parameterType="int">

		<! Separator is the separator between elements. Item is the iterated array. The property value can be arbitrary, but it is advocated that the name of the method be the same.
		delete from students where id in
		 <foreach collection="array" open="(" close=")" separator="," item="ids">
			 #{ids}
		 </foreach>

	</delete>
Copy the code

Delete records numbered 2, 3, and 4

    public void deleteByConditions(int. ids) throws Exception {
        // Get the connection object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            // Map file namespace. The ID of the SQL fragment can call the SQL in the corresponding mapping file
            /** * Since we have more than two arguments and only one Object argument collection in the method *, we use the Map collection to load our arguments */
            sqlSession.delete("StudentID.deleteByConditions", ids);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{ MybatisUtil.closeSqlSession(); }}public static void main(String[] args) throws Exception {
        StudentDao studentDao = new StudentDao();
        studentDao.deleteByConditions(2.3.4);

    }

Copy the code

Dynamic insert

Dynamic inserts are a little more complicated than other DML statements because they are indeterminate in two parts. A normal SQL statement looks like this:


insert into student(id.name,sal) values(? ,? ,?)Copy the code

SQL blocks do not automatically remove redundant commas as they did before, so we need to use the trim tag to remove them manually…

When writing insertSQL statements, don’t forget to write the () parentheses.


    <! SQL fragment does not automatically generate the appropriate SQL by default, so we need to manually remove the comma ourselves.
    <sql id="key">
        <trim suffixOverrides=",">
            <if test="id! =null">
                id,
            </if>

            <if test="id! =null">
                name,
            </if>

            <if test="id! =null">
                sal,
            </if>
        </trim>
    </sql>

    <sql id="value">
        <trim suffixOverrides=",">
            <if test="id! =null">
                #{id},
            </if>

            <if test="id! =null">
                #{name},
            </if>

            <if test="id! =null">
                #{sal},
            </if>
        </trim>
    </sql>
    <! -- Dynamic insert -->
    <insert id="insertByConditions" parameterType="zhongfucheng.Student">
	
		insert into students (<include refid="key"/>) values
        (<include refid="value"/>)

	</insert>
Copy the code

Test data for three different contents


    public void insertByConditions(Student student) throws Exception {
        // Get the connection object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            // Map file namespace. The ID of the SQL fragment can call the SQL in the corresponding mapping file
            sqlSession.insert("StudentID.insertByConditions", student);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{ MybatisUtil.closeSqlSession(); }}public static void main(String[] args) throws Exception {
        StudentDao studentDao = new StudentDao();
        studentDao.insertByConditions(new Student(55.null.null));// Name and sal are empty

        studentDao.insertByConditions(new Student(66."haxi".null));/ / sal is empty
        studentDao.insertByConditions(new Student(77.null.3999d));/ / the name is empty


    }
Copy the code

conclusion

Since we will often use conditional query in development, we used query assistant to help us complete the stitching of SQL before. And Mybatis, we are our own handwritten SQL code.
Mybatis also supports some judgment tags, so you can use these tags to perform dynamic CRUD.
It is worth noting that our SQL fragment code needs to be split manually by ourselves.

If you find this article helpful, give the author a little encouragement

SQL > SQL > SQL > SQL

SQL > SQL > SQL > SQL

A dynamic query

Dynamic update

Dynamic delete

Dynamic insert

conclusion

Related Posts

Rethinking design tools in machine learning era

Postfix + Dovecot mail server

Project management tool: Maven