标签：ps finish String javaJDBC Connection sql null conn

JDBC核心技术

1.JDBC概述

1.1 数据的持久化

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后的使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以"固化",而持久化的实现过程大多通过各种关系数据库来完成
持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在硬盘中，xml数据文件中

1.2SQLyog安装失败解决

mysql8.0及其之前的加密方式于mysql8.0之后的加密方式不同：mysql8以前的版本加密规则是mysql_native_password，而mysql8之后的加密规则是caching_sha2_password,所以造成了命令连接正常，而用native连接失败。

需要添加

mysql> use mysql;
Database changed
 
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.12 sec)
 
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.03 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

1.3技术概览

1.4 java中存储技术

在Java中，数据库存取技术可以分为如下几类：
- JDBC直接访问数据库
- JDO（java Data Object)技术
- 第三方O/R工具如：Mybatis等
JDBC是java访问数据库的基石，JDO,Mybatis等只是封装了JDBC

1.5 JDBC介绍

1.6 JDBC体系结构

JDBC接口（API）包括两个层次：
- 面向应用的API：JAVA API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
- 面向数据库的API：java Driver API.供开发商开发数据库驱动程序用
  JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。

不同数据库厂商，需要针对这套接口，提供不同实现，不同实现的集合，即为不同数据库的驱动----面向接口编程

1.7 JDBC程序编写步骤

ODBC(Open Database Connectivity，开放式数据库连接)，是微软在Windows平台下推出的。使用者在程序中只需要调用ODBC API，由 ODBC 驱动程序将调用转换成为对特定的数据库的调用请求。

1.8加载到项目

加载JDBC驱动到项目

2 获取数据库连接

2.1要素一：Driver接口实现类

2.11Driver接口介绍

Driver接口实现连接数据库代码：

package com.peng.connectiom;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.junit.Test;

public class ConnectionTest {
	
	//方式一
	@Test
	public void testConection() throws SQLException {
		
		//1. 获取Driver的实现类对象并提供数据库地址
		Driver driver=new com.mysql.jdbc.Driver();
		
		//jdbc:mysql:协议
		//locaolhost:ip协议
		//3306：默认mysql的端口号
		//test:test的数据库
		String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
		
		//2.将用户名和密码封装在Properties中
		Properties info=new Properties();
		info.setProperty("user", "root");
		info.setProperty("password", "1094148867g");
		
		//3.连接数据库
		Connection conn=driver.connect(url,info);
		
		System.out.println(conn);
		
		
		
	}
	
	//方式二：方式一的迭代(取消第三方的API（com.mysql.jdbc.Driver）)
	
	@Test
	public void testConnection2() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
		//1.获取Driver的实现类对象，使用反射
		Class clazz=Class.forName("com.mysql.jdbc.Driver");
		Driver driver=(Driver)clazz.newInstance();
		
		//2.提供要连接的数据库
		
		String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
		
		//提供连接需要的用户名和密码
		Properties info=new Properties();
		info.setProperty("user", "root");
		info.setProperty("password", "1094148867g");
		
		//获取连接
		Connection conn=driver.connect(url, info);
		
