Android通过JDBC连接MySql数据库

Android通过JDBC连接MySql数据库

android中联网访问权限的添加

• AndroidManifest文件中要添加权限

...
    </application>
    <uses-permission android:name="android.permission.INTERNET" />
</manifest>

关闭Windows防火墙

• （点击控制面板, 进入之后在控制面板中找到Windows防火墙, 点进去之后全部选择关闭就行了）

找到自己的Ip地址

---

之所以要找到自己的Ip地址是因为App实在虚拟机中运行的，所以localhost指虚拟机，并不是当前电脑，所以需要当前电脑的ip地址链接MYSQL数据库。

---

* 右击网络 -> 属性, 进入之后看到这个画面

• 点击红线那里进入到下图
  

• 之后点击详细信息就可以看到IP地址了
  

数据库增删改查

数据库的增删改查我给放到了一个.class文件中，用来方便以后其他的程序调用

//链接数据库的函数
public static Connection openConnection(String url, String user,
            String password) {
        Connection conn = null;
        try {
            final String DRIVER_NAME = "com.mysql.jdbc.Driver";
            //动态加载MySQL驱动
            Class.forName(DRIVER_NAME);
            //获取MySql数据库的连接对象
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            conn = null;
        } catch (SQLException e) {
            conn = null;
        }

        return conn;
    }
