This Mybatis Framework Learn Notes; Keep up with previous studies
Mybatis Framework Learning Notes (2) - >
Configuration to learn in the core configuration file of the mybatis framework
Configuration (configuration)
properties
settings
typeAliases (type alias)
typeHandlers
objectFactory (Object Factory)
plugins
environments (environment configuration)
Environment (environment variable)
transactionManager (Transaction Manager)
dataSource (data source)
databaseIdProvider (Database Vendor Identification)
mappers
1. Differences between using #{} and ${} in sql statements
Use #{}
Recall that when you sorted out your first note, you added the user's sql to the mapper.xml configuration file at that time;
#{} was used to get the value in the object
<?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">
<!--Note that the corresponding space here is the persistent layer mapping interface-->
<mapper namespace="com.xiaozhi.mapper.UserMapper">
<!--Add a new user's sql-->
<!--There parameterType The parameter type uses the alias just configured user Yes-->
<insert id="addUser" parameterType="user">
insert into t_user(account,password,address)
values (#{account},#{password},#{address})
</insert>
</mapper>
Test Use
@Test
public void testAddUser(){
//Create user object;
User user = new User("Points of gold","431231w","Chengdu, Sichuan");
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//Call method; The sql statement is actually executed here;
mapper.addUser(user);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
You can see the log of the test prints;
Placeholders appear?,?,?
That is, he used PreparedStatement to execute sql; It is safer; Prevents SQL injection
Use ${}
In fact, you can also use ${};
Ok, then try it.
Replace sql in UserMapper.xml
<insert id="addUser" parameterType="user">
insert into t_user(account,password,address)
values (${account},${password},${address})
</insert>
Execute Tests
//Execute sql by passing parameters in ${};
@Test
public void testAddUser2(){
//Create user object;
User user = new User("Afei","123132","Chengdu, Sichuan");
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//Call method; The sql statement is actually executed here;
mapper.addUser(user);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Execution, _, error occurred; It writes the input assignment directly into the sql statement. It also takes the assignment of the input as the column name of the database. So the first hint is that we can't find a column named Afei. This is already wrong. Naturally, it will not be carried out anymore.
You know, these attributes are assigned as strings; When assigning a string in a database, add''or'';
So, modify the sql statement
<insert id="addUser" parameterType="user">
insert into t_user(account,password,address)
values ('${account}','${password}','${address}')
</insert>
Execute again;
OK, the execution was successful;
When added; It is a way of splicing strings directly; Assigning objects to pass in;
When executing SQL in the ${} manner, it is actually the Statement's way; That is, the unsafe one; Cannot prevent SQL injection
But; $ {} is also available,
Because ${} passes column names, not data;
2. After executing sql, get the primary key value of the data table;
In the previous add users, since the ID of the data table was automatically incremented as the primary key at that time;
After executing the add statement, I want to get the ID of the data I just added quickly.
So if you get Id directly, you will get 0.
Try it - >
//The value of the primary key ID is obtained immediately after adding user data;
@Test
public void testAddUser3(){
//Create user object;
User user = new User("Love drinking water","123132","Xi'an, Shaanxi");
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//Call method; The sql statement is actually executed here;
mapper.addUser(user);
System.out.println("Current User Id Value is-->"+user.getId());
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Execute it once; The user must have saved it; But the ID output here is 0;
Look at the data table, the user's ID is 24 oh
Of course, you can write two sql statements; That is, after the new user data I just added, I then query the user's ID information based on some information. This is possible;
However, there is a better way to execute sql statements without having to execute other sql statements. You can also get the user's ID;
That's when you write sql statements in the UserMapper.xml configuration file; Set the parameter useGeneratedKeys to true; The generated primary key is used; Of course, set the column name of the database and the corresponding attributes in the class.
<!--After execution,Want to get the primary key value of the table Confirm Use of Primary Key Column Name of Data Table Corresponding attributes of entity classes-->
<insert id="addUser" parameterType="user" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into t_user(account,password,address)
values (#{account},#{password},#{address})
</insert>
Test execution again;
//The value of the primary key ID is obtained immediately after adding user data;
@Test
public void testAddUser4(){
//Create user object;
User user = new User("Little salted fish","123132","Chengdu, Sichuan");
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//Call method; The sql statement is actually executed here;
mapper.addUser(user);
System.out.println("Current User Id Value is-->"+user.getId());
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Execution; Gets the primary key ID number
3. Write a Modify User Information
User's entity class, changed by adding a new parametric construction method; And the toString() method
package com.xiaozhi.pojo;
/**
* @author by CSDN@Smart RE0
* @date 2021-10-24 22:07
* Entity Class User (User Class)
*/
public class User {
//Property, id, account, password, address;
private int id;
private String account;
private String password;
private String address;
//Construction method;
public User() {
}
public User(int id, String account, String password, String address) {
this.id = id;
this.account = account;
this.password = password;
this.address = address;
}
//The construction method here does not include an ID when it is initialized;
public User(String account, String password, String address) {
this.account = account;
this.password = password;
this.address = address;
}
//getter,setter;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", account='" + account + '\'' +
", password='" + password + '\'' +
", address='" + address + '\'' +
'}';
}
}
Of course, first define the method at the UserMapper persistence layer interface;
//Update and modify user information; void updateUser(User user);
Then write the corresponding sql file in the UserMapper.xml mapping file
<!--Modify User Information Parameter to Basic Type,Can not label-->
<update id="updateUser">
update t_user set account=#{account},password=#{password},address=#{address}
where id=#{id}
</update>
Test Execution
//Modify user information;
@Test
public void updateUser(){
//Create user object;
User user = new User(25,"Study mybatis frame","12weds","Shaanxi");
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//Call method;
userMapper.updateUser(user);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Successful execution
4. Write a delete user
Define the method at the UserMapper persistence layer interface;
//Delete user according to ID; void deleteUser(int id);
Write the corresponding sql file in the UserMapper.xml mapping file
<!--Delete user information-->
<delete id="deleteUser">
delete from t_user where id=#{id}
</delete>
Test Execution
//Delete user information;
@Test
public void deleteUser(){
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//Call method;
userMapper.deleteUser(20);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Execution; Delete succeeded;
5. Query all users
Define the method at the UserMapper persistence layer interface;
//Query all users; List<User> findUser();
Write the corresponding sql file in the UserMapper.xml mapping file
<!--Query all users-->
<select id="findUser" resultType="user">
select * from t_user
</select>
Test Execution
//Query all users;
@Test
public void findAllUser(){
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//Call method;
List<User> user = userMapper.findUser();
System.out.println(user);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Execution;
6. Query users based on Id
Define the method at the UserMapper persistence layer interface;
//Obtained according to user ID; User findUserById(int id);
Write the corresponding sql file in the UserMapper.xml mapping file
<!--according to Id Query Information-->
<select id="findUserById" resultType="user">
select * from t_user where id=#{id};
</select>
Test Execution
//Query user information according to Id;
@Test
public void findUserById(){
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//Call method;
User userById = userMapper.findUserById(25);
System.out.println(userById);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
Successful execution;
7. Configure Open Hump Naming Matching
Have you noticed it? In the previous query for information, there was no specified property to obtain. It is automatically matched to the object properties of the user class; This is because when entity classes were written, the attribute names and database column names were aligned
So, what if the column names of the tables and the properties of the user classes are different?
For example, here I changed the address property of the user class to address. Now I'll change it to addRess, the way the humps are named;
package com.xiaozhi.pojo;
/**
* @author by CSDN@Smart RE0
* @date 2021-10-27 1:48
* Entity Class User (User Class)
*/
public class User {
//Property, id, account, password, address;
private int id;
private String account;
private String password;
private String addRess;
//Construction method;
public User() {
}
public User(int id, String account, String password, String addRess) {
this.id = id;
this.account = account;
this.password = password;
this.addRess = addRess;
}
//The construction method here does not include an ID when it is initialized;
public User(String account, String password, String addRess) {
this.account = account;
this.password = password;
this.addRess = addRess;
}
//getter,setter;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getAddRess() {
return addRess;
}
public void setAddRess(String addRess) {
this.addRess = addRess;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", account='" + account + '\'' +
", password='" + password + '\'' +
", addRess='" + addRess + '\'' +
'}';
}
}
Then I change the address of the table from address to add_ress
At this time, when I query information based on ID again, the address cannot find the information;
In fact, this requires a setup in mybatis's core configuration file, mybatis-config.xml
Configure mapUnderscoreToCamelCase to true under settings
Whether to turn on automatic mapping of hump naming, i.e. column name A_from a classic database COLUMN maps to the classic Java property name aColumn.
It defaults to false off state;
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config
3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--Introduce external resource files-->
<properties resource="database.properties">
</properties>
<settings>
<!--Configuration Log-->
<setting name="logImpl" value="LOG4J"/>
<!--Set Open Hump Naming Match-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--Configure Alias-->
<typeAliases>
<!--Is Entity Class User Configuration Alias user-->
<typeAlias type="com.xiaozhi.pojo.User" alias="user"/>
</typeAliases>
<!-- environments Environment Configuration , There default To specify the environment variable used by default -->
<environments default="development">
<!-- environment environment variable ;Can write multiple,Ensure as much as possible id Do not rename-->
<!--Development environment variables-->
<environment id="development">
<!--transaction management-->
<transactionManager type="JDBC">
</transactionManager>
<!--dataSource:data source-->
<dataSource type="UNPOOLED">
<!--Database Driver,url Connect,Database Account,Password-->
<property name="driver" value="${driver}" />
<property name="url" value="${url}"/>
<property name="username" value="${username}" />
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--To configure mapper mapping-->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
OK, then execute, query user information according to ID;
8. When querying with multiple parameters as criteria
Mode 1: Name the parameter with the comment @Param
Define methods directly in the persistence layer interface UserMappper;
Query users based on account name and address
//Query users based on account name and address; Name the parameters here using annotations;
User findUserByAccAndAdd(@Param("acc")String account,@Param("add")String address);
Write the corresponding sql file in the UserMapper.xml mapping file;
Note that the placeholder assignment in sql here is the parameter name named in the comment;
<!--Query users based on account name and address -->
<select id="findUserByAccAndAdd" resultType="user">
select * from t_user where account=#{acc} and add_ress=#{add}
</select>
Test use;
//Query information based on the user's account name and address; Use the comment @param at the interface method
@Test
public void findUserByAccAndAddress(){
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//Call method;
User user = userMapper.findUserByAccAndAdd("Love drinking water", "Xi'an, Shaanxi");
System.out.println(user);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
There are query results;
Mode 2: Use the map collection directly, and the map key stores the columns to be queried; Map values store assignments;
Define methods directly in the persistence layer interface UserMappper;
Or the last case, but this time using a set Map as a parameter to the method;
//Query users with Map account name and address; User findUserByMap(Map<String,Object> map);
Write the corresponding sql file in the UserMapper.xml mapping file;
<!--Use map Ways; The parameter types here are encapsulated directly using the framework-->
<select id="findUserByMap" resultType="user" parameterType="hashmap">
select * from t_user where account=#{name} and add_ress=#{ourAddress}
</select>
The parameter types used here are parameters that are automatically encapsulated by the framework.
The source code needs to be viewed and can be found in the TypeAliasRegistry class under the type directory under the org.apache.ibatis directory under mybatis-3.4.2.jar; Construction methods for this class
Test execution;
//Query information based on the user's account name and address; How to use the Map collection;
@Test
public void findUserByMap(){
//Create sqlSession using a tool class;
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//Get the proxy object;
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//Call method;
Map<String,Object> map = new HashMap<>();
//Note that here the map's key must be the same as the one bound in sql;
map.put("name","Ajie");
map.put("ourAddress","Hanzhong, Shaanxi");
User user = userMapper.findUserByMap(map);
System.out.println(user);
//Transaction commit;
sqlSession.commit();
//Close sqlSession;
sqlSession.close();
}
query was successful
