JAVAWEB开发之Tomcat内置连接池的配置和使用、DbUtils的使用详解和案例、以及元数据详解

<?xml version="1.0" encoding="UTF-8"?>
<Context><Resource name="jdbc/EmployeeDB" auth="Container" type="javax.sql.DataSource"username="root" password="root" driverClassName="com.mysql.jdbc.Driver"url="jdbc:mysql:///mydb1" maxActive="8" maxIdle="4" />
</Context>

package cn.itcast;import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;public class DataSourceServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {try {Context context = new InitialContext();Context envCtx = (Context) context.lookup("java:comp/env"); // 固定路径DataSource datasource = (DataSource) envCtx.lookup("jdbc/EmployeeDB");Connection con = datasource.getConnection();ResultSet rs = con.createStatement().executeQuery("select * from account");while (rs.next()) {System.out.println(rs.getInt("id") + "  "+ rs.getString("name"));}} catch (Exception e) {e.printStackTrace();}}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}

元数据

• getURL()：返回一个String类对象，代表数据库的URL。
• getUserName()：返回连接当前数据库管理系统的用户名。
• getDriverName()：返回驱动程序的名称
• getPrimaryKeys(String catalog, String schema, String table)：返回指定表主键的结果集

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:///day18?generateSimpleParameterMetadata=true
username=root
password=abc#driverClass=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:XE
#username=system
#password=system

package cn.itcast.utils;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;public class JdbcUtils {private static final String DRIVERCLASS;private static final String URL;private static final String USERNAME;private static final String PASSWORD;static {DRIVERCLASS = ResourceBundle.getBundle("jdbc").getString("driverClass");URL = ResourceBundle.getBundle("jdbc").getString("url");USERNAME = ResourceBundle.getBundle("jdbc").getString("username");PASSWORD = ResourceBundle.getBundle("jdbc").getString("password");}static {try {// 将加载驱动操作，放置在静态代码块中.这样就保证了只加载一次.Class.forName(DRIVERCLASS);} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConnection() throws SQLException {Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD);// 对Connection进行增强// return new MyConnection(con);// 使用动态代理来完成return con;}// 关闭操作public static void closeConnection(Connection con) throws SQLException {if (con != null) {con.close();}}public static void closeStatement(Statement st) throws SQLException {if (st != null) {st.close();}}public static void closeResultSet(ResultSet rs) throws SQLException {if (rs != null) {rs.close();}}
}

package cn.itcast.metadata;import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;import cn.itcast.utils.JdbcUtils;public class DataBaseMetaDataTest {public static void main(String[] args) throws SQLException {Connection con = JdbcUtils.getConnection();// 获取DataBaseMetaDataDatabaseMetaData dmd = con.getMetaData();// 获取驱动名称、String driverName = dmd.getDriverName();System.out.println(driverName);// 获取用户名String username = dmd.getUserName();System.out.println(username);// 获取URLString url = dmd.getURL();System.out.println(url);// 获取数据库产品名称String databaseProductName = dmd.getDatabaseProductName();System.out.println(databaseProductName);// 获取数据库产品版本String version = dmd.getDatabaseProductVersion();System.out.println(version);ResultSet  rs = dmd.getPrimaryKeys(null, null, "account");while(rs.next()){System.out.println(rs.getObject(3));}}}

package cn.itcast.metadata;import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;import cn.itcast.utils.JdbcUtils;public class ParameterMetaDataTest {public static void main(String[] args) throws SQLException {Connection con = JdbcUtils.getConnection();String sql = "select * from account where id=? and name=? ";PreparedStatement pst = con.prepareStatement(sql);// 获取一个ParameterMetaDataParameterMetaData pmd = pst.getParameterMetaData();int count = pmd.getParameterCount();System.out.println(count);String type1 = pmd.getParameterTypeName(1);System.out.println(type1);}
}

ResultSetMetaData结果集元数据

可以通过ResultSet的getMetaData()方法获取

获得通过ResultSet对象元数据的 ResultSetMetaData对象 

package cn.itcast.metadata;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;import cn.itcast.utils.JdbcUtils;public class ResultSetMetaDataTest {public static void main(String[] args) throws SQLException {Connection con = JdbcUtils.getConnection();ResultSet rs = con.createStatement().executeQuery("select * from account");// 得到结果集元数据ResultSetMetaData rsmd = rs.getMetaData();// System.out.println(rsmd.getColumnCount());//获取结果集中列数量//// System.out.println(rsmd.getColumnName(2));//获取结果集中指定列的名称.//// System.out.println(rsmd.getColumnTypeName(3));//获取结果集中指定列的类型。int count = rsmd.getColumnCount();for (int i = 1; i <= count; i++) {System.out.print(rsmd.getColumnName(i)+"("+rsmd.getColumnTypeName(i)+")" + "\t");}System.out.println();while (rs.next()) {for (int i = 1; i <= count; i++) {System.out.print(rs.getObject(i) + "\t\t");}System.out.println();}}
}

•  org.apache.commons.dbutils.QueryRunner  核心类
•  org.apache.commons.dbutils.ResultSetHandler
•  工具类： org.apache.commons.dbutils.Dbutils

• public static void close(...) throws java.sql.SQLException：DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
• public static void closeQuietly(...)：这一类方法不仅能在Connection、Statement和ResultSet为NULL的情况下悄悄关闭，就是指捕获关闭时抛出的SQLException 不抛出也不进行任何处理。
• public static void commitAndCloseQuietly(Connection  conn)：用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
• public static boolean loadDriver(java.lang.String  driverClassName)：这一方法装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，不需要捕捉这个异常ClassNotFoundException

package cn.itcast.domain;public class Account {private int id;private String name;private double money;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 double getMoney() {return money;}public void setMoney(double money) {this.money = money;}@Overridepublic String toString() {return "Account [id=" + id + ", name=" + name + ", money=" + money+ "]";}}

package cn.itcast.dbutils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.junit.Test;import cn.itcast.domain.Account;
import cn.itcast.utils.JdbcUtils;public class DbUtilsTest1 {// 查询@Testpublic void selectTest() throws SQLException {String sql = "select * from account";QueryRunner runner = new QueryRunner();Connection con = JdbcUtils.getConnection();List<Account> as = runner.query(con, sql,new ResultSetHandler<List<Account>>() {public List<Account> handle(ResultSet rs)throws SQLException {List<Account> as = new ArrayList<Account>();while (rs.next()) {Account a = new Account();a.setId(rs.getInt("id"));a.setName(rs.getString("name"));a.setMoney(rs.getDouble("money"));as.add(a);}return as;}});for (Account a : as) {System.out.println(a);}DbUtils.close(con);}// 添加@Testpublic void addTest() throws SQLException {String sql = "insert into account values(null,?,?)";QueryRunner runner = new QueryRunner();int row = runner.update(JdbcUtils.getConnection(), sql, "张三", 1000d);System.out.println(row);}
}

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql:///mydb1</property><property name="user">root</property><property name="password">root</property></default-config>
</c3p0-config>

package cn.itcast.utils;import java.sql.Connection;
import java.sql.SQLException;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DataSourceUtils {private static ComboPooledDataSource cpds = new ComboPooledDataSource();public static Connection getConnection() throws SQLException {return cpds.getConnection();}public static DataSource getDataSource() {return cpds;}
}

package cn.itcast.dbutils;import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;import cn.itcast.domain.Account;
import cn.itcast.utils.DataSourceUtils;public class QueryRunnerTest {@Test// 使用无参数 的QueryRunnerpublic void fun1() throws SQLException {String sql = "select * from account where id>? and name=? ";QueryRunner runner = new QueryRunner(); // 事务手动控制Connection con = DataSourceUtils.getConnection();// con.setAutoCommit(false);List<Account> list = runner.query(con, sql,new BeanListHandler<Account>(Account.class),2,"ccc");// con.rollback();System.out.println(list);}@Test// 使用有参数 的QueryRunnerpublic void fun2() throws SQLException {String sql = "select * from account where id=?";QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); // 自动事务List<Account> list = runner.query(sql, new BeanListHandler<Account>(Account.class),2);System.out.println(list);}
}

package cn.itcast.mydbutils;import java.sql.ResultSet;
import java.sql.SQLException;public interface MyResultHandler<T> {public T handle(ResultSet rs) throws SQLException;
}

package cn.itcast.mydbutils;import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;import org.apache.commons.beanutils.BeanUtils;public class MyBeanHandler implements MyResultHandler {private Class clazz;public MyBeanHandler(Class clazz) {this.clazz = clazz;}// 方式一：依赖于BeanUtils工具类public Object handle1(ResultSet rs) throws SQLException {Object obj = null;Map<String, String[]> map = new HashMap<String, String[]>();ResultSetMetaData md = rs.getMetaData();int count = md.getColumnCount();if (rs.next()) {try {obj = clazz.newInstance();for (int i = 1; i <= count; i++) {map.put(md.getColumnName(i),new String[] { rs.getString(md.getColumnName(i)) });}BeanUtils.populate(obj, map);} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}}return obj;}// 方式二：从结果集的方向去封装数据public Object handle(ResultSet rs) throws SQLException {Object obj = null;// 得到结果集元数据ResultSetMetaData md = rs.getMetaData();// 由结果集元数据可以获得所有字段名称int count = md.getColumnCount();// 遍历结果集 因为最后的需要的结果只是一个Bean对象，所以不用循环if (rs.next()) {// 利用内省技术进行实现try {obj = clazz.newInstance();BeanInfo bif = Introspector.getBeanInfo(clazz);// 得到JavaBean的所有属性描述器PropertyDescriptor[] pds = bif.getPropertyDescriptors();for (int i = 1; i <= count; i++) {// 得到每一列的名称String name = md.getColumnName(i);for (PropertyDescriptor pd : pds) {if (name.equals(pd.getName())) {// 使用setXxx方法将结果集中的字段值封装到JavaBean的对应属性上。pd.getWriteMethod().invoke(obj, rs.getObject(name));}}}} catch (IntrospectionException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (IllegalArgumentException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();} catch (InstantiationException e) {e.printStackTrace();}}return obj;}
}

package cn.itcast.mydbutils;import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;import javax.sql.DataSource;public class MyQueryRunner {private DataSource ds;public MyQueryRunner() {}public MyQueryRunner(DataSource ds) {this.ds = ds;}// 执行select操作public <T> T query(Connection con, String sql, MyResultHandler<T> mrs,Object... params) throws SQLException {// 获取一个预处理的StatementPreparedStatement pst = con.prepareStatement(sql);// 注意：SQL语句中可能存在参数，需要对参数进行赋值ParameterMetaData pmd = pst.getParameterMetaData();// 获取参数个数int count = pmd.getParameterCount();for (int i = 1; i <= count; i++) {pst.setObject(i, params[i - 1]);}// 获取结果集ResultSet rs = pst.executeQuery();// 注意得到了结果集，需要将结果集封装成用户想要的对象，但是工具不可能知道用户的封装需求return mrs.handle(rs);}// 执行update操作public int update(Connection con, String sql, Object... params)throws SQLException {// 获取一个预处理的Statement对象PreparedStatement pst = con.prepareStatement(sql);ParameterMetaData pmd = pst.getParameterMetaData();// 可以得到几个参数int count = pmd.getParameterCount();for (int i = 1; i <= count; i++) {pst.setObject(i, params[i - 1]);}int row = pst.executeUpdate();// 关闭资源 因为Statement对象指执行一次操作pst.close();return row;}// 执行update操作public int update(String sql, Object... params) throws SQLException {Connection con = ds.getConnection();// 获取一个预处理的Statement对象PreparedStatement pst = con.prepareStatement(sql);ParameterMetaData pmd = pst.getParameterMetaData();// 可以得到几个参数int count = pmd.getParameterCount();for (int i = 1; i <= count; i++) {pst.setObject(i, params[i - 1]);}int row = pst.executeUpdate();// 关闭资源 因为Statement对象指执行一次操作pst.close();con.close(); // 因为不带Connection参数的方法是一次性事务，需要关闭return row;}}

package cn.itcast.mydbutils;import java.sql.ResultSet;
import java.sql.SQLException;import org.junit.Test;import cn.itcast.domain.Account;
import cn.itcast.utils.DataSourceUtils;public class MyQueryRunnerTest {// 测试update方法@Testpublic void updateTest() throws SQLException {String sql = "delete from account where id=?";MyQueryRunner mqr = new MyQueryRunner();mqr.update(DataSourceUtils.getConnection(), sql, 4);}// 测试select@Testpublic void selectTest() throws SQLException {String sql = "select * from account where id=?";MyQueryRunner mqr = new MyQueryRunner();Account a = mqr.query(DataSourceUtils.getConnection(), sql,new MyResultHandler<Account>() {public Account handle(ResultSet rs) throws SQLException {Account a = null;if (rs.next()) {a = new Account();a.setId(rs.getInt("id"));a.setName(rs.getString("name"));a.setMoney(rs.getDouble("money"));}return a;}}, 2);System.out.println(a);}// 测试MyBeanHandler@Testpublic void beanHandlerTest() throws SQLException{String sql = "select * from account where id=?";MyQueryRunner mqr = new MyQueryRunner();Account a = mqr.query(DataSourceUtils.getConnection(), sql, new MyBeanHandler(Account.class), 1);System.out.println(a);}
}

• ArrayHandler：将结果集中第一条记录封装到Object[]，数组的每一个元素就是记录中的字段值。
• ArrayListHandler：将结果集中每一条记录封装到Object[]，数组中的每一个元素就是记录中的字段值。再将这些数组装入到List集合。
• BeanHandler（重点）：将结果集中第一条记录封装到一个JavaBean中。
• BeanListHandler（重点）：将结果集中每一条记录封装到JavaBean中，再将JavaBean封装到List集合。
• ColumnListHandler：将结果集中指定列的值封装到List集合。
• MapHandler：将结果集中第一条记录封装到Map集合中，集合的key就是字段名称，value就是字段值。
• MapListHandler：将结果集中每一条记录封装到每一个Map集合中，集合的key就是字段名称，value就是字段值，再 讲这些Map封装到List集合。
• KeyedHandler：将结果集每行的数据封装成一个Map，再将Map存入另一个Map作为value，指定一列作为key
• ScalarHandler：进行单值查询  select  count(*) from account;

package cn.itcast.dbutils;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;import cn.itcast.domain.Account;
import cn.itcast.utils.DataSourceUtils;// 介绍ResultSetHandler
public class ResultSetHandlerImplTest {// ArrayHandler@Testpublic void fun1() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());Object[] obj = runner.query("select * from account", new ArrayHandler());System.out.println(Arrays.toString(obj));}// ArrayListHandler@Testpublic void fun2() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());List<Object[]> objs = runner.query("select * from account",new ArrayListHandler());for (Object[] obj : objs) {System.out.println(Arrays.toString(obj));}}// BeanHandler@Testpublic void fun3() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());Account obj = runner.query("select * from account",new BeanHandler<Account>(Account.class));System.out.println(obj);}// BeanListHandler@Testpublic void fun4() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());List<Account> objs = runner.query("select * from account",new BeanListHandler<Account>(Account.class));for (Account account : objs) {System.out.println(account);}}// ColumnListHandler@Testpublic void fun5() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());List<Object> obj = runner.query("select * from account",new ColumnListHandler(1));List<Object> obj2 = runner.query("select * from account",new ColumnListHandler("name"));System.out.println(obj);System.out.println(obj2);}// MapHandler@Testpublic void fun6() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());Map<String, Object> obj = runner.query("select * from account",new MapHandler());System.out.println(obj);}// MapHandler@Testpublic void fun7() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());List<Map<String, Object>> objs = runner.query("select * from account",new MapListHandler());for (Map<String, Object> map : objs) {System.out.println(map);}}// KeyedHandler@Testpublic void fun8() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());Map<Object, Map<String, Object>> obj = runner.query("select * from account", new KeyedHandler("name"));System.out.println(obj);}// ScalarHandler@Testpublic void fun9() throws SQLException {QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());long obj = (Long) runner.query("select count(*)  from account",new ScalarHandler());System.out.println(obj);}// 按照需求自定义ResultSetHandler// 将结果封装到一个JavaBean对象中@Testpublic void fun10() throws SQLException {String sql = "select * from account where id=?";QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());Account a = runner.query(sql, new ResultSetHandler<Account>() {// 重写handle方法，在这个方法中确定，怎样将结果集封装public Account handle(ResultSet rs) throws SQLException {Account a = null;if (rs.next()) {a = new Account();a.setId(rs.getInt("id"));a.setName(rs.getString("name"));a.setMoney(rs.getDouble("money"));}return a;}}, 2);System.out.println(a);}
}

package cn.itcast.customer.dao;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 cn.itcast.customer.domain.Customer;
import cn.itcast.customer.utils.DataSourceUtils;public class CustomerDao {// 查询所有客户public List<Customer> findAll() throws SQLException {String sql = "select * from customer";QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());return runner.query(sql, new BeanListHandler<Customer>(Customer.class));}public void delById(String id) throws SQLException {String sql = "delete from customer where id=?";QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());runner.update(sql, id);}public Customer findById(String id) throws SQLException {String sql = "select * from customer where id=?";QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());return runner.query(sql, new BeanHandler<Customer>(Customer.class), id);}public void update(Customer c) throws SQLException {String sql = "update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?";QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());runner.update(sql, c.getName(), c.getGender(), c.getBirthday(),c.getCellphone(), c.getEmail(), c.getPreference(), c.getType(),c.getDescription(), c.getId());}}

package cn.itcast.customer.domain;import java.util.Date;public class Customer {// Id 编号 varchar(40)// name 客户姓名 varchar(20)// gender 性别 varchar(10)// birthday 生日 date// cellphone 手机 varchar(20)// email 电子邮件 varchar(40)// preference 客户爱好 varchar(100)// type 客户类型 varchar(40)// description 备注 varchar(255)private String id;private String name;private String gender;private Date birthday;private String cellphone;private String email;private String preference;private String type;private String description;public String getId() {return id;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getCellphone() {return cellphone;}public void setCellphone(String cellphone) {this.cellphone = cellphone;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getPreference() {return preference;}public void setPreference(String preference) {this.preference = preference;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}@Overridepublic String toString() {return "Customer [id=" + id + ", name=" + name + ", gender=" + gender+ ", birthday=" + birthday + ", cellphone=" + cellphone+ ", email=" + email + ", preference=" + preference + ", type="+ type + ", description=" + description + "]";}}

package cn.itcast.customer.service;import java.sql.SQLException;
import java.util.List;import cn.itcast.customer.dao.CustomerDao;
import cn.itcast.customer.domain.Customer;public class CustomerService {private CustomerDao dao=new CustomerDao();//查询所有客户信息操作public List<Customer> findAll() throws SQLException {return dao.findAll();}//根据id删除public void delById(String id) throws SQLException {dao.delById(id);}//根据id查询public Customer findById(String id) throws SQLException {return dao.findById(id);}//修改客户信息public void update(Customer c) throws SQLException {dao.update(c);}}

package cn.itcast.customer.tag;import java.io.IOException;import javax.servlet.jsp.JspException;
import javax.servlet.jsp.tagext.SimpleTagSupport;public class GenderTag extends SimpleTagSupport {private String gender;public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}@Overridepublic void doTag() throws JspException, IOException {StringBuffer buff = new StringBuffer();if ("男".equals(gender)) {buff.append("<input type='radio' name='gender' value='男' checked='checked'>男<input type='radio' name='gender' value='女'>女<br>");} else {buff.append("<input type='radio' name='gender' value='男'>男<input type='radio' name='gender' value='女' checked='checked'>女<br>");}this.getJspContext().getOut().write(buff.toString());}
}

package cn.itcast.customer.utils;import java.sql.Connection;
import java.sql.SQLException;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DataSourceUtils {private static ComboPooledDataSource cpds = new ComboPooledDataSource();public static Connection getConnection() throws SQLException {return cpds.getConnection();}public static DataSource getDataSource() {return cpds;}}

package cn.itcast.customer.web.servlet;import java.io.IOException;
import java.sql.SQLException;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import cn.itcast.customer.service.CustomerService;public class CustomerDelByIdServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {response.setContentType("text/html;charset=utf-8");// 1.得到要删除的idString id = request.getParameter("id");// 2.调用service中根据id删除的方法CustomerService service = new CustomerService();try {service.delById(id);// 跳转到CustomerFindAllServlet,就是要重新查询一次response.sendRedirect(request.getContextPath() + "/findAll");return;} catch (SQLException e) {e.printStackTrace();response.getWriter().write("删除失败");return;}}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}

package cn.itcast.customer.web.servlet;import java.io.IOException;
import java.sql.SQLException;
import java.util.List;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import cn.itcast.customer.domain.Customer;
import cn.itcast.customer.service.CustomerService;public class CustomerFindAllServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {response.setContentType("text/html;charset=utf-8");// 调用servic中查询所有方法CustomerService service = new CustomerService();try {List<Customer> cs = service.findAll();request.setAttribute("cs", cs);request.getRequestDispatcher("/showCustomer.jsp").forward(request,response);return;} catch (SQLException e) {e.printStackTrace();response.getWriter().write("查询客户信息失败");return;}}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}

package cn.itcast.customer.web.servlet;import java.io.IOException;
import java.sql.SQLException;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import cn.itcast.customer.domain.Customer;
import cn.itcast.customer.service.CustomerService;public class CustomerFindByIdServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {response.setContentType("text/html;charset=utf-8");// 1.得到要查询的idString id = request.getParameter("id");// 2.调用service中根据id查询的方法.CustomerService service = new CustomerService();try {Customer c = service.findById(id);request.setAttribute("c", c);request.getRequestDispatcher("/customerInfo.jsp").forward(request,response);return;} catch (SQLException e) {e.printStackTrace();response.getWriter().write("根据id查询失败");return;}}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}

package cn.itcast.customer.web.servlet;import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;import cn.itcast.customer.domain.Customer;
import cn.itcast.customer.service.CustomerService;public class CustomerUpdateServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// 处理请求编码，request.setCharacterEncoding("utf-8");response.setContentType("text/html;charset=utf-8");// 1.得到所有请求参数 ，封装到javaBean中.Customer c = new Customer();DateConverter dc = new DateConverter(); // 这是一个日期类型转换器.dc.setPattern("yyyy-MM-dd");try {ConvertUtils.register(dc, java.util.Date.class);BeanUtils.populate(c, request.getParameterMap());} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}// 调用service中修改操作CustomerService service = new CustomerService();try {service.update(c);// 跳转到CustomerFindAllServlet,就是要重新查询一次response.sendRedirect(request.getContextPath() + "/findAll");return;} catch (SQLException e) {e.printStackTrace();response.getWriter().write("修改失败");return;}}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}}

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql:///mydb1</property><property name="user">root</property><property name="password">root</property></default-config></c3p0-config>

<?xml version="1.0" encoding="UTF-8"?>
<taglib version="2.1" xmlns="http://java.sun.com/xml/ns/javaee"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-jsptaglibrary_2_1.xsd"><tlib-version>1.0</tlib-version><short-name>my</short-name><uri>http://www.itcast.cn/tag</uri><tag><name>sex</name><!-- 标签名称 --><tag-class>cn.itcast.customer.tag.GenderTag</tag-class><!-- 标签类 --><body-content>empty</body-content><!-- 标签体中内容 --><attribute><name>gender</name> <!-- 属性名称 --><required>true</required> <!-- 属性必须有 --><rtexprvalue>true</rtexprvalue><!-- 属性值可以接收el表达式 --></attribute></tag></taglib>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib  prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@taglib  prefix="my" uri="http://www.itcast.cn/tag"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><title>My JSP 'index.jsp' starting page</title></head><body><form action="${pageContext.request.contextPath}/update" method="post"><input type="hidden" name="id" value="${c.id}">客户姓名:<input type="text" name="name" value="${c.name}"><br>客户性别:<my:sex gender="${c.gender}"/><%--<c:if test="${c.gender=='男'}"><input type="radio" name="gender" value="男" checked="checked">男<input type="radio" name="gender" value="女">女</c:if><c:if test="${c.gender!='男'}"><input type="radio" name="gender" value="男">男<input type="radio" name="gender" value="女" checked="checked">女</c:if>		--%>客户生日:<input type="text" name="birthday" value="${c.birthday}"><br>客户电话:<input type="text" name="cellphone" value="${c.cellphone}"><br>客户邮箱:<input type="text" name="email" value="${c.email}"><br>客户爱好:<input type="text" name="preference" value="${c.preference}"><br>客户类型:<input type="text" name="type" value="${c.type}"><br>客户备注:<input type="text" name="description" value="${c.description}"><br><input type="submit" value="修改"></form></body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head><title>My JSP 'index.jsp' starting page</title><script type="text/javascript">function del(id) {var flag = window.confirm("确认删除吗");if (flag) {//确认删除location.href = "${pageContext.request.contextPath}/delById?id="+ id;}}
</script>
</head><body><c:if test="${empty cs}">无客户信息</c:if><c:if test="${not empty cs}"><table border="1" align="center" width="65%"><tr><td><input type="checkbox"></td><td>客户编号</td><td>客户姓名</td><td>客户性别</td><td>客户生日</td><td>客户电话</td><td>客户邮箱</td><td>客户爱好</td><td>客户类型</td><td>客户备注</td><td>操作</td></tr><c:forEach items="${cs}" var="c"><tr><td><input type="checkbox"></td><td>${c.id }</td><td>${c.name}</td><td>${c.gender }</td><td>${c.birthday }</td><td>${c.cellphone }</td><td>${c.email }</td><td>${c.preference }</td><td>${c.type }</td><td>${c.description }</td><td><a href="${pageContext.request.contextPath}/findById?id=${c.id}">编辑</a><a href="javascript:void(0)"οnclick="del('${c.id}')">删除</a></td></tr></c:forEach><tr><td colspan="11"><a>删除选中</a></td></tr></table></c:if></body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><title>My JSP 'index.jsp' starting page</title></head><body><a href="${pageContext.request.contextPath}/findAll">查看所有客户信息</a></body>
</html>