Mybatis:
框架搭建:
mybatis所需jar
<mybatis.version>3.2.8</mybatis.version>
<slf4j.version>1.7.12</slf4j.version>
<log4j.version>1.2.17</log4j.version>
<druid.version>1.0.9</druid.version>
<!-- mybatis框架包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- log -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
创建主配置文件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="database.properties"/>
<settings>
<!-- value="STDOUT_LOGGING" 打印在控制台 配置打印SQL语句 -->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!-- 在mybatis主配置文件中关闭自动映射 -->
<!--<setting name="autoMappingBehavior" value="NONE"/>-->
<!-- 在mybatis主配置文件中开启全自动映射 -->
<!--<setting name="autoMappingBehavior" value="FULL"/>-->
</settings>
<!--配置包名-->
<typeAliases>
<package name="com.nfl.pojo"/>
</typeAliases>
<!--environments可以配置多个环境信息-->
<environments default="development">
<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/ProviderMapper.xml"/>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
mybatis工具类连接数据库:
编写测试类
String resource = "mybatis-config.xml";
try {
InputStream is = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
/**
* true 自动提交事务 (默认)
* false 关闭自动提交
*/
SqlSession sqlSession = factory.openSession(true);
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.getUserId(1);
//关闭sqlsession
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
SQL映射文件
<mapper namespace="com.nfl.dao.UserDao">
<select id="getUserId" parameterType="Long" resultType="User" >
SELECT * FROM sf_user where user_id=#{user_id}
</select>
</mapper>
标签解释:
mapper:根元素 ,只有一个属性namespace,用来区分不同的mapper文件,必须与接口绑定。
select:映射查询语句
delete:映射删除语句
update: 映射修改语句
insert: 映射插入语句
sql: 可以重用sql块,也可以被其他语句引用
resultMap : 用来描述数据库结果集和对象的对应关系
cache :配置给定命名空间的缓存
SQL映射文件参数传递
单参数:
接口中定义
public List<User> getUserListByName(String user_name);
mapper中使用
<select id="getUserListByName" parameterType="String" resultType="User" >
SELECT * FROM sf_user where user_name like concat('%',#{user_name},'%')
</select>
id :与接口中方法名相同
parameterType:指定要接收的参数类型
resultType:返回值类型,对象或集合都可以写对象
多个参数:
使用注解@Param
public List<User> getUserList(@Param("user_name") String user_name, @Param("user_state") int user_state);
使用对象
public int updateUser(User user);
<update id="updateUser" parameterType="User">
update sf_user set user_name=#{user_name} where user_id=#{user_id}
</update>
注意#{user_name}
为User对象下属性名,不能随便写
update,insert,delete 都没有resultType 默认返回受影响行数int类型
使用map集合
接口中定义
public List<User> getUserListMap(Map<String,Object> map);
mapper中使用
<select id="getUserListMap" parameterType="Map" resultType="User">
<!--传入一个map查询 concat拼接字符串 -->
SELECT * FROM sf_user where user_name like concat('%',#{user_name},'%') and user_state=#{user_state}
</select>
注意:#{user_name}
为map的KEY
SQL映射文件指定返回列
select 标签中的属性
resultType:标识返回值类型,基础类型与复杂数据类型
resultMap:用来引入外部的一个resultMap,与外部resultMap标签中id对应
示例:
SQL映射文件获取一对一关系值(对象套对象association)
使用association实现一对一
property:属性名
javaType:所属数据类型
实体类定义:
//角色实体类
public class Role {
private long role_id;
private String role_name;
private String role_remarks;
//管理员实体类
public class Admin {
private long admin_id;
private String admin_name;
private Role admin_role;//角色对象
mapper中代码
<!--
对应关系:
property 实体类
column 数据库
-->
<resultMap id="mapAdmin" type="Admin">
<id property="admin_id" column="admin_id"></id>
<result property="admin_name" column="admin_name"></result>
<association property="admin_role" javaType="Role">
<id property="role_id" column="role_id"></id>
<result property="role_name" column="role_name"></result>
<result property="role_remarks" column="role_remarks"></result>
</association>
</resultMap>
<!--
resultMap指定映射的id
-->
<select id="AdminMapper" parameterType="Long" resultMap="mapAdmin">
SELECT * FROM sf_admin a left join sf_role b on a.admin_role_id=b.role_id where admin_id=#{admin_id}
</select>
或
<resultMap id="mapAdmin" type="Admin">
<id property="admin_id" column="admin_id"></id>
<result property="admin_name" column="admin_name"></result>
<association property="admin_role" resultMap="rolemap" javaType="Role"/>
</resultMap>
<resultMap id="rolemap" type="Role">
<id property="role_id" column="role_id"></id>
<result property="role_name" column="role_name"></result>
<result property="role_remarks" column="role_remarks"></result>
</resultMap>
SQL映射文件获取一对多关系值(对象套集合)
实体类定义:
//收货地址实体类
public class Address {
private long address_id;
private String address_name;
//用户实体类
public class User {
private long user_id;
private String user_name;
private List<Address> addr_list;
mapper中代码
<!--
对应关系:
property 实体类
ofType 集合中的类型
-->
<resultMap id="user_addr_map" type="User">
<id property="user_id" column="user_id"></id>
<result property="user_name" column="user_name"></result>
<collection property="addr_list" ofType="Address">
<id property="address_id" column="address_id"></id>
<result property="address_name" column="address_name"></result>
</collection>
</resultMap>
<select id="getUserId" parameterType="Long" resultMap="user_addr_map" >
SELECT * FROM sf_user a right join sf_address b on a.user_id=b.address_user_id where user_id=#{user_id}
</select>