Spring Boot学习6：Spring Boot JDBC

1 数据源
数据源是数据库连接的来源，通过DataSource接口获得

数据源类型
1）通用型数据源（javax.sql.DataSource）
主要使用场景：通用型数据库，本地事务，一般通过Socket方式连接

2）分布型数据源（javax.sql.XADataSource）
主要使用场景：通用型数据库，分布式事务，一般通过Socket方式连接

3）嵌入式数据源（org.springframework.jdbc.datasource.embedded.EmbeddedDatabase）
主要使用场景：本地文件系统数据库，如HSQL,H2,Derby等
枚举：org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType

2 事务
事务用于提供数据完整性，并在并发访问下确保数据视图的一致性

例子：创建springboot项目，并引用包web,jdbc,mysql驱动
创建项目后，我们看一下几个接口Driver,DriverManager,Connection,Statement,ResultSet
我们来建立数据库连接
创建JdbcController

@RestController
public class Jdbc2Controller {

    @Autowired
    @Qualifier(value="dataSource")
    private DataSource dataSource;

    @RequestMapping("/user/getUsers")
    public Map<String,Object> getUsers(@RequestParam(name="id",defaultValue = "1") String id){
        Map<String,Object> data = new HashMap<String,Object>();

        Connection connection=null;
        Statement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            String sql = "select * from user where id="+id;
//            connection.prepareStatement()
            ResultSet resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                int r_id = resultSet.getInt("id");
                String r_name = resultSet.getString("name");
                int r_age = resultSet.getInt("age");
                data.put("id",r_id);
                data.put("name",r_name);
                data.put("age",r_age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try{
                connection.close();
                statement.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        return data;
    }
}

配置jdbc,在application.properties中配置：
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.jdbc.Driver

测试：
http://localhost:8080/user/getUsers?id=1
返回：
{"name":"xiaohuang","id":1,"age":1}

修改为防止sql注入的方式

@RestController
public class Jdbc2Controller {

    @Autowired
    @Qualifier(value="dataSource")
    private DataSource dataSource;

    @RequestMapping("/user/getUsers")
    public Map<String,Object> getUsers(@RequestParam(name="id",defaultValue = "1") int id){
        Map<String,Object> data = new HashMap<String,Object>();

        Connection connection=null;
        Statement statement = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select id,name,age from user where id = ?";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int r_id = resultSet.getInt("id");
                String r_name = resultSet.getString("name");
                int r_age = resultSet.getInt("age");
                data.put("id",r_id);
                data.put("name",r_name);
                data.put("age",r_age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try{
                connection.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        return data;
    }
}

3.JDBC4.0

例子：使用事务操作

package com.segmentfault.springbootlesson7;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import pojo.User;

import java.sql.PreparedStatement;
import java.sql.SQLException;

@Service
@EnableTransactionManagement
public class UserServiceImpl implements UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional(propagation = Propagation.REQUIRED,isolation= Isolation.SERIALIZABLE)
    public String save(User user) {
        Object obj = jdbcTemplate.execute("insert into user (name,age) values(?,?)", new PreparedStatementCallback<Object>() {
            @Override
            public Boolean doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
                preparedStatement.setString(1,"zhejaing");
                preparedStatement.setInt(2,100);
                return preparedStatement.executeUpdate()>0;
            }
        });
        return obj.toString();
    }
}

例子：使用jdbc获取到数据库中的元数据以及数据

@Autowired
@Qualifier(value="dataSource")
private DataSource dataSource;

@Autowired
private JdbcTemplate jdbcTemplate;

@RequestMapping("/user")
public List<Map<String,Object>> getUser(){
    List<Map<String,Object>> data = jdbcTemplate.execute("select * from user", new PreparedStatementCallback<List<Map<String,Object>>>() {
        @Override
        public List<Map<String, Object>> doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<String> columnNameList = new ArrayList<String>(columnCount);
            for(int i=1;i<=columnCount;i++){
                String columnName = metaData.getColumnName(i);
                columnNameList.add(columnName);
            }

            List<Map<String,Object>> data = new LinkedList<>();
            while(resultSet.next()){
                Map<String,Object> columnData = new LinkedHashMap<>();
                for(String columnName:columnNameList){
                    Object columnValue = resultSet.getObject(columnName);
                    columnData.put(columnName,columnValue);
                    data.add(columnData);
                }
            }
            return data;
        }
    });
    return data;
}

运行测试

[{"id":1,"name":"xiaohuang","age":1},{"id":1,"name":"xiaohuang","age":1},{"id":1,"name":"xiaohuang","age":1},{"id":2,"name":"xiaolong","age":22},{"id":2,"name":"xiaolong","age":22},{"id":2,"name":"xiaolong","age":22},{"id":3,"name":"xiaolong","age":23},{"id":3,"name":"xiaolong","age":23},{"id":3,"name":"xiaolong","age":23},{"id":4,"name":"John","age":18},{"id":4,"name":"John","age":18},{"id":4,"name":"John","age":18}]

还需要注意Transactional的底层源码，可以自行再次调试学习

使用Transactional，会自动封装对象，并强化事务功能

启动代理有两种，

接口的代码，是jdk的默认代理proxy，

@Autowired
private UserService userService;

@Service
@EnableTransactionManagement(proxyTargetClass = false)
public class UserServiceImpl implements UserService {

对类进行代理，那么需要使用到cglib

@Autowired
private UserServiceImpl userService;

@Service
@EnableTransactionManagement(proxyTargetClass = true)
public class UserServiceImpl implements UserService {

4.微服务的分布式事务

使用Bitronix，

在start.spring.io上创建项目，并引用相关包

Spring Boot学习6：Spring Boot JDBC

我们可以了解一下JTA相关的知识

http://blog.csdn.net/habren/article/details/52090773

https://www.ibm.com/developerworks/cn/java/j-lo-jta/

两阶段提交原理

tomcat中，分布式事务需要使用原始方法，UserTransaction, UserTransactionManager

5.问题

tomcat下面跨多个数据库的分布式事务有什么好的方法和建议？

UserTransaction，UserTransactionManager等了解下

如何高效学习JSR规范，有哪些规范值得关注？

Web：Servlet，WebSocket，WebService等

最重要的是Java JDBC规范，Java虚拟机规范等

JSR规范主要：

JaxB

JDBC

JMX（事务，点对点，高级协议）

JPA

JSTL

JSP

JTA

LANG

REST

SAX

SOAP

WEBSERVICE

WEBSOCKET

XML

WSDL

等

3）问题，Spring事务的底层实现，遇到RuntimeException才会回滚，为什么？

因为SpringBoot里面基本上都是运行时异常，但是Transactional注解可以指定哪些异常可以被回滚，哪些异常不回滚等

4）springboot动态切换数据源的解决方案？

5）jdbc的connection和Orm比如Hibernate中的session什么关系？如果将Hibernate的一级缓存关了？

connection无状态，session有状态。

Hibernate的一级环境可以通过代码的方式关闭，不可通过配置方式关闭。

6）手动事务

PlatformTransactionManager事务管理器

TransactionStatus事务状态

例子：

@Autowired
private PlatformTransactionManager platformTransactionManager;

public Boolean save2(User usr){
    DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
    TransactionStatus transactionStatus = platformTransactionManager.getTransaction(transactionDefinition);
    Boolean result = null;
    try {
        result = save(usr);
        platformTransactionManager.commit(transactionStatus);
    } catch (Exception e) {
        e.printStackTrace();
        platformTransactionManager.rollback(transactionStatus);
    }
    return result;
}

Spring Boot学习6：Spring Boot JDBC

相关推荐