jdbc小结3

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();
	}
}