Android使用JDBC连接MYSQL

本次实践源于我们的一门叫做“智能终端应用系统”的课程的作业

以下将具体分析思路

实现效果

数据库表设计

数据库表设计

以下贴上具体实现代码

main.java(主界面代码）

package com.example.library;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import com.example.dbService.DBUtils;

import org.w3c.dom.Text;

import java.util.HashMap;

public  class main extends Activity{

    @Override
    protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        Button btn_data=(Button)findViewById(R.id.btn_1);
        final EditText edit_data=(EditText)findViewById(R.id.editText);
        final TextView output=(TextView)findViewById(R.id.textView2);
        @SuppressLint("HandlerLeak")
       final Handler handler = new Handler(){
            @Override
            public void handleMessage(Message msg) {

                switch (msg.what){
                    case 1:
                        String s = (String) msg.obj;
                        output.setText(s);
                        break;
                    case 2:
                        String ss = (String) msg.obj;
                        output.setText(ss);
                        break;
                }

            }
        };

        btn_data.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        //HashMap<String ,Object> map= DBUtils.getinfo("");
                        System.out.println(edit_data.getText().toString());
                        HashMap<String ,Object> map= DBUtils.getinfo(edit_data.getText().toString());
                        Message message=handler.obtainMessage();
                        if(map!=null){
                            String s="";
                            for (String key:map.keySet()){
                                s+=key+":"+map.get(key)+"\n";
                            }
                            message.what=1;
                            message.obj=s;
                        }
                        else{
                            message.what=2;
                            message.obj="无效查询";
                        }
                        handler.sendMessage(message);//传递参数给主线程
                    }


                }).start();
            }
        });

    }

}

DBUtils.java

package com.example.dbService;

import com.mysql.jdbc.ConnectionPropertiesTransform;
import com.mysql.jdbc.util.ResultSetUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;

public class DBUtils{
    private static String driver ="com.mysql.jdbc.Driver";
    private static String url ="jdbc:mysql://localhost:3306/book";
    private static String user ="root";
    private static String password="*******";
    private static Connection getconnet(String dbName){
        Connection connection=null;
        try{
            Class.forName(driver);//加载动态类
            String ip="10.128.247.205";
            connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbName,
                    user, password);
        }catch (Exception e){
            e.printStackTrace();
        }
        return connection;
    }
    public static HashMap<String,Object> getinfo(String book_id){
        HashMap<String ,Object>map=new HashMap<>();
        Connection connection=getconnet("book");
                try{
                    String sql="select * from book_info where book_id= ? ";
                    String sum="select count(*) as 'bupt'  from book_info;";
                    if(connection!=null){
                        PreparedStatement ps=connection.prepareStatement(sql);
                        PreparedStatement num=connection.prepareStatement(sum);
                        if(ps!=null){
                            ps.setString(1,book_id);
                            ResultSet result=ps.executeQuery();
                            ResultSet amount=num.executeQuery();
                            if(result!=null){
                                int count =result.getMetaData().getColumnCount();
                                while (result.next()){
                                    for(int i=1;i<count+1;i++){
                                        String field =result.getMetaData().getColumnName(i);
                                        map.put(field,result.getString(field));
                                    }
                                    //String middle=amount.getMetaData().getColumnName(1);//amount.getString("bupt")
                                   ///map.put("总共拥有的藏书数量","bupt");
                                }
                                connection.close();
                                ps.close();
                                return map;
                            }else {
                                return null;
                            }
                        }
                        else {
                            return null;
                        }
                    }
                    else{
                        return null;
                    }
                }
                catch (Exception e){
                    e.printStackTrace();
                    return null;
                }
    }

}