MyBatis框架中通过在XML映射配置文件定义insert、delete、update和select标签进而定义相应SQL语句的方式实现增删改查操作。
查询操作使用select标签:
返回List集合,代码如下:
import java.util.List; public class UserInfo { private Integer id; private String userName; private String password; private String realName; private Integer age; private List<Address> list; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getRealName() { return realName; } public void setRealName(String realName) { this.realName = realName; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public List<Address> getList() { return list; } public void setList(List<Address> list) { this.list = list; } }
import java.util.List; import org.apache.ibatis.annotations.Param; import com.hw.vo.UserInfo; public interface IUserInfoDao { List<UserInfo> selectByUserName(@Param("userName")String userName ); }
mybatis帮助我们实现DAO层(使得SQL语句和Java代码分离,按照MVC的开发模式,使得整个项目更具有层次感,然后通过servlet接收数据并进行跳转),使得更容易操作数据,让他更持久保存在数据库中。
import java.util.List; import com.hw.vo.UserInfo; public interface IUserInfoService { public List<UserInfo> selectByUserName(String userName); }
service给DAO层提供数据,然后DAO层负责执行xml里的SQL语句,最终完成持久化。
import java.io.IOException; import java.io.InputStream; import java.util.List; 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 com.hw.userinfo.IUserInfoDao; import com.hw.userinfo.IUserInfoService; import com.hw.vo.UserInfo; public class UserInfoService implements IUserInfoService { /** * 根据名字查询 */ @Override public List<UserInfo> selectByUserName(String userName) { if (userName != null && userName.trim().length() > 0) { userName = "%" + userName + "%"; } SqlSession sqlSession = null; try { InputStream inputStream = Resources.getResourceAsStream("config-mybatis.xml"); //以IO流方式加载config-mybatis.xml文档 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);获取SqlSessionFactory对象 sqlSession = sqlSessionFactory.openSession();得到sqlsession 相当于一次会话 IUserInfoDao userInfoDao = sqlSession.getMapper(IUserInfoDao.class);//调用DAO层-动态获取IUserInfoDao的代理对象 return userInfoDao.selectByUserName(userName);//返回结果 } catch (IOException e) { e.printStackTrace(); } finally { if (sqlSession != null) { sqlSession.close(); } } return null; } }
<?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> <!-- 连接数据库的配置信息 --> <environments default="dev"> <environment id="dev"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <!-- 指定含有SQL语句的xml文件的位置 --> <mappers> <mapper resource="./userinfo.xml"/> </mappers> </configuration>
<?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:必须为接口的全类名 --> <mapper namespace="com.hw.userinfo.IUserInfoDao"> <resultMap type="com.hw.vo.UserInfo" id="userinfo"> <result column="id" property="id"/> <result column="user_name" property="userName"/> <result column="real_name" property="realName" /> <collection property="list" ofType="com.hw.vo.Address"> <result column="addr_id" property="id"/> <result column="addr_user_id" property="userId"/> <result column="addr_real_name" property="realName"/> <result column="addr_mobile" property="mobile"/> <result column="addr_address" property="address"/> </collection> </resultMap> <!-- id:接口中定义的抽象方法名;resultMap:与上面的id相匹配 --> <select id="selectByUserName" resultMap="userinfo"> select ui.id,ui.user_name,ui.real_name, addr.id addr_id,addr.user_id addr_user_id,addr.real_name addr_real_name,addr.mobile addr_mobile,addr.address addr_address from user_info ui left join address addr on addr.user_id=ui.id <where> <if test="userName !=null"> user_name like #{userName} </if> </where> </select> </mapper>
创建userInfoService对象---上转型对象,然后调用实现类中的查询方法:
import com.hw.userinfo.IUserInfoService; import com.hw.userinfo.imp.UserInfoService; import com.hw.vo.Address; import com.hw.vo.UserInfo; public class select { public static void main(String[] args) { IUserInfoService userInfoService = new UserInfoService(); for (UserInfo userInfo : userInfoService.selectByUserName("梦")) { System.out.println(userInfo.getRealName()+"--用户ID:"+userInfo.getId()); for (Address address : userInfo.getList()) { System.out.println(address.getId()+"----用户ID:"+address.getUserId()+"----真实的名字:"+address.getRealName()+"----地址:"+address.getAddress()); } } } }
运行结果:
用户表
地址表
①、resultType为自定义类型,此时需要开启MyBatis自动映射功能,必要时需要开启驼峰命名规则;
②、使用resultMap标签进行自定义字段与成员变量之间的映射关系,并在select标签中设定resultMap标签属性。注意:resultType和resultMap不能同时使用。
映射关系
使用mybatis进行数据查询时,数据库中的字段名(全部小写字母,单词之间_)和java代码(驼峰式-从第二个字母开始大写)中的不一致,resultType为自定义类型,此时需要开启MyBatis自动映射功能,必要时需要开启驼峰命名规则;
解决办法:
1.将查询的SQL语句使用 as 将字段名转换成驼峰式
2.设置全局配置文件,让mybatis自动把含有下划线的字段名统一变成驼峰式:
全局配置文件的父标签为configuration,该标签的直接子标签有settings、typeAliases、environment、properties、mapper
settings子标签:该标签用于改变 MyBatis 的运行时行为常用的设置项有以下几个:
3.使用resultMap中的result--column字段和property属性做关联
使用resultMap标签进行自定义字段与成员变量之间的映射关系,并在select标签中设定resultMap标签属性。注意:resultType和resultMap不能同时使用。
多表自定义映射
一对一:一个用户只有一个地址
import com.jd.userinfo.IUserInfoService; import com.jd.userinfo.imp.UserInfoService; import com.jd.vo.UserInfo; public class Test { public static void main(String[] args) { IUserInfoService userInfoService = new UserInfoService(); for (UserInfo userInfo :userInfoService.selectByUserName("风铃") ) { System.out.println(userInfo.getRealName()+userInfo.getAddress()); } } }
<?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:必须为接口的全类名 --> <mapper namespace="com.jd.userinfo.IUserInfoDao"> <!-- id:接口中定义的抽象方法名;resultType:返回值类型 --> <resultMap type="com.jd.vo.UserInfo" id="userinfo"> <result column="id" property="id"/> <result column="user_name" property="userName"/> <result column="real_name" property="realName" /> <result column="add_id" property="address.id"/> <result column="add_real_name" property="address.realName"/> <result column="add_mobile" property="address.mobile"/> <result column="add_address" property="address.address"/> </resultMap> <select id="selectByUserName" resultMap="userinfo"> select ui.id,ui.user_name,ui.real_name, addr.id addr_id,addr.real_name addr_real_name,addr.mobile addr_mobile,addr.address addr_address from user_info ui left join address addr on addr.user_id=ui.id <where> <if test="userName !=null"> user_name like #{userName} </if> </where> </select> </mapper>
一对多:一个用户有多个地址
import com.hw.userinfo.IUserInfoService; import com.hw.userinfo.imp.UserInfoService; import com.hw.vo.Address; import com.hw.vo.UserInfo; public class select { public static void main(String[] args) { IUserInfoService userInfoService = new UserInfoService(); for (UserInfo userInfo : userInfoService.selectByUserName("梦")) { System.out.println(userInfo.getRealName()+"--用户ID:"+userInfo.getId()); for (Address address : userInfo.getList()) { System.out.println(address.getId()+"----用户ID:"+address.getUserId()+"----真实的名字:"+address.getRealName()+"----地址:"+address.getAddress()); } } } }
<?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:必须为接口的全类名 --> <mapper namespace="com.hw.userinfo.IUserInfoDao"> <resultMap type="com.hw.vo.UserInfo" id="userinfo"> <result column="id" property="id"/> <result column="user_name" property="userName"/> <result column="real_name" property="realName" /> <collection property="list" ofType="com.hw.vo.Address"> <result column="addr_id" property="id"/> <result column="addr_user_id" property="userId"/> <result column="addr_real_name" property="realName"/> <result column="addr_mobile" property="mobile"/> <result column="addr_address" property="address"/> </collection> </resultMap> <!-- id:接口中定义的抽象方法名;resultMap:与上面的id相匹配 --> <select id="selectByUserName" resultMap="userinfo"> select ui.id,ui.user_name,ui.real_name, addr.id addr_id,addr.user_id addr_user_id,addr.real_name addr_real_name,addr.mobile addr_mobile,addr.address addr_address from user_info ui left join address addr on addr.user_id=ui.id <where> <if test="userName !=null"> user_name like #{userName} </if> </where> </select> </mapper>