安卓通过JDBC连接数据库遇到的坑

安卓通过JDBC连接数据库遇到的坑

村头老杨头

Fri Jul 3

村头老杨头

最近有一项目需要通过安卓直接连接数据库,想做的这样只能使用安卓原生开发,通过JDBC连接数据库,结果就遇到了如下BUG: W/System.err: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
首先通过JDBC去连接数据库要做的步骤如下:

导入Jar包

导入数据库连接驱动的jar包 mysql-connector-java-5.1.47.jar

把项目类型选择Project在App文件夹下创建一个libs文件夹把jar包拷贝过去

image.png

选中jar包右击 在选项卡选择 add as library 把jar包添加到项目中

image.png
image.png

看到下图的小三角符号证明导入进当前项目

image.png

java代码文件结构

image.png
第一步:编写UserInfo实体类这个类对应数据库中的表

public class UserInfo {
    private Integer id;
    private String username;
    private String password;

    public UserInfo() {
    }
    public UserInfo(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

数据库中表的字段
image.png

第二步:编写BaseDao用于连接数据库的通用工具
BaseDao代码
第三步:编写Dao层代码(Dao层继承BaseDao工具)

public class UserInfoDao extends BaseDao<UserInfo> {
    //添加用户
    public int add(UserInfo userInfo){
        String sql = "insert into `userinfo` (`username`,`password`) values (?,?)";
        Object[] objects = new Object[]{userInfo.getUsername(),userInfo.getPassword()};
        int i = executeUpdate(sql, objects);
        return i;
    }
    //查询用户
    public UserInfo getUserInfoByUsernameAndPassword(UserInfo userInfo){
        String sql = "select * from `userinfo` where username=? and password=?";

        Object[] objects = new Object[]{userInfo.getUsername(),userInfo.getPassword()};
        List<UserInfo> userInfos = executeQuery(sql, UserInfo.class, objects);
        if(userInfos!=null && userInfos.size()!=0){
            return userInfos.get(0);
        }
        return null;
    }
}

AndroidManifest.xml

在AndroidManifest文件中添加<uses-permission android:name="android.permission.INTERNET" />开启网络权限

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.myhello" >
    <uses-sdk android:minSdkVersion="7" />
    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
    <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>

bug

public class MainActivity extends Activity {
    private UserInfo userInfo;

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

        final EditText username = (EditText) findViewById(R.id.username);
        final EditText password = (EditText) findViewById(R.id.password);
        Button login = (Button) findViewById(R.id.login);

        login.setOnClickListener(new View.OnClickListener() {
            //做登录操作
            @Override
            public void onClick(View view) {
                final UserInfo user = new UserInfo(username.getText().toString(), password.getText().toString());
                final UserInfoDao userInfoDao = new UserInfoDao();
                UserInfo userInfoByUsernameAndPassword = userInfoDao.getUserInfoByUsernameAndPassword(user);
                System.out.println(userInfoByUsernameAndPassword);
            }
        });
    }
}

刚开始以为这样写就可以跟java中调用数据库一样结果报错
主要是:W/System.err: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.这句话。翻译成中文的意思是:W /系统。错:com.mysql.jdbc.exceptions.jdbc4。无法创建到数据库服务器的连接。尝试重新连接3次。放弃。
(ps:一直以为是连接出了问题)
有的说是数据库连接超时造成的说要改数据库参数。
有的说是连接数据地址不能是127.0.0.1要改成电脑网卡内网IP但是我原本用的就是外网的数据库不用纠结IP
查了好多资料结果终于看到了本质的问题,那就是安卓连接数据库必须要开启子线程去处理,知道了问题就知道该如何解决这个问题了

W/System.err: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at java.lang.reflect.Constructor.newInstance0(Native Method)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:343)
W/System.err:     at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.Util.getInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
W/System.err:     at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2105)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2030)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:778)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at java.lang.reflect.Constructor.newInstance0(Native Method)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:343)
W/System.err:     at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
W/System.err:     at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
        at java.sql.DriverManager.getConnection(DriverManager.java:580)
        at java.sql.DriverManager.getConnection(DriverManager.java:218)
        at com.example.myhello.utils.BaseDao.getConnection(BaseDao.java:26)
        at com.example.myhello.utils.BaseDao.executeQuery(BaseDao.java:57)
        at com.example.myhello.dao.UserInfoDao.getUserInfoByUsernameAndPassword(UserInfoDao.java:24)
W/System.err:     at com.example.myhello.MainActivity$1.onClick(MainActivity.java:39)
        at android.view.View.performClick(View.java:7438)
        at android.view.View.performClickInternal(View.java:7415)
        at android.view.View.access$3600(View.java:810)
        at android.view.View$PerformClick.run(View.java:28286)
W/System.err:     at android.os.Handler.handleCallback(Handler.java:938)
W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:99)
W/System.err:     at android.os.Looper.loop(Looper.java:223)
        at android.app.ActivityThread.main(ActivityThread.java:7523)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:941)
W/System.err: Caused by: android.os.NetworkOnMainThreadException
W/System.err:     at android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode.java:1597)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:389)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:230)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:212)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:436)
        at java.net.Socket.connect(Socket.java:621)
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
W/System.err:     at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:301)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2197)
W/System.err:     at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2046)
    	... 25 more
D/AndroidRuntime: Shutting down VM

解决代码:

public class MainActivity extends Activity {
    private UserInfo userInfo;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        final EditText username = (EditText) findViewById(R.id.username);
        final EditText password = (EditText) findViewById(R.id.password);
        Button login = (Button) findViewById(R.id.login);
        login.setOnClickListener(new View.OnClickListener() {
            //做登录操作
            @Override
            public void onClick(View view) {
                final UserInfo user = new UserInfo(username.getText().toString(), password.getText().toString());
                final UserInfoDao userInfoDao = new UserInfoDao();
		//创建线程并启动
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        UserInfo userInfoByUsernameAndPassword = userInfoDao.getUserInfoByUsernameAndPassword(user);
                        System.out.println(userInfoByUsernameAndPassword);
                    }
                }).start();
            }
        });
    }
}

至此问题解决完成Android通过JDBC连接数据库

评论