jdbc小结3
批量执行SQL
package com.kang;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
//SQL语句的批量执行
//1.只能批量执行DML
//2.只能通过Statement执行
public class JDBC4 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbc1?user=root&password=123456&characterEncoding=UTF8&serverTimezone=UTC";
Connection conn =DriverManager.getConnection(url);
String sql = "insert into student (name,age,sex) values('李四',23,'男')";
String sql1 = "insert into student (name,age,sex) values('王五',24,'男')";
String sql2 = "insert into student (name,age,sex) values('马六',25,'男')";
String sql3 = "insert into student (name,age,sex) values('吴七',26,'男')";
String sql4 = "insert into student (name,age,sex) values('刘八',27,'男')";
String sql5 ="delete from student where id =1";
String[] sqls = {sql,sql1,sql2,sql3,sql4};
Statement stmt = conn.createStatement();
for(String s: sqls) {
//将需要执行的SQL语句添加
stmt.addBatch(s);
//执行所有的语句
int[] rowCounts=stmt.executeBatch();
System.out.println(Arrays.toString(rowCounts));
}
}
}
事务
事务四大特性:
- 1.原子性:当多条SQL语句处于一个事务中,将同一个事务中所有语句看作一个整体,要么都执行,要么都不执行
- 2.一致性:数据在操作之后,数据的总量和操作之前必须一致
- 3.隔离性:多个事务时,通过隔离级别来保证事务的独立性
- 4.持久性:事务一旦发生,对数据库造成的影响是持续的,不可逆
七个传播特性:SQL在执行过程中,对事务的使用方案。
package com.kang;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
public class JDBC5 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbc1?user=root&password=123456&characterEncoding=UTF8&serverTimezone=UTC";
Connection conn =DriverManager.getConnection(url);
//关闭自动提交
//自动提交不关闭,不能使用rollback和commit方法
conn.setAutoCommit(false);
String sql = "insert into student (name) values ('bb')";
int rc= conn.createStatement().executeUpdate(sql);
System.out.println(rc);
//添加存档点
Savepoint sp = conn.setSavepoint();
String sql1 = "insert into student (name) values ('cc')";
int rc1= conn.createStatement().executeUpdate(sql);
System.out.println(rc1);
//回滚事务
conn.rollback();
//回滚到指定的存档点
conn.rollback(sp);
//提交事务
conn.commit();
}
}
数据库连接池
package com.kang;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class JDBC6 {
public static void main(String[] args) throws Exception {
//Hashtable的子类
Properties properties = new Properties();
//加载属性文件
InputStream in = new FileInputStream("src/driud.properties");
properties.load(in);
System.out.println(properties);
// properties.put("username", "root");
// properties.put("url", "jdbc:mysql://localhost:3306/jdbc1?characterEncoding=UTF8&serverTimezone=UTC");
// properties.put("password", "123456");
// properties.put("driverClassName", "com.mysql.cj.jdbc.Driver");
//数据库连接池
DataSource dataSource =
DruidDataSourceFactory.createDataSource(properties);
//获取连接对象
Connection conn = dataSource.getConnection();
//将该conn对象交还给连接池
conn.close();
}
}