mybatis mysql crud_Mybatis实现数据的增删改查（CRUD）

什么是 MyBatis？

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

MyBatis下载：https://github.com/mybatis/mybatis-3/releases

Mybatis实例

对一个User表的CRUD操作：

User表：

--------------------------------Table structure for `user`------------------------------

DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`userName`varchar(50) DEFAULT NULL,

`userAge`int(11) DEFAULT NULL,

`userAddress`varchar(200) DEFAULT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;--------------------------------Records of user------------------------------

INSERT INTO `user` VALUES ('1', 'summer', '30', 'shanghai');INSERT INTO `user` VALUES ('2', 'test2', '22', 'suzhou');INSERT INTO `user` VALUES ('3', 'test1', '29', 'some place');INSERT INTO `user` VALUES ('4', 'lu', '28', 'some place');INSERT INTO `user` VALUES ('5', 'xiaoxun', '27', 'nanjing');

在Src目录下建一个mybatis的xml配置文件Configuration.xml

/p>

"http://mybatis.org/dtd/mybatis-3-config.dtd">

定义User mappers的User.xml配置文件

/p>

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from `user` where user.id = #{id}

select * from user where user.userName = #{userName}

insert into user(userName,userAge,userAddress)

values(#{userName},#{userAge},#{userAddress})

update user set userName=#{userName},userAge=#{userAge},userAddress=#{userAddress} where id=#{id}

delete from user where id=#{id}

配置文件实现了接口和SQL语句的映射关系。selectUsersByName采用了2种方式实现，注释掉的也是一种实现，采用resultMap可以把属性和数据库列名映射关系定义好，property为类的属性，column是表的列名，也可以是表列名的别名！

User类的定义：

packagecom.mybatis.test;public classUser {private intid;privateString userName;private intuserAge;privateString userAddress;public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}publicString getUserName() {returnuserName;

}public voidsetUserName(String userName) {this.userName =userName;

}public intgetUserAge() {returnuserAge;

}public void setUserAge(intuserAge) {this.userAge =userAge;

}publicString getUserAddress() {returnuserAddress;

}public voidsetUserAddress(String userAddress) {this.userAddress =userAddress;

}

@OverridepublicString toString(){return this.userName+" "+this.userAge+" "+this.userAddress;

}

}

IUserOperaton定义：

packagecom.mybatis.test;importjava.util.List;public interfaceIUserOperation {public User selectUserByID(intid);public ListselectUsersByName(String userName);public voidaddUser(User user);public voidupdateUser(User user);public void deleteUser(intid);

}

IUserOperation为操作接口，函数名和mybatis的xml配置文件中的操作id名对应。

测试类Test：

packagecom.mybatis.test;importjava.io.Reader;importjava.util.List;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;public classTest {private staticSqlSessionFactory sqlSessionFactory;private staticReader reader;static{try{

reader= Resources.getResourceAsReader("Configuration.xml");

sqlSessionFactory= newSqlSessionFactoryBuilder().build(reader);

}catch(Exception e) {

e.printStackTrace();

}

}public staticSqlSessionFactory getSession() {returnsqlSessionFactory;

}public void getUserByID(intuserID) {

SqlSession session=sqlSessionFactory.openSession();try{

IUserOperation userOperation=session

.getMapper(IUserOperation.class);

User user=userOperation.selectUserByID(userID);if (user != null) {

System.out.println(user.getId()+ ":" +user.getUserName()+ ":" +user.getUserAddress());

}

}finally{

session.close();

}

}public voidgetUserList(String userName) {

SqlSession session=sqlSessionFactory.openSession();try{

IUserOperation userOperation=session

.getMapper(IUserOperation.class);

List users =userOperation.selectUsersByName(userName);for(User user : users) {

System.out.println(user.getId()+ ":" +user.getUserName()+ ":" +user.getUserAddress());

}

}finally{

session.close();

}

}/*** 增加后要commit*/

public voidaddUser() {

User user= newUser();

user.setUserAddress("place");

user.setUserName("test_add");

user.setUserAge(30);

SqlSession session=sqlSessionFactory.openSession();try{

IUserOperation userOperation=session

.getMapper(IUserOperation.class);

userOperation.addUser(user);

session.commit();

System.out.println("新增用户ID：" +user.getId());

}finally{

session.close();

}

}/*** 修改后要commit*/

public voidupdateUser() {

SqlSession session=sqlSessionFactory.openSession();try{

IUserOperation userOperation=session

.getMapper(IUserOperation.class);

User user= userOperation.selectUserByID(1);if (user != null) {

user.setUserAddress("A new place");

userOperation.updateUser(user);

session.commit();

}

}finally{

session.close();

}

}/*** 删除后要commit.

*

*@paramid*/

public void deleteUser(intid) {

SqlSession session=sqlSessionFactory.openSession();try{

IUserOperation userOperation=session

.getMapper(IUserOperation.class);

userOperation.deleteUser(id);

session.commit();

}finally{

session.close();

}

}public static voidmain(String[] args) {try{

Test test= newTest();//test.getUserByID(1);//test.getUserList("test1");//test.addUser();//test.updateUser();//test.deleteUser(6);

}catch(Exception e) {

System.out.println(e.getMessage());

}

}

}

MyBatis Mapper单个参数、多个参数传参区别

1、单个参数

mybatis不会做特殊处理

#{参数名/任意名}：取出参数值。

在接口EmployeeMapper.java中有

public Employee getEmpById(Integer id);

EmployeeMapper.xml中用#{id}取出参数值，因为只有一个，用任意名也可以，如下：

select * from tbl_employee where id = #{abcde}

2、多个参数

mybatis会做特殊处理

我们在接口EmployeeMapper.java中新加一个方法：

public Employee getEmpByIdAndLastName(Integer id,String lastName);

取值方法：用#{id},#{lastName}这种方式取出参数

select * from tbl_employee where id = #{id} and last_name=#{lastName}

结果出异常了：

org.apache.ibatis.binding.BindingException:

Parameter'id'not found.

Available parameters are [1, 0, param1, param2]

【命名参数】：明确指定封装参数：

public Employee getEmpByIdAndLastName(@Param("id") Integer id, @Param("lastName") String lastName);

这样就用#{id},#{lastName}这种方式取出参数了：

select * from tbl_employee where id = #{id} and last_name=#{lastName}

代码下载：http://download.csdn.net/detail/luxiaoxun/8056559

参考：

http://www.mybatis.org/mybatis-3/zh/getting-started.html

http://legend2011.blog.51cto.com/3018495/d-5

http://mybatis.github.io/mybatis-3/zh/sqlmap-xml.html