小贝子编程

sqlitedatabase update where clauses



>我正在从其他类获取字符串类别和地址名称 我需要的是使用特定address_id更新类别表，这是地址表的外键。地址表包含address_name。 所以我从其他类中得到address_name，我想更新具有给定address_name的特定address_id的类别表，并将其类别名称更改为字符串类别。

这是我尝试过的代码，但它不起作用，它在 Android Studio 中没有给出错误，但它实际上并没有在数据库中更新。

public void updateData(String categories, String positionName){
ContentValues contentValues = new ContentValues();
contentValues.put("categories_name", categories);
Log.d(TAG, "updateData: " + categories );
database.update("categories", contentValues, "address_id = ?", new String[]{"(SELECT address_id FROM address WHERE address_name = " + positionName + ")"});

这是我的地址表

CREATE TABLE "address" (
"address_id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"address_name"  TEXT UNIQUE,
"lat"   BLOB NOT NULL,
"lng"   BLOB NOT NULL,
"date"  NUMERIC);

这是我的类别表

CREATE TABLE "categories" (
"Categories_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"categories_name"   TEXT NOT NULL DEFAULT 'place' CHECK(categories_name in ('place','home','work')),
"address_id"    INTEGER NOT NULL UNIQUE,
FOREIGN KEY("address_id") REFERENCES "address"("address_id"));

public class EditProfile extensions AppCompatActivity {

Button searchBtn;
EditText userName_editText;
EditText password_editText;
EditText dob_editText;
RadioGroup genderRadioGroup;
RadioButton genderRadioBtn;
Button editBtn;
Button deleteBtn;
Intent intent;
DBHandler dbHandler;
public static final String USERID_EDITPROFILE = "userID";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_edit_profile);
searchBtn = (Button)findViewById(R.id.editprof_searchbtn);
userName_editText = (EditText)findViewById(R.id.editprof_userName);
password_editText = (EditText)findViewById(R.id.editprof_password);
dob_editText = (EditText)findViewById(R.id.editprof_dob);
genderRadioGroup = (RadioGroup)findViewById(R.id.editprof_radiogroup);
editBtn = (Button)findViewById(R.id.editprof_editbtn);
deleteBtn = (Button)findViewById(R.id.editprof_deletebtn);
intent = getIntent();

dbHandler = new DBHandler(EditProfile.this);
setUserDetails();
deleteBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String username = userName_editText.getText().toString();
if(username == null){
Toast.makeText(EditProfile.this,"Please enter username to delete your profile",Toast.LENGTH_SHORT).show();
}
else{
UserProfile.Users users = dbHandler.readAllInfor(username);
if(users == null){
Toast.makeText(EditProfile.this,"No profile found from this username, please enter valid username",Toast.LENGTH_SHORT).show();
}
else{
dbHandler.deleteInfo(username);
Intent redirectintent_home = new Intent("com.modelpaper.mad.it17121002.Home");
startActivity(redirectintent_home);
}
}
}
});
editBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String userID_String = intent.getStringExtra(Home.USERID);
if(userID_String == null){
Toast.makeText(EditProfile.this,"Error!!",Toast.LENGTH_SHORT).show();
Intent redirectintent_home =  new Intent(getApplicationContext(),Home.class);
startActivity(redirectintent_home);
}
int userID = Integer.parseInt(userID_String);
String username = userName_editText.getText().toString();
String password = password_editText.getText().toString();
String dob = dob_editText.getText().toString();
int selectedGender = genderRadioGroup.getCheckedRadioButtonId();
genderRadioBtn = (RadioButton)findViewById(selectedGender);
String gender = genderRadioBtn.getText().toString();
UserProfile.Users users = UserProfile.getProfile().getUser();
users.setUsername(username);
users.setPassword(password);
users.setDob(dob);
users.setGender(gender);
users.setId(userID);
dbHandler.updateInfor(users);
Toast.makeText(EditProfile.this,"Updated Successfully",Toast.LENGTH_SHORT).show();
Intent redirectintent_home = new Intent(getApplicationContext(),Home.class);
startActivity(redirectintent_home);
}
});
searchBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String username = userName_editText.getText().toString();
if (username == null){
Toast.makeText(EditProfile.this,"Please enter a username",Toast.LENGTH_SHORT).show();
}
else{
UserProfile.Users users_search = dbHandler.readAllInfor(username);

if(users_search == null){
Toast.makeText(EditProfile.this,"Please enter a valid username",Toast.LENGTH_SHORT).show();
}
else{
userName_editText.setText(users_search.getUsername());
password_editText.setText(users_search.getPassword());
dob_editText.setText(users_search.getDob());
int id = users_search.getId();
Intent redirectintent = new Intent("com.modelpaper.mad.it17121002.EditProfile");
redirectintent.putExtra(USERID_EDITPROFILE,Integer.toString(id));
startActivity(redirectintent);
}
}
}
});

}
public void setUserDetails(){
String userID_String = intent.getStringExtra(Home.USERID);
if(userID_String == null){
Toast.makeText(EditProfile.this,"Error!!",Toast.LENGTH_SHORT).show();
Intent redirectintent_home = new Intent("com.modelpaper.mad.it17121002.Home");
startActivity(redirectintent_home);
}
int userID = Integer.parseInt(userID_String);
UserProfile.Users users = dbHandler.readAllInfor(userID);
userName_editText.setText(users.getUsername());
password_editText.setText(users.getPassword());
dob_editText.setText(users.getDob());
}

}

