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();
}
}
}
}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);
}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)
as
begin
if sex_id=0 then
select count(*) into user_count from p_user where p_user.sex='女';
else
select 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/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/tx
http://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);
}
}
2981
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
