最近在测试中发现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.运行成功后如下：
image.png
点击按钮展示接口数据如图
image.png
每页只展示10条数据，若需要查看第二页数据传入参数page =1即可
image.png
网友评论