一、后台服务器
①首先服务器端数据库(用户名root密码123456),test数据库表pro
在eclipse中创建Web->Dynamic web Project 工程Web_Picture_Words,新建包com.product
新建包com.product.jdbc.db创建访问数据库的工具类DBManager.java
package com.product.jdbc.db;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.management.Query;
/**
* 负责访问数据库的工具类
* @author liuya
*
*/
public class DBManager {
// 数据库的用户名和密码
private final String USERNAME = "root";
private final String PSWD = "123456";
// 数据库的驱动
private final String DRIVER = "com.mysql.jdbc.Driver";
private final String URL = "jdbc:mysql://localhost:3306/test";
private Connection connection;// 链接数据库
private PreparedStatement pstmt;// 采用预编译的sql语句执行添加、删除、修改和查询的功能,效率高
private ResultSet rs;// 查询返回的结果集合
private static DBManager instance;
private DBManager() {
// TODO Auto-generated constructor stub
try {
Class.forName(DRIVER);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
public void getConnection() {
try {
connection = DriverManager.getConnection(URL, USERNAME, PSWD);
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 使用单例模式获得数据库的访问对象:保证数据的安全性
*
* @return
*/
public static DBManager getInstance() {
if (instance == null) {
instance = new DBManager();
}
return instance;
}
/**
* 释放连接:
*/
public void releaseConn() {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
// TODO: handle exception
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
}
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
/**
* 功能:对数据库的表的添加删除和修改功能
*
* @param sql
* 传递的Sql语句
* @param params
* sql的占位符
* @return
*/
public boolean updateBySql(String sql, List<Object> params)
throws SQLException {
boolean flag = false;
pstmt = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
// 影响数据库的行数,如果大于0 表示操作成功
int count = pstmt.executeUpdate();
flag = count > 0 ? true : false;
return flag;
}
/**
* 使用java的 反射机制封装查询,查询返回单条记录
*
* @param <T>
* @param sql
* @param params
* @param cls
* @return
*/
public <T> T querySimleResultRef(String sql, List<Object> params,
Class<T> cls) throws Exception {
T t = null;
pstmt = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
// 查询结果
rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int cols_len = metaData.getColumnCount();
if (rs.next()) {
t = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = rs.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true);
field.set(t, cols_value);
}
}
return t;
}
/**
* 使用java的 反射机制封装查询,查询返回多条记录
*
* @param <T>
* @param sql
* @param params
* @param cls
* @return
* @throws Exception
*/
public <T> List<T> queryMultResultRef(String sql, List<Object> params,
Class<T> cls) throws Exception {
List<T> list = new ArrayList<T>();
pstmt = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int cols_len = metaData.getColumnCount();
while (rs.next()) {
T t = cls.newInstance();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = rs.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true);
field.set(t, cols_value);
}
list.add(t);
}
return list;
}
/**
* 使用封装查询,查询返回一个Map表示一行记录
*
* @param sql
* @param params
* @return
*/
public Map<String, Object> querySimpleMap(String sql, List<Object> params)
throws SQLException {
Map<String, Object> map = new HashMap<String, Object>();
pstmt = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int cols_len = metaData.getColumnCount();
while (rs.next()) {
for (int i = 0; i < cols_len; i++) {
String key = metaData.getColumnName(i + 1);
Object value = rs.getObject(key);
if (value == null) {
value = "";
}
map.put(key, value);
}
}
return map;
}
/**
* 使用封装查询,查询返回一个List<Map<String, Object>>表示多行记录
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public List<Map<String, Object>> queryMultMap(String sql,
List<Object> params) throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
pstmt = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int cols_len = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String key = metaData.getColumnName(i + 1);
Object value = rs.getObject(key);
if (value == null) {
value = "";
}
map.put(key, value);
}
list.add(map);
}
return list;
}
}
②开发服务器端的功能
新建接口(interface)ProductService.java
package com.product;
import java.util.List;
import java.util.Map;
public interface ProductService {
public boolean addProduct(List<Object> params);
public void releaseConn();
public List<Map<String, Object>> listProduct();
public boolean delProduct(List<Object> params);
public Map<String,Object> viewProduct(List<Object> params);
}
③
声明ProductDao.java实现
ProductService.java
package com.product;
import java.util.List;
import java.util.Map;
import com.product.jdbc.db.DBManager;
public class ProductDao implements ProductService {
private DBManager dbManager;
public ProductDao() {
// TODO Auto-generated constructor stub
dbManager = DBManager.getInstance();
}
@Override
public boolean addProduct(List<Object> params) {
// TODO Auto-generated method stub
String sql = "insert into pro(name,address,price,img) values(?,?,?,?)";
boolean flag = false;
dbManager.getConnection();
try {
flag = dbManager.updateBySql(sql, params);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return flag;
}
@Override
public void releaseConn() {
// TODO Auto-generated method stub
}
@Override
public List<Map<String, Object>> listProduct() {
// TODO Auto-generated method stub
List<Map<String, Object>> list = null;
String sql = "select * from pro";
try {
dbManager.getConnection();
list = dbManager.queryMultMap(sql, null);
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
}
return list;
}
@Override
public boolean delProduct(List<Object> params) {
// TODO Auto-generated method stub
String sql = "delete from pro where id = ? ";
boolean flag = false;
dbManager.getConnection();
try {
flag = dbManager.updateBySql(sql, params);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return flag;
}
@Override
public Map<String, Object> viewProduct(List<Object> params) {
// TODO Auto-generated method stub
String sql = "select * from pro where id = ? ";
Map<String, Object> map = null;
try {
dbManager.getConnection();
map = dbManager.querySimpleMap(sql, params);
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
}
return map;
}
}
④
提供一个网络访问接口(Servlet)ProductAction.java
package com.product;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
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 javax.swing.text.View;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
/**
* Servlet implementation class ProductAction
*/
@WebServlet("/ProductAction")
public class ProductAction extends HttpServlet {
private static final long serialVersionUID = 1L;
private ProductService service;
@Override
public void init() throws ServletException {
// TODO Auto-generated method stub
super.init();
service = new ProductDao();
}
/**
* @see HttpServlet#HttpServlet()
*/
public ProductAction() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html;charset=utf-8");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String action_flag = request.getParameter("action_flag");
PrintWriter writer = response.getWriter();
if (action_flag.equals("add")) {
add(request, response);
} else if (action_flag.equals("list")) {
list(request, response);
} else if (action_flag.equals("del")) {
del(request, response);
} else if (action_flag.equals("view")) {
view(request, response);
}
writer.flush();
writer.close();
}
/**
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
private void view(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
String path = request.getContextPath();
String id = request.getParameter("id");
List<Object> params = new ArrayList<Object>();
params.add(id);
Map<String, Object> map = service.viewProduct(params);
request.setAttribute("map", map);
request.getRequestDispatcher("/view.jsp").forward(request, response);
}
/**
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
private void del(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
String path = request.getContextPath();
String id = request.getParameter("id");
System.out.println("--id->>" + id);
List<Object> params = new ArrayList<Object>();
params.add(id);
boolean flag = service.delProduct(params);
if (flag) {
response.sendRedirect(path + "/ProductAction?action_flag=list");
}
}
/**
* 列表界面
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
private void list(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
List<Map<String, Object>> list = service.listProduct();
request.setAttribute("list", list);
String path = request.getContextPath();// 获得工程的路径
System.out.println("-path-->>" + path);
request.getRequestDispatcher("/list.jsp").forward(request, response);
}
/**
* 实现添加操作
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
protected void add(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String path = request.getContextPath();
// 需要完成两个功能:
// 声明服务端保存文件的情况
DiskFileItemFactory factory = new DiskFileItemFactory();
// 使用文件上传功能
ServletFileUpload fileUpload = new ServletFileUpload(factory);
// 规定客户端上传文件的大小
fileUpload.setSizeMax(4 * 1024 * 1024);// 总大小
fileUpload.setFileSizeMax(1 * 1024 * 1024);// 每一个文件的大小
List<FileItem> list = null;// 表单以input 开始的输入框
List<Object> params = new ArrayList<Object>();// 插入数据库
try {
list = fileUpload.parseRequest(request);
for (FileItem item : list) {
// 表单的文本数据
if (item.isFormField()) {
if (item.getFieldName().equals("name")) {
String name = item.getString("utf-8");
params.add(name);
}
if (item.getFieldName().equals("address")) {
String address = item.getString("utf-8");
params.add(address);
}
if (item.getFieldName().equals("price")) {
String price = item.getString("utf-8");
params.add(price);
}
// 文件
} else {
String img = item.getName();
params.add(img);
String file_upload_path = request.getRealPath("/upload")
+ "/" + img;
System.out.println("-file_upload_path-->>"
+ file_upload_path);
File file = new File(file_upload_path);
// System.out.println("--->>"+file.getName());
item.write(file);
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
boolean flag = service.addProduct(params);
if (flag) {
// 直接跳转到列表显示界面
// System.out.println("success!!!");
response.sendRedirect(path + "/ProductAction?action_flag=list");
}
}
}
⑤新建包com.product.json中创建(Servlet)JsonAction.java类用于提供网络访问接口显示json数据
package com.product.json;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import com.product.ProductDao;
import com.product.ProductService;
import net.sf.json.JSONSerializer;
/**
* Servlet implementation class JsonAction
*/
@WebServlet("/JsonAction")
public class JsonAction extends HttpServlet {
private static final long serialVersionUID = 1L;
private ProductService service;
@Override
public void init() throws ServletException {
// TODO Auto-generated method stub
super.init();
service = new ProductDao();
}
/**
* @see HttpServlet#HttpServlet()
*/
public JsonAction() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html;charset=utf-8");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String action_flag = request.getParameter("action_flag");
PrintWriter writer = response.getWriter();
if (action_flag.equals("list")) {
List<Map<String, Object>> list = service.listProduct();
Map<String, Object> map = new HashMap<String, Object>();
map.put("products", list);
String jsonString = JSONSerializer.toJSON(map).toString();
writer.print(jsonString);
} else if (action_flag.equals("add")) {
add(request, response);
}
writer.flush();
writer.close();
}
/**
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
protected void add(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter writer = response.getWriter();
String path = request.getContextPath();
// 需要完成两个功能:
// 声明服务端保存文件的情况
DiskFileItemFactory factory = new DiskFileItemFactory();
// 使用文件上传功能
ServletFileUpload fileUpload = new ServletFileUpload(factory);
// 规定客户端上传文件的大小
fileUpload.setSizeMax(4 * 1024 * 1024);// 总大小
fileUpload.setFileSizeMax(1 * 1024 * 1024);// 每一个文件的大小
List<FileItem> list = null;// 表单以input 开始的输入框
List<Object> params = new ArrayList<Object>();// 插入数据库
String name = "";
String address = "";
String price = "";
String img = "";
try {
list = fileUpload.parseRequest(request);
for (FileItem item : list) {
// 表单的文本数据
if (item.isFormField()) {
if (item.getFieldName().equals("name")) {
name = item.getString("utf-8");
} else if (item.getFieldName().equals("address")) {
address = item.getString("utf-8");
} else if (item.getFieldName().equals("price")) {
price = item.getString("utf-8");
}
// 文件
} else {
img = item.getName();
String file_upload_path = request.getRealPath("/upload")
+ "/" + img;
System.out.println("-file_upload_path-->>"
+ file_upload_path);
File file = new File(file_upload_path);
// System.out.println("--->>"+file.getName());
item.write(file);
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
params.add(name);
params.add(address);
params.add(price);
params.add(img);
boolean flag = service.addProduct(params);
if (flag) {
// 直接跳转到列表显示界面
// System.out.println("success!!!");
// response.sendRedirect(path + "/ProductAction?action_flag=list");
writer.println("success!!添加成功");
}
writer.flush();
writer.close();
}
}
便于管理数据库,生成web页面,jsp文件如下
index.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%
String path = getServletContext().getContextPath();
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<center>
<a href="<%=path %>/ProductAction?action_flag=list">进入产品信息列表</a>
</center>
</body>
</html>
view.jsp
<%@page import="java.util.*"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%
String path = getServletContext().getContextPath();
Map<String, Object> map = (Map<String, Object>) request
.getAttribute("map");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>显示详细记录</title>
</head>
<body>
产品名称:
<%=map.get("name")%>
<br> 产品产地:
<%=map.get("address")%>
<br> 产品价格:
<%=map.get("price")%>
<br> 产品图片:
<img src="<%=path+"//upload//"+map.get("img")%>">
<br>
<input type="button" name="submit" value="返回列表界面" οnclick="javascript:window.history.back();">
</body>
</html>
list.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ page import="java.util.*"%>
<%
String path = getServletContext().getContextPath();
//获得数据表的记录
List<Map<String, Object>> list = (List<Map<String, Object>>)request.getAttribute("list");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
<script type="text/javascript">
function addproduct(){
window.location.href="<%=path%>/add.jsp";
}
function delProduct(pid){
window.location.href="<%=path%>/ProductAction?action_flag=del&id="+pid;
}
function viewProduct(pid){
window.location.href="<%=path%>/ProductAction?action_flag=view&id="+pid;
}
</script>
</head>
<body>
<center>
<table border="1" bordercolor="#CCCCCC" cellpadding="1"
cellspacing="1" width="300">
<tr>
<td>名称</td>
<td>产地</td>
<td>价格</td>
<td>操作</td>
</tr>
<%
if(list!=null&&!list.isEmpty()){
for(int i=0;i<list.size();i++){
%>
<tr>
<td><%=list.get(i).get("name") %></td>
<td><%=list.get(i).get("address") %></td>
<td><%=list.get(i).get("price") %></td>
<td><a href="javascript:delProduct('<%=list.get(i).get("id") %>');">删除</a> <a href="javascript:viewProduct('<%=list.get(i).get("id") %>')">查看</a></td>
</tr>
<%
}
}
%>
<tr>
<td colspan="4" align="center">
<input type="button" value="添加产品" οnclick="addproduct();">
</td>
</tr>
</table>
</center>
</body>
</html>
add.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%
//获得工程的路径
String path = getServletContext().getContextPath();
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>添加产品信息</title>
</head>
<body>
<form action="<%=path %>/ProductAction?action_flag=add" method="POST" name="form1" enctype="multipart/form-data">
产品名称:<input type="text" name="name" value=""><br> 产品产地:<input
type="text" name="address" value=""><br> 产品价格:<input
type="text" name="price" value=""><br> 产品图片:<input
type="file" name="img" value="" size="40"><br> <input
type="submit" name="submit" value="添加产品信息">
</form>
</body>
</html>
在地址栏输入http://192.168.1.101:8080/Web_Picture_Words/index.jsp显示如下
点击进入list界面显示如下
点击添加产品,进入add界面显示如下,
在地址栏输入地址http://192.168.1.101:8080/Web_Picture_Words/JsonAction?action_flag=list效果如下