下载依赖网址
https://search.maven.org/
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>cn.kgc</groupId>
<artifactId>mybatis-demo</artifactId>
<version>1.0-SNAPSHOT</version>
<name>mybatis-demo</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<pluginManagement>
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-jar-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
<plugin>
<artifactId>maven-site-plugin</artifactId>
<version>3.7.1</version>
</plugin>
<plugin>
<artifactId>maven-project-info-reports-plugin</artifactId>
<version>3.0.0</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
main目录下新建resources目录,resources目录下新建mappers目录
mappers目录下
UserMapper.xml(映射文件,写sql语句的地方)
<?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="cn.kgc.dao.UserMapper">
<!--单参-->
<select id="getUserList" resultType="User">
select id,userCode,userName,userPassword from smbms_user
</select>
<select id="findById" resultType="User">
select id,userCode,userName,userPassword from smbms_user where id=#{id}
</select>
<select id="findByName" resultType="User">
select id,userCode,userName,userPassword from smbms_user where userName like concat('%',#{userName},'%')
</select>
<insert id="insert" parameterType="User">
INSERT INTO smbms_user(userCode,userName,userPassword)VALUES(#{userCode},#{userName},#{userPassword})
</insert>
<update id="update" parameterType="User">
update smbms_user set userCode = #{userCode},userName = #{userName},userPassword = #{userPassword} where id = #{id}
</update>
<delete id="delete" parameterType="Long">
delete from smbms_user where id=#{id}
</delete>
<!--对象入参-->
<!--resultType是返回结果的类型,parameterType是参数传入的类型-->
<select id="getUserListByUser" resultType="User" parameterType="user">
select id,userCode,userName,userPassword from smbms_user where userName like concat('%',#{userName},'%')
</select>
<!--Map入参-->
<!--uName是测试的时候的键-->
<select id="getUserListByMap" resultType="User" parameterType="Map">
select id,userCode,userName,userPassword from smbms_user where userName like concat('%',#{uName},'%')
</select>
<!--resultMap 字段不一样时-->
<resultMap id="userList" type="User">
<id property="id" column="id"></id>
<result property="userCode" column="userCode"></result>
<result property="userName" column="userName"></result>
<result property="userPassword" column="userPassword"></result>
</resultMap>
<select id="getUserList2" resultMap="userList">
select * from smbms_user
</select>
<!--association 一对一-->
<resultMap id="userRoleResult" type="User">
<id property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="userPassword" column="userPassword"/>
<result property="userRole" column="userRole"/>
<association property="role" javaType="Role">
<id property="id" column="r_id"/>
<result property="roleCode" column="roleCode"/>
<result property="roleName" column="roleName"/>
</association>
</resultMap>
<select id="getUserByRoleId" resultMap="userRoleResult" parameterType="Integer">
select r.roleName,u.* from smbms_user u,smbms_role r where r.id=u.userRole and u.userRole=#{userRole}
</select>
<!--collection 一对多-->
<resultMap id="userList2" type="User">
<id property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="userPassword" column="userPassword"/>
<result property="userRole" column="userRole"/>
<collection property="list" ofType="Role" resultMap="roleList"></collection>
</resultMap>
<resultMap id="roleList" type="Role">
<id property="id" column="r_id"/>
<result property="roleCode" column="roleCode"/>
<result property="roleName" column="roleName"/>
</resultMap>
<select id="getUserListByRoleId" resultMap="userList2" parameterType="Integer">
select r.roleName,u.* from smbms_user u,smbms_role r where r.id=u.userRole and u.userRole=#{userRole}
</select>
<!--collection 一对多 自动映射后 只需要写主键id-->
<resultMap id="BillList" type="Bill">
<id property="id" column="b_id"/>
<collection property="listProvider" ofType="Provider">
<id property="id" column="p_id"/>
</collection>
</resultMap>
<select id="findBillByProName" resultMap="BillList" parameterType="String">
SELECT b.*,p.* FROM smbms_bill b,smbms_provider p WHERE productName LIKE concat('%',#{ProName},'%') AND b.providerId=p.id
</select>
<!--collection 一对多 if-trim判断参数-->
<resultMap id="BillList2" type="Bill">
<id property="id" column="b_id"/>
<collection property="listProvider" ofType="Provider">
<id property="id" column="p_id"/>
</collection>
</resultMap>
<select id="findBillByProName2" resultMap="BillList2">
SELECT b.*,p.* FROM smbms_bill b,smbms_provider p
<trim prefix="where" prefixOverrides="and" >
<if test="productName!=null and productName!=''">
productName LIKE CONCAT('%',#{productName},'%')
</if>
<if test="providerId!=null">
AND b.providerId=#{providerId} AND b.providerId=p.id
</if>
</trim>
</select>
<!--set-if-->
<update id="update2" parameterType="User">
update smbms_user
<set>
<if test="userCode!=null and userCode!=''">userCode = #{userCode},</if>
<if test="userName!=null and userName!=''">userName = #{userName},</if>
<if test="userPassword!=null and userPassword!=''">userPassword = #{userPassword},</if>
</set>
where id = #{id}
</update>
<!--foreach -list-->
<resultMap id="userMapByRoleId" type="User">
<id property="id" column="id"></id>
</resultMap>
<select id="findListByRoleId_foreach_list" resultMap="userMapByRoleId">
select * from smbms_user where userRole in
<foreach collection="list" item="userRoleList" open="(" separator="," close=")">
#{userRoleList}
</foreach>
</select>
<!--分页-->
<select id="getUserListFen" resultMap="userList">
select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole=r.id
<if test="userRole !=null">
and u.userRole=#{userRole}
</if>
<if test="userName !=null and userName!=''">
and u.userName like CONCAT('%',#{userName},'%')
</if>
order by creationDate DESC limit #{from},#{pageSize}
</select>
</mapper>
main根目录下
db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
username=root
password=123456
log4j.properties
log4j.rootLogger=DEBUG,CONSOLE,file
#log4j.rootLogger=ERROR,ROLLING_FILE
log4j.logger.cn.kgc.dao=debug
log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug
log4j.logger.java.sql.ResultSet=debug
log4j.logger.org.tuckey.web.filters.urlrewrite.UrlRewriteFilter=debug
######################################################################################
# Console Appender \u65e5\u5fd7\u5728\u63a7\u5236\u8f93\u51fa\u914d\u7f6e
######################################################################################
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.Threshold=error
log4j.appender.CONSOLE.Target=System.out
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern= [%p] %d %c - %m%n
######################################################################################
# DailyRolling File \u6bcf\u5929\u4ea7\u751f\u4e00\u4e2a\u65e5\u5fd7\u6587\u4ef6\uff0c\u6587\u4ef6\u540d\u683c\u5f0f:log2009-09-11
######################################################################################
log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.DatePattern=yyyy-MM-dd
log4j.appender.file.File=log.log
log4j.appender.file.Append=true
log4j.appender.file.Threshold=error
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-M-d HH:mm:ss}%x[%5p](%F:%L) %m%n
log4j.logger.com.opensymphony.xwork2=error
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"></properties>
<!--自动映射-->
<settings>
<setting name="autoMappingBehavior" value="FULL"/>
</settings>
<!--取别名-->
<typeAliases>
<package name="cn.kgc.pojo"></package>
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<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>
<environment id="test">
<transactionManager type=""></transactionManager>
<dataSource type=""></dataSource>
</environment>
</environments>
<!--映射文件在哪里-->
<mappers>
<mapper resource="mappers/UserMapper.xml"></mapper><!--绝对路径-->
</mappers>
</configuration>
pojo目录下的类加入(setting–plugins需导入lombok)
@Data
@AllArgsConstructor
@NoArgsConstructor
dao目录下
UserMapper接口
package cn.kgc.dao;
import cn.kgc.pojo.Bill;
import cn.kgc.pojo.Provider;
import cn.kgc.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface UserMapper {
//查询
List<User> getUserList();
//根据id查询User对象
User findById(int id);
//根据姓名 模糊查询
List<User> findByName(String userName);
//增加
int insert(@Param(value = "userCode")String userCode,@Param(value = "userName")String userName,@Param(value = "userPassword")String userPassword);
//修改
int update(@Param(value = "userCode")String userCode,@Param(value = "userName")String userName,@Param(value = "userPassword")String userPassword,@Param(value = "id")long id);
//增加
int delete(long id);
//查询 对象入参
List<User> getUserListByUser(User user);
//查询 Map入参
List<User> getUserListByMap(Map<String,String> userMap);
//查询
List<User> getUserList2();
//association 一对一
User getUserByRoleId(Integer userRole);
//collection 一对多
List<User>getUserListByRoleId(Integer userRole);
//模糊查询 根据id 一对多查询
List<Bill> findBillByProName(String ProName);
//模糊查询 根据id 一对多查询 if-trim判断参数
List<Bill> findBillByProName2(@Param(value = "productName")String productName,@Param(value = "providerId")int providerId);
//修改 set---if 判断参数
int update2(User user);
//foreach 集合入参
List<User>findListByRoleId_foreach_list(List<Integer> userRoleList);
//分页显示
// int count();
List<User>getUserListFen(@Param("from")Integer currentPageNo,
@Param("pageSize")Integer pageSize,
@Param("userName")String userName,
@Param("userRole")Integer userRole
);
}
until目录下
MybatisUtil (java文件)
package cn.kgc.until;
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 java.io.IOException;
import java.io.InputStream;
public class MybatisUtil {
private static SqlSessionFactory sqlSessionFactory;
// 静态代码块,sqlSessionFactory只创建一次
static {
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
// 创建sqlSession false:关闭自动提交。需手动提交
public static SqlSession createSqlSession(){
return sqlSessionFactory.openSession(true);
}
//关闭sqlSession
public static void closeSqlSession(SqlSession sqlSession){
if (null != sqlSession){
sqlSession.close();
}
}
}
测试类
package cn.kgc;
import cn.kgc.dao.UserMapper;
import cn.kgc.pojo.Bill;
import cn.kgc.pojo.Provider;
import cn.kgc.pojo.Role;
import cn.kgc.pojo.User;
import cn.kgc.until.MybatisUtil;
import jdk.nashorn.internal.objects.annotations.Constructor;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.ibatis.annotations.ConstructorArgs;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import static org.junit.Assert.assertTrue;
public class AppTest {
private static Logger logger=Logger.getLogger(AppTest.class);
private SqlSession sqlSession;
private UserMapper mapper;
@Test
public void shouldAnswerWithTrue()
{
assertTrue( true );
}
@Before
public void init(){
sqlSession = MybatisUtil.createSqlSession();
mapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void closeSqlSession(){
MybatisUtil.closeSqlSession(sqlSession);
}
@Test
public void getUserListTest(){
//查询
List<User> userList = mapper.getUserList();
for (User user:userList) {
System.out.println("===="+user.toString());
}
}
@Test
public void findById(){
//根据id查询
User byId = mapper.findById(11);
System.out.println("测试========"+byId);
}
@Test
public void findByName(){
//根据姓名模糊查询
List<User> list = mapper.findByName("少");
for (User user:list) {
System.out.println("===="+user.toString());
}
}
@Test
public void add(){
//增加
int j = mapper.insert("123", "少爷", "123");
System.out.println("j============"+j);
}
@Test
public void update(){
//修改
int i = mapper.update("456", "少爷", "456", 16);
System.out.println("i=============="+i);
}
@Test
public void delete(){
//删除
int i = mapper.delete(16);
System.out.println("i=============="+i);
}
@Test
public void getUserListByUser(){
//查询 对象入参
User user=new User();
user.setUserName("赵");
List<User> list = mapper.getUserListByUser(user);
for (User user2:list) {
System.out.println("===="+user2.toString());
}
}
@Test
public void getUserListByMap(){
//查询 Map入参
Map<String,String> userMap=new HashMap<String,String>();
userMap.put("uName","赵");
List<User> list = mapper.getUserListByMap(userMap);
for (User user:list) {
System.out.println("===="+user.toString());
}
}
@Test
public void getUserList2Test(){
//查询
List<User> userList = mapper.getUserList2();
for (User user:userList) {
System.out.println("===="+user.toString());
}
}
@Test
public void getUserListByRoled(){
//查询 association 一对一
User userListByRoled = mapper.getUserByRoleId(1);
System.out.println("===="+userListByRoled);
}
@Test
public void getUserListByRoleId(){
//查询 collection 一对多
List<User> userListByRoleId = mapper.getUserListByRoleId(2);
for(User user:userListByRoleId){
for(Role role:user.getList()){
System.out.println("===="+user.getUserName()+" "+role.getRoleName());
}
}
}
@Test
public void findBillByProName2(){
//模糊查询 根据id 查询
List<Bill> billList = mapper.findBillByProName2("大",3);
System.out.println("订单编码"+" "+"商品名称"+" "+"供应商编码"+" "+"供应商名称"+" "+"供应商联系人"+" "+"联系电话"+" "+"订单金额"+" "+"是否付款");
for(Bill bill:billList){
for(Provider provider:bill.getListProvider()){
System.out.println(bill.getBillCode()+bill.getProductName()+provider.getProCode()+provider.getProName()+provider.getProContact()+provider.getProPhone()+bill.getTotalPrice()+bill.getIsPayment());
}
}
}
@Test
public void update2(){
//修改
User user=new User();
user.setId(14);
user.setUserName("公子");
user.setUserPassword("789");
int i = mapper.update2(user);
System.out.println("i=============="+i);
}
@Test
public void findListByRoleId(){
//foreach 集合入参
List<User>list=new ArrayList<>();
List<Integer>roleList=new ArrayList<>();
roleList.add(2);
roleList.add(3);
List<User> listByRoleId = mapper.findListByRoleId_foreach_list(roleList);
for(User user:listByRoleId){
System.out.println("集合======="+user.getUserName()+" "+user.getUserRole());
}
}
@Test
public void getUserListFen(){
//分页显示
String userName="";
Integer roleId=null;
Integer pageSize=3;
Integer currentPageNo=0;
List<User> userListFen = mapper.getUserListFen(currentPageNo, pageSize, userName, roleId);
for(User user:userListFen){
System.out.println("分页显示====="+user.getUserName()+" "+user.getUserRole());
}
}
}