springboot（三）：连接mysql数据库

操作以下步骤时，请先在数据库中创建好需要访问的数据库。

1、在pom.xml添加mysql的jdbc的依赖

	<!-- 2、mysql,jdbc依赖 -->
	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	</dependency>
	<dependency>
	    <groupId>org.springframework.boot</groupId>
	    <artifactId>spring-boot-starter-jdbc</artifactId>
	</dependency>

2、在application.properties的配置文件中添加数据库的配置信息

#datasource
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.testOnBorrow = true
spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.minEvictableIdleTimeMillis = 30000
spring.datasource.validationQuery = SELECT 1
spring.datasource.max-active = 15
spring.datasource.max-idle = 10
spring.datasource.max-wait = 8000

3、编写实体

package com.wedu.springboot.entity;

public class Student {

	private Integer id;
	private String mobile;
	private String userName;

	public Student() {
		super();
	}

	public Student(String mobile, String userName) {
		super();
		this.mobile = mobile;
		this.userName = userName;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getMobile() {
		return mobile;
	}

	public void setMobile(String mobile) {
		this.mobile = mobile;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", mobile=" + mobile + ", userName=" + userName + "]";
	}
}

4、编写dao

编写数据访问层接口

package com.wedu.springboot.dao;

import java.util.List;

import com.wedu.springboot.entity.Student;

public interface StudentDao {

	List<Student> findAll();

	Student getById(Integer id);
}

使用JdbcTemplate编写数据访问层实现

package com.wedu.springboot.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.wedu.springboot.entity.Student;

@Repository
public class StudentDaoImpl implements StudentDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<Student> findAll() {
		RowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);
		return jdbcTemplate.query("select * from student", rowMapper);
	}

	@Override
	public Student getById(Integer id) {
		RowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);
		Student student = jdbcTemplate.queryForObject("select * from student where id = ?", rowMapper,id);
		return student;
	}
}

5、编写service

编写业务层接口

package com.wedu.springboot.service;

import java.util.List;

import com.wedu.springboot.entity.Student;

public interface StudentService {

	List<Student> findAll();

	Student getById(Integer id);
}

编写业务层实现

package com.wedu.springboot.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.wedu.springboot.dao.StudentDao;
import com.wedu.springboot.entity.Student;

@Service
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentDao studentDao;
	
	@Override
	public List<Student> findAll() {
		return studentDao.findAll();
	}

	@Override
	public Student getById(Integer id) {
		return studentDao.getById(id);
	}
}

6、编写controller

package com.wedu.springboot.web;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.wedu.springboot.entity.Student;
import com.wedu.springboot.service.StudentService;

@RestController
public class StudentController {

	@Autowired
	private StudentService studentService;
	
	@RequestMapping("/findAll")
	public List<Student> findAll(){
		List<Student> student = studentService.findAll();
		for (Student s : student) {
			System.out.println(s.toString());
		}
		return student;
	}
	
	@RequestMapping("/getById")
	public Student get(Integer id){
		Student student = studentService.getById(id);
		System.out.println(student.toString());
		return student;
	}
}

7、编写springboot启动类

package com.wedu.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App {
	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}
}

8、运行启动类，并在浏览器的地址栏输入http://localhost:8080/findAll