Android-studio 学生管理系统数据库的使用(云数据库与本地数据库的上传,下载)
功能
- 实现本地数据库sqlite的增删改查
- 实现云数据库的增删改查(腾讯云为例)
- 实现上传,下载。
云数据库的连接
1.AndroidManifest.xml中添加
<uses-permission android:name="android.permission.INTERNET" />
2.连接数据库语句
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://(云数据库的外网地址):61963/(数据库名)?+(时区)";
String name =“数据库登录账号”;
String passwd =”数据库登录密码“;
Connection connection = DriverManager.getConnection(url,name,passwd);
3.导入mysql-connector-java-5.1.23-bin.jar
代码
布局(.xml)
登录页面(activity_main.xml)
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android/apk/res/android"
xmlns:app="http://schemas.android/apk/res-auto"
xmlns:tools="http://schemas.android/tools"
android:layout_width="match_parent"
android:orientation="vertical"
android:layout_height="match_parent"
android:background="#7DD0F3"
tools:context=".MainActivity">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="学生管理"
android:textStyle="bold"
android:textColor="#385AC3"
android:textSize="15pt"
android:paddingLeft="60pt"/>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_stuId"
android:hint="请输入学号"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_stuName"
android:hint="请输入姓名"></EditText>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<Button
android:layout_width="wrap_content"
android:id="@+id/btn_login1"
android:text="登录本地数据库"
android:layout_height="wrap_content"></Button>
<Button
android:layout_width="wrap_content"
android:id="@+id/btn_login2"
android:text="登录云数据库"
android:layout_height="wrap_content"></Button>
</LinearLayout>
</LinearLayout>
云端界面(yunpage.xml)
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android/apk/res/android"
xmlns:app="http://schemas.android/apk/res-auto"
xmlns:tools="http://schemas.android/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".YunpageActivity">
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入学号"
android:id="@+id/et_stuId1"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_stuName1"
android:hint="请输入姓名"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_major"
android:hint="请输入专业"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_class"
android:hint="请输入班级"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/et_grade"
android:hint="请输入成绩"></EditText>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btn_show"
android:text="显示"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="插入"
android:id="@+id/btn_insert"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除"
android:id="@+id/btn_del"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="修改"
android:id="@+id/btn_change"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/btn_person"
android:text="查询个人信息"></Button>
<Button
android:layout_width="wrap_content"
android:id="@+id/btn_download"
android:text="下载"
android:layout_height="wrap_content"></Button>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/tv_show"></TextView>
<ListView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/list_view"/>
</LinearLayout>
本地界面(localpage.xml)
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android/apk/res/android"
xmlns:app="http://schemas.android/apk/res-auto"
xmlns:tools="http://schemas.android/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".LocalpageActivity">
<ListView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/list_view"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="新建"
android:id="@+id/btn_new_note"/>
</LinearLayout>
本地修改数据与上传界面(new_local_page.xml)
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android/apk/res/android"
xmlns:app="http://schemas.android/apk/res-auto"
xmlns:tools="http://schemas.android/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".NewLocalPageActivity">
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入学号"
android:id="@+id/et_title">
</EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入姓名"
android:id="@+id/et_content"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入专业"
android:id="@+id/et_local_major"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入班级"
android:id="@+id/et_local_class"></EditText>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入成绩"
android:id="@+id/et_local_grade"></EditText>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="提交"
android:id="@+id/btn_submit"></Button>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="上传"
android:id="@+id/btn_unload"></Button>
</LinearLayout>
本地数据库显示信息界面(listview_item.xml)
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android/apk/res/android"
android:layout_width="match_parent"
android:orientation="vertical"
android:layout_height="match_parent">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/tv_250"
android:text="id 学号 姓名 专业 班级 成绩"></TextView>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="10pt"
android:textColor="#55A1F3"
android:id="@+id/tv_id"></TextView>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="7pt"
android:paddingLeft="40px"
android:id="@+id/tv_title">
</TextView>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="7pt"
android:layout_marginLeft="40px"
android:id="@+id/tv_content"></TextView>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/tv_major"
android:textSize="7pt"
android:layout_marginLeft="40px"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/tv_class"
android:textSize="7pt"
android:layout_marginLeft="40px"/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/tv_grade"
android:textSize="7pt"
android:layout_marginLeft="40px"/>
</LinearLayout>
</LinearLayout>
java文件
主页java文件(MainActivity)
package com.example.mainfest.studentinformation;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MainActivity extends AppCompatActivity {
Button btn_login;
Button btn_login1;
StringBuilder str_name;
EditText et_stuId;
EditText et_stuName;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
et_stuId = (EditText) findViewById(R.id.et_stuId);
et_stuName = (EditText) findViewById(R.id.et_stuName);
btn_login=(Button) findViewById(R.id.btn_login2);
btn_login1=(Button) findViewById(R.id.btn_login1);
btn_login1.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
if (et_stuId.getText().toString().equals("")||et_stuName.getText().toString().equals("")){
Log.d("result","用户名或密码不能为空");
Toast.makeText(MainActivity.this,"用户名或密码不能为空",Toast.LENGTH_SHORT).show();
}else if (et_stuId.getText().toString().equals("2021001")&&et_stuName.getText().toString().equals("***")) {
Intent intent = new Intent(MainActivity.this, LocalpageActivity.class);
startActivity(intent);
}else {
Log.d("result","登陆失败");
Toast.makeText(MainActivity.this,"用户名或密码错误",Toast.LENGTH_SHORT).show();
}
}
});
btn_login.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
str_name = new StringBuilder();
new Thread(new Runnable() {
@Override
public void run() {
String stuId = et_stuId.getText().toString();
String stuName = et_stuName.getText().toString();
if (et_stuName.getText().toString().equals("")||et_stuId.getText().toString().equals("")){
Log.d("result","用户名或密码不能为空");
}
else if (et_stuId.getText().toString().equals("2021001")&&et_stuName.getText().toString().equals("***")){
Intent intent=new Intent(MainActivity.this,YunpageActivity.class);
startActivity(intent);
}else {
Log.d("result","登陆失败");
}
}
}).start();
}
});
}
}
云端数据库界面java文件(YunpageActivity)
package com.example.mainfest.studentinformation;
import androidx.appcompat.app.AppCompatActivity;
import android.content.ContentValues;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class YunpageActivity extends AppCompatActivity {
DBHelper dbHelper=new DBHelper(YunpageActivity.this,"MyDatabase.db",1);
StringBuilder str_name;
TextView tv_name;
EditText et_stuName1;
EditText et_stuId1;
EditText et_class;
EditText et_major;
EditText et_grade;
String stuYunid = "";
String stuYunname = "";
String stuYunmajor = "";
String stuYunclass = "";
String stuYungrade = "";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.yunpage);
et_stuName1=(EditText) findViewById(R.id.et_stuName1);
et_stuId1=(EditText) findViewById(R.id.et_stuId1);
et_class=(EditText) findViewById(R.id.et_class);
et_major=(EditText) findViewById(R.id.et_major);
et_grade=(EditText) findViewById(R.id.et_grade);
tv_name = (TextView) findViewById(R.id.tv_show);
Button btn_show =(Button) findViewById(R.id.btn_show);
Button btn_insert=(Button) findViewById(R.id.btn_insert);
Button btn_del=(Button) findViewById(R.id.btn_del);
Button btn_change=(Button) findViewById(R.id.btn_change);
Button btn_person=(Button) findViewById(R.id.btn_person);
Button btn_download=(Button)findViewById(R.id.btn_download);
btn_download.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
new Thread(new Runnable() {
@Override
public void run() {
try {
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://cd-cdb-9i92yj3g.sql.tencentcdb:61963/studentInformation?userverTimezone=Asia/Shanghai";
String name = "root";
String passwd = "hjc20001023";
Connection connection = DriverManager.getConnection(url, name, passwd);
String sql = "SELECT*FROM`text2`";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
rs = preparedStatement.executeQuery(sql);
if (rs.first()) {
dbHelper.deleteAll("Text2");
do {
stuYunid = rs.getString("stuid");
stuYunname = rs.getString("stuname");
stuYunmajor = rs.getString("major");
stuYunclass = rs.getString("class");
stuYungrade = rs.getString("grade");
ContentValues contentValues = new ContentValues();
contentValues.put("stuid", stuYunid);
contentValues.put("stuname", stuYunname);
contentValues.put("major", stuYunmajor);
contentValues.put("class", stuYunclass);
contentValues.put("grade", stuYungrade);
dbHelper.insertdown("Text2", contentValues);
} while (rs.next());
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}).start();
}
});
btn_change.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
final String stu_name = et_stuName1.getText().toString();
final String stu_id = et_stuId1.getText().toString();
final String stu_class = et_class.getText().toString();
final String stu_major= et_major.getText().toString();
final String stu_grade = et_grade.getText().toString();
new Thread(new Runnable() {
@Override
public void run() {
try{
String sql = "update text2 set stuname='"+stu_name+"' where stuid='"+stu_id+"'";
update(sql);
runOnUiThread(new Runnable() {
@Override
public void run() {
btn_show.performClick();
}
});
} catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}
try{
String sql = "update text2 set class='"+stu_class+"' where stuid='"+stu_id+"'";
update(sql);
runOnUiThread(new Runnable() {
@Override
public void run() {
btn_show.performClick();
}
});
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try{
String sql = "update text2 set major='"+stu_major+"' where stuid='"+stu_id+"'";
update(sql);
runOnUiThread(new Runnable() {
@Override
public void run() {
btn_show.performClick();
}
});
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try{
String sql = "update text2 set grade='"+stu_grade+"' where stuid='"+stu_id+"'";
update(sql);
runOnUiThread(new Runnable() {
@Override
public void run() {
btn_show.performClick();
}
});
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}).start();
}
});
btn_del.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
final String del_id = et_stuId1.getText().toString();
new Thread(new Runnable(){
@Override
public void run(){
try{
if (del_id.equals("2021001")){
Log.d("result","管理员无法删除");
}
else {
String sql = "delete from text2 where stuid='"+del_id+"'";
update(sql);}
runOnUiThread(new Runnable() {
@Override
public void run() {
btn_show.performClick();
}
});
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}
}
}).start();
}
});
btn_insert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String new_id = et_stuId1.getText().toString();
String new_name = et_stuName1.getText().toString();
String new_class = et_class.getText().toString();
String new_major = et_major.getText().toString();
String new_result = et_grade.getText().toString();
new Thread(new Runnable() {
@Override
public void run() {
try {
if (query("select * from text2 where stuid='"+new_id+"'").next()){
Log.d("result","学号已存在");
}else {
String sql = "insert into text2(stuid,stuname,class,major,grade) values ('" + new_id + "','" + new_name + "','" + new_class + "','" + new_major + "','" + new_result + "')";
update(sql);}
runOnUiThread(new Runnable() {
@Override
public void run() {
btn_show.performClick();
}
});
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e){
e.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}
}
}).start();
}
});
btn_show.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
str_name = new StringBuilder();
new Thread(new Runnable() {
@Override
public void run() {
try {
String sql = "select * from text2";
ResultSet resultSet = query(sql);
final List<String> names=new ArrayList<String>(){};
str_name.append("学号 姓名 班级 专业 成绩\n");
int i=0;
while (resultSet.next()) {
String str=resultSet.getString("stuid") + " " + resultSet.getString("stuname")+" "+ resultSet.getString("class") +" "+ resultSet.getString("major")+" "+ resultSet.getString("grade")+"\n";
names.add(str);
str_name.append("\n");
}
runOnUiThread(new Runnable() {
@Override
public void run() {
tv_name.setText(str_name.toString());
ListView listView=(ListView) findViewById(R.id.list_view);
ArrayAdapter<String> adapter=new ArrayAdapter<String>(YunpageActivity.this, android.R.layout.simple_list_item_1,names);
listView.setAdapter(adapter);
}
});
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}
}
}).start();
}
});
btn_person.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
final String query_id = et_stuId1.getText().toString();
str_name = new StringBuilder();
new Thread(new Runnable() {
@Override
public void run() {
try {
String sql = "select * from text2 where stuid='"+query_id+"'";
ResultSet resultSet = query(sql);
final List<String> names=new ArrayList<String>(){};
str_name.append("学号 姓名 班级 专业 成绩\n");
int i=0;
while (resultSet.next()) {
String str=resultSet.getString("stuid") + " " + resultSet.getString("stuname")+" "+ resultSet.getString("class") +" "+ resultSet.getString("major")+" "+ resultSet.getString("grade")+"\n";
names.add(str);
}
runOnUiThread(new Runnable() {
@Override
public void run() {
tv_name.setText(str_name.toString());
ListView listView=(ListView) findViewById(R.id.list_view);
ArrayAdapter<String> adapter=new ArrayAdapter<String>(YunpageActivity.this, android.R.layout.simple_list_item_1,names);
listView.setAdapter(adapter);
}
});
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}
}
}).start();
}
});
}
public ResultSet query(String sql) throws ClassNotFoundException,SQLException{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://(外网地址):61963/数据库名?userverTimezone=域名";
String name = "数据库登录账号";
String passwd = "数据库登录密码";
Connection connection = DriverManager.getConnection(url,name,passwd);
PreparedStatement pre = connection.prepareStatement(sql);
ResultSet resultSet = pre.executeQuery();
return resultSet;
}
public void update(String sql) throws ClassNotFoundException,SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://(外网地址):61963/数据库名?userverTimezone=域名";
String name = "数据库登录账号";
String passwd = "数据库登录密码";
Connection connection = DriverManager.getConnection(url, name, passwd);
PreparedStatement pre = connection.prepareStatement(sql);
pre.executeUpdate();
pre.close();
connection.close();
}
}
本地数据库主界面java文件(LocalpageActivity)
package com.example.mainfest.studentinformation;
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;
import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.Toast;
import java.text.SimpleDateFormat;
import java.util.Date;
public class LocalpageActivity extends AppCompatActivity {
DBHelper dbHelper=new DBHelper(LocalpageActivity.this,"MyDatabase.db",1);
ListView listView;
final int NEW_NOTE=1;
final int EDIT_NOTE=2;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.localpage);
listView=(ListView) findViewById(R.id.list_view);
renderListView();
Button btn_new=(Button) findViewById(R.id.btn_new_note);
btn_new.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Intent intent=new Intent(LocalpageActivity.this,NewLocalPageActivity.class);
startActivityForResult(intent,NEW_NOTE);
}
});
listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int i, long id) {
Cursor cursor= (Cursor) listView.getItemAtPosition(i);
@SuppressLint("Range") String title=cursor.getString(cursor.getColumnIndex("stuid"));
@SuppressLint("Range") String content=cursor.getString(cursor.getColumnIndex("stuname"));
@SuppressLint("Range") String major=cursor.getString(cursor.getColumnIndex("major"));
@SuppressLint("Range") String classs=cursor.getString(cursor.getColumnIndex("class"));
@SuppressLint("Range") String grade=cursor.getString(cursor.getColumnIndex("grade"));
Intent intent = new Intent(LocalpageActivity.this,NewLocalPageActivity.class);
intent.putExtra("stuid",title);
intent.putExtra("stuname",content);
intent.putExtra("major",major);
intent.putExtra("class",classs);
intent.putExtra("grade",grade);
startActivityForResult(intent,EDIT_NOTE);
}
});
listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
@Override
public boolean onItemLongClick(AdapterView<?> adapterView, View view, int i, long l) {
Cursor cursor=(Cursor) listView.getItemAtPosition(i);
@SuppressLint("Range") String title=cursor.getString(cursor.getColumnIndex("stuid"));
AlertDialog.Builder builder= new AlertDialog.Builder(LocalpageActivity.this);
builder.setTitle("删除数据·").setMessage("确认删除"+title+"?");
builder.setPositiveButton("确定", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
dbHelper.deleteNotes(title);
renderListView();
}
});
builder.setNegativeButton("取消", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialogInterface, int i) {
dialogInterface.dismiss();
}
});
builder.show();
return true;
}
});
}
protected void renderListView(){
dbHelper.getWritableDatabase();
Cursor cursor=dbHelper.queryAll("Text2");
String from[]=new String[]{"_id","stuid","stuname","major","class","grade"};
int to[]=new int[]{R.id.tv_id,R.id.tv_title,R.id.tv_content,R.id.tv_major,R.id.tv_class,R.id.tv_grade};
SimpleCursorAdapter adapter=new SimpleCursorAdapter(LocalpageActivity.this,R.layout.listview_item,cursor,from,to,0);
listView.setAdapter(adapter);
}
@Override
protected void onActivityResult(int requestCode,int resultCode,Intent intent){
super.onActivityResult(requestCode,resultCode,intent);
if (resultCode==RESULT_OK){
String title=intent.getStringExtra("stuid");
String content=intent.getStringExtra("stuname");
String major=intent.getStringExtra("major");
String classs=intent.getStringExtra("class");
String grade=intent.getStringExtra("grade");
String old_title=intent.getStringExtra("old_title");
ContentValues values=new ContentValues();
values.put("stuid",title);
values.put("stuname",content);
values.put("major",major);
values.put("class",classs);
values.put("grade",grade);
switch (requestCode){
case NEW_NOTE:
dbHelper.insert(values);
break;
case EDIT_NOTE:
dbHelper.updateNotes(old_title,values);
break;
}
}
renderListView();
}
}
本地数据库修改信息与上传界面java文件(NewLocalPageActivity)
package com.example.mainfest.studentinformation;
import androidx.appcompat.app.AppCompatActivity;
import android.annotation.SuppressLint;
import android.content.Intent;
import android.database.AbstractCursor;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class NewLocalPageActivity extends AppCompatActivity {
EditText et_title;
EditText et_content;
EditText et_local_major;
EditText et_local_class;
EditText et_local_grade;
Cursor cursor;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.new_local_page);
et_title=(EditText) findViewById(R.id.et_title);
et_content=(EditText) findViewById(R.id.et_content);
et_local_major=(EditText) findViewById(R.id.et_local_major);
et_local_class=(EditText) findViewById(R.id.et_local_class);
et_local_grade=(EditText) findViewById(R.id.et_local_grade);
Intent oldIntent=getIntent();
final String title=oldIntent.getStringExtra("stuid");
final String content=oldIntent.getStringExtra("stuname");
final String local_major=oldIntent.getStringExtra("major");
final String local_class=oldIntent.getStringExtra("class");
final String local_grade=oldIntent.getStringExtra("grade");
et_title.setText(title);
et_content.setText(content);
et_local_major.setText(local_major);
et_local_class.setText(local_class);
et_local_grade.setText(local_grade);
Button btn_upload=(Button)findViewById(R.id.btn_unload);
btn_upload.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
final String stu_upid =et_title.getText().toString();
final String stu_upname =et_content.getText().toString();
final String stu_upmajor =et_local_major.getText().toString();
final String stu_upclass =et_local_class.getText().toString();
final String stu_upgrade =et_local_grade.getText().toString();
new Thread(new Runnable() {
@Override
public void run() {
try {
if (query("select * from text2 where stuid='"+stu_upid+"'").next()){
Log.d("result","学号已存在");
}else {
String sql = "insert into text2(stuid,stuname,class,major) values ('"+stu_upid+"','"+stu_upname+"','"+stu_upclass+"','"+stu_upmajor+"')";
update(sql);}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e){
e.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}
}
}).start();
}
});
Button btn_submit=(Button) findViewById(R.id.btn_submit);
btn_submit.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
;
Intent intent = new Intent();
intent.putExtra("old_title", title);
intent.putExtra("stuid", et_title.getText().toString());
intent.putExtra("stuname", et_content.getText().toString());
intent.putExtra("major", et_local_major.getText().toString());
intent.putExtra("class", et_local_class.getText().toString());
intent.putExtra("grade", et_local_grade.getText().toString());
setResult(RESULT_OK, intent);
finish();
}
});
}
public ResultSet query(String sql) throws ClassNotFoundException,SQLException{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://(外网地址):61963/数据库名?userverTimezone=域名";
String name = "数据库登录账号";
String passwd = "数据库登录密码";
Connection connection = DriverManager.getConnection(url,name,passwd);
PreparedStatement pre = connection.prepareStatement(sql);
ResultSet resultSet = pre.executeQuery();
return resultSet;
}
public void update(String sql) throws ClassNotFoundException,SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://(外网地址):61963/数据库名?userverTimezone=域名";
String name = "数据库登录账号";
String passwd = "数据库登录密码";
Connection connection = DriverManager.getConnection(url, name, passwd);
PreparedStatement pre = connection.prepareStatement(sql);
pre.executeUpdate();
pre.close();
connection.close();
}
}
创建本地数据库(DBHelper)
package com.example.mainfest.studentinformation;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
public class DBHelper extends SQLiteOpenHelper {
final String CREATE_TABLE2="create table Text2(_id integer primary key autoincrement,stuid int,stuname varchar(40),major varchar(50),class varchar(40),grade int(20))";
Context context;
public DBHelper(Context context, String dbname, int version){
super(context,dbname,null,version);
this.context=context;
}
@Override
public void onCreate(SQLiteDatabase db){
db.execSQL(CREATE_TABLE2);
Toast.makeText(context,"创建成功",Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
db.execSQL("drop table if exists Test2");
onCreate(db);
}
public void insert(ContentValues values){
SQLiteDatabase db=getWritableDatabase();
db.insert("Text2",null,values);
db.close();
}
public void insertdown(String table, ContentValues values) {
SQLiteDatabase db = getWritableDatabase();
db.insert(table, null, values);
}
public Cursor queryAll(String dbname){
SQLiteDatabase db=getWritableDatabase();
Cursor cursor=db.query(dbname,null,null,null,null,null,null);
return cursor;
}
public boolean querystuid(String stuid) {
SQLiteDatabase db = getWritableDatabase();
Cursor cursor = db.query("Text2", null, "stuid=?",new String[]{stuid}, null, null, null);
// Cursor cursor=db.rawQuery("select * from text where content='"+str+"'",null);
if (cursor.moveToFirst()){
return true;}
else{
return false;}
}
public boolean queryUserByName(String stuid){
SQLiteDatabase db=getWritableDatabase();
Cursor cursor=db.query("Text2",null,"stuid=?",new String[]{stuid},null,null,null);
// Cursor cursor=db.rawQuery("select * from text where content='"+str+"'",null);
if (cursor.moveToFirst()){
return true;}
else{
return false;}
}
public void deleteNotes(String stuid){
SQLiteDatabase db=getWritableDatabase();
db.delete("Text2","stuid=?",new String[]{stuid});
db.close();
}
public void deleteAll(String table) {
SQLiteDatabase db = getWritableDatabase();
db.delete(table, null,null);
}
public void updateNotes(String stuid,ContentValues values){
SQLiteDatabase db=getWritableDatabase();
db.update("Text2",values,"stuid=?",new String[]{stuid});
}
}
活动截图
1.主页面(登录界面)
2.云端数据库显示,管理,下载界面
3.本地数据库显示界面
4.本地数据库管理与上传界面
更多推荐
Android-studio 学生管理系统数据库的使用(云数据库与本地数据库的增删改查,上传,下载)
发布评论