安卓Android_Studio_Java_操作mysql数据库

安卓Android_Studio_Java_操作mysql数据库

 

基本连接方法：https://www.cnblogs.com/txwtech/p/16380248.html

基本连接方法：如何创建mysql数据库。

Mysql所有连接方法与汇总参考：链接https://www.cnblogs.com/txwtech

 

创建好的mysql的数据库确保用IP与端口能够连接，比如用MySql Workbench

 

用真机测试：（WIFI）保持数据库的的网络与手机在同一个网段。

各个类：

UserDao类：增删改查的操作

package com.example.myapplication;

import android.os.Bundle;

import com.google.android.material.floatingactionbutton.FloatingActionButton;
import com.google.android.material.snackbar.Snackbar;

import androidx.appcompat.app.AppCompatActivity;
import androidx.appcompat.widget.Toolbar;

import android.util.Log;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

import java.sql.Date;
import java.text.SimpleDateFormat;

//by txwtech
public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Toolbar toolbar = findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        FloatingActionButton fab = findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
                        .setAction("Action", null).show();

            }
        });
        Button button=findViewById(R.id.button1);
        button.setOnClickListener(new View.OnClickListener()
        {
            @Override
            public void onClick(View view)
            {

                         // DBUtil.getConn();
                UserBean bean=new UserBean();
               UserDao.add(bean);
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }

                // Toast.makeText(MainActivity.this, "数据写入失败", Toast.LENGTH_SHORT).show();
                TextView textView=findViewById(R.id.textview1);
                Date date=new java.sql.Date(System.currentTimeMillis());
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String date1 = sdf.format(date);
                String show_msg="";
               if(UserDao.f==true)
               {
                   show_msg="写入成功"+date1;
                   Toast.makeText(MainActivity.this,show_msg , Toast.LENGTH_SHORT).show();

                   textView.setText(show_msg);

                   UserDao.f=false;
               }
               else
               {
                   show_msg="写入失败"+date1;
                   textView.setText(show_msg);
                   Toast.makeText(MainActivity.this, show_msg, Toast.LENGTH_SHORT).show();
               }









               System.out.println("点击了一个按钮");
              //  Toast.makeText(MainActivity.this, "点击了一个按钮", Toast.LENGTH_SHORT).show();
            }
        });
    }





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

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

package com.example.myapplication;
 
import android.util.Log;
 
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static android.content.ContentValues.TAG;


public class DBUtil {
 
    private static String diver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://192.168.10.102:3306/txw_oqa_db";
    private static String user = "root";//用户名
    private static String password = "87958868";//密码
    public static Connection conn;
    private static Connection conn2;

 
    /*
     * 连接数据库
     * */
    public static Connection getConn()
    {

        final Thread thread =new Thread(new Runnable() {
            @Override
            public void run() {

              //  while (!Thread.interrupted())
                while(true)
                {
                    try {
                        Thread.sleep(100);  // 每隔0.1秒尝试连接
                    } catch (InterruptedException e) {
                        Log.e(TAG, e.toString());
                    }

                    try {
                        Class.forName(diver);
                         conn =  DriverManager.getConnection(url, user, password);//获取连接
                        Log.e("getConn", "连接成功");

                         if(conn==null)
                         {
                             Thread.sleep(1000);
                         }
                         break;



                    } catch (ClassNotFoundException e) {
                        Log.e("getConn", e.getMessage(), e);
                        e.printStackTrace();
                    } catch (SQLException e) {
                        Log.e("getConn", e.getMessage(), e);
                        e.printStackTrace();
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }

                }
            }
        });
        thread.start();
        return conn;

    }
 
