Java学习路程之数据库连接, common-dbutils和数据源

一.数据库连接
1.封装数据库连接

public class Day28{
	public static void main(String[] args) throws SQLException {
		//查询sort全表并且将查询出来的每条数据封装成一个对象
		//给每一张表创建一个实体类
		Connection connection = JDBCConnection.getConnection();
		//获取SQL语句执行对象
		Statement statement = connection.createStatement();
		String sql = "select * from sort";
		ResultSet resultSet = statement.executeQuery(sql);
		//结果处理
		while (resultSet.next()) {
			Sort sort = new Sort();
			sort.setSid(resultSet.getInt("sid"));
			sort.setSname(resultSet.getString("sname"));
			sort.setSprice(resultSet.getDouble("sprice"));
			sort.setSdesc(resultSet.getString("sdesc"));
			System.out.println(sort);
		}
		//关闭资源
		JDBCConnection.myClose(connection, resultSet, statement);
	}
}

public class JDBCConnection {
	//声明连接
	private static Connection connection;
	//注册驱动 在静态代码块中会只注册一次
	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			//抛出运行时异常停止程序
			throw new RuntimeException("注册驱动失败");
		}
	}
	private JDBCConnection(){
	}
	//获取连接方法
	public static Connection getConnection(){
		//数据库地址
		String url = "jdbc:mysql://localhost:3306/ldmysql01";
		/数据库/用户名
		String user = "root";
		//数据库密码
		String password = "123456";
		try {
			//获取连接对象
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("数据库连接失败");
		}
		return connection;
	}
	//关闭资源方法
	public static void myClose(Connection connection, ResultSet resultSet, Statement statement){
		//当使用try catch处理异常时,发生异常时,不影响下面代码的执行.当自己抛出异常时,异常发生会导致程序中断.
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

二.common-dbutils 工具类库
1.dbutils类
closeQuietly(Connection conn)方法关闭资源
2.queryrunner类
query(Connection conn, String sql, ResultSetHandler rsh, Object… params) 查询
参数1:数据库连接对象 参数2:SQL语句(可使用占位符) 参数3:查询后得到的结果集(可以选用不同类型的结果集) 参数4:替换SQL语句占位符
update(Connection conn, String sql, Object… params) 更新
插入数据

    //创建查询对象
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	String sql = "insert into sort values (null, ?, ?, ?)";
	//创建数组 传参数
	Object[] params = {"热风鞋子", 100, "好穿不贵"};
	int row = queryRunner.update(connection, sql, params);
	System.out.println("受影响的行数" + row);
	//关闭资源
	DbUtils.close(connection);

删除 id为10的数据

	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	String sql = "delete from sort where sid=?";
	int row = queryRunner.update(connection, sql, 10);
	System.out.println("受影响的行数" + row);
	DbUtils.close(connection);

修改id=8 毛巾 100 柔软

	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	String sql = "update sort set sname=?, sprice=?, sdesc=? where sid=?";
	Object[] params = {"毛巾", 100, "柔软", 8};
	int row = queryRunner.update(connection, sql, params);
	System.out.println("受影响的行数" + row);
	DbUtils.close(connection);

3.ResaultSetHandle 提供结果集
ArrayHandler 默认返回查询的第一条数据

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	Object[] query = queryRunner.query(connection, sql, new ArrayHandler());
	for (Object object : query) {
		System.out.println(object);
	}
	DbUtils.close(connection);

ArrayListHandler 返回多条记录

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	List<Object[]> query = queryRunner.query(connection, sql, new ArrayListHandler());
	for (Object[] objects : query) {
		for (Object object : objects) {
			System.out.print(object+ " ");
		}
		System.out.println();
	}
	DbUtils.close(connection);

BeanHandler 默认只返回第一条数据

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	Sort sort = queryRunner.query(connection, sql, new BeanHandler<>(Sort.class));
	System.out.println(sort);
	DbUtils.close(connection);

BeanListHandler 返回保存好的对象的list集合,返回所有数据

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	List<Sort> query = queryRunner.query(connection, sql, new BeanListHandler<>(Sort.class));
	for (Sort sort : query) {
		System.out.println(sort);
	}
	DbUtils.close(connection);

ColumnListHandler 默认返回数据库中第一列数据,可以传入参数(字段名)

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	List<Object> query = queryRunner.query(connection, sql, new ColumnListHandler<>("sname"));
	for (Object object : query) {
		System.out.println(object);
	}
	DbUtils.close(connection);

ScalarHandler 获取聚合函数返回的数据并且返回的是long型

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	String sql2 = "select count(*) from sort";
	Long query = queryRunner.query(connection, sql2, new ScalarHandler<Long>());
	System.out.println(query);
	//或
	/*Number number = queryRunner.query(connection, sql2, new ScalarHandler<Long>());
	System.out.println(number.longValue());*/
	DbUtils.close(connection);

MapHandler 默认返回第一条数据

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	Map<String, Object> map = queryRunner.query(connection,sql, new MapHandler());
	for (String key : map.keySet()) {
		System.out.println(key + " "+ map.get(key));
	}
	DbUtils.close(connection);

MapListHandler 返回多条记录

	String sql = "select * from sort";
	QueryRunner queryRunner = new QueryRunner();
	Connection connection = JDBCutil1.getConnection();
	List<Map<String,Object>> query = queryRunner.query(connection, sql, new MapListHandler());
	for (Map<String, Object> map : query) {
		for (String key : map.keySet()) {
			System.out.print(key +" "+ map.get(key));
		}
		System.out.println();
	}

三.DataSource 数据源
查询时创建和销毁连接耗费资源,使用数据源可以解决这个问题,查询时会从数据库连接池找出一个空闲的连接去查询数据库,查询完毕后不会销毁连接会重新放入连接池中

	// 创建数据库连接池
	BasicDataSource dataSource = new BasicDataSource();
	//基础设置 账号密码 数据库地址
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");
	dataSource.setUrl("jdbc:mysql://localhost:3306/ldmysql01");
	dataSource.setUsername("root");
	dataSource.setPassword("123456");
	// 获取连接
	Connection connection = dataSource.getConnection();
	System.out.println(connection);

封装dataSource工具类

public class DBCPUtils {
    private static BasicDataSource source = new BasicDataSource();
    // 在静态代码块中配置池子
    static{
        source.setDriverClassName("com.mysql.jdbc.Driver");
        source.setUrl("jdbc:mysql://localhost:3306/day07");
        source.setUsername("root");
        source.setPassword("123456");
        // 池子对象初始化默认的连接对象个数
        source.setInitialSize(10);
        // 最大空闲连接个数
        source.setMaxIdle(5);
        // 最小空闲连接个数
        source.setMinIdle(2);
        // 最大活跃个数
        source.setMaxActive(8);
    }
    // 静态方法获取连接池对象
    public static DataSource getSource(){
        return source;
    }
    // 返回连接对象的方法
    public static Connection getConnection() throws SQLException{
        return source.getConnection();
    }
}
	// 通过数据库连接池创建 QueryRunner对象
	QueryRunner queryRunner = new QueryRunner(DBCPUtils.getSource());
	String sql = "select * from sort";
	List<Sort> query = queryRunner.query(sql, new BeanListHandler<>(Sort.class));
	System.out.println(query);