myBatis实例

1. myBatis的介绍:
		MyBatis是支持普通SQL查询,存储过程和高级映射的优秀持久层框架。MyBatis消除
	了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索。MyBatis可以使用简单的
	XML或注解用于配置和原始映射,将接口和Java的POJO(Plain Old Java Objects,普通的
	Java对象)映射成数据库中的记录。
	
	jdbc-->dbutil-->(mybatis)-->hibernate
	
2. mybatis快速入门
	a. 添加jar包
		mybatis
			asm-3.3.1.jar
			cglib-2.2.2.jar
			commons-logging-1.1.1.jar
			log4j-1.2.16.jar
			mybatis-3.1.1.jar
			slf4j-api-1.6.2.jar
			slf4j-log4j12-1.6.2.jar
		mysql驱动
			mysql-connector-java-5.1.7-bin.jar
		
	b. 建库+表
		create database mybatis;
		use mybatis;
		CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);
		INSERT INTO users(NAME, age) VALUES('Tom', 12);
		INSERT INTO users(NAME, age) VALUES('Jack', 11);
	c. 在应用的src下添加Mybatis的配置文件conf.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="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/mybatis" />
						<property name="username" value="root" />
						<property name="password" value="root" />
					</dataSource>
				</environment>
			</environments>
		</configuration>
		
	d. 定义表所对应的实体类
		public class User {
			private int id;
			private String name;
			private int age;
		}
	e. 定义操作users表的sql映射文件userMapper.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="org.mybatis.example.BlogMapper"> 
			<select id="selectBlog" parameterType="int" resultType="Blog"> 
				select * from Blog where id = #{id} 
			</select> 
		</mapper>
	f. 在conf.xml文件中注册userMapper.xml文件
		<mappers>
			<mapper resource="com/atguigu/day03_mybatis/test1/userMapper.xml"/>
		</mappers>
	g. 编写测试代码:执行定义的select语句
		public class Test {
			public static void main(String[] args) throws IOException {
				String resource = "conf.xml"; 
				//加载mybatis的配置文件(它也加载关联的映射文件)
				Reader reader = Resources.getResourceAsReader(resource); 
				//构建sqlSession的工厂
				SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
				//创建能执行映射文件中sql的sqlSession
				SqlSession session = sessionFactory.openSession();
				//映射sql的标识字符串
				String statement = "net.lamp.java.mybatis.bean.userMapper"+".selectUser";
				//执行查询返回一个唯一user对象的sql
				User user = session.selectOne(statement, 1);
				System.out.println(user);
			}
		}
	
3. 编写基于mybatis的操作users表的CRUD操作的dao类
	a. XML的实现:
		1. 定义sql映射xml文件:
			<insert id="insertUser" parameterType="net.lamp.java.ibatis.bean.User">
				insert into users(name, age) values(#{name}, #{age});
			</insert>
			
			<delete id="deleteUser" parameterType="int">
				delete from users where id=#{id}
			</delete>
			
			<update id="updateUser" parameterType="net.lamp.java.ibatis.bean.User">
				update users set name=#{name},age=#{age} where id=#{id}
			</update>
			
			<select id="selectUser" parameterType="int" resultType="net.lamp.java.ibatis.bean.User">
				select * from users where id=#{id}
			</select>
			
			<select id="selectAllUsers" resultType="net.lamp.java.ibatis.bean.User">
				select * from users
			</select>
		2. 在config.xml中注册这个映射文件
			<mapper resource="net/lamp/java/ibatis/bean/userMapper.xml"/>
		3. 在dao中调用:
			public User getUserById(int id) {
		
				SqlSession session = sessionFactory.openSession();
				User user = session.selectOne(URI+".selectUser", id);
				return user;
			}
	b. 注解的实现:
		1. 定义sql映射的接口
			public interface UserMapper {

				@Insert("insert into users(name, age) values(#{name}, #{age})")
				public int insertUser(User user);

				@Delete("delete from users where id=#{id}")
				public int deleteUserById(int id);
				
				@Update("update users set name=#{name},age=#{age} where id=#{id}")
				public int updateUser(User user);

				@Select("select * from users where id=#{id}")
				public User getUserById(int id);

				@Select("select * from users")
				public List<User> getAllUser();
			}
		2. 在config中注册这个映射接口
			<mapper class="net.lamp.java.ibatis.crud.ano.UserMapper"/>
		3. 在dao类中调用
			public User getUserById(int id) {
				SqlSession session = sessionFactory.openSession();
				UserMapper mapper = session.getMapper(UserMapper.class);
				User user = mapper.getUserById(id);
				return user;
			}
	
4. 几个可以优化的地方
	a. 连接数据库的几个配置可以单独放在一个properties文件中
		db.properties
		
		<properties resource="db.properties"/>
		
		<property name="driver" value="${driver}" />
		<property name="url" value="${url}" />
		<property name="username" value="${username}" />
		<property name="password" value="${password}" />
		
	b. 为实体类定义别名,简化sql映射xml文件中的引用
		<typeAliases>
			<typeAlias type="net.lamp.java.ibatis.bean.User" alias="_User"/>
		</typeAliases>
	c. 可以在src下加入log4j的配置文件,打印日志信息(主要是sql语句)
		1. log4j.properties
			log4j.properties,
			log4j.rootLogger=DEBUG, Console
			#Console
			log4j.appender.Console=org.apache.log4j.ConsoleAppender
			log4j.appender.Console.layout=org.apache.log4j.PatternLayout
			log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
			log4j.logger.java.sql.ResultSet=INFO
			log4j.logger.org.apache=INFO
			log4j.logger.java.sql.Connection=DEBUG
			log4j.logger.java.sql.Statement=DEBUG
			log4j.logger.java.sql.PreparedStatement=DEBUG
		2. log4j.xml
			<?xml version="1.0" encoding="UTF-8" ?>
			<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
			<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
				<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
					<layout class="org.apache.log4j.PatternLayout">
						<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
					</layout>
				</appender>
				<logger name="java.sql">
					<level value="debug" />
				</logger>
				<logger name="org.apache.ibatis">
					<level value="debug" />
				</logger>
				<root>
					<level value="debug" />
					<appender-ref ref="STDOUT" />
				</root>
			</log4j:configuration>
		
5. 解决表的字段名与实体类的属性名不相同的冲突
	a. 准备表和数据:
		CREATE TABLE orders(
			order_id INT PRIMARY KEY AUTO_INCREMENT,
			order_no VARCHAR(20), 
			order_price FLOAT
		);
		INSERT INTO orders(order_no, order_price) VALUES('aaaa', 23);
		INSERT INTO orders(order_no, order_price) VALUES('bbbb', 33);
		INSERT INTO orders(order_no, order_price) VALUES('cccc', 22);
			
	b. 定义实体类:
		public class Order {
			private int id;
			private String orderNo;
			private float price;
		}
	c. 实现getOrderById(id)的查询
		c1. 通过在sql语句中定义别名
			<select id="selectOrder" parameterType="int" resultType="_Order">
				select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}
			</select>
			
		c2. 定义<resultMap>
			<select id="selectOrderResultMap" parameterType="int" resultMap="orderResultMap">
				select * from orders where order_id=#{id}
			</select>
			
			<resultMap type="_Order" id="orderResultMap">
				<id property="id" column="order_id"/>
				<result property="orderNo" column="order_no"/>
				<result property="price" column="order_price"/>
			</resultMap>
	