    public static void close(Statement state, Connection conn) {
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
 
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    public static void close(ResultSet rs, Statement state, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
 
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
 
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
}

package com.example.myapplication;
 
import java.io.Serializable;
//用户实体类
public class UserBean implements Serializable {
    //主键
    private int id;
    //用户姓名
    private String user_name;
    //手机号
    private String phone;
    //密码
    private String password;
    //创建时间
    private String create_date;
 
    public String getCreate_date() {
        return create_date;
    }
 
    public void setCreate_date(String create_date) {
        this.create_date = create_date;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getUser_name() {
        return user_name;
    }
 
    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }
 
    public String getPhone() {
        return phone;
    }
 
    public void setPhone(String phone) {
        this.phone = phone;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    @Override
    public String toString() {
        return "UserBean{" +
                "id=" + id +
                ", user_name='" + user_name + '\'' +
                ", phone='" + phone + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

package com.example.myapplication;

import android.util.Log;

import java.text.SimpleDateFormat;
import java.util.ArrayList;

import java.sql.*;

//import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

//用户数据库连接类
public class UserDao {
    public  static boolean f = false;
    public  static int counter = 2;
 
    //新增
    public static boolean add(UserBean bean) {
      //  String sql = "insert into user(user_name,phone,create_date,password)values('" + bean.getUser_name() + "','" + bean.getPhone() + "','" + bean.getCreate_date() + "','" + bean.getPassword() + "')";
   //     String sql = "insert into lot_weight_measurement_record(id,lot,base_data,sn_box,weight,exec_date)values('1','LA123456',,'SN123874EAA','88','2022-6-16 22:42')";


        Connection conn = DBUtil.getConn();


        final Thread thread=new Thread(new Runnable() {
            Statement state = null;

            int a = 0;

            @Override
            public void run() {
                while (true)
                {
                    if(DBUtil.conn==null)
                    {
                        Log.e("getConn", "连接失败");
                        try {
                            Thread.sleep(1000);
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                    }
                    else
                    {
                        Log.e("getConn", "连接成功");
                        counter++;
                        //string转换成int  :Integer.valueOf("12")
                        //
                        //int转换成string : String.valueOf(12)

                        String sn=String.valueOf(counter);
                        Date date=new java.sql.Date(System.currentTimeMillis());
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        String date1 = sdf.format(date);
                        String sql = "insert into lot_weight_measurement_record set id='10008',sn_box='"+sn+"--"+date1+"'"+",exec_date='"+date1+"'";
                        try {
                            state = DBUtil.conn.createStatement();
                            a = state.executeUpdate(sql);
                            f = true;
                        } catch (Exception e) {
                            Log.e("add->", e.getMessage(), e);
                            e.printStackTrace();
                        } finally {
                            DBUtil.close(state, DBUtil.conn);
                        }
                      //  if (a > 0)
                        if(state!=null)
                        {
                            f = true;
                        }
                        Log.e("getConn", "开始插入数据");
                        break;
                    }
                }

            }
        });
        thread.run();



        return f;
    }
 
    //删除
    public static boolean delete(UserBean bean) {
        String sql = "delete from user where id=" + bean.getId();
        Connection conn = DBUtil.getConn();
        Statement state = null;
        boolean f = false;
        int a = 0;
        try {
            state = conn.createStatement();
            a = state.executeUpdate(sql);
        } catch (Exception e) {
            Log.e("delete->", e.getMessage(), e);
            e.printStackTrace();
        } finally {
            DBUtil.close(state, conn);
        }
        if (a > 0) {
            f = true;
        }
        return f;
    }
 
 
    //修改
    public static boolean update(UserBean bean) {
 
        String sql = "update user set " + "user_name='" + bean.getUser_name() + "', phone='" + bean.getPhone() + "', create_date='" + bean.getCreate_date() + "', password='" + bean.getPassword() + "' where id='" + bean.getId() + "'";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        boolean f = false;
        int a = 0;
        try {
            state = conn.createStatement();
            a = state.executeUpdate(sql);
        } catch (Exception e) {
            Log.e("update->", e.getMessage(), e);
            e.printStackTrace();
        } finally {
            DBUtil.close(state, conn);
        }
        if (a > 0) {
            f = true;
        }
        return f;
    }
 
    //获取列表
    public static List getListByPhone(String phone) {
 
        //结果存放集合
        List list = new ArrayList();
        //MySQL 语句
        String sql = "select * from user where phone=" + phone;
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;
        boolean f = false;
        int a = 0;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            Log.e("getListByPhone->","getListByPhone");
            while (rs.next()) {
                UserBean bean = new UserBean();
                bean.setId(rs.getInt("id"));
                bean.setUser_name(rs.getString("user_name"));
                bean.setPhone(rs.getString("phone"));
                bean.setPassword(rs.getString("password"));
                bean.setCreate_date(rs.getString("create_date"));
                list.add(bean);
                Log.e("getListByPhone->",bean.toString());
 
            }
        } catch (Exception e) {
            Log.e("getListByPhone->", e.getMessage(), e);
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }
        if (a > 0) {
            f = true;
        }
        return list;
    }
 
    //获取列表
    public static List getList() {
 
        //结果存放集合
        List list = new ArrayList();
        //MySQL 语句
        String sql = "select * from user";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;
        boolean f = false;
        int a = 0;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                UserBean bean = new UserBean();
                bean.setId(rs.getInt("id"));
                bean.setUser_name(rs.getString("user_name"));
                bean.setPhone(rs.getString("phone"));
                bean.setPassword(rs.getString("password"));
                bean.setCreate_date(rs.getString("create_date"));
                list.add(bean);
 
            }
        } catch (Exception e) {
            Log.e("update->", e.getMessage(), e);
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }
        if (a > 0) {
            f = true;
        }
        return list;
    }
 
 
}