package?com.atguigu.jdbc;    import?java.io.FileInputStream;  import?java.io.FileNotFoundException;  import?java.io.IOException;  import?java.sql.Connection;  import?java.sql.SQLException;  import?java.util.Properties;    import?javax.sql.DataSource;    import?org.apache.commons.dbcp.BasicDataSource;  import?org.apache.commons.dbcp.BasicDataSourceFactory;  import?org.junit.Test;    public?class?DBCPTest?{  	@Test  	public?void?test2()?throws?Exception?{  		Properties?properties?=?new?Properties();  		properties.load(new?FileInputStream("dbcp.properties"));  		DataSource?dataSource?=?BasicDataSourceFactory.createDataSource(properties);  		System.out.println("inital:"?+?((BasicDataSource)dataSource).getInitialSize());  		System.out.println("getMaxActive:"?+?((BasicDataSource)dataSource).getMaxActive());  		System.out.println("getMaxIdle:"?+?((BasicDataSource)dataSource).getMaxIdle());  		Connection?connection?=?dataSource.getConnection();  		System.out.println(connection);  		connection.close();  	}  }

package?com.atguigu.jdbc;    import?java.beans.PropertyVetoException;  import?java.sql.Connection;  import?java.sql.SQLException;    import?javax.sql.DataSource;    import?org.junit.Test;    import?com.mchange.v2.c3p0.*;    public?class?C3P0Test?{  	@Test  	public?void?test1()?throws?PropertyVetoException,?SQLException?{  		DataSource?dataSource?=?new?ComboPooledDataSource("mysql-config");?//?它會(huì)默認(rèn)自動(dòng)去讀取文件  		System.out.println(dataSource);  		Connection?connection?=?dataSource.getConnection();  		System.out.println(connection);  		connection.close();//?把連接歸還給連接池  		  		DataSources.destroy(dataSource);//?完全釋放池中所有連接,并銷毀連接池!!  	}		  		  	@Test  	public?void?test2()?throws?PropertyVetoException,?SQLException?{  		DataSource?dataSource?=?new?ComboPooledDataSource("oracle-config");?//?它會(huì)默認(rèn)自動(dòng)去讀取文件  		System.out.println(dataSource);  		Connection?connection?=?dataSource.getConnection();  		System.out.println(connection);  		connection.close();//?把連接歸還給連接池  		  		DataSources.destroy(dataSource);//?完全釋放池中所有連接,并銷毀連接池!!  	}	  }

package?com.atguigu.jdbc;    import?java.io.FileInputStream;  import?java.io.IOException;  import?java.sql.Connection;  import?java.sql.DriverManager;  import?java.sql.ResultSet;  import?java.sql.SQLException;  import?java.sql.Statement;  import?java.util.Properties;    import?javax.sql.DataSource;    import?com.mchange.v2.c3p0.ComboPooledDataSource;  import?com.mchange.v2.c3p0.DataSources;    /**  ?*?預(yù)備工作?:?  ?*?	1)?把要訪問的數(shù)據(jù)庫(kù)相關(guān)的驅(qū)動(dòng)程序復(fù)制到項(xiàng)目中,?就是jar包  ?*?	2)?配置項(xiàng)目屬性,?把jar包導(dǎo)入到本項(xiàng)目的buildpath中  ?*?@author?Administrator  ?*  ?*/  public?class?JdbcUtil?{  	  	private?static?DataSource?dataSource;?//?聲明靜態(tài)屬性對(duì)象引用.  	  	static?{  		dataSource?=?new?ComboPooledDataSource("mysql-config");?//?連接池對(duì)象只需要?jiǎng)?chuàng)建一次就可以了  	}  	  	public?static?Connection?getConnection()?throws?SQLException?{  		return?dataSource.getConnection();?//?要想獲取連接,?只需要從連接池中獲取,用完以后,?再歸還回來  	}  	  	public?static?Connection?getConnectionOld()?throws?IOException,?ClassNotFoundException,?SQLException?{  		//?1)?讀取配置文件  		Properties?properties?=?new?Properties();  		properties.load(new?FileInputStream("jdbc.properties"));  		//?2)?獲取配置文件中的必要的信息  		String?driverClass?=?properties.getProperty("driverClass");  		String?url?=?properties.getProperty("url");  		String?user?=?properties.getProperty("user");  		String?password?=?properties.getProperty("password");  		//?3)?注冊(cè)驅(qū)動(dòng)?,?加載驅(qū)動(dòng)類  		Class.forName(driverClass);  		//?4)?通過驅(qū)動(dòng)管理器獲取連接(需要url,用戶,密碼)  		return?DriverManager.getConnection(url,?user,?password);//?暗含??new?Socket(host,port),?認(rèn)證,其他各種初始化操作  	}  	  	//關(guān)閉連接  	public?static?void?close(Connection?connection)?{  		close(connection,?null);  	}  	  	public?static?void?close(Connection?connection,?Statement?statement)?{  		close(connection,?statement,?null);  	}  	  	public?static?void?close(Connection?connection,?Statement?statement,?ResultSet?resultSet)?{  		if?(resultSet?!=?null)?{  			try?{  				resultSet.close();  			}?catch?(Exception?e)?{  				e.printStackTrace();  			}  		}  		  		if?(statement?!=?null)?{  			try?{  				statement.close();  			}?catch?(Exception?e)?{  				e.printStackTrace();  			}  		}  		  		if?(connection?!=?null)?{  			try?{  				connection.close();  			}?catch?(Exception?e)?{  				e.printStackTrace();  			}  		}  	}  	  	//銷毀連接池  	public?static?void?destroy()?{  		try?{  			DataSources.destroy(dataSource);  		}?catch?(SQLException?e)?{  			e.printStackTrace();  		}  	}	  }

package?com.atguigu.jdbc;    import?java.sql.Connection;  import?java.sql.SQLException;  import?java.util.List;    import?org.apache.commons.dbutils.QueryRunner;  import?org.apache.commons.dbutils.handlers.ArrayListHandler;  import?org.apache.commons.dbutils.handlers.BeanHandler;  import?org.apache.commons.dbutils.handlers.BeanListHandler;  import?org.apache.commons.dbutils.handlers.ScalarHandler;  import?org.junit.Test;    public?class?QueryRunnerTest?{    	//?使用我們自定義工具實(shí)現(xiàn)表的創(chuàng)建  	@Test  	public?void?test1()?throws?SQLException?{  		QueryRunner?qr?=?new?QueryRunner();  		Connection?connection?=?JdbcUtil.getConnection();  		qr.update(connection,?"create?table?test2(aa?int,?bb?varchar(10))");  		JdbcUtil.close(connection);  	}	  	  	//?使用我們自定義工具向表中插入一條記錄  	@Test  	public?void?test2()?throws?SQLException?{  		QueryRunner?qr?=?new?QueryRunner();  		Connection?connection?=?JdbcUtil.getConnection();  		int?rows?=?qr.update(connection,?"insert?into?test2(aa,?bb)?values(?,?)",?10,?"xxx");  		System.out.println(rows?+?"?rows");  		JdbcUtil.close(connection);  	}  	  	//?使用DBUtils.JDBC接口中提供的方法對(duì)departments表進(jìn)行查詢，把結(jié)果集中的所有記錄轉(zhuǎn)換為department對(duì)象集合并存入List集合中，然后遍歷輸出對(duì)象  	@Test  	public?void?test3()?throws?SQLException?{  		//query(Connection?conn,?String?sql,?ResultSetHandler<t>?rsh,?Object...?params)?  		String?sql?=?"select?*?from?departments?where?department_id?&gt;??";  		QueryRunner?qr?=?new?QueryRunner();  		Connection?connection?=?JdbcUtil.getConnection();  		BeanListHandler<department>?rsh?=?new?BeanListHandler<department>(Department.class);?//?把結(jié)果集中的所有記錄轉(zhuǎn)換為對(duì)象集合  		List<department>?list?=?qr.query(connection,?sql,?rsh,?20);  		for?(Department?department?:?list)?{  			System.out.println(department);  		}  	}	  	  	//?使用DBUtils.JDBC接口中提供的方法對(duì)departments表進(jìn)行查詢，把結(jié)果集中的一條記錄轉(zhuǎn)換為department實(shí)體對(duì)象，然后輸出對(duì)象  	@Test  	public?void?test4()?throws?SQLException?{  		String?sql?=?"select?*?from?departments?where?department_id?=??";  		QueryRunner?qr?=?new?QueryRunner();  		Connection?connection?=?JdbcUtil.getConnection();  		BeanHandler<department>?rsh?=?new?BeanHandler<department>(Department.class);?//?把結(jié)果集中的一條記錄轉(zhuǎn)換為實(shí)體對(duì)象  		Department?objDepartment?=?qr.query(connection,?sql,?rsh,?20);  		System.out.println(objDepartment);  	}  	  	//?使用DBUtils.JDBC接口中提供的方法對(duì)departments表進(jìn)行查詢，將每一條記錄存入集合中，然后遍歷輸出每一個(gè)數(shù)據(jù)  	@Test  	public?void?test5()?throws?SQLException?{  		String?sql?=?"select?*?from?employees";  		QueryRunner?qr?=?new?QueryRunner();  		Connection?connection?=?JdbcUtil.getConnection();  		ArrayListHandler?rsh?=?new?ArrayListHandler();  		List<object>?list?=?qr.query(connection,?sql,?rsh);  		for?(Object[]?objects?:?list)?{  			for?(int?i?=?0;?i??list?=?qr.query(JdbcUtil.getConnection(),??"select?*?from?student",?new?ColumnListHandler(1));  		for?(Object?object?:?list)?{  			System.out.println(object);  		}  	}    	//MapHandler把第一行數(shù)據(jù)封裝到Map集合中,?列名作為鍵,?對(duì)應(yīng)值作為值  	@Test  	public?void?test8()?throws?Exception?{  		QueryRunner?qr?=?new?QueryRunner();  		Map<string>?map?=?qr.query(JdbcUtil.getConnection(),??"select?*?from?student",?new?MapHandler());  		Set<string>?keys?=?map.keySet();  		for?(String?key?:?keys)?{  			Object?value?=?map.get(key);  			System.out.println(key?+?"?--------?"?+?value);  		}  	}	  		  	//MapListHandler把一行數(shù)據(jù)封裝到Map集合中,?并把所有行生成的Map再放入一個(gè)List集合  	@Test  	public?void?test9()?throws?Exception?{  		QueryRunner?qr?=?new?QueryRunner();  		List<map>&gt;?list?=?qr.query(JdbcUtil.getConnection(),??"select?*?from?student",?new?MapListHandler());  		for?(Map<string>?map2?:?list)?{  			Set<string>?keys?=?map2.keySet();  			for?(String?key?:?keys)?{  				Object?value?=?map2.get(key);  				System.out.println(key?+?"?--------?"?+?value);  			}  			System.out.println();  		}  	}	  }</string></string></map></string></string></object></department></department></department></department></department></t>

package?com.atguigu.jdbc;    import?java.sql.Connection;  import?java.sql.ResultSet;  import?java.sql.SQLException;  import?java.sql.Statement;    import?javax.sql.DataSource;    import?com.mchange.v2.c3p0.ComboPooledDataSource;  import?com.mchange.v2.c3p0.DataSources;    public?class?JdbcUtil?{  	  	private?static?DataSource?dataSource;  	  	static?{  		dataSource?=?new?ComboPooledDataSource("config1");?//?它必須依賴文件src/c3p0-config.xml  	}  	  	//?獲取c3p0連接池的連接  	public?static?Connection?getConnection()?throws?SQLException?{  		return?dataSource.getConnection();  	}  	  	public?static?void?close(Connection?connection)?{  		close(connection,?null);  	}  	  	public?static?void?close(Connection?connection,?Statement?statement)?{  		close(connection,?statement,?null);  	}  	  	public?static?void?close(Connection?connection,?Statement?statement,?ResultSet?resultSet)?{  		if?(resultSet?!=?null)?{  			try?{  				resultSet.close();  			}?catch?(Exception?e)?{  				e.printStackTrace();  			}  		}  		  		if?(statement?!=?null)?{  			try?{  				statement.close();  			}?catch?(Exception?e)?{  				e.printStackTrace();  			}  		}  		  		if?(connection?!=?null)?{  			try?{  				connection.close();  			}?catch?(Exception?e)?{  				e.printStackTrace();  			}  		}  	}  	  	public?static?void?destroy()?{  		try?{  			DataSources.destroy(dataSource);  		}?catch?(SQLException?e)?{  			e.printStackTrace();  		}  	}  }

package?com.atguigu.jdbc;    import?java.lang.reflect.Field;  import?java.sql.Connection;  import?java.sql.PreparedStatement;  import?java.sql.ResultSet;  import?java.sql.ResultSetMetaData;  import?java.sql.SQLException;  import?java.util.ArrayList;  import?java.util.List;    public?class?CommonUtil?{  	  	/**  	?*?把結(jié)果集中的每一行都放入Object對(duì)象數(shù)組中,?再把所有的Object對(duì)象數(shù)組放入一個(gè)List集合中.  	?*?@throws?SQLException?  	?*/  	public?static?List<object>?query(Connection?connection,?String?sql,?Object...?values)?throws?SQLException?{  		PreparedStatement?preparedStatement?=?null;  		ResultSet?resultSet?=?null;  		try?{  			preparedStatement?=?connection.prepareStatement(sql);  			fillArguments(preparedStatement,?values);  			resultSet?=?preparedStatement.executeQuery();  			List<object>?list?=?new?ArrayList<object>();  			int?cols?=?resultSet.getMetaData().getColumnCount();  			while?(resultSet.next())?{  				Object[]?dataRow?=?new?Object[cols];  				for?(int?i?=?0;?i??T?queryBean(Connection?connection,?String?sql,?Class<t>?clazz,?Object...?values)?throws?SQLException,?  	NoSuchFieldException,?SecurityException,?InstantiationException,?IllegalAccessException?{  		PreparedStatement?preparedStatement?=?null;  		ResultSet?resultSet?=?null;  		try?{  			preparedStatement?=?connection.prepareStatement(sql);  			  			fillArguments(preparedStatement,?values);  			  			resultSet?=?preparedStatement.executeQuery();  			if?(resultSet.next())?{  				T?t?=?clazz.newInstance();  				ResultSetMetaData?metaData?=?resultSet.getMetaData();  				int?cols?=?metaData.getColumnCount();  				for?(int?i?=?0;?i??List<t>?query(Connection?connection,?String?sql,?Class<t>?clazz,?Object...?values)?throws?SQLException,?  	InstantiationException,?IllegalAccessException,?NoSuchFieldException,?SecurityException?{  		PreparedStatement?preparedStatement?=?null;  		ResultSet?resultSet?=?null;  		try?{  			preparedStatement?=?connection.prepareStatement(sql);  			  			fillArguments(preparedStatement,?values);  			  			resultSet?=?preparedStatement.executeQuery();  			List<t>?list?=?new?ArrayList<t>();  			ResultSetMetaData?metaData?=?resultSet.getMetaData();  			int?cols?=?metaData.getColumnCount();  			while?(resultSet.next())?{  				T?t?=?clazz.newInstance();  				for?(int?i?=?0;?i?<h2>BaseDAO</h2> <p>綜合之前學(xué)習(xí)過的知識(shí)，在這里創(chuàng)建一個(gè)BaseDAO<t>類借助DBUtils工具類實(shí)現(xiàn)數(shù)據(jù)操作功能:</t></p> <pre class="java">package?com.atguigu.jdbc;    import?java.lang.reflect.ParameterizedType;  import?java.lang.reflect.Type;  import?java.sql.Connection;  import?java.sql.SQLException;  import?java.util.List;    import?org.apache.commons.dbutils.QueryRunner;  import?org.apache.commons.dbutils.handlers.BeanHandler;  import?org.apache.commons.dbutils.handlers.BeanListHandler;  import?org.apache.commons.dbutils.handlers.ScalarHandler;    public?class?BaseDAO<t>{    	protected?Class<t>?clazz;?//?T泛型究竟是什么類型,?用類模板對(duì)象來描述  	protected?QueryRunner?qr?=?new?QueryRunner();?//?用于執(zhí)行通用查詢和更新的工具類對(duì)象  	protected?Connection?connection;?//?數(shù)據(jù)庫(kù)連接  	protected?String?tableName;?//?涉及到的表,需要通過構(gòu)造器初始化賦值  	  	public?JdbcDAO(String?tableName)?{  		//?以下代碼的執(zhí)行者是子類對(duì)象,所以this.getClass是獲取子類的類模板對(duì)象  		Type?type?=?this.getClass().getGenericSuperclass();?//?JdbcDAO<teacher>  		if?(type?instanceof?ParameterizedType)?{  			ParameterizedType?parameterizedType?=?(ParameterizedType)type;//JdbcDAO<teacher>  			Type[]?types?=?parameterizedType.getActualTypeArguments();  			clazz?=?(Class<t>)types[0];  		}?else?{  			clazz?=?(Class<t>)Object.class;  		}  		//?獲取一個(gè)連接供所有方法使用  		try?{  			connection?=?JdbcUtil.getConnection();  		}?catch?(SQLException?e)?{  			e.printStackTrace();  		}  		this.tableName?=?tableName;  	}  	  	//獲得記錄中具體的一個(gè)數(shù)據(jù)  	public?Object?getValue(String?sql,?Object...?values)?{  		try?{  			return?qr.query(connection,?sql,?new?ScalarHandler(),?values);  		}?catch?(SQLException?e)?{  			e.printStackTrace();  		}  		return?null;  	}    	//獲得一行數(shù)據(jù)并封裝成javabean對(duì)象  	public?T?get(String?sql,?Object...?values)?{  		try?{  			return?qr.query(connection,?sql,?new?BeanHandler<t>(clazz),?values);  		}?catch?(SQLException?e)?{  			e.printStackTrace();  		}  		return?null;  	}    	//獲得多行記錄，封裝成javabean對(duì)象，保存在list集合中  	public?List<t>?getList(String?sql,?Object...?values)?{  		try?{  			return?qr.query(connection,?sql,?new?BeanListHandler<t>(clazz),?values);  		}?catch?(SQLException?e)?{  			e.printStackTrace();  		}  		return?null;  	}  	  	  	//獲得所有記錄，封裝成javabean對(duì)象，保存在list集合中  	public?List<t>?getAll()?{  		return?getList("select?*?from?"?+?tableName);  	}  	  	//根據(jù)id獲取某一條記錄，并封裝成javabean對(duì)象返回  	public?T?getById(int?id)?{  		return?get("select?*?from?"?+?tableName?+?"?where?id?=??",?id);  	}  	  	//根據(jù)id刪除某一條記錄，刪除成功返回ture，失敗返回false  	public?boolean?deleteById(int?id)?{  		int?rows?=?update("delete?from?"?+?tableName?+?"?where?id?=??",?id);  		if?(rows?&gt;?0)?{  			return?true;  		}  		return?false;  	}    	//通用的更新操作  	public?int?update(String?sql,?Object...?values)?{  		try?{  			return?qr.update(connection,?sql,?values);  		}?catch?(SQLException?e)?{  			e.printStackTrace();  		}  		return?0;  	}  	  	//關(guān)閉連接  	public?void?close()?{  		JdbcUtil.close(connection);  	}  }</t></t></t></t></t></t></teacher></teacher></t></t>