MyBatis学习
来源:互联网 发布:淘宝达人的名字大全集 编辑:程序博客网 时间:2024/06/03 03:22
MyBatis快速入门
JDBC example
package test.mybatis;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestJDBC{ static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/EMP"; // Database credentials static final String USER = "username"; static final String PASS = "password"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); //STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql; sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); //STEP 5: Extract data from result set while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); } //STEP 6: Clean-up environment rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); }finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// nothing we can do try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } }}其中关键的class有:Connection, Statement, DriverManager, ResultSet
jdbc dbutils hibernate和mybatis
对比jdbc, dbutils能够自动封装查询结果集,不用操作statement和resultSet。Hibernate, 基本不用写SQL 可以面向对象操作数据库
MyBatis处于dbutils和hibernate之间。自己写SQL到xml文件或者注解中。对比dbutils,在插入数据时,dbutils需要传入各属性参数,而Mybatis只需要传入对象。
mybatis example
public static void main(String[] args) throws IOException{ Reader reader = Resources.getResourceAsReader("conf.xml"); SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSession session = sessionFactory.openSession(); String statement = "test.mybatis.userMapper.getUser"; User user = session.selectOne(statement, 1);}其中主要的class有:SqlSessionFactory SqlSessionFactoryBuilder SqlSession
conf.xml -- 配置数据库连接参数以及userMappers,Utils类中提供getSession方法,该方法读取conf.xml中的属性值,返回Session对象。
userMappers.xml -- 将该实体类Mapper.xml在conf.xml中注册。
实体类Mapper.xml中定义curd操作的sql以及结果集到对象的映射
简单对象映射用resultType, 复杂对象(需要在类属性和表的列之间映射)可以用resultMap。一对多的情况可以在resultMap中定义association.
MyBatis的CRUD操作
// 在实体类Mapper.xml中配置Sql语句<pre name="code" class="html"><mapper namespace="test.mybatis.simple.userMapper1"> <select id="getUser" parameterType="int" resultType="User"> select * from Users where id = #{id} </select> <insert id="addUser" parameterType="User"> insert into Users (name, age) values (#{name}, #{age}) </insert> <delete id="deleteUser" parameterType="int"> delete from Users where id = #{id} </delete> <update id="updateUser" parameterType="User"> update Users set name=#{name}, age=#{age} where id=#{id} </update> <select id="getAllUsers" resultType="User"> select * from Users </select></mapper>
ResultMap 映射实体类属性与字段名
在实体类Mapper.xml中配置Sql到结果集的映射,解决字段名和实体类属性名不匹配。<mapper namespace="test.mybatis.simple.orderMapper1"> <!-- 重命名字段名方法 --> <select id="getOrder" parameterType="int" resultType="Order"> select order_id id, order_no orderNo, order_price price from Orders where order_id = #{id} </select> <!-- resultMap映射方法 --> <select id="getOrder2" parameterType="int" resultMap="getOrderMap"> select * from Orders where order_id = #{id} </select> <resultMap type="Order" id="getOrderMap"> <id property="id" column="order_id"/> <result property="orderNo" column="order_no"/> <result property="price" column="order_price"/> </resultMap></mapper>
Association 一对一
在实体类Mapper.xml中配置Sql到结果集的映射,解决一对一关联表查询。
<select id="getClass" parameterType="int" resultMap="getClassMap"> select c.id c_id, c.name c_name, t.id t_id, t.name t_name from Class c, Teacher t where c.teacher_id=t.id and c.id = #{id} </select> <resultMap type="Class" id="getClassMap"> <id property="id" column="c_id"/> <result property="name" column="c_name"/> <association property="teacher" column="teacher_id" javaType="Teacher"> <id property="id" column="t_id"/> <result property="name" column="t_name"/> </association> </resultMap>
Collection 一对多
在实体类Mapper.xml中配置Sql到结果集的映射,解决一对多关联表查询。
<!--联表查询--> <select id="getClass" parameterType="int" resultMap="getClassMap"> select c.id c_id, c.name c_name, t.id t_id, t.name t_name, s.id s_id, s.name s_name from Class c, Teacher t, Student s where c.teacher_id= t.id and c.id=s.class_id and c.id = #{id} </select> <resultMap type="Class" id="getClassMap"> <id property="id" column="c_id"/> <result property="name" column="c_name"/> <association property="teacher" column="teacher_id" javaType="Teacher"> <id property="id" column="t_id"/> <result property="name" column="t_name"/> </association> <collection property="list" ofType="Student"> <id property="id" column="s_id"/> <result property="name" column="s_name"/> </collection> </resultMap> <!--嵌套查询--> <select id="getClass" parameterType="int" resultMap="getClassMap"> select c.id c_id, c.name c_name, c.teacher_id teacher_id from class c where c.id=#{id} </select> <select id="getTeacher" parameterType="int" resultType="Teacher"> select t.id id, t.name name from Teacher t where t.id=#{id} </select> <select id="getStudent" parameterType="int" resultType="Student"> select s.id id, s.name name from Student s where s.class_id=#{id} </select> <resultMap type="Class" id="getClassMap"> <id property="id" column="c_id"/> <result property="name" column="c_name"/> <association property="teacher" column="teacher_id" select="getTeacher"></association> <collection property="list" column="c_id" select="getStudent"></collection> </resultMap>
动态SQL与模糊查询OGNL表达式
利用OGNL表达式构造动态sql语句
<parameterMap type="ConditionUser" id="getConditionUserMap"></parameterMap> <select id="getUser" parameterType="ConditionUser" resultType="User"> select * from Users u where <if test='name != "%null%"'> u.name like #{name} and </if> u.age between #{mixAge} and #{maxAge} </select>
调用存储过程
新建存储过程
create OR REPLACE PROCEDURE get_user_count (sex_id IN NUMBER, user_count out NUMBER)asbeginif sex_id=0 thenselect count(*) into user_count from p_user where p_user.sex='女';elseselect count(*) into user_count from p_user where p_user.sex='男' ;end if;end get_user_count;
定义UserMapper.xml
<parameterMap type="java.util.Map" id="getUserCountMap"> <parameter property="sex" mode="IN" jdbcType="INTEGER"/> <parameter property="usercount" mode="OUT" jdbcType="INTEGER"/> </parameterMap> <select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE"> call get_user_count(?,?) </select>
Test example
SqlSession session = Utils.getSession(); String querySql = "test.mybatis.procedure.userMapper.getUserCount"; Map<String, Integer> parameterMap = new HashMap<>(); parameterMap.put("sex", 1); parameterMap.put("usercount", -1); session.selectOne(querySql, parameterMap); System.out.println(parameterMap.get("usercount")); session.close();
缓存
在hibernate中,一级缓存是session级,二级缓存是sessionFactory级别。Mybatis类似,在Mybatis中,
一级缓存:基于PerpetualCache的hashMap本地缓存,其存储作用域为Session,当Session flush或close之后,该Session中的所有Cache就将清空。
二级缓存与一级缓存机制相同,默认也是采用PerpetualCache的HashMap存储,不同在于其作用域为Mapper(Namespace),并可自定义存储源,如Ehcache.
对于缓存数据更新机制,当某一个作用域(一级缓存Session/二级缓存namespaces)的进行了C/U/D操作后,默认该作用域下所有select中的缓存将被clear。
A) UserMapper.xml
<mapper namespace="test.mybatis.cache.userMapper"> <cache></cache> // 配置二级缓存 <select id="getUser" parameterType="int" resultType="User"> select * from users where id=#{id} </select> <select id="getAllUsers" resultType="User"> select * from users </select> <update id="updateUser" parameterType="User"> update users set name=#{name}, age=#{age} where id = #{id} </update></mapper>
B) Test example
public static void main(String[] args) throws IOException { SqlSessionFactory factory = Utils.getSessionFactory(); SqlSession session = factory.openSession(); String querySql = "test.mybatis.cache.userMapper.getUser"; // 测试一级缓存 User user1 = session.selectOne(querySql, 3); System.out.println(user1); User user2 = session.selectOne(querySql, 3); //不执行sql System.out.println(user2); //session.close(); //session.clearCache(); // close()或者clearCache()方法或者CRD操作能清除缓存 String updateSql = "test.mybatis.cache.userMapper.updateUser"; session.update(updateSql, new User(4, "test1", 16)); session.commit(); System.out.println("---------"); User user3 = session.selectOne(querySql, 3); // 执行sql System.out.println(user3); session.close(); System.out.println("---------------------------------------"); // 测试二级缓存 SqlSession session1 = factory.openSession(); User user4 = session1.selectOne(querySql, 3); //不执行sql session1.commit(); System.out.println(user4); session1.close();}
与Spring整合
spring配置beans.xml
<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:p="http://www.springframework.org/schema/p"xmlns:context="http://www.springframework.org/schema/context"xmlns:tx="http://www.springframework.org/schema/tx"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.2.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context-3.2.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.2.xsd"><!-- 1. 数据源 : DriverManagerDataSource --><bean id="dataSource"class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="oracle.jdbc.OracleDriver"/><property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/><property name="username" value="root"/><property name="password" value="root"/></bean><!-- 2. mybatis 的SqlSession 的工厂: SqlSessionFactoryBean --><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="dataSource"/><property name="typeAliasesPackage" value="test.mybatis.spring.bean"/></bean><!-- 3. mybatis 自动扫描加载Sql 映射文件 : MapperScannerConfigurer --><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"><property name="basePackage" value="test.mybatis.spring.mapper"/><property name="sqlSessionFactory" ref="sqlSessionFactory"/></bean><!-- 4. 事务管理 : DataSourceTransactionManager --><bean id="txManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="dataSource"/></bean><!-- 5. 使用声明式事务 --><tx:annotation-driven transaction-manager="txManager" /></beans>
mapper.xml与接口
public interface EmployeeMapper{ void save(Employee employee); void update(Employee employee); void delete(int id); Employee findById(int id);List<Employee> findAll();}
<!-- namespace名字与接口全类名一致 id与接口的某个方法名一致 --><mapper namespace="test.mybatis.spring.mapper.EmployeeMapper"> <cache></cache> <select id="findById" parameterType="int" resultType="Employee"> select * from employee where id=#{id} </select> <select id="findAll" resultType="Employee"> select * from employee </select> <update id="update" parameterType="Employee"> update employee set name=#{name}, birthday=#{birthday}, salary=#{salary} where id = #{id} </update> <delete id="delete" parameterType="int"> delete from employee where id=#{id} </delete> <insert id="save" parameterType="Employee"> insert into employee (name, birthday, salary) values (#{name}, #{birthday}, #{salary}) </insert></mapper>
测试类
@RunWith(SpringJUnit4ClassRunner.class) // 使用Spring的测试框架@ContextConfiguration("/beans.xml") // 加载spring的配置文件public class TestEmployee{ @Autowired private EmployeeMapper employeeMapper; @Test public void testAdd() { Employee employee = new Employee(-1, "tom", new Date(), 1234); employeeMapper.save(employee); } @Test public void testUpdate() { Employee employee = new Employee(6, "Tom", new Date(), 3000); employeeMapper.update(employee); }}
0 0
- Mybatis学习---了解Mybatis
- 【Mybatis学习】Mybatis缓存
- MyBatis学习
- MyBatis学习
- MyBatis学习
- Mybatis学习
- mybatis学习
- Mybatis学习
- mybatis学习
- MyBatis 学习
- MyBatis学习
- MyBatis学习
- MyBatis学习
- MyBatis学习
- mybatis学习
- mybatis 学习
- mybatis学习
- MyBatis学习
- .Net中导出数据到Excel(asp.net和winform程序中)
- python脚本网页点击抢购京东乐视2(现在好像不用抢购了)
- 文章标题
- iOS 教你如何修改微信运动步数
- XML文件转换成plist文件
- MyBatis学习
- mysql创建用户 赋权
- 梯度下降法和反向传导法
- logback 常用配置详解(二) <appender>
- android 之推送(本地推送远程推送)
- 设计模式之简单工厂模式(C++实现)
- 上线5年,手握用户5千万、问题1千万,知乎要做付费文字直播了,你会买单吗?
- CDH集群部署时遇到的问题
- Android中Fragment切换时重叠透明问题总结