6. 实现联表查询
	1). 一对一:
		a. 创建表和数据:
			CREATE TABLE teacher(
				t_id INT PRIMARY KEY AUTO_INCREMENT, 
				t_name VARCHAR(20)
			);
			CREATE TABLE class(
				c_id INT PRIMARY KEY AUTO_INCREMENT, 
				c_name VARCHAR(20), 
				teacher_id INT
			);
			ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);	

			INSERT INTO teacher(t_name) VALUES('LS1');
			INSERT INTO teacher(t_name) VALUES('LS2');

			INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
			INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);
		b. 定义实体类:
			public class Teacher {
				private int id;
				private String name;
			}
			public class Classes {
				private int id;
				private String name;
				private Teacher teacher;
			}
		c. 定义sql映射文件ClassMapper.xml:
			<!-- 方式一:嵌套查询方式, 通过执行另外一个SQL映射语句来返回预期的复杂类型 -->
			<select id="getClasses" parameterType="int" resultMap="ClassesResultMap2">
				select * from class where c_id=#{id}
			</select>
			
			<resultMap type="CLasses" id="ClassesResultMap2">
				<id column="c_id" property="id"/>
				<result column="c_name" property="name"/>
				<association property="teacher" javaType="Teacher" column="teacher_id" select="getTeacher"></association>
			</resultMap>
			
			<select id="getTeacher" parameterType="int" resultType="Teacher">
				select t_id id, t_name name from teacher where t_id=#{id}
			</select>
				
			<!-- 
				方式二:嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
			 -->
			 <select id="getClasses2" parameterType="int" resultMap="ClassesResultMap">
					select * from class c,teacher t where c.c_id=#{id} and c.teacher_id=t.t_id; 
			</select>
			
			<resultMap type="Classes" id="ClassesResultMap">
				<id column="c_id" property="id"/>
				<result column="c_name" property="name"/>
				<association column="teacher_id" property="teacher" javaType="Teacher">
					<id column="t_id" property="id"/>
					<result column="t_name" property="name"/>
				</association>
			</resultMap>
		d. 测试:
			@Test
			public void testQueryOO() {
				SqlSession session = factory.openSession();
				Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.CTMapper.getClasses", 1);
				System.out.println(c);
			}
			
			@Test
			public void testQueryOO2() {
				SqlSession session = factory.openSession();
				Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.CTMapper.getClasses2", 1);
				System.out.println(c);
			}
	2) 一对多
		a. 创建表和数据:
			CREATE TABLE student(
				s_id INT PRIMARY KEY AUTO_INCREMENT, 
				s_name VARCHAR(20), 
				class_id INT
			);
			INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
			INSERT INTO student(s_name, class_id) VALUES('xs_D', 1);
			INSERT INTO student(s_name, class_id) VALUES('xs_E', 1);
			INSERT INTO student(s_name, class_id) VALUES('xs_A', 2);
			INSERT INTO student(s_name, class_id) VALUES('xs_H', 2);
			INSERT INTO student(s_name, class_id) VALUES('xs_J', 2);
		b. 定义实体类:
			public class Student {
				private int id;
				private String name;
			}
			
			public class Classes {
				private int id;
				private String name;
				private Teacher teacher;
				private List<Student> students;
			}
		
		c. 定义sql映射文件ClassMapper.xml:(根据classId查询对应的班级信息,包括学生)
			<!-- 集合的嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集-->
			 <select id="getClasses3" parameterType="int" resultMap="ClassesResultMap3">
					select * from class c,teacher t, student s where c.c_id=#{id} and c.teacher_id=t.t_id and s.class_id=c.c_id 
			</select>
			
			<resultMap type="Classes" id="ClassesResultMap3">
				<id column="c_id" property="id"/>
				<result column="c_name" property="name"/>
				<association column="teacher_id" property="teacher" javaType="Teacher">
					<id column="t_id" property="id"/>
					<result column="t_name" property="name"/>
				</association>
				<collection property="students" ofType="Student" javaType="ArrayList">
					<id property="id" column="s_id" />
					<result property="name" column="s_name"/>
				</collection>
			</resultMap>
			
			<!-- 集合的嵌套查询方式, 通过执行另外一个SQL映射语句来返回预期的复杂类型 -->
			<select id="getClasses4" parameterType="int" resultMap="ClassesResultMap4">
					select * from class c where c.c_id=#{id}
			</select>
			<resultMap type="CLasses" id="ClassesResultMap4">
				<id column="c_id" property="id"/>
				<result column="c_name" property="name"/>
				<association property="teacher" javaType="Teacher" column="teacher_id" select="getTeacher"></association>
				<collection property="students" ofType="Teacher" column="c_id" select="getStudentsSelect" ></collection>
			</resultMap>
			
			<select id="getTeacher" parameterType="int" resultType="Teacher">
				select t_id id, t_name name from teacher where t_id=#{id}
			</select>
			<select id="getStudentsSelect" parameterType="int" resultType="Student" >
				select s_id id, s_name name from student where class_id=#{id}
			</select>
		d. 测试:
			@Test
			public void testQueryOT() {
				SqlSession session = factory.openSession();
				Classes classes = openSession.selectOne("com.atguigu.day03_mybatis.test6.OMMapper.getClasses3", 1);
				System.out.println(c);
			}
			
			@Test
			public void testQueryOT2() {
				SqlSession session = factory.openSession();
				Classes classes = openSession.selectOne("com.atguigu.day03_mybatis.test6.OMMapper.getClasses4", 1);
				System.out.println(c);
			}

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

