MVC 小案例 -- 信息管理
前几次更新博客都是每次周日晚上到周一，这次是周一晚上开始写，肯定也是有原因的！那就是我的 Tomact 忽然报错，无法启动，错误信息如下！同时我的 win10 也崩了，重启之后连 WIFI 的标志也不见了，上不了网。额。额。额。。。后面连 IDEA 也报错，就很无奈！不过现在能写也就是我把他折腾好了！原因很简单就是执行了下面简单的几句命令后重启就可以上网了，我对这方面的知识不是很熟悉但应该重置网络方面的东西！接着 Tomact 的报错也熟悉了 --- 端口被占用！这都是小事，改好端口就开始本周的学习之路！cmd 命令：
1 ipconfig /flushdns 2 3 netsh int ip rese 4 5 netsh winsock reset
Tomac报错如图（错误太多我就折叠了，懂的大神还望指教）：
1 Microsoft Windows [版本 10.0.16296.0] 2 (c) 2017 Microsoft Corporation。保留所有权利。 3 4 C:\Users\lenovo>catalina run 5 Using CATALINA_BASE: D:\apache-tomcat-6.0.16 6 Using CATALINA_HOME: D:\apache-tomcat-6.0.16 7 Using CATALINA_TMPDIR: D:\apache-tomcat-6.0.16\temp 8 Using JRE_HOME: D:\Java\jdk1.7.0_71 9 十一月 19, 2017 8:05:03 下午 org.apache.catalina.core.AprLifecycleListener init 10 信息: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: D:\Java\jdk1.7.0_71\bin;C:\WINDOWS\Sun\Java\bin;C:\WINDOWS\system32;C:\WINDOWS;c:\gtk\bin;F:\app\yin‘zhao\product\11.2.0\client_1\bin;:\orcal\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\Intel\iCLS Client\;D:\gradle\gradle-4.2\bin;C:\Program Files\Intel\iCLS Client\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;C:\Users\lenovo\AppData\Local\Microsoft\WindowsApps;C:\Program Files\MySQL\MySQL Utilities 1.6\;D:\Java\jdk1.7.0_71\bin;D:\apache-tomcat-6.0.16\bin;F:\Git\cmd;C:\TortoiseGit\bin;F:\nodejs\;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;D:\Java\jdk1.7.0_71\jre\bin;C:\Users\lenovo\AppData\Local\Microsoft\WindowsApps;F:\nodejs\node_global;C:\Users\lenovo\AppData\Local\GitHubDesktop\bin;%USERPROFILE%\AppData\Local\Microsoft\WindowsApps;;. 11 十一月 19, 2017 8:05:03 下午 org.apache.coyote.http11.Http11Protocol init 12 严重: Error initializing endpoint 13 java.net.SocketException: Protocol wrong type for socket: create 14 at java.net.ServerSocket.createImpl(ServerSocket.java:308) 15 at java.net.ServerSocket.getImpl(ServerSocket.java:257) 16 at java.net.ServerSocket.bind(ServerSocket.java:376) 17 at java.net.ServerSocket.<init>(ServerSocket.java:237) 18 at java.net.ServerSocket.<init>(ServerSocket.java:181) 19 at org.apache.tomcat.util.net.DefaultServerSocketFactory.createSocket(DefaultServerSocketFactory.java:50) 20 at org.apache.tomcat.util.net.JIoEndpoint.init(JIoEndpoint.java:496) 21 at org.apache.coyote.http11.Http11Protocol.init(Http11Protocol.java:176) 22 at org.apache.catalina.connector.Connector.initialize(Connector.java:1058) 23 at org.apache.catalina.core.StandardService.initialize(StandardService.java:677) 24 at org.apache.catalina.core.StandardServer.initialize(StandardServer.java:795) 25 at org.apache.catalina.startup.Catalina.load(Catalina.java:530) 26 at org.apache.catalina.startup.Catalina.load(Catalina.java:550) 27 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 28 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 29 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 30 at java.lang.reflect.Method.invoke(Method.java:606) 31 at org.apache.catalina.startup.Bootstrap.load(Bootstrap.java:260) 32 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:412) 33 34 十一月 19, 2017 8:05:03 下午 org.apache.catalina.startup.Catalina load 35 严重: Catalina.start 36 LifecycleException: Protocol handler initialization failed: java.net.SocketException: Protocol wrong type for socket: create 37 at org.apache.catalina.connector.Connector.initialize(Connector.java:1060) 38 at org.apache.catalina.core.StandardService.initialize(StandardService.java:677) 39 at org.apache.catalina.core.StandardServer.initialize(StandardServer.java:795) 40 at org.apache.catalina.startup.Catalina.load(Catalina.java:530) 41 at org.apache.catalina.startup.Catalina.load(Catalina.java:550) 42 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 43 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 44 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 45 at java.lang.reflect.Method.invoke(Method.java:606) 46 at org.apache.catalina.startup.Bootstrap.load(Bootstrap.java:260) 47 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:412) 48 49 十一月 19, 2017 8:05:03 下午 org.apache.catalina.startup.Catalina load 50 信息: Initialization processed in 362 ms 51 十一月 19, 2017 8:05:03 下午 org.apache.catalina.core.StandardService start 52 信息: Starting service Catalina 53 十一月 19, 2017 8:05:03 下午 org.apache.catalina.core.StandardEngine start 54 信息: Starting Servlet Engine: Apache Tomcat/6.0.16 55 十一月 19, 2017 8:05:04 下午 org.apache.catalina.core.StandardContext addApplicationListener 56 信息: The listener "listeners.ContextListener" is already configured for this context. The duplicate definition has been ignored. 57 十一月 19, 2017 8:05:04 下午 org.apache.catalina.core.StandardContext addApplicationListener 58 信息: The listener "listeners.SessionListener" is already configured for this context. The duplicate definition has been ignored. 59 十一月 19, 2017 8:05:04 下午 org.apache.coyote.http11.Http11Protocol start 60 严重: Error starting endpoint 61 java.net.SocketException: Protocol wrong type for socket: create 62 at java.net.ServerSocket.createImpl(ServerSocket.java:308) 63 at java.net.ServerSocket.getImpl(ServerSocket.java:257) 64 at java.net.ServerSocket.bind(ServerSocket.java:376) 65 at java.net.ServerSocket.<init>(ServerSocket.java:237) 66 at java.net.ServerSocket.<init>(ServerSocket.java:181) 67 at org.apache.tomcat.util.net.DefaultServerSocketFactory.createSocket(DefaultServerSocketFactory.java:50) 68 at org.apache.tomcat.util.net.JIoEndpoint.init(JIoEndpoint.java:496) 69 at org.apache.tomcat.util.net.JIoEndpoint.start(JIoEndpoint.java:515) 70 at org.apache.coyote.http11.Http11Protocol.start(Http11Protocol.java:203) 71 at org.apache.catalina.connector.Connector.start(Connector.java:1131) 72 at org.apache.catalina.core.StandardService.start(StandardService.java:531) 73 at org.apache.catalina.core.StandardServer.start(StandardServer.java:710) 74 at org.apache.catalina.startup.Catalina.start(Catalina.java:578) 75 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 76 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 77 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 78 at java.lang.reflect.Method.invoke(Method.java:606) 79 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288) 80 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413) 81 82 十一月 19, 2017 8:05:04 下午 org.apache.catalina.startup.Catalina start 83 严重: Catalina.start: 84 LifecycleException: service.getName(): "Catalina"; Protocol handler start failed: java.net.SocketException: Protocol wrong type for socket: create 85 at org.apache.catalina.connector.Connector.start(Connector.java:1138) 86 at org.apache.catalina.core.StandardService.start(StandardService.java:531) 87 at org.apache.catalina.core.StandardServer.start(StandardServer.java:710) 88 at org.apache.catalina.startup.Catalina.start(Catalina.java:578) 89 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 90 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 91 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 92 at java.lang.reflect.Method.invoke(Method.java:606) 93 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288) 94 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413) 95 96 十一月 19, 2017 8:05:04 下午 org.apache.catalina.startup.Catalina start 97 信息: Server startup in 515 ms 98 十一月 19, 2017 8:05:04 下午 org.apache.catalina.core.StandardServer await 99 严重: StandardServer.await: create[8005]: 100 java.net.SocketException: Protocol wrong type for socket: create 101 at java.net.ServerSocket.createImpl(ServerSocket.java:308) 102 at java.net.ServerSocket.getImpl(ServerSocket.java:257) 103 at java.net.ServerSocket.bind(ServerSocket.java:376) 104 at java.net.ServerSocket.<init>(ServerSocket.java:237) 105 at org.apache.catalina.core.StandardServer.await(StandardServer.java:373) 106 at org.apache.catalina.startup.Catalina.await(Catalina.java:642) 107 at org.apache.catalina.startup.Catalina.start(Catalina.java:602) 108 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 109 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 110 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 111 at java.lang.reflect.Method.invoke(Method.java:606) 112 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288) 113 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413) 114 115 十一月 19, 2017 8:05:04 下午 org.apache.coyote.http11.Http11Protocol pause 116 信息: Pausing Coyote HTTP/1.1 on http-9090 117 十一月 19, 2017 8:05:04 下午 org.apache.catalina.connector.Connector pause 118 严重: Protocol handler pause failed 119 java.lang.NullPointerException 120 at org.apache.jk.server.JkMain.pause(JkMain.java:679) 121 at org.apache.jk.server.JkCoyoteHandler.pause(JkCoyoteHandler.java:153) 122 at org.apache.catalina.connector.Connector.pause(Connector.java:1073) 123 at org.apache.catalina.core.StandardService.stop(StandardService.java:563) 124 at org.apache.catalina.core.StandardServer.stop(StandardServer.java:744) 125 at org.apache.catalina.startup.Catalina.stop(Catalina.java:628) 126 at org.apache.catalina.startup.Catalina$CatalinaShutdownHook.run(Catalina.java:671) 127 128 十一月 19, 2017 8:05:05 下午 org.apache.catalina.core.StandardService stop 129 信息: Stopping service Catalina 130 十一月 19, 2017 8:05:05 下午 org.apache.catalina.connector.MapperListener destroy 131 警告: Error unregistering MBeanServerDelegate 132 java.lang.NullPointerException 133 at org.apache.catalina.connector.MapperListener.destroy(MapperListener.java:162) 134 at org.apache.catalina.connector.Connector.stop(Connector.java:1179) 135 at org.apache.catalina.core.StandardService.stop(StandardService.java:593) 136 at org.apache.catalina.core.StandardServer.stop(StandardServer.java:744) 137 at org.apache.catalina.startup.Catalina.stop(Catalina.java:628) 138 at org.apache.catalina.startup.Catalina$CatalinaShutdownHook.run(Catalina.java:671) 139 140 十一月 19, 2017 8:05:05 下午 org.apache.coyote.http11.Http11Protocol destroy 141 信息: Stopping Coyote HTTP/1.1 on http-9090 142 十一月 19, 2017 8:05:05 下午 org.apache.catalina.connector.Connector stop 143 严重: Coyote connector has not been started
============================================================================================================================================================
接下来就开始介绍我们今天的主题 -- MVC 小案例
我先大概介绍我练习的这个小案例，主要所用知识有 JSP, Servlet, C3p0, DBUtils, Mysql 数据库, IDEA 开发环境
数据库（Mysql），其中 ID 为主键，且自增，customer_name 不为空，且不可以有相同值，如下
1 CREATE TABLE customer ( 2 id INT, 3 customer_name VARCHAR(10) NOT NULL UNIQUE, 4 customer_address VARCHAR(20), 5 customer_phone VARCHAR(10), 6 customer_thing VARCHAR(20) 7 )
接着说一下我们项目的目录结构，我们这个案例并不是特别大，但也应该有好的目录结构，我们要有这样的好习惯，要不怕麻烦，为自己以后看的时候提供便利（如果有不懂的，接下来将会为大家进行详细的讲解）:
再强调一遍在 IDEA 中我们一般导包都是将所使用的包封装为一个 library，哪个项目需要就直接导入 library 就好，但是对于连接数据库应该将其加入 lib 目录，这样项目才可以找到对应的包（因为这个错，由 eclipse 转 IDEA 的我废了我好多时间）。
首先我们从获取数据库连接开始，我们使用的是 c3p0 数据库连接池，其配置文件以及获取数据库连接和释放数据库连接的代码如下（项目中的 JDBCTools.java）,这个没有什么可说的，我前面的博文也讲过。
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <c3p0-config> 3 <named-config name="mvcC3p0"> 4 <property name="user">root</property> 5 <property name="password">zy961029</property> 6 <property name="driverClass">com.mysql.jdbc.Driver</property> 7 <property name="jdbcUrl">jdbc:mysql:///sh_db</property> 8 9 <property name="maxStatements">1</property> 10 <property name="maxStatementsPerConnection">5</property> 11 </named-config> 12 </c3p0-config>
JDBCTools.java
1 package com.java.homework.db; 2 3 import com.mchange.v2.c3p0.ComboPooledDataSource; 4 5 import javax.sql.DataSource; 6 import java.sql.Connection; 7 import java.sql.SQLException; 8 9 /** 10 * 获取数据库连接和释放数据库连接的工具类 11 */ 12 public class JDBCTools { 13 14 // 由于静态代码块只执行一次，所以只会创建一份 dataSource，达到节省资源的目的，并且将工具类方法声明为静态的，可由类直接调用 15 private static DataSource dataSource = null; 16 17 static { 18 dataSource = new ComboPooledDataSource("mvcC3p0"); 19 } 20 21 public static Connection getConnection() { 22 Connection connection = null; 23 try { 24 connection = dataSource.getConnection(); 25 } catch (SQLException e) { 26 e.printStackTrace(); 27 } 28 return connection; 29 } 30 31 public static void reledse(Connection connection) { 32 try { 33 if (connection != null) { 34 connection.close(); 35 } 36 } catch (SQLException e) { 37 e.printStackTrace(); 38 } 39 } 40 }
获取到数据库连接之后我们开始我们共有的 DAO 方法，本案例中我们只是操作 Customer 一个数据表，也就只有 CustomerDao 接口，对于复杂的案例不止一个表，那么这样的一个 DAO 基类将会是特别有用的，其包括了增、删、改、查方法。同时针对我们数据表创建其对应的 domain 类。
DAO.java
1 package com.java.homework.dao; 2 3 import com.java.homework.db.JDBCTools; 4 import org.apache.commons.dbutils.QueryRunner; 5 import org.apache.commons.dbutils.handlers.BeanHandler; 6 import org.apache.commons.dbutils.handlers.BeanListHandler; 7 import org.apache.commons.dbutils.handlers.ScalarHandler; 8 9 import java.lang.reflect.ParameterizedType; 10 import java.lang.reflect.Type; 11 import java.sql.Connection; 12 import java.sql.SQLException; 13 import java.util.List; 14 15 /** 16 * 封装 CRUD 的方法， 17 * 利用 C3P0 数据库连接池和 DBUtils 连接数据库 18 * 注意 type 的获取方法，直接在构造器中初始化，为 BeanHandler、BeanListHandler 传值 19 * @param <T> 当前 DAO 处理的实体类类型 20 */ 21 public class DAO<T> { 22 23 private QueryRunner queryRunner; 24 private Class<T> type; 25 26 /* 27 * 构造器直接初始化 queryRunner 对象和 BeanHandler 和 BeanListHandler 所需要的 Class 对象（利用反射创建，使其更有可扩展性） 28 * */ 29 public DAO() { 30 queryRunner = new QueryRunner(); 31 32 Type superclass = getClass().getGenericSuperclass(); 33 if (superclass instanceof ParameterizedType) { 34 ParameterizedType parameterizedType = (ParameterizedType) superclass; 35 36 Type[] args = parameterizedType.getActualTypeArguments(); 37 if (args != null && args.length > 0) { 38 if (args[0] instanceof Class) { 39 type = (Class<T>) args[0]; 40 } 41 } 42 } 43 } 44 45 /* 46 * 封装了增删改的方法 47 * */ 48 public void update(String sql, Object...args) { 49 Connection connection = JDBCTools.getConnection(); 50 try { 51 queryRunner.update(connection, sql, args ); 52 } catch (SQLException e) { 53 e.printStackTrace(); 54 }finally { 55 JDBCTools.reledse(connection); 56 } 57 } 58 59 /* 60 获取单个值，比如某一列的某一个值，或 count 值 61 */ 62 public Object getForValue(String sql, Object...args){ 63 Connection connection = JDBCTools.getConnection(); 64 Object value = null; 65 try { 66 value = queryRunner.query(connection, sql, new ScalarHandler(), args); 67 } catch (SQLException e) { 68 e.printStackTrace(); 69 } finally { 70 JDBCTools.reledse(connection); 71 } 72 return value; 73 } 74 75 /* 76 * 获取多行值，封装为 List 返回 77 * */ 78 public List<T> getForList(String sql, Object...args) { 79 Connection connection = JDBCTools.getConnection(); 80 List<T> list = null; 81 try { 82 list = queryRunner.query(connection, sql, new BeanListHandler<T>(type),args); 83 } catch (SQLException e) { 84 e.printStackTrace(); 85 }finally { 86 JDBCTools.reledse(connection); 87 } 88 return list; 89 } 90 91 /* 92 * 获取单列值 93 * */ 94 public T get(String sql, Object...args){ 95 Connection connection = JDBCTools.getConnection(); 96 T entity = null; 97 try { 98 entity = queryRunner.query(connection, sql, new BeanHandler<T>(type), args); 99 } catch (SQLException e) { 100 e.printStackTrace(); 101 }finally { 102 JDBCTools.reledse(connection); 103 } 104 return entity; 105 } 106 }
Customer.java
1 package com.java.homework.domain; 2 3 public class Customer { 4 5 private Integer id; 6 private String customerName; 7 private String customerAddress; 8 private String customerPhone; 9 private String customerThing; 10 11 @Override 12 public String toString() { 13 return "Customer{" + 14 "id=" + id + 15 ", customerName='" + customerName + '\'' + 16 ", customerAddress='" + customerAddress + '\'' + 17 ", customerPhone='" + customerPhone + '\'' + 18 ", customerThing='" + customerThing + '\'' + 19 '}'; 20 } 21 22 public Integer getId() { 23 return id; 24 } 25 26 public void setId(Integer id) { 27 this.id = id; 28 } 29 30 public String getCustomerName() { 31 return customerName; 32 } 33 34 public void setCustomerName(String customerName) { 35 this.customerName = customerName; 36 } 37 38 public String getCustomerAddress() { 39 return customerAddress; 40 } 41 42 public void setCustomerAddress(String customerAddress) { 43 this.customerAddress = customerAddress; 44 } 45 46 public String getCustomerPhone() { 47 return customerPhone; 48 } 49 50 public void setCustomerPhone(String customerPhone) { 51 this.customerPhone = customerPhone; 52 } 53 54 public String getCustomerThing() { 55 return customerThing; 56 } 57 58 public void setCustomerThing(String customerThing) { 59 this.customerThing = customerThing; 60 } 61 62 // 为了方便测试我们写一个带参的构造器 63 public Customer(Integer id, String customerName, String customerAddress, String customerPhone, String customerThing) { 64 this.id = id; 65 this.customerName = customerName; 66 this.customerAddress = customerAddress; 67 this.customerPhone = customerPhone; 68 this.customerThing = customerThing; 69 } 70 // 由于需要利用反射，所以必须写无参构造器 71 public Customer() { 72 } 73 }
有了 DAO 类之后我们便要针对于我们案例去定制操作数据表的方法，并将其封装为一个接口：
CustomerDao.java
1 package com.java.homework.dao; 2 3 import com.java.homework.criteria.CriteriaCustomer; 4 import com.java.homework.domain.Customer; 5 6 import java.sql.SQLException; 7 import java.util.List; 8 9 /** 10 * 针对本数据表封装所需功能的方法，将其封装为一个接口 11 */ 12 public interface CustomerDao { 13 // 获得数据表的所有值 14 List<Customer> getAll(); 15 // 根据 id 获取单行 16 Customer get(Integer id) throws SQLException; 17 // 执行删除操作 18 void delete(Integer id) throws SQLException; 19 // 执行保存操作 20 void save(Customer customer) throws SQLException; 21 // 根据 customerName 获取与之匹配行的行数 22 Long getCountWithName(String name1) throws SQLException; 23 // 模糊查询，将查询条件封装为一个单类，CriteriaCustomer。因为有些查询条件并不是像本案例一样和 Customer 基类一样 24 List<Customer> getListOfLike(CriteriaCustomer criteriaCustomer); 25 // 执行更新操作 26 void update(Customer customer, Integer id); 27 // 获取 customerName 28 Object getCusName(Integer id); 29 }
接下来就是实现我们所写的接口，该类 继承自 DAO.java 和 CustomerDao.java 实现 CustomerDao.java 方法，如下：
CustomerDaoImpl.java
1 package com.java.homework.daoImpl; 2 3 import com.java.homework.criteria.CriteriaCustomer; 4 import com.java.homework.dao.CustomerDao; 5 import com.java.homework.dao.DAO; 6 import com.java.homework.domain.Customer; 7 8 import java.sql.SQLException; 9 import java.util.List; 10 11 /** 12 * 继承 DAO 类实现 CustomerDao 接口 13 */ 14 public class CustomerDaoImpl extends DAO<Customer> implements CustomerDao { 15 16 @Override 17 public List<Customer> getAll() { 18 String sql = "SELECT id, customer_name customerName, customer_address customerAddress, customer_phone customerPhone," + 19 "customer_thing customerThing FROM customer"; 20 List<Customer> customerList; 21 customerList = getForList(sql); 22 return customerList; 23 } 24 25 @Override 26 public Customer get(Integer id) throws SQLException { 27 String sql = "SELECT id,customer_name customerName, customer_address customerAddress, customer_phone customerPhone," + 28 "customer_thing customerThing FROM customer WHERE id=?"; 29 return get(sql, id); 30 } 31 32 @Override 33 public void delete(Integer id) throws SQLException { 34 String sql = "DELETE FROM customer WHERE id=?"; 35 update(sql, id); 36 } 37 38 @Override 39 public void save(Customer customer) throws SQLException { 40 String sql = "INSERT INTO customer(customer_name, customer_address, customer_phone, customer_thing) VALUES(" + 41 "?,?,?,?)"; 42 update(sql, customer.getCustomerName(), customer.getCustomerAddress(), customer.getCustomerPhone(), customer.getCustomerThing()); 43 } 44 45 @Override 46 public Long getCountWithName(String name) throws SQLException { 47 Long value; 48 String sql = "SELECT COUNT(id) FROM customer WHERE customer_name=?"; 49 value = (Long) getForValue(sql, name); 50 return value; 51 } 52 53 @Override 54 public List<Customer> getListOfLike(CriteriaCustomer criteriaCustomer) { 55 String sql = "SELECT id, customer_name customerName, customer_address customerAddress, customer_phone customerPhone," + 56 "customer_thing customerThing FROM customer WHERE customer_name LIKE ? AND customer_address LIKE ? AND " + 57 "customer_phone LIKE ? AND customer_thing LIKE ?"; 58 59 return getForList(sql, criteriaCustomer.getCustomerName(), criteriaCustomer.getCustomerAddress(), criteriaCustomer 60 .getCustomerPhone(), criteriaCustomer.getCustomerThing()); 61 } 62 63 @Override 64 public void update(Customer customer, Integer id) { 65 String sql = "UPDATE customer SET customer_name=?, customer_address=?, customer_phone=?," + 66 " customer_thing=? WHERE id=?"; 67 update(sql, customer.getCustomerName(), customer.getCustomerAddress(), customer.getCustomerPhone(), customer.getCustomerThing(), id); 68 } 69 70 @Override 71 public Object getCusName(Integer id) { 72 String sql = "SELECT customer_name customerName FROM customer WHERE id=?"; 73 return getForValue(sql, id); 74 } 75 }
写到这里我们已经写了不少的方法了，在一个案例甚至以后的项目中我们每写好一个方法都应该去测试，以免后面大量的错误不容易定位，下面的代码包括了我所写的所有的测试方法，后面的几个的方法就不在单独放出了，记得都在这就好：
1 package homeworl.test; 2 3 import com.java.homework.criteria.CriteriaCustomer; 4 import com.java.homework.dao.CustomerDao; 5 import com.java.homework.daoImpl.CustomerDaoImpl; 6 import com.java.homework.db.JDBCTools; 7 import com.java.homework.domain.Customer; 8 import org.junit.Test; 9 10 import java.sql.Connection; 11 import java.sql.SQLException; 12 import java.util.List; 13 14 /** 15 * Created by shkstart on 2017/11/13. 16 */ 17 public class TestCustomerDaoImpl { 18 19 private CustomerDao customerDao = new CustomerDaoImpl(); 20 @Test 21 public void testGetForList() { 22 List<Customer> customerList = customerDao.getAll(); 23 System.out.println(customerList); 24 } 25 26 @Test 27 public void testGetConnection() { 28 Connection connection = JDBCTools.getConnection(); 29 System.out.println(connection); 30 } 31 32 @Test 33 public void testGet() throws SQLException { 34 Customer customer = customerDao.get(1); 35 System.out.println(customer); 36 } 37 38 @Test 39 public void testGetForValue() throws SQLException { 40 System.out.println(customerDao.getCountWithName("a")); 41 } 42 43 @Test 44 public void testAdd() throws SQLException { 45 Customer customer = new Customer(null,"dsa", "dad", "sdas", "dsa"); 46 customerDao.save(customer); 47 } 48 49 // 按条件查询的测试方法 50 @Test 51 public void testGetListForLike() { 52 CriteriaCustomer criteriaCustomer = new CriteriaCustomer("a","s",null, null); 53 List<Customer> customerList = customerDao.getListOfLike(criteriaCustomer); 54 System.out.println(customerList); 55 } 56 57 @Test 58 public void testUpdate() { 59 Customer customer = new Customer(null, "dsa", "dad", "sdas", "dsa"); 60 customerDao.update(customer, 19); 61 } 62 63 @Test 64 public void testGetUserName() { 65 Object cusName = customerDao.getCusName(6); 66 System.out.println(cusName); 67 } 68 }
写完上面的代码并将其测试成功之后我们便可以利用这些代码去写页面的部分功能，首先我们实现点击 Submit 按钮之后将数据表中的所有信息都打印出来。首先贴出一张实现后的动图，如下：
其页面代码如下，Index.jsp（我们还没有使用 EL 和 JSTL 而是在页面中直接使用 java 代码，使用了 EL 和 JSTL 之后将会使代码更整洁）
<%@ page import="com.java.homework.domain.Customer" %> <%@ page import="java.util.List" %><%-- Created by IntelliJ IDEA. User: yin‘zhao Date: 2017/11/13 Time: 16:38 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Customer</title> </head> </html> <body> <table cellspacing="10"> <form action="list.do" method="post"> <tr> <td>CusName:</td> <td><input type="text" name="cus_name"></td> </tr> <tr> <td>CusAddress:</td> <td><input type="text" name="cus_address"></td> </tr> <tr> <td>CusPhone:</td> <td><input type="text" name="cus_phone"></td> </tr> <tr> <td>CusThing:</td> <td><input type="text" name="cus_thing"></td> </tr> <tr> <td> <button type="submit">Submit</button> </td> <%--转到 add.jsp 进行添加用户--%> <td><a href="add.jsp">CreateNewCustomer</a></td> </tr> </form> </table> <table border="1" cellpadding="10" cellspacing="0"> <%-- java 代码写在元素中，不应直接写在 body 中 --%> <%-- 获取所有的 customer 判断若不为空则将其打印在页面上 --%> <% List<Customer> customerList = (List<Customer>) request.getAttribute("customerList"); if (customerList != null && customerList.size() > 0) { %> <tr> <th>CUSNAME</th> <th>CUSADDRESS</th> <th>CUSPHONE</th> <th>CUSTHING</th> <th>DELETE/UPDATE</th> </tr> <% for (int i = 0; i < customerList.size(); i++) { %> <tr> <td><%= customerList.get(i).getCustomerName()%> </td> <td><%= customerList.get(i).getCustomerAddress()%> </td> <td><%= customerList.get(i).getCustomerPhone()%> </td> <td><%= customerList.get(i).getCustomerThing()%> </td> <%--执行删除和更新操作--%> <td><a href="delete.do?id=<%= customerList.get(i).getId()%>" class="delete">DELETE</a> <a href="query.do?id=<%= customerList.get(i).getId()%>">UPDATE</a></td> </tr> <% } %> <% } %> </table> </body> </html>
对于 index.jsp 我们提交到的是 list.do 而不是某一个 servlet，不要惊讶，因为我们的 servlet 的映射为 *.do，这样便实现了多个请求使用同一个 Servlet 的第一步，把每次提交的地址写为 xxx.do，然后在 Servlet 中利用反射根据所请求的参数执行不同方法，代码如下：
web.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" 5 version="3.1"> 6 <servlet> 7 <servlet-name>AllServlet</servlet-name> 8 <servlet-class>com.java.homework.controller.AllServlet</servlet-class> 9 </servlet> 10 <servlet-mapping> 11 <servlet-name>AllServlet</servlet-name> 12 <!-- 注意是 *.do --> 13 <url-pattern>*.do</url-pattern> 14 </servlet-mapping> 15 </web-app>
首先贴出 AllServlet.java 中利用反射执行不同方法的代码，如下：
1 package com.java.homework.controller; 2 3 import com.java.homework.criteria.CriteriaCustomer; 4 import com.java.homework.dao.CustomerDao; 5 import com.java.homework.daoImpl.CustomerDaoImpl; 6 import com.java.homework.domain.Customer; 7 8 import javax.servlet.ServletException; 9 import javax.servlet.http.HttpServletRequest; 10 import javax.servlet.http.HttpServletResponse; 11 import java.io.IOException; 12 import java.lang.reflect.InvocationTargetException; 13 import java.lang.reflect.Method; 14 import java.sql.SQLException; 15 import java.util.List; 16 17 /** 18 * 使用反射实现多个请求使用同一个 Servlet，利用反射 19 */ 20 public class AllServlet extends javax.servlet.http.HttpServlet { 21 22 private CustomerDao customerDao = new CustomerDaoImpl(); 23 24 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws javax.servlet.ServletException, IOException { 25 // 获取 servletPath，为 /xxx.do 26 String servletPath = request.getServletPath(); 27 // 去除 servletPath 前面的 / 和后面的 .do 28 String methodName = servletPath.substring(1).substring(0, servletPath.length() - 4); 29 try { 30 // 利用反射执行方法名为上述字符串的方法 31 Method method = getClass().getDeclaredMethod(methodName, HttpServletRequest.class, HttpServletResponse.class); 32 method.invoke(this, request, response); 33 } catch (NoSuchMethodException e) { 34 e.printStackTrace(); 35 } catch (IllegalAccessException e) { 36 e.printStackTrace(); 37 } catch (InvocationTargetException e) { 38 e.printStackTrace(); 39 } 40 } 41 42 // 若为 get 请求则执行 post 请求的方法 43 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 44 doPost(request, response); 45 } 46 }
上面的代码实现我们的需求 ---- 多个请求使用一个 Servlet。接下来我们在前面的功能上加上查询条件的查询，再贴出代码，展示如下：
上面 GIF 展示了在查询的基础上我们加了查询条件，如果查询条件为空我们就将所有的数据打印出来，但若查询条件不为空我们就将符合条件的数据打印出来。
我们首先要知道带查询条件的 SQL 语句应该如何写，如
SELECT * FROM customer WHERE customer_name LIKE '%xx%' AND customer_address LIKE '%xx%' AND customer_phone LIKE '%123%' AND customer_thing LIKE '%%'
首先我们需要将查询条件封装为一个单独类。上面的语句表示 customer_name 的值中包含 xx，customer_phone 中包含 123，且其他两个条件不受限制的写法。由于在案例中某一个条件为空时不能不写，所以将其条件为空的时候在 CriteriaCustomer 将其处理为 %%，等同于没有查询条件，其具体用在 CustomerDaoImpl.java 类中的 getForListOfLike(CriteriaCustomer criteriaCustomer) 方法中，详情可参见其方法体。
CriteriaCustomer.java
1 package com.java.homework.criteria; 2 3 /** 4 * 封装查询条件的类 5 */ 6 public class CriteriaCustomer { 7 private String customerName; 8 private String customerAddress; 9 private String customerPhone; 10 private String customerThing; 11 12 public String getCustomerName() { 13 // 如果其为空就将其置为 %%，否则将查询条件加在 %% 中间，拼出正确的 SQL 语句 14 if (customerName == null) { 15 customerName = "%%"; 16 } else { 17 customerName = "%" + customerName + "%"; 18 } 19 return customerName; 20 } 21 22 public String getCustomerAddress() { 23 if (customerAddress == null) { 24 customerAddress = "%%"; 25 } else { 26 customerAddress = "%" + customerAddress + "%"; 27 } 28 return customerAddress; 29 } 30 31 public String getCustomerPhone() { 32 if (customerPhone == null) { 33 customerPhone = "%%"; 34 } else { 35 customerPhone = "%" + customerPhone + "%"; 36 } 37 return customerPhone; 38 } 39 40 public String getCustomerThing() { 41 if (customerThing == null) { 42 customerThing = "%%"; 43 } else { 44 customerThing = "%" + customerThing + "%"; 45 } 46 return customerThing; 47 } 48 49 public CriteriaCustomer(String customerName, String customerAddress, String customerPhone, String customerThing) { 50 this.customerName = customerName; 51 this.customerAddress = customerAddress; 52 this.customerPhone = customerPhone; 53 this.customerThing = customerThing; 54 } 55 }
下面我们看 AllServlet.java 中的 list 方法（在 index.jsp 页面的显示代码可参见上面 index.jsp 代码，我觉得不是很难理解），如下：
1 // 显示数据表所有的信息 2 protected void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 3 List<Customer> customerList; 4 String cusName = request.getParameter("cus_name"); 5 String cusAddress = request.getParameter("cus_address"); 6 String cusPhone = request.getParameter("cus_phone"); 7 String cusThing = request.getParameter("cus_thing"); 8 // 将查询条件封装为 CriteriaCustomer 对象 9 CriteriaCustomer criteriaCustomer = new CriteriaCustomer(cusName, cusAddress, cusPhone, cusThing); 10 11 if (cusName == null) { 12 // 执行查询所有数据 13 customerList = customerDao.getAll(); 14 } else { 15 // 执行按条件查询 16 customerList = customerDao.getListOfLike(criteriaCustomer); 17 } 18 // 将查询到的 List 对象添加到 request 域对象中，并转发到 index.jsp 中，在页面上显示数据库信息 19 request.setAttribute("customerList", customerList); 20 request.getRequestDispatcher("/index.jsp").forward(request, response); 21 }
到这里我们就讲完了查询包括带条件和不带条件查询，接下来我们讲解删除和更新用户信息操作，删除操作可能很简单只需要调用 CustomerDaoImpl 的 delete() 方法即可，那么想一想会应该如何定位我们操作的是哪行信息？
对于更新操作我们在点击更新的超链接之后跳转到 update.jsp 之后首先要将其信息回显出来，我们要求 customer_name 属性必须唯一，所以我们在更新的时候必须在提交的时候查询数据库是否合法，若合法则执行更新，否则提示错误消息 XXX 已经被占用，我们该如何判断我们更新的 username 是否存在？如果查询 username 为所改值的列数来判断是否存在相同的 username，由于改之前还没有提交同时我们如果没有提交的话那么如何将它原来的 username 行排除在外？
在 index.jsp 页面上点击删除的超链接将请求提交到 delete.do，在 AllServlet 中执行 delete方法，执行删除操作，delete() 方法代码如下：
1 protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 2 String id = request.getParameter("id"); 3 try { 4 customerDao.delete(Integer.parseInt(id)); 5 } catch (SQLException e) { 6 e.printStackTrace(); 7 } 8 // 应该将重定向放于 try 块之后，那么如果出现异常也会转到 list.do， 9 response.sendRedirect("list.do"); 10 }
update.jsp
1 <%@ page import="com.java.homework.domain.Customer" %><%-- 2 Created by IntelliJ IDEA. 3 User: yin‘zhao 4 Date: 2017/11/13 5 Time: 23:45 6 To change this template use File | Settings | File Templates. 7 --%> 8 <%@ page contentType="text/html;charset=UTF-8" language="java" %> 9 <html> 10 <head> 11 <title>UpdateCustomer</title> 12 </head> 13 <body> 14 <table cellspacing="10"> 15 <%--保存错误消息，用户名重复时错误消息--%> 16 <% 17 String message = (String) request.getAttribute("message"); 18 Customer customer = (Customer) request.getAttribute("customer"); 19 %> 20 <h3><% 21 if (message != null) { 22 out.print(message); 23 } 24 %></h3> 25 <form action="update.do" method="post"> 26 <input type="hidden" name="id" value=<%=customer.getId()%>> 27 <tr> 28 <td>CusName:</td> 29 <td><input type="text" name="cus_name" value=<%=customer.getCustomerName() %>></td> 30 </tr> 31 <tr> 32 <td>CusAddress:</td> 33 <td><input type="text" name="cus_address" value=<%=customer.getCustomerAddress() %>></td> 34 </tr> 35 <tr> 36 <td>CusPhone:</td> 37 <td><input type="text" name="cus_phone" value=<%=customer.getCustomerPhone()%>></td> 38 </tr> 39 <tr> 40 <td>CusThing:</td> 41 <td><input type="text" name="cus_thing" value=<%=customer.getCustomerThing()%>></td> 42 </tr> 43 <tr> 44 <td> 45 <button type="submit">Update</button> 46 </td> 47 </tr> 48 </form> 49 </table> 50 </body> 51 </html>
AllServlet 中的 update 方法和为了第一次回显所用到的 query 方法
1 { 2 Customer customer = getCustomer(request); 3 Integer id = customer.getId(); 4 String cusName = customer.getCustomerName(); 5 String cusNameReal = (String) customerDao.getCusName(id); 6 try { 7 Long count = customerDao.getCountWithName(cusName); 8 System.out.println(count); 9 // 如果所传入的用户名的行数大于0，分为两种情况，没改存在一个，改了存在相同的 10 // 由于或运算是计算了前面的若为假就不在执行后面的所以，当用户名相同时不判断后面的部分为正常操作，若不相等且有重复的则回馈错误信息 11 // 也可以将旧的用户名存为一个隐藏表单，用上面同样的方法进行处理，与之相比少了对数据库的请求 12 // 使用 equalsIgnoreCase 可以忽略大小写进行比较，那么在将当前值改为大写或者小写的时候就不会返回错误信息 13 if (!(cusNameReal.equalsIgnoreCase(cusName)) && (count > 0)) { 14 String message = "用户名" + cusName + "已经被占用！"; 15 // 如果因为用户名重复那么就将传入 request 域中的 name 属性改为最原始的，其他属性为改好的 16 customer.setCustomerName(cusNameReal); 17 request.setAttribute("message", message); 18 request.setAttribute("customer", customer); 19 request.getRequestDispatcher("update.jsp").forward(request, response); 20 } else { 21 customerDao.update(customer, id); 22 response.sendRedirect("list.do"); 23 } 24 } catch (SQLException e) { 25 e.printStackTrace(); 26 } 27 }
我们由代码可知，对于删除操作中如何定位删除的是哪行是通过点击删除的超链接的时候将该行的 id 同时也传过去；对于更新操作其第u一次回显是先将请求给 query 方法，query方法通过传过来的 id 属性将 customer 对象转发到更新页面完成回显，再次提交到 update 方法中，通过 customer_username 获取行数，其 customer_username 是通过 update.jsp 中的隐藏值将 id 传到 servlet，通过 id 获取 Customer 对象，并在用户名出错的情况下将 Customer 对象返回，保存修改以前的信息，具体可以去读代码。
删除、更新和查询都处理完了就剩添加了，通过前面的 GIF 也可以看到我们 index.jsp 有一个 CreateNewCustomer 超链接，没错就是添加新用户，它和更新一样也需要去查询数据库判断是否存在相同的值，也需要报错，同时它需要信息的完整性，如果不完整也会报错！代码和演示如下：
点击超链接到 add.jsp；
add.jsp
1 <%@ page import="com.java.homework.domain.Customer" %><%-- 2 Created by IntelliJ IDEA. 3 User: yin‘zhao 4 Date: 2017/11/13 5 Time: 23:45 6 To change this template use File | Settings | File Templates. 7 --%> 8 <%@ page contentType="text/html;charset=UTF-8" language="java" %> 9 <html> 10 <head> 11 <title>CreateNewCustomer</title> 12 </head> 13 </html> 14 <body> 15 <br> 16 <h3> 17 <%--保存错误消息，当信息不完整和用户名重复时的错误消息--%> 18 <% 19 String message1 = (String) request.getAttribute("message1"); 20 String message2 = (String) request.getAttribute("message2"); 21 if (message1 != null) { 22 out.print(message1); 23 } 24 25 if (message2 != null) { 26 out.print(message2); 27 } 28 %></h3> 29 <br> 30 <table cellspacing="10"> 31 <form action="add.do" method="post"> 32 <tr> 33 <td>CusName:</td> 34 <td><input type="text" name="cus_name" 35 value=<%= request.getParameter("cus_name") == null ? "" : request.getParameter("cus_name")%>></td> 36 </tr> 37 <tr> 38 <td>CusAddress:</td> 39 <td><input type="text" name="cus_address" 40 value=<%= request.getParameter("cus_address") == null ? "" : request.getParameter("cus_address")%>></td> 41 </tr> 42 <tr> 43 <td>CusPhone:</td> 44 <td><input type="text" name="cus_phone" 45 value=<%= request.getParameter("cus_phone") == null ? "" : request.getParameter("cus_phone")%>></td> 46 </tr> 47 <tr> 48 <td>CusThing:</td> 49 <td><input type="text" name="cus_thing" 50 value=<%= request.getParameter("cus_thing") == null ? "" : request.getParameter("cus_thing")%>></td> 51 </tr> 52 <tr> 53 <td> 54 <button type="submit">Submit</button> 55 </td> 56 </tr> 57 </form> 58 </table> 59 </body> 60 </html>
AllServlet 中的 add 方法
1 { 2 String cusName = request.getParameter("cus_name"); 3 String cusAddress = request.getParameter("cus_address"); 4 String cusPhone = request.getParameter("cus_phone"); 5 String cusThing = request.getParameter("cus_thing"); 6 Customer customer = new Customer(null, cusName, cusAddress, cusPhone, cusThing); 7 Long count = null; 8 try { 9 count = customerDao.getCountWithName(cusName); 10 } catch (SQLException e) { 11 e.printStackTrace(); 12 } 13 14 if (cusName.trim().equals("") || cusAddress.trim().equals("") || cusPhone.trim().equals("") || cusThing.trim().equals("")) { 15 request.setAttribute("message1", "请输入完整的信息后在确认！"); 16 request.getRequestDispatcher("add.jsp").forward(request, response); 17 return; 18 } 19 if (count > 0) { 20 request.setAttribute("message2", "用户名" + cusName + "已经被占用"); 21 request.getRequestDispatcher("add.jsp").forward(request, response); 22 return; 23 } 24 try { 25 customerDao.save(customer); 26 // 如果使用转发，那么新加的页面和list 页面将会冲突，导致list 页面将会执行模糊查询 27 // request.getRequestDispatcher("list.do").forward(request, response); 28 response.sendRedirect("list.do"); 29 } catch (SQLException e) { 30 e.printStackTrace(); 31 } 32 }
到这里我们的这个小案例就讲解的差不多了，有什么讲解的纰漏还望指出，谢谢！
