Mybatis引入Param注解的使用实现SQL语句的字段拼接-----Mybatis框架

ゝ一世哀愁。 2024-03-08 09:08 159阅读 0赞

>     <?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>
>         <properties resource="jdbc.properties"/>
>         <settings>
>             <setting name="logImpl" value="SLF4J"/>
>         </settings>
>         <typeAliases>
>             <package name="com.powernode.mybatis.POJO"/>
>         </typeAliases>
>         <environments default="development">
>             <environment id="development">
>                 <transactionManager type="JDBC"/>
>                 <dataSource type="POOLED">
>                     <property name="driver" value="${jdbc.driver}"/>
>                     <property name="url" value="${jdbc.url}"/>
>                     <property name="username" value="${jdbc.username}"/>
>                     <property name="password" value="${jdbc.password}"/>
>                 </dataSource>
>             </environment>
>         </environments>
>         <mappers>
>             <package name="com.powernode.mybatis.Mapper"/>
>         </mappers>
>     </configuration>
> 
>     <?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>
>         <properties resource="jdbc.properties"/>
>         <settings>
>             <setting name="logImpl" value="SLF4J"/>
>         </settings>
>         <typeAliases>
>             <package name="com.powernode.mybatis.POJO"/>
>         </typeAliases>
>         <environments default="development">
>             <environment id="development">
>                 <transactionManager type="JDBC"/>
>                 <dataSource type="POOLED">
>                     <property name="driver" value="${jdbc.driver}"/>
>                     <property name="url" value="${jdbc.url}"/>
>                     <property name="username" value="${jdbc.username}"/>
>                     <property name="password" value="${jdbc.password}"/>
>                 </dataSource>
>             </environment>
>         </environments>
>         <mappers>
>             <package name="com.powernode.mybatis.Mapper"/>
>         </mappers>
>     </configuration>

>     <?xml version="1.0" encoding="UTF-8" ?>
>     
>     <configuration debug="false">
>         <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
>             <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
>                 <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
>             </encoder>
>         </appender>
>     
>         <logger name="org.apache.ibatis" level="TRACE">
>     
>         </logger>
>         <logger name="java.sql.Connection" level="DEBUG">
>     
>         </logger>
>         <logger name="java.sql.Statement" level="DEBUG">
>     
>         </logger>
>         <logger name="java.sql.PreparedStatement" level="DEBUG">
>     
>         </logger>
>         
>         
>         <root level="DEBUG">
>             <appender-ref ref="STDOUT">
>     
>             </appender-ref>
>             <appender-ref ref="FILE">
>     
>             </appender-ref>
>         </root>
>     
>     </configuration>
> 
>     <?xml version="1.0" encoding="UTF-8" ?>
>     
>     <configuration debug="false">
>         <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
>             <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
>                 <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
>             </encoder>
>         </appender>
>     
>         <logger name="org.apache.ibatis" level="TRACE">
>     
>         </logger>
>         <logger name="java.sql.Connection" level="DEBUG">
>     
>         </logger>
>         <logger name="java.sql.Statement" level="DEBUG">
>     
>         </logger>
>         <logger name="java.sql.PreparedStatement" level="DEBUG">
>     
>         </logger>
>         
>         
>         <root level="DEBUG">
>             <appender-ref ref="STDOUT">
>     
>             </appender-ref>
>             <appender-ref ref="FILE">
>     
>             </appender-ref>
>         </root>
>     
>     </configuration>

>     jdbc.driver=com.mysql.cj.jdbc.Driver
>     jdbc.url=jdbc:mysql://localhost:13306/powernode
>     jdbc.username=root
>     jdbc.password=abc123
> 
>     jdbc.driver=com.mysql.cj.jdbc.Driver
>     jdbc.url=jdbc:mysql://localhost:13306/powernode
>     jdbc.username=root
>     jdbc.password=abc123