dbms_xplan之display_cursor函数的使用

        DBMS_XPLAN包中display_cursor函数不同于display函数,display_curso...

883
来自专栏乐沙弥的世界

PL/SQL --> 动态SQL的常见错误

动态SQL在使用时,有很多需要注意的地方,如动态SQL语句结尾处不能使用分号(;),而动态PL/SQL结尾处需要使用分号(;),但不能使用正

662
来自专栏芋道源码1024

数据库[分库分表]中间件 Sharding-JDBC 源码分析 —— SQL 解析(四)之插入SQL

本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. InsertStatement 3. #parse() 3.1 #parseI...

3757
来自专栏企鹅号快讯

数据库中间件 Sharding-JDBC 源码分析——SQL 解析之插入SQL

1. 概述 本文前置阅读: 《SQL 解析(一)之词法解析》 《SQL 解析(二)之SQL解析》 本文分享插入SQL解析的源码实现。 不考虑 INSERT SE...

1875
来自专栏c#开发者

oracle 常用command

Lunatic 整理 1. 删除表的注意事项 在删除一个表中的全部数据时,须使用TRUNCATE TABLE 表名;因为用DROP TABLE,DE...

3213
来自专栏学习有记

T-SQL进阶:超越基础 Level 2:编写子查询

831
来自专栏Albert陈凯

2018-07-20 oracle优化:避免全表扫描

例如:在City-State-Zip列创建了三列复合索引,那么仅对State列限定条件不能使用这个索引,因为State不是索引的主列。

1414
来自专栏Linux运维学习之路

MySQL索引

索引管理 索引是什么? 索引就好比一本书的目录,它会让你更快的找到内容; 让获取的数据更有目的性,从而提高数据库检索数据的性能; 索引建立在表的列上(字段)。 ...

3145
来自专栏java达人

mysql left( right ) join使用on 与where 筛选的差异

有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。 可能只看着两个关键字看不出任...

2017
来自专栏java学习

数据库_mysq单表操作

1.1 排序 通过order by语句,可以将查询出的结果进行排序。放置在select语句的最后。 格式: SELECT * FROM 表名 ORDER ...

3165

扫码关注云+社区