数据库的使用

最新推荐文章于 2021-10-05 19:32:14 发布

小孤星

最新推荐文章于 2021-10-05 19:32:14 发布

阅读量250

点赞数 1

分类专栏： Java

本文链接：https://blog.youkuaiyun.com/guxin0729/article/details/82902755

版权

Java 专栏收录该内容

29 篇文章

订阅专栏

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

三个核心类

1.DBUtils
提供了closeQuietly()方法,安静关闭,不用处理异常

2.QueryRunner
query(Connection conn, String sql, ResultSetHandler rsh, Object… params)
参数1:数据库的链接对象
参数2:sql语句(可以使用?占位符)
参数3:查询后得到的结果集(可以选用不同类型的结果)
参数4:替换sql语句的占位符

更新(插入删除修改)
update(Connection conn, String sql, Object… params)

3.ResultSetHandler(提供8个结果集让你使用)

修改sid=8,sname=毛巾,sprice=200,sdesc=不好用

		QueryRunner runner = new QueryRunner();
		Connection connection = JDBCUitl1.getConnection();
		String sql = "update sort set sname=?,sprice=?,sdesc=? where sid=?";
		Object[] params = { "毛巾", 200, "不好用", 8 };
		int row = runner.update(connection, sql, params);
		System.out.println("受影响的行数: " + row);
		// 关闭资源
		DbUtils.close(connection);

删除ID=10的

		QueryRunner runner = new QueryRunner();
		Connection connection = JDBCUitl1.getConnection();
		String sql = "delete from sort where sid=?";

		int row = runner.update(connection, sql, 10);
		System.out.println("受影响的行数: " + row);
		DbUtils.closeQuietly(connection);

插入简易衣柜,30,免费赠送

		// 创建查询对象
		QueryRunner runner = new QueryRunner();
		Connection connection = JDBCUitl1.getConnection();
		String sql = "insert into sort values (null,?,?,?)";
		// 创建数组
		Object[] params = { "简易衣柜", 30, "免费赠送" };
		int row = runner.update(connection, sql, params);
		System.out.println("受影响的行数: " + row);
		// 关闭资源
		DbUtils.close(connection);

ResultSetHandler八个结果集的使用

测试ArrayHandler

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

默认返回查询的第一条数据把该数据放入 object数组返回

测试ArrayListHandler

		String sql = "select * from sort";
		QueryRunner runner = new QueryRunner();
		Connection connection = JDBCUitl2.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);

可以返回多条记录并且每一条记录放在object[]中,然后把多条记录放在list中给你返回来

测试BeanHandler

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

只返回第一条,注意:要封装的对象,必须符合javabean规范

测试BeanListHandler

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

返回保存好对象的list集合

测试ColumnListHandler

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

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

测试ScalarHandler

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

可以获取聚合函数返回的数据,返回的是long型

测试MapHandler

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

测试MapListHandler

		// MapListHandler
		String sql = "select * from sort";
		QueryRunner runner = new QueryRunner();
		Connection connection = JDBCUitl2.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();
		}

查询全表

		// 查询全表 并将查询出来的每条数据封装成一个对象
		// 需要给每张表 创建一个对应的实体类
		Connection connection = JDBCUitl1.getConnection();
		System.out.println(connection);
		String sql = "select * from sort";
		PreparedStatement statement = connection.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		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);
		}
		JDBCUitl1.myClose(connection, resultSet, statement);
	}

利用数据库链接池获取链接的工具类

public class DataSourceUtil {
	// 创建连接池对象
	private static BasicDataSource dataSource = new BasicDataSource();
	// 使用静态代码块 对数据库链接池进行设置
	static {
		// 基础设置 (账号 密码 数据库地址)
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost:3306/wljdbc01");
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		// 扩展设置
		// 初始化链接数(连接池初始化后 系统创建多少个链接)
		dataSource.setInitialSize(10);
		// 最大链接数(池中最多有多少个链接)
		dataSource.setMaxActive(10);
		// 最大空闲连接数(最多空闲多少个链接 多余的会被系统销毁)
		dataSource.setMaxIdle(5);
		// 设置最小空闲连接数(少于最小空闲 系统会帮你创建出链接)
		dataSource.setMinIdle(1);
	}

	private DataSourceUtil() {

	}

	// 获取数据库链接池的方法
	public static DataSource getDataSource() {
		return dataSource;
	}

}

封装一个数据库链接工具类

public class JDBCUitl1 {
	// 声明链接
	private static Connection connection;
	// 注册驱动
	static {
		// 只注册一次
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			// 直接抛运行时异常
			throw new RuntimeException("注册驱动失败");
		}
	}

	private JDBCUitl1() {
	}

	// 获取链接的方法
	public static Connection getConnection() {
		// 数据库地址
		String url = "jdbc:mysql://localhost:3306/wljdbc01";
		// 账号
		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) {
		// 关闭资源
		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();
			}
		}
	}
}

读取文件的数据库连接工具类

public class JDBCUitl2 {
	private static Connection connection;
	private static String driverClass;
	private static String url;
	private static String user;
	private static String password;

	private JDBCUitl2() {
	}

	static {
		// 只读一次文件
		readFile();
		// 只注册驱动一次
		try {
			Class.forName(driverClass);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// 运行时异常
			throw new RuntimeException("加载驱动异常");
		}
	}

	// 读文件方法
	public static void readFile() {
		// 通过类加载器直接获取bin文件夹下的文件的字节流
		// 获取类加载器
		InputStream ist = JDBCUitl2.class.getClassLoader().getResourceAsStream("db.properties");
		Properties properties = new Properties();
		try {
			properties.load(ist);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		driverClass = properties.getProperty("driverClass");
		url = properties.getProperty("url");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
	}

	// 获取连接
	public static Connection getConnection() {

		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, Statement statement, ResultSet resultSet) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

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

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

javax.sql DataSource是Java提供的一套规范来处理数据库链接池问题
这套规范是厂商来实现,我们只需要使用实现好的方法就行
方法有 DBCP C3P0
使用DBCP 需要导入两个jar包
commons-dbcp-1.4.jar
commons-pool-1.5.6.jar

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