Zero. Database tables and configuration files
0.1 Database t_customer table
CREATE TABLE `t_customer` ( `cid` char(32) NOT NULL, `cname` varchar(40) NOT NULL, `gender` varchar(6) NOT NULL, `birthday` date DEFAULT NULL, `cellphone` varchar(15) NOT NULL, `email` varchar(40) DEFAULT NULL, `description` varchar(500) DEFAULT NULL, PRIMARY KEY (`cid`) );
0.2 c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- c3p0 Default configuration
If in code“ ComboPooledDataSource ds = new ComboPooledDataSource();"
Writing in this way means using C3P0 Default(default)Configuration information to create data sources -->
<default-config>
<!-- Connecting Four Parameters Configuration -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/customers</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- Pool parameter configuration -->
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!-- c3p0 Named configuration
If in code“ ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");"
Writing in this way means using name yes MySQL Configuration information to create data sources -->
<named-config name="MySQL">
<!-- Connecting Four Parameters Configuration -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/customers</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- Pool parameter configuration -->
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>0.3 db.properties
driver = com.mysql.jdbc.Driver url = jdbc:mysql://localhost:3306/customers?rewriteBatchedStatements=true username = root password =root
0.4 JdbcUtils.java
package waf.yty.cstm.utils;
Batch insertion data will be used!
package waf.yty.cstm.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
// Read the database connection information in db.properties file
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
prop.load(in);
// Getting database parameters
driver = prop.getProperty("driver");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
// Loading database driver
Class.forName(driver);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* Get the database connection object
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void release(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}0.5 CustomerTest.java
package waf.yty.cstm.dao;
Batch operation!
package waf.yty.cstm.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import cn.itcast.commons.CommonUtils;
import waf.yty.cstm.utils.JdbcUtils;
public class CustomerTest {
@Test
public void fun1() throws SQLException {
// CustomerDao dao = new CustomerDao();
// for
ResultSet rs = null;
Connection con = JdbcUtils.getConnection();
String sql = "insert into t_customer values (?,?,?,?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(sql);
for (int i = 0; i < 300; i++) {
pstmt.setString(1, CommonUtils.uuid());
pstmt.setString(2, "cstm_" + i);
pstmt.setString(3, i%2==0?"male":"female");
pstmt.setDate(4, new java.sql.Date(1992-11-12));
pstmt.setString(5, "666" + i);
pstmt.setString(6, "cstm_" + i + "@126.com");
pstmt.setString(7, "WDNMD");
pstmt.addBatch();
}
long start = System.currentTimeMillis();
pstmt.executeBatch();
long end = System.currentTimeMillis();
JdbcUtils.release(con, pstmt, rs);
System.out.println(end - start);
}
}0.6 BaseServlet
package cn.itcast.servlet;
import java.io.IOException;
import java.lang.reflect.Method;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* BaseServlet Used as the parent class of other servlets
*
* @author qdmmy6
*
* There are many request processing methods in one class. The prototype of each request processing method is the same as service. Prototype = return value type + method name + parameter list
*/
@SuppressWarnings("serial")
public class BaseServlet extends HttpServlet {
@Override
public void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");//Processing response code
request.setCharacterEncoding("UTF-8");
/**
* 1. Get the method parameter, which is the method that the user wants to call 2. Make the method name an instance object of the Method class 3. Call the method through invoke().
*/
String methodName = request.getParameter("method");
Method method = null;
/**
* 2. Getting Method Objects by Method Name
*/
try {
method = this.getClass().getMethod(methodName,
HttpServletRequest.class, HttpServletResponse.class);
} catch (Exception e) {
throw new RuntimeException("The method you want to call:" + methodName + "It does not exist!", e);
}
/**
* 3. Call it through the method object
*/
try {
String result = (String)method.invoke(this, request, response);
if(result != null && !result.trim().isEmpty()) {//If the request processing method returns not empty
int index = result.indexOf(":");//Get the location of the first colon
if(index == -1) {//If there is no colon, use forwarding
request.getRequestDispatcher(result).forward(request, response);
} else {//If a colon exists
String start = result.substring(0, index);//Segmentation of prefixes
String path = result.substring(index + 1);//Dividing Path
if(start.equals("f")) {//The prefix f denotes forwarding
request.getRequestDispatcher(path).forward(request, response);
} else if(start.equals("r")) {//The prefix r denotes redirection
response.sendRedirect(request.getContextPath() + path);
}
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}0.7 TxQueryRunner.class
package cn.itcast.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
public class TxQueryRunner extends QueryRunner {
@Override
public int[] batch(String sql, Object[][] params) throws SQLException {
Connection con = JdbcUtils.getConnection();
int[] result = super.batch(con, sql, params);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
Connection con = JdbcUtils.getConnection();
T result = super.query(con, sql, rsh, params);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection con = JdbcUtils.getConnection();
T result = super.query(con, sql, rsh);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public int update(String sql) throws SQLException {
Connection con = JdbcUtils.getConnection();
int result = super.update(con, sql);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public int update(String sql, Object param) throws SQLException {
Connection con = JdbcUtils.getConnection();
int result = super.update(con, sql, param);
JdbcUtils.releaseConnection(con);
return result;
}
@Override
public int update(String sql, Object... params) throws SQLException {
Connection con = JdbcUtils.getConnection();
int result = super.update(con, sql, params);
JdbcUtils.releaseConnection(con);
return result;
}
}
0.8 JdbcUtils.class
0.7 TxQueryRunner.class uses this JdbcUtils class!
package cn.itcast.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* Using this class of methods, you must provide the c3p0-copnfig.xml file
* @author qdmmy6
*/
public class JdbcUtils {
// Hungry man
private static DataSource ds = new ComboPooledDataSource();
/**
* It denotes null that there is no transaction
* It does not represent a transaction for null
* When a transaction is opened, it needs to be assigned a value
* When a transaction ends, it needs to be assigned a value of null
* And when you open a transaction, let multiple methods of dao share the Connection
*/
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
public static DataSource getDataSource() {
return ds;
}
/**
* dao Use this method to get connections
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
/*
* If there is a transaction, return the con of the current transaction
* If there are no transactions, the new con is returned through the connection pool
*/
Connection con = tl.get();//Get the transaction connection for the current thread
if(con != null) return con;
return ds.getConnection();
}
/**
* Open transaction
* @throws SQLException
*/
public static void beginTransaction() throws SQLException {
Connection con = tl.get();//Get the transaction connection of the current thread
if(con != null) throw new SQLException("Business has been started, can not be repeated open!");
con = ds.getConnection();//Assigning a value to con indicates that a transaction has been opened
con.setAutoCommit(false);//Set to manual submission
tl.set(con);//Put the current transaction connection in tl
}
/**
* Submission of affairs
* @throws SQLException
*/
public static void commitTransaction() throws SQLException {
Connection con = tl.get();//Get the transaction connection for the current thread
if(con == null) throw new SQLException("No transaction can not be committed!");
con.commit();//Submission of affairs
con.close();//Close connection
con = null;//Represents the end of the transaction!
tl.remove();
}
/**
* Rollback transaction
* @throws SQLException
*/
public static void rollbackTransaction() throws SQLException {
Connection con = tl.get();//Get the transaction connection for the current thread
if(con == null) throw new SQLException("No transaction can be rolled back!");
con.rollback();
con.close();
con = null;
tl.remove();
}
/**
* Release Connection
* @param con
* @throws SQLException
*/
public static void releaseConnection(Connection connection) throws SQLException {
Connection con = tl.get();//Get the transaction connection for the current thread
if(connection != con) {//If the parameter connection is different from the current transaction connection, it means that the connection is not the current transaction and can be closed!
if(connection != null &&!connection.isClosed()) {//If the parameter connection is not closed, close it!
connection.close();
}
}
}
}I. Customer.java
package waf.yty.cstm.domain;
The member variable name must be the same as the column name in the database table.
package waf.yty.cstm.domain;
/**
* Domain object
* Correspond to forms and database tables
* @author yangtengyu
*
*/
public class Customer {
private String cid;
private String cname;
private String gender;
private String birthday;
private String cellphone;
private String email;
private String description;
public String getCid() {
return cid;
}
public void setCid(String cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String 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 getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
@Override
public String toString() {
return "Customer [cid=" + cid + ", cname=" + cname + ", gender=" + gender + ", birthday=" + birthday
+ ", cellphone=" + cellphone + ", email=" + email + ", description=" + description + "]";
}
}
CustomerDao.java
package waf.yty.cstm.dao;
Interactive with the database, add, delete, modify and check the specific implementation!
package waf.yty.cstm.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.text.DefaultEditorKit.CutAction;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import cn.itcast.jdbc.TxQueryRunner;
import waf.yty.cstm.domain.Customer;
public class CustomerDao {
private QueryRunner qr = new TxQueryRunner();
/**
* Add customer
* @param c
*/
public void add(Customer c) {
try {
String sql = "insert into t_customer values (?,?,?,?,?,?,?)";
Object[] params = {c.getCid(),c.getCname(),c.getGender(),
c.getBirthday(),c.getCellphone(),c.getEmail(),c.getDescription()};
qr.update(sql,params);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* Query all
* @return
*/
public List<Customer> findAll() {
try {
String sql = "select * from t_customer";
return qr.query(sql, new BeanListHandler<Customer>(Customer.class));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* Load the customer to be modified
* @param cid
* @return
*/
public Customer load(String cid) {
try {
String sql = "select * from t_customer where cid=?";
return qr.query(sql, new BeanHandler<Customer>(Customer.class), cid);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* Modifying individual customers
* @param c
*/
public void edit(Customer c) {
try {
String sql = "update t_customer set cname=?, gender=?, birthday=?," +
"cellphone=?, email=?, description=? where cid=?;";
Object[] params = {c.getCname(),c.getGender(),c.getBirthday(),
c.getCellphone(),c.getEmail(),c.getDescription(),c.getCid()};
int result = qr.update(sql, params);
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* Delete a customer
* @param cid
*/
public void delete(String cid) {
try {
String sql = "delete from t_customer where cid=?";
qr.update(sql,cid);
// if (deleteResult != 0) {
//
// }
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* Multiple Conditional Combination Query
* @param condition
* @return
*/
public List<Customer> query(Customer condition) {
try {
// 1.1 Gives the first half of sql
StringBuilder sql = new StringBuilder("select * from t_customer where 1=1 ");
List<Object> params = new ArrayList<Object>();
// 1.2 Judgment condition, add where clause to sql
String cname = condition.getCname();
if (cname != null && !cname.trim().isEmpty()) {
sql.append(" and cname like ?");
params.add("%" + cname + "%");
}
String gender = condition.getGender();
if (gender != null && !gender.trim().isEmpty()) {
sql.append(" and gender=?");
params.add(gender);
}
String cellphone = condition.getCellphone();
if (cellphone != null && !cellphone.trim().isEmpty()) {
sql.append(" and cellphone like ?");
params.add("%" + cellphone + "%");
}
String email = condition.getEmail();
if (email != null && !email.trim().isEmpty()) {
sql.append(" and email like ?");
params.add("%" + email + "%");
}
// 2. Give parameters
// 3. Call query method, result set processor: BeanListHandler
return qr.query(sql.toString(), new BeanListHandler<Customer>(Customer.class),params.toArray());
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
3. CustomerService.java
package waf.yty.cstm.service;
Servlet calls service, service calls dao again!
package waf.yty.cstm.service;
import java.util.List;
import waf.yty.cstm.dao.CustomerDao;
import waf.yty.cstm.domain.Customer;
/**
* Business layer
* Rely on dao
* @author yangtengyu
*
*/
public class CustomerService {
private CustomerDao customerDao = new CustomerDao();
/**
* Add customer
* @param c
*/
public void add(Customer c) {
customerDao.add(c);
}
/**
* Query all
* @return
*/
public List<Customer> findAll() {
return customerDao.findAll();
}
/**
* Loading a single customer
* @param cid
* @return
*/
public Customer load(String cid) {
// TODO Auto-generated method stub
return customerDao.load(cid);
}
/**
* Modifying individual customers
* @param c
*/
public void edit(Customer c) {
// TODO Auto-generated method stub
customerDao.edit(c);
}
public void delete(String cid) {
customerDao.delete(cid);
}
/**
* Multiple Conditional Combination Query
* @param condition
* @return
*/
public List<Customer> query(Customer condition) {
return customerDao.query(condition);
}
}IV. CustomerServlet.java
package waf.yty.cstm.web.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.itcast.commons.CommonUtils;
import cn.itcast.servlet.BaseServlet;
import waf.yty.cstm.domain.Customer;
import waf.yty.cstm.service.CustomerService;
/**
* Web layer
* @author yangtengyu
*
*/
public class CustomerServlet extends BaseServlet {
private CustomerService customerService = new CustomerService();
public String add(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 1. Encapsulating form data into Customer objects
Customer c = CommonUtils.toBean(request.getParameterMap(), Customer.class);
// 2.cid,uuid
c.setCid(CommonUtils.uuid());
// 3. Adding Work
customerService.add(c);
// 4.request domain saves success information
request.setAttribute("msg", "Congratulations on the success of adding customers!");
// 5. Forwarding to msg.jsp
return "f:/msg.jsp";
}
/**
* Query all
* @param request
* @param response
* @return
* @throws ServletException
* @throws IOException
*/
public String findAll(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 1. Call service to get all customers
// 2. Save to request domain
request.setAttribute("cstmList", customerService.findAll());
// 3. Forward to list.jsp
return "f:/list.jsp";
}
/**
* Pre-editing Loading Work
* @param request
* @param response
* @return
* @throws ServletException
* @throws IOException
*/
public String preEdit(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 1. get cid
String cid = request.getParameter("cid");
// 2. Call service method with cid to get customer object
Customer cstm = customerService.load(cid);
// 3. Save customer to request domain
request.setAttribute("cstm", cstm);
// 4. Forward edit.jsp to display the form
return "f:/edit.jsp";
}
public String edit(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 1. Encapsulate form data into Customer objects
Customer c = CommonUtils.toBean(request.getParameterMap(), Customer.class);
// 2. Call the service method to complete the modification
customerService.edit(c);
// 3. Save Success Information request Domain
request.setAttribute("msg", "Successful Customer Modification!");
// 4. Forwarding to msg.jsp
return "f:/msg.jsp";
}
public String delete(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 1. Get cid to delete
String cid = request.getParameter("cid");
// 2. Call delete method of service
customerService.delete(cid);
// 3. Save deletion success times
request.setAttribute("msg", "Delete customer successfully!");
// 4. Request forwarding
return "f:/msg.jsp";
}
public String query(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Customer condition = CommonUtils.toBean(request.getParameterMap(), Customer.class);
List<Customer> cstmList = customerService.query(condition);
request.setAttribute("cstmList", cstmList);
return "/list.jsp";
}
}
JSP Pages
5.1 top.jsp
Comprehensive page
<body style="text-align: center;">
<h1>Customer relationship management system</h1>
<a href="<c:url value='/add.jsp'/>">Add customer</a> |
<a href="<c:url value='/CustomerServlet?method=findAll'/>">Enquiring customers</a> |
<a href="<c:url value='/query.jsp'/>">Advanced search</a>
</body>5.2 add.jsp
Add page
<form action="<c:url value='/CustomerServlet'/>" method="post"> <! -- Pass a parameter named method to the servlet, whose value indicates which method of the servlet to call - > Method <input type="hidden" name="method" value="add"> <input type="text" name="cname"/> and so on
5.3 list.jsp
Display all customer information
Traversing a collection of request domain objects named cstmList with foreach of the core tag library
<body>
<h3 align="center">Customer list</h3>
<table border="1" width="70%" align="center">
<tr>
<th>Customer name</th>
<th>Gender</th>
<th>Birthday</th>
<th>Mobile phone</th>
<th>mailbox</th>
<th>describe</th>
<th>operation</th>
</tr>
<c:forEach items="${cstmList}" var="cstm">
<tr>
<%-- <td>${cstm.cid} </td> --%>
<td>${cstm.cname} </td>
<td>${cstm.gender}</td>
<td>${cstm.birthday}</td>
<td>${cstm.cellphone}</td>
<td>${cstm.email}</td>
<td>${cstm.description}</td>
<!-- <td>Safron spire</td> -->
<td>
<a href="<c:url value='/CustomerServlet?method=preEdit&cid=${cstm.cid }'/>">edit</a>
<a href="<c:url value='/CustomerServlet?method=delete&cid=${cstm.cid }'/>">delete</a>
</td>
</tr>
</c:forEach>
</table>
</body>5.4 edit.jsp
<body>
<h3 align="center">Edit client</h3>
<form action="<c:url value='/CustomerServlet'/>" method="post">
<input type="hidden" name="method" value="edit"/>
<input type="hidden" name="cid" value="${cstm.cid }"/>
<table border="0" align="center" width="40%" style="margin-left: 100px;">
<tr>
<td width="100px">Customer name</td>
<td width="40%">
<input type="text" name="cname" value="${cstm.cname}"/>
</td>
<td align="left">
<label id="cnameError" class="error"> </label>
</td>
</tr>
<tr>
<td>Customer gender</td>
<td>
<input type="radio" name="gender" value="male" id="male" <c:if test="${cstm.gender == 'male' }">checked='checked'</c:if> />
<label for="male">male</label>
<input type="radio" name="gender" value="female" id="female" <c:if test="${cstm.gender == 'female' }">checked='checked'</c:if>/>
<label for="female">female</label>
</td>
<td>
<label id="genderError" class="error"> </label>
</td>
</tr>
5.5 query.jsp