工具: mysql 5.5.62 IDEA
参考自:https://www.cnblogs.com/best/p/5688040.html
遇到的问题: 无法读取src/main/java下配置文件, 解决方法参考自https://blog.csdn.net/qq_32778043/article/details/80746668
项目目录如下:
1 创建maven项目,添加jar包
首先不使用模板创建一个maven项目,groupid填com.admin, artifactid为MybatisTest,添加jar包,修改pom.xml为:
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.admin</groupId> <artifactId>MybatisTest</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <!-- mybatis ORM框架 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.1</version> </dependency> <!--mysql数据库驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.12</version> </dependency> <!-- JUnit单元测试工具 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.10</version> </dependency> </dependencies> <!--解决IDEA无法读取src/main/java/配置文件问题--> <build> <finalName>MybatisTest</finalName> <resources> <resource> <directory>${basedir}/src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> </resource> <resource> <directory>${basedir}/src/main/resources</directory> </resource> </resources> </build> </project>
2 添加mybatis配置文件
在src/main/java包下添加mybatis配置文件MyBatisCfg.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"></properties> <typeAliases> <package name="com.admin.entities" /> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${mysql.driver}" /> <property name="url" value="${mysql.url}" /> <property name="username" value="${mysql.uid}" /> <property name="password" value="${mysql.password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="com/admin/mapper/GoodsMapper.xml" /> </mappers> </configuration>
在src/main/java目录下添加数据库配置文件db.properties文件(对应MyBatisCfg.xml中db.peroperties),mysql-connector-java-8.0.12对应driver及url与前面的版本有些差异
mysql.driver=com.mysql.cj.jdbc.Driver mysql.url=jdbc:mysql://localhost:3306/db1?serverTimezone=GMT%2B8 mysql.uid=root mysql.password=123456
3 创建DAO接口GoodsDAO及对应的GoodsMapper.xml
在src/main/java下新建com.admin包,其下再新建entities包及mapper包
在com.admin.entities(与MyBatisCfg.xml中package name对应)中创建Goods类Goods.java
1 package com.admin.entities; 2 3 import java.io.Serializable; 4 5 public class Goods implements Serializable { 6 7 private static final long serialVersionUID = 1L; 8 9 private int id; 10 11 private String name; 12 13 14 private double price; 15 16 private String picture; 17 18 public int getId() { 19 return id; 20 } 21 22 public void setId(int id) { 23 this.id = id; 24 } 25 26 public String getName() { 27 return name; 28 } 29 30 public void setName(String name) { 31 this.name = name; 32 } 33 34 public double getPrice() { 35 return price; 36 } 37 38 public void setPrice(double price) { 39 this.price = price; 40 } 41 42 public String getPicture() { 43 return picture; 44 } 45 46 public void setPicture(String picture) { 47 this.picture = picture; 48 } 49 50 @Override 51 public String toString() { 52 return "Goods{" + 53 "id=" + id + 54 ", name='" + name + '\'' + 55 ", price=" + price + 56 ", picture='" + picture + '\'' + 57 '}'; 58 } 59 }
在com.admin.mapper包下创建GoodsDAO接口及GoodsMapper.xml
GoodsDAO.java
1 package com.admin.mapper; 2 3 4 import com.admin.entities.Goods; 5 import org.apache.ibatis.annotations.Param; 6 7 import java.util.List; 8 9 public interface GoodsDAO { 10 11 public List<Goods> getGoodsPager(@Param("skip")int skip, @Param("size") int size); 12 13 public Goods getGoodsById(int id); 14 15 public int getGoodsCount(); 16 17 public int insert(Goods entity); 18 19 public int delete(int id); 20 21 public int update(Goods entity); 22 }
GoodsMapper.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <!--命名空间应该是对应接口的包名+接口名 --> 4 <mapper namespace="com.admin.mapper.GoodsDAO"> 5 <!--获得商品信息并分页 --> 6 <select id="getGoodsPager" resultType="Goods"> 7 select 8 id,name,price,picture from goods limit #{skip},#{size} 9 </select> 10 <!-- 获得单个商品通过编号 --> 11 <select id="getGoodsById" parameterType="int" resultType="Goods"> 12 select 13 id,name,price,picture from goods where id=#{id} 14 </select> 15 <!--获得商品总数 --> 16 <select id="getGoodsCount" resultType="int"> 17 select count(*) from goods 18 </select> 19 <!--新增加商品 --> 20 <insert id="insert" parameterType="Goods"> 21 insert into 22 goods(name,price,picture) values(#{name},#{price},#{picture}); 23 </insert> 24 <!-- 删除商品 --> 25 <delete id="delete"> 26 delete from goods where id=#{id} 27 </delete> 28 <!-- 修改商品 --> 29 <update id="update" parameterType="Goods"> 30 update goods set 31 name=#{name},price=#{price},picture=#{picture} where id=#{id} 32 </update> 33 </mapper>
4 创建junit测试类
在src/test/java创建包test,创建两个类MyBatisUtil.java和TestGoods.java
MyBatisUtil.java
1 package test; 2 3 import org.apache.ibatis.session.SqlSession; 4 import org.apache.ibatis.session.SqlSessionFactory; 5 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 6 7 import java.io.InputStream; 8 9 public abstract class MyBatisUtil { 10 11 public static SqlSessionFactory getSqlSessionFactory(){ 12 // 获得环境配置文件流 13 InputStream config = MyBatisUtil.class.getClassLoader().getResourceAsStream("MyBatisCfg.xml"); 14 // 创建sql会话工厂 15 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(config); 16 return factory; 17 } 18 19 //获得会话 20 public static SqlSession getSession(){ 21 return getSqlSessionFactory().openSession(true); 22 } 23 24 /** 25 * 获得得sql会话 26 * @param isAutoCommit 是否自动提交,如果为false则需要sqlSession.commit();rollback(); 27 * @return sql会话 28 */ 29 public static SqlSession getSession(boolean isAutoCommit){ 30 return getSqlSessionFactory().openSession(isAutoCommit); 31 } 32 33 }
TestGoods.java
1 package test; 2 3 import com.admin.entities.Goods; 4 import com.admin.mapper.GoodsDAO; 5 import org.apache.ibatis.session.SqlSession; 6 import org.junit.Assert; 7 import org.junit.Test; 8 9 import java.util.List; 10 11 public class TestGoods { 12 13 @Test 14 public void getGoodsPagerTest() { 15 int skip = 4; 16 int size = 2; 17 SqlSession session = MyBatisUtil.getSession(); 18 try { 19 GoodsDAO bookdao = session.getMapper(GoodsDAO.class); 20 List<Goods> goods = bookdao.getGoodsPager(skip, size); 21 Assert.assertEquals(2, goods.size()); 22 } finally { 23 session.close(); 24 } 25 } 26 27 @Test 28 public void getGoodsByIdTest() { 29 SqlSession session = MyBatisUtil.getSession(); 30 try { 31 GoodsDAO bookdao = session.getMapper(GoodsDAO.class); 32 Goods goods = bookdao.getGoodsById(1); 33 Assert.assertEquals(1, goods.getId()); 34 } finally { 35 session.close(); 36 } 37 } 38 39 @Test 40 public void getGoodsCountTest() { 41 SqlSession session = MyBatisUtil.getSession(); 42 try { 43 GoodsDAO bookdao = session.getMapper(GoodsDAO.class); 44 Assert.assertEquals(9, bookdao.getGoodsCount()); 45 } finally { 46 session.close(); 47 } 48 } 49 50 @Test 51 public void insertTest() { 52 SqlSession session = MyBatisUtil.getSession(); 53 try { 54 Goods entity = new Goods(); 55 entity.setName("正宗无锡阳山水蜜桃新鲜水果水密桃12个6斤装江浙沪皖顺丰包邮"); 56 entity.setPrice(108); 57 entity.setPicture("nopic.jpg"); 58 GoodsDAO bookdao = session.getMapper(GoodsDAO.class); 59 Assert.assertEquals(1, bookdao.insert(entity)); 60 } finally { 61 session.close(); 62 } 63 } 64 65 @Test 66 public void deleteTest() { 67 SqlSession session = MyBatisUtil.getSession(); 68 try { 69 GoodsDAO bookdao = session.getMapper(GoodsDAO.class); 70 Assert.assertEquals(1, bookdao.delete(12)); 71 } finally { 72 session.close(); 73 } 74 } 75 76 @Test 77 public void update() { 78 SqlSession session = MyBatisUtil.getSession(); 79 try { 80 GoodsDAO bookdao = session.getMapper(GoodsDAO.class); 81 Goods entity = bookdao.getGoodsById(12); 82 entity.setName("正宗无锡阳山水蜜桃新鲜水果水密桃12个6斤装"); 83 entity.setPrice(107); 84 entity.setPicture("nopicture.jpg"); 85 86 Assert.assertEquals(1, bookdao.update(entity)); 87 } finally { 88 session.close(); 89 } 90 } 91 }
5 导入SQL并测试
使用navicat等工具, 创建数据库db1,导入sql数据,
/* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 50536 Source Host : localhost:3306 Source Database : db1 Target Server Type : MYSQL Target Server Version : 50536 File Encoding : 65001 Date: 2016-07-20 10:13:58 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `goods` -- ---------------------------- DROP TABLE IF EXISTS `goods`; CREATE TABLE `goods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `price` decimal(10,2) DEFAULT '0.00', `picture` varchar(100) DEFAULT 'default.jpg', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of goods -- ---------------------------- INSERT INTO `goods` VALUES ('1', 'G7 中原G7三合一浓醇咖啡固体饮料1200', '66.50', '1.jpg'); INSERT INTO `goods` VALUES ('2', '百草味东北松子200gx2袋 坚果炒货零', '42.90', '2.jpg'); INSERT INTO `goods` VALUES ('3', '奈津香 桂圆干500gx2袋莆田特产5A桂', '39.90', '3.jpg'); INSERT INTO `goods` VALUES ('4', '益达尊享护齿装草本40粒+冰柠40粒+西', '25.90', '4.jpg'); INSERT INTO `goods` VALUES ('5', '猴坑茶业2016新茶原产地手工太平猴魁特', '168.00', '5.jpg'); INSERT INTO `goods` VALUES ('6', '嘻鱿记 休闲零食 麻辣香辣奶香炭烧 5种', '39.80', '6.jpg'); INSERT INTO `goods` VALUES ('7', '荣业鸿福五分瘦腊肠 香港土特产香肠腊味', '126.80', '7.jpg'); INSERT INTO `goods` VALUES ('8', '蓓琳娜(BELLINA)3L PDO特级初榨橄榄油', '178.00', '8.jpg'); INSERT INTO `goods` VALUES ('10', '荣业鸿福五分瘦腊肠 香港土特产香肠腊味', '30.60', 'b454b44f-868e-4efe-ae17-91e9e6a58390.jpg');
点击TestGoods类中被@Test注解的各方法前的箭头,执行测试