初次使用mybatis,做好笔记,自己写个例子。
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis.本文只针对Mybatis如何使用做了一个小实例,无依赖框架spring,单独使用。本文使用Mybatis—3.1.1版本 下载Mybatis—3.1.1 .jar包 将主jar包以及lib下所有jar包复制到工程web-inf下lib中
做为持久层框架就一定要和数据库打交道
首先我们建立我们要使用到的库表
role表 创建sql语句
CREATE TABLE `role` (
`roleId` bigint(20) NOT NULL AUTO_INCREMENT,
`roleName` varchar(50) NOT NULL,
PRIMARY KEY (`roleId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
User表 创建sql语句
CREATE TABLE `user` (
`userId` bigint(20) NOT NULL AUTO_INCREMENT,
`userName` varchar(30) NOT NULL,
`password` varchar(80) NOT NULL,
`role_id` bigint(20) NOT NULL,
PRIMARY KEY (`userId`),
KEY `fk_role` (`role_id`),
CONSTRAINT `fk_role` FOREIGN KEY (`role_id`) REFERENCES `role` (`roleId`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
然后创建实体类
package com.mybatis.domain;
public class Role {
public int roleId;
public String roleName;
public int getRoleId() {
return roleId;
}
public void setRoleId(int roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
}
package com.mybatis.domain;
public class User {
private int userId;
private String userName;
private String password;
private int role_id;
private Role role;
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getRole_id() {
return role_id;
}
public void setRole_id(int role_id) {
this.role_id = role_id;
}
}
接下来我们定义接口
package com.mybatis.Dao;
import java.util.List;
import com.mybatis.domain.User;
public interface UserDao {
public int insert(User user);
public int update(User user);
public int delete(String userName);
public List<User> selectAll();
public int countAll();
public User findByName(String username);
} 创建加入此接口的映射配置文件——UserDaoMapper.xml接着需要创建mybatis主配置文件——MyBatis-Configuration.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.mybatis.Dao.UserDao"> <select id="countAll" resultType="int"> select count(*) c from user; </select> <select id="selectAll" resultType="com.mybatis.domain.User"> select * from user order by userName asc </select> <insert id="insert" parameterType="com.mybatis.domain.User"> insert into user(userName,password) values(#{userName},#{password}) </insert> <update id="update" parameterType="com.mybatis.domain.User"> update user set userName=#{userName},password=#{password} where userName=#{userName} </update> <delete id="delete" parameterType="int"> delete from user where userName=#{userName} </delete> <select id="findByName" parameterType="String" resultType="com.mybatis.domain.User"> select * from user where userName=#{userName} <!-- <if test="userName!=null"> and userName=#{userName} </if> --> </select> <select id="findByCon" parameterType="int" resultMap="byCon_result"> select u.userId, u.userName, u.`password`, u.role_id, r.roleId, r.roleName from user u left join role r on u.role_id=r.roleId where u.userId=#{id} </select> <resultMap type="com.mybatis.domain.User" id="byCon_result"> <id column="id" property="userId"/> <result column="userName" property="userName"/> <result column="password" property="password"/> <result column="role_id" property="role_id"/> <association property="role" javaType="com.mybatis.domain.Role"> <id column="roleId" property="roleId"/> <result column="roleName" property="roleName"/> </association> </resultMap> </mapper>
最后我们编写测试程序<?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="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mybatistest?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull" /> <property name="username" value="root" /> <property name="password" value="admin" /> </dataSource> </environment> </environments> <mappers> <mapper resource="com/mybatis/domain/UserDaoMapper.xml"/> </mappers> </configuration>
package com.mybatis.test; import java.io.IOException; import java.io.Reader; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Iterator; import java.util.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.apache.ibatis.session.SqlSessionManager; import com.mybatis.Dao.UserDao; import com.mybatis.domain.Role; import com.mybatis.domain.User; public class UserDaoTest { private static SqlSessionFactory getSessionFactory(){ SqlSessionFactory sessionFactory = null; String resource = "MyBatis-Configuration.xml"; try { sessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader(resource)); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return sessionFactory; } public static void main(String[] args) { SqlSession sqlSession = getSessionFactory().openSession(); UserDao userDao = sqlSession.getMapper(UserDao.class); //根据username查询用户 User user1 = sqlSession.selectOne("findByName","zhangsan"); System.out.println(user1.getUserName()); //根据id查询 User user = sqlSession.selectOne("findByCon",1); System.out.println(user.getUserName()); //查询全部 List<User> users = userDao.selectAll(); for (int i = 0; i < users.size(); i++) { User us = users.get(i); System.out.println("用户名:"+us.getUserName()); System.out.println("密码:"+us.getPassword()); } User user2 = new User(); user2.setUserName("wangwu"); user2.setPassword("123"); user2.setRole_id(1); try { //增加用户 int nu = userDao.insert(user2); sqlSession.commit(); System.out.println("增加"+nu+"个用户"); } catch (Exception e) { // TODO Auto-generated catch block sqlSession.rollback(); e.printStackTrace(); }finally{ sqlSession.close(); } } }