************db hander*****

/** * 由 Suraj 于 2018 年 10 月 6 日创建。 */

public class DBHandler 扩展 SQLiteOpenHelper {

public DBHandler(Context context) {
super(context, "user_db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
String createQuery = "CREATE TABLE "+UserProfile.Users.TABLE_NAME +"( "+UserProfile.Users.COL_ID +" INTEGER PRIMARY KEY AUTOINCREMENT 
,"+UserProfile.Users.COL_USERNAME+" TEXT UNIQUE," +
UserProfile.Users.COL_PASSWORD +" TEXT, "+UserProfile.Users.COL_GENDER +" TEXT, "+UserProfile.Users.COL_DOB +" TEXT"+")";
Log.d("createQuery",createQuery);
try {
db.execSQL(createQuery);
}
catch (Exception e){
e.printStackTrace();
Log.e("Exception",e.getMessage());
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
//onUpgardeMethod// String createQuery = "CREATE TABLE "+UserProfile.Users.TABLE_NAME +"( "+UserProfile.Users.COL_ID +" INTEGER PRIMARY KEY AUTO INCREMENT 
,"+UserProfile.Users.COL_USERNAME+" TEXT," +
UserProfile.Users.COL_PASSWORD +" TEXT, "+UserProfile.Users.COL_GENDER +" TEXT, "+UserProfile.Users.COL_DOB+" TEXT"+")";
Log.d("createQuery",createQuery);
try {
db.execSQL(createQuery);
}
catch (Exception e){
e.printStackTrace();
Log.e("Exception",e.getMessage());
}
}

public boolean addInfo(UserProfile.Users users({

SQLiteDatabase db = this.getWritableDatabase();
String insertQuery = "INSERT INTO "+UserProfile.Users.TABLE_NAME+"("+UserProfile.Users.COL_USERNAME+","+UserProfile.Users.COL_PASSWORD+",
"+UserProfile.Users.COL_GENDER+","+
UserProfile.Users.COL_DOB+") VALUES('"+users.getUsername()+"','"+users.getPassword()+"','"+users.getGender()+"','"+users.getDob()+"')";
Log.d("insertQuery",insertQuery);
try {
db.execSQL(insertQuery);
return true;
}
catch (Exception e){
e.printStackTrace();
Log.d("Exception",e.getMessage());
}
db.close();
return false;
}
public boolean updateInfor(UserProfile.Users users){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
String username = users.getUsername();
String password = users.getPassword();
String dob = users.getDob();
String gender = users.getGender();
int id = users.getId();
values.put(UserProfile.Users.COL_DOB,dob);
values.put(UserProfile.Users.COL_GENDER,gender);
values.put(UserProfile.Users.COL_PASSWORD,password);
values.put(UserProfile.Users.COL_USERNAME,username);
int result = db.update(UserProfile.Users.TABLE_NAME,values,UserProfile.Users.COL_ID+" = ?",new String[]{String.valueOf(id)});
if(result >0)
return true;
return false;
}

public ArrayList<UserProfile.Users> readAllInfor(){
ArrayList<UserProfile.Users> userList = new ArrayList<>();
SQLiteDatabase db = this.getWritableDatabase();
String readAllQuery = "SELECT * FROM "+UserProfile.Users.TABLE_NAME;
Cursor cursor = db.rawQuery(readAllQuery,null);
if(cursor.moveToFirst()){
do{
UserProfile.Users users = UserProfile.getProfile().getUser();
users.setId(Integer.parseInt(cursor.getString(0)));
users.setUsername(cursor.getString(1));
users.setPassword(cursor.getString(2));
users.setGender(cursor.getString(3));
users.setDob(cursor.getString(4));
userList.add(users);
}while (cursor.moveToNext());
}
return userList;
}
public UserProfile.Users readAllInfor(String userName){
SQLiteDatabase db = this.getWritableDatabase();
String readSingleQuery = "SELECT * FROM "+ UserProfile.Users.TABLE_NAME+" WHERE "+ UserProfile.Users.COL_USERNAME + " =  '"+ userName+"'";
Cursor cursor = db.rawQuery(readSingleQuery,null);
if(cursor.moveToFirst()){
UserProfile.Users users = UserProfile.getProfile().getUser();
users.setId(Integer.parseInt(cursor.getString(0)));
users.setUsername(cursor.getString(1));
users.setPassword(cursor.getString(2));
users.setGender(cursor.getString(3));
users.setDob(cursor.getString(4));
return users;
}
return null;
}
public UserProfile.Users readAllInfor(int id){
SQLiteDatabase db = this.getWritableDatabase();
String readSingleQuery = "SELECT * FROM "+ UserProfile.Users.TABLE_NAME+" WHERE "+ UserProfile.Users.COL_ID + " =  '"+ id+"'";
Cursor cursor = db.rawQuery(readSingleQuery,null);
if(cursor.moveToFirst()){
UserProfile.Users users = UserProfile.getProfile().getUser();
users.setId(Integer.parseInt(cursor.getString(0)));
users.setUsername(cursor.getString(1));
users.setPassword(cursor.getString(2));
users.setGender(cursor.getString(3));
users.setDob(cursor.getString(4));
return users;
}
return null;
}

public void deleteInfo(String username){
SQLiteDatabase db = this.getWritableDatabase();
String deleteQuery = "DELETE FROM "+ UserProfile.Users.TABLE_NAME+" WHERE "+ UserProfile.Users.COL_USERNAME +" = '"+ username +"' ";
Log.d("deleteQuery ",deleteQuery);
db.execSQL(deleteQuery);
db.close();
}

}

ProfileMangement 

public class ProfileManagement extends AppCompatActivity {
EditText username_editText;
EditText password_editText;
EditText dob_editText;
RadioGroup radioGroup;
RadioButton gender_radioBtn;
Button saveProfBtn;
public final static String USERID_PROFILEMGMT = "userID";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_profile_management);
username_editText = (EditText)findViewById(R.id.profmgmt_userName);
password_editText = (EditText)findViewById(R.id.profmgmt_password);
dob_editText = (EditText)findViewById(R.id.profmgmt_dob);
radioGroup = (RadioGroup)findViewById(R.id.profmgmt_radiogroup);
saveProfBtn = (Button)findViewById(R.id.profmgmt_btn);
final DBHandler dbHandler = new DBHandler(ProfileManagement.this);
saveProfBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String username = username_editText.getText().toString();
String password = password_editText.getText().toString();
String dob = dob_editText.getText().toString();
int selectedGender = radioGroup.getCheckedRadioButtonId();
gender_radioBtn = (RadioButton)findViewById(selectedGender);
String gender = gender_radioBtn.getText().toString();
UserProfile.Users users = UserProfile.getProfile().getUser();
users.setUsername(username);
users.setPassword(password);
users.setDob(dob);
users.setGender(gender);
boolean result = dbHandler.addInfo(users);
if(result ==  true){
Toast.makeText(ProfileManagement.this,"Successfully added",Toast.LENGTH_SHORT).show();
UserProfile.Users newusers = dbHandler.readAllInfor(username);
int userID = newusers.getId();
Intent intent = new Intent("com.modelpaper.mad.it17121002.EditProfile");
intent.putExtra(USERID_PROFILEMGMT,Integer.toString(userID));
startActivity(intent);
}
}
});
}
}