		System.out.print(conn);
		
		
		
	}
	
	//方式三：使用DriverManger替换Driver,
	@Test
	public void testConnection3() throws Exception {
		//1.获取Driver类的实现类对象
		Class clazz=Class.forName("com.mysql.jdbc.Driver");
		Driver driver=(Driver)clazz.newInstance();
		
		//2.另外3个连接的基本信息
		String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
		String user="root";
		String password="1094148867g";
		
		
		
		//3.注册驱动
		DriverManager.registerDriver(driver);
		
		//4.获取连接
		Connection conn=DriverManager.getConnection(url,user,password);
		System.out.print(conn);
	}
	
	//方式四：优化方式三
		@Test
		public void testConnection4() throws Exception {
			
			//1.另外3个连接的基本信息
			String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
			String user="root";
			String password="1094148867g";
			
			
			//2.获取Driver类的实现类对象
			Class.forName("com.mysql.jdbc.Driver");
			/*
			 * Class.forName("com.mysql.jdbc.Driver");其实这一步也可以省略，
			 * 在程序驱动导入时，则会做该操作。但最好不省略，因为只是用与mysql
			 */
			
		
			//相较于方式三则可以省略如下操作
			/*
			 * Mysql自己注册驱动，原因：
			 * 在java.sql.Driver;的Driver实现类中有：
			 * static {
					try {
						java.sql.DriverManager.registerDriver(new Driver());
					} catch (SQLException E) {
						throw new RuntimeException("Can't register driver!");
					}
				}
		 * 有以上静态代码块（在加载类时执行），可以看到注册驱动的代码
			 */
//			Driver driver=(Driver)clazz.newInstance();
//
//			注册驱动
//			DriverManager.registerDriver(driver);
			
			//3.获取连接
			Connection conn=DriverManager.getConnection(url,user,password);
			System.out.print(conn);
		}
		


	//方式五，最终版.将数据库连接需要的基本信息声明在配置文件中，通过读取配置文件的方式，获取连接
		
		@Test
		public void getConnection5() throws IOException, ClassNotFoundException, SQLException {
			
			//1.读取配置文件中的四个基本信息
			InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			
			Properties prop = new Properties();
			prop.load(is);
			
			String url=prop.getProperty("url");
			String user=prop.getProperty("user");
			String password=prop.getProperty("password");
			String driverClass=prop.getProperty("driverClass");
		
			//2.加载驱动
			Class.forName(driverClass);
			
			//3.获取连接
			
			Connection connection = DriverManager.getConnection(url,user,password);
			
			System.out.println(connection);
			
		}

		/*
		 * 1.第五种方式实现了数据与代码的分离，实现类解耦
		 * 2.如果需要修改配置文件信息，就可以避免重新打包
		 */
}

驱动加载Driver实现类

3.使用PreparedStatenment实现CRUD操作

3.1 操作和访问数据库

数据库连接被用于向数据库服务端发送命令和SQL语句，并接受数据库服务端返回的结果。其实一个数据库连接就是一个Socket连接
在java.sql包中有3个接口分别定义了对数据库的调用的不同方式：
- statement:用于执行静态SQL语句并返回它所生成结果的对象。
- prepatedStatement：SQL语句被预编译并存储在此对象中，可以使用此对象多次高效地执行语句。
- CallbleStatement:用于执行SQL存储过程

3.2 ORM编程思想

object relationship mapping对象关系映射
一个数据表对应一个java类
表中一条记录对应java类的一个对象
表中一个字段对应java类的一个属性

3.3 数据库与SQL类型的转化

java类型（左）与sql类型（右）转换:

boolean--->BIT
byte--->TINYNT
short--->SMALINT
int--->INTEGER
long--->BIGINT
String--->CHAR,VARCHAR,LONGCARCHAR
byte array--->BINARY,VAR BINARY
java.sql.Date--->DATE
java.sql.Time--->TIME
java.sql.Timetamp--->TIMESTAMP

3.3使用Statement操作数据表的弊端

通过调用Connection对象的cteatStatement()方法的创建对象，该对象用于执行静态的SQL语句，并且返回执行结果
Statement:接口中定义了下列方法用于执行SQL语句：

int excuteUpdate(String sql):
//执行更新操作INSERT,UPDATE,DELETE
ResultSet execteQuery(String sql)
//执行查询操作SELECT

但是使用statment操作数据表存在弊端：

问题一：存在并串操作，繁琐

String sql="SELECT user,password FROM user_table WHERE USER='"+userName+"'AND PASSWORD='"+password+"'"
当需要对sql语句添加java中的变量操作到语句中时要使用复杂的并串

问题二：存在SQK注入问题

#user:1' OR
#password:='1' OR '1'='1
SELECT USER,PASSWORD FROM user_table WHERE USER='1' OR 'AND PASSWORD='='1' OR '1'='1'

SQL注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL语句段或命令（如：SELECT user , passwword FROM usertable WHERE user='a' OR 1='AND password=' OR '1'='1'),从而利用系统的SQL引擎完成恶意行为的做法

对于java而言，要防范SQL注入，只要用preparedStatement(从Statement扩展而来)取代Statment就可以了

