萌新学Java之渐入佳境五----数据库连接封装

封装注册

public class JDBCUtil1 {
//声明链接
private static Connection connection;

//注册驱动
static{
	//只注册一次
	try {
		Class.forName("com.mysql.jdbc.Driver");
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		//直接抛出运行时异常
		throw new RuntimeException("注册驱动失败");
	}
}

private JDBCUtil1(){
	
}

//获取链接方法
public static Connection getConnection(){
	//数据库地址
	String url = "jdbc:mysql://localhost:3306/mysql01";
	//账户
	String user = "root";
	//密码
	String password = "123456";
	//获取链接
	try {
		connection = DriverManager.getConnection(url, user, password);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		throw new RuntimeException("数据库链接失败");
	}
	return connection;
}

//关闭资源
public static void myClose(Connection connection,
						 	ResultSet resultSet,
						 	Statement statement){
	if (connection != null) {
		try {
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	if (resultSet != null) {
		try {
			resultSet.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	if (statement != null) {
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}}

使用封装方法

// 查询全表 将查询出来的每条数据 封装成一个对象
// 需要给每一张表 创建一个对应的实体类
Connection connection = JDBCUtil1.getConnection();
System.out.println(connection);

	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);
	}
	JDBCUtil1.myClose(connection, resultSet, statement);

便捷操作数据库

commons-dbutils(提供快速操作数据库方法)

三个核心类

• 1.DBUtils
• 提供了 closeQuitly()方法
• 安静关闭 不用处理异常
• 2.QueryRunner
• query(Connection conn,

•  	String sql,
  

•  	ResultSetHandler<T> rsh, 
  

•  	Object... params)
  

• 参数1:数据库的连接对象
• 参数2:SQL语句(可以使用?占位符)
• 参数3:查询后得到的结果集(可以选用不同类型的结果)
• 参数4:替换SQL语句的占位符
• update(Connection conn, String sql, Object… params)
• 3.ResultSetHandle (提供8个结果集)
• 查询的8个结果集
• ArrayHandler
• ArrayListHandler
• BeanHandler
• BeanListHandler
• ColumnListHandler
• ScalarHandler
• MapHandler
• MaplistHandler

测试ResultSetHandle

// 测试ArrayHandler

// 默认返回查询的第一条数据 把该数据放入了Object数组中

String sql = “select * from sort”;
QueryRunner runner = new QueryRunner();
Connection connection = JDBCUtil2.getConnection();

	Object[] query = runner.query(connection, sql, new ArrayHandler());
	for (Object object : query) {
		System.out.println(object);
	}
	DbUtils.close(connection);

//测试ArrayListHandler

	//可以返回多条记录 并且每一天记录放在Object[]记录中
	String sql = "select * from sort";
	QueryRunner runner = new QueryRunner();
	Connection connection = JDBCUtil2.getConnection();
	List<Object[]> query = runner.query(connection, sql, new ArrayListHandler());
	for (Object[] objects : query) {
		for (Object object : objects) {
			System.out.print(object + " ");
		}
		System.out.println();
	}
	DbUtils.close(connection);

//测试BeanHandler

	//注意:要封装的对象 必须符合javaBean规范
	//查询多条数据 默认只返回第一条
	String sql = "select * from sort";
	QueryRunner runner = new QueryRunner();
	Connection connection = JDBCUtil2.getConnection();
	Sort query = runner.query(connection, sql, new BeanHandler<Sort>(Sort.class));
	System.out.println(query);

//测试BeanListHandler(常用)

	//返回保存好的对象的List集合
	String sql = "select * from sort";
	QueryRunner runner = new QueryRunner();
	Connection connection = JDBCUtil2.getConnection();
	List<Sort> query = runner.query(connection, sql, new BeanListHandler<>(Sort.class));
	for (Sort sort : query) {
		System.out.println(sort);
	}

//测试ColumnListHandler

	//默认返回数据中的第一列数据
	String sql = "select * from sort";
	QueryRunner runner = new QueryRunner();
	Connection connection = JDBCUtil2.getConnection();
	List<Object> query = runner.query(connection, sql, new ColumnListHandler<>("sname"));
	for (Object object : query) {
		System.out.println(object);
	}

//测试ScalarHandler(获取聚合函数返回的数据 并且返回的是Long型)

	String sql = "select * from sort";
	QueryRunner runner = new QueryRunner();
	Connection connection = JDBCUtil2.getConnection();
	String sql2 = "select count(*) from sort";
	Long query = runner.query(connection, sql2, new ScalarHandler<Long>());
	System.out.println(query);

//测试MapHandler

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

//测试MapListHandler

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

DataSource 数据源(数据库连接池)

• 查询时 创建和销毁连接 耗费资源
• 使用数据库连接池 可以解决这个问题
• 查询时 会从数据库连接池中 找一个空闲的连接 去查询数据库
• 查询完毕后 不会销毁该连接 会重新放入连接池中
• java也提供一套规范来处理数据库连接池问题
• javax.sql DataSource
• 这套规范也是厂商来实现

//创建数据库连接池
BasicDataSource dataSource = new BasicDataSource();
//基础设置(账号密码数据库地址)

dataSource.setDriverClassName(“com.mysql.jdbc.Driver”);
dataSource.setUrl(“jdbc:mysql://localhost:3306/mysql01”);
dataSource.setUsername(“root”);
dataSource.setPassword(“123456”);

	//获取连接
	Connection connection = dataSource.getConnection();
	System.out.println(connection);

//通过数据库连接池 创建QueryRunner

  QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSource());

	String sql = "select * from sort";
	List<Sort> query = runner.query(sql, new BeanListHandler<Sort>(Sort.class));
	System.out.println(query);