前言:实现一个简单的功能:从本地数据库读取所有的书籍数据,那么会想到以下几个方面:
一、Eclipse新建一个Dynamic Web Project
二、下载连接MySQL数据的驱动
1、使用JDBC连接MySQL的驱动下载Connentor/J
2、新建一个lib文件夹,将mysql-connector-java-5.1.45-bin.jar包拷贝到如下图目录
3、将生成和解析json的相关包也下载下来:jackson-core、jackson-annotations、jackson-databind
方便大家下载jackson-core,jackson-annotations,jackson-databind jar包
https://github.com/FasterXML/jackson-core/wiki
https://github.com/FasterXML/jackson-annotations/wiki
https://github.com/FasterXML/jackson-databind/wiki
4、右击刚才添加的jar包,选择Build Path —Add to Build Path,这样Jar包就成功导入到项目中了。可以在上图的“Referenced Libraries”中看到。
三、开始相关代码开发,先看下我的目录结构
1、我们根据数据库new_schema中Booklist表的数据结构,创建数据库和表不太清楚的,请看上一篇,新建BookModel
2、对照Booklist表将各字段写出来,然后选中这些字段,右击–Source–Generate Getters and Setters,自动补全各字段的get和set方法
3、相关代码
public class BookModel {
private int id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBook_name() {
return book_name;
}
public void setBook_name(String book_name) {
this.book_name = book_name;
}
public String getBook_num() {
return book_num;
}
public void setBook_num(String book_num) {
this.book_num = book_num;
}
public Date getBuy_Date() {
return buy_Date;
}
public void setBuy_Date(Date buy_Date) {
this.buy_Date = buy_Date;
}
public String getBook_author() {
return book_author;
}
public void setBook_author(String book_author) {
this.book_author = book_author;
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public String getCatgory() {
return catgory;
}
public void setCatgory(String catgory) {
this.catgory = catgory;
}
private String book_name;
private String book_num;
private Date buy_Date;
private String book_author;
private Float price;
private int count;
private String catgory;
}
四、数据库的相关操作
1、初始化数据库,新建DBHelp类,数据库的连接就是这样的操作,只需修改url、账号和密码
package com.database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* 数据库初始化相关操作
* */
public class DBHelp {
//数据库url :我用的是本地数据库,new_schema是数据库的名称
public static final String url = "jdbc:mysql://localhost:3306/new_schema";
public static final String name = "com.mysql.jdbc.Driver";//JDBC driver name
public static final String user = "root";//数据库用户名
public static final String password = "123456";// 数据库密码
public Connection connection = null;//数据库连接对象
public PreparedStatement pst = null;//对象
//初始化数据库
public DBHelp(String sql) {
try {
Class.forName(name);//注册JDBC driver
connection = DriverManager.getConnection(url, user,password);//打开一个数据库连接
pst = connection.prepareStatement(sql);//执行sql后,创建了preparedStatemen对象
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭数据库
public void close() {
try {
this.connection.close();
this.pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2、新建BookBusiness类,返回查询数据库的信息
package com.database;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.model.BookModel;
public class BookBusiness {
/**
* 获取所有的图书数据
* 从数据库中查询书籍信息
* @return
*/
public static List<BookModel> getAllBooks() {
List<BookModel> list = new ArrayList<BookModel>();//list对象
String sql = null;
DBHelp db1 = null;
sql = "select * from Booklist";// SQL
db1 = new DBHelp(sql);//创建DBHelper对象
ResultSet ret = null;//创建结果集对象,执行sql后返回的数据集合
try {
ret = db1.pst.executeQuery();//这个方法就类似于执行了SELECT语句一样!
//遍历查询到的信息
while (ret.next()) {
//注意数据类型必须与数据库的一致
int id = ret.getInt(1);
String book_name = ret.getString(2);
Date buy_Date = ret.getDate(3);
String book_num = ret.getString(4);
String book_author = ret.getString(5);
Float price = ret.getFloat(6);
int count = ret.getInt(7);
String catgory = ret.getString(8);
System.out.println("id = " + id + "book_name=" + book_name + "buy_Date = " + buy_Date + "book_num = " + book_num + "book_author = " + book_author);
BookModel bookModel = new BookModel();//创建Book对象
bookModel.setId(id);//设置id
bookModel.setBook_name(book_name);//设置name
bookModel.setBuy_Date(buy_Date);
bookModel.setBook_num(book_num);
bookModel.setBook_author(book_author);
bookModel.setPrice(price);
bookModel.setCount(count);
bookModel.setCatgory(catgory);
list.add(bookModel);//将students对象放置到列表中
} //循环从结果集中获取数据并设置到list列表对象中
ret.close();//关闭对象
db1.close();//关系数据库连接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} //
System.out.println("getAllBooks-->" + list);
return list;//返回结果
}
五、接口的json数据格式
1、客户端调用接口,返回的json包含了code状态码、msg提示信息、时间戳time、需要处理的data,新建一个json的基类AbstractJsonObject,包含这些信息
package com.json;
import java.util.Date;
/*
* Json数据的基类
* 一般包含状态码code、提示信息msg、时间戳time
* */
public class AbstractJsonObject {
private String code;
private String msg;
private Long time = new Date().getTime();
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public Long getTime() {
return time;
}
public void setTime(Long time) {
this.time = time;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public void setContent(String code, String msg) {
this.code = code;
this.msg = msg;
}
public void setStatusObject(StatusObject statusObject) {
this.code = statusObject.getCode();
this.msg = statusObject.getMsg();
}
}
2、新建一个包含code和msg的StatusObject
package com.json;
/*
* 状态码的
* */
public class StatusObject {
private String code;
private String msg;
public StatusObject(String code, String msg) {
this.code = code;
this.msg = msg;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}
3、定义code状态码StatusCode
/*
* 定义响应状态码
* */
public class StatusCode {
public static String CODE_SUCCESS = "200";//访问成功
public static String CODE_ERROR = "404"; //访问错误
public static String CODE_ERROR_PARAMETER = "201";//参数错误
public static String CODE_ERROR_PROGRAM = "500";//程序异常
public static String CODE_ERROR_NO_LOGIN_OR_TIMEOUT = "202";//未登录或登录超时,请重新登录
public static String CODE_ERROR_EXIST_OPERATION = "203";//已操作
}
4、响应状态码文字提示StatusHouse
package com.json;
/*
* 响应状态码文字提示
* */
public class StatusHouse {
public static StatusObject COMMON_STATUS_OK = new StatusObject(StatusCode.CODE_SUCCESS, "访问成功");
public static StatusObject COMMON_STATUS_ERROR = new StatusObject(StatusCode.CODE_ERROR, "访问错误,错误码:(" + StatusCode.CODE_ERROR + ")");
public static StatusObject COMMON_STATUS_NO_LOGIN_OR_TIMEOUT = new StatusObject(StatusCode.CODE_ERROR_NO_LOGIN_OR_TIMEOUT, "未登录或登录超时,请重新登录,错误码:(" + StatusCode.CODE_ERROR_NO_LOGIN_OR_TIMEOUT + ")");
public static StatusObject COMMON_STATUS_ERROR_PROGRAM = new StatusObject(StatusCode.CODE_ERROR_PROGRAM, "程序异常,错误码:(" + StatusCode.CODE_ERROR_PROGRAM + ")");
public static StatusObject COMMON_STATUS_ERROR_PARAMETER = new StatusObject(StatusCode.CODE_ERROR_PARAMETER, "参数错误,错误码:(" + StatusCode.CODE_ERROR_PARAMETER + ")");
public static StatusObject COMMON_STATUS_EXIST_OPERATION = new StatusObject(StatusCode.CODE_ERROR_EXIST_OPERATION, "已操作,错误码:(" + StatusCode.CODE_ERROR_EXIST_OPERATION + ")");
}
5、新建json对象类SingleObject
package com.json;
public class SingleObject extends AbstractJsonObject {
private Object object;
public Object getObject() {
return object;
}
public void setObject(Object object) {
this.object = object;
}
}
4、根据我们的需求是查询数据库中所有书籍,需要的是数组,新建json数组类ListObject
package com.json;
import java.util.List;
public class ListObject extends AbstractJsonObject {
private List<?> items;
public List<?> getItems() {
return items;
}
public void setItems(List<?> items) {
this.items = items;
}
}
5、新建一个工具类JackJsonUtils生成和解析json数据
package com.json;
import java.io.IOException;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
/*
* 生成json和解析json
* */
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 (JsonParseException e) {
e.printStackTrace();
} catch (JsonMappingException e) {
e.printStackTrace();
} catch (IOException 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 (JsonProcessingException e) {
e.printStackTrace();
}
return null;
}
}