前言

最近在学Android开发的时候涉及到了对MySQL数据库的操作,遇到过许许多多的坑。特此写下自己的实现方法,或许这不是最佳的做法,但对于初学者来说足够了。
由于Android在4.x开始就不允许在主线程即UI线程进行耗时操作,比如网络操作。因此连接数据库就需要另外启动一个线程。这又涉及到主线程与子线程间的通信问题。
我是使用Handler解决的通信问题,更多Handler的使用方法请自行Google,但以后可能会写一篇相关文章。

代码

MySQL 连接代码

import java.sql.*;

public class MySql {

    private static final String DBDRIVER = "com.mysql.jdbc.Driver";
    private static final String DBURL = "jdbc:mysql://locahost:3306/Android?useUnicode=true&characterEncoding=utf-8";
    private static final String DBUSER = "user";
    private static final String DBPASSWORD = "password";
    Statement sql;
    Connection con;
    ResultSet rs;

    public MySql() {

        try{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }catch(Exception e){
            System.out.println("MySql MySql() 数据库驱动连接 产生错误:" + e);
        }
        try{
            con = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
        }
        catch (Exception e) {
            System.out.println("MySql LinkMySql() 产生错误:" + e);
        }
    }
    public RS_bean select(String sql) {
        RS_bean rs_bean = new RS_bean();
        try {
            this.sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = this.sql.executeQuery(sql);

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            String []columnName = new String[columnCount];

            for(int i=0;i<columnName.length;i++) {
                columnName[i] = metaData.getColumnName(i+1);
            }
            rs_bean.setColumnName(columnName);
            rs.last();
            int rowNumber = rs.getRow();
            String [][] tableRecord = rs_bean.getTableRecord();
            tableRecord = new String[rowNumber][columnCount];
            rs.beforeFirst();
            int i = 0;
            while(rs.next()) {
                for(int k=0;k<columnCount;k++) {
                    tableRecord[i][k] = rs.getString(k+1);
                }
                i++;
            }
            rs_bean.setTableRecord(tableRecord);
            con.close();
        } catch (Exception e) {
            System.out.println("MySql select() 产生错误:" + e);
        }

        return rs_bean;
    }
    public boolean updata(String sql) {
        try {
            this.sql = con.createStatement();

            this.sql.executeUpdate(sql);

            return true;
        } catch (SQLException e) {
            System.out.println("MySql updata() 产生错误:" + e);
            return false;
        }
    }
    public void colsecon() {
        try {
            con.close();
        } catch (SQLException e) {
            System.out.println("MySql closecon() 关闭数据库连接时产生错误:" + e);
        }
    }
}

存储查询结果的JavaBean

public class RS_bean {
    String []columnName;
    String [][]tableRecord = null;
    public RS_bean() {
        tableRecord = new String[1][1];
        columnName = new String[1];
    }
    public String[] getColumnName() {
        return columnName;
    }
    public void setColumnName(String[] columnName) {
        this.columnName = columnName;
    }
    public String[][] getTableRecord() {
        return tableRecord;
    }
    public void setTableRecord(String[][] tableRecord) {
        this.tableRecord = tableRecord;
    }
}

创建连接数据库线程

public class test_Activity extends AppCompatActivity {

    private TextView tv_test;

    static Statement sql;
    static Connection con;
    static ResultSet rs;

    //对连接数据库线程返回的消息进行处理
    private Handler mHandler = new Handler(){
        @Override
        public void handleMessage(Message msg) {
            super.handleMessage(msg);
            //获得刚才发送的Message对象,然后在这里进行UI操作
            tv_test = findViewById(R.id.tv_test);
            RS_bean rs = (RS_bean) msg.obj;
            tv_test.setText(rs.getTableRecord()[0][0]+"  ?");
        }
    };

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_test_);

        //必须在线程操作数据库
        try {
            new Thread(new Runnable(){
                public void run(){

                    //查询
                    MySql con = new MySql();
                    RS_bean rs_bean = con.select("select * from user_info");
                    //将查询结果返回给主线程
                    Message message = new Message();
                    message.what = 1;
                    message.obj = rs_bean;
                    mHandler.sendMessage(message);

                    //插入、更新、删除等操作,变sql语句就行
                    String user_name = "test";
                    String user_passwd = "test";
                    String user_email = "test";
                    con.updata("INSERT INTO user_info ( user_name,user_passwd,user_email)" +
                            "VALUES" +
                            "( "
                            +"'"+user_name+"'"
                            +"'"+user_passwd+"'"
                            +"'"+user_email+"'"
                            + " )");

                    //关闭连接,最好关闭
                    con.colsecon();
                }
            }).start();
            Toast.makeText(this,"正常",Toast.LENGTH_SHORT).show();
        } catch (Exception e) {
            Toast.makeText(this,"异常",Toast.LENGTH_SHORT).show();
        }
    }
}

Android Activity

    <TextView
        android:id="@+id/tv_test"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="TextView" />

导入jar文件

将数据库驱动导入安卓libs文件夹下,具体方法就不介绍了。jar和在Java连接数据库的一样。

运行截图

总结

本文运用了Handler的机制实现在子线程获取数据库数据后返回数据到主线程的功能。算是解决了在主线程不能进行网络操作的问题。

Last modification:December 28th, 2019 at 11:08 pm
If you think my article is useful to you, please feel free to appreciate