Android Javaweb MySQL Tomcat 實(shí)現(xiàn)本地簡(jiǎn)易Api接口
需要配置兩個(gè)jar包 jackson-all-2.8.0.jar json解析和mysql 連接 mysql-connector-java-5.1.7-bin.jar 文末 云盤(pán)地址壓縮包包含。
使用Tomcat 本地服務(wù)疑苔，連接的是本地MYSQL 數(shù)據(jù)庫(kù)world 數(shù)據(jù)庫(kù) city表帽蝶，字段如圖：
image.png
創(chuàng)建city的實(shí)體類(lèi)：
public class CityBeen {
private String ID;
private String Name;
private String CountryCode;
private String District;
private String Population;
//get set
}
預(yù)期達(dá)到返回json格式,也就是包裝成json格式：
單個(gè)的
{
"code": "ok",
"msg": "訪(fǎng)問(wèn)成功",
"time": 1510989490351,
"object": {
"id": "1",
"name": "Kabul",
"population": "1780000",
"countryCode": "AFG",
"district": "Kabol"
}
}
列表
{
"code": "ok",
"msg": "訪(fǎng)問(wèn)成功",
"time": 1511175554098,
"items": [
{ … },
{ … },
{ … },
]
}
這樣子 可以找出其中的共同部分 就是code msg time ，可以據(jù)共同部分此做一個(gè)實(shí)體類(lèi)的基類(lèi)第队，包含code狀態(tài)碼 附加的msg以及時(shí)間戳 ：
public class AbstractJsonObject {
private String code;
private String msg;
private Long time = new Date().getTime();
//省去get.set方法
｝
然后單個(gè)、列表返回對(duì)象刨秆，可以繼承AbstractJsonObject 類(lèi)凳谦。
單個(gè)的SingleObject
public class SingleObject extends AbstractJsonObject{
private Object object;
// get set 方法
}
列表的 ListObject
public class ListObject extends AbstractJsonObject {
// 列表對(duì)象
private List<?> items;
public List<?> getItems() {
return items;
}
public void setItems(List<?> items) {
this.items = items;
}
}
實(shí)體類(lèi)都有了，那么該考慮數(shù)據(jù)的問(wèn)題衡未，思想就是 通過(guò)連接mysql數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)表的增刪改查尸执。mysql 連接 mysql-connector-java-5.1.7-bin.jar包引進(jìn)到項(xiàng)目里；新建數(shù)據(jù)庫(kù)操作的Dbhelper工具類(lèi)：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DbHelper {
public static final String url = "jdbc:mysql://localhost/world";
public static final String name = "com.mysql.jdbc.Driver";
public static final String user = "root";
public static final String password = "000000";
public Connection conn = null;
public PreparedStatement pst = null;
public DbHelper(String sql) {
try {
Class.forName(name);//
conn = DriverManager.getConnection(url, user, password);//
pst = conn.prepareStatement(sql);//
} catch (Exception e) {
System.err.println(e.getMessage());
e.printStackTrace();
}
}
public void close() {
try {
this.conn.close();
this.pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
其中要注意的是引用的包是java.sql.下的缓醋，所以把包都貼上來(lái)了如失，jdbc的url 要具體到對(duì)應(yīng)的數(shù)據(jù)庫(kù)，之外 Class.forName(name);e是一個(gè)靜態(tài)方法,同樣可以用來(lái)加載類(lèi)送粱，然后可以創(chuàng)建該類(lèi)的對(duì)象褪贵，http://bbs.csdn.net/topics/390216436
諸多大佬有比較清楚的解釋。
數(shù)據(jù)庫(kù)連接通之后就是數(shù)據(jù)的獲取和解析包裝：
下文方法是新建dbhelper對(duì)象執(zhí)行sql查詢(xún)語(yǔ)句從city表獲取所有數(shù)據(jù)：
public static List<CityBeen> getAllStudents() {
List<CityBeen> list = new ArrayList<CityBeen>();//list對(duì)象
String sql = null;
DbHelper db1 = null;
sql = "select * from city";// SQL
db1 = new DbHelper(sql);//創(chuàng)建DBHelper對(duì)象
ResultSet ret = null;//創(chuàng)建結(jié)果集對(duì)象抗俄，執(zhí)行sql后返回的數(shù)據(jù)集合
try {
ret = db1.pst.executeQuery();//這個(gè)方法就類(lèi)似于執(zhí)行了SELECT語(yǔ)句一樣脆丁！
while (ret.next()) {
String id = ret.getString(1);//第一列是id
String name = ret.getString(2);//第二列是name
String CountryCode = ret.getString(3);//第三列是CountryCode
String District = ret.getString(4);//第四列是District
String p= ret.getString(5);//第四列是Population
CityBeen city = new CityBeen();//創(chuàng)建city對(duì)象
city.setID(id);//設(shè)置id
city.setName(name);//設(shè)置name
city.setCountryCode(CountryCode);//設(shè)置CountryCode
city.setDistrict(District);//設(shè)置District
city.setPopulation(p);//設(shè)置District
list.add(city);//將city對(duì)象放置到列表中
} //循環(huán)從結(jié)果集中獲取數(shù)據(jù)并設(shè)置到list列表對(duì)象中
ret.close();//關(guān)閉對(duì)象
db1.close();//關(guān)系數(shù)據(jù)庫(kù)連接
} catch (SQLException e) {
e.printStackTrace();
} //
return list;//返回結(jié)果
}
但是 一個(gè)表的數(shù)據(jù)那么多顯然不現(xiàn)實(shí)，那么還是要從分頁(yè)查詢(xún)动雹，當(dāng)然這樣可以做測(cè)試來(lái)嘗試是否連接成功槽卫。復(fù)習(xí)mysql 語(yǔ)法:
mysql中的sql語(yǔ)句: select * from 表名 limit 0,10;表示取表中的前10條數(shù)據(jù)(從第1條開(kāi)始,取10條)
返回具體一個(gè)的sql很容易事項(xiàng)，那么需要兩個(gè)值一個(gè)起始值和一個(gè)偏移量胰蝠，也就是(頁(yè)碼-1)*一頁(yè)數(shù)量歼培，一頁(yè)數(shù)量震蒋。
那么完整的 實(shí)現(xiàn)從數(shù)據(jù)庫(kù)取值賦值的類(lèi)為：
public class CityBusiness {
/**
* 獲取所有數(shù)據(jù)
* @return
*/
public static List<CityBeen> getAllStudents() {
List<CityBeen> list = new ArrayList<CityBeen>();//list對(duì)象
String sql = null;
DbHelper db1 = null;
sql = "select * from city";// SQL
db1 = new DbHelper(sql);//創(chuàng)建DBHelper對(duì)象
ResultSet ret = null;//創(chuàng)建結(jié)果集對(duì)象，執(zhí)行sql后返回的數(shù)據(jù)集合
try {
ret = db1.pst.executeQuery();//這個(gè)方法就類(lèi)似于執(zhí)行了SELECT語(yǔ)句一樣丐怯！
while (ret.next()) {
String id = ret.getString(1);//第一列是id
String name = ret.getString(2);//第二列是name
String CountryCode = ret.getString(3);//第三列是CountryCode
String District = ret.getString(4);//第四列是District
String p= ret.getString(5);//第四列是Population
CityBeen city = new CityBeen();//創(chuàng)建city對(duì)象
city.setID(id);//設(shè)置id
city.setName(name);//設(shè)置name
city.setCountryCode(CountryCode);//設(shè)置CountryCode
city.setDistrict(District);//設(shè)置District
city.setPopulation(p);//設(shè)置District
list.add(city);//將city對(duì)象放置到列表中
} //循環(huán)從結(jié)果集中獲取數(shù)據(jù)并設(shè)置到list列表對(duì)象中
ret.close();//關(guān)閉對(duì)象
db1.close();//關(guān)系數(shù)據(jù)庫(kù)連接
} catch (SQLException e) {
e.printStackTrace();
} //
return list;//返回結(jié)果
}
/**
* 查詢(xún)某一頁(yè)
* @param page
* @param num
* @return
*/
public static List<CityBeen> getStudentsBypage(String pagenum,String datanum) {
List<CityBeen> list = new ArrayList<CityBeen>();//list對(duì)象
String sql = null;
DbHelper db1 = null;
int page=Integer.parseInt(pagenum);//第幾頁(yè)
int num=Integer.parseInt(datanum);//一頁(yè)數(shù)量
int start=(page-1)*num ;
sql = "select * from city limit "+start+","+num;// SQL
db1 = new DbHelper(sql);//創(chuàng)建DBHelper對(duì)象
ResultSet ret = null;//創(chuàng)建結(jié)果集對(duì)象喷好，執(zhí)行sql后返回的數(shù)據(jù)集合
try {
ret = db1.pst.executeQuery();//這個(gè)方法就類(lèi)似于執(zhí)行了SELECT語(yǔ)句一樣！
while (ret.next()) {
String id = ret.getString(1);//第一列是id
String name = ret.getString(2);//第二列是name
String CountryCode = ret.getString(3);//第三列是CountryCode
String District = ret.getString(4);//第四列是District
String p= ret.getString(5);//第四列是Population
CityBeen city = new CityBeen();//創(chuàng)建city對(duì)象
city.setID(id);//設(shè)置id
city.setName(name);//設(shè)置name
city.setCountryCode(CountryCode);//設(shè)置CountryCode
city.setDistrict(District);//設(shè)置District
city.setPopulation(p);//設(shè)置District
list.add(city);//將city對(duì)象放置到列表中
} //循環(huán)從結(jié)果集中獲取數(shù)據(jù)并設(shè)置到list列表對(duì)象中
ret.close();//關(guān)閉對(duì)象
db1.close();//關(guān)系數(shù)據(jù)庫(kù)連接
} catch (SQLException e) {
e.printStackTrace();
} //
return list;//返回結(jié)果
}
/**
* 通過(guò)id來(lái)獲取某個(gè)城市數(shù)據(jù)
* @param _id
* @return
*/
public static CityBeen getStudentInfoById(String _id) {
String sql = null;
DbHelper db1 = null;
sql = "select * from city where id =" + _id;// sql
db1 = new DbHelper(sql);//創(chuàng)建DBHelper對(duì)象
ResultSet ret = null;//創(chuàng)建結(jié)果集對(duì)象
CityBeen city = new CityBeen();//創(chuàng)建對(duì)象
try {
ret = db1.pst.executeQuery();//正常來(lái)說(shuō)读跷，這個(gè)結(jié)果集只有一個(gè)對(duì)象
while (ret.next()) {
String id = ret.getString(1);//第一列是id
String name = ret.getString(2);//第二列是name
String CountryCode = ret.getString(3);//第三列是CountryCode
String District = ret.getString(4);//第四列是District
String Population= ret.getString(5);//第四列是Population
city.setID(id);//設(shè)置id
city.setName(name);//設(shè)置name
city.setCountryCode(CountryCode);//設(shè)置CountryCode
city.setDistrict(District);//設(shè)置District
city.setPopulation(Population);//設(shè)置District
} //循環(huán)從結(jié)果集中獲取數(shù)據(jù)并設(shè)置到對(duì)象中（正常來(lái)說(shuō)梗搅，這個(gè)循環(huán)只執(zhí)行一次）
ret.close();//關(guān)閉對(duì)象
db1.close();//關(guān)系數(shù)據(jù)庫(kù)連接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} //
return city;//返回結(jié)果
}
}
有三個(gè)靜態(tài)類(lèi)分別是返回所有記錄和 分頁(yè)查詢(xún)和按照id 查詢(xún)。
使用工廠(chǎng)模式思想效览，創(chuàng)建一個(gè)實(shí)現(xiàn)類(lèi)實(shí)現(xiàn)接口无切，可以這樣寫(xiě)：
首先是一個(gè)服務(wù)接口CityService：
public interface CityService {
public List<CityBeen> getAllCitys();
public CityBeen getCityByid(String id);
public List<CityBeen> getCitysByPage(String pagenum,String datanum);
}
實(shí)現(xiàn)類(lèi)：
public class CityServiceImpl implements CityService{
@Override
public List<CityBeen> getAllCitys() {
// TODO Auto-generated method stub
return CityBusiness.getAllStudents();
}
@Override
public CityBeen getCityByid(String id) {
// TODO Auto-generated method stub
return CityBusiness.getStudentInfoById(id);
}
@Override
public List<CityBeen> getCitysByPage(String pagenum, String datanum) {
// TODO Auto-generated method stub
return CityBusiness.getStudentsBypage(pagenum, datanum);
}
}
這樣子，要構(gòu)建返回訪(fǎng)問(wèn)具體的對(duì)象時(shí)可以類(lèi)似這樣獲蓉ね鳌：
CityBeen city = new CityServiceImpl().getCityByid("1");//從數(shù)據(jù)庫(kù)查找到id=1的城市
SingleObject object = new SingleObject();//構(gòu)建單個(gè)返回對(duì)象
object.setObject(city);//返回內(nèi)容
object.setcode("ok");//狀態(tài)碼
object.setmsg("獲取信息成功")//附加信息
最后把object 解析成json返回http請(qǐng)求就可以了哆键，每個(gè)返回對(duì)象都有code 的成員變量和msg成員變量，這個(gè)要定義為cons瘦锹；
public class StatusCode {
public static String CODE_SUCCESS = "ok";//訪(fǎng)問(wèn)成功
public static String CODE_ERROR = "0001"; //訪(fǎng)問(wèn)錯(cuò)誤
public static String CODE_ERROR_PARAMETER = "0002";//參數(shù)錯(cuò)誤
public static String CODE_ERROR_PROGRAM = "0003";//程序異常
}
其實(shí)也可以把這兩個(gè)成員變量拿出來(lái)做一個(gè)對(duì)象統(tǒng)一管理：
public class StatusObject {
// 狀態(tài)碼
private String code;
// 狀態(tài)信息
private String msg;
public StatusObject(String code, String msg) {
super();
this.code = code;
this.msg = msg;
}
//get set
｝
為了統(tǒng)一管理狀態(tài)碼和msg的關(guān)聯(lián)籍嘹，新建StatusHouse 類(lèi)
public class StatusHouse {
public static StatusObject COMMON_STATUS_OK = new StatusObject(StatusCode.CODE_SUCCESS, "訪(fǎng)問(wèn)成功");
public static StatusObject COMMON_STATUS_ERROR = new StatusObject(StatusCode.CODE_ERROR,
"訪(fǎng)問(wèn)錯(cuò)誤，錯(cuò)誤碼：(" + StatusCode.CODE_ERROR + ")");
public static StatusObject COMMON_STATUS_ERROR_PROGRAM = new StatusObject(StatusCode.CODE_ERROR_PROGRAM,
"程序異常弯院，錯(cuò)誤碼：(" + StatusCode.CODE_ERROR_PROGRAM + ")");
public static StatusObject COMMON_STATUS_ERROR_PARAMETER = new StatusObject(StatusCode.CODE_ERROR_PARAMETER,
"參數(shù)錯(cuò)誤辱士，錯(cuò)誤碼：(" + StatusCode.CODE_ERROR_PARAMETER + ")");
}
在增加 AbstractJsonObject 增加set方法
public void setStatusObject(StatusObject statusObject) {
this.code = statusObject.getCode();
this.msg = statusObject.getMsg();
}
這樣子就可以一下子設(shè)定code 和msg 。
到了這里 還需要把實(shí)體類(lèi)轉(zhuǎn)換成json 的工具類(lèi)： jackson-all-2.8.0.jar包引進(jìn)項(xiàng)目（因?yàn)閰⒖紕e人的听绳，所以就直接拿過(guò)來(lái)用了）
json工具類(lèi):
import com.fasterxml.jackson.databind.ObjectMapper;
public class JackJsonUtils {
static ObjectMapper objectMapper;
/**
* 解析json
*
* @param content
* @param valueType
* @return
*/
public static <T> T fromJson(String content, Class<T> valueType) {
if (objectMapper == null) {
objectMapper = new ObjectMapper();
}
try {
return objectMapper.readValue(content, valueType);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 生成json
*
* @param object
* @return
*/
public static String toJson(Object object) {
if (objectMapper == null) {
objectMapper = new ObjectMapper();
}
try {
return objectMapper.writeValueAsString(object);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
這樣子解析也完了颂碘，就差到web訪(fǎng)問(wèn)了，web涉及到可能亂碼的問(wèn)題所以也需要一個(gè)工具椅挣。
public class ResponseUtils {
/**
* 返回json 串
*
* @param response
* @param text
*/
public static void renderJson(HttpServletResponse response, String text) {
// System.out.print(text);
render(response, "text/plain;charset=UTF-8", text);
}
/**
* 發(fā)送內(nèi)容头岔。使用UTF-8編碼。
*
* @param response
* @param contentType
* @param text
*/
public static void render(HttpServletResponse response, String contentType, String text) {
response.setContentType(contentType);
response.setCharacterEncoding("utf-8");
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
try {
response.getWriter().write(text);
} catch (IOException e) {
}
}
}
最后就差sel
/**
* Servlet implementation class CityServleet
*/
@WebServlet("/CityServleet")
public class CityServleet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* Default constructor.
*/
public CityServleet() {
super();
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String action = request.getParameter("action");
switch (action) {
case "all":
List<CityBeen> list = new CityServiceImpl().getAllCitys();
ListObject listObject = new ListObject();
listObject.setItems(list);
listObject.setStatusObject(StatusHouse.COMMON_STATUS_OK);
String responseText = JackJsonUtils.toJson(listObject);
ResponseUtils.renderJson(response, responseText);
break;
case "id":
CityBeen city = new CityServiceImpl().getCityByid("1");
SingleObject object = new SingleObject();
object.setStatusObject(StatusHouse.COMMON_STATUS_OK);
object.setObject(city);
ResponseUtils.renderJson(response, JackJsonUtils.toJson(object));
break;
case "page":
String page = request.getParameter("page");
String num = request.getParameter("num");
List<CityBeen> list2 = new CityServiceImpl().getCitysByPage(page, num);
ListObject listObject2 = new ListObject();
listObject2.setItems(list2);
listObject2.setStatusObject(StatusHouse.COMMON_STATUS_OK);
String responseText2 = JackJsonUtils.toJson(listObject2);
ResponseUtils.renderJson(response, responseText2);
break;
default:
SingleObject object2 = new SingleObject();
object2.setStatusObject(StatusHouse.COMMON_STATUS_ERROR_PARAMETER);
object2.setObject("");
ResponseUtils.renderJson(response, JackJsonUtils.toJson(object2));
break;
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
可以看到不管四post請(qǐng)求還是get 請(qǐng)求都是要先獲取action 參數(shù)來(lái)具體下一步動(dòng)作鼠证。最后返回?cái)?shù)據(jù)峡竣。
最后使用tomcat run on sever...測(cè)試。
注意：jackson-all-2.8.0.jar json解析和mysql 連接 mysql-connector-java-5.1.7-bin.jar要復(fù)制一份到tomcat的安裝路徑的lib下量九，否則會(huì)報(bào) notfound錯(cuò)誤澎胡。
測(cè)試一下：
image.png
項(xiàng)目鏈接:
https://github.com/silencefun/WebTest
壓縮包地址（包含jar包）：https://pan.baidu.com/s/1gfdBrpx 密碼: vg6r
參考：http://blog.csdn.net/zxw136511485/article/details/51437115
