Mybaits简介:
Mybaits是持久层框架优化了jdbc数据库:
JDBC和Mybaits性能对比:
JDBC:
(1)JDBC操作数据库时,需要频繁的开关连接
(2)查询数据库的结果集时,需要认为的进行封装
(3)JDBC没有缓存处理
(4)JDBC的sql语句写在Java代码中
Mybaits:
(1)内部提供数据库连接池不需要频繁开关链接.
(2)半自动对象关系映射、实现结果集自动封装,但是sql需要自己写
(3)有缓存而且是二级缓存 连接断掉之后也可以获取缓存
(4)Mybatis把sql写到xml配置文件中
简单的增删改查:
1.导包
2.引入核心配置文件 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>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/这里写数据库的名字?characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="" />
</dataSource>
</environment>
<environment id="oracle">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:XE" />
<property name="username" value="ht1602" />
<property name="password" value="htdb" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="数据库配置文件的位置 包名/配置文件的名字" />//
</mappers>
</configuration>
log4j.properties不用修改 直接拷贝
log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
UserMapper.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="自定义名字在类中调用的时候要和这个一致">
<select id="自定义名字" resultType="映射的实体类 包名.类名"> select * from user</select>
</mapper>
3.测试方法:简单的增删改查
(1)得到流将核心配置文件加载进去
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
(2)获取工厂会话
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
(3)拿到会话对象
SqlSession session = factory.openSession();
(4)增删改查的操作
在增删改的时候要 提交事务
session.update("(数据库语句配置文件的namespace).(增删改标签 id的值)");
session.commit();
查询要返回一个list集合,遍历获取
List<封装的对象> list =session.selectList("(数据库语句配置文件的namespace).(select标签 id的值)");
增强for循环遍历获取
查询代码:
@Test
public void selectAll() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
List<User> users = session.selectList("User.findAll");
for (User user : users) {
System.out.println(user);
}
}
映射配置文件的写法:
<mapper namespace="User">
<!-- sql语句 -->
<select id="findAll" resultType="pojo.User"> select * from user</select>
</mapper>
插入代码:
@Test
public void insert() throws Exception{
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
session.update("User.insert");
session.commit();
}
映射配置文件的写法:
<mapper namespace="User">
<!-- sql语句 -->
<insert id="insert">insert into user (username,password,type,age) values('关羽',"admin","打野",40)</insert>
</mapper>
如何获取传到sql语句的参数:
${}没有预编译没有加引号 当传递的参数为字段名 表名 排序方式 和一些sql语句的关键字的时候需要用${}获取值
#{}有预编译的效果有引号
总结:
通过获取参数时,没有经过预编译存在sql注入的安全问题,所有有些场景必须使用#{}
能用#{} 就用#{} 实在不行就用${}
#{}的使用案例:
从对象中获取参数:
(1)创建一个对象,设置对象的属性
(2)在映射文件中 获取传递过来的属性参数
代码:
@Test
public void findAll() throws Exception{
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
User user = new User();
user.setUsername("关羽");
List<User> list = session.selectList("User.findAllOrder", user);
for (User user2 : list) {
System.out.println(user2);
}
}
<mapper namespace="User">
<select id="findAllOrder" resultType="pojo.User">select * from user where username = #{username}</select>
</mapper>
从map集合的key中获取参数: 使用#{}
(1)Map集合的key Value赋值
(2)在映射文件中
从map集合中获取参数:
代码:
HashMap< String, String> map = new HashMap<String, String>();
map.put("username", "关羽");
map.put("password","admin");
对应的是
如果把代码修改为:
HashMap< String, String> map = new HashMap<String, String>();
map.put("username", "'关羽'");
map.put("password","'admin'");
配置文件中的sql语句为:
select * from user where username = ${username} and password = ${password}
或者
HashMap< String, String> map = new HashMap<String, String>();
map.put("username", "关羽");
map.put("password","admin");
配置文件中的sql语句为:
select * from user where username = '${username}' and password = '${password}'
都可以登录成功,但是会引发sql注入攻击
sql注入:String pw = "'or 1=1 or'";
password = ''or 1=1 or''
${}的使用案例:
@Test
public void findAllUser() throws Exception{
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
HashMap<String, String> map = new HashMap<String, String>();
map.put("type", "age");
map.put("order", "DESC");
List<User> user = session.selectList("User.find",map);
for (User user2 : user) {
System.out.println(user2);
}
}
<mapper namespace="User">
<select id="find" resultType="pojo.User"> select * from user order by ${type} ${order} </select>
</mapper>
单值传递和多值传递:
session没有多值的方法 如果传递对个参数时可以把参数封装到对象中,或者放进map中
单值传递 在映射的配置文件中的sql语句中的参数名可以随意
例如:
@Test
public void deleteUser() throws Exception{
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
Integer useID = 1 ;
session.delete("User.delete",useID);
session.commit();
}
<mapper namespace="User">
<delete id="delete"> delete from user where id = #{useID}</delete>
</mapper>
动态更新:
有什么值就修改什么值
实现的方法:
if标签:
判断传递的参数是否存在,如果存在才添加修改
set标签
将原来后面的set删掉 包括sql语句 去掉最后的逗号
代码:
@Test
public void login() throws Exception{
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
User user = new User();
user.setUsername("小米");
user.setPassword("xm");
user.setAge(4);
user.setId(5);
user.setType("战士");
session.update("User.update",user);
session.commit();
}
<mapper namespace="User">
<update id="update">update user
<set>
<if test="username!=null">username=#{username},</if>
<if test="password!=null">password=#{password},</if>
<if test="type!=null">type=#{type},</if>
<if test="age!=null">age=#{age}</if>
where id = #{id}
</set>
</update>
</mapper>
动态查询:
传什么都可以查 就是不管用户查询什么,都可以查询到相应的信息
if标签
where标签
去掉前面多余的and
案例:无论通过用户名 密码 ...都可以查到相应的信息
@Test
public void select() throws Exception{
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
User user = new User();
user.setId(3);
List<User> listuser = session.selectList("User.selectAll",user );
for (User user2 : listuser) {
System.out.println(user2);
}
}
<select id="selectAll" resultType="pojo.User">select * from user
<where>
<if test="username!=null"> username=#{username}</if>
<if test="password!=null">and password=#{password}</if>
<if test="type!=null">and type=#{type}</if>
<if test="age!=null">and age=#{age}</if>
<if test="id!=null">and id=#{id}</if>
</where>
</select>