Mybatis实现数据的增删改查
什么是 MyBatis ？
MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
源于Mybatis官方网站点>>这里<<进入官网
如果使用IDEA工具需要导入相关依赖
<dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.4.6</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.46</version></dependency></dependencies>
对一个TbHotel表的CRUD操作
package com.kmyang.mybatis.beans;public class TbHotel {private int hotelId;private String hotelName;private String hotelImage;private TbHotelType hotelType;public TbHotelType getHotelType() {return hotelType;}public void setHotelType(TbHotelType hotelType) {this.hotelType = hotelType;}public int getHotelId() {return hotelId;}public void setHotelId(int hotelId) {this.hotelId = hotelId;}public String getHotelName() {return hotelName;}public void setHotelName(String hotelName) {this.hotelName = hotelName;}public String getHotelImage() {return hotelImage;}public void setHotelImage(String hotelImage) {this.hotelImage = hotelImage;}}在Src目录下建一个mybatis的xml核心配置文件mybatis.cfg.xml,这里有两种方式连接数据库
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><!--加载属性文件--><properties resource="jdbc.properties"/><!--数据库配置,default表示默认采用哪一个数据库环境--><environments default="development"><!--生产环境数据库--><environment id="product"><!--使用JDBC事务管理，事务控制由mybatis--><transactionManager type="JDBC"/><!--连接池配置，type为POOLED采用连接池--><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/test?useSSL=TRUE"/><property name="username" value="root"/><property name="password" value="admin1806"/></dataSource></environment><!--开发环境数据库--><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${driver}"/><property name="url" value="${url}"/><property name="username" value="${username}"/><property name="password" value="${password}"/></dataSource></environment></environments><!--加载映射文件--><mappers><mapper resource="mapper/hotel.mapper.xml"/></mappers>
</configuration>
采用development方式则需要配置jdbc.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=TRUE
username=root
password=admin1806
定义hotel mappers的hotel.mapper.xml配置文件,配置文件实现了接口和SQL语句的映射关系。采用resultMap可以把属性和数据库列名映射关系定义好，property为类的属性，column是表的列名，也可以是表列名的别名
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--namespace 命名空间，类似包名，起到的作用作用跟其他文件中的配置隔离-->
<!--命名空间需要是唯一的-->
<!--规范：项目的Bean文件的包名+Bean类名+Mapper单词，比如：com.qianfeng.mybatis.beans.TbHotelMapper-->
<mapper namespace="com.kmyang.project.hotel.beans.TbHotelMapper"><!--结果集映射--><!--id是唯一标识，type是要映射的Java对象的地址--><resultMap id="hotelResultMap" type="com.kmyang.project.hotel.beans.TbHotel"><id column="hotel_id" property="hotelId" javaType="java.lang.Integer"/><result column="hotel_img" property="hotelImg" javaType="java.lang.String"/><result column="hotel_name" property="hotelName" javaType="java.lang.String"/><result column="hotel_tel" property="hotelTel" javaType="java.lang.String"/><result column="hotel_star" property="hotelStar" javaType="java.lang.String"/></resultMap><!--在当前namespace中id唯一--><!--根据ID查询单个属性值--><select id="queryHotelInfoById" resultType="java.lang.String">select hotel_name from tb_hotel where hotel_id = 1;</select><!--在当前namespace中id唯一--><!--查询所有酒店名称--><select id="queryHotelNameList" resultType="java.lang.String">select hotel_name from tb_hotel;</select><!--resultMap关联结果映射配置--><!--根据Id查询该酒店的所有信息--><select id="queryHotelById" resultMap="hotelResultMap">select hotel_id, hotel_name, hotel_tel, hotel_star, hotel_img from tb_hotel where hotel_id = 2;</select><!--查询酒店信息总条数--><select id="queryCount" resultType="java.lang.Integer">select count(hotel_id) from tb_hotel;</select><!--酒店列表一页显示四条数据--><select id="queryHotelByPage" resultMap="hotelResultMap">select hotel_id, hotel_tel, hotel_star, hotel_img, hotel_name from tb_hotel limit #{page}, #{pageSize};</select><!--添加酒店相关信息--><insert id="save">insert into tb_hotel(hotel_name, hotel_tel, hotel_star, hotel_img) value (#{hotelName}, #{hotelTel}, #{hotelStar}, #{hotelImg});</insert><!--根据ID删除酒店信息--><delete id="remove" parameterType="java.lang.String">delete from tb_hotel where hotel_id = 11;</delete><!--根据酒店ID修改酒店信息--><update id="update" parameterType="java.lang.String">update tb_hotel set hotel_name = '乔家大酒店' where hotel_id = 9;</update><!--#与$的区别,#防止spl注入攻击,安全性更高--><select id="checkHotelName" resultType="java.lang.String">select hotel_name from tb_hotel where hotel_id = ${id};</select><!--<select id="checkHotelName" resultType="java.lang.String">--><!--select hotel_name from tb_hotel where hotel_id = #{id};--><!--</select>-->
</mapper>
这里直接开始测试
package com.kmyang.project.mybatis;import com.kmyang.project.hotel.beans.TbHotel;
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 java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;/*** @Auther: DELL* @Date: 2018/12/25 17:11* @Description: Mybatis相关功能测试*/
public class MybatisTest {@Test/*** 功能描述: 根据ID查询单个属性值* @auther: kmyang* @date: 2018/12/25*/public void testCaseOne(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化MyBatisSqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = sqlSessionFactory.openSession();String hotelName = sqlSession.selectOne("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelInfoById");System.out.println(hotelName);sqlSession.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: 查询所有酒店名称* @auther: kmyang* @date: 2018/12/25 */@Testpublic void testCaseTwo(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = sqlSessionFactory.openSession();List<Object> hotelNameList = sqlSession.selectList("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelNameList");System.out.println(hotelNameList);sqlSession.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: 根据Id查询该酒店的所有信息* @auther: DELL* @date: 2018/12/25*/@Testpublic void testCaseThree(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);SqlSession session = build.openSession();TbHotel hotel = session.selectOne("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelById");System.out.println(hotel.toString());session.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: 查询酒店信息总条数* @auther: kmyang* @date: 2018/12/25*/@Testpublic void testCaseFour(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = build.openSession();Integer count = sqlSession.selectOne("com.kmyang.project.hotel.beans.TbHotelMapper.queryCount");System.out.println(count);sqlSession.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: 酒店列表一页显示四条数据* @auther: kmyang* @date: 2018/12/25*/@Testpublic void testCaseFive(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = build.openSession();//传参HashMap<String, Object> hashMap = new HashMap<String, Object>();//key需要和mybatis映射文件中的#(index)单词一致hashMap.put("page", 0);hashMap.put("pageSize", 4);List<TbHotel> hotels = sqlSession.selectList("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelByPage", hashMap);System.out.println(hotels);sqlSession.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: 添加酒店相关信息* @auther: kmyang* @date: 2018/12/25*/@Testpublic void testCaseSix(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = build.openSession();TbHotel hotel = new TbHotel();hotel.setHotelName("武汉大酒店");hotel.setHotelTel("13345621325");hotel.setHotelStar("三星级");
// for (int i = 0; i < 5000000; i++) {
// hotel.setHotelName("武汉大酒店");
// hotel.setHotelTel("10" + i);
// hotel.setHotelStar("三星级");
// int a = sqlSession.insert("com.kmyang.project.hotel.beans.TbHotelMapper.save", hotel);
// }//开启事务,mybatis已默认开启int i = sqlSession.insert("com.kmyang.project.hotel.beans.TbHotelMapper.save", hotel);//提交事务,需要手动提交sqlSession.commit();
// System.out.println(i);sqlSession.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: 根据ID删除酒店信息* @auther: kmyang* @date: 2018/12/26*/@Testpublic void testCaseServen(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession session = sessionFactory.openSession();int i = session.delete("com.kmyang.project.hotel.beans.TbHotelMapper.remove");session.commit();System.out.println(i);session.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: 根据酒店ID修改酒店信息* @auther: kmyang* @date: 2018/12/26*/@Testpublic void testCaseEight(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession session = sessionFactory.openSession();int i = session.update("com.kmyang.project.hotel.beans.TbHotelMapper.update");System.out.println(i);session.commit();session.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}/*** 功能描述: #与$的区别,#防止spl注入攻击,安全性更高* @auther: kmyang* @date: 2018/12/25*/@Testpublic void testCaseNine(){try {InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");//初始化mybatisSqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession = build.openSession();HashMap<String, Object> hashMap = new HashMap<String, Object>();hashMap.put("id", "111 or 1=1");List<Object> list = sqlSession.selectList("com.kmyang.project.hotel.beans.TbHotelMapper.checkHotelName", hashMap);System.out.println(list);sqlSession.close();inputStream.close();} catch (IOException e) {e.printStackTrace();}}
}