Dao
复制代码
package com.itheima.worldepidemic.db;
import android.util.Log;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 数据库工具类：连接数据库用、获取数据库数据用
* 相关操作数据库的方法均可写在该类
*/
public class DBUtil {
private static String driver = "com.mysql.jdbc.Driver";// MySql驱动
// private static String url = "jdbc:mysql://localhost:3306/map_designer_test_db";
private static String user = "root";// 用户名
private static String password = "123";// 密码
private static Connection getConn(String dbName){
Connection connection = null;
try{
Class.forName(driver);// 动态加载类
String ip = "49.235.116.180";// 写成本机地址，不能写成localhost，同时手机和电脑连接的网络必须是同一个
// 尝试建立到给定数据库URL的连接
connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbName,
user, password);
}catch (Exception e){
e.printStackTrace();
}
return connection;
}
public static String getInfoByName(String time,String country){
JSONArray jsonArray=new JSONArray();
// 根据数据库名称，建立连接
Connection connection = getConn("epidemic");
try {
// mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
String sql = "select * from world where date_format(Date,'%Y-%m-%d') = ? and country = ? and child !='2' order by Confirmed_num desc";
// String sql = "select * from MD_CHARGER";
if (connection != null){// connection不为null表示与数据库建立了连接
PreparedStatement ps = connection.prepareStatement(sql);
if (ps != null){
// 设置上面的sql语句中的？的值为name
ps.setString(1, time);
ps.setString(2,country);
// 执行sql查询语句并返回结果集
ResultSet rs = ps.executeQuery();
if (rs != null){
while (rs.next()){
JSONObject json=new JSONObject();
String place=rs.getString("Country");
String province=rs.getString("Province");
if(province==null||province.equals(""))
province="";
String confirmed_num=""+rs.getInt("Confirmed_num");
String yisi_num=rs.getString("Yisi_num");
String cured_num=rs.getString("Cured_num");
String dead_num=rs.getString("Dead_num");
json.put("country",place);
json.put("province", province);
json.put("confirmed_num", confirmed_num);
json.put("yisi_num", yisi_num);
json.put("cured_num", cured_num);
json.put("dead_num", dead_num);
jsonArray.add(json);
}
}else {
return null;
}
}else {
return null;
}
}else {
return null;
}
}catch (Exception e){
e.printStackTrace();
Log.e("DBUtils","异常：" + e.getMessage());
return null;
}
return jsonArray.toString();
}
}
复制代码
数据显示：
复制代码
package com.itheima.worldepidemic;
import android.annotation.SuppressLint;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.text.method.ScrollingMovementMethod;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import androidx.appcompat.app.AppCompatActivity;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.itheima.worldepidemic.db.DBUtil;
public class MainActivity extends AppCompatActivity {
private TextView date;
private TextView country;
private Button btn;
private TextView tv_data;
private String str;
@SuppressLint("HandlerLeak")
private Handler handler = new Handler( ) {
@Override
public void handleMessage(Message msg) {
tv_data=findViewById(R.id.tv_data);
tv_data.setMovementMethod(ScrollingMovementMethod.getInstance());
switch (msg.what) {
case 0x11:
str= (String) msg.obj;
tv_data.setText(str);
break;
case 0x12:
String str1 = (String) msg.obj;
tv_data.setText(str1);
break;
default:
throw new IllegalStateException("Unexpected value: " + msg.what);
}
}
};
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
date = findViewById(R.id.time);
country = findViewById(R.id.country);
country.setText("伊朗");
btn = findViewById(R.id.btn);
query( );
}
public void query() {
btn.setOnClickListener(new View.OnClickListener( ) {
@Override
public void onClick(View v) {
// 创建一个线程来连接数据库并获取数据库中对应表的数据
new Thread(new Runnable( ) {
@Override
public void run() {
// 调用数据库工具类DBUtils的getInfoByName方法获取数据库表中数据
String result = DBUtil.getInfoByName(date.getText( ).toString( ), country.getText( ).toString( ));
JSONArray jsonArray= (JSONArray) JSON.parse(result);
Message message = handler.obtainMessage( );
if (jsonArray!= null) {
String s = "";
for(int i=0;i<jsonArray.size();i++) {
JSONObject json = jsonArray.getJSONObject(i);
s = s + json.getString("country") + " " + json.getString("province") + " "
+ "确诊：" + json.getString("confirmed_num") + "疑似：" + json.getString("yisi_num") + "治愈："
+ json.getString("cured_num") + "死亡：" + json.getString("dead_num") + " ";
}
message.what = 0x12;
message.obj = s;
} else {
message.what = 0x11;
message.obj = "查询结果为空";
}
// 发消息通知主线程更新UI
handler.sendMessage(message);
}
}).start( );
}
});
}
}
复制代码
界面代码：
复制代码
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<LinearLayout
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="时间:"
android:textSize="16sp"
android:textColor="@color/colorAccent"
/>
<EditText
android:id="@+id/time"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint="例如2020-03-22"
android:inputType="text"
android:maxLines="1"
/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="地区:"
android:textSize="16sp"
android:textColor="@color/colorAccent"
/>
<EditText
android:id="@+id/country"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint="例如中国"
android:maxLines="1"
android:inputType="text"
/>
</LinearLayout>
<Button
android:id="@+id/btn"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询"
android:layout_gravity="center_horizontal"
android:textSize="16sp"
/>
<TextView
android:id="@+id/tv_data"
android:padding="10dp"
android:textSize="16sp"
android:gravity="center"
android:text="结果显示区"
android:scrollbars="vertical"
android:layout_width="match_parent"
android:layout_height="300dp" />
</LinearLayout>