Springboot学习笔记（二）

1. 集成数据库

1.1增加依赖

在原有的springboot的基础上，增加下面的依赖:

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.11</version>    
    <scope>runtime</scope>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.6</version>
</dependency>
<!-- jdbcTemplate -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

1.2增加数据库配置

修改application.yml，其中如果是mysql8的版本，驱动和url要做相应的修改

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/db_user
    username: root
    password: 1234
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

（password记得写自己mysql的密码）
如果用的是mysql8，那么配置则是配置：

驱动：com.mysql.cj.jdbc.Driver

url : jdbc:mysql://localhost:3306/db_user?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false

1.3创建表和增加数据

例子：

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `ctm` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `tb_user` VALUES ('1', '张三', '18', '2019-01-24 09:07:41');
INSERT INTO `tb_user` VALUES ('2', '李四', '20', '2019-01-24 09:07:41');
INSERT INTO `tb_user` VALUES ('3', '王五', '19', '2019-01-24 09:07:41');

创建成功的数据：

1.4添加实体类

在pojo实体类中添加User，代码如下：

public class User {
    private int id;
    private String username;
    private int age;
    private Date ctm;
    public String getUsername() {
        return username;
    }
    public int getAge() {
        return age;
    }
    public Date getCtm() {
        return ctm;
    }
    public void setId(int id) {
        this.id = id;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public void setCtm(Date ctm) {
        this.ctm = ctm;
    }
    public User(int id, String username, int age, Date ctm) {
        this.id = id;
        this.username = username;
        this.age = age;
        this.ctm = ctm;
    }
}

1.5创建Dao接口

public interface UserDao {
    User getUserById(Integer id);
    public List<User> getUserList();
    public int add(User user);
    public int update(Integer id, User user);
    public int delete(Integer id);
}

1.6增加接口实现类

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
 
import java.util.Date;
import java.util.List;
 
@Repository
public class UserDaoImpl implements UserDao {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Override
    public User getUserById(Integer id) {
        List<User> list = jdbcTemplate.query("select * from tb_user where id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));
        if(list!=null && list.size()>0){
            return list.get(0);
        }else{
            return null;
        }
    }
 
    @Override
    public List<User> getUserList() {
        List<User> list = jdbcTemplate.query("select * from tb_user", new Object[]{}, new BeanPropertyRowMapper(User.class));
        if(list!=null && list.size()>0){
            return list;
        }else{
            return null;
        }
    }
 
 
    @Override
    public int add(User user) {
        return jdbcTemplate.update("insert into tb_user(username, age, ctm) values(?, ?, ?)",
                user.getUsername(),user.getAge(), new Date());
    }
 
    @Override
    public int update(Integer id, User user) {
        return jdbcTemplate.update("UPDATE tb_user SET username = ? , age = ? WHERE id=?",
                user.getUsername(),user.getAge(), id);
    }
 
    @Override
    public int delete(Integer id) {
        return jdbcTemplate.update("DELETE from tb_user where id = ? ",id);
    }
 
}

1.7测试

1.7.1 修改controller，增加getUserById()方法和成员变量userDao

public class UserController {
    @Autowired
    UserDao userDao;//由springboot自动注入dao对象给contrroller

    //测试访问数据获取对象
    @RequestMapping("/getUserById")
    public User getUserById(int id){
        return userDao.getUserById(id);
    }

1.7.2 访问url获取用户信息

访问url：http://127.0.0.1:8080/user/getUserById?id=3

2.集成Mybatis

2.1 导入依赖

在pom.xml文件上加上：

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

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

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

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

        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.37</version>
        </dependency>

        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.16</version>
        </dependency>
		<!-- jpa依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

2.2 配置文件

2.2.1在resources目录下建立mybatis-config.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>

    <!--需要内容自己添加，可以什么都不写-->
    <settings>
        <!--开启驼峰命名匹配规则-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

</configuration>

2.2.2application.properties

server.port=8080
server.servlet.context-path=/
#
#数据源
spring.datasource.url=jdbc:mysql://localhost:3306/travel?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.jdbc.Driver
#
#mybatis
mybatis.config-location=classpath:mybatis-config.xml
mybatis.checkConfigLocation=true
# mybatis 别名扫描
mybatis.type-aliases-package=edu.ln.travel.pojo
# mapper.xml文件全部放在resources/mappers目录中
mybatis.mapper-locations=classpath:mappers/*.xml

（ps：如果是mysql8，
driver-class-name= com.mysql.cj.jdbc.Driver
url= jdbc:mysql://localhost:3306/travel?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false）

2.2.3 mapper层加入@Mapper

@Mapper
public interface UserDao {

    public List<User> findAll();

}

2.2.4 pojo

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer uid;
    private String name;
    private Integer telephone;
}

2.2.5 resources/mappers/UserMapper.xml的配置

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
    <mapper namespace="edu.ln.travel.dao.UserDao">

        <select id="findAll" resultType="User">
            select * from tab_user
        </select>
    </mapper>

2.2.6 测试集成mybatis

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MybatisApplication.class)
class MybatisApplicationTest {
    @Autowired
    UserDao userDao;

    @Test
    public void test1(){
        List<User> users = userDao.findAll();
        System.out.println(users);
    }

    @Test
    public void test2(){
        User user = userDao.findById(4);
        System.out.println(user);
    }
}