MyBatis first day of study

Establishment of MyBatis basic environment (taking querying all data as an example)

pom

 <dependencies>
 <dependency>
 <groupId>org.mybatis</groupId>
 <artifactId>mybatis</artifactId>
 <version>3.4.5</version>
 </dependency>
 <dependency>
 <groupId>junit</groupId>
 <artifactId>junit</artifactId>
 <version>4.10</version>
 <scope>test</scope>
 </dependency>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.6</version>
 <scope>runtime</scope>
 </dependency>
 <dependency>
 <groupId>log4j</groupId>
 <artifactId>log4j</artifactId>
 <version>1.2.12</version>
 </dependency>
 </dependencies>

This is the dependency required in the pom file, where myBatis and Mysql are essential, junnit is used for unit testing and log4j is used for outputting log information, and log4j also needs to import a log4j Propreties file

Entity class

Here, we operate on the user table in the database, so we create a user class,

/**
* 
* <p>Title: User</p>
* <p>Description: User's entity class</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
Be sure to inherit serialization
 Intelligence podcast - focus on Java,.Net and Php,Training of Web Graphic Design Engineers
 Floor 1, jinyanlong office building, Jiancai Chengxi Road, Changping District, Beijing Tel: 400-618-9090
public Integer getId() {
return id; }
public void setId(Integer id) {
this.id = id; }
public String getUsername() {
return username; }
public void setUsername(String username) {
this.username = username; }
public Date getBirthday() {
return birthday; }
public void setBirthday(Date birthday) {
this.birthday = birthday; }
public String getSex() {
return sex; }
public void setSex(String sex) {
this.sex = sex; }
public String getAddress() {
return address; }
public void setAddress(String address) {
this.address = address; }
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday=" + birthday
+ ", sex=" + sex + ", address="
+ address + "]"; } }

Note that the attribute names in the class are consistent with those in the table, otherwise an error will be reported. Another solution is to use the resultMap keyword.

<!-- establish User Correspondence between entity and database table
 Intelligence podcast - focus on Java,.Net and Php,Training of Web Graphic Design Engineers
 Floor 1, jinyanlong office building, Jiancai Chengxi Road, Changping District, Beijing Tel: 400-618-9090
type Property: Specifies the fully qualified class name of the entity class
id Attribute: given a unique identifier, it is used for query select For label reference.
--> <resultMap type="com.itheima.domain.User" id="userMap"> <id column="id" property="userId"/>
<result column="username" property="userName"/>
<result column="sex" property="userSex"/>
<result column="address" property="userAddress"/>
<result column="birthday" property="userBirthday"/>
</resultMap>
id Label: used to specify the primary key field
result Label: used to specify non primary key fields
column Property: used to specify the database column name
property Attribute: used to specify the attribute name of the entity class

In this way, the attributes in the table and the entity class can correspond. Note that sql is case insensitive under windows. Therefore, if the attributes in the entity class and the attributes in the database table are different only because of the case of letters, it will not have any impact on the use

Persistence layer Dao interface

MyBatis uses the proxy Dao method, so we only need to create a Dao interface. MyBatis will automatically create a Dao instance for us to run the code later. Here we create a Dao interface and add a findAll method to it. It should be noted that if we need to add a database operation behavior, we must first think about the parameters and return values passed in the method under the interface. Then create the corresponding method.

IUserDao The interface is our persistence layer interface (which can also be written as UserDao perhaps UserMapper),The specific codes are as follows:
/**
* 
Intelligence podcast - focus on Java Net and Php, Web Graphic Design Engineer Training
 First floor of jinyanlong office building, Jiancai Chengxi Road, Changping District, Beijing Tel: 400-618-9090
* <p>Title: IUserDao</p>
* <p>Description: User's persistence layer operation</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public interface IUserDao {
/**
* Query all users
* @return
*/
List<User> findAll();
}

Write the mapping file iuserdao for the persistence layer interface xml

requirement:
Creation location: must be in the same package as the persistence layer interface.
Name: the file name must be named with the name of the persistence layer interface, and the extension is xml

Pay special attention to the above two points, otherwise the system will report an error,

<?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"> <mapper namespace="com.itheima.dao.IUserDao">
<!-- Configure query all operations --> <select id="findAll" resultType="com.itheima.domain.User">
select * from user
</select>
</mapper>

In addition, it should be noted that the id behind the select tag here must write the method name in the Dao interface, and the fully qualified class name must be written in the resultType, as well as in the namespace. Because the User class is not like String, Integer and other classes, the system has automatically created an alias for it. You only need to write String and Integer. If you don't want to be so troublesome, you just need to use the typeAliases tag

stay SqlMapConfig.xml Configuration in:
<typeAliases>
<!-- Single alias definition --> <typeAlias alias="user" type="com.itheima.domain.User"/>
<!-- Batch alias definition, scan the classes under the whole package, and the alias is the class name (the initial letter can be uppercase or lowercase) --> <package name="com.itheima.domain"/>
<package name="Other packages"/>
</typeAliases>

< typealias alias = "user" type = "com. Itheima. Domain. User" / > defines a user entity class as user

< package name = "com.itheima.domain" / > Yes itheima. All classes under the domain package automatically create aliases, and the aliases are set to their class names

Write sqlmapconfig XML configuration file

This file is the main configuration file of MyBatis, which contains the core configuration content of MyBatis operation

<?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>
<!-- to configure mybatis Environment --> <environments default="mysql">
<!-- to configure mysql Environment --> <environment id="mysql">
<!-- Configure the type of transaction --> <transactionManager type="JDBC"></transactionManager>
<!-- Configure the information of connecting to the database: the data source is used(Connection pool) --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>I
</environments>
<!-- inform mybatis Location of mapping configuration --> <mappers> <mapper resource="com/itheima/dao/IUserDao.xml"/>
</mappers>
</configuration>

