Mybatis入门
为什么使用Mybatis？
我们要从JDBC程序开始说起
需求
案例场景: 根据 user 表，查询出所有“上海”的用户对象，返回一个pojo的 List集合。
初始化测试数据
1、创建数据库，数据库名：mybatisDemo
2、将如下Sql语句在数据库中执行，初始化测试数据
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP DATABASE IF EXISTS `mybatisDemo`;
CREATE DATABASE mybatisDemo CHARSET=utf8;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` date DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '2018-07-10', '1', '北京');
INSERT INTO `user` VALUES ('2', '李四', '2018-07-10', '1', '上海');
INSERT INTO `user` VALUES ('3', '王五', '2018-07-10', '1', '广州');
INSERT INTO `user` VALUES ('4', '王六', '2018-07-10', '1', '深圳');
INSERT INTO `user` VALUES ('5', '王八', '2018-07-10', '1', '上海');JDBC 实现
代码：
- 创建pojo类
2.编写JDBC代码import java.util.Date; public class User { private Integer id; private String username; private String sex; private Date birthday; private String address; // 省略getter ，setter，toString 方法 // ...... // ...... }public class JdbcTest { @Test public void test1(){ ResultSet resultSet = null; PreparedStatement preparedStatement = null; Connection connection = null; try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://127.0.0.1:3306/mybatisdemo"; connection = DriverManager.getConnection(url,"root","root"); List<User> users = new ArrayList<>();//JDK 1.7 之后才允许 // String sql = " select id, username ,birthday,sex , address from user where address = ? "; String addr = "上海"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,addr); boolean isSelect = preparedStatement.execute(); if(isSelect){ resultSet = preparedStatement.getResultSet(); while (resultSet.next()){ int id = resultSet.getInt("id"); String username = resultSet.getString("username"); Date birthday = resultSet.getDate("birthday"); String sex = resultSet.getString("sex"); String address = resultSet.getString("address"); User user = new User(id,username,sex,birthday,address); users.add(user); } } for (User u : users) { System.out.println(u); } /* ResultSet resultSet = preparedStatement.executeQuery(); int count = preparedStatement.executeUpdate();*/ } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭结果集 if(resultSet!=null) try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } // 关闭statement if(preparedStatement!=null) try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } // 关闭连接 if( connection!=null) try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
JDBC问题分析：
上边使用JDBC的原始方法（未经封装）实现了查询数据库表记录的操作。
1、数据库链接创建、释放频繁造成系统资源浪费从而影响系统性能，如果使用数据库链接池可解决此问题。
2、Sql语句在代码中硬编码，造成代码不易维护，实际应用sql变化的可能较大，sql变动需要改变Java代码。
3、使用preparedStatement向占有位符号传参数存在硬编码，因为sql语句的where条件不一定，可能多也可能少，修改sql还要修改代码，系统不易维护。
4、对结果集解析存在硬编码（查询列名），sql变化导致解析代码变化，系统不易维护，如果能将数据库记录封装成pojo对象解析比较方便。
下面我们将通过Mybatis 解决部分问题。
Mybatis 实现
创建maven工程
导入mybatis，jdbc等相关包
<dependencies> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.48</version> </dependency> <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.3</version> </dependency> <!-- https://mvnrepository.com/artifact/junit/junit --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> </dependencies>建立数据库和数据表
-- ---------------------------- -- Table structure for `user` -- ---------------------------- DROP DATABASE IF EXISTS `mybatisDemo`; CREATE DATABASE mybatisDemo CHARSET=utf8; DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL COMMENT '用户名称', `birthday` date DEFAULT NULL COMMENT '生日', `sex` char(1) DEFAULT NULL COMMENT '性别', `address` varchar(256) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('1', '张三', '2018-07-10', '1', '北京'); INSERT INTO `user` VALUES ('2', '李四', '2018-07-10', '1', '上海'); INSERT INTO `user` VALUES ('3', '王五', '2018-07-10', '1', '广州'); INSERT INTO `user` VALUES ('4', '王六', '2018-07-10', '1', '深圳'); INSERT INTO `user` VALUES ('5', '王八', '2018-07-10', '1', '上海');配置jdk编译环境，防止乱码
<build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>1.8</source> <target>1.8</target> <encoding>UTF-8</encoding> </configuration> </plugin> </plugins> </build>新建实体类和Dao接口
public class User {
private Integer id;
private String username;
private String sex;
private Date birthday;
private String address;
}
//get，set 构造方法以及toString方法省略public interface UserDao {
User selectUserByPrimaryKey(int id);
}配置使用mybatis
配置文件：mybatis-cofig.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> <environments default="env1"> <environment id="env1"> <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/mybatisDemo"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <mappers> <mapper resource="mybatis/mapper/UserMapper.xml"/> </mappers> </configuration>映射文件：UserDao.xml（sql语句 ）
<?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="net.suncaper.mybatis01hello.dao.UserDao"> <select id="selectUserByPrimaryKey" parameterType="_int" resultType="net.suncaper.mybatis01hello.domain.User"> select * from user where id = #{id} </select> </mapper>
也可以把配置文件写成这样，使用resultMap的方式让查询出的值与实体类的属性名一一对应：
<?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="top.leafii.mybatis01.mapper.UserMapper">
<resultMap id="userMap" type="top.leafii.mybatis01.domain.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</resultMap>
<select id="selectUserByAddrSexa" parameterType="map" resultMap="userMap">
select * from user where sex=#{sex} and address=#{address}
</select>
</mapper> * 名称空间namespace要和Dao接口的完整类名对应
* sql语句的id和方法名一致
* 注意写上sql语句的参数和返回类型（parameterType 和 resultType )
* parameterType如果方法中是int，parameterType要写成_int,如果方法中是Integer，就要写成int
* resultType就是为了将查询好的数据的字段名和实体类的属性一一对应
* 记住把映射文件添加到配置文件中(mapper标签 )
写java运行代码
- 读取配置文件
- 创建SqlSessionFactory对象
- 打开SqlSession 对象（mybatis框架的核心对象）
- 通过SqlSession获取UserDao接口对象
- 执行对应的sql方法
- 关闭SqlSession
public class Main {
UserDao dao = null;
public static void main(String[] args) throws Exception {
//1. 读取配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis/mybatis-config.xml");
//2. 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3. 打开SqlSession，SqlSession相当于jdbc的Connection
SqlSession sqlSession = sqlSessionFactory.openSession();
//4. 拿到Dao接口代理对象
UserDao dao = sqlSession.getMapper(UserDao.class);
User user = dao.selectUserByPrimaryKey(5);
System.out.println(user);
//5.关闭资源
sqlSession.close();
}
}