public final class UserProfile {

private UserProfile(){
}
public static UserProfile getProfile(){
UserProfile userProfile = new UserProfile();
return userProfile;
}
class Users implements BaseColumn{
public static final String TABLE_NAME = "UserInfo";
public static final String COL_ID = "_ID";
public static final String COL_USERNAME  = "userName ";
public static final String COL_DOB = "dateOfBirth";
public static final String COL_GENDER = "Gender";
public static final String COL_PASSWORD = "Password";
private int id;
private String username;
private String dob;
private String gender;
private String password;

public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getDob() {
return dob;
}
public void setDob(String dob) {
this.dob = dob;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
public Users getUser(){
Users users = new Users();
return users;
}

}

public class Home 扩展 AppCompatActivity {

EditText username_editText;
EditText password_editText;
Button loginbtn;
Button registerbtn;
public static final String USERID = "userID";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_home);
username_editText = (EditText)findViewById(R.id.home_userName);
password_editText = (EditText)findViewById(R.id.home_password);
loginbtn = (Button)findViewById(R.id.home_loginBtn);
registerbtn = (Button)findViewById(R.id.home_registerBtn);
final DBHandler dbHandler = new DBHandler(Home.this);
registerbtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Intent intent = new Intent("com.modelpaper.mad.it17121002.ProfileManagement");
startActivity(intent);
}
});
loginbtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String userName = username_editText.getText().toString();
String password = password_editText.getText().toString();
if(userName == null){
Toast.makeText(Home.this,"Login Unsuccessful",Toast.LENGTH_SHORT).show();
}
else{
UserProfile.Users users = dbHandler.readAllInfor(userName);
if(users == null){
Toast.makeText(Home.this,"Invalid username or password",Toast.LENGTH_SHORT).show();
}
else{
int userID = users.getId();
Intent editProfIntent = new Intent("com.modelpaper.mad.it17121002.EditProfile");
editProfIntent.putExtra(USERID,Integer.toString(userID));
startActivity(editProfIntent);
}
}
}
});
}

}

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium