Android连接Mysql数据库教程以及增删改查学习笔记（Android与数据库 （二））
安装Mysql的驱动（Android需要mysql的驱动才能与mysql连接）
Android Studio 版本 3.2 mysql驱动版本 5.0.7 （本人mysql 5.7.26 ）这两个版本最好都是5.xx，不然会出现连接不了的错误（版本要一致）
关于版本不一致会出现的错误可见：
解决Android连接Mysql数据库出现的DexArchiveBuilderException异常
打开mysql驱动下载页这里我链接的是5.0.7，你可以选择其他的版本
如图点击jar下载：
第二步，导入jar包
将我们下载好的mysql-connector-java.jar包放入我们建好的项目中的lib文件夹中，如图：
右键这个jar包，选择添加为库：
如果出现下图这几个东西，表明导入成功了：
好了准备工作已经完成了。
第三步，建议数据库与表
这里我用的是HeidiSQL操作mysql的，安装好heidiSQL之后，新建数据库test如下：
然后新建表test_one以及字段id，name：
添加如下数据记录：
第四步，建立连接
第一步，新建数据库连接帮助类
新建一个DBOpenHelper.java
package com.example.mysqlconnectiontest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBOpenHelper {
private static String diver = "com.mysql.jdbc.Driver";
//加入utf-8是为了后面往表中输入中文，表中不会出现乱码的情况
private static String url = "jdbc:mysql://192.168.1.121:3306/test?characterEncoding=utf-8";
private static String user = "root";//用户名
private static String password = "123456";//密码
/*
* 连接数据库
* */
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(diver);
conn = (Connection) DriverManager.getConnection(url,user,password);//获取连接
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
第二步，修改activity_main.xml文件
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<Button
android:id="@+id/bt_send"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询数据表"/>
<TextView
android:id="@+id/tv_response"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textStyle="bold" />
</LinearLayout>
第三步，修改MainActivity.java
package com.example.mysqlconnectiontest;
import android.annotation.SuppressLint;
import android.os.Handler;
import android.os.Message;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MainActivity extends AppCompatActivity {
private Button button;
private TextView textView;
private static final int TEST_USER_SELECT = 1;
int i =0;
@SuppressLint("HandlerLeak")
private Handler handler = new Handler(){
@Override
public void handleMessage(Message msg) {
String user;
switch (msg.what){
case TEST_USER_SELECT:
Test test = (Test) msg.obj;
user = test.getUser();
System.out.println("***********");
System.out.println("***********");
System.out.println("user:"+user);
textView.setText(user);
break;
}
}
};
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
button = (Button) findViewById(R.id.bt_send);
textView = (TextView) findViewById(R.id.tv_response);
}
@Override
protected void onStart() {
super.onStart();
button.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
//执行查询操作
//通多点击buttoni自增长查询对应id的name
if (i<=3){//因为数据库我就添加了三个数据条数，所以进行判断使其可以循环查询
i++;
}
else{
i=1;
}
//连接数据库进行操作需要在主线程操作
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
conn =(Connection) DBOpenHelper.getConn();
String sql = "select name from test_one where id='"+i+"'";
Statement st;
try {
st = (Statement) conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()){
//因为查出来的数据试剂盒的形式，所以我们新建一个javabean存储
Test test = new Test();
test.setUser(rs.getString(1));
Message msg = new Message();
msg.what =TEST_USER_SELECT;
msg.obj = test;
handler.sendMessage(msg);
}
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
}
});
}
}
第五步 添加网络权限
修改AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.mysqlconnectiontest">
//添加网络权限
<uses-permission android:name="android.permission.INTERNET"/>
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
运行结果如下：
第六步，数据库删除操作
第一步，修改activity_main.xml文件（添加一个删除按钮）
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<Button
android:id="@+id/bt_send"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询数据表"/>
<TextView
android:id="@+id/tv_response"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textStyle="bold" />
<Button
android:id="@+id/bt_delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除数据"/>
</LinearLayout>
第二步，修改MainActivity.java
package com.example.mysqlconnectiontest;
import android.annotation.SuppressLint;
import android.os.Handler;
import android.os.Message;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private Button button,button_delete;
private TextView textView;
private static final int TEST_USER_SELECT = 1;
int i =0,d=0;
@SuppressLint("HandlerLeak")
private Handler handler = new Handler(){
@Override
public void handleMessage(Message msg) {
String user;
switch (msg.what){
case TEST_USER_SELECT:
Test test = (Test) msg.obj;
user = test.getUser();
System.out.println("***********");
System.out.println("***********");
System.out.println("user:"+user);
textView.setText(user);
break;
}
}
};
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
button = (Button) findViewById(R.id.bt_send);
textView = (TextView) findViewById(R.id.tv_response);
button_delete = (Button) findViewById(R.id.bt_delete);
}
@Override
protected void onStart() {
super.onStart();
button.setOnClickListener(this);
button_delete.setOnClickListener(this);
}
@Override
public void onClick(View view) {
switch (view.getId()){
case R.id.bt_send:
//执行查询操作
//通多点击buttoni自增长查询对应id的name
if (i<=3){
i++;
}
else{
i=1;
}
//连接数据库进行操作需要在主线程操作
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
conn =(Connection) DBOpenHelper.getConn();
String sql = "select name from test_one where id='"+i+"'";
Statement st;
try {
st = (Statement) conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()){
//因为查出来的数据是集合的形式，所以我们新建一个javabean存储
Test test = new Test();
test.setUser(rs.getString(1));
Message msg = new Message();
msg.what =TEST_USER_SELECT;
msg.obj = test;
handler.sendMessage(msg);
}
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
case R.id.bt_delete:
//new一个线程执行删除数据库数据
d++;
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
int u = 0;
conn =(Connection) DBOpenHelper.getConn();
String sql = "delete from test_one where id='"+d+"'";
PreparedStatement pst;
try {
pst = (PreparedStatement) conn.prepareStatement(sql);
u = pst.executeUpdate();
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
default:
}
}
}
运行效果如下：
表中数据全部被删掉（所以删除东西前最好加个弹出框，是否确定删除，来保证数据不会被乱删）
第七步，插入数据
第一步，修改activity_main.xml文件（添加一个添加按钮和输入框）
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<Button
android:id="@+id/bt_send"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询数据表"/>
<TextView
android:id="@+id/tv_response"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textStyle="bold" />
<Button
android:id="@+id/bt_delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除数据"/>
<EditText
android:id="@+id/ed_insert"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入要插入的数据"
android:maxLines="2"/>
<Button
android:id="@+id/bt_insert"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="插入数据"/>
</LinearLayout>
第二步，修改MainActivity.java
package com.example.mysqlconnectiontest;
import android.annotation.SuppressLint;
import android.os.Handler;
import android.os.Message;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private Button button,button_delete,button_insert;
private TextView textView;
private static final int TEST_USER_SELECT = 1;
int i =0,d=0;
private EditText editText;
@SuppressLint("HandlerLeak")
private Handler handler = new Handler(){
@Override
public void handleMessage(Message msg) {
String user;
switch (msg.what){
case TEST_USER_SELECT:
Test test = (Test) msg.obj;
user = test.getUser();
System.out.println("***********");
System.out.println("***********");
System.out.println("user:"+user);
textView.setText(user);
break;
}
}
};
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
button = (Button) findViewById(R.id.bt_send);
textView = (TextView) findViewById(R.id.tv_response);
button_delete = (Button) findViewById(R.id.bt_delete);
button_insert = (Button) findViewById(R.id.bt_insert);
editText = (EditText) findViewById(R.id.ed_insert);
}
@Override
protected void onStart() {
super.onStart();
button.setOnClickListener(this);
button_delete.setOnClickListener(this);
button_insert.setOnClickListener(this);
}
@Override
public void onClick(View view) {
switch (view.getId()){
case R.id.bt_send:
//执行查询操作
//通多点击buttoni自增长查询对应id的name
i++;
//连接数据库进行操作需要在主线程操作
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
conn =(Connection) DBOpenHelper.getConn();
String sql = "select name from test_one where id='"+i+"'";
Statement st;
try {
st = (Statement) conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()){
//因为查出来的数据试剂盒的形式，所以我们新建一个javabean存储
Test test = new Test();
test.setUser(rs.getString(1));
Message msg = new Message();
msg.what =TEST_USER_SELECT;
msg.obj = test;
handler.sendMessage(msg);
}
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
case R.id.bt_delete:
//new一个线程执行删除数据库数据
d++;
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
int u = 0;
conn =(Connection) DBOpenHelper.getConn();
String sql = "delete from test_one where id='"+d+"'";
PreparedStatement pst;
try {
pst = (PreparedStatement) conn.prepareStatement(sql);
u = pst.executeUpdate();
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
case R.id.bt_insert:
//执行插入操作
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
int u = 0;
conn =(Connection) DBOpenHelper.getConn();
String sql = "insert into test_one (name) values(?)";
PreparedStatement pst;
try {
pst = (PreparedStatement) conn.prepareStatement(sql);
//将输入的edit框的值获取并插入到数据库中
pst.setString(1,editText.getText().toString());
u = pst.executeUpdate();
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
default:
}
}
}
运行结果如下：
数据库表记录：
第八步，更新数据
第一步，修改activity_main.xml文件（添加一个更新按钮和输入框）
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">
<Button
android:id="@+id/bt_send"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询数据表"/>
<TextView
android:id="@+id/tv_response"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textStyle="bold" />
<Button
android:id="@+id/bt_delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除数据"/>
<EditText
android:id="@+id/ed_insert"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入要插入的数据"
android:maxLines="2"/>
<Button
android:id="@+id/bt_insert"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="插入数据"/>
<EditText
android:id="@+id/ed_update"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入更新的内容"
android:maxLines="2"/>
<Button
android:id="@+id/bt_update"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="更新数据"/>
</LinearLayout>
第二步，修改MainActivity.java
package com.example.mysqlconnectiontest;
import android.annotation.SuppressLint;
import android.os.Handler;
import android.os.Message;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private Button button,button_delete,button_insert,button_update;
private TextView textView;
private static final int TEST_USER_SELECT = 1;
int i =0,d=0,z=0;
private EditText editText,editText_update;
@SuppressLint("HandlerLeak")
private Handler handler = new Handler(){
@Override
public void handleMessage(Message msg) {
String user;
switch (msg.what){
case TEST_USER_SELECT:
Test test = (Test) msg.obj;
user = test.getUser();
System.out.println("***********");
System.out.println("***********");
System.out.println("user:"+user);
textView.setText(user);
break;
}
}
};
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
button = (Button) findViewById(R.id.bt_send);
textView = (TextView) findViewById(R.id.tv_response);
button_delete = (Button) findViewById(R.id.bt_delete);
button_insert = (Button) findViewById(R.id.bt_insert);
button_update = (Button) findViewById(R.id.bt_update);
editText_update = (EditText) findViewById(R.id.ed_update);
}
@Override
protected void onStart() {
super.onStart();
button.setOnClickListener(this);
button_delete.setOnClickListener(this);
button_insert.setOnClickListener(this);
button_update.setOnClickListener(this);
}
@Override
public void onClick(View view) {
switch (view.getId()){
case R.id.bt_send:
//执行查询操作
//通多点击buttoni自增长查询对应id的name
if (i<=3){
i++;
}else {
i=1;
}
//连接数据库进行操作需要在主线程操作
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
conn =(Connection) DBOpenHelper.getConn();
String sql = "select name from test_one where id='"+i+"'";
Statement st;
try {
st = (Statement) conn.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next()){
//因为查出来的数据试剂盒的形式，所以我们新建一个javabean存储
Test test = new Test();
test.setUser(rs.getString(1));
Message msg = new Message();
msg.what =TEST_USER_SELECT;
msg.obj = test;
handler.sendMessage(msg);
}
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
case R.id.bt_delete:
//new一个线程执行删除数据库数据
d++;
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
int u = 0;
conn =(Connection) DBOpenHelper.getConn();
String sql = "delete from test_one where id='"+d+"'";
PreparedStatement pst;
try {
pst = (PreparedStatement) conn.prepareStatement(sql);
u = pst.executeUpdate();
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
case R.id.bt_insert:
//执行插入操作
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
int u = 0;
conn =(Connection) DBOpenHelper.getConn();
String sql = "insert into test_one (name) values(?)";
PreparedStatement pst;
try {
pst = (PreparedStatement) conn.prepareStatement(sql);
//将输入的edit框的值获取并插入到数据库中
pst.setString(1,editText.getText().toString());
u = pst.executeUpdate();
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
case R.id.bt_update:
//new一个线程执行删除数据库数据
z++;
new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
int u = 0;
conn =(Connection) DBOpenHelper.getConn();
String sql = "update test_one set name='"+editText_update.getText().toString()+"' where id='"+z+"'";
PreparedStatement pst;
try {
pst = (PreparedStatement) conn.prepareStatement(sql);
u = pst.executeUpdate();
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}).start();
break;
default:
}
}
}
运行结果如下：
关于Android连接mysql数据库以及增删改查就讲到这里了，欢迎讨论！
项目GitHub地址：https://github.com/Tobey-r1/RepositoryTest 。
补充
有人私信我问了我那个Test的问题，我简单补充上去吧：
public class Test {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
