Android整合mysql

前期准备

我这边使用的开发工具是Android Studio，采用的方式是导入jar包的方式引入mysql

将jar包拖到libs下面就可以，然后右键拖入的jar包，有一个add to library，mysql的jar包就导入了。

mysql连接工具类

package com.example.dayfour2021_6_10.utils;

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

/**
 * @author xiaow
 * Created on 2021/6/16
 */
public class DbOpenHelper {

    private static String driver = "com.mysql.jdbc.Driver";// mysql 驱动
    private static String ip = "ipaddr";  // 安装了 mysql 的电脑的 ip 地址
    private static String dbName = "ssm";    // 要连接的数据库
    private static String url = "jdbc:mysql://ipaddr/home??useUnicode=true&characterEncoding=UTF-8&useAffectedRows=true&useTimezone=true&serverTimezone=GMT%2B8";    // mysql 数据库连接 url
    private static String user = "username";    // 用户名
    private static String password = "pwd"; // 密码

    private static Connection sConnection;

    /**
     * 连接数据库
     */
    public static Connection getConnection() {
        if (sConnection == null) {
            try {
                Class.forName(driver);  // 获取 mysql 驱动
                sConnection = DriverManager.getConnection(url, user, password);   // 获取连接
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return sConnection;
    }

    /**
     * 关闭数据库
     */
    public static void closeConnection() {
        if (sConnection != null) {
            try {
                sConnection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

这里没有加入连接池，有兴趣的可以加一下，毕竟有了连接池会更好一些。

Dao

package com.example.dayfour2021_6_10.dao;

import com.example.dayfour2021_6_10.utils.DbOpenHelper;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

import static java.lang.System.*;

/**
 * @author xiaow
 * Created on 2021/6/16
 */

public class AllDao {
    private Connection connection;
    public AllDao(){
        connection= DbOpenHelper.getConnection();
    }

    public List<Float> list(String tableName){
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        List<Float> all=new LinkedList<>();
        try {
            preparedStatement=connection.prepareStatement("select * from "+tableName+" order by id desc limit 10");
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
            all.add(resultSet.getFloat(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if(resultSet!=null)
                    resultSet.close();
                if(preparedStatement!=null)
                    preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return all;
    }

    public void deleteFirst(String tableName){
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement=connection.prepareStatement("delete from "+tableName+" limit 1");
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if(preparedStatement!=null)
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public void addLast(String tableName,Float data){
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement=connection.prepareStatement("insert into "+tableName+" values(0,?,now())");
            preparedStatement.setDouble(1,data);
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if(preparedStatement!=null)
                    preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

仅仅实现了简单的查询和删除功能，其他功能可以自主添加，和java操作mysql一摸一样，有兴趣的可以在封装一个service层。