Statement代码演示

	public class StatementTest {

	// 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
	@Test
	public void testLogin() {
		Scanner scan = new Scanner(System.in);

		System.out.print("用户名：");
		String userName = scan.nextLine();
		System.out.print("密   码：");
		String password = scan.nextLine();

		// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
		String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
				+ "'";
		User user = get(sql, User.class);
		if (user != null) {
			System.out.println("登陆成功!");
		} else {
			System.out.println("用户名或密码错误！");
		}
	}

	// 使用Statement实现对数据表的查询操作
	public <T> T get(String sql, Class<T> clazz) {
		T t = null;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1.加载配置文件
			InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);

			// 2.读取配置信息
			String user = pros.getProperty("user");
			String password = pros.getProperty("password");
			String url = pros.getProperty("url");
			String driverClass = pros.getProperty("driverClass");

			// 3.加载驱动
			Class.forName(driverClass);

			// 4.获取连接
			conn = DriverManager.getConnection(url, user, password);

			st = conn.createStatement();

			rs = st.executeQuery(sql);

			// 获取结果集的元数据
			ResultSetMetaData rsmd = rs.getMetaData();

			// 获取结果集的列数
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {

				t = clazz.newInstance();

				for (int i = 0; i < columnCount; i++) {
					// //1. 获取列的名称
					// String columnName = rsmd.getColumnName(i+1);

					// 1. 获取列的别名
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. 根据列名获取对应数据表中的数据
					Object columnVal = rs.getObject(columnName);

					// 3. 将数据表中得到的数据，封装进对象
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return null;
	}
}

3.4 PreparedStatement

数据库的连接与关闭

每次都要进行数据库的连接和关闭
可以将开启关闭操作封装到工具类JDBCUtils

public class JDBCUtils {
	public static Connection getConnection() throws Exception{
		//通过类加载器 获取 数据库文件中的配置信息
		ClassLoader loader = ClassLoader.getSystemClassLoader();
		InputStream is = loader.getResourceAsStream("jdbc.properties");
		Properties info = new Properties();
		info.load(is);
		String user = info.getProperty("user");
		String password = info.getProperty("password");
		String url = info.getProperty("url");
		String driver = info.getProperty("driver");
		//通过反射加载驱动类
		Class.forName(driver);
		//连接数据库
		Connection con = DriverManager.getConnection(url, user, password);
		return con;
	}

	public static void closeResource(Connection con, Statement ps) {
		try {
			if (ps != null)
				ps.close();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		try {
			if (con != null)
				con.close();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
	}
}

对其增/删/改

增加操作

@Test
	public void insertTest(){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//开启连接
			conn = JDBCUtils.getConnection();

			//1.SQL预编译、获取PrepareStatement
			String sql = "INSERT INTO customers(NAME,email,birth) VALUES(?,?,?);";
			ps = conn.prepareStatement(sql);

			//2.填充占位符
			ps.setString(1,"哪吒");
			ps.setString(2,"[email protected]");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = sdf.parse("1000-01-01");
			ps.setDate(3,new Date(date.getTime()));

			//3. 执行sql
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//关闭连接
			JDBCUtils.closeResource(conn,ps);
		}
	}

删除操作

@Test
	public void deleteTest(){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//开启连接
			conn = JDBCUtils.getConnection();

			//1.SQL预编译、获取PrepareStatement
			String sql = "delete from customers where id = ?";
			ps = conn.prepareStatement(sql);

			//2.填充占位符
			ps.setObject(1,18);

			//3. 执行sql
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//关闭连接
			JDBCUtils.closeResource(conn,ps);
		}
	}

修改操作

@Test
	public void updateTest(){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//开启连接
			conn = JDBCUtils.getConnection();

			//1.SQL预编译、获取PrepareStatement
			String sql = "update customers set name = ? where id = ?;";
			ps = conn.prepareStatement(sql);

			//2.填充占位符
			ps.setObject(1,"莫扎特11");
			ps.setObject(2,18);

			//3. 执行sql
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//关闭连接
			JDBCUtils.closeResource(conn,ps);
		}
	}

增/删/改的通用操作

通过上面改的例子，可以看出，变化的只有SQL和填充占位符
所以可以将增删改操作改写成一个通用操作，每次只要传入SQL和变化参数即可

@Test
	public void commonUpdate(String sql, Object... args){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//数据库建立连接
			conn = JDBCUtils.getConnection();
			//预编译SQL
			ps = conn.prepareStatement(sql);
			//填充占位符
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			//执行SQL
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			//关闭连接
			JDBCUtils.closeResource(conn,ps);
		}
	}

查询

