Android使用SwipeRefreshLayout实现下拉刷新_android swiperefreshlayout github

Android连接mysql数据库，实现下拉刷新（简易新闻 三）

关于之前的功能实现可以从制作简易新闻App导航篇中查看

首先看一下效果：

采用swipeRefreshLayout，mysql数据库来实现下拉刷新并通过Handler发送消息加载本地数据到界面上，本篇实现的功能有：

1. 建立新闻数据库表，下拉刷新
2. 在聚合数据访问了100次每天免费次数后，加载本地数据内容到主界面
3. 在点击子item新闻时，将新闻json数据插入到new_info表中

关于新闻json的解析与展示和界面搭建请看上一篇：

获取解析聚合新闻数据，并显示到主界面上（简易新闻 二）

项目结构：


使用版本

数据库 MySQL 5.7
编辑器 Android Studio 3.2
MySQL 驱动 5.1.47

实现过程第一步，导入jar包

下载MySQL Connector/J » 5.1.47版本，如图所示：

将下载好的jia包复制到项目的libs目录下，然后点击jar包，在选项中选择Add as Library,当build完成后，表示驱动已经安装完成。

第二步，建立MySql数据库表

1. 下载MySql数据库
2.下载安装HeidiSQL
**3.**建表
打开HeidiSQL，如图所示你可以设置自己的用户名与密码：

打开后选择新建库，这里我新建一个user数据库，然后新建一个news_info的表，字段属性设置如下：

，到这里关于外部数据库的部署已经解决。

注意，如果数据库不是部署在云服务器上而是装在本地电脑上时，在第三步中连接数据库帮助类中的url IP地址需要是你主机网络v4地址，且电脑和手机处于同一个网络中，每次变化网络都需要调整一下

查看个人ip地址，在电脑中输入cmd进入命令行中，输入ipconfig，查看ip地址：

第三步新建数据库连接帮助类DBOpenHelper类

在java/com.example,frametest/下新建一个tools文件夹，在文件夹内新建一个DBOpenHelper类