//执行查询，插入，更新动作的函数，此处返回了一个ArrayList类型的引用对象，方便app调用数据
    public static ArrayList<Detail> query(Connection conn, String sql) {
        ArrayList<Detail> detailArray = new ArrayList<Detail>();

        if (conn == null) {
            return null;
        }

        Statement statement = null;
        ResultSet result = null;

        try {
            //从数据库连接对象中获取一个用来传递SQL语句的对象
            statement = conn.createStatement();
            result = statement.executeQuery(sql);
            if (result != null && result.first()) {
                while (!result.isAfterLast()) {
                    //getXXX("columnIndex")方法用来获取数据表中该列的数据并返回
                    int idColumnIndex = result.getInt("id");
                    String titleColumnIndex = result.getString("title");
                    String contentColumnIndex = result.getString("Content");
                    //此处用来检测数据是否被读出，可以删除掉
                    System.out.println(titleColumnIndex+"\n");

                    detailArray.add(new Detail(titleColumnIndex, contentColumnIndex, idColumnIndex));
                    result.next();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (result != null) {
                    result.close();
                    result = null;
                }
                if (statement != null) {
                    statement.close();
                    statement = null;
                }

            } catch (SQLException sqle) {

            }
        }
        return detailArray;
    }
//数据库查询是否存在目标数据的操作函数
    public static boolean execSQL(Connection conn, String sql) {
        boolean execResult = false;
        if (conn == null) {
            return execResult;
        }

        Statement statement = null;

        try {
            statement = conn.createStatement();
            if (statement != null) {
                execResult = statement.execute(sql);
            }
        } catch (SQLException e) {
            execResult = false;
        }

        return execResult;
    }

---

开发环境：eclipse + MySQL

开发语言：java

用了一小的阅读app来解释如何连接MySql数据库

1. 首先创建一个Android Application Project

• android_main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >

    <ListView 
        android:id="@+id/list_items"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:divider="#ffffff"
        android:dividerHeight="1dip"
        ></ListView>

</RelativeLayout>

我为ListView自动一个Item的格式

• Item.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content" 
    android:id="@+id/RelativeLayout"
    android:paddingBottom="4dip"
    android:paddingLeft="12dip"
    android:paddingRight="12dip"
    >

    <ImageView 
        android:id="@+id/image"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentRight="true"
        android:paddingLeft="6dip"
        android:paddingTop="6dip"/>

    <TextView 
        android:id="@+id/username"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:paddingTop="6dip"
        android:textColor="#000"
        android:textSize="18sp"
        />

    <TextView 
        android:id="@+id/birthday"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/username"
        android:maxLines="2"
        android:paddingRight="20dip"
        android:textColor="#000"
        />

</RelativeLayout>

之后自定义一个设配器，用来为android_main.xml中的Listview设配Item.xml中的样式

• MyAdapter.class

public class MyAdapater extends ArrayAdapter<Detail>{

    private int reasourceID;

    Context context;

    public MyAdapater(Context context, int textViewReasourceId, Detail[] objects) {
        super(context, textViewReasourceId, objects);
        reasourceID = textViewReasourceId;
        this.context = context;
    }


    @Override
    public View getView(int position, View convertView, ViewGroup parent) { 
        Detail user = (Detail)getItem(position);

        View view;

        view = LayoutInflater.from(this.getContext()).inflate(reasourceID, null);
        //获取Item.xml页面中的控件
        TextView userName = (TextView)view.findViewById(R.id.username);
        TextView userBirthdy = (TextView)view.findViewById(R.id.birthday);
        ImageView userImageView = (ImageView)view.findViewById(R.id.image);
        //为控件赋值
        userName.setText(user.getTitle());
        userBirthdy.setText(user.getContent().length()>15?user.getContent().substring(0, 15)+"...":user.getContent());
        userImageView.setImageResource(R.drawable.ic_launcher);

        return view;
    }
}

之后为listview设置item样式 ( MainActivity.class )

ListView listView = (ListView)this.findViewById(R.id.list_items);
MyAdapater adapter = new MyAdapater(this, R.layout.item, array);
listView.setAdapter(adapter);

为listview设置点击跳转事件 ( MainActivity.class )

listView.setOnItemClickListener(new OnItemClickListener() {

    @Override
    public void onItemClick(AdapterView<?> arg0, View arg1, int position, long id) {
//      View就是条目视图
//      position就是ListView的条目，即第几条
//      id 就是Adapter返回的ID
        Intent intent = new Intent();
        //利用Intent在两个页面之间传值
        String str = id+"";
        intent.putExtra("str", str);

        intent.setClass(MainActivity.this, SecondActivity.class);
        MainActivity.this.startActivity(intent);
    }
});

从MySql数据库中获取标题

在Android中连接数据库不能在主线程中使用，所以我新建了一个线程从中获取数据

static class MyThread extends Thread{
        @Override
        public void run() {
            conn = Util.openConnection(serverDB, serverUser, serverPw);

            String sqlStr = "select * from cp";

            temp = Util.query(conn, sqlStr);
            array = new Detail[temp.size()];
            temp.toArray(array);
            Flag = 1;
        }
    }

• MainActivity.class

package com.example.listviewdemo1;

import android.os.Bundle;
import java.sql.Connection;
import java.util.ArrayList;

import android.app.Activity;
import android.content.Intent;
import android.view.Menu;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.AdapterView.OnItemClickListener;


public class MainActivity extends Activity {
    private static final String serverDB = "jdbc:mysql://117.87.93.205:3306/programming_c";
    private static final String serverUser = "zetile";
    private static final String serverPw = "123456";
    private static Connection conn;
    private static int Flag = 0;
    public static Detail[] array;
    private static ArrayList<Detail> temp;

    static class MyThread extends Thread{
        @Override
        public void run() {
            conn = Util.openConnection(serverDB, serverUser, serverPw);

            String sqlStr = "select * from cp";

            temp = Util.query(conn, sqlStr);
            array = new Detail[temp.size()];
            temp.toArray(array);
            Flag = 1;
        }
    }

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

        MyThread myThread = new MyThread();
        myThread.start();
        //设置标志位让主线程在新建的线程运行之后在运行
        while(Flag != 1){

        }

        Flag =  0;

        ListView listView = (ListView)this.findViewById(R.id.list_items);
        MyAdapater adapter = new MyAdapater(this, R.layout.item, array);
        listView.setAdapter(adapter);

        listView.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> arg0, View arg1, int position, long id) {
//              View就是条目视图
//              position就是ListView的条目，即第几条
//              id 就是Adapter返回的ID
                Intent intent = new Intent();
                //利用Intent在两个页面之间传值
                String str = id+"";
                intent.putExtra("str", str);

                intent.setClass(MainActivity.this, SecondActivity.class);
                MainActivity.this.startActivity(intent);
            }
        });
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

}

运行截图