javaweb

一.学习内容
1，DBUtils 工具
（1）QueryRunner 类
（2）ResultSetHandler 接口
（3）BeaHandler 实现类
BeaListHandler
2.编写代码如下：
（1）

package cn.itcat.chapter10.example;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class excample01 {
	 
	public  static DataSource ds=null;
	
	 static {
		 BasicDataSource bds=new BasicDataSource();
		 
		bds.setDriverClassName("com.mysql.cj.jdbc.Driver");
	    bds.setUrl("jdbc:mysql://localhost:3306/jdbc?serverTimezone=Asia/Shanghai");
	    bds.setUsername("root");
	    bds.setPassword("123456");
	    bds.setInitialSize(5);
	    bds.setMaxActive(10);
	    
	    ds=bds;
	 }
	public static void main(String[] args) throws Exception {
		Connection conn=ds.getConnection();
		DatabaseMetaData metaData=conn.getMetaData();
		System.out.println(metaData.getURL());
		System.out.println(metaData.getUserName());
		System.out.println(metaData.getDriverVersion());
}
}

（2）

package cn.itcat.chapter10.example;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

public class excample02 {
	public  static DataSource ds=null;
	
	static {
		Properties prop= new Properties();
		
		try {
			
		InputStream in =new excample02().getClass().getClassLoader().getResourceAsStream("dbcp.properties");
		
		prop.load(in);
		
		ds=BasicDataSourceFactory.createDataSource(prop);
		
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public static void main(String[] args) throws SQLException {
		Connection conn=ds.getConnection();  
		
		DatabaseMetaData metaData=conn.getMetaData();
		System.out.println(metaData.getURL());
		System.out.println(metaData.getUserName());
		System.out.println(metaData.getDriverVersion());
		System.out.println(metaData.getDriverName());
		  
	}
}

（3）

package cn.itcat.chapter10.example;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class excample03 { 
	public  static DataSource ds=null;
	static {
		ComboPooledDataSource cpds=new ComboPooledDataSource();
		try {
			cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
			cpds.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc?serverTimezone=Asia/Shanghai");
			cpds.setUser("root");
			cpds.setPassword("123456");
			
			 cpds.setInitialPoolSize(5);
			 cpds.setMaxPoolSize(10);
			 
			 ds=cpds;
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
  public static void main(String[] args) throws SQLException {
	 
	  Connection conn=ds.getConnection();  
		
		DatabaseMetaData metaData=conn.getMetaData();
		System.out.println(metaData.getURL());
		System.out.println(metaData.getUserName());
		System.out.println(metaData.getDriverVersion());
		System.out.println(metaData.getDriverName());
	  
}
}

（4）

package cn.itcat.chapter10.example;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class excample034 {
	public static DataSource ds=null;
	static {
		ComboPooledDataSource cpds=new ComboPooledDataSource("itcast");
		ds=cpds;
	}
	public static void main(String[] args) throws SQLException {
		 
		 System.out.println(ds.getConnection());
	}
}

（5）

package cn.itcat.chapter10.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbutils.ResultSetHandler;

public class BaseDao {

	public static Object query(String sql,ResultSetHandler<?> rsh,Object...params) {
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs =null;
	try {
		conn=JDBCUtils.getConnection();
		pstmt=conn.prepareStatement(sql);
		for(int i=0;params!=null && i<params.length;i++)
		{
			pstmt.setObject(i+1, params[i]);
		}
	rs=pstmt.executeQuery();
	Object obj=rsh.handle(rs);
	return obj;	
	}catch(Exception e) {
		e.printStackTrace();
	}
	finally {
		JDBCUtils.release(rs, pstmt, conn);
	}
	return rs;
	}
}

（6）
该类用来封装user对象

package cn.itcat.chapter10.example;
public class User {
	private int id;
	private String name;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
}

（7）
该类用于演示BeanHandler类结果集的处理

package cn.itcat.chapter10.example;

import java.sql.SQLException;

import org.apache.commons.dbutils.handlers.BeanHandler;

public class ResultSetTest {
   public static void testBeanHandler() throws SQLException {
	   BaseDao basebao= new BaseDao();
	   String sql="select * from user where id=?";
	   
	   Object object= basebao.query(sql, new BeanHandler(User.class),1);
	   if(object!=null && object instanceof User) {
		   User user =(User) object;
		   System.out.println("id 为1的User对象的name值为：" +user.getName());
	   }else {
		   System.out.println("查询结果为空:" +object);
	   }
	   
   }
   public static void main(String[] args) throws SQLException {
	testBeanHandler();
}
}

（8）
该类用于演示BeanListHandler类对结果集的处理

package cn.itcat.chapter10.example;

import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class ResultSetTest2 {
	
	public static void testBeanHandler() throws SQLException {
		   BaseDao basedao= new BaseDao();
		   String sql="select * from user where id=?";
		    
		   ArrayList<User> list=(ArrayList<User>) basedao.query(sql, new BeanListHandler(User.class));
		  
		   for(int i = 0; i < list.size();i++) {
			   System.out.println("id 为" + i+"的用户名：" +list.get(i).getName());
		   }
	 }
	   public static void main(String[] args) throws SQLException {
		   testBeanHandler();
		}
}

效果图