1:我这里直接使用的maven项目结构可能不一样;
这里首先是核心的配置文件;
<?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>
<!-- 可以配置实体类的别名,扫描这个包下的所有文件,在sql中的xml文件只需要写对应的类名即可-->
<typeAliases>
<package name="domain"/>
</typeAliases>
<!-- environments是配置数据库连接环境信息,可以配置多个environment,通过default可以切换不同的environments-->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理-->
<transactionManager type="JDBC" />
<!-- 数据库连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/user?characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载sql映射文件-->
<!-- <mapper resource="mapperImpl/UserMapper.xml"/>-->
<!-- mapper代理方式,要保证名称相同,同一目录下,直接映射文件夹-->
<package name="mapperImpl"/>
</mappers>
</configuration>
2:接下来就是一些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">
<!--
namespace:名称空间
resultType:对应返回结果的类型
parameterType:
-->
<!--如果使用mapper代理,namespace必须写对应的接口全部地名;-->
<!--如果在mapper接口中定义方法,那么这里的sql语句中的id与参数类型,与返回值必须一致-->
<mapper namespace="mapperImpl.UserMapper">
<!-- 普通查询全部用户-->
<select id="findAllUser" resultType="user">
select * from u_user
</select>
<!-- 使用动态sql查询条件,查询所有用户-->
<select id="findAllUseDynamic" resultType="user">
select * from u_user
<where>
<if test="uid != null and uid != '' ">
and uid like ${uid}
</if>
<if test="uname != null and uname != '' ">
and uname like ${uname}
</if>
</where>
</select>
<!-- 多条件in查询-->
<select id="findAllUseList" resultType="user">
select * from u_user
<where>
<if test="list != null and list != ''">
<foreach collection="list" item="id" index="index" open=" uid in (" separator="," close=")">
#{id}
</foreach>
</if>
</where>
</select>
<!-- 一对一根据指定用户id查询订单-->
<!-- 使用映射的方式加载类-->
<resultMap id="fruitsUser" type="domain.Fruits">
<result column="fname" property="fname"></result>
<result column="fmoney" property="fmoney"></result>
<!-- 映射类里面的类-->
<association property="user" javaType="domain.User">
<id column="uid" property="uid"></id>
<result column="uname" property="uname"></result>
<result column="state" property="state"></result>
</association>
</resultMap>
<select id="findUserFruits" resultMap="fruitsUser">
select u.uid,u.uname,u.state,f.fname,f.fmoney from u_user u,fruits f where u.uid=#{id} and u.uid=f.uid
</select>
<!-- 多对一查询,根据订单查询用户-->
<resultMap id="fruitsUserFruits" type="domain.User">
<id column="uid" property="uid"></id>
<result column="uname" property="uname"></result>
<result column="state" property="state"></result>
<collection property="fruits" ofType="domain.Fruits">
<result property="fname" column="fname"></result>
<result column="fmoney" property="fmoney"></result>
</collection>
</resultMap>
<select id="findFruitsAndUser" resultMap="fruitsUserFruits">
select u.uid,u.uname,u.state,f.fname,f.fmoney from u_user u left join fruits f on f.uid=u.uid
</select>
<!-- 添加功能,添加一个用户-->
<!-- 主键返回:
1:设置属性useGeneratedKeys="true"为true
2:keyProperty="uid":里面的值对应你主键的名称
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="uid">
insert into u_user (uid,uname,upassword,state,fid) value (#{uid},#{uname},#{upassword},#{state},#{fid})
</insert>
<!-- 动态sql修改指定用户的信息-->
<update id="updateUser">
update u_user
<set>
<if test="uname != null and uname != '' ">
uname = #{uname},
</if>
<if test="upassword != null and upassword != '' ">
upassword = #{upassword},
</if>
<if test="state != null and state != '' ">
state = #{state},
</if>
<if test="fid != null and fid != '' ">
fid = #{fid}
</if>
</set>
<where>
<if test="uid != null and uid != '' ">
uid = #{uid}
</if>
</where>
</update>
<!-- 删除功能-->
<delete id="deleteUser">
delete from u_user where uid = #{uid}
</delete>
<!-- 批量删除选中用户-->
<select id="deletePitchUser">
delete from u_user
<where>
<if test="list != null and list != ''">
<foreach collection="list" item="id" index="index" open=" uid in (" separator="," close=")">
#{id}
</foreach>
</if>
</where>
</select>
<!-- 多对多查询,根据订单查询用户-->
<resultMap id="orderAndUser" type="domain.Order">
<id column="orderid" property="orderid"></id>
<result column="orderamount" property="orderamount"></result>
<result column="ordermoney" property="ordermoney"></result>
<association property="orderuser" javaType="domain.User">
<result column="uname" property="uname"></result>
<collection property="fruits" ofType="domain.Fruits">
<result column="fname" property="fname"></result>
<result column="fmoney" property="fmoney"></result>
</collection>
</association>
</resultMap>
<select id="selectOrderAndUser" resultMap="orderAndUser">
select o.orderid,o.orderamount,u.uname,f.fname,f.fmoney,o.ordermoney
from orderfruits o,u_user u,fruits f where o.fid=f.fid and o.uid=u.uid
</select>
</mapper>
3:因为里面有反射我这里使用了接口的,与上面sql语句的注释都是一一对应的;
package mapperImpl;
import domain.Fruits;
import domain.Order;
import domain.User;
import org.apache.ibatis.annotations.Select;
import java.util.ArrayList;
import java.util.List;
/**
* 使用mapper代理
* 1:定义的mapper接口名称必须和与sql映射文件同名
* 2:并且mapper接口文件路径必须和sql映射文件放在同移目录下
*/
public interface UserMapper {
/**
* 普通查询所有用户,使用注解的方式开发
* @return
*/
@Select("select * from u_user")
List<User> findAllUser();
/**
* 使用条件动态sql语句查询用户
* @param user
* @return
*/
List<User> findAllUseDynamic(User user);
/**
* 使用in多条件查询
* @param list
* @return
*/
List<User> findAllUseList(List<Integer> list);
/**
* 一对一根据指定用户id查询订单
* @return
*/
List<Fruits> findUserFruits(String str);
/**
* 多对一查询,根据订单查询用户
* @return
*/
List<User> findFruitsAndUser();
/**
* 添加用户
* @param user
*/
void insertUser(User user);
/**
* 动态sql修改指定用户的信息
* @param user
* @return
*/
int updateUser(User user);
/**
* 删除指定用户功能
* @param str
*/
void deleteUser(String str);
/**
* 批量删除选中的用户
* @param list
*/
void deletePitchUser(ArrayList<String> list);
/**
* 多对多查询,根据订单查询用户
* @return
*/
List<Order> selectOrderAndUser();
}
4:下面就是一些测试类了,也是与sql一一对应的;
package userTset;
import domain.Fruits;
import domain.Order;
import domain.User;
import mapperImpl.UserMapper;
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.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class TestUser {
SqlSessionFactory build = null;
@Before
// 加载mybatis的核心文件,获取SqlSessionFactory
public void creatFactory(){
InputStream input = null;
try {
// 通过mybatis提供的Resources方法,进行加载资源
input = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
e.printStackTrace();
}
build = new SqlSessionFactoryBuilder().build(input);
}
/**
* 普通查询所有用户
*/
@Test
public void findAllUser(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
// 执行sql语句,查询一个selectOne
//User user = sqlSession.selectOne("test.findAllUser", "1");
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> user = mapper.findAllUser();
System.out.println(user);
// 释放资源
sqlSession.close();
}
/**
* 使用动态sql查询用户
*/
@Test
public void findAllUseDynamic(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
// 执行sql语句,查询一个selectOne
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// 定义变量
String u_id = "1";
String u_name = "儿";
// 处理变量,进行模糊查询
String uid = "%"+u_id+"%";
String uname = "%"+u_name+"%";
User user = new User();
//user.setUid("2");
//user.setUname(uname);
List<User> allUseDynamic = mapper.findAllUseDynamic(user);
System.out.println(allUseDynamic);
// 释放资源
sqlSession.close();
}
/**
* 使用in多条件查询
*/
@Test
public void findAllUseList(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
// 执行sql语句,查询一个selectOne
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//user.setUname(uname);
ArrayList<Integer> list1 = new ArrayList<>();
// list1.add(1);
// list1.add(3);
List<User> list = mapper.findAllUseList(list1);
System.out.println(list);
// 释放资源
sqlSession.close();
}
/**
* 一对一根据指定用户id查询订单
*/
@Test
public void findUserFruits(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Fruits> fruits = mapper.findUserFruits("1669709518274");
System.out.println(fruits);
// 释放资源
sqlSession.close();
}
/**
* 多对一查询,根据订单查询用户
*/
@Test
public void findFruitsAndUser(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> fruits = mapper.findFruitsAndUser();
System.out.println(fruits);
sqlSession.close();
}
/**
* 添加用户
*/
@Test
public void insertUser(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
Date date = new Date();
user.setUid(date.getTime()+"");
user.setUname("谯幺儿");
user.setUpassword("123");
user.setState("0");
user.setFid("1");
mapper.insertUser(user);
System.out.println(user.getUid());
// 添加修改删除需要提交事务
// 提交事务
sqlSession.commit();
sqlSession.close();
}
/**
* 动态sql修改指定用户信息
*/
@Test
public void updateUser(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession(true);//设置为true提交事务
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUid("16696861166658");
user.setState("0");
user.setUpassword("123");
int i = mapper.updateUser(user);
System.out.println(i);
sqlSession.close();
}
/**
* 删除指定用户的功能
*/
@Test
public void deleteUser(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession(true);//设置为true提交事务
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUser("1669687739185");
sqlSession.close();
}
/**
* 批量删除选中用户的功能
*/
@Test
public void deletePitchUser(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
ArrayList<String> list = new ArrayList<>();
list.add("1669709372727");
if (list.size() != 0) {
mapper.deletePitchUser(list);
sqlSession.commit();
sqlSession.close();
}else {
System.out.println("数据为空不执行删除语句");
}
}
/**
* 多对多查询,根据订单查询用户
*/
@Test
public void selectOrderAndUser(){
// 获取sqlSession对象,用他执行sql语句
SqlSession sqlSession = build.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Order> list = mapper.selectOrderAndUser();
System.out.println(list);
sqlSession.close();
}
}
最后就是数据库,可以参考参考