何为mybatis
mybatis是一款orm(对象关系映射)框架,因为传统的jdbc编程每次操作都需要打开关闭connection连接(工作量大),需要在代码中对sql编码进行维护(硬编码)等问题,各种优秀的orm框架应运而生,其中比较出名的就有hibernate和mybatis。
hibernate与mybatis的区别
1、hibernate是全自动映射orm框架,不需要写sql语句,其只需提供pojo和映射关系即可。而mybaits是半自动映射orm框架,需要在xml中写sql语句,需要提供pojo,sql和映射关系。
2、mybatis优势:手动写sql,可以进行更为细致的sql优化(可以配置动态sql);mybatis更易上手;
3、hibernate优势:数据库无关性比较好,开发代码比较少,开发速度比较快
mybatis开发
mybatisy原生开发步骤
1、sqlMapConfig.xml作为mybatis全局配置文件,配置了数据库的信息,以及mapper.xml映射文件。
2、SqlSessionFactoryBuilder通过读取sqlMapConfig.xml,生成SqlSessionFactory实例,这个实例是单例的;
3、通过SqlSessionFactory的openSession()方法可以获得sqlSession(这里的sqlSession就是类似于jdbc的Connection)。
4、完成映射文件的准备。
5、通过sqlSession实现对数据库的操作,返回结果集。
pojo类
public class Bank {
@Override
public String toString() {
return "Bank [id=" + id + ", username=" + username + ", money=" + money + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
private int id;
private String username;
private double money;
}
bankMapper.xml文件
<?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="test">
<select id="SelectById" parameterType="Integer" resultType="com.test.mybatis.pojo.Bank">
select * from bank where id=#{value}
</select>
</mapper>
sqlMapConfig.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>
<!-- 和spring整合后 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/test?characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mybatis/BankMapper.xml"/>
</mappers>
</configuration>
dao层实现类
public class BankDaoImpl implements BankDao {
/**
* @param sqlsessionFactory
*/
public BankDaoImpl(SqlSessionFactory sqlsessionFactory) {
super();
this.sqlsessionFactory = sqlsessionFactory;
}
public SqlSessionFactory getSqlsessionFactory() {
return sqlsessionFactory;
}
public void setSqlsessionFactory(SqlSessionFactory sqlsessionFactory) {
this.sqlsessionFactory = sqlsessionFactory;
}
/* (non-Javadoc)
* @see
*/
private SqlSessionFactory sqlsessionFactory;
@Override
public Bank selectById(int id) {
// TODO Auto-generated method stub
SqlSession openSession = sqlsessionFactory.openSession();
Bank bank = openSession.selectOne("SelectById",1);
return bank;
}
}
最后测试
public class MyBatisTest {
@Test
public void test() throws IOException{
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("mybatis/sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = builder.build(inputStream);
BankDao bankDao=new BankDaoImpl(sqlSessionFactory);
Bank bank = bankDao.selectById(1);
System.out.println(bank.toString());
}
}
mybatis的${}和#{}
1、#{}是占位符,jdbc中的preparedstatement。可以防止注入。但是不能用于order by。在注入字符串时候,会自动在字符串两头加上”.
比如在模糊查询中,其写法就是
<!-- 根据用户名模糊查询 ,#{}-->
<select id="SelectByLike" parameterType="string" resultType="com.test.mybatis.pojo.Bank">
select * from bank where username like #{value}
</select>
然后java代码是
BankMapper mapper = openSession.getMapper(BankMapper.class);
List<Bank> list = mapper.SelectByLike("%琳");
2、${}是拼接字符串,其模糊查询的用法
<!-- 根据用户名模糊查询 ,$()-->
<select id="SelectByLike2" parameterType="string" resultType="com.test.mybatis.pojo.Bank">
select * from bank where username like '%${value}'
</select>
java代码是
BankMapper mapper = openSession.getMapper(BankMapper.class);
List<Bank> list = mapper.SelectByLike2("琳");
${}有时候会产生sql注入问题
如下:
<!--拼接符 ,注意这里 ${value}没有引号-->
<select id="SelectByName2" parameterType="string" resultType="com.test.mybatis.pojo.Bank">
select * from bank where username=${value}
</select>
然后java代码中
List<Bank> list = mapper.SelectByName2("'李琳' or 1=1");
会将说有结果都查询出来
Mybaits一对多查询
比如有用户表和订单表,一个用户用多个订单,一个订单只属于一个用户,如果想查出一个用户的所有订单,就涉及到一对多查询。一种方法是先根据用户名从用户表获取用户的id,然后根据id去订单表查询所有的订单信息。这种方法需要两次查询数据库。mybatis还提供了另一种方法。即在用户的pojo里添加进订单的list。然后在mapper文件里进行配置,一次查询用户下所有的订单。
订单信息:
用户信息:
订单里的user_id对应于用户信息里的id;
订单pojo:
public class Order implements Serializable{
@Override
public String toString() {
return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createTime=" + createTime
+ ", note=" + note + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
private int id;
private int userId;
private String number;
private Date createTime;
private String note;
}
用户pojo:
public class Bank implements Serializable{
@Override
public String toString() {
return "Bank [id=" + id + ", username=" + username + ", money=" + money + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
private int id;
private String username;
private double money;
private List<Order>orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
}
mapper文件:
<resultMap type="com.test.mybatis.pojo.Bank" id="BankMap">
<id property="id" column="id" />
<result property="username" column="username"/>
<collection property="orders" javaType="list" ofType="com.test.mybatis.pojo.Order">
<id property="id" column="id_order" />
<result property="createTime" column="create_time"/>
<result property="note" column="note"/>
</collection>
</resultMap>
<select id="SelectById2" parameterType="Integer" resultMap="BankMap">
select o.id id_order, o.user_id,o.create_time,o.note,b.username from bank b left join orders o ON b.id = o.user_id
</select>
考虑到用户和订单的pojo里都有id这个主键,所以在做关联查询的时候,需要将其中一个取别名以示区别,否则在映射的时候会出现不和实际情况的异常——sql和参数映射的时候是正确的,解析也是正确的,到数据库执行是正确的,数据库的返回值也是正确的,但是sql返回的是多条完整的数据记录。因此在mapper文件中改成即可
select o.id id_order, o.user_id,o.create_time,o.note,b.username from bank b left join orders o ON b.id = o.user_id