Mybatis系列(三)XML
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
程序员的眼里,不止有代码和bug,还有诗与远方和妹子!!!