以下讲的是mybatis3.x版本的例子:
参考博客:blog.csdn.net/yucaifu1989/article/details/9256345
先引入mybatis-3.2.2.jar 和mysql-connector-java-5.0.4.jar即可
项目结构图:
SQL:
Create TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(50) DEFAULT NULL,
`userAge` int(11) DEFAULT NULL,
`userAddress` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Insert INTO `user` VALUES ('1', 'summer', '100', 'shanghai,pudong');
Create TABLE `article` (
`id` int(11) NOT NULL auto_increment,
`userid` int(11) NOT NULL,
`title` varchar(100) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- 添加几条测试数据
-- ----------------------------
Insert INTO `article` VALUES ('1', '1', 'test_title', 'test_content');
Insert INTO `article` VALUES ('2', '1', 'test_title_2', 'test_content_2');
Insert INTO `article` VALUES ('3', '1', 'test_title_3', 'test_content_3');
Insert INTO `article` VALUES ('4', '1', 'test_title_4', 'test_content_4');
1、建立实体类和映射文件与数据库表对应
User.java文件:
package com.yihaomen.mybatis.model;
public class User {
private int id;
private String userName;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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;
}
}
User.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.yihaomen.mybatis.dao.UserDao"><!-- 命名空间接口方式编程时这里对应dao接口-->
<!-- 开启二级缓存 -->
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>
<select id="selectUserByID" parameterType="int" resultType="User">
select * from users where ID=#{id}
</select>
<!-- 为了返回list 类型而定义的returnMap -->
<resultMap type="User" id="resultListUser">
<id column="id" property="id" />
<result column="userName" property="userName" />
<result column="password" property="password" />
</resultMap>
<!-- 返回list 的select 语句,注意 resultMap 的值是指向前面定义好的 '%'||#{userName}||'%' -->
<select id="selectUsers" parameterType="String" resultMap="resultListUser">
select * from users where userName like CONCAT(CONCAT('%', #{userName}),'%')
</select>
<!-- 直接返回map -->
<select id="queryUsers" parameterType="java.util.HashMap" resultType="java.util.HashMap">
select * from users where userName like CONCAT(CONCAT('%', #{userName}),'%')
</select>
<!--执行增加操作的SQL语句。id和parameterType
分别与IUserOperation接口中的addUser方法的名字和
参数类型一致。以#{name}的形式引用Student参数
的name属性,MyBatis将使用反射读取Student参数
的此属性。#{name}中name大小写敏感。引用其他
的gender等属性与此一致。seGeneratedKeys设置
为"true"表明要MyBatis获取由数据库自动生成的主
键;keyProperty="id"指定把获取到的主键值注入
到Student的id属性
添加前缀:suffix="" 删除后缀:suffixOverrides=","
-->
<insert id="addUser" parameterType="User">
insert into users(
<trim suffix="" suffixOverrides=",">
<if test="userName!=null">
userName,
</if>
<if test="password!=null">
password,
</if>
</trim>
) values(
<trim suffix="" suffixOverrides=",">
<if test="userName!=null">
#{userName},
</if>
<if test="password!=null">
#{password},
</if>
</trim>
)
</insert>
<update id="updateUser" parameterType="User" >
update users
<trim prefix="SET" suffixOverrides=",">
<if test="password and password != ''"> PASSWORD=#{password},</if>
</trim>
where id=#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from users where id=#{id}
</delete>
<!-- User 联合文章进行查询 方法之一的配置 (多对一的方式) -->
<!-- 方式一 -->
<resultMap id="resultUserArticleList" type="Article">
<id property="id" column="aid" />
<result property="title" column="title" />
<result property="content" column="content" />
<association property="user" javaType="User">
<id property="id" column="id" />
<result property="userName" column="userName" />
<result property="password" column="password" />
</association>
</resultMap>
<!-- User 联合文章进行查询 方法之二的配置 (多对一的方式) -->
<!-- <resultMap type="User" id="resultListUser">
<id column="id" property="id" />
<result column="userName" property="userName" />
<result column="userAge" property="userAge" />
<result column="userAddress" property="userAddress" />
</resultMap>
<resultMap id="resultUserArticleList-2" type="Article">
<id property="id" column="aid" />
<result property="title" column="title" />
<result property="content" column="content" />
<association property="user" javaType="User" resultMap="resultListUser" />
</resultMap> -->
<select id="getUserArticles" parameterType="int" resultMap="resultUserArticleList">
select users.id,users.userName,users.password,article.id aid,article.title,article.content
from users ,article
where users.id=article.userid and users.id=#{id}
</select>
</mapper>
另一个实体类对象用做联合查询测试
Article.java文件:
package com.yihaomen.mybatis.model;
public class Article {
private int id;
private User user;
private String title;
private String content;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
}
UserDao.java文件:
package com.yihaomen.mybatis.dao;
import java.util.List;
import java.util.Map;
import com.yihaomen.mybatis.model.Article;
import com.yihaomen.mybatis.model.User;
public interface UserDao {
//接口方法名对应sql的ID
public User selectUserByID(int id);
//查询list
public List<User> selectUsers(String userName);
//参数和返回都用hashMap形式
public List<Map> queryUsers(Map map);
//插入数据
public void addUser(User user);
//更新数据
public void updateUser(User user);
public void deleteUser(int id);
//关联查询
public List<Article> getUserArticles(int id);
}
mybatis的核心配置文件Configuration.xml
<?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>
<typeAliases>
<!-- User.java类取一个别名User -->
<typeAlias alias="User" type="com.yihaomen.mybatis.model.User"/>
<typeAlias alias="Article" type="com.yihaomen.mybatis.model.Article"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!-- mybatis有两种事务管理类型JDBC和 MANAGED -->
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb" />
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/yihaomen/mybatis/model/User.xml"/>
</mappers>
</configuration>
测试类:Test.java
package com.yihaomen.test;
import java.io.IOException;
import java.io.Reader;
import java.security.spec.MGF1ParameterSpec;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 com.yihaomen.mybatis.dao.UserDao;
import com.yihaomen.mybatis.model.Article;
import com.yihaomen.mybatis.model.User;
public class Test {
private SqlSessionFactory sqlSessionFactory;
private Reader reader;
public SqlSessionFactory getSession() {
try {
reader = Resources.getResourceAsReader("Configuration.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSessionFactory;
}
//用session单条数据查询
// @org.junit.Test
public void queryByIdtest(){
getSession();
SqlSession session = sqlSessionFactory.openSession();
try {
User u = session.selectOne("com.yihaomen.mybatis.dao.UserDao.selectUserByID",3);
System.out.println(u.getUserName()+"--"+u.getPassword());
}finally {
session.close();
}
}
//接口方式编程
//@org.junit.Test
public void queryinfaceById(){
getSession();
SqlSession session = sqlSessionFactory.openSession();
try {
UserDao userdao=session.getMapper(UserDao.class);
User user = userdao.selectUserByID(3);
System.out.println(user.getUserName());
}catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
//查询list
@org.junit.Test
public void QueryUserListByName(){
getSession();
SqlSession session = sqlSessionFactory.openSession();
try {
UserDao userdao=session.getMapper(UserDao.class);
List<User> users = userdao.selectUsers("文");
for(User user:users){
System.out.println(user.getId()+"-"+user.getUserName()+"-"+user.getPassword());
}
System.out.println("==========================");
//参数用 和返回类型都是map形式
Map map=new HashMap();
map.put("userName", "文");
List<Map> list=userdao.queryUsers(map);
for(Map ma:list){
System.out.println(ma.get("id").toString()+ma.get("username").toString()+ma.get("password"));
}
}catch(Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
//插入数据
@org.junit.Test
public void addUsertest(){
getSession();
SqlSession session = sqlSessionFactory.openSession();
try {
UserDao userdao=session.getMapper(UserDao.class);
User user=new User();
user.setUserName("钢管");
user.setPassword("1534");
userdao.addUser(user);
session.commit();//要记得提交事务否则不会更新上去
}catch(Exception e) {
session.rollback();//回滚
e.printStackTrace();
} finally {
session.close();
}
}
//更新数据
@org.junit.Test
public void updateUsertest(){
getSession();
SqlSession session = sqlSessionFactory.openSession();
try {
UserDao userdao=session.getMapper(UserDao.class);
User user=new User();
user.setId(8);
user.setPassword("15888");
userdao.updateUser(user);
session.commit();//要记得提交事务否则不会更新上去
}catch(Exception e) {
session.rollback();//回滚
e.printStackTrace();
} finally {
session.close();
}
}
//删除数据
@org.junit.Test
public void deleteUsertest(){
getSession();
SqlSession session = sqlSessionFactory.openSession();
try {
UserDao userdao=session.getMapper(UserDao.class);
userdao.deleteUser(1);
session.commit();//要记得提交事务否则不会更新上去
}catch(Exception e) {
session.rollback();//回滚
e.printStackTrace();
} finally {
session.close();
}
}
//关联查询
@org.junit.Test
public void getUserArticles(){
getSession();
SqlSession session = sqlSessionFactory.openSession();
try {
UserDao userdao=session.getMapper(UserDao.class);
List<Article> articles = userdao.getUserArticles(2);
for(Article article:articles){
System.out.println(article.getTitle()+" ,"+article.getContent()+
" ,"+article.getUser().getUserName()+" ,"+article.getUser().getPassword());
}
} finally {
session.close();
}
}
}