package com.example.frametest.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBOpenHelper {
    private static String driver = "com.mysql.jdbc.Driver"; //MySql驱动
    private static String url = "jdbc:mysql://192.168.1.121:3306/user?characterEncoding=utf-8"; //MySql数据库连接url
    private static String user = "liu"; //用户名
    private static String paswword = "031122"; //密码
    /*连接数据库*/
    public static Connection getConn(){
    Connection conn = null;
        try {
            Class.forName(driver);
            conn = (Connection) DriverManager.getConnection(url,user,paswword); //获取连接
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  conn;

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

第四步修改NewsFragment.class类

在下拉刷新的监听事件中新建一个线程，在Thread中，连接数据库查询new_info表，每次下拉刷新时进行page自增，然后查询进行翻页每页10条新闻数据，这里还解决了访问次数上限的数据问题。

package com.example.frametest;

import android.annotation.SuppressLint;
import android.content.Intent;
import android.os.AsyncTask;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.support.design.widget.FloatingActionButton;
import android.support.v4.app.Fragment;
import android.support.v4.widget.SwipeRefreshLayout;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.ListView;
import com.example.frametest.json.NewsBean;
import com.example.frametest.TabAdapter.MyTabAdapter;
import com.example.frametest.tools.DBOpenHelper;
import com.google.gson.Gson;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.ProtocolException;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsFragment extends Fragment {
    private FloatingActionButton fab;
    private ListView listView;
    private SwipeRefreshLayout swipeRefreshLayout;
    private List<NewsBean.ResultBean.DataBean> list;
    private static final int UPNEWS_INSERT = 0;
    private int page =0,row =10;
    private static final int SELECT_REFLSH = 1;
    @SuppressLint("HandlerLeak")
    private Handler newsHandler = new Handler(){
        @Override
        public void handleMessage(Message msg) {
            String uniquekey,title,date, category,author_name,url,thumbnail_pic_s,thumbnail_pic_s02,thumbnail_pic_s03;
            switch (msg.what){
                case UPNEWS_INSERT:
                    list = ((NewsBean) msg.obj).getResult().getData();
                    MyTabAdapter adapter = new MyTabAdapter(getActivity(),list);
                    listView.setAdapter(adapter);
                    adapter.notifyDataSetChanged();
                    break;
                case SELECT_REFLSH:
                    list =((NewsBean) msg.obj).getResult().getData();
                    MyTabAdapter myTabAdapter = new MyTabAdapter(getActivity(),list);
                    listView.setAdapter(myTabAdapter);
                    myTabAdapter.notifyDataSetChanged();
                    break;
                    default:
            }
        }
    };
    @Override
    public void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

    }

    @Nullable
    @Override
    public View onCreateView(@NonNull LayoutInflater inflater, @Nullable ViewGroup container, @Nullable Bundle savedInstanceState) {
        View view = inflater.inflate(R.layout.list_item,container,false);
        listView = (ListView) view.findViewById(R.id.listView);
        fab = (FloatingActionButton) view.findViewById(R.id.fab);
        swipeRefreshLayout = (SwipeRefreshLayout) view.findViewById(R.id.swipe_refresh);
        return view;
    }

    @SuppressLint("HandlerLeak")
    @Override
    public void onActivityCreated(@Nullable Bundle savedInstanceState) {
        super.onActivityCreated(savedInstanceState);
        onAttach(getActivity());
        //获取传递的值
        Bundle bundle = getArguments();
        final String data = bundle.getString("name","top");
        //置顶功能
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                listView.smoothScrollToPosition(0);
            }
        });
        //下拉刷新
        swipeRefreshLayout.setColorSchemeResources(R.color.colorRed);
        swipeRefreshLayout.setOnRefreshListener(new SwipeRefreshLayout.OnRefreshListener() {
            @Override
            public void onRefresh() {
                page++;
                new Handler().postDelayed(new Runnable() {
                    @Override
                    public void run() {
                        swipeRefreshLayout.setRefreshing(false);
                      // 下一步实现从数据库中读取数据刷新到listview适配器中
                        new Thread(new Runnable() {
                            @Override
                            public void run() {
                               NewsBean newsBean = new NewsBean();
                               List<NewsBean.ResultBean.DataBean> dataBeanList = new ArrayList<>();
                                Connection conn = null;
                                conn = (Connection) DBOpenHelper.getConn();
                                int pages = (page -1)*row;
                                String sql = "select *from news_info limit "+pages+","+row;
                                PreparedStatement pst;
                                try {
                                    pst = (PreparedStatement) conn.prepareStatement(sql);
                                    ResultSet rs = pst.executeQuery();
                                    while (rs.next()){
                                        NewsBean.ResultBean.DataBean dataBean = new NewsBean.ResultBean.DataBean();
                                        dataBean.setUniquekey(rs.getString(1));
                                        dataBean.setTitle(rs.getString(2));
                                        dataBean.setDate(rs.getString(3));
                                        dataBean.setCategory(rs.getString(4));
                                        dataBean.setAuthor_name(rs.getString(5));
                                        dataBean.setUrl(rs.getString(6));
                                        dataBean.setThumbnail_pic_s(rs.getString(7));
                                        dataBean.setThumbnail_pic_s02(rs.getString(8));
                                        dataBean.setThumbnail_pic_s03(rs.getString(9));
                                        dataBeanList.add(dataBean);
                                    }
                                    newsBean.setResult(new NewsBean.ResultBean());
                                    newsBean.getResult().setData(dataBeanList);
                                    pst.close();
                                    conn.close();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                                Message msg = newsHandler.obtainMessage();
                                msg.what = SELECT_REFLSH;
                                msg.obj = newsBean;
                                newsHandler.sendMessage(msg);
                            }
                        }).start();
                    }
                },1000);
            }
        });
        //异步加载数据
        getDataFromNet(data);
        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                //获取点击条目的路径，传值显示webview页面
                String url = list.get(position).getUrl();
                String uniquekey = list.get(position).getUniquekey();
                final NewsBean.ResultBean.DataBean dataBean = (NewsBean.ResultBean.DataBean) list.get(position);
                //这里是在listview子item的点击事件中添加一个插入新闻的具体json数据到news_info表中
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        Connection conn = null;
                        conn = (Connection) DBOpenHelper.getConn();
                        System.out.print(conn);
                        String sql = "insert into news_info(uniquekey,title,date,category,author_name,url,thumbnail_pic_s,thumbnail_pic_s02,thumbnail_pic_s03) values(?,?,?,?,?,?,?,?,?)";
                        int i = 0;
                        PreparedStatement pstmt;
                        try {
                            pstmt = (PreparedStatement) conn.prepareStatement(sql);
                            pstmt.setString(1,dataBean.getUniquekey());
                            pstmt.setString(2,dataBean.getTitle());
                            pstmt.setString(3,dataBean.getDate());
                            pstmt.setString(4,dataBean.getCategory());
                            pstmt.setString(5,dataBean.getAuthor_name());
                            pstmt.setString(6,dataBean.getUrl());
                            pstmt.setString(7,dataBean.getThumbnail_pic_s());
                            pstmt.setString(8,dataBean.getThumbnail_pic_s02());
                            pstmt.setString(9,dataBean.getThumbnail_pic_s03());
                            i = pstmt.executeUpdate();
                            pstmt.close();
                            conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }).start();
                Intent intent = new Intent(getActivity(),WebActivity.class);
                intent.putExtra("url",url);
                startActivity(intent);

            }
        });
    }
    private void getDataFromNet(final String data){
        @SuppressLint("StaticFieldLeak") AsyncTask<Void,Void,String> task = new AsyncTask<Void, Void, String>() {
            @Override
            protected String doInBackground(Void... params) {
                String path = "http://v.juhe.cn/toutiao/index?type="+data+"&key=547ee75ef186fc55a8f015e38dcfdb9a";
                URL url = null;
                try {
                    url = new URL(path);
                    HttpURLConnection connection = (HttpURLConnection) url.openConnection();
                    connection.setRequestMethod("GET");
                    connection.setReadTimeout(5000);
                    connection.setConnectTimeout(5000);

                    int responseCode = connection.getResponseCode();
                    if (responseCode == 200){
                        InputStream inputStream = connection.getInputStream();
                        String json = streamToString(inputStream,"utf-8");
                        return json;
                    } else {
                        System.out.println(responseCode);
                        return "已达到今日访问次数上限";
                    }

                } catch (MalformedURLException e) {
                    e.printStackTrace();
                } catch (ProtocolException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                return "";
            }
            protected void onPostExecute(final String result){
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        NewsBean newsBean = new Gson().fromJson(result,NewsBean.class);
                        System.out.println(newsBean.getError_code());
                        //打印输出看一下当达到每日访问次数上限时出现的错误code，发现为10012
                        if ("10012".equals(""+newsBean.getError_code())){
                            //这里通过判断error_code来决定是否查询new_info表中的数据来填充到listview的适配器中
                            List<NewsBean.ResultBean.DataBean> listDataBean = new ArrayList<>();
                            Connection conn = null;
                            conn = (Connection) DBOpenHelper.getConn();
                            String sql = "select * from news_info ";
                            PreparedStatement pstmt;
                            try {
                                pstmt = (PreparedStatement) conn.prepareStatement(sql);
                                ResultSet rs = pstmt.executeQuery();
                                while (rs.next()){
                                    NewsBean.ResultBean.DataBean dataBean = new NewsBean.ResultBean.DataBean();
                                    dataBean.setUniquekey(rs.getString(1));
                                    dataBean.setTitle(rs.getString(2));
                                    dataBean.setDate(rs.getString(3));
                                    dataBean.setCategory(rs.getString(4));
                                    dataBean.setAuthor_name(rs.getString(5));
                                    dataBean.setUrl(rs.getString(6));
                                    dataBean.setThumbnail_pic_s(rs.getString(7));
                                    dataBean.setThumbnail_pic_s02(rs.getString(8));
                                    dataBean.setThumbnail_pic_s03(rs.getString(9));
                                    listDataBean.add(dataBean);


                                }
                                newsBean.setResult(new NewsBean.ResultBean());
                                newsBean.getResult().setData(listDataBean);
                                pstmt.close();
                                conn.close();
                                System.out.println(newsBean.getResult().getData());
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                        Message msg=newsHandler.obtainMessage();
                        msg.what=UPNEWS_INSERT;
                        msg.obj = newsBean;
                        newsHandler.sendMessage(msg);
                    }
                }).start();
            }
            @Override
            protected void onProgressUpdate(Void... values) {
                super.onProgressUpdate(values);
            }
        };
        task.execute();
    }

    private String streamToString(InputStream inputStream, String charset){
        try {
            InputStreamReader inputStreamReader = new InputStreamReader(inputStream,charset);
            BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
            String s = null;
            StringBuilder builder = new StringBuilder();
            while ((s = bufferedReader.readLine()) != null){
                builder.append(s);
            }
            bufferedReader.close();
            return builder.toString();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;

    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305

到此，本篇就结束了，下篇将会实现每个新闻的点击事件内容，通过WebView加载网页。

App实现WebView加载网页及网页美化（简易新闻 四）

欢迎各位一起讨论。

项目源码GitHub地址为：https://github.com/ziruiliu1/MyBlog