@Test
	public void queryTest() throws Exception {
		//连接数据库
		Connection conn = JDBCUtils.getConnection();
		//预编译SQL
		String sql = "SELECT id,`NAME`,email,birth FROM customers where id = ?;";
		PreparedStatement ps = conn.prepareStatement(sql);
		//填充占位符
		ps.setObject(1,1);
		//执行，获取结果集
		ResultSet res = ps.executeQuery();

		if(res.next()){ //判断结果集下一条是否有数据，有数据返回Ture，并指针下移，如果返回false，指针不会下移
			int id = res.getInt(1);
			String name = res.getString(2);
			String email = res.getString(3);
			Date birth = res.getDate(4);
			//打印方式一
			System.out.println("id:" + id + " name:" + name + " email:" + email+ " birth:" + birth);

			//打印方式二
			Object[] data = new Object[]{id,name,email,birth};
			System.out.println(Arrays.toString(data));

			//打印方式三
			Customer customer = new Customer(id, name, email, birth);
			System.out.println(customer);
		}
		
		//关闭资源
		JDBCUtils.closeResource(conn,ps,res);
	}

查询与增删改的区别，查询结果集ResultSet
使用next方法检查结果集中是否存在数据集
有数据则指针下移看，指向数据，无数据则指针不够。ResultSet.next方法类似iterate.hasNext与iterate.next的功能综合版本，但是不会返回数据
是通过索引获取当前行的数据
推荐使用方式三的方式存储数据，存储到对象之中

查询通用操作

1.针对单表查询

//Customer 表进行查询
public Customer CommonqueryTest(String sql,Object... args) throws Exception {
		//连接数据库
		Connection conn = JDBCUtils.getConnection();
		//预编译SQL
		PreparedStatement ps = conn.prepareStatement(sql);
		//填充占位符
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i+1,args[i]);
		}
		//返回结果集
		ResultSet res = ps.executeQuery();
		//获取结果集的元数据
		ResultSetMetaData rsmd = res.getMetaData();
		int columnCount = rsmd.getColumnCount();
		//判断结果集中是否存在对象，将指针指向数据行
		if(res.next())
		{
			Customer customer = new Customer();
			for (int i = 0; i < columnCount; i++) {
				//获取列名、值
				Object columnValue = res.getObject(i + 1);
				String columnName = rsmd.getColumnName(i + 1); //使用getColumnLabel代替
				//反射类字段值
				Field field = Customer.class.getDeclaredField(columnName);
				//访问私有字段
				field.setAccessible(true);
				//设置值
				field.set(customer,columnValue);
			}
			return customer;
		}
		return null;
	}

2.针对通用表查询

public <T> T CommonqueryTest2(Class<T> clazz, String sql,Object... args) throws Exception {
		//连接数据库
		Connection conn = JDBCUtils.getConnection();
		//预编译
		PreparedStatement ps = conn.prepareStatement(sql);
		//填充占位符
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i+1,args[i]);
		}
		//查询获取结果集
		ResultSet res = ps.executeQuery();
		//获取结果集的元数据
		ResultSetMetaData rsmd = res.getMetaData();
		int columnCount = rsmd.getColumnCount();
		//获取结果集
		if(res.next())
		{
			T t = clazz.newInstance();
			for (int i = 0; i < columnCount; i++) {
				//获取列名、值
				Object columnValue = res.getObject(i + 1);
				String columnName = rsmd.getColumnName(i + 1);
				//反射类字段值
				Field field = Customer.class.getDeclaredField(columnName);
				//访问私有字段
				field.setAccessible(true);
				//设置值
				field.set(t,columnValue);
			}
			return t;
		}
		return null;
	}

3.返回查询结果:将实例化的类作为参数传递进来后实例化

public <T> List<T> CommonqueryTest3(Class<T> clazz, String sql, Object... args) throws Exception {
		//连接数据库
		Connection conn = JDBCUtils.getConnection();
		//预编译
		PreparedStatement ps = conn.prepareStatement(sql);
		//填充占位符
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i+1,args[i]);
		}
		//查询获取结果集
		ResultSet res = ps.executeQuery();
		//获取结果集的元数据
		ResultSetMetaData rsmd = res.getMetaData();
		int columnCount = rsmd.getColumnCount();
		ArrayList<T> list = new ArrayList<>();
		//获取结果集
		while(res.next())
		{
			T t = clazz.newInstance();
			for (int i = 0; i < columnCount; i++) {
				//获取列名、值
				Object columnValue = res.getObject(i + 1);
				String columnName = rsmd.getColumnName(i + 1);
				//反射类字段值
				Field field = Customer.class.getDeclaredField(columnName);
				//访问私有字段
				field.setAccessible(true);
				//设置值
				field.set(t,columnValue);
			}
			list.add(t);
		}
		return list;
	}

