Mybatis的select查询的三种方式
1、首先建立一个测试的dao
1 public interface IStudentDao { 2 3 // 根据姓名查询 4 List<Student> selectStudentsByName(String name); 5 }
2、对这个dao进行实现
1 public class StudentDaoImpl implements IStudentDao { 2 3 private SqlSession sqlSession; 4 5 // 根据姓名查询 6 public List<Student> selectStudentsByName(String name) { 7 List<Student> students = null; 8 9 try { 10 sqlSession = MybatisUtil.getSqlSession(); 11 students = sqlSession.selectList("selectStudentsByName", name); 12 } catch (Exception e) { 13 e.printStackTrace(); 14 } finally { 15 if (sqlSession != null) { 16 sqlSession.close(); 17 } 18 } 19 return students; 20 } 21 }
3、utils的书写
1 public class MybatisUtil { 2 private static SqlSessionFactory sqlSessionFactory; 3 4 public static SqlSession getSqlSession() { 5 try { 6 if (sqlSessionFactory == null) { 7 // 读取配置文件 8 InputStream inputStream = Resources 9 .getResourceAsStream("Mybatis.xml"); 10 // 创建工厂 11 sqlSessionFactory = new SqlSessionFactoryBuilder() 12 .build(inputStream); 13 } 14 } catch (IOException e) { 15 e.printStackTrace(); 16 } 17 return sqlSessionFactory.openSession(); 18 } 19 }
4、配置文档
(1)Mybatis.xml
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 4 <configuration> 5 6 <properties resource="jdbc.properties" /> 7 8 <!-- 别名标签 --> 9 <typeAliases> 10 <typeAlias type="com.liuya.demo.mybaits.crud.pojo.Student" 11 alias="Student" /> 12 </typeAliases> 13 14 <!-- 配置运行的数据库环境 --> 15 <environments default="mysqlenvironment"> 16 <environment id="mysqlenvironment"> 17 <!-- 連接池在本地连接中使用,在SSM中不用,用C3P0和DBCP --> 18 <transactionManager type="JDBC" /> 19 <dataSource type="POOLED"> 20 <property name="driver" value="${driver}" /> 21 <property name="url" value="${url}" /> 22 <property name="username" value="${username}" /> 23 <property name="password" value="${password}" /> 24 </dataSource> 25 </environment> 26 </environments> 27 28 <!-- 连接映射文件 --> 29 <mappers> 30 <!-- 最终使用的都是package --> 31 <mapper resource="com/liuya/demo/mybaits/crud/mapper/StudentMapper.xml" /> 32 </mappers> 33 </configuration>
(2)properties的书写
# ##正式服务器 driver = com.mysql.jdbc.Driver url = jdbc:mysql://127.0.0.1:3306/crud?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true username = root password = 123456
(3)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="test"> <!-- 根据姓氏模糊查询 --> <select id="selectStudentsByName" resultType="Student"> select NAME,AGE,SCORE from STUDENT where NAME like concat ('%',#{ooo},'%') </select> </mapper>
5、测试的书写
1 public class MyTest { 2 3 private IStudentDao idao; 4 5 @Before 6 public void before() { 7 idao = new StudentDaoImpl(); 8 } 9 // 根据name查询一个学生 10 @Test 11 public void testSelectStudentsByName() { 12 System.out.println("开始查询学生"); 13 List<Student> students = idao.selectStudentsByName("张"); 14 for (Student student : students) { 15 System.out.println(student); 16 } 17 System.out.println("查询学生成功"); 18 } 19 }
这就是一个完整的select查询的书写,重点在于mapper中的select书写
写法一(比较复杂):
<!-- 根据姓氏模糊查询 -->
<select id="selectStudentsByName" resultType="Student">
select
NAME,AGE,SCORE from STUDENT where NAME like concat ('%',#{ooo},'%')
</select>
写法二(一般使用的比较多):
<!-- 根据姓氏模糊查询 --> <select id="selectStudentsByName" resultType="Student"> select NAME,AGE,SCORE from STUDENT where NAME like '%'#{ooo}'%' </select>
写法三(不建议使用,会有sql注入问题和加载速度慢的问题):
<!-- 根据姓氏模糊查询 --> <select id="selectStudentsByName" resultType="Student"> select NAME,AGE,SCORE from STUDENT where NAME like '%&{value}%' </select>
like后的括号值,只能是value。
十年磨一剑,一剑破万法