Write test class

/**
* 
* <p>Title: MybatisTest</p>
* <p>Description: Environment for testing mybatis</p>
* <p>Company: http://www.itheima.com/ </p>
*/
public class MybatisTest {
public static void main(String[] args)throws Exception {
//1. Read the configuration file
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. Create the builder object of SqlSessionFactory
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3. Use the builder to create the factory object SqlSessionFactory
SqlSessionFactory factory = builder.build(in);
//4. Use SqlSessionFactory to produce SqlSession objects
SqlSession session = factory.openSession();
//5. Use SqlSession to create the proxy object of dao interface
IUserDao userDao = session.getMapper(IUserDao.class);
//6. Use the proxy object to execute all methods of query
List<User> users = userDao.findAll();
for(User user : users) {
System.out.println(user);
}
//7. Release resources
session.close();
in.close();
} }

MyBatis performs a save operation

Dao

First, update the Dao interface

   void saveUser(User user);

DaoUser.xml

Update the configuration file. Fill in the User class with parameters here, and then write the sql statement. The value after values uses the corresponding attribute name in the User object

    <insert id="saveUser" parameterType="doMain.User">

        insert into user (username , birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})
    </insert>

Details:
parameterType property:
Represents the type of the parameter. Because we want to pass in the object of a class, the type writes the full name of the class.
Use #{} characters in sql statements: it represents placeholders, which is equivalent to what was learned in the original jdbc part?, Are used to replace the actual data when executing a statement.
The specific data is determined by #{} the contents.
#How to write the content in {}:
Since the parameter of our saving method is a User object, the attribute name in the User object should be written here.
It uses an ognl expression.
ognl expression:
It is an expression language provided by apache. Its full name is:
Object Graphic Navigation Language
It obtains data according to a certain syntax format.
Syntax format is the way to use #{object. Object}
#{user.username} it will first look for the user object, then find the username attribute in the user object and call
The getUsername() method takes out the value. However, we specified the name of the entity class on the parameterType attribute, so we can omit user Instead, write username directly.

Test class

@Test
public void testSave(){
User user = new User();
user.setUsername("modify User property");
user.setAddress("Shunyi District, Beijing");
user.setSex("male");
user.setBirthday(new Date());
System.out.println("Before saving the operation:"+user);
//5. Execute the save method
userDao.saveUser(user);
System.out.println("After saving:"+user);
}
open Mysql The database found that no records were added. Why?
This is similar to jdbc It is the same. We must control the submission of transactions when adding, deleting and modifying mybatis How to control transactions in
 What about submission?
have access to:session.commit();To achieve transaction commit. The code after adding transaction submission is as follows:
@After//Execute after the test method execution is completed
public void destroy() throws Exception{
session.commit();
//7. Release resources
session.close();
in.close();
}

If we need to get the id value of the column data after the insertion value, we only need to update Dao's configuration file

After adding a new user, you also need to return the current new user's id Value, because id It is realized by the automatic growth of the database, so it is similar
 When we want to add, it will grow automatically auto_increment Return the value of.
<insert id="saveUser" parameterType="USER">
<!-- Get inserted when saving configuration id --> <selectKey keyColumn="id" keyProperty="id" resultType="int">
select last_insert_id();
</selectKey>
insert into user(username,birthday,sex,address) 
values(#{username},#{birthday},#{sex},#{address})
</insert>

myBatis performs an update operation

Dao

int updateUser(User user);

DaoUser.xml

<!-- Update user --> <update id="updateUser" parameterType="com.itheima.domain.User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},
address=#{address} where id=#{id}
</update>

Test class

@Test
public void testUpdateUser()throws Exception{
//1. Query by id
User user = userDao.findById(52);
//2. Update operation
user.setAddress("Shunyi District, Beijing");
int res = userDao.updateUser(user);
System.out.println(res);
}

Here, 1 means success and 0 means failure

myBatis performs a delete operation

Dao

int deleteUser(Integer userId);

DaoUser.xml

<!-- delete user --> <delete id="deleteUser" parameterType="java.lang.Integer">
delete from user where id = #{uid}
</delete>

Test class

@Test
public void testDeleteUser() throws Exception {
//6. Perform the operation
int res = userDao.deleteUser(52);
System.out.println(res);
}

MyBatis performs fuzzy queries

Dao

/**
* Fuzzy query by name
* @param username
* @return
*/
List<User> findByName(String username);

DaoUser.xml

<!-- Fuzzy query by name --> <select id="findByName" resultType="com.itheima.domain.User" parameterType="String">
 select * from user where username like #{username}
</select>

Test class

@Test
 public void testFindByName(){
 //5. Execute a query method
 List<User> users = userDao.findByName("%king%");
 for(User user : users){
 System.out.println(user);
 }
 }

Note that we need to write%

#The difference between {} and ${}

#{} represents a placeholder
By #{} setting the value in the placeholder of the preparedStatement, the java type and jdbc type conversion can be realized automatically,
#{} can effectively prevent sql injection# {} can receive simple type values or pojo attribute values. If parameterType transfers a single simple class
Type value, #{} which can be value or other names in parentheses.
surface show Spell meet s q l strand through too {} indicates that the spliced sql string passes Indicates that the sql string can be spliced through {} to splice the content passed in by parameterType into sql without jdbc type conversion, can with meet collect simple single class type value or p o j o genus nature value ， as fruit p a r a m e t e r T y p e pass transport single individual simple single class type value ， {} can receive simple type values or pojo attribute values. If parameterType transmits a single simple type value, You can receive simple type values or pojo attribute values. If parameterType transmits a single simple type value, only value can be in {} brackets.

MyBatis implements aggregate function query

Dao

Insert code slice here