>     <?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.powernode.mybatis.Mapper.StudentMapper">
>     
>     
>         <select id="selectById" resultType="Student" parameterType="long">
>             select * from t_student where id = #{id,javaType=Long,jdbcType=BIGINT};
>         </select>
>         <select id="selectByName" resultType="Student" parameterType="String">
>             select * from t_student where name = #{name,javaType=String,jdbcType=VARCHAR};
>         </select>
>         <select id="selectByBirth" resultType="Student" parameterType="Date">
>             select * from t_student where birth = #{birth,javaType=Date,jdbcType=DATE};
>         </select>
>         <select id="selectBySex" resultType="Student" parameterType="Character">
>             select * from t_student where sex = #{sex,javaType=Character,jdbcType=CHAR}
>         </select>
>         <insert id="insertStudentsByMap">
>             insert into t_student(id,name,age,sex,birth,height) values(null,#{姓名},#{年龄},#{性别},#{生日},#{身高});
>         </insert>
>         <insert id="InsertStudentByPOJO" parameterType="com.powernode.mybatis.POJO.Student">
>             insert into t_student(id,name,age,sex,birth,height) values(null,#{name},#{age},#{sex},#{birth},#{height});
>         </insert>
>     
>     
>         <select id="selectByNameAndSex" resultType="Student">
>             select * from t_student where name = #{arg0} and sex = #{param2};
>         </select>
>         <select id="selectByNameAndSex2" resultType="Student">
>     --         一旦使用了@Param注解之后,arg标注就失效了,param还可以使用
>             select * from t_student where name = #{name} and sex = #{sex};
>         </select>
>     </mapper>
> 
>     <?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.powernode.mybatis.Mapper.StudentMapper">
>     
>     
>         <select id="selectById" resultType="Student" parameterType="long">
>             select * from t_student where id = #{id,javaType=Long,jdbcType=BIGINT};
>         </select>
>         <select id="selectByName" resultType="Student" parameterType="String">
>             select * from t_student where name = #{name,javaType=String,jdbcType=VARCHAR};
>         </select>
>         <select id="selectByBirth" resultType="Student" parameterType="Date">
>             select * from t_student where birth = #{birth,javaType=Date,jdbcType=DATE};
>         </select>
>         <select id="selectBySex" resultType="Student" parameterType="Character">
>             select * from t_student where sex = #{sex,javaType=Character,jdbcType=CHAR}
>         </select>
>         <insert id="insertStudentsByMap">
>             insert into t_student(id,name,age,sex,birth,height) values(null,#{姓名},#{年龄},#{性别},#{生日},#{身高});
>         </insert>
>         <insert id="InsertStudentByPOJO" parameterType="com.powernode.mybatis.POJO.Student">
>             insert into t_student(id,name,age,sex,birth,height) values(null,#{name},#{age},#{sex},#{birth},#{height});
>         </insert>
>     
>     
>         <select id="selectByNameAndSex" resultType="Student">
>             select * from t_student where name = #{arg0} and sex = #{param2};
>         </select>
>         <select id="selectByNameAndSex2" resultType="Student">
>     --         一旦使用了@Param注解之后,arg标注就失效了,param还可以使用
>             select * from t_student where name = #{name} and sex = #{sex};
>         </select>
>     </mapper>

>     package com.powernode.mybatis.Utils;
>     
>     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 java.io.IOException;
>     
>     public class SqlSessionUtil
>     {
>         private SqlSessionUtil(){};
>         private static SqlSessionFactory sqlSessionFactory;
>         private static ThreadLocal<SqlSession> local = new ThreadLocal<>();
>         static
>         {
>             try
>             {
>                 sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
>             }
>             catch (IOException e)
>             {
>                 throw new RuntimeException(e);
>             }
>         }
>         public static SqlSession openSession()
>         {
>             SqlSession sqlSession = local.get();
>             if (sqlSession == null)
>             {
>                 sqlSession = sqlSessionFactory.openSession();
>                 local.set(sqlSession);
>             }
>             return sqlSession;
>         }
>         public static void close(SqlSession sqlSession)
>         {
>             if(sqlSession != null)
>             {
>                 sqlSession.close();
>                 local.remove();
>             }
>         }
>     }
> 
>     package com.powernode.mybatis.Utils;
>     
>     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 java.io.IOException;
>     
>     public class SqlSessionUtil
>     {
>         private SqlSessionUtil(){};
>         private static SqlSessionFactory sqlSessionFactory;
>         private static ThreadLocal<SqlSession> local = new ThreadLocal<>();
>         static
>         {
>             try
>             {
>                 sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
>             }
>             catch (IOException e)
>             {
>                 throw new RuntimeException(e);
>             }
>         }
>         public static SqlSession openSession()
>         {
>             SqlSession sqlSession = local.get();
>             if (sqlSession == null)
>             {
>                 sqlSession = sqlSessionFactory.openSession();
>                 local.set(sqlSession);
>             }
>             return sqlSession;
>         }
>         public static void close(SqlSession sqlSession)
>         {
>             if(sqlSession != null)
>             {
>                 sqlSession.close();
>                 local.remove();
>             }
>         }
>     }

>     package com.powernode.mybatis.POJO;
>     
>     import java.util.Date;
>     
>     public class Student
>     {
>         private Long id;
>         private String name;
>         private Integer age;
>         private Double height;
>         private Date birth;
>         private Character sex;
>     
>         @Override
>         public String toString() {
>             return "Student{" +
>                     "id=" + id +
>                     ", name='" + name + '\'' +
>                     ", age=" + age +
>                     ", height=" + height +
>                     ", birth=" + birth +
>                     ", sex=" + sex +
>                     '}';
>         }
>     
>         public Long getId() {
>             return id;
>         }
>     
>         public void setId(Long id) {
>             this.id = id;
>         }
>     
>         public String getName() {
>             return name;
>         }
>     
>         public void setName(String name) {
>             this.name = name;
>         }
>     
>         public Integer getAge() {
>             return age;
>         }
>     
>         public void setAge(Integer age) {
>             this.age = age;
>         }
>     
>         public Double getHeight() {
>             return height;
>         }
>     
>         public void setHeight(Double height) {
>             this.height = height;
>         }
>     
>         public Date getBirth() {
>             return birth;
>         }
>     
>         public void setBirth(Date birth) {
>             this.birth = birth;
>         }
>     
>         public Character getSex() {
>             return sex;
>         }
>     
>         public void setSex(Character sex) {
>             this.sex = sex;
>         }
>     
>         public Student() {
>         }
>     
>         public Student(Long id, String name, Integer age, Double height, Date birth, Character sex) {
>             this.id = id;
>             this.name = name;
>             this.age = age;
>             this.height = height;
>             this.birth = birth;
>             this.sex = sex;
>         }
>     }
> 
>     package com.powernode.mybatis.POJO;
>     
>     import java.util.Date;
>     
>     public class Student
>     {
>         private Long id;
>         private String name;
>         private Integer age;
>         private Double height;
>         private Date birth;
>         private Character sex;
>     
>         @Override
>         public String toString() {
>             return "Student{" +
>                     "id=" + id +
>                     ", name='" + name + '\'' +
>                     ", age=" + age +
>                     ", height=" + height +
>                     ", birth=" + birth +
>                     ", sex=" + sex +
>                     '}';
>         }
>     
>         public Long getId() {
>             return id;
>         }
>     
>         public void setId(Long id) {
>             this.id = id;
>         }
>     
>         public String getName() {
>             return name;
>         }
>     
>         public void setName(String name) {
>             this.name = name;
>         }
>     
>         public Integer getAge() {
>             return age;
>         }
>     
>         public void setAge(Integer age) {
>             this.age = age;
>         }
>     
>         public Double getHeight() {
>             return height;
>         }
>     
>         public void setHeight(Double height) {
>             this.height = height;
>         }
>     
>         public Date getBirth() {
>             return birth;
>         }
>     
>         public void setBirth(Date birth) {
>             this.birth = birth;
>         }
>     
>         public Character getSex() {
>             return sex;
>         }
>     
>         public void setSex(Character sex) {
>             this.sex = sex;
>         }
>     
>         public Student() {
>         }
>     
>         public Student(Long id, String name, Integer age, Double height, Date birth, Character sex) {
>             this.id = id;
>             this.name = name;
>             this.age = age;
>             this.height = height;
>             this.birth = birth;
>             this.sex = sex;
>         }
>     }

>     package com.powernode.mybatis.Mapper;
>     
>     import com.powernode.mybatis.POJO.Student;
>     import org.apache.ibatis.annotations.Param;
>     
>     import java.util.Date;
>     import java.util.List;
>     import java.util.Map;
>     
>     public interface StudentMapper
>     {
>     //    只要制定了Param,底层实现就可以提示底层map框架怎么实现封装
>         List<Student> selectByNameAndSex2(@Param("name") String name,@Param("sex") Character sex);
>         List<Student> selectByNameAndSex(String name,Character sex);
>         //通过POJO保存学生信息
>         int InsertStudentByPOJO(Student student);
>         //通过Map保存学生信息
>         int insertStudentsByMap(Map<String, Object> map);
>         List<Student> selectById(Long id);
>         List<Student> selectByName(String name);
>         List<Student> selectByBirth(Date birth);
>         List<Student> selectBySex(Character sex);
>     }
> 
>     package com.powernode.mybatis.Mapper;
>     
>     import com.powernode.mybatis.POJO.Student;
>     import org.apache.ibatis.annotations.Param;
>     
>     import java.util.Date;
>     import java.util.List;
>     import java.util.Map;
>     
>     public interface StudentMapper
>     {
>     //    只要制定了Param,底层实现就可以提示底层map框架怎么实现封装
>         List<Student> selectByNameAndSex2(@Param("name") String name,@Param("sex") Character sex);
>         List<Student> selectByNameAndSex(String name,Character sex);
>         //通过POJO保存学生信息
>         int InsertStudentByPOJO(Student student);
>         //通过Map保存学生信息
>         int insertStudentsByMap(Map<String, Object> map);
>         List<Student> selectById(Long id);
>         List<Student> selectByName(String name);
>         List<Student> selectByBirth(Date birth);
>         List<Student> selectBySex(Character sex);
>     }

>     package com.powernode.mybatis.Test;
>     
>     import com.powernode.mybatis.Mapper.StudentMapper;
>     import com.powernode.mybatis.POJO.Student;
>     import com.powernode.mybatis.Utils.SqlSessionUtil;
>     import org.apache.ibatis.session.SqlSession;
>     
>     import java.text.SimpleDateFormat;
>     import java.util.Date;
>     import java.util.HashMap;
>     import java.util.List;
>     import java.util.Map;
>     
>     public class Test
>     {
>         @org.junit.Test
>         public void Test()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> students = mapper.selectById(1L);
>             students.forEach(student -> {
>                 System.out.println(student);
>             });
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void TestSelect()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectByName("李四");
>             list.forEach(l ->{
>                 System.out.println(l);
>             });
>         }
>         @org.junit.Test
>         public void TestDate() throws Exception
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
>             Date birth = simpleDateFormat.parse("2000-10-11");
>             List<Student> list = mapper.selectByBirth(birth);
>             list.forEach(l -> {
>                 System.out.println(l);
>             });
>         }
>         @org.junit.Test
>         public void TestSEX()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectBySex('男');
>             list.forEach(te -> {
>                 System.out.println(te);
>             });
>         }
>         @org.junit.Test
>         public void TestInsertMap() throws Exception
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             Map<String,Object> map = new HashMap<>();
>             map.put("姓名","王五");
>             map.put("年龄",20);
>             map.put("身高",1.82);
>             map.put("性别",'男');
>             SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
>             Date date = simpleDateFormat.parse("2001-08-14");
>             map.put("生日",date);
>             mapper.insertStudentsByMap(map);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void TestInsertByPOJO()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             Student student = new Student(null,"展会",20,1.8,new Date(),'女');
>             int i = mapper.InsertStudentByPOJO(student);
>             System.out.println(i);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void selectByNameAndSex()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectByNameAndSex("张三",'男');
>             list.forEach(student -> {
>                 System.out.println(student);
>             });
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void selectByNameAndSex1()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             //mapper实际指向代理对象,内存中生成的虚拟类
>             //selectByNameAndSex2使用的是代理方法
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectByNameAndSex2("张三",'男');
>             list.forEach(student -> {
>                 System.out.println(student);
>             });
>             SqlSessionUtil.close(sqlSession);
>         }
>     }
> 
>     package com.powernode.mybatis.Test;
>     
>     import com.powernode.mybatis.Mapper.StudentMapper;
>     import com.powernode.mybatis.POJO.Student;
>     import com.powernode.mybatis.Utils.SqlSessionUtil;
>     import org.apache.ibatis.session.SqlSession;
>     
>     import java.text.SimpleDateFormat;
>     import java.util.Date;
>     import java.util.HashMap;
>     import java.util.List;
>     import java.util.Map;
>     
>     public class Test
>     {
>         @org.junit.Test
>         public void Test()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> students = mapper.selectById(1L);
>             students.forEach(student -> {
>                 System.out.println(student);
>             });
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void TestSelect()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectByName("李四");
>             list.forEach(l ->{
>                 System.out.println(l);
>             });
>         }
>         @org.junit.Test
>         public void TestDate() throws Exception
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
>             Date birth = simpleDateFormat.parse("2000-10-11");
>             List<Student> list = mapper.selectByBirth(birth);
>             list.forEach(l -> {
>                 System.out.println(l);
>             });
>         }
>         @org.junit.Test
>         public void TestSEX()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectBySex('男');
>             list.forEach(te -> {
>                 System.out.println(te);
>             });
>         }
>         @org.junit.Test
>         public void TestInsertMap() throws Exception
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             Map<String,Object> map = new HashMap<>();
>             map.put("姓名","王五");
>             map.put("年龄",20);
>             map.put("身高",1.82);
>             map.put("性别",'男');
>             SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
>             Date date = simpleDateFormat.parse("2001-08-14");
>             map.put("生日",date);
>             mapper.insertStudentsByMap(map);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void TestInsertByPOJO()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             Student student = new Student(null,"展会",20,1.8,new Date(),'女');
>             int i = mapper.InsertStudentByPOJO(student);
>             System.out.println(i);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void selectByNameAndSex()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectByNameAndSex("张三",'男');
>             list.forEach(student -> {
>                 System.out.println(student);
>             });
>             SqlSessionUtil.close(sqlSession);
>         }
>         @org.junit.Test
>         public void selectByNameAndSex1()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             //mapper实际指向代理对象,内存中生成的虚拟类
>             //selectByNameAndSex2使用的是代理方法
>             StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
>             List<Student> list = mapper.selectByNameAndSex2("张三",'男');
>             list.forEach(student -> {
>                 System.out.println(student);
>             });
>             SqlSessionUtil.close(sqlSession);
>         }
>     }