使用mybatis操作数据库
1.配置mybatis依赖和mysql依赖
<!-- MyBatis依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<!-- MySQL依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
2.mybatis配置文件及数据库配置
#检查MyBatis配置是否存在,一般命名为mybatis-config.xml
mybatis.check-config-location=true
#配置文件位置
mybatis.config-location=classpath:mybatis/mybatis-config.xml
#mapper xml位置
mybatis.mapper-locations=classpath*:mapper/*Mapper.xml
#日志级别
logging.level.com.com.example.demo.mapper.UserMapper=debug
#数据库配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456abc
3.创建mybatis全局配置文件mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- mybatis全局配置文件 -->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL Map Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 类型别名 -->
<typeAliases>
<typeAlias alias="Integer" type="java.lang.Integer" />
<typeAlias alias="Long" type="java.lang.Long" />
<typeAlias alias="HashMap" type="java.util.HashMap" />
<typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
<typeAlias alias="ArrayList" type="java.util.ArrayList" />
<typeAlias alias="LinkedList" type="java.util.LinkedList" />
<!--自行配置-->
<typeAlias alias="user" type="com.example.demo.pojo.User"/>
</typeAliases>
</configuration>
4.创建一个实体类User
@Alias(value = "user")
public class User {
private String id;
private String user_name;
private String user_password;
//相应的构造方法,注意不要让无参的被覆盖
//setter getter
}
5.基于xml的使用,创建UserMapper接口
基于注解也可以,用的时候再说
//这个注解很好用,1.不用再在mybatis-config.xml里写xml映射文件2.直接就可以有相应的对象实例使用了
//同时如果有多个mapper一个个加@Mapper比较麻烦,可以在启动类上加上@MapperScan(basePackages = "com.example.demo.dao")扫描此包下的所有mapper
@Mapper
public interface UserMapper {
User findUserByUsername(String username);
void updateUserByUsername(User user);
void deleteUserByUsername(String username);
void saveUser(User user);
List<User> getUserList();
}
6.创建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="com.example.demo.mapper.UserMapper">
<resultMap id="user" type="com.example.demo.pojo.User"/>
<parameterMap id="user" type="com.example.demo.pojo.User"/>
<select id="findUserByUsername" parameterType="String" resultMap="user">
SELECT * FROM user
WHERE user_name=#{1}
</select>
<update id="updateUserByUsername" parameterMap="user">
UPDATE USER SET USER_PASSWORD=#{user_password} WHERE USER_NAME=#{user_name}
</update>
<delete id="deleteUserByUsername" parameterType="String">
DELETE FROM USER WHERE USER_NAME=#{1}
</delete>
<!-- 使用alias自定义的parameterType-->
<insert id="saveUser" parameterType="user">
INSERT INTO USER (user_password,user_name) VALUES (#{user_password},#{user_name})
</insert>
<select id="getUserList" resultMap="user">
SELECT * FROM USER
</select>
</mapper>
7.创建controller运行测试
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
//http://localhost:8080/getUser?username=xiaoli2
@GetMapping("/getUser")
public String getUser(String username){
User user =userMapper.findUserByUsername(username);
return user!=null ? username+"的密码是:"+user.getUser_password():"不存在用户名为"+username+"的用户";
}
//http://localhost:8080/updateUser?username=xiaoli2&password=123
@GetMapping("/updateUser")
public String updateUser(String password,String username){
User user = new User(username,password);
userMapper.updateUserByUsername(user);
return "success!";
}
//http://localhost:8080/addUser?username=xiaoli2&password=123
@GetMapping("/addUser")
public String addUser(String username,String password){
User user = new User(username,password);
userMapper.saveUser(user);
return "success!";
}
//http://localhost:8080/deleteUser?username=xiaoli2
@GetMapping("/deleteUser")
public String deleteUser(String username){
userMapper.deleteUserByUsername(username);
return "success!";
}
//http://localhost:8080/getUserList
@GetMapping("/getUserList")
public List getUserList(){
return userMapper.getUserList();
}
}
使用Mybatis-Generator插件
1.在pom文件里加入Mybatis-Generator插件
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<executions>
<execution>
<id>mybatis-generator</id>
<phase>deploy</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<configuration>
<!-- Mybatis-Generator 工具配置文件的位置 -->
<configurationFile>src/main/resources/mybatis-generator/generatorConfig.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<classifier>exec</classifier>
</configuration>
</plugin>
</plugins>
</build>
2.创建Mybatis-Generator配置文件generatorConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<!-- 配置生成器 -->
<generatorConfiguration>
<!--执行generator插件生成文件的命令: call mvn mybatis-generator:generate -e -->
<!-- 引入配置文件 -->
<properties resource="application.properties"/>
<!--classPathEntry:数据库的JDBC驱动,换成你自己的驱动位置 可选 -->
<!--<classPathEntry location="D:\generator_mybatis\mysql-connector-java-5.1.24-bin.jar" /> -->
<!-- 一个数据库一个context -->
<!--defaultModelType="flat" 大数据字段,不分表 -->
<!-- MyBatis3Simple没有生成selective结尾的方法,设置为MyBatis3即可生成 -->
<context id="MysqlTables" targetRuntime="MyBatis3Simple" defaultModelType="flat">
<!-- 自动识别数据库关键字,默认false,如果设置为true,根据SqlReservedWords中定义的关键字列表;
一般保留默认值,遇到数据库关键字(Java关键字),使用columnOverride覆盖 -->
<property name="autoDelimitKeywords" value="true" />
<!-- 生成的Java文件的编码 -->
<property name="javaFileEncoding" value="utf-8" />
<!-- beginningDelimiter和endingDelimiter:指明数据库的用于标记数据库对象名的符号,比如ORACLE就是双引号,MYSQL默认是`反引号; -->
<property name="beginningDelimiter" value="`" />
<property name="endingDelimiter" value="`" />
<!-- 格式化java代码 -->
<property name="javaFormatter" value="org.mybatis.generator.api.dom.DefaultJavaFormatter"/>
<!-- 格式化XML代码 -->
<property name="xmlFormatter" value="org.mybatis.generator.api.dom.DefaultXmlFormatter"/>
<plugin type="org.mybatis.generator.plugins.SerializablePlugin" />
<plugin type="org.mybatis.generator.plugins.ToStringPlugin" />
<!-- 注释 -->
<commentGenerator >
<property name="suppressAllComments" value="false"/><!-- 是否取消注释 -->
<property name="suppressDate" value="true" /> <!-- 是否生成注释代时间戳-->
</commentGenerator>
<!-- jdbc连接 -->
<jdbcConnection driverClass="${spring.datasource.driver-class-name}" connectionURL="${spring.datasource.url}" userId="${spring.datasource.username}" password="${spring.datasource.password}" />
<!-- 类型转换 -->
<javaTypeResolver>
<!-- 是否使用bigDecimal, false可自动转化以下类型(Long, Integer, Short, etc.) -->
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 需要自行配置 ------------------------------------------------------------------------------------>
<!-- 生成实体类地址 -->
<javaModelGenerator targetPackage="com.springboot.entity" targetProject="${mybatis.project}" >
<property name="enableSubPackages" value="false"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- 生成mapxml文件 -->
<sqlMapGenerator targetPackage="mapper" targetProject="${mybatis.resources}" >
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<!-- 需要自行配置 ------------------------------------------------------------------------------------>
<!-- 生成mapxml对应client,也就是接口dao -->
<javaClientGenerator targetPackage="com.springboot.dao" targetProject="${mybatis.project}" type="XMLMAPPER" >
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!-- table可以有多个,每个数据库中的表都可以写一个table,tableName表示要匹配的数据库表,也可以在tableName属性中通过使用%通配符来匹配所有数据库表,只有匹配的表才会自动生成文件 -->
<table tableName="user" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true">
<property name="useActualColumnNames" value="false" />
<!-- 数据库表主键 -->
<generatedKey column="id" sqlStatement="Mysql" identity="true" />
</table>
</context>
</generatorConfiguration>
3.Mybatis-Generator项目配置文件(项目配置文件就是写在默认配置文件里的)
#mapper xml位置
mybatis.mapper-locations=classpath*:mapper/*Mapper.xml
#数据库配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456abc
#mybatis generator配置
#dao类(或mapper类)和实体类位置
mybatis.project =src/main/java
#mapper文件位置
mybatis.resources=src/main/resources
4.运行生成
右键项目run as—>maven build:goal里输入mybatis-generator:generate -e
PageHelper插件
1.引入PageHelper插件依赖
<!-- pagehelper插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
2.在项目配置文件中配置
#pagehelper分页插件配置
pagehelper.helper-dialect=mysql
3.编写UserMapper类,加入方法
@Select("select * from user")
List<User> getUserListPage();
4.编写Controller
//pageNum为页码,pageSize为每页的数量
//http://localhost:8080/getUserListPage?pageNum=1&pageSize=2
@GetMapping("/getUserListPage")
public List<User> getUserListPage(Integer pageNum,Integer pageSize){
PageHelper.startPage(pageNum, pageSize);
return userMapper.getUserListPage();
}
是不是非常简单,测试成功。
MyBatis-Plus插件
1.引入相关依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatisplus-spring-boot-starter</artifactId>
<version>1.0.5</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>2.3</version>
</dependency>
2.配置项目配置文件
#mybatis-plus mapper xml文件地址
mybatis-plus.mapper-locations=classpath*:mapper/*Mapper.xml
#mybatis-plus type-aliases文件地址
mybatis-plus.type-aliases-package=com.example.demo.pojo
3.MyBatis-Plus配置
@Configuration
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor page=new PaginationInterceptor();
//设置方言类型
page.setDialectType("mysql");
return page;
}
}
4.dao层需要继承MyBatis-Plus提供的BaseMapper
@Mapper
public interface UserMapper extends BaseMapper<User>{
List<User> getUserList();
}
5.在mapper.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="com.example.demo.mapper.UserMapper">
<resultMap id="user" type="com.example.demo.pojo.User"/>
<parameterMap id="user" type="com.example.demo.pojo.User"/>
<select id="getUserList" resultMap="user">
SELECT * FROM USER
</select>
</mapper>
6.在写controller进行测试
MyBatis-Plus应该还有很多要学的东西,未完待续!