建立连接可能涉及到的问题(只需要自己改一下就行)
1、MyBatis是⼀款优秀的 持久层 框架,⽤于简化JDBC的开发
2、数据库连接配置
(1)yml配置
# 数据库连接配置
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/mybatis_test?
characterEncoding=utf8&useSSL=false
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
(2)properties配置
#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis_test?
characterEncoding=utf8&useSSL=false
#连接数据库的⽤⼾名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=root
3、数据库建表代码
-- 创建数据库
DROP DATABASE IF EXISTS mybatis_test;
CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
-- 使⽤数据数据
USE mybatis_test;
-- 创建表[⽤⼾表]
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`username` VARCHAR ( 127 ) NOT NULL,
`password` VARCHAR ( 127 ) NOT NULL,
`age` TINYINT ( 4 ) NOT NULL,
`gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-⼥ 0-默认',
`phone` VARCHAR ( 15 ) DEFAULT NULL,
`delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
`create_time` DATETIME DEFAULT now(),
`update_time` DATETIME DEFAULT now(),
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
-- 添加⽤⼾信息
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );
4、需要引入的pom文件依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.1</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>4.0.0</version>
</dependency>
5、数据库查询代码
(1)接口
package com.example.demo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface UserInfoMapper {
//访问数据库数据
@Select("select * from userinfo")
List<UserInfo> queryUserList();
}
(2)定义对象
package com.example.demo;
import lombok.Data;
import java.util.Date;
@Data
public class UserInfo {
//数据库里面有什么我们这里就定义什么
private Integer id;
private String username;
private String password;
private Integer age;
private Integer gender;
private String phone;
private Integer deleteFlag;
private Date createTime;
private Date updateTime;
}
(3)测试类
package com.example.demo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class DemoApplicationTests {
@Autowired
private UserInfoMapper userInfoMapper;
@Test
void contextLoads() {
System.out.println(userInfoMapper.queryUserList());
}
}
6、数据删除方式有两种
(1)逻辑删除:update(推荐)
(2)物理删除:delete
7、在Mybatis当中我们可以借助⽇志, 查看到sql语句的执⾏、执⾏传递的参数以及执⾏结果在配置⽂件中进⾏配置
mybatis:
configuration: # 配置打印 MyBatis⽇志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
8、生成测试(测试类上面要加@SpringBootTest)
9、单参查寻(只有一个参数时#后面的名称无所谓随便定义)
(1)接口部分代码
@Select("select * from userinfo where id = #{userId}")
UserInfo queryUserInfo(Integer userId);
(2)测试部分代码
void queryUserInfo() {
log.info(userInfoMapper.queryUserInfo(1).toString());
}
10、多参数时
(1)接口部分代码(idea上面创建的代码)阿里云创建的不能应用此方法
@Select("select * from userinfo where id = #{userId} and delete_flag = #{deleteFlag}")
UserInfo queryUserInfo( Integer userId, Integer deleteFlag);
(1)接口部分代码(阿里云创建的项目代码)
@Select("select * from userinfo where id = #{param1} and delete_flag = #{param2}")
UserInfo queryUserInfo(Integer id, Integer deleteFlag);
(2)测试部分代码
void testQueryUserInfo() {
log.info(userInfoMapper.queryUserInfo(1,0).toString());
}
11、参数重命名(重命名之后不能用之前的)
(1)接口部分代码
@Select("select * from userinfo where id = #{id} and delete_flag = #{param2}")
UserInfo queryUserInfo(@Param("id") Integer id, Integer deleteFlag);
(2)测试部分代码
void testQueryUserInfo() {
log.info(userInfoMapper.queryUserInfo(1,0).toString());
}
12、web调用查询代码(三层架构模式)
(1)Controller代码
package com.example.demo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/queryAllUser")
public List<UserInfo> queryAllUser(){
return userService.queryAllUser();
}
}
(2)Service代码
package com.example.demo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserInfoMapper userInfoMapper;
public List<UserInfo> queryAllUser(){
return userInfoMapper.queryUserList();
}
}
(3)Mapper代码
package com.example.demo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface UserInfoMapper {
//访问数据库数据
@Select("select * from userinfo")
List<UserInfo> queryUserList();
}
(4)查询结果
13、插入数据
(1)接口部分代码
@Select("insert into userinfo (username,password,age,gender,phone) values(#{username},#{password},#{age},#{gender},#{phone})")
Integer insert(UserInfo userInfo);
(2)测试部分代码
@Test
void insert() {
UserInfo userInfo=new UserInfo();
userInfo.setUsername("zhangsan");
userInfo.setPassword("123456");
userInfo.setAge(18);
userInfo.setGender(0);
userInfo.setPhone("18330322");
userInfoMapper.insert(userInfo);
}
(3)重命名的方式
@Select("insert into userinfo (username,password,age,gender,phone) " +
"values(#{userInfo.username},#{userInfo.password},#{userInfo.age},#{userInfo.gender},#{userInfo.phone})")
Integer insert(@Param("userInfo") UserInfo userInfo);
(4)获得自增的数据,比如我们买东西我们可以看我们买了多少东西,这样有个数
(4.1)接口部分代码
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("insert into userinfo (username,password,age,gender,phone) " +
"values(#{username},#{password},#{age},#{gender},#{phone})")
Integer insert(UserInfo userInfo);
(4.2)测试部分代码
@Test
void insert() {
UserInfo userInfo=new UserInfo();
userInfo.setUsername("zhangsan");
userInfo.setPassword("123456");
userInfo.setAge(18);
userInfo.setGender(0);
userInfo.setPhone("18330322");
Integer result= userInfoMapper.insert(userInfo);
log.info("插入了几条数据"+result+"自增Id是"+userInfo.getId());
}
14、删除数据
(1)接口部分代码
@Delete("delete from userinfo where id=#{id}")
Integer delete(@Param("id") Integer id);
(2)测试部分代码
@Test
void delete() {
userInfoMapper.delete(11);
}
15、更改数据
(1)接口部分代码(idea创建的项目)
@Update("update userinfo set password=#{password} where id=#{id}")
Integer update(String password,Integer id);
(1)接口部分代码(阿里云创建的项目)
@Update("update userinfo set password=#{param1} where id=#{param2}")
Integer update(String password,Integer id);
(2)测试部分代码
@Test
void update() {
userInfoMapper.update("777777",12);
}
15.1、放在对象里更新
(1)接口部分代码
@Update("update userinfo set username=#{username},password=#{password},age=#{age} where id=#{id}")
Integer update(UserInfo userInfo);
(2)测试部分代码
@Test
void update() {
UserInfo userInfo=new UserInfo();
userInfo.setUsername("lisi");
userInfo.setPassword("987654");
userInfo.setAge(10);
userInfo.setId(12);
userInfoMapper.update(userInfo);
}
16、mybatis赋值失败原因
(1)解决办法
(1.1)改别名 用as的方法
@Select("select id,username,password,age,phone," +
"delete_flag as deleteFlag,create_time as createTime,update_time as updateTime from userinfo")
List<UserInfo> queryUserList();
(1.2)注解方式
@Results({
@Result(column = "delete_flag",property = "deleteFlag"),
@Result(column = "create_time",property = "createTime"),
})
@Select("select * from userinfo")
List<UserInfo> queryUserList();
(1.3)1.2的@Results的复用
@Results(id = "base",value = {
@Result(column = "delete_flag",property = "deleteFlag"),
@Result(column = "create_time",property = "createTime"),
})
@Select("select * from userinfo")
List<UserInfo> queryUserList();
@ResultMap(value = "base")
@Select("select id,username,password,age,phone,delete_flag ,create_time,update_time from userinfo")
UserInfo queryUserInfo( Integer userId, Integer deleteFlag);
♥(1.4)开启驼峰命名(推荐)
直接配置yml文件
mybatis:
configuration:
map-underscore-to-camel-case: true
标签:JAVA,入门,userInfo,import,MyBatis,Integer,password,id,userinfo
From: https://blog.csdn.net/m0_69134284/article/details/137203120