使用MyBatis访问数据库_mybatis 访问数据库-优快云博客

本文链接：https://blog.youkuaiyun.com/sinat_33801679/article/details/89279800

使用的数据库是MySQL 5.7版本

1.先将myblog.sql脚本写入MySQL中

cmd终端输入导入代码，注意要选择默认编码utf-8，不然导入不进，还会丢失sql脚本内容

mysql -uroot -p123456 --default-character-set=utf8 mysql < D:\myblog.sql

# ************************************************************
# MySQL: 5.7
# Database: mysql
# Generation Time: 2019-04-13 11:24:33 +0000
# ************************************************************

# 这里引号有区别。一个是``反引号，一个是''引号
# Dump of table article
# ------------------------------------------------------------

DROP TABLE IF EXISTS `article`;

CREATE TABLE `article` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`title` varchar(32) DEFAULT NULL,
	`author` varchar(32) DEFAULT NULL,
	`content` text,
	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `article` WRITE;
/*!40000 ALTER TABLE `article` DISABLE KEYS */;

INSERT INTO `article` (`id`, `title`, `author`, `content`, `create_time`)
VALUES
	(1,'MyBatis 源码分析系列文章导读','coolblog','MyBatis 源码分析系列文章导读','2018-07-15 15:30:09'),
	(2,'HashMap 源码详细分析(JDK1.8)','coolblog','HashMap 源码详细分析(JDK1.8)','2018-01-18 15:29:13'),
	(3,'Java CAS 原理分析','coolblog','Java CAS 原理分析','2018-05-15 15:28:33'),
	(4,'Spring IOC 容器源码分析 - 获取单例 bean','coolblog','Spring IOC 容器源码分析 - 获取单例 bean','2018-06-01 00:00:00'),
	(5,'Spring IOC 容器源码分析 - 循环依赖的解决办法','coolblog','Spring IOC 容器源码分析 - 循环依赖的解决办法','2018-06-08 00:00:00'),
	(6,'Spring AOP 源码分析系列文章导读','coolblog','Spring AOP 源码分析系列文章导读','2018-06-17 00:00:00'),
	(7,'Spring AOP 源码分析 - 创建代理对象','coolblog','Spring AOP 源码分析 - 创建代理对象','2018-06-20 00:00:00'),
	(8,'Spring MVC 原理探秘 - 一个请求的旅行过程','coolblog','Spring MVC 原理探秘 - 一个请求的旅行过程','2018-06-29 00:00:00'),
	(9,'Spring MVC 原理探秘 - 容器的创建过程','coolblog','Spring MVC 原理探秘 - 容器的创建过程','2018-06-30 00:00:00'),
	(10,'Spring IOC 容器源码分析系列文章导读','coolblog','Spring IOC 容器源码分析系列文章导读','2018-05-30 00:00:00');

/*!40000 ALTER TABLE `article` ENABLE KEYS */;
UNLOCK TABLES;

2.建立jdbc访问MyBatis的配置文件jdbc.properties

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mysql?useUnicode=true&useSSL=false&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
jdbc.username = root
jdbc.password = 123456

因为我用的是MySql是5.7版本的，驱动使用mysql-connector-java-5.1.39.jar包中的com.mysql.jdbc.Driver

rewriteBatchedStatements=TRUE是属于MySql 的批量操作

注意这里编码utf8，因为数据库我们使用的utf-8

3.建立MyBatis配置文件

<?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="jdbc.properties"/>
	
	<typeAliases>
		<typeAlias alias="Article" type="icomac.chapter1.model.Article"/>
	</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="chapter1/mapper/ArticleMapper.xml"/>
	</mappers>
</configuration>

4.建立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="icomac.chapter1.dao.ArticleDao">
	<select id="findByAuthorAndCreateTime" resultType="Article">
	SELECT
		`id`, `title`, `author`, `content`, `create_time`
	FROM
		`article`
	WHERE
		`author` = #{author} AND `create_time` > #{createTime}
	</select>
</mapper>

注意反引号和引号的区别

5.建立ArticleDao接口

package icomac.chapter1.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import icomac.chapter1.model.Article;

/**
 * ArticleDao
 * 
 * @author icomac
 * @data 2019-04-12
 */
public interface ArticleDao {
	List<Article> findByAuthorAndCreateTime(@Param("author") String author, @Param("createTime") String createTime);
}

传入的参数要对应到SQL映射文件。

6.建立一个对象类，用来设置其属性

package icomac.chapter1.model;

import java.io.Serializable;
import java.sql.Date;
import org.apache.ibatis.annotations.Param;

/*
 * Article
 * 
 * @author icomac
 * @date 2019-04-12
 */
public class Article implements Serializable {
	
	private Integer id;
	
	private String title;
	
	private String author;
	
	private String content;
	
	private Date createTime;
	
	public Article() {
		
	}
	
	public Article(@Param("title") String title) {
		this.title = title;
	}
	
	public Article(@Param("id") Integer id, @Param("title") String title, @Param("content") String content) {
		this.id = id;
		this.title = title;
		this.content = content;
	}
	
	public Integer getId() {
		return id;
	}
	
	public void setId(Integer id) {
		this.id = id;
	}
	
	public String getTitle() {
		return title;
	}
	
	public void setTitle(String title) {
		this.title = title;
	}
	
	public String getAuthor() {
		return author;
	}
	
	public void setAuthor(String author) {
		this.author = author;
	}
	
	public String getContent() {
		return content;
	}
	
	public void setContent(String content) {
		this.content = content;
	}
	
	public Date getCreateTime() {
		return createTime;
	}
	
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	
	@Override
	public String toString() {
		return "Article{" +
			"id=" + id +
			", title='" + title + '\'' +
			", author='" + author + '\'' +
			", content='" + content + '\'' +
			", createTime='" + createTime +
			'}';
	}
}

7.测试类

package icomac.chapter1;

import java.io.IOException;
import java.io.InputStream;
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 org.junit.Before;
import org.junit.Test;

import icomac.chapter1.dao.ArticleDao;
import icomac.chapter1.model.Article;

/**
 * MyBatisTest
 * 
 * @author icomac
 * @date 2019-04-12
 */
public class MyBatisTest {
	
	private SqlSessionFactory sqlSessionFactory;
	
	@Before
	public void prepare() throws IOException{
		String resource = "chapter1/mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		inputStream.close();
	}
	
	@Test
	public void testMyBatis() throws IOException{
		SqlSession session = sqlSessionFactory.openSession();
		try {
			ArticleDao articleDao = session.getMapper(ArticleDao.class);
			List<Article> articles = articleDao.findByAuthorAndCreateTime("coolblog", "2018-1-18");
		} finally {
			// TODO: handle finally clause
			session.commit();
			session.close();
		}
	}
}

搜索作者为coolblog，时间在2018-1-18以后发的文章

具体代码可以查看github文件

测试结果