package top.qaqaq.P48.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtils {

	/**
	 * 
	 * @Description 获取数据库的连接
	 * @author RichieZhang
	 * @date 2022年11月26日下午4:00:53
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		// 1.读取配置文件中的4个基本信息
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

		Properties pros = new Properties();
		pros.load(is);

		String user = pros.getProperty("user");
		String password = pros.getProperty("password");
		String url = pros.getProperty("url");
		String driverClass = pros.getProperty("driverClass");

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

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

	}

	/**
	 * 
	 * @Description 使用C3P0的数据库连接池技术
	 * @author RichieZhang
	 * @date 2022年11月28日下午7:01:06
	 * @return
	 * @throws SQLException
	 */
	// 数据库连接池只需提供一个即可。
	private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");

	public static Connection getConnection1() throws SQLException {
		Connection conn = cpds.getConnection();

		return conn;
	}

	/**
	 * 
	 * @Description 使用DBCP数据库连接池技术获取数据库连接
	 * @author RichieZhang
	 * @date 2022年11月28日下午8:16:11
	 * @return
	 * @throws Exception
	 */
	private static DataSource source;
	static {
		try {
			Properties pros = new Properties();

			FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));

			pros.load(is);
			// 创建一个DBCP数据库连接池
			source = BasicDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection2() throws Exception {

		Connection conn = source.getConnection();

		return conn;
	}

	/**
	 * 使用Druid数据库连接池技术
	 */
	private static DataSource source1;
	static {
		try {
			Properties pros = new Properties();

			InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");

			pros.load(is);

			source1 = DruidDataSourceFactory.createDataSource(pros);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection3() throws SQLException {

		Connection conn = source1.getConnection();

		return conn;
	}

	/**
	 * 
	 * @Description 关闭连接和Statement的操作
	 * @author RichieZhang
	 * @date 2022年11月26日下午4:04:17
	 * @param conn
	 * @param ps
	 */
	public static void closeResource(Connection conn, Statement ps) {
		try {
			if (ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 
	 * @Description 关闭资源操作
	 * @author RichieZhang
	 * @date 2022年11月26日下午5:29:47
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	public static void closeResource(Connection conn, Statement ps, ResultSet rs) {
		try {
			if (ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}

package top.qaqaq.P48.juint;

import static org.junit.jupiter.api.Assertions.*;

import java.sql.Connection;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.List;

import org.junit.jupiter.api.Test;

import top.qaqaq.P48.util.JDBCUtils;
import top.qaqaq.P43.bean.Customer;
import top.qaqaq.P45.dao.CustomerDAOImpl;

class CustomerDAOImplTest {
	
	private CustomerDAOImpl dao = new CustomerDAOImpl();

	@Test
	void testInsert() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Customer cust = new Customer(1, "张小飞", "xiaofei@126.com", new Date(42131114141214L));
			dao.insert(conn, cust);
			
			System.out.println("添加成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
		
	}

	@Test
	void testDeleteById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			dao.deleteById(conn, 13);
			
			System.out.println("删除成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
		
	}

	@Test
	void testUpdateConnectionCustomer() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date birth = sdf.parse("2000-01-01");
			
			Customer cust = new Customer(18,"贝多芬","beiduofen@126.com",new Date(birth.getTime()));
			dao.update(conn, cust);
			
			System.out.println("修改成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
		
	}

	@Test
	void testGetCustomerById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection3();
			
			Customer cust = dao.getCustomerById(conn, 19);
			
			System.out.println(cust);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
		
	}

	@Test
	void testGetAll() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			List<Customer> list = dao.getAll(conn);
			
			list.forEach(System.out::println);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
		
	}

	@Test
	void testGetCount() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Long count = dao.getCount(conn);
			
			System.out.println("表中的记录数为：" + count);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
		
	}

	@Test
	void testGetMaxBirth() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Date maxBirth = dao.getMaxBirth(conn);
			
			System.out.println("最大的生日为：" + maxBirth);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
		
	}

}