Mybatis系列（三）XML
1.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">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example.mapper</groupId>
<artifactId>springboot_all</artifactId>
<version>1.0</version>
<packaging>jar</packaging>
<name>springboot_all</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.10.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--spring boot aop切面-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!--guava缓存cache-->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>23.5-jre</version>
</dependency>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!--mysql依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>
<!--user对象注解@Id-->
<dependency>
<groupId>javax.persistence</groupId>
<artifactId>persistence-api</artifactId>
<version>1.0.2</version>
</dependency>
<!--分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.0.0</version>
</dependency>
<!--alibaba支持json转换-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.39</version>
</dependency>
<!--支持redis的jedis操作-->
<dependency>
<groupId>redis.clients</groupId>
<artifactId>jedis</artifactId>
<version>2.9.0</version>
</dependency>
<!--ehcache缓存-->
<dependency>
<groupId>net.sf.ehcache</groupId>
<artifactId>ehcache</artifactId>
<version>2.10.2</version>
</dependency>
<!--<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-cache</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-jcs-jcache</artifactId>
<version>2.0</version>
</dependency>-->
<!--http依赖1-->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
</dependency>
<!--http依赖2-->
<dependency>
<groupId>commons-httpclient</groupId>
<artifactId>commons-httpclient</artifactId>
<version>3.1</version>
</dependency>
<!--读取文件配置依赖-->
<dependency>
<groupId>commons-configuration</groupId>
<artifactId>commons-configuration</artifactId>
<version>1.10</version>
</dependency>
<!--RabbiMQ依赖-->
<dependency>
<groupId>com.rabbitmq</groupId>
<artifactId>amqp-client</artifactId>
<version>3.5.6</version>
</dependency>
<!--RedisTemplate依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<!--springboot监控依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>conf</directory>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>../../conf/*.properties</include>
<include>../../conf/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>
2.applications.properties文件：
#mysql数据库配置
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
3.mybatis-config-local.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="application.properties"></properties>
<!--启用下划线与驼峰式命名规则的映射-->
<settings>
<setting name="logImpl" value="LOG4J"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--类型进行别名配置-->
<typeAliases>
<typeAlias alias="User" type="com.example.mapper.mybatisMap.entity.User"/>
<package name="com.example.mapper.mybatisMap.entity" />
</typeAliases>
<!--通过javaType处理枚举类型-->
<typeHandlers>
<typeHandler
javaType="com.example.mapper.mybatisMap.entity.Enabled"
handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" />
</typeHandlers>
<!-- 配置分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->
<property name="helperDialect" value="mysql"/>
</plugin>
</plugins>
<!-- 配置mybatis运行环境 -->
<environments default="master">
<environment id="master">
<!-- type="JDBC" 代表使用JDBC的提交和回滚来管理事务 -->
<transactionManager type="JDBC" />
<!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI -->
<!-- POOLED 表示支持JDBC数据源连接池 -->
<!-- UNPOOLED 表示不支持数据源连接池 -->
<!-- JNDI 表示支持外部数据源连接池 -->
<dataSource type="POOLED">
<property name="driver" value="${spring.datasource.driver-class-name}" />
<property name="url" value="${spring.datasource.url}" />
<property name="username" value="${spring.datasource.username}" />
<property name="password" value="${spring.datasource.password}" />
</dataSource>
</environment>
<environment id="slave">
<!-- type="JDBC" 代表使用JDBC的提交和回滚来管理事务 -->
<transactionManager type="JDBC" />
<!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI -->
<!-- POOLED 表示支持JDBC数据源连接池 -->
<!-- UNPOOLED 表示不支持数据源连接池 -->
<!-- JNDI 表示支持外部数据源连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/user" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="dao/UserDaoMapper.xml"/>
<mapper resource="dao/OrdersMapperCustom.xml"/>
</mappers>
</configuration>
4.springboot启动项数据库初始化：
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Timer;
import java.util.concurrent.TimeoutException;
@SpringBootApplication
public class SpringBootAll {
private static String env = "local";
public static SqlSessionFactory sqlSessionFactory;
public static void main(String[] args) throws Exception{
new SpringBootAll().init();
SpringApplication.run(SpringBootAll.class, args);
}
public void init() throws Exception{
//初始化数据库-单个数据库
initMySql();
}
/**
* 单个数据库
*/
private void initMySql(){
try{
String resource = String.format("mybatis-config-%s.xml", SpringBootAll.env);
Reader reader = Resources.getResourceAsReader(resource);
SpringBootAll.sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
}
5.建立对象User:
public class User {
private int id;
private int age;
private String name;
private String role;
private String email;
private String phone;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRole() {
return role;
}
public void setRole(String role) {
this.role = role;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", age=" + age +
", name='" + name + '\'' +
", role='" + role + '\'' +
", email='" + email + '\'' +
", phone='" + phone + '\'' +
'}';
}
}
6.编写接口UserDao：
package com.example.mapper.mybatisMap.dao;
import com.example.mapper.mybatisMap.entity.User;
import org.apache.ibatis.annotations.Param;
import java.util.ArrayList;
import java.util.List;
/**
* @author: yiqq
* @date: 2018/7/20
* @description:
*/
public interface UserDao {
ArrayList<User> findInfoList(@Param("name") String name, @Param("age") int age);
Integer findCount(@Param("name") String name, @Param("age") int age);
String findName(User user);
boolean insert(User user);
List<User> selectall();
}
7.编写实现类UserDaoIml:
package com.example.mapper.mybatisMap.dao;
import com.example.mapper.mybatisMap.entity.OOO;
import com.example.mapper.mybatisMap.entity.Student;
import com.example.mapper.mybatisMap.entity.User;
import com.example.mapper.mybatisMap.entity.User_c;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* @author: yiqq
* @date: 2018/7/20
* @description:
*/
public class UserDaoIml implements UserDao{
private Logger logger = LoggerFactory.getLogger(getClass());
private SqlSessionFactory sessionFactory;
public UserDaoIml(SqlSessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
}
public ArrayList<User> findInfoList(@Param("name") String name, @Param("age") int age) {
SqlSession sqlSession = null;
ArrayList<User> users = new ArrayList();
try {
sqlSession = sessionFactory.openSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
users = mapper.findInfoList(name, age);
return users;
} catch (Exception e) {
e.printStackTrace();
return users;
}
}
public Integer findCount(@Param("name") String name, @Param("age") int age) {
SqlSession sqlSession = null;
int users =0;
try {
sqlSession = sessionFactory.openSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
users = mapper.findCount(name, age);
return users;
} catch (Exception e) {
e.printStackTrace();
return users;
}
}
public String findName(User user) {
SqlSession sqlSession = null;
String nameR =null;
try {
sqlSession = sessionFactory.openSession();
nameR = sqlSession.selectOne("com.example.mapper.mybatisMap.dao.UserDao.findName", user);
return nameR;
} catch (Exception e) {
e.printStackTrace();
return nameR;
}
}
public boolean insert(User user) {
SqlSession sqlSession = null;
boolean res =false;
try {
sqlSession = sessionFactory.openSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
res = mapper.insert(user);
if( res == true) {
sqlSession.commit();
res = true;
} else {
sqlSession.rollback();
}
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
public List<User> selectall() {
SqlSession sqlSession = null;
List<User> res =new ArrayList<>();
try {
sqlSession = sessionFactory.openSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
res = mapper.selectall();
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
}
8.编写UserDaoMapper:
<?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">
<!-- namespace：命名空间，对statement的信息进行分类管理 -->
<!-- 注意：在mapper代理时，它具有特殊及重要的作用 -->
<mapper namespace="com.example.mapper.mybatisMap.dao.UserDao">
<resultMap id="UserInfo" type="com.example.mapper.mybatisMap.entity.User">
<id column="id" property="id"/>
<result column="age" property="age"/>
<result column="name" property="name"/>
<result column="role" property="role"/>
<result column="email" property="email"/>
<result column="phone" property="phone"/>
</resultMap>
<!--查询-->
<select id="findInfoList" resultType="com.example.mapper.mybatisMap.entity.User">
SELECT id, age ,name, role, email,phone
FROM
user
WHERE
age =#{age}
and name =#{name}
</select>
<select id="findCount" resultType = "java.lang.Integer">
SELECT COUNT(*)
FROM
user
WHERE
age =#{age}
and name =#{name}
</select>
<select id="findName" resultType = "java.lang.String">
SELECT name
FROM
user
WHERE
age =#{age}
and name =#{name}
</select>
<insert id="insert" >
INSERT INTO USER (age, name, role, email, phone) VALUES (#{age}, #{name}, #{role}, #{email}, #{phone})
</insert>
<select id="selectall" resultMap="UserInfo">
select * from user
</select>
</mapper>
9.在controller中调用实现的mybatis方法：
package com.example.mapper.mybatisMap.controller;
import com.example.mapper.mybatisMap.SpringBootAll;
import com.example.mapper.mybatisMap.constant.ZKConst;
import com.example.mapper.mybatisMap.dao.UserDao;
import com.example.mapper.mybatisMap.dao.UserDaoIml;
import com.example.mapper.mybatisMap.entity.User;
import com.example.mapper.mybatisMap.tool.LocalLock;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import static com.example.mapper.mybatisMap.tool.CommonObject.*;
@RestController
public class HelloController {
private Logger logger = LoggerFactory.getLogger(getClass());
UserDao userDao = new UserDaoIml(SpringBootAll.sqlSessionFactory);
/**
* hello world 第一个程序
* @return
*/
@RequestMapping("/hello")
public String hello(){
return "Hello World!";
}
/**
* 分页插件,加锁
*/
@RequestMapping(value = "/selectallpage",produces = "application/json", method = RequestMethod.GET)
@ResponseBody
public List<User> SelectAllPage(HttpServletRequest request
, HttpServletResponse response
,@RequestParam(value = "start",defaultValue = "1") int start
, @RequestParam(value = "end",defaultValue = "8") int end) {
// 获取session，同一次运行的session都是一样的
String sessionId = request.getSession().getId();
logger.debug("hhhhhhhhhhhh");
logger.info("1111111111");
// 获取锁
LocalLock lock = new LocalLock(sessionId, String.format(ZKConst.SCORE_LOCK_NODE, 102, 123, 234));
// 加锁
if(!lock.writeLock(ZKConst.LOCK_EXPIRE_TIME)){
//log.warn("加锁失败！");
return null;
}
PageHelper.startPage(start, end);
List<User> userList = userDao.selectall();
PageInfo<User> pageInfo = new PageInfo<>(userList);
// 释放锁
lock.unWriteLock();
return pageInfo.getList();
}
/**
* 返回对象
*/
@RequestMapping(value = "/goods", method = RequestMethod.GET)
@ResponseBody
public ArrayList<User> GetGoodList(@RequestParam("name") String name, @RequestParam("age") int age) throws Exception{
ArrayList<User> user = userDao.findInfoList(name, age);
return user ;
}
/**
* 返回对象，可不传参数，使用默认参数
*/
@RequestMapping(value = "/goodsCount", method = RequestMethod.GET)
@ResponseBody
public ArrayList<User> GetGoodCount(@RequestParam(value = "name",defaultValue = "易水寒") String name
, @RequestParam(value = "age", defaultValue = "27") int age) throws Exception{
ArrayList<User> user = userDao.findInfoList(name, age);
return user ;
}
/**
* 查询所有User
* @return
*/
@RequestMapping(value = "/selectall", method = RequestMethod.GET)
@ResponseBody
public List<User> SelectAll() {
return userDao.selectall();
}
/**
* mapper方式实现mybatis查询数据库
* sqlSession.selectOne方式
*/
@RequestMapping(value = "/selectone", method = RequestMethod.GET)
@ResponseBody
public String GetSelectOne(@RequestParam(value = "name", defaultValue = "文彪承") String name
, @RequestParam(value = "age", defaultValue = "35") int age) throws Exception{
User user = new User();
user.setAge(age);
user.setName(name);
String names = userDao.findName(user);
return names ;
}
/**
* insert主键自增长
* 写入数据库
*/
@RequestMapping(value = "/insertC", method = RequestMethod.POST)
@ResponseBody
public String insert() {
User user = new User();
//随机年龄
Random rand = new Random();
int age = rand.nextInt(20) + 20;
user.setAge(age);
user.setName(getChineseName());
user.setRole(getRoad());
user.setEmail(getEmail(6,9));
user.setPhone(getTel());
boolean res = userDao.insert(user);
if(res == true) {
return "主键自增长插入成功";
}else {
return "主键自增长插入失败";
}
}
}
10.sql:
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`age` int(10) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`role` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`phone` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8