总结：

数据库字段名要与Java类中属性名一致
- 根据ORM编程思想，数据库一行数据对应一个Java对象
- 数据库一个字段对应Java类的一个属性
- 所以当数据库字段与Java属性名不一致会报错
数据库字段名要与Java类中属性名不一致：则必须在SQL语句中给字段起别名

注意点：

结果集源数据获取列名时不要使用getColumnName方法，而是使用getColumnLabel代替
- getColumnName:获取字段名
- getColumnLabel:获取别名，没有别名时获取字段名。
ResultSetMetaData:结果集源数据：对结果集进行描述的数据
- 源数据：对数据进行描述的数据
- 源注解：对注解尽心描述的注解

对Order表进行查询的思维导图：
Order表查询图解

PreparedStatement对比Statement

拼串和SQL注入问题

# 1.Statement中，直接输入SQL语句进行执行，就会出现SQL注入问题
SELECT USER,PASSWORD FROM user_table WHERE USER = '1' OR ' AND PASSWORD = '='1' OR '1' = '1'

# 2.在PreparStatement中则是先进行SQL的预编译, 使用占位符，不用拼串，后续填充占位符，也不会改变SQL原有的逻辑，解决SQL注入
String sql = "SELECT * FROM admin WHERE username = ? AND PASSWORD = ?;";
PreparedStatement ps = conn.prepareStatement(sql);

Statement无法操作批量数据
repaerStatement便于批量操作，
- Statement批量操作：执行一次SQL语句，需要校验一次SQL
- repaerStatement批量操作：无论执行多少次SQL,都只校验一次，效率更高。
PreparedStatement 能最大可能提高性能

DBServer会对预编译语句提供性能优化。只要预编译语句有可能被重复调用，所以语句在被DNServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。

在Statement语句中，即使是相同操作但因为数据内容不一样，所以整个语句本身不能匹配，没有缓存语句的意义，事实是没有数据库会对普通话语句编译后的执行代码缓存。这样没执行一次都要对传入的语句编译一次。
（语法检查，语法检查，翻译成二进制命令，缓存）

操作Blob

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
插入BLOB类型的数据必须使用PreparedStatement,因为BLOB类型的数据无法使用字符串拼接写。
MySQL的四种BLOB类型（除了在存储的大量信息量上不同外，他们是等同的）
实际使用中根据需要存入的数据大小定义不同的BLOB类型
需要注意的是：如果存储的文件过大，数据库的性能会下降。
如果在指定了相关的Blob类型以后，还报错：XXX too large,那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：
max_allowed_packet=16M.同时注意：修改了my.in文件之后，需要重新启动mysql服务。

插入

@Test
	public void test1() throws Exception { //正式应该使用Try catch进行操作
		Connection conn = JDBCUtils.getConnection();
		String sql = "INSERT INTO customers(NAME,email,photo) VALUES(?,?,?);";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setObject(1,"Tom");
		ps.setObject(2,"[email protected]");
		InputStream is = new FileInputStream(new File("a1.jpeg"));
		ps.setBlob(3,is);
		ps.execute();
		is.close();
		JDBCUtils.closeResource(conn,ps);
	}

增删改都一样，使用update进行操作

查询

@Test
	public void test1() throws Exception { //正式应该使用Try catch进行操作
		Connection conn = JDBCUtils.getConnection();
		String sql = "SELECT NAME,email,photo FROM customers WHERE id = ?;";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setObject(1,27);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			String name = rs.getString("Name");
			String email = rs.getString("email");
			System.out.println("Name: " + name + "email: " + email);

			//将Blob类型的数据保存到本地
			Blob photo = rs.getBlob("photo");
			InputStream is = photo.getBinaryStream();
			FileOutputStream fis = new FileOutputStream(new File("pic.jpg"));
			byte[] buffer = new byte[1024];
			int len = 0;
			while((len = is.read(buffer)) != -1){
				fis.write(buffer,0,len);
			}
			fis.close();
			is.close();
		}
		JDBCUtils.closeResource(conn,ps);
	}

