Mybatis-mapper-xml-基础
今天学习http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html。关于mapper.xml的sql语句的使用。
项目路径:https://github.com/chenxing12/l4mybatis
首先,准备环境。
1.创建project
在parent项目上右键,new model->maven->mybatis-mapper.
填充pom.xml
<?xml version="1.0" encoding="UTF-8"?> <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"> <parent> <artifactId>l4mybatis</artifactId> <groupId>com.test</groupId> <version>1.0-SNAPSHOT</version> </parent> <modelVersion>4.0.0</modelVersion> <artifactId>mytatis-mapper</artifactId> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> </dependency> </dependencies> <build> <resources> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> </build> </project>
在resources下添加log4j.properties:
log4j.rootLogger=DEBUG, stdout, logfile log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n log4j.appender.logfile=org.apache.log4j.RollingFileAppender log4j.appender.logfile.File=log/test.log log4j.appender.logfile.MaxFileSize=128MB log4j.appender.logfile.MaxBackupIndex=3 log4j.appender.logfile.layout=org.apache.log4j.PatternLayout log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p [%t] %c.%M(%L) - %m%n
在resources下添加mybatis-config.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> <properties resource="db.properties"/> <typeAliases> <package name="com.test.mapper.model"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com.test.mapper.mapper/PersonMapper.xml"/> </mappers> </configuration>
在resources下添加db.properties:
#jdbc.driver=com.mysql.jdbc.Driver jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false jdbc.username=root jdbc.password=123456
在数据库mybatis中创建一个person表:
/* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 50605 Source Host : localhost:3306 Source Database : mybatis Target Server Type : MYSQL Target Server Version : 50605 File Encoding : 65001 Date: 2016-07-06 22:22:34 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for person -- ---------------------------- DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of person -- ---------------------------- INSERT INTO `person` VALUES ('1', 'Ryan');
在resources下创建com.test.mapper.mapper/PersonMapper.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.test.mapper.dao.PersonMapper"> <select id="selectPerson" parameterType="int" resultType="hashmap"> select * from person where id = #{id} </select> </mapper>
在java下新建com.test.mapper.dao.PersonMapper.java:
package com.test.mapper.dao; import java.util.HashMap; /** * Created by miaorf on 2016/7/6. */ public interface PersonMapper { HashMap selectPerson(int id); }
在java下添加:com.test.mapper.model.Person:
package com.test.mapper.model; import java.io.Serializable; /** * Created by miaorf on 2016/7/6. */ public class Person implements Serializable { private Integer id; private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Person{" + "id=" + id + ", name='" + name + '\'' + '}'; } }
测试环境:
在test下创建com.test.mapper.dao.PersonMapperTest:
package com.test.mapper.dao; 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.junit.Before; import org.junit.BeforeClass; import org.junit.Test; import java.io.IOException; import java.util.HashMap; import static org.junit.Assert.*; /** * Created by miaorf on 2016/7/6. */ public class PersonMapperTest { private SqlSession sqlSession; private static SqlSessionFactory sqlSessionFactory; @BeforeClass public static void init() throws IOException { String config = "mybatis-config.xml"; sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(config)); } @Before public void setUp() throws Exception { sqlSession = sqlSessionFactory.openSession(); } @Test public void selectPerson() throws Exception { PersonMapper mapper = sqlSession.getMapper(PersonMapper.class); HashMap map = mapper.selectPerson(1); System.out.println(map); } }
运行:
2016-07-06 22:23:31,962 DEBUG [org.apache.ibatis.logging.LogFactory] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2016-07-06 22:23:32,128 DEBUG [org.apache.ibatis.io.VFS] - Class not found: org.jboss.vfs.VFS 2016-07-06 22:23:32,129 DEBUG [org.apache.ibatis.io.JBoss6VFS] - JBoss 6 VFS API is not available in this environment. 2016-07-06 22:23:32,131 DEBUG [org.apache.ibatis.io.VFS] - Class not found: org.jboss.vfs.VirtualFile 2016-07-06 22:23:32,132 DEBUG [org.apache.ibatis.io.VFS] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment. 2016-07-06 22:23:32,134 DEBUG [org.apache.ibatis.io.VFS] - Using VFS adapter org.apache.ibatis.io.DefaultVFS 2016-07-06 22:23:32,135 DEBUG [org.apache.ibatis.io.DefaultVFS] - Find JAR URL: file:/D:/workspace/mybatis/l4mybatis/mytatis-mapper/target/classes/com/test/mapper/model 2016-07-06 22:23:32,135 DEBUG [org.apache.ibatis.io.DefaultVFS] - Not a JAR: file:/D:/workspace/mybatis/l4mybatis/mytatis-mapper/target/classes/com/test/mapper/model 2016-07-06 22:23:32,213 DEBUG [org.apache.ibatis.io.DefaultVFS] - Reader entry: Person.class 2016-07-06 22:23:32,214 DEBUG [org.apache.ibatis.io.DefaultVFS] - Listing file:/D:/workspace/mybatis/l4mybatis/mytatis-mapper/target/classes/com/test/mapper/model 2016-07-06 22:23:32,214 DEBUG [org.apache.ibatis.io.DefaultVFS] - Find JAR URL: file:/D:/workspace/mybatis/l4mybatis/mytatis-mapper/target/classes/com/test/mapper/model/Person.class 2016-07-06 22:23:32,215 DEBUG [org.apache.ibatis.io.DefaultVFS] - Not a JAR: file:/D:/workspace/mybatis/l4mybatis/mytatis-mapper/target/classes/com/test/mapper/model/Person.class 2016-07-06 22:23:32,217 DEBUG [org.apache.ibatis.io.DefaultVFS] - Reader entry: ���� 1 6 2016-07-06 22:23:32,220 DEBUG [org.apache.ibatis.io.ResolverUtil] - Checking to see if class com.test.mapper.model.Person matches criteria [is assignable to Object] 2016-07-06 22:23:32,306 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections. 2016-07-06 22:23:32,307 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections. 2016-07-06 22:23:32,309 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections. 2016-07-06 22:23:32,310 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - PooledDataSource forcefully closed/removed all connections. 2016-07-06 22:23:32,511 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection 2016-07-06 22:23:32,842 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 733672688. 2016-07-06 22:23:32,842 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2bbaf4f0] 2016-07-06 22:23:32,847 DEBUG [com.test.mapper.dao.PersonMapper.selectPerson] - ==> Preparing: select * from person where id = ? 2016-07-06 22:23:32,911 DEBUG [com.test.mapper.dao.PersonMapper.selectPerson] - ==> Parameters: 1(Integer) 2016-07-06 22:23:32,946 DEBUG [com.test.mapper.dao.PersonMapper.selectPerson] - <== Total: 1 {name=Ryan, id=1}
2.select
查询语句。负责拼接查询语句并将查询结果映射出来。上例中:
<select id="selectPerson" parameterType="int" resultType="hashmap"> SELECT * FROM PERSON WHERE ID = #{id} </select>
- 这个语句的id为selectPerson,这个就是对应mapper接口中的方法的名字。
- parameterType是输入参数类型为int。
- resultType表示查询结果映射为HashMap
- #{id}是占位符,相当于JDBC中采用PreparedStatement时sql语句中的问号,表示参数名为id的参数值会替换这个位置。
注意到mapper.xml的namespace就是指向所对应的mapper 接口:
<mapper namespace="com.test.mapper.dao.PersonMapper">
在mapper接口中的方法要和mapper.xml中的id所一一对应。因此,这个查询的节点对应的mapper接口的方法为:
public interface PersonMapper { HashMap selectPerson(int id); }
事实上,select节点的可选参数有以下几种:
<select id="selectPerson" parameterType="int" parameterMap="deprecated" resultType="hashmap" resultMap="personResultMap" flushCache="false" useCache="true" timeout="10000" fetchSize="256" statementType="PREPARED" resultSetType="FORWARD_ONLY">
文档对各个参数含义给出了解释:
属性 | 描述 |
---|---|
id | 在命名空间中唯一的标识符,可以被用来引用这条语句。 |
parameterType | 将会传入这条语句的参数类的完全限定名或别名。这个属性是可选的,因为 MyBatis 可以通过 TypeHandler 推断出具体传入语句的参数,默认值为 unset。 |
resultType | 从这条语句中返回的期望类型的类的完全限定名或别名。注意如果是集合情形,那应该是集合可以包含的类型,而不能是集合本身。使用 resultType 或 resultMap,但不能同时使用。 |
resultMap | 外部 resultMap 的命名引用。结果集的映射是 MyBatis 最强大的特性,对其有一个很好的理解的话,许多复杂映射的情形都能迎刃而解。使用 resultMap 或 resultType,但不能同时使用。 |
flushCache | 将其设置为 true,任何时候只要语句被调用,都会导致本地缓存和二级缓存都会被清空,默认值:false。 |
useCache | 将其设置为 true,将会导致本条语句的结果被二级缓存,默认值:对 select 元素为 true。 |
timeout | 这个设置是在抛出异常之前,驱动程序等待数据库返回请求结果的秒数。默认值为 unset(依赖驱动)。 |
fetchSize | 这是尝试影响驱动程序每次批量返回的结果行数和这个设置值相等。默认值为 unset(依赖驱动)。 |
statementType | STATEMENT,PREPARED 或 CALLABLE 的一个。这会让 MyBatis 分别使用 Statement,PreparedStatement 或 CallableStatement,默认值:PREPARED。 |
resultSetType | FORWARD_ONLY,SCROLL_SENSITIVE 或 SCROLL_INSENSITIVE 中的一个,默认值为 unset (依赖驱动)。 |
databaseId | 如果配置了 databaseIdProvider,MyBatis 会加载所有的不带 databaseId 或匹配当前 databaseId 的语句;如果带或者不带的语句都有,则不带的会被忽略。 |
resultOrdered | 这个设置仅针对嵌套结果 select 语句适用:如果为 true,就是假设包含了嵌套结果集或是分组了,这样的话当返回一个主结果行的时候,就不会发生有对前面结果集的引用的情况。这就使得在获取嵌套的结果集的时候不至于导致内存不够用。默认值:false。 |
resultSets | 这个设置仅对多结果集的情况适用,它将列出语句执行后返回的结果集并每个结果集给一个名称,名称是逗号分隔的。 |
3.Insert
首先,准备数据库:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DROP TABLE IF EXISTS `author`; CREATE TABLE `author` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(40) NOT NULL DEFAULT '', `password` varchar(128) NOT NULL DEFAULT '', `email` varchar(40) NOT NULL DEFAULT '', `bio` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
然后,编写对应实体:com.test.mapper.model.Author
package com.test.mapper.model; import java.io.Serializable; /** * Created by miaorf on 2016/7/7. */ public class Author implements Serializable { private Integer id; private String username; private String password; private String email; private String bio; public Author() { } public Author(String username, String password, String email, String bio) { this.username = username; this.password = password; this.email = email; this.bio = bio; } public Integer getId() { return id; } public void setId(Integer 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; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getBio() { return bio; } public void setBio(String bio) { this.bio = bio; } }
编写mapper接口:com.test.mapper.dao.AuthorMapper
package com.test.mapper.dao; import com.test.mapper.model.Author; import java.util.HashMap; import java.util.List; /** * Created by miaorf on 2016/7/6. */ public interface AuthorMapper { int insertAuthor(Author author); int insertAuthors(List<Author> list); }
关注我的公众号
![](https://images2018.cnblogs.com/blog/686418/201808/686418-20180822091328437-1109977663.jpg)