B4A 使用JDBC连接MySQL数据库

2023-03-02 13:17:06 浏览数 (1)

类库引用

  • • jdbcSQL.jar
  • • jdbcSQL.xml
  • • mysql-connector-java-5.1.49-bin.jar

关键点注意

需要把 mysql-connector-java-5.1.49-bin.jar 驱动文件,放到 B4A 安装目录下的 Libraries 内部类库文件夹下

码代码

代码语言:javascript复制
#Region  Project Attributes 
  #ApplicationLabel: JDBC连接mysql
  #VersionCode: 1
  #VersionName: 
  #SupportedOrientations: portrait
  #CanInstallToExternalStorage: False

#End Region

#Region  Activity Attributes 
  #FullScreen: False
  #IncludeTitle: True
  
#End Region

#AdditionalJar: mysql-connector-java-5.1.49-bin

Sub Process_Globals
  Private MysqlJdbc As JdbcSQL
End Sub

Sub Globals
  Private tf As Boolean=False
  

  Private WebView1 As WebView
  Private Button1 As Button
  Private Button2 As Button

End Sub

Sub Activity_Create(FirstTime As Boolean)
  Activity.LoadLayout("Layout")
  'DisableStrictMode
  ''-------
  
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub Button1_Click
  Dim driver As String = "com.mysql.jdbc.Driver"
  Dim jdbcUrl As String = "jdbc:mysql://42.193.251.120:3306/db_student"
  Dim Username As String = "student"
  Dim Password As String = "student"
  MysqlJdbc.InitializeAsync("EventMysql", driver, jdbcUrl, Username, Password)
  Wait For EventMysql_Ready (Success As Boolean)
  If Success = False Then
    ToastMessageShow("数据库连接失败",False)
    Button1.TextColor=Colors.Red
  Else
    ToastMessageShow("数据库连接成功",False)
    tf=True
    Button1.TextColor=Colors.Green
  End If
End Sub


Private Sub Button2_Click
  If tf Then
    Dim SenderFilter As Object = MysqlJdbc.ExecQueryAsync("SQL", "SELECT `学生ID`,`姓名`,`语文`,`数学`,`英语`,`总成绩` FROM v_allscore;", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As JdbcResultSet)
    If Success Then
      
      Dim htmltd As StringBuilder
      htmltd.Initialize
      
      Do While rs.NextRow
        htmltd.Append($"<tr>"$)
        htmltd.Append($"<td>${rs.GetString("学生ID")}</td>"$)
        htmltd.Append($"<td>${rs.GetString("姓名")}</td>"$)
        htmltd.Append($"<td>${rs.GetString("语文")}</td>"$)
        htmltd.Append($"<td>${rs.GetString("数学")}</td>"$)
        htmltd.Append($"<td>${rs.GetString("英语")}</td>"$)
        htmltd.Append($"<td>${rs.GetString("总成绩")}</td>"$)
        htmltd.Append($"<td><a href="${rs.GetString("学生ID")}">明细</a></td>"$)
        htmltd.Append($"</tr>"$)
      Loop
      
      WebView1.LoadHtml(htmls(htmltd.ToString))
      
      rs.Close
    Else
      Log(LastException)
    End If
  Else
    ToastMessageShow("未连接数据库",False)
  End If
  
End Sub


Private Sub htmls(td As String) As String
  Dim html As String=$"
  <html>
    <head>
      <style>
        table{
            font-size:0.8rem;
            width:100%;
            text-align:center;
            border-collapse:collapse;
            border-spacing:0;
            border-left:1px solid #888;
            border-top:1px solid #888;
            background:#efefef;
          }
        th,td{
            border-right:1px solid #888;
            border-bottom:1px solid #888;
          }
        th{
            font-weight:bold;
            background:#ccc;
          }
        a{
            text-decoration: none;
            color: green;
          }
      </style>
    </head>
    <body>
      <table>
        <tr>
          <th>学生ID</th>
          <th>姓名</th>
        <th>语文</th>
        <th>数学</th>
        <th>英语</th>
        <th>总成绩</th>
        <th>明细</th>
        </tr>
       ${td}
      </table>
    </body>
  </html>
"$
Return html
End Sub

Private Sub WebView1_OverrideUrl (Url As String) As Boolean
  Dim res As String=Url.Replace("file:///","")
  'ToastMessageShow(res,False)
  Seltss(res)
  Return True
End Sub



Private Sub Seltss(sid As Int)
  If tf Then
    Dim SenderFilter As Object = MysqlJdbc.ExecQueryAsync("SQL", $"SELECT `学生ID`,`学生名`,`生日`,`性别`,`任课教师`,`科目`,`成绩` FROM v_tsscore WHERE `学生ID` = ${sid} GROUP BY `科目`;"$, Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As JdbcResultSet)
    If Success Then
      
      Dim ressb As StringBuilder
      ressb.Initialize
      
      Dim i As Int=1,sumcj As Double
      Do While rs.NextRow
        If i=1 Then
          ressb.Append($"ID:${rs.GetString("学生ID")}${CRLF}"$)
          ressb.Append($"姓名:${rs.GetString("学生名")}${CRLF}"$)
          ressb.Append($"生日:${rs.GetString("生日")}${CRLF}"$)
          ressb.Append($"性别:${rs.GetString("性别")}${CRLF}"$)
        End If
        
        If rs.GetString("科目")="语文" Then  
          ressb.Append($"语文:${rs.GetString("成绩")}${CRLF}"$)
          sumcj=sumcj rs.GetString("成绩")
        End If
        
        If rs.GetString("科目")="数学" Then
          ressb.Append($"数学:${rs.GetString("成绩")}${CRLF}"$)
          sumcj=sumcj rs.GetString("成绩")
        End If

        If rs.GetString("科目")="英语" Then
          ressb.Append($"英语:${rs.GetString("成绩")}${CRLF}"$)
          sumcj=sumcj rs.GetString("成绩")
        End If
        
        
        i=i 1
      Loop
      
      ressb.Append($"总成绩:${sumcj}"$)
      
      rs.Close
      
      MsgboxAsync(ressb.ToString,"明细")
    Else
      Log(LastException)
    End If
  Else
    ToastMessageShow("未连接数据库",False)
  End If
  
End Sub

源码下载

代码语言:javascript复制
链接:https://pan.baidu.com/s/1dSACc_F7nVbZK8H1cUgtfg?pwd=goh3 
提取码:goh3 

0 人点赞