MyBatis---环境搭建及简单的SQL映射

2023-02-07 07:54:54

数据库表结构：
MyBatis---环境搭建及简单的SQL映射
MyBatis---环境搭建及简单的SQL映射


一、环境搭建

       1、导入jar包。
       2、编写两个配置文件。一个是全局的mybatis配置文件，用来指定连接哪个数据库的。另一个是相当于接口的实现类，第二个配置文件需要在第一个中注册。

       全局配置文件：

	<?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>
	    <environments default="development">
	        <environment id="development">
	            <transactionManager type="JDBC"/>
	            <dataSource type="POOLED">
	                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
	                <property name="url" value="jdbc:mysql://localhost:3306/test_mybatis?serverTimezone=GMT%2B8&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;useAffectedRows=true"/>
	                <property name="username" value="root"/>
	                <property name="password" value="123456"/>
	            </dataSource>
	        </environment>
	    </environments>
	    <mappers>
	    <!--  注册接口的实现  --!>
	        <mapper resource="mybatis/StudentDao.xml"/>
	    </mappers>
	</configuration>

       第二个配置文件：

	<!DOCTYPE mapper
	        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
	        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
	
	<mapper namespace="com.cj.dao.StudentDao">
	    <insert id="Add">
	        insert into student(STUDENT_NAME,STUDENT_NUMBER,STUDENT_AGE) values(#{STUDENT_NAME},#{STUDENT_NUMBER},#{STUDENT_AGE})
	    </insert>
	
	    <delete id="Delete">
	        delete from student where STUDENT_ID=#{id}
	    </delete>
	
	    <update id="Update">
	        update student set STUDENT_NAME=#{param2.STUDENT_NAME},STUDENT_NUMBER=#{param2.STUDENT_NUMBER},STUDENT_AGE=#{param2.STUDENT_AGE} where STUDENT_ID=#{param1}
	    </update>
	
	    <select id="QueryById" resultType="com.cj.entity.Student">
	        select * from student where STUDENT_ID=#{id}
	    </select>
	</mapper>

细节：
       ① 接口实现的配置文件中namespace用来指定实现的接口的全类名。

       ② JDBC数据源配置的URL中拼接参数的时候用&amp;代替&。

       ③ MyBatis框架底层用的还是原生的jdbc，因此当更新的时候要想返回影响的行数，必须带上useAffectedRows=true。

       ④ 增删改查分别对应一种标签，只有查询需要在配置文件中显示地指定返回值类型，也会涉及到更多的数据库查询细节。

       ⑤ #{ }这种形式其实是preparedStatement的预编译模式，用 # 可以取到接口中抽象方法的参数。当参数只有一个的时候，可以用任意的变量名取到。当携带一个以上参数时候，MyBatis会将这些参数放到map中，key是“1”、“param1”等等。当参数是POJO的时候，用实体类对应的成员变量也可以在sql语句中取到值。

       ⑥ 可以在全局配置文件中用package完成批量注册。name指定扫描的包名，某个接口实现的配置文件必须要和接口放一起，并且文件名要相同。可以在类路径下面新建出和相应接口相同的包来完成批量注册的目标。

       3、写接口。
       接口代码：

public interface StudentDao {
    public int Add(Student student);

    public boolean Delete(Integer id);

    public int Update(Integer id, Student student);

    public Student QueryById(Integer id);
}

       4、运行helloworld。
       测试代码：

public class MybatisTest {

    @Test
    public void test01() {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession(true);   //设置为自动提交
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            int i = studentDao.Add(new Student(0, "daxx", "123", 22));
            System.out.println(i);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
//            sqlSession.commit();
            sqlSession.close();
        }
    }

    @Test
    public void test02() {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession(true);   //设置为自动提交
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            boolean b = studentDao.Delete(5);
            System.out.println(b);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
//            sqlSession.commit();
            sqlSession.close();
        }
    }

    @Test
    public void test03() {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession(true);   //设置为自动提交
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            int i = studentDao.Update(6, new Student(0, "小米", "12345", 18));
            System.out.println(i);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
//            sqlSession.commit();
            sqlSession.close();
        }
    }

    @Test
    public void test04() throws IOException {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession();   //增删改需要设置自动提交, 查询不需要手动设置
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            Student student = studentDao.QueryById(7);
            System.out.println(student);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
}

       细节：
       查询出来的结果会自动提交，不需要手动设置。但是增删改操作需要手动提交，可以在new SqlSession对象的时候传入参数true表示会自动提交，或者调用SqlSession 对象.commit()来提交。




