一.DAO初级重构
新增工具类,把重复的的代码封装起来
JDBCUtil
然后其他类调用即可,再使用PreparedStatement改造即可
// JDBC工具类
public class JDBCUtil {
private static String url;
private static String userName;
private static String password;
private static String driverClassName;
//
static {
try {
InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
Properties pro = new Properties();
pro.load(in);
url = pro.getProperty("url").trim();
userName = pro.getProperty("userName").trim();
password = pro.getProperty("password").trim();
driverClassName = pro.getProperty("driverClassName").trim();
Class.forName(driverClassName);
} catch (Exception e) {
e.printStackTrace();
}
}
private JDBCUtil() {
}
public static Connection getConnection() {
// 2) 连
try {
return DriverManager.getConnection(url, userName, password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
// 5)释
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
二.PreparedSatement
- PreparedStatement 执行效率高
mysql 不支持预编译 (自己测试) - PreparedStatement 可以防止 sql注入问题
通过拼接具有特殊含义的字符串, 改变原先sql语句逻辑这种操作, 称之为sql注入
新建类PreparedStatement
// PreparedStatement
public class PreparedTest {
@Test
public void insert() {
// SQL语句
String sql = "insert into t_student(name, age) " + "value(?,?);";
Connection connection = null;
PreparedStatement statement = null;
// 1)加
try {
connection = JDBCUtil.getConnection();
// 3)语
statement = connection.prepareStatement(sql);
// 4)执
statement.setString(1, "宙斯");
statement.setInt(2, 18);
statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5)释
JDBCUtil.close(connection, statement, null);
}
}
//登录成功
@Test
public void testStatement() throws Exception {
String username = "宙斯";
String password = "' or 1 = 1 or '";
String sql = "select * from t_user " + "where name='" + username + "' and password = '" + password + "';";
Connection connection = JDBCUtil.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
JDBCUtil.close(connection, statement, resultSet);
}
//登录失败
@Test
public void testPreparedStatement() throws Exception {
String username = "宙斯";
String password = "' or 1 = 1 or '";
String sql = "select * from t_user " + "where name= ? and password = ?";
Connection connection = JDBCUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, username);
statement.setString(2, password);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
JDBCUtil.close(connection, statement, resultSet);
}
}
三.事务
事务的ACID属性:
- 原子性(Atomicity):原子在化学中,是最小单位,不可以再分割了.
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 - 一致性(Consistency):包装数据的完整性.
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。(数据不被破坏) - 隔离性(Isolation):Spring再讲
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。 - 持久性(Durability):
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响
没有使用事务假如转账时中间断电,Over~
public class TxTest {
// 没有使用事务
@Test
public void testTx() throws Exception {
// 1)检查西门的钱是否大于1000
String sql = "select * from bank where name = ? and balance >= ?";
Connection connection = JDBCUtil.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, "西门");
ps.setInt(2, 1000);
ResultSet resultSet = ps.executeQuery();
if (!resultSet.next()) {
throw new RuntimeException("余额不足");
}
// 2)从西门扣除一千
sql = "update bank set balance = balance - ? where name = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1, 1000);
ps.setString(2, "西门");
ps.executeUpdate();
// 模拟断电
int i = 1 / 0;
// 3)东门增加一千
sql = "update bank set balance = balance + ? where name = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1, 1000);
ps.setString(2, "东门");
ps.executeUpdate();
JDBCUtil.close(connection, ps, resultSet);
}
@Test
public void testTx1() throws Exception {
// 1)检查西门的钱是否大于1000
String sql = "select * from bank where name = ? and balance >= ?";
Connection connection = JDBCUtil.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, "西门");
ps.setInt(2, 1000);
ResultSet resultSet = ps.executeQuery();
if (!resultSet.next()) {
throw new RuntimeException("余额不足");
}
// 手动关闭事务
connection.setAutoCommit(false);
try {
// 2)从西门扣除一千
sql = "update bank set balance = balance - ? where name = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1, 1000);
ps.setString(2, "西门");
ps.executeUpdate();
// 模拟断电
int i = 1 / 0;
// 3)东门增加一千
sql = "update bank set balance = balance + ? where name = ?";
ps = connection.prepareStatement(sql);
ps.setInt(1, 1000);
ps.setString(2, "东门");
ps.executeUpdate();
// 提交事务
connection.commit();
} catch (Exception e) {
// 回滚
connection.rollback();
e.printStackTrace();
} finally {
JDBCUtil.close(connection, ps, resultSet);
}
}
}
四.批处理
JDBC的批量处理语句包括下面两个方法:
addBatch(String sql):添加需要批量处理的SQL语句或是参数;
executeBatch();执行批量处理语句;
public class BatchTest {
// 使用批量处理的Statement
@Test
public void tsetStatementBatch() throws Exception {
Connection connection = JDBCUtil.getConnection();
Statement sta = connection.createStatement();
long start = System.currentTimeMillis();
// 10000条SQL
for (int i = 0; i < 10000; i++) {
String sql = "insert into bank(name, balance) values('吴静'," + i + ")";
// 将SQL保存到缓存池中
sta.addBatch(sql);
// 200条做一个批次
if (i % 200 == 0) {
// 将缓存池中的sql发送到数据库
sta.executeBatch();
// 清掉Batch
sta.clearBatch();
}
}
// 将缓存池中的sql发送到数据库
sta.executeBatch();
System.out.println(System.currentTimeMillis() - start);
JDBCUtil.close(connection, sta, null);
}
// 没有批量处理的Statement
@Test
public void tsetStatement() throws Exception {
Connection connection = JDBCUtil.getConnection();
Statement sta = connection.createStatement();
long start = System.currentTimeMillis();
// 10000条SQL
for (int i = 0; i < 10000; i++) {
String sql = "insert into bank(name, balance) values('吴静'," + i + ")";
sta.executeUpdate(sql);
}
System.out.println(System.currentTimeMillis() - start);
JDBCUtil.close(connection, sta, null);
}
//批量处理的PreparedStatement
@Test
public void tsetPreparedStatementBatch() throws Exception {
Connection connection = JDBCUtil.getConnection();
String sql = "insert into bank(name, balance) values('吴静',?)";
PreparedStatement sta = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
// 10000条SQL
for (int i = 0; i < 10000; i++) {
sta.setInt(1, i);
// 将SQL保存到缓存池中
sta.addBatch();
// 200条做一个批次
if (i % 200 == 0) {
// 将缓存池中的sql发送到数据库
sta.executeBatch();
// 清掉Batch
sta.clearBatch();
}
}
// 将缓存池中的sql发送到数据库
sta.executeBatch();
System.out.println(System.currentTimeMillis() - start);
JDBCUtil.close(connection, sta, null);
}
//不使用批量处理的PreparedStatement
@Test
public void tsetPreparedStatement() throws Exception {
Connection connection = JDBCUtil.getConnection();
String sql = "insert into bank(name, balance) values('吴静',?)";
PreparedStatement sta = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
// 10000条SQL
for (int i = 0; i < 10000; i++) {
sta.setInt(1, i);
sta.executeUpdate();
}
System.out.println(System.currentTimeMillis() - start);
JDBCUtil.close(connection, sta, null);
}
}
MySQL服务器既不支持PreparedStatement的性能优化,也不支持JDBC中的批量操作.
但是,在新的JDBC驱动中,我们可以通过设置参数来优化.
url=jdbc:mysql://localhost:3306/jdbcdemo?rewriteBatchedStatements=true
五.大数据类型
BLOB系列是大的二进制类型,允许存储(255b - 4G).
TINYBLOB,BLOB(64K),MEDIUMBLOB(160M),LONGBLOB(4G)
二进制类型存储任何类型的数据(包括:图像,音频,视频等);
//大数据类型
public class BigTypeTest {
//把图片插入到数据库中
@Test
public void testImage() throws Exception {
//图片
String sql = "insert into bank(image) values(?)";
File file = new File("D:/Java/openSource/JDBC_2/stack.png");
Connection connection = JDBCUtil.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
//设置参数
ps.setBlob(1, new FileInputStream(file));
ps.executeUpdate();
JDBCUtil.close(connection, ps, null);
}
//获取数据库中的文件
@Test
public void testGetImage() throws Exception {
//图片
String sql = "select image from bank where id = ?";
//
Connection connection = JDBCUtil.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
//设置值
ps.setLong(1, 1L);
ResultSet resultSet = ps.executeQuery();
if (resultSet.next()) {
//获取数据库中的blob
Blob blob = resultSet.getBlob("image");
//将blob以流方式返回
InputStream inputStream = blob.getBinaryStream();
Files.copy(inputStream, Paths.get("D:/Java/openSource/JDBC_2/stack2.png"));
inputStream.close();
}
JDBCUtil.close(connection, ps, resultSet);
}
}
六.获取自动生成的主键
Statement方式:
int executeUpdate(String sql, int autoGeneratedKeys):执行SQL:
参数:autoGeneratedKeys,是否需要返回自动生成的主键.常量值:Statement.RETURN_GENERATED_KEYS.
PreparedStatement方式:
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) :
创建PreparedStatement对象,并指定是否需要返回生成的主键. 常量值:Statement.RETURN_GENERATED_KEYS
ResultSet getGeneratedKeys():获取自动生成的主键
public class AutoKeyTest {
//使用Statement
@Test
public void testStatement() throws Exception {
String sql = "insert bank(name, balance) values('吴静',2);";
//获取链接
Connection con = JDBCUtil.getConnection();
//Statement对象
Statement sta = con.createStatement();
//执行
sta.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = sta.getGeneratedKeys();
if (rs.next()) {
System.out.println(rs.getLong(1));
}
JDBCUtil.close(con, sta, rs);
}
//使用PreparedStatement
@Test
public void testPreparedStatement() throws Exception {
String sql = "insert bank(name, balance) values(?,?);";
//获取链接
Connection con = JDBCUtil.getConnection();
//Statement对象
PreparedStatement sta = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
sta.setString(1, "金坷垃");
sta.setInt(2, 3);
//执行
sta.executeUpdate();
ResultSet rs = sta.getGeneratedKeys();
if (rs.next()) {
System.out.println(rs.getLong(1));
}
JDBCUtil.close(con, sta, rs);
}
}