批量操作

方式一

使用Statement进行批量添加

@Test
	public void test3() throws Exception{
		Connection conn = JDBCUtils.getConnection();
		Statement st = conn.createStatement();
		long start = System.currentTimeMillis();
		for (int i = 0; i < 100000; i++) {
			String sql = "INSERT INTO goods(NAME) VALUES('Jack_"+i+"')";
			st.execute(sql);
		}
		long end = System.currentTimeMillis();
		System.out.println(end-start);
		JDBCUtils.closeResource(conn,st);
	}

------------------
142333

方式二

使用PreparedStatement进行批量添加

@Test
	public void test4() throws Exception{
		Connection conn = JDBCUtils.getConnection();
		String sql = "INSERT INTO goods(NAME) VALUES(?);";
		PreparedStatement ps = conn.prepareStatement(sql);
		long start = System.currentTimeMillis();
		for (int i = 0; i < 100000; i++) {
			ps.setObject(1,"jack_"+i);
			ps.execute();
		}
		long end = System.currentTimeMillis();
		System.out.println(end-start);
		JDBCUtils.closeResource(conn,ps);
	}
--------------------
142134

对比方式一和方式二：

方式一：每次都会创建字符串变量，并且对SQL进行检查
方式二：创建SQL缓存，不会多次创建，只对SQL检查一次
总结：理论上方式二在批量数据操作上，效率更高，但是运行时间差不多

方式三

方式一和方式二，每一条SQL都会去执行一次，效率比较慢
方式三通过将需要执行的SQL缓存下来，批量执行，提升效率

使用addBatch()/executBatch()/clearBatch()
mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理支持。
?rewriteBatchedStatements=true 写在配置文件的url后面

@Test
	public void test5() throws Exception{
		Connection conn = JDBCUtils.getConnection();
		String sql = "INSERT INTO goods(NAME) VALUES(?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		long start = System.currentTimeMillis();
		for (int i = 1; i <= 100000; i++) {
			ps.setObject(1,"jack_"+i);
			//缓存SQL
			ps.addBatch();
			if(i%500 == 0){
				//将缓存的SQL批量执行
				ps.executeBatch();
				//清理缓存
				ps.clearBatch();
			}
		}
		long end = System.currentTimeMillis();
		System.out.println(end-start);
		JDBCUtils.closeResource(conn,ps);
	}
--------------------
1374

方式四
在方式三的基础上再次进行迭代
将自动提交事务关闭，全部SQL执行完毕之后再提交

@Test
	public void test6() throws Exception{
		Connection conn = JDBCUtils.getConnection();
		//关闭事务自动提交
		conn.setAutoCommit(false);
		String sql = "INSERT INTO goods(NAME) VALUES(?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		long start = System.currentTimeMillis();
		for (int i = 1; i <= 1000000; i++) {
			ps.setObject(1,"jack_"+i);
			ps.addBatch();
			if(i%500 == 0){
				ps.executeBatch();
				ps.clearBatch();
			}
		}
		//提交事务
		conn.commit();
		long end = System.currentTimeMillis();
		System.out.println(end-start);
		JDBCUtils.closeResource(conn,ps);
	}

---------------------
//插入100w数据
8730

方式四效率更高

JDBC事务

数据一旦提交，就不可回滚
哪些操作会导致数据的自动提交
- DDL操作一旦执行，就会自行提交[set_autocommit=false 对DDL操作无效]
- DML默认情况下一旦执行，就会自动提交
- 默认再关闭连接时，会自动提交数据

模拟事务

调用Connection对象的setAutoCommit(false):以取消自动提交事务
再所有的SQL语句都成功执行后，调用commit():方法提交事务
再出现异常时，调用rollback();方法回滚事务
若此时Connection没有被关闭，还可能被重复使用，则需要恢复其自动提交状态setDutoCommit(true).

