I. use annotation to integrate mybatis
The project contents are as follows
Step 1: import the jar package
<build> <resources> <!-- mapper.xml File in java Directory --> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> </resource> </resources> </build> <dependencies> <dependency> <groupId>aopalliance</groupId> <artifactId>aopalliance</artifactId> <version>1.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver --> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.6.9</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp --> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> <version>1.2</version> </dependency> <dependency> <groupId>commons-pool</groupId> <artifactId>commons-pool</artifactId> <version>1.6</version> </dependency> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j</artifactId> <version>2.6.1</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.1</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>2.0.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>c3p0</groupId> <artifactId>c3p0</artifactId> <version>0.9.1.2</version> <scope>compile</scope> </dependency> </dependencies>
Step 2: create student entity class
package com.cc.entity; public class Student { private int id; private String stuno; private String name; private String classid; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStuno() { return stuno; } public void setStuno(String stuno) { this.stuno = stuno; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getClassid() { return classid; } public void setClassid(String classid) { this.classid = classid; } @Override public String toString() { return "Student{" + "id=" + id + ", stuno='" + stuno + '\'' + ", name='" + name + '\'' + ", classid='" + classid + '\'' + '}'; } }
Step 3: create Mapper interface and implement sql statement by annotation in the interface
package com.cc.dao; import com.cc.entity.Student; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import java.util.List; public interface StudentMapper { @Select("select * from student where id=#{id}") Student findById(@Param("id") int id); @Select("select * from student") List<Student> selectAll(); }
Then there is another problem. What should I do if I want to implement dynamic query in annotations??? The case is as follows
@Mapper public interface DemandCommentMapper extends BaseMapper<DemandComment>{ @Select("SELECT " + "a.id as 'id',a.create_date as 'createDate',a.content as 'content'," + "a.parent_id as 'parentId',a.first_comment_id as 'firstCommentId'," + "b.id as 'fromUser.id',b.realname as 'fromUser.realname',b.avatar as 'fromUser.avatar'," + "c.id as 'toUser.id',c.realname as 'toUser.realname',c.avatar as 'toUser.avatar' " + "FROM t_demand_comment a " + "LEFT JOIN t_user b ON b.id = a.from_uid " + "LEFT JOIN t_user c ON c.id = a.to_uid " + "WHERE a.demand_id = #{demandId} " + "ORDER BY a.create_date ASC" + "LIMIT #{startNo},#{pageSize}") public List<DemandComment> listDemandComment(@Param("demandId") Long demandId, @Param("startNo") Integer pageNo, @Param("pageSize") Integer pageSize);
In this way, the whole statement is write dead. If I want to judge whether paging is needed according to whether pageNo and pageSize are empty, what should I do? If I use xml to configure, I can use
<when test='startNo!=null and pageSize != null '> LIMIT #{startNo},#{pageSize} </when>
If it is used @Select How to do this? Method: surround it with a script tag and write it like xml syntax
@Mapper public interface DemandCommentMapper extends BaseMapper<DemandComment>{ @Select("<script>" + "SELECT " + "a.id as 'id',a.create_date as 'createDate',a.content as 'content'," + "a.parent_id as 'parentId',a.first_comment_id as 'firstCommentId'," + "b.id as 'fromUser.id',b.realname as 'fromUser.realname',b.avatar as 'fromUser.avatar'," + "c.id as 'toUser.id',c.realname as 'toUser.realname',c.avatar as 'toUser.avatar' " + "FROM t_demand_comment a " + "LEFT JOIN t_user b ON b.id = a.from_uid " + "LEFT JOIN t_user c ON c.id = a.to_uid " + "WHERE a.demand_id = #{demandId} " + "ORDER BY a.create_date ASC " + "<if test='startNo!=null and pageSize != null '>" + "LIMIT #{startNo},#{pageSize}" + "</if>" + "</script>") public List<DemandComment> listDemandComment(@Param("demandId") Long demandId, @Param("startNo") Integer pageNo, @Param("pageSize") Integer pageSize);
Project example
@Select("<script>" +"select * from mi_taobao where 1=1" +"<if test='status != null'>" +"and status = #{status}" +"</if>" +"</script>") public List<Taobao> getTaobao(@Param("status") Integer status);
There is also a problem here: caused by: org. Apache. Ibatis. Reflection. Reflectionexception: there is no getter for property named 'status' in' class java. Lang. interger '
Cause: the problem here is caused by the inconsistency between the parameters defined in the DAO method and the properties defined in the entity.
Solution: add @ Param("userId") annotation to dao layer (add @ Param("status") in the instance)
public List<DictItem> selectKeyByUserId(@Param("userId") long userId);
Step 4: implement the interface class in the service layer
package com.cc.service; import com.cc.entity.Student; import java.util.List; public interface StudentService { Student findById(int id); List<Student> selectAll(); }
package com.cc.service; import com.cc.dao.StudentMapper; import com.cc.entity.Student; import java.util.List; public class StudentServiceImpl implements StudentService { private StudentMapper studentMapper; public void setStudentMapper(StudentMapper studentMapper) { this.studentMapper = studentMapper; } @Override public Student findById(int id) { return (Student) studentMapper.findById(id); } @Override public List<Student> selectAll() { return studentMapper.selectAll(); } }
Step 5: configure the ApplicationContext.xml file
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!--Indicates the referenced database profile db.properties--> <context:property-placeholder location="classpath:db.properties"/> <!--Configuration database--> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <!--To configure sqlSessionFactoryBean Object, mybatis Main rely on sqlSessionFactoryBean object ，There are two parameters configured, one is dataSource,One is mybatis Core profile--> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"></property> <!--mybatis Core profile--> <property name="configLocation" value="classpath:mybatis-config.xml"></property> </bean> <!--To configure Mapper--> <bean id="StudentMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> <!--It's mainly that Mapper Specifically--> <property name="mapperInterface" value="com.cc.dao.StudentMapper"></property> <property name="sqlSessionFactory" ref="sqlSessionFactory"></property> </bean> <!--To configure StudentService--> <bean id="studentService" class="com.cc.service.StudentServiceImpl"> <property name="studentMapper" ref="StudentMapper"></property> </bean> </beans>
Step 6: configure the db.properties file
#mysql jdbc fresh jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/fresh?useUnicode=true&characterEncoding=UTF-8 jdbc.username=root jdbc.password=root
Step 7: Test
import com.cc.entity.Student; import com.cc.service.StudentService; import com.cc.service.StudentServiceImpl; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import java.util.List; public class Testdemo1 { @Test public void test(){ ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext.xml"); StudentService studentService =ac.getBean("studentService", StudentServiceImpl.class); Student student=studentService.findById(5); System.out.println(student); List<Student> studentList=studentService.selectAll(); for (Student stu: studentList) { System.out.println(stu); } } }
