*.数据库连接池c3p0,连接mysql数据库;
*.Jquery使用,删除时跳出框,确定是否要删除;
*.使用EL和JSTL,简化在jsp页面中插入的java语言
1.连接数据库
(1)导入连接数据库连接池的开源架包和EL和JSTL的架包(在lib目录下导入)
c3p0-0.9.1.2jar
commons-dbutils-1.3jar
mysql-connector-java-5.1jar
jstl.jar
standard.jar
(2)在src目录下建立一个c3p0-config.xml文件,存放数据库连接池的变量:
root
lxn123
com.mysql.jdbc.Driver
jdbc:mysql:///test
5
10
10
50
20
5
(2)连接数据库连接池的类:JdbcUtils
packagecom.lanqiao.javatest;importjava.sql.Connection;importjava.sql.SQLException;importorg.junit.Test;importcom.mchange.v2.c3p0.ComboPooledDataSource;public classJdbcUtils {//实现数据库连接池的方法类//该静态代码块只执行一次
private static ComboPooledDataSource dataSource=null;static{
dataSource=new ComboPooledDataSource("mvcapp");
}public static Connection getConnection() throwsSQLException{returndataSource.getConnection();
}
@Test//测试数据库连接池是否连接成功
public void testT() throwsSQLException{
System.out.println(getConnection());
}//释放connection连接
public static voidreleaseConnection(Connection connection){try{if(connection!=null){
connection.close();
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
2.建立customer类,对数据库中的属性进行封装;
packagecom.lanqiao.javatest;public classCustomer {privateInteger id;privateString name;privateString address;privateString phone;publicCustomer() {super();//TODO Auto-generated constructor stub
}publicCustomer(String name, String address, String phone) {this.name =name;this.address =address;this.phone =phone;
}publicCustomer(Integer id, String name, String address, String phone) {super();this.id =id;this.name =name;this.address =address;this.phone =phone;
}publicInteger getId() {returnid;
}public voidsetId(Integer id) {this.id =id;
}publicString getName() {returnname;
}public voidsetName(String name) {this.name =name;
}publicString getAddress() {returnaddress;
}public voidsetAddress(String address) {this.address =address;
}publicString getPhone() {returnphone;
}public voidsetPhone(String phone) {this.phone =phone;
}
@OverridepublicString toString() {return "Person [id=" + id + ", name=" + name + ", address=" + address + ", phone=" + phone + "]";
}
}
3.建立CriteriaCustomer类,对数据库中的部分数据进行封装,便于模糊查询使用
packagecom.lanqiao.javatest;//模糊查询的类
public classCriteriaCustomer {privateString name;privateString address;privateString phone;publicCriteriaCustomer() {super();
}publicCriteriaCustomer(String name, String address, String phone) {super();this.name =name;this.address =address;this.phone =phone;
}publicString getName() {if (name==null) {
name="%%";
}else{
name="%"+name+"%";
}returnname;
}public voidsetName(String name) {this.name =name;
}publicString getAddress() {if (address==null) {
address="%%";
}else{
address="%"+address+"%";
}returnaddress;
}public voidsetAddress(String address) {this.address =address;
}publicString getPhone() {if (phone==null) {
phone="%%";
}else{
phone="%"+phone+"%";
}returnphone;
}public voidsetPhone(String phone) {this.phone =phone;
}
}
4.在src目录下建立一个接口类:CustomerDAO,对一些方法进行了封装;
packagecom.lanqiao.javatest;importjava.util.List;//创建以CustomerDAO接口,可以实现其他类的调用
public interfaceCustomerDAO {//模糊查询方法
public ListgetForListWithCriteriaCustomer(CriteriaCustomer cc);//查询的方法,将数据库的所有数据放在list集合中,并实现输出的查询
public ListgetAll();// public voidsave(Customer customer);//通过jsp超链接里面?后面的id,获取该id下的各属性的值
publicCustomer get(Integer id);//通过jsp超链接里面?后面的id,并且实现删除的功能
public voiddelete(Integer id);//返回和该name相等的个数
public longgetCountWithName(String name);voidupdate(Customer customer);
}
5.建一个DAO类,有得到某一个类方法明的反射方法,实现对数据的增删改查的方法
packagecom.lanqiao.javatest;importjava.lang.reflect.ParameterizedType;importjava.lang.reflect.Type;importjava.sql.Connection;importjava.util.List;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.handlers.BeanHandler;importorg.apache.commons.dbutils.handlers.BeanListHandler;importorg.apache.commons.dbutils.handlers.ScalarHandler;/** 封装了基本的增删改查方法,以供子类继承使用;
* 当前dao没有事务,直接在方法中获取数据库的链接
**/
public class DAO {//这个是线程安全的
private QueryRunner queryRunner=newQueryRunner();private Classclazz;//类的构造方法
publicDAO() {//得到父类带泛型的类型//type类型导包为import java.lang.reflect.Type;反射类型里面的//反射。。。。。。Type所有超级类接口,ParameterizedType表示参数化类型,参数化类型在反射方法首次需要时创建(在此包中指定)。//当创建参数化类型 p 时,p 实例化的一般类型声明会被解析,并且按递归方式创建 p 的所有类型参数。
Type superClass=getClass().getGenericSuperclass();if (superClass instanceofParameterizedType) {
ParameterizedType parameterizedType=(ParameterizedType) superClass;//getActualTypeArguments():返回表示此类型实际类型参数的 Type 对象的数组。
Type[] typeArgs=parameterizedType.getActualTypeArguments();if (typeArgs!=null && typeArgs.length>0) {if (typeArgs[0] instanceofClass) {
clazz=(Class) typeArgs[0];
}
}
}
}//返回某一字段的值:比如返回某一条记录的customerName,或返回有多少条记录
public E getForValue(String sql,Object...args){
Connection connection=null;try{
connection=JdbcUtils.getConnection();return (E) queryRunner.query(connection,sql,newScalarHandler(),args);
}catch(Exception e) {
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(connection);
}return null;
}//返回所对应的list集合,获得的是一组的
public ListgetForList(String sql,Object...args){
Connection connection=null;try{
connection=JdbcUtils.getConnection();return queryRunner.query(connection,sql,new BeanListHandler<>(clazz),args);
}catch(Exception e) {
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(connection);
}return null;
}//返回对应的T的一个实体类的对象
publicT get(String sql,Object...args){
Connection connection=null;try{
connection=JdbcUtils.getConnection();return queryRunner.query(connection,sql,new BeanHandler<>(clazz),args);
}catch(Exception e) {
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(connection);
}return null;
}//该方法封装了增删改操作
public voidupdate(String sql,Object...args){
Connection connection=null;try{
connection=JdbcUtils.getConnection();
queryRunner.update(connection, sql, args);
}catch(Exception e) {
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(connection);
}
}
}
6.建立一个类:CustomerDAOJdbcImpl 继承于父类 DAO 和继承于接口 CustomerDAO,增删改查方法和模糊查询的方法;
packagecom.lanqiao.javatest;importjava.util.List;importcom.lanqiao.javatest.CriteriaCustomer;importcom.lanqiao.javatest.CustomerDAO;importcom.lanqiao.javatest.DAO;//实现各个功能的类
public class CustomerDAOJdbcImpl extends DAO implementsCustomerDAO{
@Override//模糊查询的方法
public ListgetForListWithCriteriaCustomer(CriteriaCustomer cc) {
String sql="select id,name,address,phone from customer "
+ "where name like ? and address like ? and phone like ?";//修改了CriteriaCustomer的get方法,使其返回的字符串中含有%%
returngetForList(sql,cc.getName(),cc.getAddress(),cc.getPhone());
}
@Override//获取整个数据库中的所有数据
public ListgetAll() {
String sql1="select id,name,address,phone from customer";returngetForList(sql1);
}
@Override//插入数据到数据库的方法
public voidsave(Customer customer) {
String sql2="insert into customer(name,address,phone) values(?,?,?)";
update(sql2, customer.getName(),customer.getAddress(),customer.getPhone());
}
@Override//获取某个数据
publicCustomer get(Integer id) {
String sql3="select id,name,address,phone from customer where id=?";returnget(sql3,id);
}
@Override//删除id=?的数据
public voiddelete(Integer id) {
String sql4="delete from customer where id=?";
update(sql4, id);
}
@Override//用名字获取count(name),即获取用户的数量
public longgetCountWithName(String name) {
String sql5="select count(name) from customer where name=?";returngetForValue(sql5, name);
}
@Overridepublic voidupdate(Customer customer) {
String sql= "UPDATE customer SET name = ?, address = ?, phone = ? " +
"WHERE id = ?";
update(sql, customer.getName(), customer.getAddress(),
customer.getPhone(), customer.getId());
}
}
7.建立一个Servlet类:doGet和doPost方法;
packagecom.lanqiao.javatest1;importjava.io.IOException;importjava.lang.reflect.Method;importjava.util.List;importjavax.management.Query;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcom.lanqiao.javatest.CriteriaCustomer;importcom.lanqiao.javatest.Customer;importcom.lanqiao.javatest.CustomerDAO;importcom.lanqiao.javatest.CustomerDAOJdbcImpl;public class CustomerServlet extendsHttpServlet {private static final long serialVersionUID = 1L;private CustomerDAO customerDAO=newCustomerDAOJdbcImpl();protected voiddoGet(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {
doPost(request, response);
}protected voiddoPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {//利用反射,接受多个以.do结尾的请求//1.变量 servletPath获取所有后边带.do的方法名和.do ,例如这个/pass.do
String servletPath=request.getServletPath();//2.在字符串后面除去.do这个字符
String methodName=servletPath.substring(1);
methodName=methodName.substring(0,methodName.length()-3);//System.out.println(methodName);
try{//反射获取该类的该方法
Method method=getClass().getDeclaredMethod
(methodName, HttpServletRequest.class,HttpServletResponse.class);//这里面的this指通过这个方法的到方法名字,并且输出,及this指methodName的到的方法名字
method.invoke(this,request,response);
}catch(Exception e) {//如果出现错误,去error页面
response.sendRedirect("error.jsp");
}
}//查询方法
private void query(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {//获取模糊查询的请求参数
String name=request.getParameter("name");
String address=request.getParameter("address");
String phone=request.getParameter("phone");//把请求参数封装为一个CriteriaCustomer对象
CriteriaCustomer cc=newCriteriaCustomer(name,address,phone);//1.调用CustomerDAO的getForListWithCriteriaCustomer(cc)方法得到lists的集合
List lists=customerDAO.getForListWithCriteriaCustomer(cc);//2.把list集合放到request中
request.setAttribute("list", lists);//3.转发页面到index.jsp(不能使用重定向) /代表的是根目录下的jsp文件;
request.getRequestDispatcher("/index.jsp").forward(request, response);
}//插入数据的方法:
private voidaddCustomer(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException{//1.获取表单参数,name,address,phone
String name=request.getParameter("name");
String address=request.getParameter("address");
String phone=request.getParameter("phone");//检验名字是否已经被占用了:调用CustomerDAO的getCountWithName方法,获取name参数是否大于0,如果大于....//并且消息可以回显:value=""
long count=customerDAO.getCountWithName(name);if (count>0) {
request.setAttribute("message","用户名"+name+"已经被占用了,请重新选择!!!");//名字重复了,请求的转发到/newcustomer.jsp
request.getRequestDispatcher("/newcustomer.jsp").forward(request, response);//结束方法
return;
}//2.若验证通过的话,把表单参数封装为一个customer的对象
Customer customer=newCustomer(name,address,phone);//3.调用CustomerDAO的save方法执行保存
customerDAO.save(customer);//数据插入成功后,请求的转发到newcustomer.jsp//request.getRequestDispatcher("/newcustomer.jsp").forward(request, response);//4.数据插入成功后,重定向到success.jsp页面:使用重定向可以避免出现表单的重复提交问题.
response.sendRedirect("success.jsp");
}private void delete(HttpServletRequest request, HttpServletResponse response) throwsIOException {//1.获取请求的id
String idStr=request.getParameter("id");int id=0;try{
id=Integer.parseInt(idStr);//2.调用CustomerDAO的getId()方法执行删除
customerDAO.delete(id);
}catch(Exception e) {
response.sendRedirect("query.do");
}//重定向的页面jsp,其前面不用加"/"
response.sendRedirect("success.jsp");
}//修改数据表里的数据
private voidedit(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {//出现了所查询的数据不存在的错误显示到error.jsp页面
String forwardPath="/error.jsp";//1.获取请求的id
String idStr=request.getParameter("id");//2. 调用 CustomerDAO 的 customerDAO.get(id) 获取和 id 对应的 Customer 对象 customer
try{
Customer customer=customerDAO.get(Integer.parseInt(idStr));//如果数据存在的跳转到updatecustomer.jsp页面,进行修改数据
if(customer!=null){
forwardPath="/updatecustomer.jsp";//将数据放到request请求的转发的里面
request.setAttribute("customer", customer);
}
}catch(Exception e) {
System.out.println("fsdlkf");
}//4. 响应 updatecustomer.jsp 页面: 转发.
request.getRequestDispatcher(forwardPath).forward(request, response);
}private voidupdate(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {//1. 获取表单参数: id, name, address, phone, oldName
String id=request.getParameter("id");
String name=request.getParameter("name");
String address=request.getParameter("address");
String phone=request.getParameter("phone");
String oldName=request.getParameter("oldName");//2. 检验 name 是否已经被占用://2.1 比较 name 和 oldName 是否相同, 若相同说明 name 可用.//2.1 若不相同, 则调用 CustomerDAO 的 getCountWithName(String name) 获取 name 在数据库中是否存在
if(!oldName.equalsIgnoreCase(name)){long count=customerDAO.getCountWithName(name);//2.2 若返回值大于 0, 则响应 updatecustomer.jsp 页面: 通过转发的方式来响应 newcustomer.jsp
if (count>0) {//2.2.1 在 updatecustomer.jsp 页面显示一个错误消息: 用户名 name 已经被占用, 请重新选择!//在 request 中放入一个属性 message: 用户名 name 已经被占用, 请重新选择!,//在页面上通过 request.getAttribute("message") 的方式来显示
request.setAttribute("message", "用户名"+name+"已经被占用,请重现选择!!!");//2.2.2 newcustomer.jsp 的表单值可以回显.//address, phone 显示提交表单的新的值, 而 name 显示 oldName, 而不是新提交的 name//2.2.3 结束方法: return
request.getRequestDispatcher("updatecustomer.jsp").forward(request, response);return;
}
}//3. 若验证通过, 则把表单参数封装为一个 Customer 对象 customer
Customer customer=newCustomer(name,address,phone);
customer.setId(Integer.parseInt(id));//4. 调用 CustomerDAO 的 update(Customer customer) 执行更新操作
customerDAO.update(customer);//5. 重定向到 query.do
response.sendRedirect("query.do");
}
}
8.在WEB-INF下的web.xml文件;
day-11
CustomerServlet1
CustomerServlet1
com.lanqiao.javatest1.CustomerServlet
CustomerServlet1
*.do
9.几个jsp页面;
(1)index.jsp
Insert title here$(".delete12").click(function(){
var content= $(this).parent().parent().find("td:eq(1)").text();
var flag= confirm("确定要是删除" + content + "的信息吗?");returnflag;
});
});
Name: | |
Address: | |
Phone: | |
Add New Customer |
id | name | address | phone | update | delete |
---|---|---|---|---|---|
${cust.id } | ${cust.name } | ${cust.address } | ${cust.phone } |
(2)newcustomer.jsp
Insert title here${requestScope.message }
Name: | |
Address: | |
Phone: | |
(3)updatecustomer.jsp
Insert title here${requestScope.message }
Name: | |
Address: | |
Phone: | |
(4)success.jsp
Insert title here操作成功!!!
(5)error.jsp
Insert title here