mybatis 整合
一:导入四个jar包
1.mybatis(必须);2.mysql(MySQL 驱动包必须) 3.junit (用于@test);4.log4j(用于错误日志)
<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.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
</dependencies>
二:在resources下新建SqlMapConfig.xml
1:详细见官网:http://www.mybatis.org/mybatis-3/zh/getting-started.html
<?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>
<!--default 和 id 可以自己随便定义,但是名称一样-->
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!--我的MySQL版本为8.0.13-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/jdbc?serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 两种方式映射到UserDao.xml文件
第一种:比较繁琐,必须每个xml文件都到写上去-->
<!--<mapper resource="com/itcast/dao/UserDao.xml"></mapper>-->
<!-- 第二种:直接写入mapper.xml所在的包下,但是必须注意文件所在的包和名称必须要与dao接口完全一致-->
<package name="com.itcast.dao"></package>
</mappers>
</configuration>
三:UserDao.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">
<!--namespace 是dao接口所在的位置-->
<mapper namespace="com.itcast.dao.UserDao">
<!--id="findAll"必须与UserDao 中的方法名称一致-->
<!--resultType 为返回结果的pojo-->
<select id="findAll" resultType="com.itcast.pojo.User">
select * from user ;
</select>
<!--模糊查询-->
<select id="getLikeUser" parameterType="String" resultType="com.itcase.pojo.User">
select * from user where username like concat('%',#{username},'%');
<!-- select *from user where username like #{username};-->
<!-- select *from user where username like '%${value}%';-->
</select>
<!--添加用户-->
<insert id="saveUser" parameterType="com.itcase.pojo.User" >
<!-- insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address})-->
insert into user (username,birthday,sex,address)values (#{username},#{birthday},#{sex},#{address});
</insert>
<!--修改用户-->
<update id="updateUser" parameterType="java.util.Map">
update user set username=#{username} where address=#{address};
</update>
<!--删除用户-->
<delete id="delUser" parameterType="java.lang.String">
delete from user where username=#{username};
</delete>
<!--聚合函数查询用户总数-->
<select id="findCount" resultType="int">
select count(*) from user;
</select>
<!--一对一时的多表查询写法-->
<!--User实体类中要添加Bill类的List<Bill> bill-->
<resultMap id="userAndBill" type="com.itcast.pojo.User">
<id property="id" column="id"></id>
<result property="userName" column="username"></result>
<result property="passWord" column="password"></result>
<result property="birthday" column="birthday"></result>
<!--这里的id为User 的Id,把值带入到下面-->
<association property="bill" column="id" javaType=com.itcast.pojo.Bill>
<id property="bid" column="bid"></id>
<result property="billName" column="bill_name"></result>
<result property="money" column="money"></result>
</association>
</resultMap>
<!--userAndBill 的名称就是resultMap id名称一致-->
<select id="userAndBill" resultMap="userAndBill">
select *from user ,bill where user.id=bill.bid;
</select>
<!--sql中 SELECT *FROM USER WHERE id IN (1,2,3) 是mybatis的写法-->
<!-- QueryVo 见图 item 值与#{id}名称一致-->
<select id="findIds" resultType="com.itcast.pojo.User" parameterType="com.itcast.pojo.QueryVo">
select *from user
<where>
<if test="ids!=null and ids.size()>0">
<foreach collection="ids" open="id in(" close=")" item="id" separator=",">#{id}</foreach>
</if>
</where>
</select>
</mapper>
<!--一对多查询-->
JavaType和ofType都是用来指定对象类型的,但是JavaType是用来指定pojo中属性的类型,而ofType指定的是映射到list集合属性中pojo的类型。
pojo类:
publicclass User {
privateint id;
privateString username;
privateString mobile;
privateList<Post>posts;
}
user.xml:
<resultMap type="User" id="resultUserMap">
<result property="id" javaType="int" column="user_id" />
<result property="username" javaType="string" column="username" />
<result property="mobile" column="mobile" />
<!--javatype指定的是user对象的属性的类型(例如id,posts),而oftype指定的是映射到list集合属性中pojo的类型(本例指的是post类型)-->
<collection property="posts" ofType="com.spenglu.Post" javaType="java.util.ArrayList" column="userid">
<id property="id" column="post_id" javaType="int" jdbcType="INTEGER"/>
<result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
<result property="content" column="content" javaType="string" jdbcType="VARCHAR"/>
</collection>
</resultMap>
四 :写dao和pojo(具体代码略)
五:测试
public class UserTest {
public static void main(String[] args) throws Exception{
//SqlMapConfig.xml 为mybatis配置文件,加载mybatis
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
SqlSession sqlSession = factory.openSession();
//UserDao 为要调用的dao接口名称
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> users = mapper.findAll();
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
in.close();
}
}
六:如果用注解的方式
1.删除UserDao.xml文件
2:在UserDao.java接口文件上加入sql
public interface UserDao {
@Select("select *from user")
List<User> findAll();
}
3:修改SqlMapConfig.xml文件
<mappers>
<!-- 两种方式映射到UserDao.xml文件
第一种:比较繁琐,必须每个xml文件都到写上去-->
<!--<mapper resource="com/itcast/dao/UserDao.xml"></mapper>-->
<!-- 第二种:直接写入mapper.xml所在的包下,但是必须注意文件所在的包和名称必须要与dao接口完全一致-->
<!--<package name="com.itcast.dao"></package>-->
<!--注解方式的配置-->
<mapper class="com.itcast.dao.UserDao"/>
</mappers>