近日学习Mybatis 3,下载了这个资源(http://download.csdn.net/detail/a499316988/5120026),发现文章写得很好,但里面的例子不是maven的,因此jar并不完整,于是把例子4(两表关联查询)和例子5(mybatis/spring集成)转换为了maven工程。文章末尾可下载工程代码,没兴趣看的同学可直接跳至文章末尾。
1. 建表及插入数据使用的MySQL 5.5,数据库名是mybatis,共两张表,user和article
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');
2. POM文件
使用了dbcp连接池。不是web工程,这里只有普通application包含的jar
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>spring-mybatis-test</groupId>
<artifactId>spring-mybatis-test</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-mybatis-test</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>3.2.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.1.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.22</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
</dependencies>
</project>
3. spring的applicationContext.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:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" 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.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://127.0.0.1:3306/mybatis?characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="111111" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="Configuration.xml" />
</bean>
<bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
<property name="mapperInterface" value="com.ourfuture.test.mybatis.inter.IUserOperation" />
</bean>
</beans>
4. 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>
<typeAlias alias="User" type="com.ourfuture.test.mybatis.model.User" />
<typeAlias alias="Article" type="com.ourfuture.test.mybatis.model.Article" />
</typeAliases>
<!-- 使用spring管理数据源的时候,整个environments节点都可以删除 -->
<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://127.0.0.1:3306/mybatis" />
<property name="username" value="root" />
<property name="password" value="111111" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/ourfuture/test/mybatis/model/User.xml" />
</mappers>
</configuration>
5. mybatis必须的model类及对应的xml文件。
User.java
package com.ourfuture.test.mybatis.model;
public class User {
private int id;
private String userName;
private int userAge;
private String userAddress;
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 int getUserAge() {
return userAge;
}
public void setUserAge(int userAge) {
this.userAge = userAge;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
}
Article.java
package com.ourfuture.test.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;
}
}
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.ourfuture.test.mybatis.inter.IUserOperation">
<!-- <mapper namespace="com.ourfuture.test.mybatis.model.User"> -->
<select id="selectUserByID" parameterType="int" resultType="User">
select * from `user` where id = #{id}
</select>
<select id="selectUsers" parameterType="string" resultMap="resultListUser">
select * from user where userName like #{userName}
</select>
<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>
<insert id="addUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
insert into user(userName, userAge, userAddress) values(#{userName}, #{userAge}, #{userAddress})
</insert>
<update id="updateUser" parameterType="User">
update user set userName=#{userName},userAge=#{userAge},userAddress=#{userAddress} where id=#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from user where id=#{id}
</delete>
<resultMap type="Article" id="resultUserArticleList">
<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="userAddress" column="userAddress"/>
</association>
</resultMap>
<select id="getUserArticles" parameterType="int" resultMap="resultUserArticleList">
select user.id, user.userName, user.userAddress, article.id aid, article.title, article.content
from user, article
where user.id = article.userid and user.id = #{id}
</select>
</mapper>
6. Mybatis的接口类IUserOperation.java
package com.ourfuture.test.mybatis.inter;
import java.util.List;
import com.ourfuture.test.mybatis.model.Article;
import com.ourfuture.test.mybatis.model.User;
public interface IUserOperation {
public User selectUserByID(int id);
public List<User> selectUsers(String userName);
public void addUser(User user);
public void updateUser(User user);
public void deleteUser(int id);
public List<Article> getUserArticles(int id);
}
7. 测试类
MybatisTest.java(这个是非spring注入数据源的测试类)
package com.ourfuture.test.mybatis;
import java.io.Reader;
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 com.ourfuture.test.mybatis.inter.IUserOperation;
import com.ourfuture.test.mybatis.model.Article;
import com.ourfuture.test.mybatis.model.User;
public class MybatisTest {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("Configuration.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSession() {
return sqlSessionFactory;
}
public void getUserList(String userName) {
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session
.getMapper(IUserOperation.class);
List<User> users = userOperation.selectUsers(userName);
for (User user : users) {
System.out.println(user.getId() + "\t" + user.getUserName()
+ "\t" + user.getUserAddress());
}
} finally {
session.close();
}
}
public void addUser() {
User user = new User();
user.setUserAddress("人民广场");
user.setUserName("飞鸟");
user.setUserAge(80);
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();
}
}
public void updateUser() {
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session
.getMapper(IUserOperation.class);
User user = userOperation.selectUserByID(4);
user.setUserAddress("魔都的浦东创新园区");
userOperation.updateUser(user);
session.commit();
} finally {
session.close();
}
}
public void deleteUser(int id) {
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session
.getMapper(IUserOperation.class);
userOperation.deleteUser(id);
session.commit();
} finally {
session.close();
}
}
public void test01() {
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session
.getMapper(IUserOperation.class);
User user = userOperation.selectUserByID(1);
System.out.println(user.getUserAddress());
System.out.println(user.getUserName());
} finally {
session.close();
}
}
public void getUserArticles(int userid) {
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session.getMapper(IUserOperation.class);
List<Article> articles = userOperation.getUserArticles(userid);
for(Article article:articles) {
System.out.println(article.getTitle()+":"+article.getContent()+ ":作者是:"+article.getUser().getUserName()+":地址:"+ article.getUser().getUserAddress());
}
} finally {
session.close();
}
}
public static void main(String[] args) {
MybatisTest obj = new MybatisTest();
// obj.getUserList("%");
// obj.addUser();
// obj.updateUser();
// obj.deleteUser(13);
obj.getUserArticles(1);
}
}
MybatisSprintTest.java(这个是spring注入数据源的测试类)
package com.ourfuture.test.mybatis;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.ourfuture.test.mybatis.inter.IUserOperation;
import com.ourfuture.test.mybatis.model.Article;
import com.ourfuture.test.mybatis.model.User;
public class MybatisSprintTest {
private static ApplicationContext ctx;
static {
ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
}
public static void main(String[] args) {
IUserOperation mapper = (IUserOperation) ctx.getBean("userMapper"); // 测试id=1的用户查询,根据数据库中的情况,可以改成你自己的.
System.out.println("得到用户id=1的用户信息");
User user = mapper.selectUserByID(1);
System.out.println(user.getUserAddress());
//得到文章列表测试
System.out.println("得到用户id为1的所有文章列表");
List<Article> articles = mapper.getUserArticles(1);
for (Article article : articles) {
System.out
.println(article.getContent() + "--" + article.getTitle());
}
}
}
完整代码下载地址:
http://download.csdn.net/detail/zhu19774279/8389211