最近在测试中发现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 rows;

public TestCaseTotal() {

}

public TestCaseTotal(int total, List rows) {

this.total = total;

this.rows = rows;

}

public int getTotal() {

return total;

}

public void setTotal(int total) {

this.total = total;

}

public List getRows() {

return rows;

}

public void setRows(List rows) {

this.rows = rows;

}

}

5.基础操作写到一个接口里

package model;

import java.util.List;

public interface TestCaseDao {

public List 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 getCaseAll() {

// TODO Auto-generated method stub

List list = new ArrayList();

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 list=testCaseDaoImpl.getCaseAll();

List list1 =new ArrayList<>();

int size =list.size();

TestCase testCase;

for(int i=0;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 caselist = new ArrayList();

//以下代码做分页，写的不好，不喜勿喷

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

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

for(int i=10*pageNum;i<10*pageNum+10;i++){

caselist.add(list1.get(i));

}

}

else{

for(int i=10*pageNum;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

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

Test Json1

9.修改web.xml文件如下

JsonTest

index.jsp

JsonServlet

model.JsonServlet

JsonServlet

/getJson

10.右击项目名-->选择Run as-->选择Run on Server

11.运行成功后如下：

image.png

点击按钮展示接口数据如图

image.png

每页只展示10条数据，若需要查看第二页数据传入参数page =1即可

image.png