转载于:(https://blog.csdn.net/qq_31242531/article/details/80497940)
内容详见上文,谢谢!
最近在测试中发现mysql数据库保存测试用例,测试结果是一种不错的选择,为了后期方便在客户端展示测试,便写了个接口。此次接口开发不使用任何框架,需下载gson.jar,mysql-connector.jar,servlet-api.jar,以下为接口的开发思路
1.创建数据表,我这边使用的是mysql数据库
-
/*
-
SQLyog 企业版 - MySQL GUI v8.14
-
MySQL - 5.5.49 : Database - test
-
*********************************************************************
-
*/
-
-
-
/*!40101 SET NAMES utf8 */;
-
-
/*!40101 SET SQL_MODE=''*/;
-
-
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
-
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
-
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-
CREATE
DATABASE
/*!32312 IF NOT EXISTS*/
`test`
/*!40100 DEFAULT CHARACTER SET utf8 */;
-
-
USE
`test`;
-
-
/*Table structure for table `testcase` */
-
-
DROP
TABLE
IF
EXISTS
`testcase`;
-
-
CREATE
TABLE
`testcase` (
-
`caseId`
INT(
11)
NOT
NULL AUTO_INCREMENT,
-
`caseName`
VARCHAR(
255)
CHARACTER
SET latin1
NOT
NULL,
-
`caseDetail`
VARCHAR(
255)
CHARACTER
SET latin1
NOT
NULL,
-
`caseDevice`
VARCHAR(
255)
CHARACTER
SET latin1
NOT
NULL,
-
PRIMARY
KEY (
`caseId`)
-
)
ENGINE=
INNODB AUTO_INCREMENT=
48
DEFAULT
CHARSET=utf8
CHECKSUM=
1 DELAY_KEY_WRITE=
1 ROW_FORMAT=DYNAMIC;
-
-
/*Data for the table `testcase` */
-
-
INSERT
INTO
`testcase`(
`caseId`,
`caseName`,
`caseDetail`,
`caseDevice`)
VALUES (
2,
'2',
'233',
'444'),(
16,
'dsf',
'sf',
'sfd'),(
17,
'safd',
'sadf',
'asf'),(
18,
'saf',
'ggd',
'sdf'),(
19,
'dsaf',
'sdf',
'sdf'),(
20,
'sfd',
'sfd',
'(NfdsULL)'),(
21,
'sdf',
'v',
'v'),(
22,
'f',
'f',
'e'),(
23,
'w',
'r3',
'rt'),(
24,
'er',
'rtrt',
'sfad'),(
25,
'sf',
'sdf',
'fds'),(
26,
'sa',
's',
's'),(
27,
'e',
'e',
'e'),(
28,
'sa',
'rt',
'rt'),(
29,
'we',
'ew',
'qw'),(
30,
'we',
'(NULfL)',
'dsf'),(
31,
'fd',
's',
'g'),(
32,
's',
'g',
'f'),(
33,
'sf',
'f',
'f'),(
34,
'c',
'c',
'c'),(
35,
'e',
'e',
'e'),(
36,
'd',
'd',
'q'),(
37,
'q',
'e',
'r'),(
38,
'f',
'r',
'g'),(
39,
'g',
'(NULgL)',
'g'),(
40,
'gg',
'g',
'd'),(
41,
'f',
'f',
'f'),(
42,
'f',
'f',
'f'),(
43,
'f',
'f',
'f'),(
44,
'v',
'f',
'g'),(
45,
'e',
'e',
'r'),(
46,
'4',
'rt',
'fg'),(
47,
'dsf',
'ds',
'sdf');
-
-
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
-
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
-
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2.编写数据库工具类
-
package model;
-
-
import java.sql.*;
-
-
public
class DBconn {
-
static String url =
"jdbc:mysql://localhost:3306/test?useunicuee=true& characterEncoding=utf8";
-
static String username =
"root";
-
static String password =
"123456";
-
static Connection conn =
null;
-
static ResultSet rs =
null;
-
static PreparedStatement ps =
null;
-
public static void init(){
-
try {
-
Class.forName(
"com.mysql.jdbc.Driver");
-
conn = DriverManager.getConnection(url,username,password);
-
}
catch (Exception e) {
-
System.out.println(
"init [SQL驱动程序初始化失败!]");
-
e.printStackTrace();
-
}
-
}
-
public static int addUpdDel(String sql){
-
int i =
0;
-
try {
-
PreparedStatement ps = conn.prepareStatement(sql);
-
i = ps.executeUpdate();
-
}
catch (SQLException e) {
-
System.out.println(
"sql数据库增删改异常");
-
e.printStackTrace();
-
}
-
-
return i;
-
}
-
public static ResultSet selectSql(String sql){
-
try {
-
ps = conn.prepareStatement(sql);
-
rs = ps.executeQuery(sql);
-
}
catch (SQLException e) {
-
System.out.println(
"sql数据库查询异常");
-
e.printStackTrace();
-
}
-
return rs;
-
}
-
public static void closeConn(){
-
try {
-
conn.close();
-
}
catch (SQLException e) {
-
System.out.println(
"sql数据库关闭异常");
-
e.printStackTrace();
-
}
-
}
-
}
3.编写实体类
-
package model;
-
-
public
class TestCase {
-
private
int id;
-
private String caseName;
-
private String caseDetail;
-
private String caseDevice;
-
public int getId() {
-
return id;
-
}
-
public void setId(int id) {
-
this.id = id;
-
}
-
public String getCaseName() {
-
return caseName;
-
}
-
public void setCaseName(String caseName) {
-
this.caseName = caseName;
-
}
-
public String getCaseDetail() {
-
return caseDetail;
-
}
-
public void setCaseDetail(String caseDetail) {
-
this.caseDetail = caseDetail;
-
}
-
public String getCaseDevice() {
-
return caseDevice;
-
}
-
public void setCaseDevice(String caseDevice) {
-
this.caseDevice = caseDevice;
-
}
-
-
}
4.新建一个分页实体类,以便后期数据做分页
-
package model;
-
-
import java.util.List;
-
-
public
class TestCaseTotal {
-
private
int total;
-
private List<TestCase> rows;
-
-
-
public TestCaseTotal() {
-
}
-
public TestCaseTotal(int total, List<TestCase> rows) {
-
this.total = total;
-
this.rows = rows;
-
}
-
public int getTotal() {
-
return total;
-
}
-
public void setTotal(int total) {
-
this.total = total;
-
}
-
public List<TestCase> getRows() {
-
return rows;
-
}
-
public void setRows(List<TestCase> rows) {
-
this.rows = rows;
-
}
-
-
}
5.基础操作写到一个接口里
-
package model;
-
-
import java.util.List;
-
-
-
public
interface TestCaseDao {
-
public List<TestCase> getCaseAll();
-
public boolean addCase(TestCase testCase) ;
-
public boolean deleteCase(int id) ;
-
public boolean updateCase(TestCase testCase) ;
-
-
}
6.实现Dao接口
-
package model;
-
-
import java.sql.PreparedStatement;
-
import java.sql.ResultSet;
-
import java.sql.SQLException;
-
import java.util.ArrayList;
-
import java.util.List;
-
-
-
-
public
class TestCaseDaoImpl implements TestCaseDao {
-
private PreparedStatement ptmt =
null;
-
private ResultSet rs =
null;
-
@Override
-
public List<TestCase> getCaseAll() {
-
// TODO Auto-generated method stub
-
List<TestCase> list =
new ArrayList<TestCase>();
-
try {
-
DBconn.init();
-
ResultSet rs = DBconn.selectSql(
"select * from testcase");
-
while(rs.next()){
-
TestCase testCase=
new TestCase();
-
testCase.setId(rs.getInt(
"caseId"));
-
testCase.setCaseName(rs.getString(
"caseName"));
-
testCase.setCaseDetail(rs.getString(
"caseDetail"));
-
testCase.setCaseDevice(rs.getString(
"caseDevice"));
-
-
list.add(testCase);
-
}
-
DBconn.closeConn();
-
return list;
-
}
catch (SQLException e) {
-
e.printStackTrace();
-
}
-
return
null;
-
-
}
-
-
@Override
-
public boolean addCase(TestCase testCase) {
-
// TODO Auto-generated method stub
-
boolean flag =
false;
-
DBconn.init();
-
int i =DBconn.addUpdDel(
"insert into user(caseName,caseDetail,caseDevice) " +
-
"values('"+testCase.getCaseName()+
"','"+testCase.getCaseDetail()+
"','"+testCase.getCaseDevice()+
"')");
-
if(i>
0){
-
flag =
true;
-
}
-
DBconn.closeConn();
-
return flag;
-
}
-
-
@Override
-
public boolean deleteCase(int id) {
-
// TODO Auto-generated method stub
-
boolean flag =
false;
-
DBconn.init();
-
String sql =
"delete from testcase where caseId="+id;
-
-
-
int i =DBconn.addUpdDel(sql);
-
if(i>
0){
-
flag =
true;
-
}
-
DBconn.closeConn();
-
return flag;
-
-
-
}
-
-
@Override
-
public boolean updateCase(TestCase testCase) {
-
// TODO Auto-generated method stub
-
return
false;
-
}
-
-
}
7.新建JsonServlet
-
package model;
-
-
import java.io.IOException;
-
import java.io.PrintWriter;
-
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 com.google.gson.Gson;
-
-
-
-
public
class JsonServlet extends HttpServlet {
-
-
private
static
final
long serialVersionUID =
1L;
-
-
@Override
-
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
-
-
-
TestCaseDaoImpl testCaseDaoImpl=
new TestCaseDaoImpl();
-
List<TestCase> list=testCaseDaoImpl.getCaseAll();
-
List<TestCase> list1 =
new ArrayList<>();
-
int size =list.size();
-
TestCase testCase;
-
for(
int i=
0;i<size;i++){
-
testCase=
new TestCase();
-
testCase.setId(list.get(i).getId());
-
testCase.setCaseName(list.get(i).getCaseName());
-
testCase.setCaseDetail(list.get(i).getCaseDetail());
-
list1.add(testCase);
-
-
}
-
-
String page = req.getParameter(
"page");
-
// 将数据添加到数组
-
List<TestCase> caselist =
new ArrayList<TestCase>();
-
-
//以下代码做分页,写的不好,不喜勿喷
-
if (page ==
null || page.equals(
"0")) {
-
if(list1.size()>
9){
-
for(
int i=
0;i<
10;i++){
-
caselist.add(list1.get(i));
-
}
-
}
-
else{
-
for(
int i=
0;i<list1.size();i++){
-
caselist.add(list1.get(i));
-
}
-
}
-
-
}
-
else {
-
int caseSize=list1.size();
-
int page1 =caseSize/
10;
-
-
int pageNum=Integer.parseInt(page);
-
//String parm=Integer.toString(a);
-
if(pageNum<page1){
-
for(
int i=
10*pageNum;i<
10*pageNum+
10;i++){
-
caselist.add(list1.get(i));
-
}
-
}
-
else{
-
for(
int i=
10*pageNum;i<list1.size();i++){
-
caselist.add(list1.get(i));
-
}
-
}
-
-
}
-
-
-
TestCaseTotal nt =
new TestCaseTotal(caselist.size(), caselist);
-
-
// 调用GSON jar工具包封装好的toJson方法,可直接生成JSON字符串
-
Gson gson =
new Gson();
-
String json = gson.toJson(nt);
-
-
// 输出到界面
-
System.out.println(json);
-
resp.setContentType(
"text/plain");
-
resp.setCharacterEncoding(
"gb2312");
-
PrintWriter out =
new PrintWriter(resp.getOutputStream());
-
out.print(json);
-
out.flush();
-
-
}
-
-
@Override
-
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
-
this.doGet(req, resp);
-
}
-
-
}
8.新建index.jsp
-
<%@ page language="java" contentType="text/html; charset=UTF-8" 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>Test Json1
</title>
-
-
-
<meta http-equiv="Content-Type" content="text/html"; charset=UTF-8>
-
<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">
-
-
-
</head>
-
<body >
-
<form action="getJson" method="get">
-
<input type="submit" value="点击获取测试用例数据"/>
-
</form>
-
</body>
-
</html>
9.修改web.xml文件如下
-
<?xml version="1.0" encoding="UTF-8"?>
-
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
-
<display-name>JsonTest
</display-name>
-
<welcome-file-list>
-
<welcome-file>index.jsp
</welcome-file>
-
</welcome-file-list>
-
-
<servlet>
-
<servlet-name>JsonServlet
</servlet-name>
-
<servlet-class>model.JsonServlet
</servlet-class>
-
</servlet>
-
<servlet-mapping>
-
<servlet-name>JsonServlet
</servlet-name>
-
<url-pattern>/getJson
</url-pattern>
-
</servlet-mapping>
-
</web-app>
10.右击项目名-->选择Run as-->选择Run on Server
11.运行成功后如下:
每页只展示10条数据,若需要查看第二页数据传入参数page =1即可
<script>
(function(){
function setArticleH(btnReadmore,posi){
var winH = $(window).height();
var articleBox = $("div.article_content");
var artH = articleBox.height();
if(artH > winH*posi){
articleBox.css({
'height':winH*posi+'px',
'overflow':'hidden'
})
btnReadmore.click(function(){
if(typeof window.localStorage === "object" && typeof window.csdn.anonymousUserLimit === "object"){
if(!window.csdn.anonymousUserLimit.judgment()){
window.csdn.anonymousUserLimit.Jumplogin();
return false;
}else if(!currentUserName){
window.csdn.anonymousUserLimit.updata();
}
}
articleBox.removeAttr("style");
$(this).parent().remove();
})
}else{
btnReadmore.parent().remove();
}
}
var btnReadmore = $("#btn-readmore");
if(btnReadmore.length>0){
if(currentUserName){
setArticleH(btnReadmore,3);
}else{
setArticleH(btnReadmore,1.2);
}
}
})()
</script>
</article>