2021-06-02 MyBatis框架实验(2个学时)

一、实验目的

熟悉JDBC、MySQL、JUnit和MyBatis等相关技术，培养良好的代码风格，为其他实验做好准备。

二、实验内容

在熟悉MySQL、Eclipse或MyEclipse开发环境基础上，创建一个学生表，使用Mybatis技术实现对学生对象的增删改查操作，其中查询包括根据学生ID查询、根据名字的模糊查找和通过姓名和住址的组合查询（姓名和住址可以同时给出也可以单独给出）。

数据库的名称为“db_你的名字首字母组合”（如“db_gbl”），建表的SQL语句为：
CREATE TABLE students (
Id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
age int(11) NOT NULL,
gender varchar(255) DEFAULT NULL,
number varchar(20) DEFAULT NULL,
address varchar(20) DEFAULT NULL,
status int(11) NOT NULL DEFAULT 1,
PRIMARY KEY (id) )

项目框架：

Student类

package com.itheima.po;

public class Student {
	private Integer id;
	private String name;
	private Integer age;
	private String gender;
	private String number;
	private String address;
	private Integer status;
	public Integer getId() {
		return id;
	}
	public void setId(Integer 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 String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}

	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Integer getStatus() {
		return status;
	}
	public void setStatus(Integer status) {
		this.status = status;
	}
	@Override
	public String toString() {
		return "Student [id = "+ id + ",name = " + name + ",age = " + age + 
				 ", gender = "+ gender +",address = "+ address +",status = " + status + "]";
	}
}

StudentMapper.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">
	
	<!-- 定义映射文件的命名空间 -->
<mapper namespace = "com.itheima.mapper.StudentMapper">

	<select id="findStudentByNameAndAddress" parameterType = "String"
		resultType = "com.itheima.po.Student">
		<!-- parameterType ：传入参数的类型    resultType： 返回结果的类型 -->
		select *from students where 1=1
	   	<if test = " name != null and name !=''">
			and  name like concat('%',#{name}, '%')
		</if>
		 
		<if test = "address != null and address !=''">
			and address = #{address}
		</if>
	
	</select>
	<select id="findStudentByName" parameterType = "String"
		resultType = "com.itheima.po.Student">
		<!-- parameterType ：传入参数的类型    resultType： 返回结果的类型 -->
		select *from students where name like '%${value}%'
	</select>
	
	<select id="findStudentById" parameterType = "Integer"
		resultType = "com.itheima.po.Student">
		<!-- parameterType ：传入参数的类型    resultType： 返回结果的类型 -->
		select *from students where id = #{id}
	</select>
</mapper>

MybatisUtils.java

package com.itheima.utils;

import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisUtils {
	private static SqlSessionFactory sqlSessionFactory = null;
	static {
		try {
			Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static SqlSession getSession() {
		return sqlSessionFactory.openSession();
	}
}

MybatisTest.java

package com.itheima.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
import com.itheima.po.Student;
import com.itheima.utils.MybatisUtils;

public class MybatisTest {
	/**
	 * 根据姓名和地址组合条件查询客户信息
	 */
 
	@Test
	public void findStudentByNameAndAddressTest() {
        
        SqlSession session = MybatisUtils.getSession();
        Student student = new Student();
        student.setName("lisi");
        student.setAddress("beijing");
        List<Student> student1 = session.selectList("com.itheima.mapper"+
        		".StudentMapper.findStudentByNameAndAddress",student);
        for (Student student2 : student1) {
        	System.out.println(student2);
        }
        session.close();	 
	}
	/**
	 * 根据姓名模糊查询客户信息
	 */
	@Test
	public void findStudentByNameTest() {
        
        SqlSession session = MybatisUtils.getSession();
        List<Student> student1 = session.selectList("com.itheima.mapper"+
        		".StudentMapper.findStudentByName","l");
        for (Student student2 : student1) {
        	System.out.println(student2);
        }
        session.close();	 
	}
	/**
	 * 根据id查询客户信息
	 */
	@Test
	public void findStudentByIdTest() {
        
        SqlSession session = MybatisUtils.getSession();
        Student student = session.selectOne("com.itheima.mapper"+
        		".StudentMapper.findStudentById",1);
        System.out.println(student.toString());
        session.close();	 
	}
}	

db.properties

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/db_ljh
jdbc.username = root
jdbc.password = root

log4j.properties

log4j.rootLogger=ERROR, stdout
log4j.logger.com.itheima = DEBUG

log4j.appender.stdout = org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
 
log4j.appender.stdout.layout.ConversionPattern= %5p [%t] - %m%n

Mybatis-config.xml

<?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="db.properties"/>
    <!--配置默认使用那个环境变量  以id作为标志-->
    <environments default="mysql">
        <!--配置连接环境-->
        <environment id="mysql">
            <!--事务管理器使用JDBC-->
            <transactionManager type="JDBC"/>
            <!--数据源   连接池使用该POOLED-->
            <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>
        <mapper resource="com/itheima/mapper/StudentMapper.xml"/>
    </mappers>
</configuration>

结果截图：
根据姓名和地址组合条件查询客户信息

根据姓名模糊查询客户信息

根据id查询客户信息