[spring 学习6] 数据库

JDBC

项目结构

.
├── build.gradle
└── src
    └── main
        ├── java
        │   └── com
        │       └── yww
        │           ├── config
        │           │   └── DatabaseConfig.java
        │           ├── JdbcController.java
        │           ├── JdbcTemplateController.java
        │           ├── sql
        │           │   ├── schema.sql
        │           │   └── test-data.sql
        │           └── User.java
        ├── resources
        │   └── application.properties
        └── webapp
            ├── index.jsp
            └── WEB-INF
                ├── applicationContext.xml
                ├── dispatcher-servlet.xml
                ├── jsp
                │   └── show.jsp
                ├── sql
                │   ├── schema.sql
                │   └── test-data.sql
                └── web.xml

示例数据库

示例用的数据库，表。（账号：root，密码：123456）

DROP DATABASE demo_db;

CREATE DATABASE if NOT exists demo_db;
use demo_db;
create table if NOT exists user_t(
    id int auto_increment,
    name VARCHAR(10),
    passwd VARCHAR(20),
    PRIMARY KEY (id)
) engine=InnoDB;

INSERT INTO user_t(id, name, passwd)
VALUES
(1001, "admin", "123456"),
(1002, "yww", "123456");

SELECT * from user_t;

传统方式

import java.sql.*;

public class Main {

