MyBatis入门程序
完成客户信息的增删改查:
根据客户编号查询客户信息。
根据客户名称、客户级别、固定电话组合查询客户信息(其中,客户名称模糊查询)
新增客户信息
修改客户信息
删除客户信息
动态SQL元素
使用< if >元素完成组合查询条件的判断(客户名、客户等级)
使用< foreach >元素完成删除多条id客户信息
掌握< resultMap >元素的使用
编译器:eclipse-javaee版
数据库:mysql
1、创建数据库
#创建一个名为boot_crm的数据库
CREATE DATABASE boot_crm;
#使用数据库boot_crm;
USE `boot_crm`;
CREATE TABLE `customer` (
`cust_id` int(32) NOT NULL AUTO_INCREMENT COMMENT '客户编号(主键)',
`cust_name` varchar(50) NOT NULL COMMENT '客户名称',
`cust_level` varchar(32) DEFAULT NULL COMMENT '客户级别',
`cust_phone` varchar(64) DEFAULT NULL COMMENT '固定电话',
PRIMARY KEY (`cust_id`)
)
2、在eclipse中创建名为Mid-term的JavaWeb项目,将Mybatis的核心JAR包、lib目录中的依赖Jar包,以及Mysql的数据驱动JAR包一同添加到项目的lib目录下。lib目录如下:
3、在src目录下创建log4j.properties文件
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.itheima=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
4、在src目录下,创建一个com.itheima.po的包,在该报下创建持久化类Customer,并类中声明cust_id,cust_name,cust_level,cust_phone属性,及对应的getter/setter方法
Customer.java
package com.itheima.po;
/**
* 客户持久化类
*/
public class Customer {
private int cust_id;//客户编号
private String cust_name;//客户姓名
private String cust_level;//客户级别
private String cust_phone;//客户电话
public int getCust_id() {
return cust_id;
}
public void setCust_id(int cust_id) {
this.cust_id = cust_id;
}
public String getCust_name() {
return cust_name;
}
public void setCust_name(String cust_name) {
this.cust_name = cust_name;
}
public String getCust_level() {
return cust_level;
}
public void setCust_level(String cust_level) {
this.cust_level = cust_level;
}
public String getCust_phone() {
return cust_phone;
}
public void setCust_phone(String cust_phone) {
this.cust_phone = cust_phone;
}
@Override
public String toString() {
return "Customer [cust_id=" + cust_id + ", cust_name=" + cust_name + ", cust_level=" + cust_level
+ ", cust_phone=" + cust_phone + "]";
}
}
5、在src目录下创建com.itheima.mapper包,并在包中创建映射文件CustomerMapper.xml
CustomerMapper.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表示命名空间 -->
<mapper namespace="com.itheima.mapper.CustomerMapper">
<!--根据客户编号获取客户信息 -->
<select id="findCustomerById" parameterType="Integer"
resultType="com.itheima.po.Customer">
select * from customer where cust_id = #{cust_id}
</select>
<!--根据客户名模糊查询客户信息列表-->
<select id="findCustomerByName" parameterType="String"
resultType="com.itheima.po.Customer">
select * from customer where cust_name like '%${value}%'
<!-- select * from customer where cust_name like concat('%',#{value},'%')-->
</select>
<!-- 添加客户信息 -->
<insert id="addCustomer" parameterType="com.itheima.po.Customer">
insert into customer(cust_name,cust_level,cust_phone)
values(#{cust_name},#{cust_level},#{cust_phone})
</insert>
<!-- 更新客户信息 -->
<update id="updateCustomer" parameterType="com.itheima.po.Customer">
update customer set
cust_name=#{cust_name},cust_level=#{cust_level},cust_phone=#{cust_phone}
where cust_id=#{cust_id}
</update>
<!-- 删除客户信息 -->
<delete id="deleteCustomer" parameterType="Integer">
delete from customer where cust_id=#{cust_id}
</delete>
<!-- <if>元素使用 -->
<select id="findCustomerByNameAndLevel" parameterType="com.itheima.po.Customer"
resultType="com.itheima.po.Customer">
select * from customer where 1=1
<if test="cust_name !=null and cust_name !=''">
and cust_name like concat('%',#{cust_name},'%')
</if> <if test="cust_level !=null and cust_level !=''">
and cust_level= #{cust_level}
</if>
</select>
<!--<foreach>元素使用 -->
<select id="deleteCustomerByIds" parameterType="List"
resultType="com.itheima.po.Customer">
delete from customer where cust_id in
<foreach item="id" index="index" collection="list" open="("
separator="," close=")">
#{id}
</foreach>
</select>
<!-- <resultMap>元素使用 -->
<resultMap type="com.itheima.po.Customer" id="resultMap">
<id property="cust_id" column="cust_id"/>
<result property="cust_name" column="cust_name"/>
<result property="cust_level" column="cust_level"/>
</resultMap>
<select id="findAllCustomer" resultMap="resultMap">
select * from customer
</select>
</mapper>
6、在src目录下,窗机Mybatis的核心配置文件mybatis-config.xml.
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>
<!--1.配置环境 ,默认的环境id为mysql-->
<environments default="mysql">
<!--1.2.配置id为mysql的数据库环境 -->
<environment id="mysql">
<!-- 使用JDBC的事务管理 -->
<transactionManager type="JDBC" />
<!--数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/boot_crm" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!--2.配置Mapper的位置 -->
<mappers>
<mapper resource="com/itheima/mapper/CustomerMapper.xml" />
</mappers>
</configuration>
6、在src目录下创建一个com.itheima.test的包,在该包下创建测试类MybatisTest,并在类中编写增删改查的测试方法。
MybatisTest.java
package com.itheima.test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
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 org.junit.Test;
import com.itheima.po.Customer;
import com.itheima.utils.MybatisUtils;
public class MybatisTest {
/**
* 根据客户编号查询客户信息
*/
@Test
public void findCustomerByIdTest() throws Exception {
// 1、读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream =
Resources.getResourceAsStream(resource);
// 2、根据配置文件构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
// 3、通过SqlSessionFactory创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
// 4、SqlSession执行映射文件中定义的SQL,并返回映射结果
Customer customer = sqlSession.selectOne("com.itheima.mapper"
+ ".CustomerMapper.findCustomerById", 2);
// 打印输出结果
System.out.println(customer.toString());
// 5、关闭SqlSession
sqlSession.close();
}
/**
* 根据用户名称来模糊查询用户信息列表
*/
@Test
public void findCustomerByNameTest() throws Exception{
// 1、读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 2、根据配置文件构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
// 3、通过SqlSessionFactory创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
// 4、SqlSession执行映射文件中定义的SQL,并返回映射结果
List<Customer> customers = sqlSession.selectList("com.itheima.mapper"
+ ".CustomerMapper.findCustomerByName", "j");
for (Customer customer : customers) {
//打印输出结果集
System.out.println(customer);
}
// 5、关闭SqlSession
sqlSession.close();
}
/**
* 添加客户
*/
@Test
public void addCustomerTest() throws Exception{
// 1、读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 2、根据配置文件构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
// 3、通过SqlSessionFactory创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
// 4、SqlSession执行添加操作
// 4.1创建Customer对象,并向对象中添加数据
Customer customer = new Customer();
customer.setCust_name("lili");
customer.setCust_level("VIP");
customer.setCust_phone("13387533092");
// 4.2执行SqlSession的插入方法,返回的是SQL语句影响的行数
int rows = sqlSession.insert("com.itheima.mapper"
+ ".CustomerMapper.addCustomer", customer);
// 4.3通过返回结果判断插入操作是否执行成功
if(rows > 0){
System.out.println("您成功插入了"+rows+"条数据!");
}else{
System.out.println("执行插入操作失败!!!");
}
// 4.4提交事务
sqlSession.commit();
// 5、关闭SqlSession
sqlSession.close();
}
/**
* 更新客户
*/
@Test
public void updateCustomerTest() throws Exception{
// 1、读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 2、根据配置文件构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
// 3、通过SqlSessionFactory创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
// 4、SqlSession执行更新操作
// 4.1创建Customer对象,对对象中的数据进行模拟更新
Customer customer = new Customer();
customer.setCust_id(3);
customer.setCust_name("lose");
customer.setCust_level("svip");
customer.setCust_phone("13311111111");
// 4.2执行SqlSession的更新方法,返回的是SQL语句影响的行数
int rows = sqlSession.update("com.itheima.mapper"
+ ".CustomerMapper.updateCustomer", customer);
// 4.3通过返回结果判断更新操作是否执行成功
if(rows > 0){
System.out.println("您成功修改了"+rows+"条数据!");
}else{
System.out.println("执行修改操作失败!!!");
}
// 4.4提交事务
sqlSession.commit();
// 5、关闭SqlSession
sqlSession.close();
}
/**
* 删除客户
*/
@Test
public void deleteCustomerTest() throws Exception{
// 1、读取配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 2、根据配置文件构建SqlSessionFactory
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
// 3、通过SqlSessionFactory创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
// 4、SqlSession执行删除操作
// 4.1执行SqlSession的删除方法,返回的是SQL语句影响的行数
int rows = sqlSession.delete("com.itheima.mapper"
+ ".CustomerMapper.deleteCustomer", 1);
// 4.2通过返回结果判断删除操作是否执行成功
if(rows > 0){
System.out.println("您成功删除了"+rows+"条数据!");
}else{
System.out.println("执行删除操作失败!!!");
}
// 4.3提交事务
sqlSession.commit();
// 5、关闭SqlSession
sqlSession.close();
}
/**
* 根据客户姓名和职业组合条件查询客户信息列表
*/
@Test
public void findCustomerByNameAndLevelTest(){
// 通过工具类生成SqlSession对象
SqlSession session = MybatisUtils.getSession();
// 创建Customer对象,封装需要组合查询的条件
Customer customer = new Customer();
customer.setCust_name("joye");
customer.setCust_level("VIP");
// 执行SqlSession的查询方法,返回结果集
List<Customer> customers = session.selectList("com.itheima.mapper"
+ ".CustomerMapper.findCustomerByNameAndJobs",customer);
// 输出查询结果信息
for (Customer customer2 : customers) {
// 打印输出结果
System.out.println(customer2);
}
// 关闭SqlSession
session.close();
}
/**
* 根据id删除多条信息
*/
@Test
public void deleteCustomerByIdsTest(){
// 获取SqlSession
SqlSession sqlSession = MybatisUtils.getSession();
// 创建List集合,封装查询id
List<Integer> ids=new ArrayList<Integer>();
ids.add(3);
ids.add(5);
int rows = sqlSession.delete("com.itheima.mapper"
+ ".CustomerMapper.deleteCustomerByIds", ids);
System.out.println("您成功删除了"+rows+"条数据!");
sqlSession.commit();
// 关闭SqlSession
sqlSession.close();
}
/**
* <resultMap>映射查询
*/
@Test
public void findAllCustomerTest() {
// 获取SqlSession
SqlSession sqlSession = MybatisUtils.getSession();
// SqlSession执行映射文件中定义的SQL,并返回映射结果
List<Customer> list = sqlSession.selectList("com.itheima.mapper.CustomerMapper.findAllCustomer");
for (Customer customer : list) {
System.out.println(customer);
}
// 关闭SqlSession
sqlSession.close();
}
}