Two Development Methods of Querying a User by ID

Two Development Methods of Querying a User by ID

The database table sql statement is as follows: https://github.com/beyondyanyu/Sayingyy/blob/master/JDBC2-database sql table building statement

(1) Primitive Dao development:

UserDao.java (interface):

package com.pdsu.mybatis.dao;

import com.pdsu.mybatis.pojo.User;

public interface UserDao {
	//Query a user by user ID
	public User selectUserById(Integer id);
		
}

UserDaoImpl.java (Implementation Interface):

package com.pdsu.mybatis.dao;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.pdsu.mybatis.pojo.User;

/**
 * 
 * */
public class UserDaoImpl implements UserDao {
	//Injecting SQL Session Factory
	private SqlSessionFactory sqlsessionFactory;

	//constructor
	public UserDaoImpl(SqlSessionFactory sqlsessionFactory) {
		this.sqlsessionFactory = sqlsessionFactory;
	}
	
	//Query a user by user ID
	public User selectUserById(Integer id){
		SqlSession sqlSession = sqlsessionFactory.openSession(); 
		return sqlSession.selectOne("user.findUserById", id);
	}
}

Mybatis DaoTest. java test

package com.pdsu.mybatis.junit;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.pdsu.mybatis.dao.UserDao;
import com.pdsu.mybatis.dao.UserDaoImpl;
import com.pdsu.mybatis.pojo.User;

public class MybatisDaoTest {
	public SqlSessionFactory sqlSessionFactory;
	
	@Before
	public void before() throws Exception{ 
		//Load the core configuration file, load it to read with IO stream
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		//Create SqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	}
	
	@Test
	public void testDao() throws Exception{
		UserDao userDao = new UserDaoImpl(sqlSessionFactory);
		User user = userDao.selectUserById(10);
		System.out.println(user);//The output is user information with ID 10 in the database.
	}
}

User.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace: Namespaces for isolation sql,There's also a very important role to play, which I'll talk about later. -->
<!-- 
//Namespace:user.findUserById
//Namespace:order.findUserById 
-->

<mapper namespace="com.pdsu.mybatis.mapper.UserMapper">
	
	<!-- 
	resultType:Return value
	parameterType:input parameter 
	-->
	
	
	<!-- adopt ID Query a user -->
	<select id="findUserById" parameterType="Integer" resultType="com.pdsu.mybatis.pojo.User">
		select * from user where id= #{sq}
	</select>
	
	<!-- 
	#{} === select * from user where username=     Represents a placeholder?   sq Can be substituted at will
		=== select * from user where username= 'Si Qi'
	
	${} === select * from user where username like '%${value}%'  Representation string splicing  value Can't be substituted at will
		
		=== select * from user where username like '%Qi%' 			sql Fuzzy Sentence Query
		=== select * from user where username like "%"'Qi%'"%" 		sql Fuzzy Sentence Query
		
		=== select * from user where username like "%"#{sq}"%" 
	-->
	
	
	<!-- Fuzzy Query of User List Based on User Name -->
	<select id="findUserByUsername" parameterType="String" resultType="com.pdsu.mybatis.pojo.User">
		select * from user where username like '%${value}%'
	</select>
	
