First write an EmpDao interface, which has a method to query all data
package com.sun.test;
import java.util.List;
public interface EmpDao {
public List<Emp> findAll(String name,String sex);
}
Write an implementation class to implement this interface and inherit our BaseDao, which is full of encapsulated add, delete, modify and query statements in the database.
package com.sun.test;
import java.util.ArrayList;
import java.util.List;
public class EmpDaoImpl extends BaseDao<Emp> implements EmpDao{
@Override
public List<Emp> findAll(String name,String sex) {
StringBuilder sql=new StringBuilder("select * from emp where 1=1");
List objects = new ArrayList<>();
if(name!=null & amp; & amp;!name.equals("")){
sql.append(" and name like ?");
objects.add('%' + name + '%');
}
if(sex!=null & amp; & amp;!sex.equals("")!sex.equals("--Please select--")){
sql.append("and gender=?");
objects.add(sex);
}
List beanList = super.getBeanList(sql.toString(),objects.toArray());
return beanList;
}
}
Database encapsulated add, delete, modify, check code
package com.sun.test;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* Define a Dao that is inherited to perform basic operations on the database
* @param <T> Generic -> bean type
*/
public abstract class BaseDao<T>{
private QueryRunner queryRunner;
//Define a variable to receive the generic type
private Class<T> type;
// Get the Class object of T and get the type of the generic. The generic is determined when it is inherited by a subclass.
public BaseDao() {
queryRunner = new QueryRunner(JDBCUtils.getDataSource());
// Get the type of subclass
Class clazz = this.getClass();
// Get the type of parent class
// getGenericSuperclass() is used to get the type of the parent class of the current class
// ParameterizedType represents a type with generics
ParameterizedType parameterizedType =
(ParameterizedType) clazz.getGenericSuperclass();
// Get the specific generic type getActualTypeArguments Get the specific generic type
// This method will return an array of Type
Type[] types = parameterizedType.getActualTypeArguments();
// Get the specific generic type·
this.type = (Class<T>) types[0];
}
/**
* Get all objects
*
* @param sql
* @param params
* @return
*/
public List<T> getBeanList(Connection conn, String sql, Object... params) {
List<T> list = null;
try {
list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* Get an object
*
* @param sql
* @param params
* @return
*/
public T getBean(Connection conn,String sql, Object... params) {
T t = null;
try { //type == String.class
t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return t;
}
/**
* General addition, deletion and modification operations (transaction related)
*
* @param sql
* @param params
* @return
*/
public int update(Connection conn, String sql, Object... params) {
int count = 0;
try {
count = queryRunner.update(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
/**
* General addition, deletion and modification operations (there is no way to use transactions)
*
* @param sql
* @param params
* @return
*/
public int update(String sql, Object... params) {
int count = 0;
try {
count = queryRunner.update(sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
/**
* Return the primary key id after adding data (transaction related)
* @param sql
* @param params
* @return
*/
public int insert(Connection conn,String sql, Object... params){
try {
return queryRunner.insert(conn,sql, new ScalarHandler<Long>(), params).intValue();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* Return the primary key id after adding data (there is no way to use transactions)
* @param sql
* @param params
* @return
*/
public int insert(String sql, Object... params){
try {
return queryRunner.insert(sql, new ScalarHandler<Long>(), params).intValue();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* Get the value of a single row and column, specifically used to execute SQL statements like select count(*)...
*
* @param sql
* @param params
* @return
*/
public int getValue(Connection conn,String sql, Object... params) {
int count = 0;
try {
// Call the query method of queryRunner to get a single value
count = queryRunner.query(conn, sql, new ScalarHandler<Long>(), params).intValue();
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
/**
* Get all objects
*
* @param sql
* @param params
* @return
*/
public List<T> getBeanList(String sql, Object... params) {
List<T> list = null;
try {
list = queryRunner.query(sql, new BeanListHandler<T>(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* Get an object
*
* @param sql
* @param params
* @return
*/
public T getBean(String sql, Object... params) {
T t = null;
try { //type == String.class
t = queryRunner.query(sql, new BeanHandler<T>(type), params);
} catch (SQLException e) {
e.printStackTrace();
}
return t;
}
/**
* Get the value of a single row and column, specifically used to execute SQL statements like select count(*)...
*
* @param sql
* @param params
* @return
*/
public int getValue(String sql, Object... params) {
int count = 0;
try {
// Call the query method of queryRunner to get a single value
count = queryRunner.query(sql, new ScalarHandler<Long>(), params).intValue();
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
}
Write servlet classes for requests and responses
package com.sun.test;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
@WebServlet("/EmpServlet")
public class EmpServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
EmpDao empDao=new EmpDaoImpl();
String username = req.getParameter("username");
String sex = req.getParameter("sex");
List<Emp> list = empDao.findAll(username,sex);
PrintWriter writer = resp.getWriter();
writer.println("<table border='1'>");
for (Emp emp : list) {
writer.println("<tr>");
writer.println("<td>" + emp.getId() + "</td>");
writer.println("<td>" + emp.getName() + "</td>");
writer.println("<td>" + emp.getGender() + "</td>");
writer.println("<td>" + emp.getJoin_date() + "</td>");
writer.println("<td>" + emp.getDept_id() + "</td>");
writer.println("<td>" + emp.getSalary() + "</td>");
writer.println("</tr>");
}
writer.println("</table>");
}
}
Write a front-end html web page
<form action="EmpServlet" method="post">
<label>Employee name:</label><input type="text" name="username"><br>
<label>Employee gender:</label><select name="sex">
<option>--Please select--</option>
<option>Male</option>
<option>Female</option>
</select>
<input type="submit" value="query">
</form>
Summary: The advantage of using the above method is that the code is easy to expand and can be queried with multiple conditions. The advantage of using StringBuilder is high splicing efficiency and the use of array syntax sugar. Through this small training, we have a deeper understanding of servlets.
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. MySQL entry-level skills treeDatabase compositionTable 77574 people are learning the system