数据源(DataSource)
数据源是数据库连接的来源,通过DataSource接口获取
类型:
通用型数据源(javax.sql.DataSource)
主要使用场景:通用型数据库,本地事务,一般通过socket方式连接
分布式数据源(javax.sql.XADataSource)
主要使用场景:通用习惯数据库,分布式事务,一般通过socket方式连接
嵌入式数据源(org.springframework.jdbc.datasource.embedded.EmbeddedDatabase)
主要使用场景:本地文件系统数据库,如 HSQL,H2, Derby
枚举:org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType
事务(Transaction)
重要概念:
1 自动提交模式(Auto-commit mode)
默认情况下,当独立SQL语句执行后,当前事务将会自动提交
触发时机(DML执行后,DDL执行后,SELECT查询后结果集关闭后,
存储过程执行后(如果执行返回结果集的话,待其关闭后))
2 事务隔离级别(Transaction isolation levels)
事务隔离级别决定事务中执行语句中的数据可见性
事务并发可能的影响
a 脏读
b 不可重现读
c 幻读
3 保护点(savepoints)
保护点是在事务中创建,提供细粒度事务控制
部分事务回滚
当 配置 properties 不记得时,可以直接查路径,Find in Path
打开 http://start.spring.io/,构建应用,版本 spring-boot 1.5.14
数据库连接池 maven 依赖 |
<dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> </dependency> |
实体类 |
public class User { private Integer id; private String userName;
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public String getUserName() { return userName; }
public void setUserName(String userName) { this.userName = userName; } } |
第一步 配置 properties |
spring.datasource.url=jdbc:mysql://localhost:3306/db_jxc2 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.type=org.apache.commons.dbcp.BasicDataSource spring.datasource.dbcp.url=jdbc:mysql://localhost:3306/db_jxc2 spring.datasource.dbcp.username=root spring.datasource.dbcp.password=root spring.datasource.dbcp.driver-class-name=com.mysql.jdbc.Driver |
第二步 编写 接口与 实现 |
import cn.shendu.springbootlesson6.domain.User;
public interface UserService { public boolean save(User user); } |
import cn.shendu.springbootlesson6.service.UserService; 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.Transactional;
import java.sql.PreparedStatement; import java.sql.SQLException;
@Service @EnableTransactionManagement @Transactional public class UserServiceImpl implements UserService{ @Autowired private JdbcTemplate jdbcTemplate;
@Override public boolean save(User user) { return jdbcTemplate.execute("INSERT INTO t_user(user_name) VALUES (?)", new PreparedStatementCallback<Boolean>() { @Override public Boolean doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException { preparedStatement.setString(1,user.getUserName()); return preparedStatement.executeUpdate() > 0; } }); } } |
编写 controller |
import cn.shendu.springbootlesson6.domain.User; import cn.shendu.springbootlesson6.service.UserService; 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.util.StringUtils; import org.springframework.web.bind.annotation.*;
import javax.sql.DataSource; import java.sql.*; import java.util.HashMap; import java.util.Map;
@RestController public class JdbcController {
@Autowired protected DataSource dataSource; @Autowired protected UserService userService;
@RequestMapping("/user/get") public Map<String,Object> getUser(@RequestParam(value = "id",defaultValue = "1") String id){ Map<String,Object> data = new HashMap<>(); Connection connection = null; // Savepoint savepoint = null;
try{ connection = dataSource.getConnection(); Statement statement = connection.createStatement();
// 设置还原点 /* connection.setAutoCommit(false); savepoint = connection.setSavepoint();*/
// 可以 加判断,防止sql 注入
ResultSet resultSet = statement.executeQuery("select * from t_user where id="+id);
while(resultSet.next()){ int id_ = resultSet.getInt("id"); String name = resultSet.getString("user_name");
data.put("id",id); data.put("name",name); } // 还原点 //connection.commit();
}catch (Exception e){ /* if(connection!=null){ connection.rollback(savepoint); }*/ e.printStackTrace(); }finally { try { // 还原点 // connection.setAutoCommit(true); connection.close(); } catch (SQLException e) { e.printStackTrace(); } }
return data; }
@RequestMapping("/user/add") @ResponseBody public Map<String,Object> addUser(@RequestBody User user){ Map<String,Object> data = new HashMap<>();
userService.save(user);
return data; }
}
|
参考资料:
小马哥 spring boot 教程