	<!-- Add user -->
	<insert id="insertUser" parameterType="com.pdsu.mybatis.pojo.User">
		<selectKey keyProperty="id" resultType="Integer" order="AFTER" >
			select LAST_INSERT_ID()
		</selectKey>
		insert into user(username,birthday,address,sex) values (#{username},#{birthday},#{address},#{sex})
	</insert>
	
	
	<!-- Update user -->
	<update id="updateUserById" parameterType="com.pdsu.mybatis.pojo.User">
		update user set username = #{username},sex = #{sex},birthday = #{birthday},address = #{address}
		where id = #{id}
	</update>	
	
	<!-- delete user -->
	<delete id="deleteUserById" parameterType="Integer" >
		delete from user where id = #{sq}
	</delete>
	
	
</mapper>
(2) Mapper dynamic agent development:

UserMapper.java:

package com.pdsu.mybatis.mapper;

import java.util.List;

import com.pdsu.mybatis.pojo.User;


public interface UserMapper {
	/*
	 Follow the Four Principles
	For: public User findUserById(Integer id);
	Interface method name=== id name in User.xml===== findUserById
	The return value type should be the same as the result type in Mapper.xml file === User === com.pdsu.mybatis.pojo.User
	The input type of the method should be the same as that of the parameter type in Mapper.xml=== Integer id
	Namespace binds this interface <mapper namespace="com.pdsu.mybatis.mapper.UserMapper">, and the path in User.xml is the full path of the interface.
	*/
	public User findUserById(Integer id);
		
}

MybatisMapperTest.java:

package com.pdsu.mybatis.mapper;

import static org.junit.Assert.*;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.pdsu.mybatis.pojo.User;

public class MybatisMapperTest {
	@Test
	public void testMapper() throws Exception {
		//Load the core configuration file, load to read using IO stream
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		//Create SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//SqlSessionFactoryBuilder This is an implementation class that needs a new look
		
		//Create SQL Session
		SqlSession sqlSession = sqlSessionFactory.openSession();	
		
		//SqlSession automatically generates implementation classes (interfaces need to follow the four principles)==== UserMapper.class is an interface that follows the four principles
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		User user = userMapper.findUserById(30);

		System.out.println(user);//The output is user information with id 30 in the database.
	}
}

User.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace: Namespaces for isolation sql,There's also a very important role to play, which I'll talk about later. -->
<!-- 
//Namespace:user.findUserById
//Namespace:order.findUserById 
-->

<mapper namespace="com.pdsu.mybatis.mapper.UserMapper">
	
	<!-- 
	resultType:Return value
	parameterType:input parameter 
	-->
	
	
	<!-- adopt ID Query a user -->
	<select id="findUserById" parameterType="Integer" resultType="com.pdsu.mybatis.pojo.User">
		select * from user where id= #{sq}
	</select>
	
	<!-- 
	#{} === select * from user where username=     Represents a placeholder?   sq Can be substituted at will
		=== select * from user where username= 'Si Qi'
	
	${} === select * from user where username like '%${value}%'  Representation string splicing  value Can't be substituted at will
		
		=== select * from user where username like '%Qi%' 			sql Fuzzy Sentence Query
		=== select * from user where username like "%"'Qi%'"%" 		sql Fuzzy Sentence Query
		
		=== select * from user where username like "%"#{sq}"%" 
	-->
	
	
	<!-- Fuzzy Query of User List Based on User Name -->
	<select id="findUserByUsername" parameterType="String" resultType="com.pdsu.mybatis.pojo.User">
		select * from user where username like '%${value}%'
	</select>
	
	<!-- Add user -->
	<insert id="insertUser" parameterType="com.pdsu.mybatis.pojo.User">
		<selectKey keyProperty="id" resultType="Integer" order="AFTER" >
			select LAST_INSERT_ID()
		</selectKey>
		insert into user(username,birthday,address,sex) values (#{username},#{birthday},#{address},#{sex})
	</insert>
	
	
	<!-- Update user -->
	<update id="updateUserById" parameterType="com.pdsu.mybatis.pojo.User">
		update user set username = #{username},sex = #{sex},birthday = #{birthday},address = #{address}
		where id = #{id}
	</update>	
	
	<!-- delete user -->
	<delete id="deleteUserById" parameterType="Integer" >
		delete from user where id = #{sq}
	</delete>
	
	
</mapper>

Summary (Reprint: Black Horse Programmer):

selectOne and selectList
The dynamic proxy object calls sqlSession.selectOne() and sqlSession.selectList() are determined by the return value of the mapper interface method. If a list is returned, the selectList method is called, and if a single object is returned, the selectOne method is called.

namespace
mybatis officially recommends using mapper proxy method to develop mapper interface. Programmers do not need to write mapper interface implementation classes. When using mapper proxy method, input parameters can use pojo wrapper object or map object to ensure the universality of dao.

Tags: Mybatis SQL xml Apache

Posted on Thu, 10 Oct 2019 15:36:06 -0700 by frymaster