Introduction – Ramble
As a happy coder, at every stage I often write different versions of student management, user management, registration and login, from the console version of JavaSE, or the GUI version, to the JSP version of JavaWeb, to the version that uses PURE HTML as the front end. And the use of a newer technology, in this one, we used TXT do database, using XML can also, now commonly used MySQL, add and delete has been an indispensable part of our content, even if you don’t understand the principle, even if you are not very deep understanding of the technology, take out your add and delete, or is a knocking, MyBatis CURD MyBatis CURD MyBatis CURD MyBatis CURD
Optimized test method
In the test method, reading configuration files, producing SQLSessions, releasing resources, etc., are all repeated in each test method, so we can definitely present this part to prevent a lot of duplicate code
@Before
public void init(a) throws Exception{
// Read the configuration file
inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
// Create the SqlSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
// Use factories to produce SqlSession objects
sqlSession= factory.openSession();
// Create a proxy object for the Mapper interface using SqlSession
userMapper = sqlSession.getMapper(UserMapper.class);
}
Copy the code @After
public void destroy(a) throws Exception{
sqlSession.close();
inputStream.close();
}
Copy the codeAdding @before and @aftrer annotations to both methods ensures that init() and destory() are executed Before and after the method we are actually testing
(1) Add operations
(1) Write code
First, add the corresponding method to the UserMapper interface
public interface UserMapper {
/** * Add user *@param user
*/
void addUser(User user);
}
Copy the codeNext, in the SQL mapping file, add the new mapping configuration in the < INSERT >
tag pairs as follows
<insert id="addUser" parameterType="cn.ideal.domain.User">
insert into user(username,telephone,birthday,gender,address)values(#{username},# {telephone},#{birthday},#{gender},#{address})
</insert>
Copy the code(2) :
1. The id attribute, of course, is the corresponding method name, and since we do not need to get the return information here, so there is no return parameter resultType, and the parameter in the method is a JavaBean class, namely the User entity class, so it needs to be in the tag attribute. Add a parameterType attribute where you need to specify the entity class
2, insert SQL statement in the text, because the entity class has quickly generated the corresponding GET set method, so one can use #{} to represent the corresponding value
SQL > alter database id = ‘id’
(3) note:
Commit (id); commit (id); commit (id); commit (id)
(4) Test code:
/** * Test new user *@throws Exception
*/
@Test
public void testUpdateUser(a) throws Exception{
User user = new User();
user.setId(17);
user.setUsername("Change");
user.setTelephone("18899999999");
user.setBirthday(new Date());
user.setGender("Female");
user.setAddress("Guangzhou");
// Execute method
userMapper.updateUser(user);
}
Copy the code(5) Execution Results:
Console:
(6) Obtain the ID of the new user
(SELECT 1603013, SELECT 1603013, SELECT 1603013, SELECT 1603013, SELECT 1603013, SELECT 1603013, SELECT 1603013, SELECT 1603013, SELECT 1603013.
In the SQL mapping configuration file, with the
Note: This label is inserted into < SELECT >
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
SELECT 1603013;
</selectKey>
Copy the codeTest the
@Test
public void testAddUser(a) throws Exception{
User user = new User();
user.setUsername("Add");
user.setTelephone("12266660000");
user.setBirthday(new Date());
user.setGender("Male");
user.setAddress("Zhuhai");
System.out.println("Before insertion" + user);
// Execute method
userMapper.addUser(user);
System.out.println("After insertion" + user);
}
Copy the codeperform
(2) Modification operation
(1) Write code
Add modification methods to the UserMapper interface
public interface UserMapper {
/** * Update user *@param user
*/
void updateUser(User user);
}
Copy the codeTo add a statement to the SQL mapping file, the content is included in
<update id="updateUser" parameterType="cn.ideal.domain.User">
update user set username=#{username},telephone=#{telephone},birthday=#{birthday},gender=#{gender},address=#{address} where id=#{id}
</update>
Copy the code(2) Test the code
/** * Test new user *@throws Exception
*/
@Test
public void testAddUser(a) throws Exception{
User user = new User();
user.setUsername("Add");
user.setTelephone("12266668888");
user.setBirthday(new Date());
user.setGender("Female");
user.setAddress("Chengdu");
// Execute method
userMapper.addUser(user);
}
Copy the code(3) Implementation effect
(3) Delete operation
(1) Write code
Added the deletion method to the interface
public interface UserMapper {
/** * Delete user *@param uid
*/
void deleteUser(Integer uid);
}
Copy the codeIn the SQL mapping file, the
<delete id="deleteUser" parameterType="java.lang.Integer">
delete from user where id=#{id}
</delete>
Copy the code(2) Test the code
/** * Test delete user *@throws Exception
*/
@Test
public void testDeleteUser(a) throws Exception{
// Execute method
userMapper.deleteUser(17);
}
Copy the code(3) Implementation effect
(4) fuzzy query
Because the query is all very simple, I won’t show it here, but the basic process is the same
(1) Write code
Write methods in the UserMapper interface
public interface UserMapper {
/** * query by name *@param username
* @return* /
List<User> findByName(String username);
}
Copy the codeAdd a query statement to the SQL mapping file
<select id="findByName" parameterType="java.lang.String" resultType="cn.ideal.domain.User">
select * from user where username like #{username}
</select>
Copy the code(2) Test the code
/** * test fuzzy query *@throws Exception
*/
@Test
public void testFindByName(a) throws Exception{
List<User> users = userMapper.findByName("%张%");
for(User user : users){ System.out.println(user); }}Copy the code(3) pay attention to
When using fuzzy query, we need the query conditions on both sides of joining together two “%” string, this time there are two solutions, one is like in the above code I string added during the test is complete, there is a way is to use ${}, it represents a in SQL configuration file “splicing symbol”, That is, you can write SQL statements like this
select * from user where username like '%{value}'
Copy the codeAcceptable types include plain types (in which {} can only write values inside), Javabeans, and hashMaps
However, using %{} concatenation strings causes SQL injection and is not recommended
(4) Implementation effect
(5) User-defined packaging class as the query conditions
In Mapper’s input mapping example, we have some knowledge about basic data types and basic data wrapper classes, but let’s talk about a relatively complex case, that is, custom wrapper classes
Let’s start with a requirement: the query is still about the user, but the query conditions are more complex, not only limited to the user’s information, but also may include the order, shopping cart, or some information related to the user’s behavior, so how to implement such a requirement?
So we were wondering if we could add some information to the User class that we need
- From a code point of view, adding a field to User doesn’t necessarily correspond to the database, and changing it would affect User’s ability to function as a database mapping object, so we could create a UserInstance class, Inheriting the User class allows you to add fields for certain businesses that are not part of the database
(1) Define the wrapper class
package cn.ideal.domain;
public class QueryUserVo {
private UserInstance userInstance;
public UserInstance getUserInstance(a) {
return userInstance;
}
public void setUserInstance(UserInstance userInstance) {
this.userInstance = userInstance;
}
// Other query criteria, such as order, shopping cart, etc
}
Copy the codeConfigure the Mapper file
So we’re going to write SQL using the gender of the user and a fuzzy query for the name of the user and of course you can write SQL using other information yourself
<select id="findUserByVo" parameterType="cn.ideal.domain.QueryUserVo" resultType="cn.ideal.domain.UserInstance">
select * from user where user.gender=#{userInstance.gender} and user.username like #{userInstance.username}
</select>
Copy the codeQueryUserVo encapsulates various objects for query information. Why can the above code directly fetch the corresponding attributes through userinstance. gender? This method is called OGNL expression. In a class we would normally write user.getUsername but OGNL omits get
(3) Test code
/** * wrap objects as query parameters *@throws Exception
*/
@Test
public void testFindUserByVo(a) throws Exception{
// Create a wrapper object and set the query criteria
QueryUserVo queryUserVo = new QueryUserVo();
UserInstance userInstance = new UserInstance();
userInstance.setGender("Female");
userInstance.setUsername("%张%");
queryUserVo.setUserInstance(userInstance);
// Call the UserMapper method
List<UserInstance> userInstances
= userMapper.findUserByVo(queryUserVo);
for(UserInstance u : userInstances){ System.out.println(u); }}Copy the code(4) Implementation effect
At the end
If there is any inadequacy in the article, welcome to comment correction, thank you for your support!
If it helps you, follow me! If you prefer the way of reading articles on wechat, you can follow my official account
We don’t know each other here, but we are working hard for our dreams
A adhere to push original development of technical articles of the public number: ideal more than two days
