mybatis入门使用1:mybatis框架搭建和增删改查

     一、mybatis简单介绍

     MyBatis是一个支持普通SQL查询，存储过程和高级映射的优秀持久层框架，为ibatis的升级版。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录。

   二、框架搭建

   1、创建maven project：

   pom.xml:

  <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.lls</groupId>
<artifactId>mybatis</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>mybatis Maven Webapp</name>
<url>http://maven.apache.org</url>

<properties>
<!-- lib versions -->
<junit.version>4.11</junit.version>
<spring.version>4.0.2.RELEASE</spring.version>
<mybatis.version>3.2.2</mybatis.version>
<mybatis.spring.version>1.2.2</mybatis.spring.version>
<mysql.connector.version>5.1.30</mysql.connector.version>
<slf4j.version>1.6.6</slf4j.version>
<log4j.version>1.2.12</log4j.version>

</properties>

<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
</dependency>

<!-- springframe start -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- springframe end -->

<!-- mybatis start -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>

<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>${mybatis.spring.version}</version>
</dependency>
<!--mybatis end -->

<!-- mysql-connector -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.connector.version}</version>
</dependency>

<!-- log start -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>
<!-- log end -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.3</version>
</dependency>

</dependencies>
<build>
<finalName>mybatis</finalName>
</build>
</project>

2、配置spring-mybatis.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:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">
<!-- 自动扫描 -->
<context:component-scan base-package="com.lls" />
<!-- 引入配置文件 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:local/jdbc.properties" />
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
<!-- 初始化连接大小 -->
<property name="initialSize" value="${initialSize}"></property>
<!-- 连接池最大数量 -->
<property name="maxActive" value="${maxActive}"></property>
<!-- 连接池最大空闲 -->
<property name="maxIdle" value="${maxIdle}"></property>
<!-- 连接池最小空闲 -->
<property name="minIdle" value="${minIdle}"></property>
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="${maxWait}"></property>
</bean>

<!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 自动扫描mapping.xml文件 -->
<property name="mapperLocations" value="classpath:sqlMapper/dao/*.xml"></property>
</bean>

<!-- DAO接口所在包名，Spring会自动查找其下的类 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lls.mapper" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
</bean>

<!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>

</beans>

3、jdbc.properties

#mysql db connect
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root
initialSize=0
maxActive=20
maxIdle=20
minIdle=1
maxWait=60000

4、log4j.properties

log4j.rootLogger=INFO,Console,File
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.Target=System.out
log4j.appender.Console.layout = org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=[%c] - %m%n


log4j.appender.File = org.apache.log4j.RollingFileAppender
log4j.appender.File.File = logs/mybatis.log
log4j.appender.File.MaxFileSize = 10MB—
log4j.appender.File.Threshold = ALL
log4j.appender.File.layout = org.apache.log4j.PatternLayout
log4j.appender.File.layout.ConversionPattern =[%p] [%d{yyyy-MM-dd HH\:mm\:ss}][%c]%m%n

5、数据库sql脚本CREATE TABLE `t_department` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`DepartmentName` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`Description` varchar(255) DEFAULT NULL,
`Manager` varchar(64) DEFAULT NULL,
`Tel` varchar(32) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `t_employee` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`EmployeeName` varchar(64) DEFAULT NULL,
`Position` varchar(64) DEFAULT NULL,
`Salary` double(16,0) DEFAULT NULL,
`Tel` varchar(32) DEFAULT NULL,
`DepartmentID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

三、增删改查测试：

1、model类

package com.lls.model;

public class Employee {
private Integer id;

private String employeename;

private String position;

private Double salary;

private String tel;

private Integer departmentid;

// get/set方法
}

2、mapper.java

package com.lls.mapper;

import org.apache.ibatis.annotations.Param;

import com.lls.model.Employee;

public interface EmployeeMapper {
int deleteByPrimaryKey(Integer id);
int insert(Employee record);
Employee selectByPrimaryKey(Integer id);
int updateByPrimaryKey(Employee record);
}

3、mapper.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="com.lls.mapper.EmployeeMapper">
<resultMap id="BaseResultMap" type="com.lls.model.Employee">
<id column="ID" jdbcType="INTEGER" property="id" />
<result column="EmployeeName" jdbcType="VARCHAR" property="employeename" />
<result column="Position" jdbcType="VARCHAR" property="position" />
<result column="Salary" jdbcType="DOUBLE" property="salary" />
<result column="Tel" jdbcType="VARCHAR" property="tel" />
<result column="DepartmentID" jdbcType="INTEGER" property="departmentid" />
</resultMap>
<sql id="Base_Column_List">
ID, EmployeeName, Position, Salary, Tel, DepartmentID
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_employee
where ID = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_employee
where ID = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.lls.model.Employee">
<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID() AS ID
</selectKey>
insert into t_employee (ID, EmployeeName, Position,
Salary, Tel, DepartmentID
)
values (#{id,jdbcType=INTEGER}, #{employeename,jdbcType=VARCHAR}, #{position,jdbcType=VARCHAR},
#{salary,jdbcType=DOUBLE}, #{tel,jdbcType=VARCHAR}, #{departmentid,jdbcType=INTEGER}
)
</insert>

<update id="updateByPrimaryKey" parameterType="com.lls.model.Employee">
update t_employee
set EmployeeName = #{employeename,jdbcType=VARCHAR},
Position = #{position,jdbcType=VARCHAR},
Salary = #{salary,jdbcType=DOUBLE},
Tel = #{tel,jdbcType=VARCHAR},
DepartmentID = #{departmentid,jdbcType=INTEGER}
where ID = #{id,jdbcType=INTEGER}
</update>
</mapper>

4、test

package com.lls.test;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.lls.mapper.EmployeeMapper;
import com.lls.model.Employee;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:config/spring-mybatis.xml" })
public class TestMyBatisAUQR {
private static final Logger LOGGER = LoggerFactory.getLogger(TestMyBatisAUQR.class);

@Autowired
private EmployeeMapper employeeMapper;

@Before
public void testAdd() {
Employee employee = new Employee();
employee.setId(1);
employee.setDepartmentid(1);
employee.setEmployeename("xiaoA");
employee.setPosition("manager");
employee.setSalary(13000d);
int result = employeeMapper.insert(employee);
LOGGER.info("result: " + result);
}

@After
public void testDelete() {
int id = 1;
int result = employeeMapper.deleteByPrimaryKey(id);
LOGGER.info("result: " + result);
}

@Before
public void testUpdate() {
Employee employee = new Employee();
employee.setDepartmentid(1);
employee.setEmployeename("xiaoA");
employee.setPosition("manager");
employee.setSalary(13000d);
employee.setId(1);
int result = employeeMapper.updateByPrimaryKey(employee);
LOGGER.info("result: " + result);
}

@Test
public void testSelect() {
int id = 1;
Employee employee = employeeMapper.selectByPrimaryKey(id);
LOGGER.info("result: " + employee.getEmployeename());
}
}

至此，会简单的使用mybatis了。

mybatis与 jdbc：

1、使用mybatis框架可以使用框架配置来连接数据库dataSource，Connection；

2、使用spring管理来生成Statement，只需写业务的sql语句；

3、用mybatis框架 反射来将结果集ResultSet封装成对象POJO。

代码文档：http://download.csdn.net/download/lanlianhua_luffy/9869769