工具类Util
工具类Dbs是从Dao层抽取出来的。Dao层用来处理客户端与服务器之间的响应,接收服务端的数据,然后对数据进行操作。而工具类则是拿到这些数据,直接操作数据库。具体的有:连接数据库、这些SQL语句等。
数据库配置文件
private static String url;
private static String username;
private static String password;
static{
//类加载器在class路径在寻找文件
//因为在class路径下寻找文件,所以是写相对路径
try(InputStream is=Dbs.class.getClassLoader().getResourceAsStream("db.properties")) {
Properties properties=new Properties();
properties.load(is);
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
配置文件中需要注意的点:
类加载器肯定知道这个文件夹在哪里,所以将properties文件放在这个文件下面的可方便间接寻找properties文件。
数据库连接池
druid.properties配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/Dao_crm
username=root
password=root
initialSize=5
maxActive=10
maxWait=5000
private static DataSource ds;
static{
//类加载器在class路径在寻找文件
//因为在class路径下寻找文件,所以是写相对路径
try(InputStream is= Dbs2.class.getClassLoader().getResourceAsStream("druid.properties")) {
Properties properties=new Properties();
properties.load(is);
DataSource ds= DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
在连接池中直接获取连接
Connection conn =ds.getConnection();
Update()
public static int update(String sql,Object ...args){
try {
Class.forName("com.mysql.jdbc.Driver");
//自动释放资源
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
for(int i=0;i<args.length;i++){
pstmt.setObject(i+1,args[i]);
}
//执行
return pstmt.executeUpdate();
}
}catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
return 0;
}
}
Query()方法
public static <T> List<T> Query(String sql, RowMapper<T> mapper, Object ...args){
if(mapper==null) return null;
try{
Class.forName("com.mysql.jdbc.Driver");
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
for(int i=0;i<args.length;i++){
pstmt.setObject(i+1,args[i]);
}
//执行
List<T> arr=new ArrayList<T>();
ResultSet rs=pstmt.executeQuery();
int row=0;
while(rs.next()){
//rs->bean
//这个行号可能没有实际的意义
arr.add(mapper.map(rs,row++));
}
return arr;
}
}catch(SQLException | ClassNotFoundException e){
e.printStackTrace();
return null;
}
}
/**
* @Description: 用来执行每一行数据的映射(rs->bean)
* @Param:
* @return:
*/
public interface RowMapper<T>{
T map(ResultSet rs,int row) throws SQLException;
}
RowMapper接口对应的CustomerDao中list()方法的写法:
public List<Customer> list(){
String sql = "SELECT * FROM customer WHERE company_id=?";
return Dbs.Query(sql,(rs,row)->{
Customer customer=new Customer();
customer.setID(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setPhone(rs.getString("phone"));
customer.setCompany_id(rs.getInt("company_id"));
return customer;
});
}
servlet的处理
因为后期的数据表很多,现在的servlet的建造方式是非常不科学的,现在的需求是办到:
调用customerServlet对customer表来进行存储:
http://localhost:8080/crm/customer/save
调用customerServlet对customer表来进行更新:
http://localhost:8080/crm/customer/update
调用companyServlet对company表来进行存储:
http://localhost:8080/crm/company/update
调用companyServlet对company表来进行更新:
http://localhost:8080/crm/company/update
现在来创建一个CustomerServlet对针对Customer表的请求进行处理。
DoGet()方法:
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String uri=request.getRequestURI();
String[] cmps=uri.split("/");
String methodName=cmps[cmps.length-1];
//反射(通过字符串名获取到方法、成员变量等)
try {
Method method=getClass().getMethod(methodName,HttpServletRequest.class, HttpServletResponse.class);
method.invoke(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}
其中,有用到反射技术,就是利用方法名来寻找对应的方法进行执行。
save()
和前面的save()一样。
public void save(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
//设置编码
request.setCharacterEncoding("UTF-8");
//获取客户端发送的参数
Integer id=Integer.parseInt(request.getParameter("id"));
String name=request.getParameter("name");
String phone=request.getParameter("phone");
Integer company_id=Integer.parseInt(request.getParameter("company_id"));
Customer cus=new Customer(id,name,phone,company_id);
if(dao.save(cus)){
response.sendRedirect("/Dao_crm/list");
}else{
request.setAttribute("error","保存客户信息失败");
//为了能让error.html界面拿到错误信息,需要的是转发而不是重定向
request.getRequestDispatcher("/page/error.jsp").forward(request,response);
}
}
list()
public void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
request.setAttribute("customers",dao.list());
request.getRequestDispatcher("/page/list.jsp").forward(request,response);
}