    public static void main(String[] args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 通过驱动管理获取数据库连接
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/demo_db",
                    "root",
                    "123456");
            // 获取预处理
            preparedStatement = connection.prepareStatement("select * from user_t where id = ?");
            // 设置参数
            preparedStatement.setString(1, "1001");
            // 执行查询语句
            resultSet = preparedStatement.executeQuery();
            // 遍历查询结果集
            while (resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Spring的数据访问哲学

异常体系

不同于JDBC，Spring提供了多个数据访问异常。

这些异常都继承自DataAccessException，是非检查型异常（可以不捕获）。

数据访问模板

Spring将数据访问过程中固定的和可变的部分明确划分为2部分：模板（template）和回调（callback）。

模板	回调
1.准备事务 2.开始事务 ->	3.在事务中执行
5.提交/回滚事务 6.关闭资源和处理错误	<- 4.返回数据

Spring所提供的部分数据访问模板及用途：

模板类（org.springframework.*）	用途
jdbc.core.JdbcTemplate	JDBC连接
jdbc.core.namedparam.NamedParameterJdbcTemplate	支持命名参数的JDBC连接
orm.hibernate3.HibernateTemplate	Hibernate 3.x以上的Session
orm.jpa.JpaTemplate	Java持久化API的实体管理器

配置数据源

无论那种数据访问方式，都需要配置一个数据源引用。

Spring提供了在Spring上下文中配置数据源bean的多种方式：

通过JDBC驱动程序定义的数据源。
通过JNDI查找的数据源。
连接池的数据源。

JNDI数据源

java方式：

[DatabaseConfig.java]

package com.yww.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jndi.JndiObjectFactoryBean;

@Configuration
public class DatabaseConfig {

    @Bean
    public JndiObjectFactoryBean dataSource(){
        JndiObjectFactoryBean jndiObjectFactoryBean = new JndiObjectFactoryBean();
        jndiObjectFactoryBean.setJndiName("jdbc/MyDS");
        jndiObjectFactoryBean.setResourceRef(true);
        jndiObjectFactoryBean.setProxyInterface(javax.sql.DataSource.class);
        return jndiObjectFactoryBean;
    }
}

jndi-name指定JNDI中资源的名称。如果应用程序运行在java应用服务器中，需要将resource-ref设置为true，这样给定的jndi-name将会自动添加"java:/comp/env/"前缀。

xml方式：

[applicationContext.xml]

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jee="http://www.springframework.org/schema/jee"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd">

    <jee:jndi-lookup id="dataSource" jndi-name="/jdbc/MyDS" resource-ref="true" />

</beans>

数据源连接池java方式：

需要在build.gradle中添加apache.commons.dbcp（这里使用的dbcp2）。

compile "org.apache.commons:commons-dbcp2:2.7.0"

java方式：

[DatabaseConfig.java]

package com.yww.config;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DatabaseConfig {

    @Bean
    public BasicDataSource dataSource(){
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/demo_db");
        ds.setUsername("root");
        ds.setPassword("123456");
        ds.setInitialSize(5);
        ds.setMaxTotal(10);

        return ds;
    }
}

xml方式：

[applicationContext.xml]

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource"
          class="org.apache.commons.dbcp2.BasicDataSource"
          p:driverClassName="com.mysql.jdbc.Driver"
          p:url="jdbc:mysql://localhost:3306/demo_db"
          p:username="root"
          p:password="123456"
          p:initialSize="5"
          p:maxTotal="10" />

</beans>

基于JDBC驱动的数据源

这是最简单的方式。

Spring提供了3个数据源类（位于org.springframework.jdbc.datasource）：

DriverManagerDataSource：每次连接请求时都会返回一个新建的连接。
SimpleDriverDataSource：与上一个类似，但直接使用JDBC驱动，来解决特定环境下类的加载问题。
SingleConnectionDataSource：每次请求都会返回同一个连接。

java方式：

[DatabaseConfig.java]

package com.yww.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class DatabaseConfig {

    @Bean
    public DataSource dataSource(){
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/demo_db");
        // ds.setUrl("jdbc:mysql://localhost:3306/demo_db?characterEncoding=utf8&useSSL=false");    // xml中&转义为&amp;
        ds.setUsername("root");
        ds.setPassword("123456");

        return ds;
    }
}

xml方式：

[applicationContext.xml]

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource"
          p:driverClassName="com.mysql.jdbc.Driver"
          p:url="jdbc:mysql://localhost:3306/demo_db"
          p:username="root"
          p:password="123456" />

</beans>

嵌入式数据源

需要在build.gradle中添加h2。

compile "com.h2database:h2:1.4.200"

java方式：

[DatabaseConfig.java]

package com.yww.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

import javax.sql.DataSource;

@Configuration
public class DatabaseConfig {
    @Bean
    public DataSource dataSource(){
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScript(("classpath:com/yww/sql/schema.sql"))
                .addScript("classpath:com/yww/sql/test-data.sql")
                .build();
    }
}

xml方式：

[applicationContext.xml]

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">

    <jdbc:embedded-database id="dataSource" type="H2">
<!--        <jdbc:script location="classpath:com/yww/sql/schema.sql" />-->
        <jdbc:script location="./WEB-INF/sql/schema.sql" />
        <jdbc:script location="file:WEB-INF/sql/test-data.sql" />
    </jdbc:embedded-database>
</beans>

sql文件的路径，可使用类路径或文件路径。

profile选择数据源

需要在application.properties文件中配置：

spring.profiles.default=dev
spring.profiles.active=production

package com.yww.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.jndi.JndiObjectFactoryBean;

import javax.sql.DataSource;

@Configuration
public class DatabaseConfig {
    @Profile("dev")
    @Bean
    public DataSource embeddedDataSource(){
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScript(("classpath:com/yww/sql/schema.sql"))
                .addScript("classpath:com/yww/sql/test-data.sql")
                .build();
    }

    @Profile("production")
    @Bean
    public DataSource dataSource(){
        JndiObjectFactoryBean jndiObjectFactoryBean = new JndiObjectFactoryBean();
        jndiObjectFactoryBean.setJndiName("jdbc/MyDS");
        jndiObjectFactoryBean.setResourceRef(true);
        jndiObjectFactoryBean.setProxyInterface(javax.sql.DataSource.class);
        return (DataSource)jndiObjectFactoryBean;
    }
}

JDBC

传统用法

[JdbcController.java]

package com.yww;

import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.beans.factory.annotation.Autowired;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Controller
@RequestMapping("/show")
public class JdbcController {

    @RequestMapping(method = RequestMethod.GET)
    public String showData(ModelMap model){
        String msg = query(1001);
        System.out.println(msg);
        model.addAttribute("msg", msg);
        return "show";
    }

    @Autowired
    DataSource dataSource;

    public String query(int id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        String result = "";
        try{
            conn = dataSource.getConnection();  // 获取连接
            String sql = "select * from user_t where id = ?";
            stmt = conn.prepareStatement(sql);  // 创建语句
            stmt.setString(1, String.valueOf(id));  // 绑定参数
            resultSet = stmt.executeQuery();     // 执行语句
            while (resultSet.next())
                result = resultSet.getInt("id") + resultSet.getString("name") + resultSet.getString("passwd");
        }catch (SQLException e){    // 处理异常
        }finally {  // 清理资源
            try{
                if(resultSet != null)
                    resultSet.close();
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    conn.close();
            }catch (SQLException e){    // 处理异常
            }
        }

        return result;
    }
}

JDBC模板

Spring为JDBC提供了三个模板类供选择：

JdbcTemplate：最基本的Spring JDBC模板，这个模板支持简单的JDBC数据库访问功能以及基于索引参数的查询。
NamedParameterJdbcTemplate：可以在执行查询时，将值以命名参数的形式绑定到SQL中，而不是使用简单的索引参数。
SimpleJdbcTemplate：利用一些如自动装箱，范型以及可变参数列表来简化JDBC模板的使用。

首先需要配置一个jdbc模板的Bean。 [DatabaseConfig.java]

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

JdbcOperations是一个接口，定义了JdbcTemplate所实现的操作。

[JdbcTemplateController.java]

package com.yww;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Random;

@Controller
@RequestMapping("/show_t")
public class JdbcTemplateController {

    @Autowired
    private JdbcOperations jdbcOperations;

    @RequestMapping(method = RequestMethod.GET)
    public String showData(ModelMap model){
        Random random = new Random();
        User user = new User(random.nextInt(10000), "yww", "123456");
        addUser(user);
        User userFound = findOne(1001);
        model.addAttribute("msg", userFound);
        return "show";
    }

    // 添加
    public void addUser(User user){
        String sql = "insert into user_t (id, name, passwd) values (?, ?, ?)";
        jdbcOperations.update(sql,
                user.getId(),
                user.getName(),
                user.getPasswd());
    }

    // 查询
    public User findOne(int id){
        String sql = "select * from user_t where id = ?";
        return jdbcOperations.queryForObject(sql,
                new UserRowMapper(),    // 指定映射
                
//                (rs, rowNum) -> {
//                    return new User(
//                            rs.getInt("id"),
//                            rs.getString("name"),
//                            rs.getString("passwd")
//                    );
//                },    // 指定映射(java 8 lambda,函数式编程)

//                this::mapUser,  // 指定映射(Java 8 方法引用)
                id);
    }

    // 映射类
    public static final class UserRowMapper implements RowMapper<User>{
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new User(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("passwd")
            );
        }
    }

    // 映射方法
    public User mapUser(ResultSet rs, int row) throws SQLException{
        return new User(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("passwd")
        );
    }

}

因为RowMapper接口只声明了addRow()一个方法，因此它完全符合函数式接口的标准，可以使用Lambda表达式。或使用java 8中的方法引用，在单独的方法中定义映射逻辑。（可以不必显式实现RowMapper接口，只需Lambda表达式或方法接受相同参数，返回相同类型）

简单描述一下NamedParameterJdbcTemplate的用法，先声明一个它的Bean，使用时，sql语句不用?，和放入的参数为字典Map类型。如下：

    public void addUserByName(User user){
        String sql = "insert into user_t (id, name, passwd) values (:id, :name, :passwd)";
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("id", user.getId());
        paramMap.put("name", user.getName());
        paramMap.put("passwd", user.getPasswd());
        jdbcOperations.update(sql, paramMap);
    }

ORM

使用mybatis，hibernate之类的方式。

JPA

Spring Data JPA通过方法签名，自动创建方法的实现。（本质上Spring Data定义了领域特定语言(DSL)）

由动词 + 主题 + 关键词 + 断言组成。

readSpittersByFirstnameOrLastnameOrderByLastname()
|__|_______|__|_________________________________|
查询动词    关键词
    主题（可不指定）          断言

配置：需配置LocalEntityManagerFactoryBean或LocalContainerEntityManagerFactoryBean的Bean，再配置JpaVendorAdapter的Bean，再配置@EnableJpaRepositories，最后自定义的Repository接口继承JpaRepository。

上篇[paper] You Only Look Once:Unified, Real-Time Object Detection

下篇[springboot 学习1] 快速开始