@Test
public void  getConnection() throws Exception{
		String driverClass = null;
		String jdbcUrl = null;
		String user = null;
		String password = null;

		//读取类路径下的jdbc.properties 文件
		InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
		Properties properties = new Properties();
		properties.load(in);
		driverClass = properties.getProperty("driver");
		jdbcUrl = properties.getProperty("jdbcUrl");
		user = properties.getProperty("user");
		password = properties.getProperty("password");

		@SuppressWarnings("deprecation")
		Driver driver = (Driver) Class.forName(driverClass).newInstance();

		Properties info = new Properties();
		info.put("user", user);
		info.put("password", password);
		Connection connection = driver.connect(jdbcUrl, info);
		System.out.println(connection);

	}

  @Test
	public void testDriver() throws SQLException {
		//1、创建一个Driver实现类对象
		Driver driver = new com.mysql.cj.jdbc.Driver();

		//2、准备连接数据库的基本信息：url，user，password
		String url = "jdbc:mysql://127.0.1:3306/test?serverTimezone=GMT";
		Properties info = new Properties();
		info.put("user", "root");
		info.put("password", "password");

		//3、调用Driver接口的connect(url,info)获取数据库连接
		Connection connection = driver.connect(url, info);
		System.out.println(connection);

	}

  @Test
	public void testDriverManager() throws SQLException, IOException, ClassNotFoundException {
		String driverClass = null;
		String jdbcUrl = null;
		String user = null;
		String password = null;

		//1、读取类路径下的jdbc.properties 文件
		InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
		Properties properties = new Properties();
		properties.load(in);
		driverClass = properties.getProperty("driver");
		jdbcUrl = properties.getProperty("jdbcUrl");
		user = properties.getProperty("user");
		password = properties.getProperty("password");

		//2、加载数据库驱动程序
		Class.forName(driverClass);

		//3、通过DriverManager的getConnection()方法 获取数据库连接

		Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
		System.out.println(connection);
	}

  @Test
	public void testGetConnection2() throws Exception {
		System.out.println(getConnection2());
	}
	public Connection getConnection2() throws Exception {
		//1、准备连接数据库的4个字符串
		//1)创建Properties对象
		Properties properties = new Properties();
		//2)获取jdbc.properties对应的输入流
		InputStream in =
				this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");

		//3)加载2)对应的输入流
		properties.load(in);
		//4)具体决定user，password等4个字符串
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		String jdbcUrl = properties.getProperty("jdbcUrl");
		String driver = properties.getProperty("driver");

		//2、加载数据库驱动程序(对应的Driver实现类中有注册驱动的静态代码块)
		Class.forName(driver);
		//3、通过DriverManager的getConnection()方法获取数据库连接
		return DriverManager.getConnection(jdbcUrl, user, password);
	}

@Test
	/**
	  * 通过JDBC向指定的数据表中插入一条记录
		  * 1、Statement：用于执行SQL语句的对象
		  * 1）通过Connection的createStatement()方法来获取
		  * 2）通过executeUpdate(sql)可以执行SQL语句
		  * 3）传入的SQL可以是 insert delete update，但不能是select
		  *
		  * 2、Connection和Statement都是应用程序和数据库服务器的连接资源，使用后一定要关闭
		  * 1)要是有异常，可以在finally中关闭
		  *
		  * 3、关闭的顺序，先关闭后获取的，即先关闭Statement,后关闭Connection
	 *
	 */

	public void testStatement() throws Exception {
		//1、获取数据库连接
		Connection conn = getConnection2();
		//3、准备插入SQL语句
		String sql = "INSERT INTO customer(NAME,EMAIL,BIRTH) "
				+ "VALUES('xyz','xyz@atguigu.com','1991-10-25')";
		//4、执行插入

		//1)获取操作 SQL 语句的 Statement对象，
		//调用Connection的createStatement()方法来获取
		Statement statement = conn.createStatement();

		//2)调用Statement对象的executeUpdate(sql)执行SQL语句进行插入
		statement.executeUpdate(sql);
		//5、关闭Statement对象
		statement.close();
		//2、关闭连接
		conn.close();
	}

public void update(String sql) {
		Connection conn = null;
		Statement statement = null;

		try {
			conn = JDBCTools.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();

		}finally {
			JDBCTools.release(statement, conn);

		}
	}

