java核心技术第四篇之JDBC第二篇

/*	   
dbcpcongif.properties(配置文件)

		#连接设置--必须设置的
	driverClassName=com.mysql.jdbc.Driver
	url=jdbc:mysql://localhost:3306/hei66_day21
	username=root
	password=1233

	#可选设置
	#<!-- 初始化连接 -->
	initialSize=10

	#最大连接数量
	maxActive=50

	#<!-- 最大空闲连接 -->
	maxIdle=20

	#<!-- 最小空闲连接 -->
	minIdle=5

	#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
	maxWait=60000


	#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
	#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
	connectionProperties=useUnicode=true;characterEncoding=gbk

	#指定由连接池所创建的连接的自动提交（auto-commit）状态。
	defaultAutoCommit=true

	#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
	#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
	defaultTransactionIsolation=READ_UNCOMMITTED

3).使用连接池：
	1).读取配置文件信息：
		Properties pro = new Properties();
		pro.load(当前类名.class.getClassLoader().getResourceAsStream("配置文件的名"));//运行时会默认从bin目录下查找资源文件
	2).创建连接池对象
		BasicDataSource dataSource = (BasicDataSource)BasicDataSourceFactory.createDataSource(pro);
	3).从连接池中获取Connection对象：
		Connection conn = dataSource.getConnection();
	4).发送SQL语句
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select * from products");
		while(rs.next()){
			System.out.println(rs.getInt("pid") + "\t" + rs.getString("pname"));
		}
		rs.close();
		stmt.close();
		conn.close();//不是关闭，是回收
	注意：配置文件中的四项必须配置项中的"键名"不要更改，是固定的名字，由DBCP内部读取。

<!-- 命名的配置 -->
<named-config name="baidu">
	<!-- 连接数据库的4项基本参数 -->
	<property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/day30_db</property>
	<property name="user">root</property>
	<property name="password">123</property>
	<!-- 如果池中数据连接不够时一次增长多少个 -->
	<property name="acquireIncrement">5</property>
	<!-- 初始化连接数 -->
	<property name="initialPoolSize">20</property>
	<!-- 最小连接数 -->
	<property name="minPoolSize">10</property>
	<!-- 最大连接数 -->
	<property name="maxPoolSize">40</property>
	<!-- -JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量 -->
	<property name="maxStatements">0</property>
	<!-- 连接池内单个连接所拥有的最大缓存statements数 -->
	<property name="maxStatementsPerConnection">5</property>
</named-config>
<!-- 命名的配置 -->
<named-config name="heima">
	<!-- 连接数据库的4项基本参数 -->
	<property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/day25_db</property>
	<property name="user">root</property>
	<property name="password">123</property>
	<!-- 如果池中数据连接不够时一次增长多少个 -->
	<property name="acquireIncrement">5</property>
	<!-- 初始化连接数 -->
	<property name="initialPoolSize">20</property>
	<!-- 最小连接受 -->
	<property name="minPoolSize">10</property>
	<!-- 最大连接数 -->
	<property name="maxPoolSize">40</property>
	<!-- -JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量 -->
	<property name="maxStatements">0</property>
	<!-- 连接池内单个连接所拥有的最大缓存statements数 -->
	<property name="maxStatementsPerConnection">5</property>
</named-config>

3).使用连接池：
	1).创建连接池对象：
		ComboPooledDataSource dataSource = new ComboPooledDataSource();//寻找配置文件中的默认配置
		或者
		ComboPooledDataSource dataSource = new ComboPooledDataSource("baidu");//寻找配置文件中的命名配置
	2).获取连接对象：
		Connection conn = dataSource.getConnection();
	3).发送SQL语句：
		....
		conn.close();//不是关闭，是回收
大家要掌握：
1.jar包位置，添加构建路径；
2.c3p0-config.xml文件的位置，内部的必须项的四项的修改：driverClass、jdbcUrl、user、password
3.ComboPooledDataSource的创建的两种方式；
4.以及后面要讲的DBUtils工具类；

......
//遍历结果集
while(rs.next()){//遍历，封装的过程比较繁琐，DBUtils工具包就可以很方便的封装JavaBean
	Products pro = new Products();
	pro.setPid(rs.getInt("pid"));
	pro.setPname(rs.getString("pname"));
	pro.setPrice(rs.getInt("price"));
	pro.setFlag(rs.getInt("flag"));
	pro.setCategory_id(rs.getInt("category_id"));
	
	//将对象添加到集合中
	proList.add(pro);
}
.....

	//3.执行查询
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery("select * from products");
	//定义集合
	List<Products> proList = new ArrayList<>();
	//遍历结果集
	while(rs.next()){
		Products pro = new Products();
		pro.setPid(rs.getInt("pid"));
		pro.setPname(rs.getString("pname"));
		pro.setPrice(rs.getInt("price"));
		pro.setFlag(rs.getInt("flag"));
		pro.setCategory_id(rs.getInt("category_id"));
		
		//将对象添加到集合中
		proList.add(pro);
	}
	//释放资源
	rs.close();
	stmt.close();
	conn.close();
	
	//遍历集合
	for(Products p : proList){
		System.out.println(p);
	}
	
}

	//2.修改
	public void update() throws Exception{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
		//2.执行SQL语句--修改
		String sql = "update products set pname = ?,price = ?,flag = ?,category_id = ? where pid = ?";
		int row = qr.update(sql,"索尼电视2",8002,0,3,12);
		System.out.println("修改影响的行数：" + row);
	}
	//3.删除
	@Test
	public void delete() throws Exception{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
		//2.执行SQL语句--删除
		String sql = "delete from products where pid = ?";
		int row = qr.update(sql,12);
		System.out.println("删除影响的行数：" + row);
	}

//2.修改
public void update() throws Exception{
	//1.创建QueryRunner对象
	QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
	//2.执行SQL语句--修改
	String sql = "update products set pname = ?,price = ?,flag = ?,category_id = ? where pid = ?";
	int row = qr.update(sql,"索尼电视2",8002,0,3,12);
	System.out.println("修改影响的行数：" + row);
}
//3.删除
public void delete() throws Exception{
	//1.创建QueryRunner对象
	QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
	//2.执行SQL语句--删除
	String sql = "delete from products where pid = ?";
	int row = qr.update(sql,12);
	System.out.println("删除影响的行数：" + row);
}

//4.查询
@Test
public void select() throws Exception{
	//1.创建QueryRunner对象
	QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
	//2.执行SQL语句--查询
	//1).ArrayHandler:将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值
	/*String sql = "select * from products where category_id = 20";
	Object[] objArray = qr.query(sql, new ArrayHandler());
	for(Object o : objArray){
		System.out.println(o);
	}*/
	
	//2).List<Object[]> ArrayListHandler:用于查询多条记录。将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
	/*String sql = "select * from products";
	List<Object[]> objList = qr.query(sql, new ArrayListHandler());
	for(Object[] objArray  : objList){//遍历行
		for(Object o : objArray){//遍历列
			System.out.print(o + "\t");
		}
		System.out.println();
	}
	*/
	//3).BeanHandler:将结果集中第一条记录封装到一个指定的javaBean中。
	/*String sql = "select * from products where pid = 1";
	Products pro = qr.query(sql, new BeanHandler<Products>(Products.class));
	System.out.println(pro);*/
	//4).BeanListHandler:将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
	/*String sql = "select * from products";
	List<Products> proList = qr.query(sql, new BeanListHandler<Products>(Products.class));
	for(Products p : proList){
		System.out.println(p);
	}*/
	
	//5).ColumnListHandler:查询某个列的数据，将结果集中指定的列的字段值，封装到一个List集合中
	/*String sql = "select pname from products";
	List<Object> objList = qr.query(sql, new ColumnListHandler());
	for(Object o : objList){
		System.out.println(o);
	}
	*/
	//6).KeyedHandler:将结果集中每一条记录封装到Map<String,Object>,在将这个map集合做为另一个Map的value,另一个Map集合的key是指定的字段的值。
	/*String sql = "select * from products";
	Map<Object, Map<String, Object>> map = qr.query(sql, new KeyedHandler());
	Set<Object> keys = map.keySet();
	for(Object k : keys){
		System.out.println(k);
		Map<String,Object> map2 = map.get(k);
		Set<String> keys2 = map2.keySet();
		for(String k2 : keys2){
			System.out.println("\t" + k2 + " -- " + map2.get(k2));
		}
	}*/
	
	//7).MapHandler	将结果集中第一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值
	/*String sql = "select * from products where pid = 1";
	Map<String, Object> map = qr.query(sql, new MapHandler());
	Set<String> keys = map.keySet();
	for(String k : keys){
		System.out.println("键：" + k + " 值：" + map.get(k));
	}*/
	
	//8).MapListHandler	将结果集中每一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值，在将这些Map封装到List集合中。
	/*String sql = "select * from products";
	List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler());
	for(Map<String,Object> map : mapList){
		Set<String> keys = map.keySet();
		for(String k : keys){
			System.out.println("键：" + k + " 值：" + map.get(k));
		}
		System.out.println("--------------------------------");
	}

public static DataSource getDataSource(){
	return dataSource;
}

//1.添加
@Test
public void save() throws Exception {
	//1.创建一个	QueryRunner对象--使用连接池创建
	//核心类
	//QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
	
	QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
	//2.执行SQL语句--添加
	//String sql = "insert into car values(?,?,?,?,?)";
	String sql = "insert into car (id,cname) values (?,?)";
	//3.参数
	Object[] params = {14,"捷达"};
	//int update = qr.update(sql,11,"朗逸","大众","德国",14);
	int update = qr.update(sql,params);
	System.out.println("添加影响的行数:" + update);
}

//2.修改
@Test
public void update() throws Exception {
	//1.创建QueryRunner对象
	QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
	//2.执行SQL语句--修改
	String sql = "update car set cname = ?,company = ?,grade = ?,price = ? where id = ?";
	//3.参数
	Object[] params = {"索纳塔","大众","德国",10,14};
	int update = qr.update(sql, params);
	//int update = qr.update(sql,"宝来","大众","德国",13,11);
	System.out.println("修改影响的行数:" + update);
}

//3.删除
@Test
public void delete() throws Exception {
	QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
	//2.sql
	String sql = "delete from car where id = ?";
	//3.参数
	Object[] parms = {10};
	int update = qr.update(sql,parms);
	//int update = qr.update(sql,14);
	System.out.println("删除影响的行数:" + update);
}

//4.通过ID查询
@Test
public void findById() throws Exception {
	QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
	String sql = "select * from car where id = ?";
	//3.参数
	Object[] parms = {8};
	Products update = qr.query(sql, new BeanHandler<Products>(Products.class),parms);
	//Products query = qr.query(sql, new BeanHandler<Products>(Products.class),9);
	System.out.println(update);
}
//5.查询所有
@Test
public void findAll() throws Exception {
	/*QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
	String sql = "select * from car";
	List<Products> query = qr.query(sql, new BeanListHandler<Products>(Products.class));
	for (Products products : query) {
		System.out.println(products);
	}*/
	QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
	String sql = "select * from car";
	Object[] parms = {};
	List<Products> query = qr.query(sql, new BeanListHandler<Products>(Products.class),parms);
	for (Products products : query) {
		System.out.println(products);
	}	
}

//6.查询总记录
@Test
public void count() throws Exception {
	/*QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
	String sql = "select avg(price) from car";
	Object query = qr.query(sql, new ScalarHandler());
	System.out.println(query);*/
	QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
	String sql = "select count(*) from car";
	Object[] parms = {};
	Object query = qr.query(sql, new ScalarHandler(),parms);
	System.out.println(query);
}

3.创建连接池：
	1).读取配置文件：
		Properties pro = new Properties();
		pro.load(当前类名.class.getClassLoader().getResourceAsStream("配置文件名"));
	2).通过工厂类获取连接池对象
		BasicDataSource dataSource = (BasicDataSource)BasicDataSourceFactory.createDataSource(pro);
	3).获取Connection对象
		Connection conn = dataSource.getConncetion();
		...
		conn.close();//不是关闭，是回收

2.发送SQL语句：
	1).public int update(String sql,Object ... params):用于执行添加、修改、删除语句的。
	2).public T query(String sql,ResultSetHandler<T> rsh,Object ... params):用于执行查询语句的。

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

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

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