查询所有的数据xml语句:
<?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"> <!-- namespace:映射文件的命名空间,内容规范就是映射文件的包名.此xml文件名 --> <mapper namespace="xxx.x.mapper.PersonTestMapper"> <!-- resultMap的type是实体类(实体数据类型),id随便起是resultMap唯一标识,下面原来的resultType去掉改成resultMap,值就是这个id 除了表里的id列用id表示外,其余都用result表示,所有属性都要写 column表示表里的字段名,property表示实体类中属性的名 --> <resultMap type="xxx.x.Person" id="BaseResultMap"> <id column = "person_id" property = "personId" /> <result column = "name" property = "name" /> <result column = "gender" property = "gender" /> <result column = "person_addr" property = "personAddr" /> <result column = "birthday" property = "birthday" /> </resultMap> <!-- parameterType是传递参数的类型,下面为整型写法 sql语句最后一个参数id是变量 resultType是查询后的结果,内容是需要输出到的地址,那个Person类里需要定义好那些需要接受数据的属性 #{}使用预编译的方式生成SQL,防止sql的注入 --> <select id="selectPersonByID" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select * from person_test where id = #{id} </select> <select id="selectPersonCount" resultType="java.lang.Integer" > select count(*) from person </select> <select id="selectPersonAll" resultMap="BaseResultMap" > select * from person </select> </mapper>
执行语句:
SqlSessionFactory sessionFactory; public void setUp() throws Exception { InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml"); sessionFactory = new SqlSessionFactoryBuilder().build(in); } public void test() { //创建SqlSession SqlSession session = sessionFactory.openSession(); try { List<Person> pList = session.selectList("xxx.x.mapper.PersonTestMapper.selectPersonAll"); //查找到要执行的sql语句,用命名空间.id for(Person p : pList) { System.out.println(p); } }finally { session.close(); } }
按多个参数查询:
<select id="selectPersonByParams" parameterType="xxx.x.QueryCondition" resultMap="BaseResultMap" > <![CDATA[ select * from person t where t.gender = #{gender} and t.birthday < #{birthday} ]]> </select> <!-- 第二种方式:传递集合 map.put("gender", 1); map.put("birthday", new Data()); 多个参数想查询也可以传递map集合,#{}中的内容map的key --> <select id="selectPersonByParams1" parameterType="java.util.Map" resultMap="BaseResultMap" > <![CDATA[ select * from person t where t.gender = #{gender} and t.birthday < #{birthday} ]]> </select>
第一种方式java语句:
SqlSessionFactory sessionFactory; public void setUp() throws Exception { InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml"); sessionFactory = new SqlSessionFactoryBuilder().build(in); }
public void selectPersonByParams() { //创建SqlSession SqlSession session = sessionFactory.openSession(); try { QueryCondition qc = new QueryCondition(); qc.setGender(1); qc.setBirthday(new Date()); List<Person> pList = session.selectList("xxx.x.mapper.PersonTestMapper.selectPersonParams",qc); //查找到要执行的sql语句,用命名空间.id for(Person p : pList) { System.out.println(p); } }finally { session.close(); } }
第二种方式java语句:
SqlSessionFactory sessionFactory; public void setUp() throws Exception { InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml"); sessionFactory = new SqlSessionFactoryBuilder().build(in); }
public void selectPersonByParams1() { //创建SqlSession SqlSession session = sessionFactory.openSession(); try { Map<String,Object> map = new HashMap<String,Object>(); map.put("gender", 1); map.put("birthday", new Data()); List<Person> pList = session.selectList("xxx.x.mapper.PersonTestMapper.selectPersonParams1",map); //查找到要执行的sql语句,用命名空间.id for(Person p : pList) { System.out.println(p); } }finally { session.close(); } }
模糊查询
<!-- ${}:非预编译,使用直接拼接字符串的方式生成sql,{}中的内容必须要从map或者查询对象中获得 --> <select id="selectPersonByName" parameterType="java.util.Map" resultMap="BaseResultMap" > select * from person t where t.name like ‘%${name}%’ </select>
java语句:
SqlSessionFactory sessionFactory; public void setUp() throws Exception { InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml"); sessionFactory = new SqlSessionFactoryBuilder().build(in); } public void selectPersonByName() { //创建SqlSession SqlSession session = sessionFactory.openSession(); try { Map<String,Object> map = new HashMap<String,Object>(); map.put("name", "张三"); List<Person> pList = session.selectList("xxx.x.mapper.PersonTestMapper.selectPersonByName",map); //查找到要执行的sql语句,用命名空间.id for(Person p : pList) { System.out.println(p); } }finally { session.close(); } }