springboot druid+mysql、使用JAP

这一节讲述springboot 连接Mysql 数据库，并使用JPA 进行CRUD操作

一：Springboot 集成druid+mysql，取得数据源

准备概述： 新建一个Maven java 项目,注意：是java 项目，不是Webapp 项目,以后的章节将通通采用Springboot 的官网推荐，尽可能避免使用jsp、Servlet

01 pom.xml 导入依赖：springboot、热部署、模板引擎：

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.10.RELEASE</version>
</parent>

<dependencies>
    <!--springboot-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!--springboot 热部署-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
    </dependency>
    <!--thymeleaf 模板引擎-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
</dependencies>

02 pom.xml 添加springboot-jdbc、druid、mysql

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

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.18</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

03 创建 resources/application.properties 文件，并添加数据源属性：

# 配置默认数据源，并使用 阿里连接池
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url = jdbc:mysql://localhost:3306/test
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.max-active=20
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.datasource.initial-size=10

04 pom.xml添加Springboot Junti 测试环境依赖：

<!--测试环境-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

05 创建测试类，测试

@RunWith(SpringJUnit4ClassRunner.class) /*添加SpringJUnit支持，引入Spring-Test框架*/
@SpringBootTest(classes = App.class) /*指定Springboot启动类启动*/
public class TestMysql {

    @Autowired
    private DataSource dataSource;

    @Test
    public void test() throws SQLException {
        System.out.println(dataSource);
        System.out.println(dataSource.getConnection());
    }
}

控制台打印：

……
{
    CreateTime:"2018-02-07 12:17:10",
    ActiveCount:0,
    PoolingCount:0,
    CreateCount:0,
    DestroyCount:0,
    CloseCount:0,
    ConnectCount:0,
    Connections:[
    ]
}
2018-02-07 12:17:11.745  INFO 20776 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
Wed Feb 07 12:17:11 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
com.mysql.jdbc.JDBC4Connection@736309a9
……

# 数据源、mysql连接获取成功

二：使用JPA 进行 CRUD 操作

01 pom.xml 导入jpa 依赖

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

02 何为JPA？

JPA=JAVA Persistence;AJVA 持久化
针对持久化操作，java 提供了一套标准的API，供开发者实现，目前最常用的JPA框架是Hibernate-JPA.
spring-boot-starter-data-jpa 采用的就是 Hibernate-JPA

03 配置jpa 属性：

######JPA 配置#####
# 声明数据库
spring.jpa.database=mysql
# 是否显示SQL语句
spring.jpa.show-sql = true
# Hibernate 自动DDL 操作
# create 每次加载hibernate时都会删除上一次的生成的表
# create-drop 每次加载hibernate时根据model类生成表，但是sessionFactory一关闭,表就自动删除
# update 最常用的属性，第一次加载hibernate时根据model类会自动建立起表的结构（前提是先建立好数据库）
spring.jpa.hibernate.ddl-auto=create
#配置方言
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect

04 创建实体类 person.jack.bean/User.java,对应建包、类：

@Entity
@Table(name = "user_info")
public class User {
    @Id @GeneratedValue
    private long id;

    @Column(name = "user_name")
    private String userName;

    @Column(name = "user_age")
    private String userAge;

    @Column(name = "user_sex")
    private String userSex;
    
    public User(){
        System.out.println("对象实例化！");
    }
    //get set 方法忽略
}

05 启功服务，查看控制台信息，初始体验hibernate JPA

#控制台打印

对象实例化！
2018-02-07 13:53:37.617  INFO 25604 --- [  restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
Hibernate: drop table if exists user_info
Hibernate: create table user_info (id bigint not null auto_increment, user_age varchar(255), user_name varchar(255), user_sex varchar(255), primary key (id))

# 因为有设置：
spring.jpa.hibernate.ddl-auto=create，所以服务在启动时会先检查删除user_info，然后再建表
# 因为有设置：
spring.jpa.show-sql = true，控制台就会打印JPA操作的Sql 语句

06 新建 person/jack/dao/UserRepository.java 接口，使其继承CrudRepository<S,T> 并传递相关泛型:

package person.jack.dao;

import org.springframework.data.repository.CrudRepository;
import person.jack.bean.User;

public interface UserRepository extends CrudRepository<User,Long> {
}

07 新建 person/jack/service/UserService,注入 UserRepository：

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    @Transactional(rollbackFor=Exception.class)
    public void save(User user){ //新增或更改；执行更改，id字段必须有值，否则为新增
        userRepository.save(user);
    }

    public List<User> getAll() { //查找所有

        /**默认返回的是个迭代**/
        Iterable<User> userAll = userRepository.findAll();

        /*转换为 List 集合返回*/
        Iterator<User> iterator = userAll.iterator();
        List<User> userList = new ArrayList<User>();
        while (iterator.hasNext()) {
            userList.add(iterator.next());
        }
        return userList;
    }

    public User findOne(Long id){
        return userRepository.findOne(id);
    }

    public void delete(User user){
        userRepository.delete(user);
    }

    public void delete(Long id){
        userRepository.delete(id);
    }
}

08 新建 person/jack/controller/UserController,注入 UserService：

@Controller
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserRepository userRepository;

    @RequestMapping("/addUser")
    public String addUser(Map map,User user){
        userRepository.save(user);
        map.put("mesg", user);
        return "result";
    }
}

09 在resources 下新建默认模板目录：templates，创建模板：result.html:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8"/>
    <title>Title</title>
</head>
<body>
    <h1 th:text="${mesg}"></h1>
</body>
</html>

10 重启服务，ddl-auto=create 会重新建表，浏览器地址栏输入：http://localhost:8080/user/addUser/?userName=Jack&userAge=18&userSex=man ，测试新增

#页面打印 刚刚添加的user对象的内存地址
person.jack.bean.User@68ee4410

11 检查控制台：

#控制台打印
对象实例化！
Hibernate: insert into user_info (user_age, user_name, user_sex) values (?, ?, ?)

12 检查数据库：

mysql> select * from user_info;
+----+----------+-----------+----------+
| id | user_age | user_name | user_sex |
+----+----------+-----------+----------+
|  1 | 18       | Jack      | man      |
+----+----------+-----------+----------+
1 row in set (0.00 sec)

mysql>

# 数据添加成功！

13 浏览器地址栏重新输入：http://localhost:8080/user/addUser/?id=1&userName=Jack&userAge=19&userSex=man，现在有id参数，查看数据库是否更新！

#浏览器打印
person.jack.bean.User@58bd6a84

14 检查数据：

mysql> select * from user_info;
+----+----------+-----------+----------+
| id | user_age | user_name | user_sex |
+----+----------+-----------+----------+
|  1 | 19       | Jack      | man      |
+----+----------+-----------+----------+
1 row in set (0.00 sec)

mysql>
# user_age 由18->19 数据成功更改！

总结：以上即为：druid+mysql+jpa 操作数据库