@Test
    public void test3(){
        Connection conn = null;
        try {
            //1. 获取连接
            conn = JDBCUtills.getConnection();
            //2. 开启事务
            conn.setAutoCommit(false);
            String sql1 = "UPDATE user_table SET balance = 1100 WHERE USER = ?";
            //3. 进行数据库操作
            updateWithTx(conn,sql1,"AA");
            //模拟网络异常
            System.out.println(10/0);
            String sql2 = "UPDATE user_table SET balance = 900 WHERE USER = ?";
            updateWithTx(conn,sql2,"BB");
            //4.提交事务
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //5.异常回滚
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        finally {
            try {
                //6.恢复DML自动提交,主要针对数据库连接池
                conn.setAutoCommit(true);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            //7.关闭资源
            JDBCUtills.closeResource(conn,null);
        }
    }

    //通用更新操作
    public void updateWithTx(Connection conn,String sql, Object... args){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            JDBCUtills.closeResource(null,ps);
        }
    }

再updateWithTx方法之中不要进行数据库的2关闭
数据库连接的关闭会导致事务自动提交

小知识

Oracle支持的2中事务隔离级别：READ COMMITED,SERIALIZABLE,默认的事务隔离级别为：READ COMMITED
Mysql支持四种事务隔离级别：Mysql默认事务隔离级别为：REPEATABLE READ

代码演示隔离级别问题

展示脏读/不可重复读/幻读再JDBC中用代码如何重现。事务并发补充章节

脏读

/**
     * 重现脏读
     * @return
     */
    @Test
    public void T1(){
        Connection con1 = null;
        List<user_table> res = null;
        try {
            con1 = JDBCUtills.getConnection();
            //设置隔离级别为read uncommitted
            con1.setTransactionIsolation(1);
            //开启事务
            con1.setAutoCommit(false);
            String sql1 = "Select * from user_table";
            //读取数据
            res = getInstance(con1, user_table.class, sql1);
            res.forEach(System.out::println);

            //T2修改表后再读取数据
            res = getInstance(con1, user_table.class, sql1);
            res.forEach(System.out::println);

            //T2回滚后再读取数据
            res = getInstance(con1, user_table.class, sql1);
            res.forEach(System.out::println);
            con1.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtills.closeResource(con1,null);
    }

    @Test
    public void T2(){
        Connection con1 = null;
        List<user_table> res = null;
        try {
            con1 = JDBCUtills.getConnection();
            con1.setTransactionIsolation(1);
            con1.setAutoCommit(false);
            String sql1 = "update user_table set  balance = 6666 where user = 'AA'";

            getUpdate(con1,sql1);

            con1.rollback();
        } catch (Exception e) {
            e.printStackTrace();
        }

        JDBCUtills.closeResource(con1,null);
    }

隔离级别：read uncommitted/read committed/repeatable read
脏读：T2在T1读取数据的时候，对数据进行了更新，后续对数据进行了回滚操作
所以T1在后续的两次读取中看到的数据并不相同.

不可重复读

@Test
    public void T1(){
        Connection con1 = null;
        List<user_table> res = null;
        try {
            con1 = JDBCUtills.getConnection();
            //设置隔离级别为read committed
            con1.setTransactionIsolation(2);
            //开启事务
            con1.setAutoCommit(false);
            String sql1 = "Select * from user_table";
            //第一次读取数据
            res = getInstance(con1, user_table.class, sql1);
            res.forEach(System.out::println);

            //T2提交后 T1第二次读取数据
            res = getInstance(con1, user_table.class, sql1);
            res.forEach(System.out::println);
            con1.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtills.closeResource(con1,null);
    }

    @Test
    public void T2(){
        Connection con1 = null;
        List<user_table> res = null;
        try {
            con1 = JDBCUtills.getConnection();
            con1.setTransactionIsolation(2);
            con1.setAutoCommit(false);
            String sql1 = "update user_table set  balance = 2333 where user = 'AA'";
            getUpdate(con1,sql1);
            con1.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtills.closeResource(con1,null);
    }

隔离级别：read committed/repeatable read
不可重复读：当T1第一次读取数据时与第二次读取数据中间的时间
T2对数据进行了更新，并且提交
然后T1再对数据进行读取，发现两次数据并不一致

幻读

    /**
     * 模拟幻读
     * @return
     */
    @Test
    public void T1(){
        Connection con1 = null;
        List<user_table> res = null;
        try {
            con1 = JDBCUtills.getConnection();
            //设置隔离级别为repeatable read
            con1.setTransactionIsolation(4);
            //开启事务
            con1.setAutoCommit(false);
            String sql1 = "Select * from user_table";
            //读取数据查看数据
            res = getInstance(con1, user_table.class, sql1);
            res.forEach(System.out::println);


            //等待T2插入数据后，进行更新操作
            String sql2 = "update user_table set password = 'ccc'";
            //输出受影响行数
            int count = getUpdate(con1, sql2);
            System.out.println(count);
            con1.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtills.closeResource(con1,null);
    }

    @Test
    public void T2(){
        Connection con1 = null;
        List<user_table> res = null;
        try {
            con1 = JDBCUtills.getConnection();
            con1.setTransactionIsolation(4);
            con1.setAutoCommit(false);
            String sql1 = "insert into user_table values('FF','aaa',3000)";
            getUpdate(con1,sql1);
            con1.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtills.closeResource(con1,null);
    }

隔离级别：read committed/repeatable read/repeatable read
幻读：当T1第一次读取数据后，T2插入了一条新数据，并且提交
T1再对数据进行了更新，发现受影响行数与第一次读取到数据行数不一致

Dao相关实现类

DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD，而不包含任何业务相关的信息。有时也称作：BaseDAO
作用：为了实现功能的模块化，更有利于代码的维护和升级

BaseDao的实现

public abstract class BaseDao {
    //事务通用查询
    public <T> List<T> getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
        List<T> list = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    //反射类所有字段，并且赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtills.closeResource(null, ps, rs);
        }
        return list;
    }

    //事务通用更新
    public <T> int getUpdate(Connection conn, String sql, Object... args) {
        List<T> list = null;
        PreparedStatement ps = null;
        int row = -1;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            row = ps.executeUpdate();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtills.closeResource(null, ps);
            return row;
        }
    }

    public <T> T getValue(Connection conn, String sql, Object... args) {
        Object val = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            val = null;
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                val = rs.getObject(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtills.closeResource(null, ps, rs);
        }
        return (T) val;
    }
}

Dao接口

public interface CustomerDao {
    /**
     * 将cust对象添加到数据库之中
     * @param conn
     * @param cust
     */
    void insert(Connection conn, Customer cust);

    /**
     * 根据id号删除数据库中customer数据
     * @param conn
     * @param id
     */
    void deleteById(Connection conn, int id);

    /**
     * 根据id号修改数据库中customer数据
     * @param conn
     * @param cust
     * @param id
     */
    void updateById(Connection conn,Customer cust, int id);

    /**
     * 根据id号查詢数据库中customer数据
     * @param conn
     * @param id
     */
    Customer getCustomerById(Connection conn, int id);

    /**
     * 查询数据库customer表中所有的数据
     * @param conn
     */
    List<Customer> getAll(Connection conn);

    /**
     * 查询数据库customer表中所有的行数
     * @param conn
     */
    long getCount(Connection conn);

    /**
     * 查询数据库customer表中年龄最大的生日
     * @param conn
     */
    Date getMaxBirth(Connection conn);
}

Dao接口实现类

public class CustomerDaoImpl extends BaseDao implements CustomerDao{

    @Override
    public void insert(Connection conn, Customer cust) {
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        getUpdate(conn,sql,cust.getName(),cust.getEmial(),cust.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from customers where id = ?";
        getUpdate(conn,sql,id);
    }

    @Override
    public void updateById(Connection conn, Customer cust, int id) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        getUpdate(conn,sql,cust.getName(),cust.getEmial(),cust.getBirth(),id);
    }

    @Override
    public Customer getCustomerById(Connection conn, int id) {
        String sql = "select name, email, birth from customers where id = ?";
        List<Customer> list = getInstance(conn, Customer.class, sql, id);
        return list.size() > 0 ? list.get(0) :null;
    }

    @Override
    public List<Customer> getAll(Connection conn) {
        String sql = "select name, email, birth from customers";
        List<Customer> list = getInstance(conn, Customer.class, sql);
        return list;
    }

    @Override
    public long getCount(Connection conn) {
        String sql = "select count(*) from customers";
        return  getValue(conn,sql);
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql = "select max(birth) from customers";
        return getValue(conn,sql);
    }
}

标签：ps,finish,String,javaJDBC,Connection,sql,null,conn
From： https://www.cnblogs.com/tkyd/p/17165683.html

javaJDBC(finish)