JavaEE实践：基于jsp+javaservlet访问MySQL数据库并绘制折线图

记录一下JavaEE的实践作业，题目要求是给定excel表格，将excel表格中的数据读入MySQL数据库，然后在jsp中显示数据库数据，并且根据索引获取数据库数据绘制折线图。

完整代码链接：基于jsp+javaservlet访问MySQL数据库并绘制折线图​​​​​​​

开发工具：myeclipse

服务器：myeclipse自带的tomcat

数据库：MySQL5.7

直接上部分代码，完整程序包连接放在后面

数据库结构（仅供参考）：

标曲信息表hhw106

字段名	类型	是否为空	键
Ngname	Varchar	否
Cname	Varchar	否
Ac	Int	否
Unit	Varchar	否
Area	Varchar	否
Acc	Float	否
Id	Int	否	是（自增长）

定量结果表106res

字段名	类型	是否为空	键
Sname	Varchar	否
Cname	Varchar	否
Cc	Float	否
Unit	Varchar	否
Id	Int	否	是（自增长）

化合物分类表106sort

字段名	类型	是否为空	键
Ename	Varchar	否
Cn	Varchar	否
Sort	Varchar	否
Id	Int	否	是（自增长）

 

 

将excel数据推入MySQL数据库：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class PushExcelToMysql {
	Workbook wb;
	public static void main(String[] args) throws Exception {

		Sheet sheet;
		Workbook workbook = null;
		HSSFWorkbook wbook;
		int column,rows = 0;
		File file = new File("E:\\j2ee\\106hhw1.xls");
		Cell[][] cells = new Cell[800][7];
		try {
			//workbook = readExcel(); 
	        workbook = Workbook.getWorkbook(file);
			//workbook = Workbook.getWorkbook(new File("E:\\j2ee\\106hhw.xls"));
			sheet = workbook.getSheet(0);
			column = sheet.getColumns();//列
			rows = sheet.getRows();//行
			//column = workbook.
			for (int i = 0; i < rows; i++) {
				for (int j = 0; j < column; j++) {
					cells[i][j] = sheet.getCell(j, i);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/j2ee525?characterEncoding=UTF-8","root", "");
			String sql = "insert into hhw106(ngname,cname,ac,unit,area,acc) values(?,?,?,?,?,?)";
			PreparedStatement ps = c.prepareStatement(sql);
			for (int i = 1; i < rows; i++) {
				ps.setString(1, cells[i][0].getContents());
				ps.setString(2, cells[i][1].getContents());
				ps.setInt(3, Integer.parseInt(cells[i][2].getContents()));
				ps.setString(4, cells[i][3].getContents());
				ps.setString(5, cells[i][4].getContents());
				ps.setDouble(6, Double.parseDouble(cells[i][5].getContents()));
				ps.execute();
			}
			System.out.println("ok");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                //return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                //return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }
            
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }
}

数据库工具类（很久之前在网上找的 一直用到现在）：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBManager {
	 
    // 数据库连接常量
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String USER = "root";
    public static final String PASS = "";
    
    public static final String URL = "jdbc:mysql://localhost:3306/j2ee525?characterEncoding=utf-8&serverTimezone=UTC";
    
 
    // 静态成员，支持单态模式
    private static DBManager per = null;
    private Connection conn = null;
    private Statement stmt = null;
 
    // 单态模式-懒汉模式
    private DBManager() {
    }
 
    public static DBManager createInstance() {
        if (per == null) {
            per = new DBManager();
            per.initDB();
        }
        return per;
    }
 
    // 加载驱动
    public void initDB() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    // 连接数据库，获取句柄+对象
    public void connectDB() {
        System.out.println("Connecting to database...");
        try {
            conn = DriverManager.getConnection(URL, USER, PASS);
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("SqlManager:Connect to database successful.");
    }
 
    // 关闭数据库 关闭对象，释放句柄
    public void closeDB() {
        System.out.println("Close connection to database..");
        try {
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("Close connection successful");
    }
 
    // 查询
    public ResultSet executeQuery(String sql) {
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }
 
    // 增添/删除/修改
    public int executeUpdate(String sql) {
        int ret = 0;
        try {
            ret = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ret;
    }
}

在jsp中显示数据库数据：

<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
	+ request.getServerName() + ":" + request.getServerPort()
	+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<style type="text/css">
table.imagetable {
    font-family: verdana,arial,sans-serif;
    font-size:11px;
    color:#333333;
    border-width: 1px;
    border-color: #999999;
    border-collapse: collapse;
}
table.imagetable th {
    background:#b5cfd2 url('cell-blue.jpg');
    border-width: 1px;
    padding: 8px;
    border-style: solid;
    border-color: #999999;
}
table.imagetable td {
    background:#dcddc0 url('cell-grey.jpg');
    border-width: 1px;
    padding: 8px;
    border-style: solid;
    border-color: #999999;
}
</style>
<base href="<%=basePath%>">

<title>My JSP 'show.jsp' starting page</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
	<!-- Bootstrap core CSS -->
    <link href="vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">


    <!-- Additional CSS Files -->
    <link rel="stylesheet" href="assets/css/fontawesome.css">
    <link rel="stylesheet" href="assets/css/templatemo-style.css">
    <link rel="stylesheet" href="assets/css/owl.css">

</head>

<body>
	This is my 标曲信息  JSP page.
	<br>
	<%
	    out.println(request.getAttribute("h106"));
		try {
		Class.forName("com.mysql.jdbc.Driver"); // 加载驱动
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/j2ee525", "root", ""); 获取Connection对象

		if (con != null) {
			Statement stmt = null;
			ResultSet rs = null;
			String sql = "SELECT * FROM hhw106;"; //查询语句
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
	%>
	
	<br>
	<table class= "default-table" border="1" cellspacing="0" cellpadding="0" align="center"
		width="50%">
		<tr align="center">
		  <td>
	          Sample Name
			</td>
			<td>
			  Component Name
			</td>
			<td>
	          Actual Concentration
			</td>
			<td>
			  Conc. Units
			</td>
			<td>
			  Area
			</td>
			<td>
			  Accuracy
			</td>
			<td>
			  Id
			</td>
			<td>
			  操作
			</td>
		</tr>
		<%
			while (rs.next()) {
		%>
		
		<tr align="center">
			
			<td>
				<%
					out.print(rs.getString("ngname"));
				%>
			</td>
			<td>
				<%
					out.print(rs.getString("cname"));
				%>
			</td>
			<td>
				<%
					out.print(rs.getInt("ac"));
				%>
			</td>
			<td>
				<%
					out.print(rs.getString("unit"));
				%>
			</td>
			<td>
				<%
					out.print(rs.getString("area"));
				%>
			</td>
			<td>
				<%
					out.print(rs.getDouble("acc"));
				%>
			</td>
			<td>
				<%
					out.print(rs.getInt("id"));
				%>
			</td>
			<td>
				<a href="addinfo.jsp">增加</a>|<a href="updateInfo.jsp">修改</a>|<a href="deleteInfo.jsp">删除</a></td>
			</td>
		</tr>
		<%
			}
		%>

	</table>

	<%
		}else{  
	                out.print("Connection fail！");  
	            }  
	        }catch (Exception e) {        
	            //e.printStackTrace();  
	            out.print("Connection Exception！");  
	        }
	%>
	<script src="assets/js/browser.min.js"></script>
    <script src="assets/js/breakpoints.min.js"></script>
    <script src="assets/js/transition.js"></script>
    <script src="assets/js/owl-carousel.js"></script>
    <script src="assets/js/custom.js"></script>
</body>
</html>

绘制折线图的servlet：

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.lang.StringUtils;

import com.db.DBManager;

public class ResListServlet extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public ResListServlet() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		HttpSession session;
		request.setCharacterEncoding("utf-8");
		List<String> rowlist = new ArrayList<String>();
		List<Float> colmunlist = new ArrayList<Float>();
		List <String>rl,cl = null; 
		rl = new ArrayList<String>();
		cl = new ArrayList<String>(); 
		try {
			//rowlist.clear();
			//colmunlist.clear();
			String inputString = request.getParameter("strbox");
			System.out.println("inputstring == " + inputString);
			DBManager dManager = DBManager.createInstance();
			dManager.connectDB();
			String querysqlString = "select * from result where title = '" + inputString + "'";
			ResultSet rs = dManager.executeQuery(querysqlString);
			while(rs.next())
			{
				//String rowitemString = rs.getString(1);
				//String colitemString = rs.getString(2);
				//System.out.println(rowitemString + " " + colitemString);
				//rowlist.add(rs.getString(1).toString());
				colmunlist.add(rs.getFloat(2));
				colmunlist.add(rs.getFloat(3));
				colmunlist.add(rs.getFloat(4));
				colmunlist.add(rs.getFloat(5));
				colmunlist.add(rs.getFloat(6));
				colmunlist.add(rs.getFloat(7));
				colmunlist.add(rs.getFloat(8));
				colmunlist.add(rs.getFloat(9));
				colmunlist.add(rs.getFloat(10));
				colmunlist.add(rs.getFloat(11));
			}
			String rolistString = "S1,S2,S3,S4,S5,S6,S7,S8,S9,S10";
			//String rolistString = StringUtils.strip(rowlist.toString(),"[]");
			String colistString = StringUtils.strip(colmunlist.toString(),"[]");
			//System.out.println(rolistString + " " + colistString);
			String [] rsplit = rolistString.split(",");
			String [] csplit = colistString.split(",");
			for(String ss : rsplit)
			{
			  
			  rl.add(ss);
			  //out.println(ss);
			}
			for(String ss : csplit)
			{
			  cl.add(ss);
			  //out.println(ss);
			}
			 /*for(int i=0; i<rl.size(); i++)
			 {
			      System.out.println(rl.get(i) +  " " + cl.get(i));
			 }*/
			request.setAttribute("newrow", rolistString); 
			request.setAttribute("rowlist", rl);
			request.setAttribute("collist", cl);
	        
			request.getRequestDispatcher("/zhexianResList.jsp").forward(request, response);
		} catch (Exception e) {
			e.printStackTrace();
			//System.out.println(e.toString());
			// TODO: handle exception
		}

	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
		out.println("<HTML>");
		out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
		out.println("  <BODY>");
		out.print("    This is ");
		out.print(this.getClass());
		out.println(", using the POST method");
		out.println("  </BODY>");
		out.println("</HTML>");
		out.flush();
		out.close();
	}

	/**
	 * Initialization of the servlet. <br>
	 *
	 * @throws ServletException if an error occurs
	 */
	public void init() throws ServletException {
		// Put your code here
	}

}

显示 折线图的JSP页面：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">

<title>My JSP 'zhexian106-1.jsp' starting page</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/echarts/4.3.0/echarts.min.js"></script>
</head>

<body>


	<form action="" method="get">
		<input type="button" value="测试画图zhexian106-结果列表" onclick="huitu()">
	</form>
	<%
	List <String>rl,cl = null; 
	//out.println(request.getAttribute("rowlist"));
	//out.println(request.getAttribute("collist"));
	//rl = new ArrayList<String>();
	//cl = new ArrayList<String>(); 
	//String rrlist = request.getAttribute("rowlist").toString();
	//String cclist = request.getAttribute("collist").toString();
	//StringUtils.strip(list.toString(),"[]");	
	%>
	<div id="box" style="width: 600px;height: 600px;"></div>
	<script type="text/javascript">
	
		function huitu() {
		    //初始化ehcharts实例
			var myChart = echarts.init(document.getElementById("box"));
			//rl横坐标 cl纵坐标
			var rstr = "<%=request.getAttribute("newrow")%>"
            var rlist = new Array(rstr.length);
            rlist = rstr.split(',');//_TheArray为目标数组，‘!@#!!%@^*$’为自定义分隔符
            
            var clist = <%=request.getAttribute("collist")%> 
                         
		var option = {
				//标题
				title : {
					text : '化合物(结果列表s1-s10)数据分析图'
				},
				//工具箱
				//保存图片
				toolbox : {
					show : true,
					feature : {
						saveAsImage : {
							show : true
						}
					}
				},
				//图例-每一条数据的名字叫销量
				legend : {
					data : [ 'accuracy' ]
				},
				//x轴
				xAxis : {
				    data : rlist
					//data : [ "苹果", "橘子", "橙子", "香蕉", "菠萝", "榴莲" ]
				},
				//y轴没有显式设置，根据值自动生成y轴
				yAxis : {},
				//数据-data是最终要显示的数据
				series : [ {
					name : '销量',
					type : 'line',
					data : clist
					//data : [ 40, 20, 35, 60, 55, 10 ]
				} ]
			};
			//使用刚刚指定的配置项和数据项显示图表
			myChart.setOption(option);
		
		    /*var arr = [1,2,6,3,7]
		    
		   
			//初始化ehcharts实例
			var myChart = echarts.init(document.getElementById("box"));
			//指定图表的配置项和数据
			var option = {
				//标题
				title : {
					text : '106化合物折线图'
				},
				//工具箱
				//保存图片
				toolbox : {
					show : true,
					feature : {
						saveAsImage : {
							show : true
						}
					}
				},
				//图例-每一条数据的名字叫销量
				legend : {
					data : [ '结果' ]
				},
				//x轴
				xAxis : {
					data : rlist
				},
				//y轴没有显式设置，根据值自动生成y轴
				yAxis : {},
				//数据-data是最终要显示的数据
				series : [ {
					name : '销量',
					type : 'line',
					data : clist
				} ]
			};
			//使用刚刚指定的配置项和数据项显示图表
			myChart.setOption(option);*/
		}

		
	</script>
</body>
</html>

实现效果：

Homepage

 

 

显示数据

 

增加/修改

 

折线图绘制界面，输入化合物名称获取数据

 

绘图结果

完整代码链接：基于jsp+javaservlet访问MySQL数据库并绘制折线图