微信小程序+Servlet后台开发（三）
前面两节已经实现了微信小程序和Servlet的通信以及Servlet与MySQL数据的连接，这一节主要记录用JDBC实现的对数据库操作的主要函数。主要是库和表的增删查改。其实说是JDBC操作，其实主要是SQL语句，只不过通过JDBC的桥梁传递给MySQL罢了。
之前也已经实现了阿里云的短信验证码的通知了，接下来就详细看一下JDBC对SQL的操作。
SQL语言简介
SQL语言由命令、子句、运算和集合函数等构成。它包含三种：
- 数据定义语言DDL（用来建立及定义数据表、字段以及索引等数据库结构）包含的命令有CREATE、DROP、ALTER。
- 数据操纵语言DML（用来提供数据的查询、排序以及筛选数据等功能）包含的命令有SELECT、INSERT、UPDATE、DELETE。
- 数据控制语言DCL语句包括GRANT、REVOKE。
数据库层面
增 — 创建新的数据表
基本语法
CREATE语句属于DDL，是一个数据库的七点。在SQL规定中，完整的CREATE语法比较复杂。在实际操作中，只需要记住精简格式即可。
CREATE TABLE <表名> <列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]
[,<表级完整性约束条件>] ;
以上语句中的<表名>就是所要定义的基本表的名字，它可以由一个或者多个属性组成。建表的同时还可以定义与该表有关的约束条件，即表中某些属性在记录数据时应满足的条件，如该属性是否唯一，是否为空等等。这些约束性条件自动存储在系统的数据字典里，由系统对数据库数据的合法性进行检查。
创建表这个功能的实际用途在于用户注册的时候，后台检测到新用户注册时为其建一张表用于储存快递信息。
Demo
小程序
wx.request({
url: 'http://localhost:8080/Demo/Demo',
data: {
type: "logon", //请求类型
phone: phone //值
},
header: {
'content-type': 'application/json' // 默认值
},
success(res) {
wx.navigateBack({
dleta: 1
})
wx.showToast({
title: '注册成功',
icon: 'success',
duration: 2000
})
}
})
小程序网络请求时要携带上请求类型，即data中的type参数，供服务器处理。
Servlet
Servlet得doGet()函数在得到小程序的网络请求后，获取小程序的请求类型，通过case语句判断对应的服务。
//根据请求类型不同进行响应
String requesTpye = request.getParameter("type");
switch (requesTpye){
case REQUESTYPE_LOGON:
createTable(request, stmt);
break;
}
以下这是建表函数：
//为用户建表，储存快递信息
//以手机号码作为唯一凭证
public void createTable(HttpServletRequest request, Statement stmt) throws SQLException {
String userPhone = request.getParameter("phone");
String sql = "CREATE TABLE user"+userPhone+"(" +
"CODE CHAR(12) NOT NULL," +
"FLAG CHAR(1)," +
"STATE CHAR(1)," +
"DISTRICT CHAR(6)," +
"SENDPHONE CHAR(11)," +
"SENDADDR VARCHAR(50)," +
"RCVPHONE CHAR(11)," +
"RCVADDR VARCHAR(50)" +
")";
stmt.execute(sql);
}
要注意MySQL的表名不能是纯数字，MySQL的语法拿不准的话可以在MySQL Command Line里面自己尝试一下。
运行一遍可以看到MySQL已经根据小程序的指示创建了一个user+手机号格式的数据表：
查 — 查询特定数据表是否存在
类原型
主要参考：
主要是利用DatabaseMetaData.getTables(...)这个方法实现的,但是每一种数据库还存在一些差异，具体见下面详解。
ResultSet DatabaseMetaData.getTables(String catalog,
String schemaPattern,
String tableNamePattern,
String types[]) throws SQLException;
catalog- 数据库目录名称，可设为null，（具体JDBC驱动的实现不一样在MySQL中指数据库名）schemaPattern- 方案名称的样式，可设为null，（ 具体JDBC驱动的实现不一样， 在Oracle中指用户名）tableNamePattern- 表名称的样式，可以包含匹配符比如："TEST%"types- 要包括的表类型组成的列表，可设为null，表示所有的。types的常量值为："TABLE","VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
各种数据库系统对Catalog和Schema的支持和实现方式是不一样的，针对具体问题需要参考具体的产品说明书，比较简单而常用的实现方式是使用数据库名作为Catalog名，使用用户名作为Schema名。
Demo
以下一段Demo主要实现的是对新用户的检测，如果该用户没有建表，则该用户是新用户。并将新用户信息添加至用户信息表里面。
//查看是否为新用户，
//如果是则返回true，否则返回false
public boolean isNewuser(Connection conn, HttpServletRequest request) throws SQLException {
// 数据表名称
String tableName = "user" + request.getParameter(REQUESTINFO_PHONE);
// 获得数据库元数据
DatabaseMetaData meta = conn.getMetaData();
// 第一个参数catalog在MySQL中对应数据库名：userinfo
// 这里不要弄混，数据库名userinfo和其中某张数据表名称一致
ResultSet rsTables = meta.getTables(ARCHIVE_NAME, null, tableName,
new String[]{"TABLE"});
boolean flag = rsTables.next();
rsTables.close();
return flag;
}
ResultSet表示select语句的查询结果集。ResultSet对象具有指向其当前数据行的指针。最初，指针被置于第一行记录之前，通过next()方法可以将指针移动到下一行记录。next()方法在ResultSet对象没有一行记录时返回false,因此可以在while循环中使用它来遍历结果集，也可以利用该方法判断结果集是否为空。
数据表层面
增 — 插入新的数据
原型
SQL的插入语句为INSERT，它有两种语法如下：
- INSERT INTO <表名>[(<字段名1> [,<字段名2>, ...])]
VALUES (<常量1> [,<常量2>, ...])
- INSERT INTO <表名>[(<字段名1> [,<字段名2>, ...])]
子查询
Demo
下面Demo是向userinfo表中插入数据，该表中只有一列为PHONE CHAR(11)
// 如果是新用户，将其手机号码添加至userinfo表中
public void addNewuser2Userinfo(HttpServletRequest request, Statement stmt) throws SQLException {
String phone = request.getParameter(REQUESTINFO_PHONE);
String sql = "INSERT INTO "+ ARCHIVE_NAME +" " +
"VALUES (" + phone + ")";
stmt.execute(sql);
}
查 — 根据主键查询数据
主键
主键 (Primary Key) 中的每一笔资料都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行资料。主键可以理解为每行数据的标识符，其值必须是唯一的。
在我的应用情境中，将用户的手机号码设置为数据表的主键。在创建用户信息表时，在相应的属性后面加上primary key就可以了。例如：
CREATE TABLE userinfo(
phone CHAR(11) PRIMARY KEY,
vcode CHAR(6));
查询语句
SQL中的查询语句为SELECT，其主要用法如下所示：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]
对于简单的查询
SELECT columna columnb FROM mytable;
如果想实现通过主键进行查询，则需要用到WHERE
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
删 — 删除表中数据
原型
DELETE FROM 表名称 WHERE 列名称 = 值
Demo
当用户重新请求短信验证码时，原存储中的验证码信息需要被替换
// 如果之前有验证码，则替换掉之前的
// 否则将生成的验证码存储到表格中，
// 触发用户登陆时间时，将之与携带的验证码进行比较
private void addORplcinfo2userinfo(String phone, Statement stmt, String vcode, Connection conn) throws SQLException {
String sql = "SELECT * FROM " + ARCHIVE_NAME + " WHERE phone=" + phone;
ResultSet rs = conn.prepareStatement(sql).executeQuery();
if(rs.next()) { // 之前已经有验证码信息了
sql = "DELETE FROM " + ARCHIVE_NAME + " WHERE phone=" + phone;
stmt.execute(sql);
}
sql = "INSERT INTO "+ ARCHIVE_NAME +" " +
"VALUES (" + phone + "," + vcode +")";
System.out.println(sql);
stmt.execute(sql);
}
4727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
