Spring Boot学习6:Spring Boot JDBC
数据源是数据库连接的来源,通过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上创建项目,并引用相关包
我们可以了解一下JTA相关的知识
http://blog.****.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规范主要:
DI
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; }