安卓如何直接将手机本地图片保存到Mysql数据库

---

前言

最近学校的华为杯比赛，身为小渣渣的我也带领我的小伙伴参加了一波，项目里要将用户的头像保存，安卓自带的sqllite小型数据库显然没办法实现，项目用到的Bomb云数据库需要有备案的域名，而我们没有，base64编码又太长了放不进，无奈只能用Mysql数据库，写接口又嫌太麻烦，于是选择直连。

---

一、导入jdbc外部jar包

1.下载jar包

在Android工程中要使用jdbc的话，要导入jdbc的外部jar包，可去官网下载https://downloads.mysql.com/archives/c-j/
我用的版本是5.1.45，看了很多大佬的博客似乎高版本的jar包直连会报错，这个版本是能够正常直连的版本，我也就直接使用了这个版本

2.导入jar包

project模式下直接将解压缩后的jar包拖进libs目录下
右键选择 “Add as libs”

)

二、具体使用

重点！！！

所有对数据库的操作都应该放在子线程当中运行，否则会报错，测试的时候可以在onCreat方法下加入如下代码可直接在主线程中运行，但是不建议使用这种方法，很容易导致崩溃

  //主线程使用网络请求
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);

1.创建数据库连接工具类

代码如下：

package com.c201801020208.msq;

import com.mysql.jdbc.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String url = "jdbc:mysql://192.168.***.***:3306/book?serverTimezone=GMT%2b8&useSSL=false";
    private static final String username = "root";
    private static final String password = "164352";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return (Connection) DriverManager.getConnection(url,username,password);
    }
    public static void closeConnection(Connection connection){
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

和java连接Mysql不同的是，这里不能使用localhost，需要用你的电脑的ip地址
win+R打开cmd，输入ipconfig查看自己的ip地址，里面有很多ipv4地址，实在不知道哪个，就百度搜一下或一个一个试

2.选择本地图片，并放入ImageView内

代码如下：

 //选择本地图片
        head.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent galleryIntent = new Intent(Intent.ACTION_GET_CONTENT);
                galleryIntent.addCategory(Intent.CATEGORY_OPENABLE);
                galleryIntent.setType("image/*");//图片
                startActivityForResult(galleryIntent, 0);
            }
        });