/**
	 * ResultSet:结果集，封装了使用JDBC查询的结果
	 * 1、调用Statement对象的executeQuery(sql)可以得到结果集
	 * 2、ResultSet返回的实际上就是一张数据表，有一个指针指向数据表的第一行的前面，可以调用next()方法检测下一行是否有效，
	 * - 有效返回True，且指针下移，相当于Iterator对象的hasNext()和next()方法的结合体
	 * 3、当指针对位到一行时，可以通过getXxx(index)或者getXxx(columnName)获取每一列的值，
	 * - 如getInt(1),getString("name")
	 * 4、ResultSet也需要关闭
	 */
	@Test
	public void testResultSet() {
		//获取id=1的customers数据表的记录。并打印

		Connection conn = null;
		Statement statement = null;
		ResultSet rs = null;

		try {
			//1.  获取Connection
			conn = JDBCTools.getConnection();

			//2. 获取Statement
			statement = conn.createStatement();

			//3. 准备SQL
			String sql = "SELECT id,NAME,EMAIL,BIRTH "
					+ "FROM customer ";

			//4. 执行查询，得到ResultSet
			rs = statement.executeQuery(sql);

			//5.  处理ResultSet
			while(rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString("name");
				String email = rs.getString(3);
				Date birth = rs.getDate(4);

				System.out.println(id);
				System.out.println(name);
				System.out.println(email);
				System.out.println(birth);
			}

			//6、关闭数据库资源


		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCTools.release(rs, statement, conn);
		}

	}

package com.atguigu.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author User
 * 1、操作JDBC的工具类，其中封装了一些工具方法
 *
 */

public class JDBCTools {