二、简单的SQL映射

1、获取自增的主键【Mysql、SQL Server】

       将自动生成的key赋值给POJO实体类的STUDENT_ID。

 	<insert id="Add" useGeneratedKeys="true" keyProperty="STUDENT_ID">
        insert into student(STUDENT_NAME,STUDENT_NUMBER,STUDENT_AGE) values(#{STUDENT_NAME},#{STUDENT_NUMBER},#{STUDENT_AGE})
    </insert>

	@Test
    public void test01() {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession(true);   //设置为自动提交
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            Student student = new Student(0, "大明", "12345", 21);
            int i = studentDao.Add(student);
//            System.out.println(i);
            System.out.println("自增的id是" + student.getSTUDENT_ID());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
//            sqlSession.commit();
            sqlSession.close();
        }
    }

MyBatis---环境搭建及简单的SQL映射

2、查询结果封装成List
 public List<Student> QueryAll();

	<select id="QueryAll" resultType="com.cj.entity.Student">
        select * from student
    </select>

	@Test
    public void test05() throws IOException {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession();   //增删改需要设置自动提交, 查询不需要手动设置
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            List<Student> students = studentDao.QueryAll();
            for (Student s : students) {
                System.out.println(s);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

       测试结果：
MyBatis---环境搭建及简单的SQL映射

3、查询结果返回map
	@MapKey("STUDENT_ID")	//指定表中的某列作为key
    public Map<Integer,Student> QueryAllReturnMap();

	<select id="QueryAllReturnMap" resultType="com.cj.entity.Student">
        select * from student
    </select>

	@Test
    public void test06() throws IOException {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession();   //增删改需要设置自动提交, 查询不需要手动设置
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            Map<Integer, Student> students = studentDao.QueryAllReturnMap();
            Set<Integer> set = students.keySet();
            for (Integer i : set) {
                System.out.println("key=" + i + ", value=" + students.get(i));
            }
//            System.out.println(students);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

       测试结果：
MyBatis---环境搭建及简单的SQL映射

4、自定义封装规则

       当数据库表中的列名和javaBean中成员变量名不一致的时候，不能进行映射，这时候需要起别名或者用resultMap自定义封装规则。

取别名：
public class Cat {
    private int CAT_ID;
    private String CAT_NAME;
    private int CAT_GENDER;
    private int CAT_AGE;
    ...
}


public interface CatDao {
    public Cat QueryById(Integer id);
}

<mapper namespace="com.cj.dao.CatDao">
    <select id="QueryById" resultType="com.cj.entity.Cat">
        select id CAT_ID, name CAT_NAME, gender CAT_GENDER, age CAT_AGE from cat where id=#{id}
    </select>
</mapper>

	@Test
    public void test07() throws IOException {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession();   //增删改需要设置自动提交, 查询不需要手动设置
            CatDao catDao = sqlSession.getMapper(CatDao.class);
            Cat cat = catDao.QueryById(2);
            System.out.println(cat);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

       测试结果：
MyBatis---环境搭建及简单的SQL映射

自定义封装规则：

       主键用id标签，普通的用result标签。column指定表的列名, property指定对应实体类中的成员名。

<mapper namespace="com.cj.dao.CatDao">
    <select id="QueryById" resultMap="catMapping">
        select * from cat where id=#{id}
    </select>

    <resultMap id="catMapping" type="com.cj.entity.Cat">
        <id column="id" property="CAT_ID"></id>
        <result column="name" property="CAT_NAME"></result>
        <result column="gender" property="CAT_GENDER"></result>
        <result column="age" property="CAT_AGE"></result>
    </resultMap>
</mapper>

	@Test
    public void test07() throws IOException {
        String resource = "mybatis-config.xml";
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

            sqlSession = sqlSessionFactory.openSession();   //增删改需要设置自动提交, 查询不需要手动设置
            CatDao catDao = sqlSession.getMapper(CatDao.class);
            Cat cat = catDao.QueryById(1);
            System.out.println(cat);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

       测试结果：

MyBatis---环境搭建及简单的SQL映射

MyBatis---环境搭建及简单的SQL映射MyBatis---环境搭建及简单的SQL映射 cj1561435010 发布了25 篇原创文章 · 获赞 5 · 访问量 1521 私信 关注
上一篇：MyBatis(五)MyBatis整合Spring原理分析


下一篇：Mybatis精讲(二)---生命周期