今天做教务管理系统的连接远程mysql数据库阶段遇到了很多问题,故记一次总计及注意事项。(第一次写博客,写的可能有些混乱)
步骤
1.在服务器上安装mysql数据库
mysql数据库默认是不支持远程连接,在安装的时候到这一步新建一个用户,就可以远程连接了,可以用navicat测试一下。如果已经安装好了数据库,网上也有很多教程,我就不重复了。
这里设置的用户名和密码一定要记住,下面连接数据库的时候需要用到!!!
2. 导入jar包,mysql-connector-java-5.1.18-bin.jar,网上有很多,这里尤其注意,我第一次用的是8.0.19的最新包,因为我的远程数据库装的是8.0.19版本的数据库,但是运行之后,发现一旦开始连接数据库,程序就闪退,在网上各种找资料,都没有解决。导致我还以为我代码写的有问题,还各种改(无语)。。。最后看到网上大部分人都是用的5.X.X的包,我就把数据库重装成了5.X.X版本的,包也是下的mysql-connector-java-5.1.18-bin.jar这个包,程序终于正常了。最终我也不知道为什么JDBC安卓直连数据库8.x的不能用。。。
导入jar包的方法如下图
将jar包粘贴到libs文件夹下,然后右键单击Add As Library,接着返回安卓视图,会在build.gradle(app)看到自动添加上了implementation files(‘libs/mysql-connector-java-5.1.18-bin.jar’),然后自己手动添加如下代码
compileOptions {
sourceCompatibility JavaVersion.VERSION_1_8
targetCompatibility JavaVersion.VERSION_1_8
}
buildTypes {
release {
minifyEnabled false
proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
}
}
compileOptions {
sourceCompatibility JavaVersion.VERSION_1_8
targetCompatibility JavaVersion.VERSION_1_8
}
}
dependencies {
implementation fileTree(include: ['*.jar'], dir: 'libs')
implementation 'androidx.appcompat:appcompat:1.1.0'
implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
testImplementation 'junit:junit:4.12'
androidTestImplementation 'androidx.test.ext:junit:1.1.1'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'
implementation files('libs/mysql-connector-java-5.1.18-bin.jar')
}
3.新建JDBC工具类(简化代码)
import android.util.Log;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//JDBC工具类
public class JDBCutils {
public static Connection connection(){
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection(
"jdbc:mysql://101.153.45.238:3306/work?useUnicode=true&autoReconnect=true&failOverReadOnly=false",
"user","password");
//101.200.48.238:要连接数据库的IP地址,3306:端口号,work:要操作的数据库,uesr:数据库的用户名,password:数据库的密码
Log.v("mit", "连接数据库成功");
} catch (ClassNotFoundException e) {
e.printStackTrace();
Log.v("mit","驱动初始化失败");
} catch (SQLException e) {
e.printStackTrace();
Log.v("mit","连接数据库失败");
}
return connection;
// return DriverManager.getConnection(url,use,pass);
}
public static void close(Statement statement,Connection connection) throws SQLException {
if(statement!=null){
statement.close();
}
if(statement!=null){
connection.close();
}
}
public static void close(ResultSet resultSet,Statement statement, Connection connection) throws SQLException {
if(statement!=null){
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(statement!=null){
connection.close();
}
}
}
4.编写xml布局文件
login.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<TextView
android:id="@+id/tv"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="教务管理系统"
android:textSize="40dp"
android:textColor="#00CCFF"
android:gravity="center"
android:layout_margin="30dp"/>
<EditText
android:id="@+id/login_user"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_below="@+id/tv"
android:background="@drawable/login_et"
android:layout_centerInParent="true"
android:hint="请输入学号"
android:textSize="20dp"/>
<EditText
android:id="@+id/login_pwd"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_below="@+id/login_user"
android:background="@drawable/login_et"
android:layout_centerInParent="true"
android:layout_margin="20dp"
android:hint="请输入密码"
android:textSize="20dp"/>
<RelativeLayout
android:layout_width="350dp"
android:layout_height="wrap_content"
android:layout_below="@id/login_pwd"
android:layout_centerInParent="true">
<Button
android:id="@+id/login"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="#3399FF"
android:text="登录"
android:textColor="#ffffff"
android:textSize="30dp"/>
</RelativeLayout>
</RelativeLayout>
login.xml用到了一个drawable
login_et.xml
<?xml version="1.0" encoding="utf-8"?>
<shape
xmlns:android="http://schemas.android.com/apk/res/android"
android:shape="rectangle">
<corners
android:radius="5dp"
android:topLeftRadius="15dp"
android:topRightRadius="15dp"
android:bottomLeftRadius="15dp"
android:bottomRightRadius="15dp" />
<padding
android:left="10dp"
android:top="10dp"
android:right="10dp"
android:bottom="10dp"/>
<size
android:width="350dp"
android:height="40dp"/>
<stroke
android:width="2dp"
android:color="#dcdcdc"/>
</shape>
4.编写主函数实现登录功能
import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.text.TextUtils;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MainActivity extends AppCompatActivity {
EditText btn_user;
EditText btn_pwd;
Button btn_login;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.login);
init();
btn_login.setOnClickListener(v -> {
String name = btn_user.getText().toString().trim();
String pasd = btn_pwd.getText().toString().trim();
if (TextUtils.isEmpty(name) || TextUtils.isEmpty(pasd)) {
Toast.makeText(MainActivity.this, "学号和密码不能为空", Toast.LENGTH_SHORT).show();
} else {
new Thread(new Runnable() {
@Override
public void run() {
loginuser(name, pasd);
}
}).start();
}
});
}
public void init() {
btn_user = (EditText) findViewById(R.id.login_user);
btn_pwd = (EditText) findViewById(R.id.login_pwd);
btn_login = (Button) findViewById(R.id.login);
}
public void loginuser(String name, String pass) {
Connection conn = null;
Statement statement = null;
ResultSet res = null;
try {
conn = JDBCutils.connection();
String sql = "select * from user where username='" + name + "' and password='" + pass + "'";
statement = conn.createStatement();
res = statement.executeQuery(sql);
if (res.next()) {
runOnUiThread(new Runnable() {
@Override
public void run() {
Toast.makeText(MainActivity.this, "登录成功", Toast.LENGTH_SHORT).show();
Intent intent = new Intent(MainActivity.this, student.class);
startActivity(intent);
finish();
}
});
} else {
runOnUiThread(new Runnable() {
@Override
public void run() {
Toast.makeText(MainActivity.this, "学号或密码错误", Toast.LENGTH_SHORT).show();
}
});
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
JDBCutils.close(res, statement, conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
这里用到了lambda表达式,需要在build.gradle(Project)添加一句话
classpath ‘me.tatarka:gradle-retrolambda:3.7.1’
dependencies {
classpath 'com.android.tools.build:gradle:3.5.3'
classpath 'me.tatarka:gradle-retrolambda:3.7.1'
// NOTE: Do not place your application dependencies here; they belong
// in the individual module build.gradle files
}
由于需要远程连接数据库,所以需要联网权限。
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.test">
<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=".student"/>
<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>
自己编写一个sudent类和student.xml,我这里只是做个测试。
sudent.class
import android.app.Activity;
import android.os.Bundle;
public class student extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.student);
}
}
sudent.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
xmlns:tools="http://schemas.android.com/tools"
tools:context=".student">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="student"/>
</LinearLayout>
到这里就结束了