package com.jdbctest;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
public class JDBCTest {
	@Test
	public void test() throws SQLException {
        // 1.创建一个 Driver 实现类的对象
		Driver driver = new com.mysql.jdbc.Driver();
        // 2.准备连接数据库的基本信息
		String url = "jdbc:mysql://localhost:3306/springstudy";
		Properties info = new Properties();
		info.put("user", "root");
		info.put("password", "123456");
        // 3.调用 Driver 接口的 connect(url,info) 获取数据库连接
		Connection connection = driver.connect(url, info);
		System.out.println(connection);
	}
}

jdbc.user=root
jdbc.password=123456
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/springstudy?useUnicode=true&characterEncoding=utf-8&useSSL=false

user=root
password=123456
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/springstudy?useUnicode=true&characterEncoding=utf-8&useSSL=false

public Connection getConnection() throws Exception {
		String jdbcUrl = null;
		String driverClass = null;
		String user = null;
		String password = null;
		// 读取类路径下的 db.propertis
		InputStream in =
		getClass().getClassLoader().getResourceAsStream("db.properties");
		Properties properties = new Properties();
		properties.load(in);
		driverClass = properties.getProperty("driverClass");
		jdbcUrl = properties.getProperty("jdbcUrl");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
		// 通过反射创建 Driver 对象
		Driver driver = (Driver) Class.forName(driverClass).newInstance();
		Properties info = new Properties();
		info.put("user", user);
		info.put("password", password);
		// 通过Driver 的connect方法获取数据库连接
		Connection connection = driver.connect(jdbcUrl, info);
		return connection;
}

public Connection getConnection2() throws Exception {
		// 1.准备连接数据库
		String driverClass = null;
		String jdbcUrl = null;
		String user = null;
		String password = null;
		// 2.读取类路径下的配置文件,获取 db.properties 对应的输入流
		InputStream in = getClass().getClassLoader().getResourceAsStream("db.properties");
		// 3.得到配置文件里的各个属性值
		Properties properties = new Properties();
		// 加载对应的输入流
		properties.load(in);
		driverClass = properties.getProperty("driverClass");
		jdbcUrl = properties.getProperty("jdbcUrl");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
		// 4.加载数据库驱动程序
		Class.forName(driverClass);
		// 5.通过 DriverManager 的 getConnection() 方法获取数据库连接
		return DriverManager.getConnection(jdbcUrl, user, password);	
	}

/*
	 * 通过 jdbc 向指定数据库插入一条记录
	 * 1.Statement:用于执行 SQL 语句的对象
	 * 1).通过 Connection 的 createStatement() 方法来获取
	 * 2).通过 executeUpdate(sql) 可以执行 SQL 语句
	 * 3). 传入的 SQL 可以是 insert,update或者delete。但不能是 select
	 * 
	 * 2.Connection,Statement 都是应用程序和数据库服务器的连接资源。使用后一定要关闭,需要在 finally 中关闭 Connection 和 Statement 对象
	 * 
	 * 3.关闭顺序是:先关闭Statement后关闭Connection
	 */
	@Test
	public void testStatement() throws Exception {
		// 1.获取数据库连接
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection2();
			// 2.准备插入的 SQL 语句
			String sql = "insert into jdbc1 (id, name, date, email) values(1, '李中贞', '1993-06-02', '295630519@qq.com')";
			// 3.执行插入
			// 1).获取操作 SQL 语句的 Statement 对象
			// 调用 Connection 的 createStatement() 方法来获取
			statement = connection.createStatement();
			// 2).调用 Statement 对象的 executeUpdate(sql) 执行 SQL 语句进行插入 
			statement.executeUpdate(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(statement != null) {
					// 4.关闭Statement
					statement.close();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if(connection != null) {
					// 5.关闭数据库连接
					connection.close();
				}
			}
		}
	}

public void release(ResultSet resultSet, Statement statement, Connection connection) {
		if(resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		if(statement != null) {
			try {
				statement.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
}

@Test
public void testOptimize() throws Exception {
	// 1.初始化
	Connection connection = null;
	Statement statement = null;
	ResultSet resultSet = null;
	try {
		// 2.获取数据库连接
		connection = getConnection2();
		statement = connection.createStatement();
		// 3.写sql
		String sql = "select * from jdbc1";
		// 4.发送 sql 语句:调用 Statement 对象的 executeQuery(sql) 方法
		// 得到结果集对象 ResultSet
		resultSet = statement.executeQuery(sql);
		// 5.处理结果集:
		// 5.1 调用 ResultSet 的 next() 方法: 查看结果集的下一条记录是否有效
		while(resultSet.next()) {
			int id = resultSet.getInt(1);
			System.out.println(id);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		release(resultSet, statement, connection);
	}
}