- class TestDemo{
-
- /**
- * 测试DML工具类,DML和DQL
- */
- @Test
- public void test01(){
- //建立工具类对象,提供相应参数传递过去
- QueryRunnerDemo myQuery = new QueryRunnerDemo(JdbcUtils.getDatasource());
- //只要是DML都可以使用update函数,进行数据库中数据的更行(增,删,改)
- String sql ="insert into t_user(id,username,password) values(?,?,?)";
- Object[] params = {"u008","张三","saner"};
- int x = myQuery.update(sql, params);
- }
-
- /**
- * 这个只是单一的查询,并没有是查询大量,因为目前已经有现成工具类
- * 这个只是理解
- */
- @Test
- public void test02(){
- QueryRunnerDemo myQuery = new QueryRunnerDemo(JdbcUtils.getDatasource());
- String sql = "select * from t_user where id = ?";
- Object[] params = {"u008"};
- //将查询到的数据封装后返回
- Use user = (Use) myQuery.query(sql, new MyRsHandler(Use.class), params);
- }
- }
- class QueryRunnerDemo {
- private static DataSource dataSource = null;
- // 在建立工具的时候传进来一个数据源
- QueryRunnerDemo(DataSource datasource) {
- this.dataSource = datasource;
- }
- /*
- * DML操作:增,删,改 他们的操作都是一样的,只是传递的参数不同
- */
- public int update(String sql, Object... params) {
- Connection conn = null;
- PreparedStatement ps = null;
- try {
- conn = dataSource.getConnection();
- ps = conn.prepareStatement(sql);
- if (params != null) {
- for (int i = 0; i < params.length; i++) {
- ps.setObject(i + 1, params[i]);
- }
- }
- return ps.executeUpdate();
- } catch (SQLException e) {
- throw new RuntimeException("添加不成功");
- } finally {
- closeRes(conn, ps, null);
- }
- }
- public Object query(String sql, MyRsHandler myHandler, Object... params) {
- Connection conn = null;
- PreparedStatement ps = null;
- ResultSet rs = null;
- try {
- conn = dataSource.getConnection();
- ps = conn.prepareStatement(sql);
- if (params != null) {
- for (int i = 0; i < params.length; i++) {
- ps.setObject(i + 1, params[i]);
- }
- }
- rs = ps.executeQuery();
- return myHandler.handler(rs);
- } catch (Exception e) {
- throw new RuntimeException("查询不成功");
- } finally {
- closeRes(conn, ps, rs);
- }
- }
- /*
- * 使用完毕释放资源,注意从低到高一次处理。
- * 并在释放之前判断是否为null 而且每个资源的释放都可能抛出异常,
- * 注意利用finally嵌套
- */
- private static void closeRes(Connection conn, Statement st, ResultSet rs) {
- try {
- if (rs != null) {
- rs.close();
- }
- } catch (Exception e) {
- throw new RuntimeException(e);
- } finally {
- try {
- if (st != null) {
- st.close();
- }
- } catch (Exception e) {
- throw new RuntimeException(e);
- } finally {
- try {
- if (conn != null) {
- conn.close();
- }
- } catch (Exception e) {
- throw new RuntimeException(e);
- }
- }
- }
- }
- }
- class MyRsHandler {
- private Class beanClass;
- public MyRsHandler(Class beanClass) {
- this.beanClass = beanClass;
- }
- public Object handler(ResultSet rs) {
-
- /**
- *这里封装数据,首先利用反射创建一个JavaBean的对象
- *然后根据结果集的元数据中获取数据库中查询到的数据
- * 最后利用内省将数据封装到建立的对象中
- */
-
- try {
- if (rs.next()) {
- Object bean = beanClass.newInstance(); //反射
- ResultSetMetaData rsmd = rs.getMetaData(); //获取元数据
- int col = rsmd.getColumnCount();
- /*
- * 元数据即:列数=值 的形式封装数据库中的内容
- */
-
- for (int i = 0; i < col; i++) {
- String colname = rsmd.getColumnName(i + 1);
- Object value = rs.getObject(colname);
-
- //利用工具类根据内省封装数据
- BeanUtils.setProperty(bean, colname, value);
- }
- return bean;
- }
- return null;
- } catch (Exception e) {
- throw new RuntimeException("封装数据出错");
- }
- }
- }
- class JdbcUtils{
-
- /*
- * 这里主要是提供获取数据源和获取连接的两个方法
- * 利用C3P0提供一个连接池,这个连接池是datasource的子类
- *
- * 这里的配置文件通过c3p0-config.xml文件进行配置
- */
- private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
-
- public static ComboPooledDataSource getDatasource(){
- return dataSource;
- }
- public static Connection getConnection() throws Exception{
- return dataSource.getConnection();
- }
- }
- /**
- * 提供一个JavaBean来封装数据
- */
- public class Use {
-
- private String id;
- private String username;
- private String password;
- public String getId() {
- return id;
- }
- public void setId(String id) {
- this.id = id;
- }
- public String getUsername() {
- return username;
- }
- public void setUsername(String username) {
- this.username = username;
- }
- public String getPassword() {
- return password;
- }
- public void setPassword(String password) {
- this.password = password;
- }
- @Override
- public String toString() {
- return "User [id=" + id + ", username=" + username + ", password="
- + password + "]";
- }
- }
复制代码
|