被要求做服务器端给自己增加工作量纵然是不愉快的,毕竟自以为APP已经完成就可以结题,指导老师教育一波实现界面友好、系统完整,感悟颇深,于是充满兴趣与干劲的开始撸服务器端,可是原来这方面的知识储备只有H5这部分的静态页面知识,做个页面so easy ,问题是要进行数据库,客户端,前端、后台之间的交互,这方面一窍不通,忙活好久,写这篇Blog记录下2017.4的服务器端,相信以后必定会超过现在。
这是一个Android APP->server的系统
从安卓客户端获取上传数据到servlet,然后存到数据库Mysql
用户登录
查看数据
对数据进行处理等
要有mysql-connector-java-5.6-bin
在这之前先联系数据库MYSQL
DBConnection
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import com.mysql.jdbc.ResultSet;
import com.pro.Information.Information;
public class DBConnection {
static{
try{
Class.forName("com.mysql.jdbc.Driver");
}catch(ClassNotFoundException e){
System.out.println("加载驱动错误");
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn=null;
try{
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/Creation", "root", "");
System.out.println("取得连接成功");
}catch(Exception e){
System.out.println("取得连接错误");
e.printStackTrace();
}
return conn;
}
public static void close(Statement st,ResultSet rs,Connection conn){
try{
if(st!=null)
{
st.close();
}
if(rs!=null)
{
rs.close();
}
if(conn!=null)
{
conn.close();
}
}catch(Exception e)
{
e.printStackTrace();
}
}
public static void close(PreparedStatement pst,Connection conn){
try{
if(pst!=null){
pst.close();
}
if(conn!=null){
conn.close();
}
}catch(Exception e)
{
e.printStackTrace();
}
}
}
然后测试一下
DBTest
import java.sql.Connection;
import java.sql.ResultSet;
import com.mysql.jdbc.Statement;
import com.pro.DBConnection.DBConnection;
public class DBtest {
public static void main(String[] args){
Connection conn=DBConnection.getConnection();
// if(conn!=null){
// System.out.println("OK ");
// }else{
// System.out.println("fail");
// }
try{
String sql="select*from data";
Statement stmt=(Statement)conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
while(rs.next())
{
int id=rs.getInt("id");
System.out.println(id);
}
}catch(Exception e)
{
e.printStackTrace();
}
}
}
有一个information没怎么用
public class Information {
private int id;
public int getId(){
return id;
}
public void setId(int id){
this.id=id;
}
}
太多了就留了一个发上来Login.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="UTF-8"%>
<!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=ISO-8859-1">
<title>登陆</title>
<style type="text/css">
</style>
</head>
<body style="background:url(loginback.jpg) no-repeat;position:absolut;">
<form method="POST" name="divlogin" action="LoginSelvet">
<div align="center">
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<div style=" background:url(back.jpg) no-repeat;width:600px;height:300px;">
<p> </p>
<font size=6 color="#2a5caa" face="宋体" ><b><i>欢迎使用查询呼叫评价系统</i></b></font>
<p> </p>
<p>账号:
<input type="text" name="username"><br><br>
密码: <input type="password" name="password"><br><br>
<input type="submit" name="login" value="登陆" οnclick="return emptyLogin()">
<input type="reset" name="reset" value="重置">
</p>
<p> </p>
</div>
</div>
<script type="text/javascript">
function emptyLogin(){
var eusername=document.divlogin.username.value;
var epassword=document.divlogin.password.value;
if(eusername==""){
alert("请输入账号");
return false;
}
if(epassword=="")
{
alert("请输入密码");
return false;
}
}
</script>
</form>
</body>
</html>
Login.servlet
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 防乱码
response.setContentType("text/html;charset=utf-8");//特别注意,text,写成test,会无脑下载Loginservlet
response.setCharacterEncoding("utf-8");
// 获取jsp中填入数据
String username = request.getParameter("username");
String password = request.getParameter("password");
System.out.println(username);
System.out.println(password);
// 数据库相关
String Dusername = null;
String Dpassword = null;
String sql = null;
// 与数据库建立连接
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Loading Database success");
} catch (Exception e) {
System.out.println("Class not found exception");
}
String url = "jdbc:mysql://localhost:3306/Creation";
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
if (username.equals("admin")&& password.equals("admin")) {//超管,如果超管登录,就跳转到添加管理员界面
System.out.println("super manager登陆成功");
// 要成功,跳转success.jsp
response.sendRedirect("addmanager.jsp");
return;
}
else{
try {
con = (Connection) DriverManager.getConnection(url, "root", "");
stmt = (Statement) con.createStatement();
sql = "select*from manager where username='"+username+"'";
rs = stmt.executeQuery(sql);
while (rs.next()) {
Dusername = rs.getString("username");
Dpassword = rs.getString("password");
System.out.println(username);
System.out.println(password);
if (Dusername.equals(username) && Dpassword.equals(password)) {
System.out.println("管理员登陆成功");
// 要成功,跳转success.jsp
response.sendRedirect("success.jsp");
return;
}
else {
System.out.println("登陆失败");
response.sendRedirect("fail.jsp");
return;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
登录使用manager表先,如果是超级管理员,实现为系统添加管理员功能
addmanager.jsp
很简单的只有账号密码确定按钮
<form method="POST" action="addServlet" name="add">
<br>
<br>
<br>
<br>
<div align="center">
<p>添加管理员</p>
Musername:<input type="text" name="addun">
<br>
<br>
Mpassword:<input type="text" name="addpsw">
<br>
<br>
<input type="submit" name="submit" value="提交" οnclick="addsubmit()">
</div>
</form>
传入addServlet
response.setContentType("text/html;charset=utf-8");
response.setCharacterEncoding("utf-8");
String Mname=request.getParameter("addun");
System.out.println(Mname);
Connection conn=DBConnection.getConnection();
String sql="insert into manager(username,password) values(?,?)";
try{
PreparedStatement pst=(PreparedStatement)conn.prepareStatement(sql);
pst.setString(1, request.getParameter("addun"));
pst.setString(2, request.getParameter("addpsw"));
int result = 0;
result=pst.executeUpdate();
System.out.println("插入成功");
}catch(Exception e)
{
e.printStackTrace();
}
return ;
}
我用system.out.print打印当前数据来确认这里确实传入数据,那些bug出的一个个泪流满面啊让人,这么个简单的东西
然后,登录成功了,就显示到数据页面
show.jsp
<body style="background:url(manager.jpg)">
<br>
<br>
<br>
<font size=7 color=#494e8f face="宋体"><center>查询呼叫评价系统管理平台</center></font>
<br>
<br>
<table align="center" bordercolor="#2a5caa">
<tr>
<td>ID</td>
<td>乘车舒适度</td>
<td>有无异味</td>
<td>安全防范措施</td>
<td>车辆信息</td>
<td>核载人数</td>
<td>服务态度</td>
<td>道路熟悉度</td>
<td>驾驶精力</td>
<td>接听电话</td>
<td>是否绕路</td>
<td>您的建议</td>
<td>管理 </td>
</tr>
<%
ArrayList<Information> list=(ArrayList<Information>)request.getAttribute("list");
if(list==null||list.size()<1){
out.print("没有数据");
}else{
for(Information info:list){
%>
<tr >
<td><%=info.getId()%></td>
<td><%=info.getcarone_feel() %></td>
<td><%=info.getcartwo_feel() %></td>
<td><%=info.getcarthree_feel() %></td>
<td><%=info.getcarfour_feel() %></td>
<td><%=info.getcarfive_feel() %></td>
<td><%=info.getdrione_feel() %></td>
<td><%=info.getdritwo_feel() %></td>
<td><%=info.getdrithree_feel() %></td>
<td><%=info.getdrifour_feel() %></td>
<td><%=info.getdrifive_feel() %></td>
<td><%=info.getsuggestion() %></td>
<td>
<a href="DeleteServlet?id=<%=info.getId()%>">删除</a>
<a href="DetailServlet?id=<%=info.getId()%>">查看</a>
<a href="repeat.jsp">回复</a>
</td>
</tr>
<%
}
}
%>
</table>
<br> <br> <br>
<div align="center" style="font-size:20; background:#b2d235; width:180px ;height:28px ;position:relative;left:600px;top:10px">
<%
out.print("数据查询成功");
%>
</div>
</body>
从findservlet获取
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
Connection conn=DBConnection.getConnection();
try
{
Statement stmt=conn.createStatement();
String sql="select*from data";
ResultSet rs=stmt.executeQuery(sql);
ArrayList<Information> list=new ArrayList<Information>();
while(rs.next())
{
Information info=new Information();
info.setId(rs.getInt("id"));
info.setcarone_feel(rs.getString("carone_feel"));
info.setcartwo_feel(rs.getString("cartwo_feel"));
info.setcarthree_feel(rs.getString("carthree_feel"));
info.setcarfour_feel(rs.getString("carfour_feel"));
info.setcarfive_feel(rs.getString("carfive_feel"));
info.setdrione_feel(rs.getString("drione_feel"));
info.setdritwo_feel(rs.getString("dritwo_feel"));
info.setdrithree_feel(rs.getString("drithree_feel"));
info.setdrifour_feel(rs.getString("drifour_feel"));
info.setdrifive_feel(rs.getString("drifive_feel"));
info.setsuggestion(rs.getString("suggestion"));
list.add(info);
}
request.setAttribute("list", list);
rs.close();
stmt.close();
conn.close();
}catch(SQLException e)
{
e.printStackTrace();
}
request.getRequestDispatcher("show.jsp").forward(request,response);
}
这里已经调用了DBConnection数据库连接,放在最后吧,
这是查询到的数据,然后删除功能
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
int id=Integer.valueOf(request.getParameter("id"));
try{
Connection conn=DBConnection.getConnection();
String sql="delete from data where id=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, id);
ps.executeUpdate();
ps.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
response.sendRedirect("FindServlet");
// request.getRequestDispatcher("show.jsp").forward(request,response);
}
这里的id是从show,jsp点击超链获取的id
再后查看,其实这没什么好看的,,,
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
int id=Integer.valueOf(request.getParameter("id"));
System.out.print(id);
Connection conn=DBConnection.getConnection();
try
{
Statement stmt=conn.createStatement();
String sql="select*from data where id="+id;
System.out.println(sql);
ResultSet rs=stmt.executeQuery(sql);
while(rs.next())
{
int i=rs.getInt("id");
String b=rs.getString("carone_feel");
String c=rs.getString("cartwo_feel");
String d=rs.getString("carthree_feel");
String e=rs.getString("carfour_feel");
String f=rs.getString("carfive_feel");
String g=rs.getString("drione_feel");
String h=rs.getString("dritwo_feel");
String j=rs.getString("drithree_feel");
String k=rs.getString("drifour_feel");
String l=rs.getString("drifive_feel");
String m=rs.getString("suggestion");
String total=" "+b+" "+c+" "+d+" "+e+" "+f+" "+g+" "+h+" "+j+" "+k+" "+l+" ";
request.setAttribute("sug", m);
request.setAttribute("id", i);
request.setAttribute("message", total);
}
rs.close();
stmt.close();
conn.close();
}catch(Exception e)
{
e.printStackTrace();
}
request.getRequestDispatcher("datail.jsp").forward(request, response);
}
查看页面就几行字,
回复还在思考,从server传输数据到android