数据库使用

一、MySql

引入包

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
       <version>8.0.26</version>
</dependency>
<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
       <version>2.2.0</version>
</dependency>

application.yml配置

spring.datasource.url=jdbc:mysql://localhost:3306/test_spring?characterEncoding=utf8&useSSL=true
spring.datasource.username=root
# spring.datasource.password=1234
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

二、SpringBoot操作Mysql

0、准备工作

建表，初始数据

create table user_test(
 id int not null,
 name varchar(20),
 sex int not null,
 cityid int not null
);
-- 放两条数据
insert into user_test(id,name,sex,cityid) values(1,'cimon',1,1);
insert into user_test(id,name,sex,cityid) values(2,'crb',1,122);

model类或者POJO类

import lombok.Data;
@Data
public class User {
    private int id;
    private String name;
    private int sex;
    private int cityid;
}

1、JdbcTemplate

参考：SpringBoot 使用jdbcTemplate操作数据库

<dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

import com.cimon.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserDaoJdbcTemplateImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public Long insertUser(User user) {
        String sql="insert into user_test(id,name,sex,cityid) values(:id,:name,:sex,:cityid)";
        Map<String,Object> param = new HashMap();
        return null;
    }
    public User getUserByName(String name){
        String sql="select id,name,sex,cityid from user_test where name=?";
        User user = this.jdbcTemplate.queryForObject(sql, new Object[]{name}, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User u = new User();
                u.setId(resultSet.getInt("id"));
                u.setCityid(resultSet.getInt("cityid"));
                u.setName(resultSet.getString("name"));
                u.setSex(resultSet.getInt("sex"));
                return u;
            }
        });
        return user;
    }
    public List<User> getUserByNameList(String name){
        return null;
    }
}

测试代码：

import com.cimon.dao.UserDao;
import com.cimon.model.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@SpringBootTest
@RunWith(SpringRunner.class)
public class UserDaoJdbcTemplateImplTest {
    @Autowired
    private UserDao userDao;
    @Test
    public void test(){
        User user = userDao.getUserByName("crb");
        System.out.println(user.toString());
    }
}

2、JPA

<dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

省略，见参考的博客

3、MyBatis

<dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.2.0</version>
</dependency>

注意：没有spring前缀

#mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
mybatis.type-aliases-package=com.cimon.dao

package com.cimon.mapper;
import com.cimon.model.User;
public interface UserMapper {
//    Long insertUser(User user);
    User getUserByName(String name);
}

package com.cimon;
import com.cimon.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@SpringBootTest
@RunWith(SpringRunner.class)
@MapperScan("com.cimon.mapper")
public class MyBatisMapperTest {
    @Autowired
    private UserMapper userMapper;
    @Test
    public void test(){
        System.out.println(userMapper.getUserByName("cimon"));
    }
}

<?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.cimon.mapper.UserMapper">
    <resultMap id="userMap" type="com.cimon.model.User">
        <result column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="sex" property="sex" jdbcType="INTEGER"/>
        <result column="cityid" property="cityid" jdbcType="INTEGER"/>
    </resultMap>
    <sql id="base_column">
        id,name,sex,cityid
    </sql>
    <select id="getUserByName" resultMap="userMap">
        select
         <include refid="base_column"/>
        from user_test
        where name=#{name}
    </select>
<!--    <insert id="insertUser" parameterType="com.cimon.model.User">-->
<!--        insert <include refid="base_column"/>-->
<!--        values-->
<!--        (#{d},#{name},#{sex},#{cityid})-->
<!--    </insert>-->
</mapper>

mapper文件名要和mapper类名一致

参考

1、Spring Boot实战：数据库操作- 3种方法
2、SpringBoot 使用jdbcTemplate操作数据库
3、元素类型 “mapper“ 声明属性 “namespace“解决
4、mybatis XML 映射配置
5、springboot中使用mybatis
6、mybatis的两种使用方式
7、mybatis标签
8、MyBatis中的resultType