基于dbcp（stream与lambda）的简单应用

main方法实现

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.IntSummaryStatistics;
import java.util.List;
import java.util.stream.IntStream;
import java.util.stream.Stream;

public class App {

	private static Connection conn;
	private static Statement stat;

	public static void main(String[] args) {
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			// 按照课程查询
			sortByCourse();

			// 按照班级查询
			sortByClass();

			// 依照学号与姓名查询
			selectByDefault(1, "id");

			selectByDefault("优优", "name");

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void statistics_people(List<Integer> list) {
		IntSummaryStatistics stastic = list.stream().mapToInt((x) -> x).summaryStatistics();

		System.out.println(
				"课程最高分： " + stastic.getMax() + " 课程最低分： " + stastic.getMin() + " 课程平均分： " + stastic.getAverage());

		int li2 = (int) list.stream().mapToInt(x -> x).filter(x -> {
			return x < 60;
		}).summaryStatistics().getCount();

		System.out.println("低于60分的有：  " + li2);

		int li3 = (int) list.stream().mapToInt(x -> x).filter(x -> {
			return x >= 60 && x < 80;
		}).summaryStatistics().getCount();

		System.out.println("高于60分低于80的有：  " + li3);

		int li4 = (int) list.stream().mapToInt(x -> x).filter(x -> {
			return x >= 80;
		}).summaryStatistics().getCount();
		System.out.println("高于80的有：  " + li4);
	}

	public static void sortByCourse() {
		String sql = "select grade from cs";

		List<Integer> list = new ArrayList<Integer>();
		try {
			ResultSet rs = stat.executeQuery(sql);
			for (; rs.next();) {
				list.add(Integer.valueOf(rs.getInt(1)));
			}
			System.out.println("<----------------CS课程的统计结果：---------------------------------->");
			statistics_people(list);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void sortByClass() {
		String sql = "select distinct classes from cs";

		List<String> list = new ArrayList<String>();

		try {
			ResultSet rs = stat.executeQuery(sql);
			for (; rs.next();) {
				list.add(rs.getString(1));
			}

			list.stream().forEach((x) -> {
				sorted(x);
			});

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void sorted(String str) {
		String sql = "select grade from cs where classes=" + "'" + str + "'";
		try {
			ResultSet rs = stat.executeQuery(sql);

			List<Integer> list = new ArrayList<Integer>();
			for (; rs.next();) {
				list.add(Integer.valueOf(rs.getInt(1)));
			}
			System.out.println(" <-----------" + str + "----------------> 成绩总结：");
			statistics_people(list);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static <T> void selectByDefault(T t, String str) {
		System.out.println("<------------------------" + str + ":" + t.toString() + "  的成绩是：-------------------->");
		String sql = "select grade from cs where " + str + "=" + "'" + t.toString() + "'";
		try {
			ResultSet rs = stat.executeQuery(sql);
			for (; rs.next();) {
				System.out.println(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}

工具类

package S201726702142;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DBUtils {

	private static Properties properties = new Properties();

	private static BasicDataSource bds;

	static {
//		if (bds == null) {
//			bds = new BasicDataSource();
//		}

		InputStream is = null;
		try {
			is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
			properties.load(is);
			// 分别设置数据库的连接参数
//			bds.setDriverClassName(properties.getProperty("driverName"));
//			bds.setUrl(properties.getProperty("url"));
//			bds.setUsername(properties.getProperty("user"));
//			bds.setPassword(properties.getProperty("password"));

			if (bds == null) {
				bds = BasicDataSourceFactory.createDataSource(properties);
			}

		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static Connection getConnection() throws SQLException {
		return bds.getConnection();
	}
}

propoties配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
username=root
password=0203

marven依赖

		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.16</version>
		</dependency>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>3.8.1</version>
			<scope>test</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.6.0</version>
		</dependency>

数据库数据

小李	88	1班	1
大王	70	1班	2
云飞	65	2班	3
李云	99	3班	4
阿呆	45	1班	5
优优	88	3班	6
大力出奇迹	65	3班	7
小帅	66	1班	8