【B4J】怎么用VBA/VB6写一个WebApi服务器端程序之MySQL数据库连接（二）

GuessMyNumber
ResetMyNumber
WSGuessMyNumber

srvr.StaticFilesFolder = File.Combine(File.DirApp, "www")
srvr.AddHandler("/guessmynumber/guess", "GuessMyNumber", False)
srvr.AddHandler("/guessmynumber/reset", "ResetMyNumber", False)
srvr.AddWebSocket("/guessmynumber_ws/ws", "WSGuessMyNumber")

https://mvnrepository.com/artifact/com.mysql/mysql-connector-j/9.4.0

#AdditionalJar: mysql-connector-j-9.4.0.jar

'' 模块全局变量
Sub Class_Globals
	Private driverClass As String = "com.mysql.cj.jdbc.Driver" '' mysql-connector-j-9.4.0.jar数据驱动类
	Private host As String = "159.75.75.206" '' 数据库IP地址
	Private port As Int=3306 '' 数据库端口
	Private dataBase As String="db_b4j" '' 数据库名称
	Private userName As String="b4j" '' 数据库用户名
	Private password As String="admin@123" '' 数据库密码
	Private mysql As SQL '' 数据库对象
End Sub

' 初始化数据库对象
Public Sub Initialize
	Dim redisUrl As String=$"jdbc:mysql://${host}:${port}/${dataBase}?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true"$
	mysql.Initialize2(driverClass,redisUrl,userName,password)
	If mysql.IsInitialized Then
		ToolHelper.WirteLog("MySQL 初始化成功！")
		Try
			Dim rs As ResultSet
			rs = mysql.ExecQuery("SELECT SYSDATE()")
			If rs.NextRow Then
				ToolHelper.WirteLog("当前MySQL数据库时间：" & rs.GetString2(0))
			End If
		Catch
			ToolHelper.WirteLog("MySQL链接异常:" & LastException.Message)
		End Try
	End If
End Sub

' 关闭数据库连接
Public Sub Close
	If mysql.IsInitialized Then
		mysql.Close
		ToolHelper.WirteLog("MySQL连接已关闭")
End If
End Sub

' 执行查询并返回单一结果
Public Sub ExecQuerySingleResult2(sql As String,params() As Object) As String
	If Not(mysql.IsInitialized) Then Return Null
	Try
		Return mysql.ExecQuerySingleResult2(sql,params)
	Catch
		ToolHelper.WirteLog("执行查询异常: " & LastException.Message & " SQL: " & sql)
		Return Null
	End Try
End Sub

' 执行查询并返回结果集
Public Sub ExecuteQueryByList2(sql As String,params() As Object) As List
	If Not(mysql.IsInitialized) Then Return Null
	Try
		Dim rs As ResultSet= mysql.ExecQuery2(sql,params)
''---------
		Dim lst As List
		lst.Initialize
''---------
		Do While rs.NextRow
			Dim mp As Map
			mp.Initialize
			Dim colCt As Int=rs.ColumnCount
''--------
			Dim i As Int
For i =0 To colCt-1
				Dim key As String=rs.GetColumnName(i)
				Dim value As String=rs.GetString(key)
				mp.Put(key,value)
			Next
''--------
			lst.Add(mp)
		Loop
''--------
Return lst
	Catch
		ToolHelper.WirteLog("执行查询异常: " & LastException.Message & " SQL: " & sql)
Return Null
End Try
End Sub

' 执行带参数的查询（防止SQL注入）
Public Sub ExecuteQuery2(sql As String, params() As Object) As ResultSet
	If Not(mysql.IsInitialized) Then Return Null
	Try
		Return mysql.ExecQuery2(sql, params)
	Catch
		ToolHelper.WirteLog("执行参数化查询异常: " & LastException.Message & " SQL: " & sql)
		Return Null
	End Try
End Sub

' 执行带参数的更新操作（防止SQL注入）
Public Sub ExecuteUpdate2(sql As String, params() As Object) As Boolean
	If Not(mysql.IsInitialized) Then Return False
	Try
		mysql.ExecNonQuery2(sql, params)
Return True
	Catch
		ToolHelper.WirteLog("执行参数化更新异常: " & LastException.Message & " SQL: " & sql)
Return False
End Try
End Sub

' 获取最后插入的ID
Public Sub GetLastInsertedId As Long
	If Not(mysql.IsInitialized) Then Return -1
	Try
		Dim rs As ResultSet = mysql.ExecQuery("SELECT LAST_INSERT_ID()")
		If rs.NextRow Then
			Dim id As Long = rs.GetLong2(0)
			rs.Close
			Return id
		End If
		rs.Close
		Return -1
	Catch
		ToolHelper.WirteLog("获取最后插入ID异常: " & LastException.Message)
		Return -1
	End Try
End Sub

srvr.AddHandler("/system/*","SystemHandler",False)

'' 模块全局变量
Sub Class_Globals

End Sub

Public Sub Initialize

End Sub

'' 处理方法
'' 参数：req 为前端发来的请求对象
'' 参数：resp 为当前模块需要返回给客户端的对象
Sub Handle(req As ServletRequest, resp As ServletResponse)
	Dim baseUrl As String=req.RequestURI.ToLowerCase
	Dim httpMethod As String=req.Method
''---------------返回体处理
	resp.CharacterEncoding="utf8"
	resp.ContentType="application/json"
''---------------根据请求方式和后缀路径作出对应的操作
        If httpMethod="POST" And baseUrl.EndsWith("login") Then
		AccountLogin(req,resp)
        Else If httpMethod="POST" And baseUrl.EndsWith("register") Then
		AccountRegister(req,resp)
        Else If httpMethod="POST" And baseUrl.EndsWith("forgotpassword") Then
		AccountForgotPassword(req,resp)
        Else
		resp.Write($"不支持的方法${httpMethod}"$)
        End If
End Sub


'' 用户登录
Private Sub AccountLogin(req As ServletRequest, resp As ServletResponse)
	resp.Write("用户登录返回结果")
End Sub

'' 用户注册
Private Sub AccountRegister(req As ServletRequest, resp As ServletResponse)
	resp.Write("用户注册返回结果")
End Sub

'' 忘记密码
Private Sub AccountForgotPassword(req As ServletRequest, resp As ServletResponse)
	resp.Write("忘记密码返回结果")
End Sub

select * from sys_user

https://gitee.com/wxvbee/b4-j-web-api-project