通过galleryIntent调用系统本地的图片，返回的结果在onActivityResult方法里获得

 @Override
    public void onActivityResult(int requestCode, int resultCode, Intent data) {
        super.onActivityResult(requestCode, resultCode, data);
        // TODO Auto-generated method stub
        if (requestCode == 0 && resultCode == -1) {
            uri = data.getData();
            head.setImageURI(uri)；
    }

3.将图片转换为base64编码

 //将图片转换成Base64编码
            try {
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                Bitmap bitmap = MediaStore.Images.Media.getBitmap(getContentResolver(), uri);
                bitmap.compress(Bitmap.CompressFormat.JPEG, 100, baos);
                byte[] imageBytes = baos.toByteArray();
                imageString = Base64.encodeToString(imageBytes, Base64.DEFAULT);

            } catch (Exception e) {
            }

4.将图片的base64编码编码存入数据库当中

try {
                Connection cn = DBUtil.getConnection();
                String sql = "INSERT into image(username,head) values(?,?)";
                PreparedStatement preparedStatement = null;
                preparedStatement = (PreparedStatement) cn.prepareStatement(sql);
                preparedStatement.setString(1, "hh");
                preparedStatement.setString(2, imageString);
                int res = preparedStatement.executeUpdate();
                if (res > 0) {
                    System.out.println("Success");
                } else {
                    System.out.println("Failed");
                }
                cn.close();//记得关闭连接 
                DBUtil.closeConnection(cn);
                Log.v("tag", "end");
            } catch (SQLException e) {
                e.printStackTrace();
                textView.setText("数据库链接失败" + e);
            }

5.将数据库中的base64编码取出并放入ImageView当中

 //数据库取出base64编码
        imageView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Connection connection = null;
                PreparedStatement preparedStatement = null;
                ResultSet resultSet = null;
                try {
                    connection = DBUtil.getConnection();
                    String sql = "select head from image where username = ?";
                    preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
                    preparedStatement.setString(1, "cuasimodo");
                    resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        byte[] imageBytes = Base64.decode(resultSet.getString("head"), Base64.DEFAULT);
                        Bitmap decodedImage = BitmapFactory.decodeByteArray(imageBytes, 0, imageBytes.length);
                        imageView.setImageBitmap(decodedImage);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DBUtil.closeConnection(connection);
                    if (resultSet != null) {
                        try {
                            resultSet.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                    if (preparedStatement != null) {
                        try {
                            preparedStatement.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        });

---

三、完整代码

1.MainActivity

package com.c201801020208.msq;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.net.Uri;
import android.os.Bundle;
import android.os.StrictMode;
import android.provider.MediaStore;
import android.util.Base64;
import android.util.Log;
import android.view.View;
import android.widget.ImageView;
import android.widget.TextView;
import android.widget.Toast;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MainActivity extends AppCompatActivity {

    TextView textView;
    ImageView head;
    ImageView imageView;
    Uri uri;//头像
    String imageString;

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

        //主线程使用网络请求
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);

        textView = findViewById(R.id.textview);
        head = findViewById(R.id.imageView);
        imageView = findViewById(R.id.imageView2);

        //选择本地图片
        head.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent galleryIntent = new Intent(Intent.ACTION_GET_CONTENT);
                galleryIntent.addCategory(Intent.CATEGORY_OPENABLE);
                galleryIntent.setType("image/*");//图片
                startActivityForResult(galleryIntent, 0);
            }
        });

        //数据库提取base64编码
        imageView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Connection connection = null;
                PreparedStatement preparedStatement = null;
                ResultSet resultSet = null;
                try {
                    connection = DBUtil.getConnection();
                    String sql = "select head from image where username = ?";
                    preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
                    //将用户名为cuasimodo的人的头像取出，放入ImageView内
                    preparedStatement.setString(1, "cuasimodo");
                    resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        byte[] imageBytes = Base64.decode(resultSet.getString("head"), Base64.DEFAULT);
                        Bitmap decodedImage = BitmapFactory.decodeByteArray(imageBytes, 0, imageBytes.length);
                        imageView.setImageBitmap(decodedImage);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DBUtil.closeConnection(connection);
                    if (resultSet != null) {
                        try {
                            resultSet.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                    if (preparedStatement != null) {
                        try {
                            preparedStatement.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        });


    }

    @Override
    public void onActivityResult(int requestCode, int resultCode, Intent data) {
        super.onActivityResult(requestCode, resultCode, data);
        // TODO Auto-generated method stub
        if (requestCode == 0 && resultCode == -1) {
            uri = data.getData();
            head.setImageURI(uri);
            Log.i("tt", uri.getPath());
            Log.i("tt", uri.getEncodedPath());

            //将图片转换成Base64编码
            try {
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                Bitmap bitmap = MediaStore.Images.Media.getBitmap(getContentResolver(), uri);
                bitmap.compress(Bitmap.CompressFormat.JPEG, 100, baos);
                byte[] imageBytes = baos.toByteArray();
                imageString = Base64.encodeToString(imageBytes, Base64.DEFAULT);

            } catch (Exception e) {
            }

            try {

                Connection cn = DBUtil.getConnection();
                String sql = "INSERT into image(username,head) values(?,?)";
                PreparedStatement preparedStatement = null;
                preparedStatement = (PreparedStatement) cn.prepareStatement(sql);
                preparedStatement.setString(1, "hh");
                preparedStatement.setString(2, imageString);
                int res = preparedStatement.executeUpdate();
                if (res > 0) {
                    System.out.println("Success");
                } else {
                    System.out.println("Failed");
                }
                cn.close();//记得关闭 不然内存泄漏
                DBUtil.closeConnection(cn);
                Log.v("tag", "end");
            } catch (SQLException e) {
                e.printStackTrace();
                textView.setText("数据库链接失败" + e);
            }
        }
    }
}

2.layout文件

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".MainActivity">

    <TextView
        android:id="@+id/textview"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Hello World!"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <ImageView
        android:id="@+id/imageView"
        android:layout_width="100dp"
        android:layout_height="100dp"
        android:layout_marginTop="200dp"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintEnd_toStartOf="@+id/imageView2"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        android:background="@color/colorAccent"/>

    <ImageView
        android:id="@+id/imageView2"
        android:layout_width="100dp"
        android:layout_height="100dp"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.5"
        app:layout_constraintStart_toEndOf="@+id/imageView"
        app:layout_constraintTop_toTopOf="@+id/imageView"
        android:background="@color/colorAccent"/>

</androidx.constraintlayout.widget.ConstraintLayout>

3.DBUtil数据库工具类

package com.c201801020208.msq;

import com.mysql.jdbc.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String url = "jdbc:mysql://192.168.***.***:3306/book?serverTimezone=GMT%2b8&useSSL=false";
    private static final String username = "root";
    private static final String password = "164352";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return (Connection) DriverManager.getConnection(url,username,password);
    }
    public static void closeConnection(Connection connection){
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

使用前记得将ip地址改成自己的~