	public static void release(ResultSet rs,
			Statement statement, Connection conn) {

		if(rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		if(statement != null) {
			try {
				statement.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

		if(conn != null) {
			try {
				conn.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			}
		}

	/**
	 * 1.关闭Statement和Connection
	 * @param statement
	 * @param conn
	 */
	public static void release(Statement statement, Connection conn) {
		if(statement != null) {
			try {
				statement.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

		if(conn != null) {
			try {
				conn.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

	}

	/**
	 *
	 * 1、获取数据连接的方法
	 * 通过读取配置文件从数据库服务器获取一个连接
	 *
	 */
	public static Connection getConnection() throws Exception {
		//1、准备连接数据库的4个字符串
		//1)创建Properties对象
		Properties properties = new Properties();
		//2)获取jdbc.properties对应的输入流
		InputStream in =
				JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");

		//3)加载2)对应的输入流
		properties.load(in);
		//4)具体决定user，password等4个字符串
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		String jdbcUrl = properties.getProperty("jdbcUrl");
		String driver = properties.getProperty("driver");

		//2、加载数据库驱动程序(对应的Driver实现类中有注册驱动的静态代码块)
		Class.forName(driver);
		//3、通过DriverManager的getConnection()方法获取数据库连接
		return DriverManager.getConnection(jdbcUrl, user, password);
	}

}

@Test
	public void testPreparedStatement() {
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = JDBCTools.getConnection();
			String sql = "INSERT INTO customer(NAME,EMAIL,BIRTH) "
					+ "VALUES(?,?,?)";

			ps = conn.prepareStatement(sql);
			ps.setString(1, "Kally");
			ps.setString(2, "kly@163.com");
			ps.setDate(3, new Date(new java.util.Date().getTime())) ;

			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCTools.release(ps, conn);
		}
	}

/**
	 * SQL 注入.
	 */
	@Test
	public void testSQLInjection() {
		String username = "a' OR PASSWORD = ";
		String password = " OR '1'='1";

		String sql = "SELECT * FROM users WHERE username = '" + username
				+ "' AND " + "password = '" + password + "'";

		System.out.println(sql);

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = JDBCTools.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);

			if (resultSet.next()) {
				System.out.println("登录成功!");
			} else {
				System.out.println("用户名和密码不匹配或用户名不存在. ");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCTools.release(resultSet, statement, connection);
		}
	}

@Test
	public void testInjection2() {
		String username = "a' OR PASSWORD = ";
		String password = " OR '1'='1";

		String sql = "SELECT * FROM users WHERE username = ?"
				+ " AND password = ?";

		System.out.println(sql);

		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet resultSet = null;

		try {
			connection = JDBCTools.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);

			resultSet = ps.executeQuery();
			if (resultSet.next()) {
				System.out.println("登录成功!");
			} else {
				System.out.println("用户名和密码不匹配或用户名不存在. ");
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCTools.release(resultSet, ps, connection);
		}
	}

/**
	 * ResultSetMetaData描述结果集的元数据
	 * 可以得到结果集中的基本信息：结果集中哪些列，列名，列的别名
	 */
	@Test
	public void testResultSetMetaData() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCTools.getConnection();

			String sql = "SELECT id,name costomerName,email,birth "
					+ "FROM customer";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			//1、得到ResultSetMetaData对象
			ResultSetMetaData rsmd = rs.getMetaData();

			//2、得到列的个数
			int columnCount = rsmd.getColumnCount();
			System.out.println(columnCount);

			for (int i = 0; i < columnCount; i++) {

				//3、得到列名
				String columnName = rsmd.getColumnName(i + 1);

				//4、得到列的别名
				String columnLabel = rsmd.getColumnLabel(i + 1);

				System.out.println(columnName + "," + columnLabel);
			}


		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCTools.release(rs, ps, conn);
		}
	}

/**
	 * 取得数据库自动生成的主键
	 */
	@Test
	public void testGetKeyValue() {

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = JDBCTools.getConnection();
			String sql = "INSERT INTO customer(name, email, birth)" +
					"VALUES(?,?,?)";
//			preparedStatement = connection.prepareStatement(sql);

			//使用重载的 prepareStatement(sql, flag)
			//来生成 PreparedStatement 对象
			preparedStatement = connection.prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);

			preparedStatement.setString(1, "ABCDE");
			preparedStatement.setString(2, "abcde@163.com");
			preparedStatement.setDate(3,
					new Date(new java.util.Date().getTime()));

			preparedStatement.executeUpdate();

			//通过 getGeneratedKeys() 获取包含了新生成的主键的 ResultSet 对象
			//在 ResultSet 中只有一列 GENERATED_KEY, 用于存放新生成的主键值.
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if(rs.next()){
				System.out.println(rs.getObject(1));
			}

			ResultSetMetaData rsmd = rs.getMetaData();
			for(int i = 0; i < rsmd.getColumnCount(); i++){
				System.out.println(rsmd.getColumnName(i + 1));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCTools.release(null, preparedStatement, connection);
		}

	}

/**
	 * 1. 加载 dbcp 的 properties 配置文件: 配置文件中的键需要来自 BasicDataSource
	 * 的属性.
	 * 2. 调用 BasicDataSourceFactory 的 createDataSource 方法创建 DataSource
	 * 实例
	 * 3. 从 DataSource 实例中获取数据库连接.
	 */
	@Test
	public void testDBCPWithDataSourceFactory() throws Exception{

		Properties properties = new Properties();
		InputStream inStream = JDBCTest.class.getClassLoader()
				.getResourceAsStream("dbcp.properties");
		properties.load(inStream);

		DataSource dataSource =
				BasicDataSourceFactory.createDataSource(properties);

		System.out.println(dataSource.getConnection());

//		BasicDataSource basicDataSource =
//				(BasicDataSource) dataSource;
//
//		System.out.println(basicDataSource.getMaxWait());
	}


/**
username=root
password=1234
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///testDataBase

initialSize=10
maxActive=50
minIdle=5
maxWait=5000
*/

@Test
	public void testJdbcTools() throws Exception{
		Connection connection = JDBCTools.getConnection();
		System.out.println(connection);
	}

	/**
	 * 1. 创建 c3p0-config.xml 文件,
	 * 参考帮助文档中 Appendix B: Configuation Files 的内容
	 * 2. 创建 ComboPooledDataSource 实例；
	 * DataSource dataSource =
	 *			new ComboPooledDataSource("helloc3p0");
	 * 3. 从 DataSource 实例中获取数据库连接.
	 */
	@Test
	public void testC3poWithConfigFile() throws Exception{
		DataSource dataSource =
				new ComboPooledDataSource("helloc3p0");

		System.out.println(dataSource.getConnection());

		ComboPooledDataSource comboPooledDataSource =
				(ComboPooledDataSource) dataSource;
		System.out.println(comboPooledDataSource.getMaxStatements());
	}

	@Test
	public void testC3P0() throws Exception{
		ComboPooledDataSource cpds = new ComboPooledDataSource();
		cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
		cpds.setJdbcUrl( "jdbc:mysql:///testDataBase" );
		cpds.setUser("root");
		cpds.setPassword("1230");

		System.out.println(cpds.getConnection());
	}

/**
c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

	<named-config name="helloc3p0">

		<!-- 指定连接数据源的基本属性 -->
		<property name="user">root</property>
		<property name="password">1230</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///atguigu</property>

		<!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
		<property name="acquireIncrement">5</property>
		<!-- 初始化数据库连接池时连接的数量 -->
		<property name="initialPoolSize">5</property>
		<!-- 数据库连接池中的最小的数据库连接数 -->
		<property name="minPoolSize">5</property>
		<!-- 数据库连接池中的最大的数据库连接数 -->
		<property name="maxPoolSize">10</property>

		<!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
		<property name="maxStatements">20</property>
		<!-- 每个连接同时可以使用的 Statement 对象的个数 -->
		<property name="maxStatementsPerConnection">5</property>

	</named-config>

</c3p0-config>
*/

/**
	 * 测试 QueryRunner 的 query 方法
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Test
	public void testResultSetHandler(){
		String sql = "SELECT id, name, email, birth " +
				"FROM customers";

		//1. 创建 QueryRunner 对象
		QueryRunner queryRunner = new QueryRunner();

		Connection conn = null;

		try {
			conn = JDBCTools.getConnection();
			/**
			 * 2. 调用 query 方法:
			 * ResultSetHandler 参数的作用: query 方法的返回值直接取决于
			 * ResultSetHandler 的 hanlde(ResultSet rs) 是如何实现的. 实际上, 在
			 * QueryRunner 类的 query 方法中也是调用了 ResultSetHandler 的 handle()
			 * 方法作为返回值的。
			 */
			Object object = queryRunner.query(conn, sql,
					new ResultSetHandler(){
						@Override
						public Object handle(ResultSet rs) throws SQLException {
							List<Customer> customers = new ArrayList<>();

							while(rs.next()){
								int id = rs.getInt(1);
								String name = rs.getString(2);
								String email = rs.getString(3);
								Date birth = rs.getDate(4);

								Customer customer =
										new Customer(id, name, email, birth);
								customers.add(customer);
							}

							return customers;
						}
					}

					);

			System.out.println(object);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCTools.releaseDB(null, null, conn);
		}

	}

/**
	 * 测试 ResultSetHandler 的 BeanListHandler 实现类
	 * BeanListHandler: 把结果集转为一个 Bean 的 List. 该 Bean
	 * 的类型在创建 BeanListHandler 对象时传入:
	 *
	 * new BeanListHandler<>(Customer.class)
	 *
	 */
	@Test
	public void testBeanListHandler(){
		String sql = "SELECT id, name customerName, email, birth " +
				"FROM customers";

		//1. 创建 QueryRunner 对象
		QueryRunner queryRunner = new QueryRunner();

		Connection conn = null;

		try {
			conn = JDBCTools.getConnection();

			Object object = queryRunner.query(conn, sql,
					new BeanListHandler<>(Customer.class));

			System.out.println(object);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCTools.releaseDB(null, null, conn);
		}
	}

@Test
	public void testMapHandler(){
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();

		String sql = "SELECT id, name customerName, email, birth " +
				"FROM customers WHERE id = ?";

		try {
			connection = JDBCTools.getConnection();
			Map<String, Object> map = queryRunner.query(connection,
					sql, new MapHandler(), 4);

			System.out.println(map);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCTools.releaseDB(null, null, connection);
		}
	}

@Test
	public void testMapListHandler(){
		Connection connection = null;
		QueryRunner queryRunner = new QueryRunner();

		String sql = "SELECT id, name, email, birth " +
				"FROM customers";

		try {
			connection = JDBCTools.getConnection();
			List<Map<String, Object>> mapList = queryRunner.query(connection,
					sql, new MapListHandler());

			System.out.println(mapList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			JDBCTools.releaseDB(null, null, connection);
		}
	}