分享程序员开发的那些事...
更新时间：2022-12-26 18:13:33
已添加到问题的一行显示为
The stack-trace that has been added to the question has a line that says
java.lang.IllegalStateException：由于连接池已关闭，无法执行此操作。
这是说连接（可能有一个数字，因此连接池）已关闭。该连接是与数据库的连接。 It is closed because you have specifically closed it using EmpDept.close().
This is saying that the connection(s as there may be a number, hence the connection pool) has been closed. The connection being the connection(s) to the database. It is closed because you have specifically closed it using EmpDept.close().
When a Cursor is created, it doesn’t actually contain the data, rather it reads the data from the database when that data is needed. If the database has been closed then it cannot get the data.
When a Cursor is created, it doesn't actually contain the data, rather it reads the data from the database when that data is needed. If the database has been closed then it cannot get the data.
e.g. you have :-
e.g. you have :-
cur.moveToFirst();
EmpDept.close(); //<<<<<<<<<< Database connection closed
return cur;
In short you cannot close the database connection and then access data in the Cursor.
In short you cannot close the database connection and then access data in the Cursor.
Here’s a complete working solution that doesn’t include the above flaw nor other flaws (some indicated by comments).
Here's a complete working solution that doesn't include the above flaw nor other flaws (some indicated by comments).
First a new Class named Employee in Employee.java
First a new Class named Employee in Employee.java
public class Employee {
private long employeeId;
private String employeeName;
private String employeeTitle;
private String employeePhone;
private String employeeEmail;
private long employeeDeptId;
private String employeeDeptName;
public Employee(
long id,
String name,
String title,
String phone,
String email,
long deptid,
String deptname
) {
this.employeeId = id;
this.employeeName = name;
this.employeeTitle = title;
this.employeePhone = phone;
this.employeeEmail = email;
this.employeeDeptId = deptid;
this.employeeDeptName = deptname;
}
public Employee() {
this(-1L,"","","","",-1L,"");
}
public Employee(String name, String title, String phone, String email) {
this(-1L,name,title,phone,email,-1L,"");
}
public long getEmployeeId() {
return employeeId;
}
public void setEmployeeId(long employeeId) {
this.employeeId = employeeId;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public String getEmployeeTitle() {
return employeeTitle;
}
public void setEmployeeTitle(String employeeTitle) {
this.employeeTitle = employeeTitle;
}
public String getEmployeePhone() {
return employeePhone;
}
public void setEmployeePhone(String employeePhone) {
this.employeePhone = employeePhone;
}
public String getEmployeeEmail() {
return employeeEmail;
}
public void setEmployeeEmail(String employeeEmail) {
this.employeeEmail = employeeEmail;
}
public long getEmployeeDeptId() {
return employeeDeptId;
}
public void setEmployeeDeptId(long employeeDeptId) {
this.employeeDeptId = employeeDeptId;
}
public String getEmployeeDeptName() {
return employeeDeptName;
}
public void setEmployeeDeptName(String employeeDeptName) {
this.employeeDeptName = employeeDeptName;
}
public boolean ifFullyUseableEmployee() {
return this.employeeId < 1 || this.employeeDeptId < 1 || this.employeeDeptName.length() < 1;
}
@Override
public String toString() {
return this.employeeTitle + " " + this.employeeName;
}
}
A perhaps new class for the DatabaseHelper namely DeptDPhelper.java
A perhaps new class for the DatabaseHelper namely DeptDPhelper.java
public class DeptDPHelper extends SQLiteOpenHelper {
SQLiteDatabase EmpDept;
//<<<<<<<<<< BAD PRACTICE TO HARD CODE TABLE AND COLUMN NAMES THROUGHOUT >>>>>>>>>>
//<<<<<<<<<< INSTEAD USE CONSTANTS >>>>>>>>>>>
public static final String DBNAME = "EmpDept";
public static final int DBVERSION = 2;
public static final String DEPARTMENT_TABLE = "department";
public static final String EMPLOYEE_TABLE = "employee"; //<<<<<<<<<< not Employee to be consistent with naming connvention
public static final String DEPARTMENT_DEPTID_COLUMN = "departmenttid";
public static final String DEPARTMENT_NAME_COLUMN = "departmentname";
public static final String EMPLOYEE_EMPID_COLUMN = "employeeid";
public static final String EMPLOYEE_NAME_COLUMN = "employeename";
public static final String EMPLOYEE_TITLE_COLUMN= "employeetitle";
public static final String EMPLOYEE_PHONE_COLUMN = "employeephone";
public static final String EMPLOYEE_EMAIL_COLUMN = "employeeemail";
public static final String EMPLOYEE_DEPT_COLUMN = "employeeedepartmentid";
public DeptDPHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
EmpDept = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String crt_dept_table_sql = "CREATE TABLE IF NOT EXISTS " + DEPARTMENT_TABLE + "(" +
DEPARTMENT_DEPTID_COLUMN + " INTEGER PRIMARY KEY, " + //<<<<<<<<<< AUTOINCREMENT INEFFICIENT NOT NEEDED
DEPARTMENT_NAME_COLUMN + " TEXT" +
")";
db.execSQL(crt_dept_table_sql);
String crt_employee_table_sql = "CREATE TABLE IF NOT EXISTS " + EMPLOYEE_TABLE + "(" +
EMPLOYEE_EMPID_COLUMN + " INTEGER PRIMARY KEY, " +
EMPLOYEE_NAME_COLUMN + " TEXT NOT NULL, " +
EMPLOYEE_TITLE_COLUMN + " TEXT NOT NULL, " +
EMPLOYEE_PHONE_COLUMN + " TEXT NOT NULL, " +
EMPLOYEE_EMAIL_COLUMN + " TEXT NOT NULL, " +
EMPLOYEE_DEPT_COLUMN + " INTEGER REFERENCES " + DEPARTMENT_TABLE + "(" +
DEPARTMENT_DEPTID_COLUMN +
")" +
")";
db.execSQL(crt_employee_table_sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//<<<<<<<<<< IF USING FOREIGN KEYS AND SUPPORT IS ON THEN CANNOT DELETE PARENT TABLE FIRST >>>>>>>>>>
//<<<<<<<<<< AS SUCH DEPARTMENT TABLE MUST BE DROPPED FIRST OTHEWISE THERE WILL BE CONFLICTS >>>>>>>>>>
db.execSQL("DROp TABLE IF EXISTS " + EMPLOYEE_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + DEPARTMENT_TABLE);
onCreate(db);
}
//<<<<<<<<<< FOREIGN KEY SUPPORT IS TUREND OFF BY DEFAULT SO TURN THEM ON >>>>>>>>>>
//<<<<<<<<<< IF NOT USELESS CODING FOREIGN KEYS >>>>>>>>>>
@Override
public void onConfigure(SQLiteDatabase db) {
db.setForeignKeyConstraintsEnabled(true);
super.onConfigure(db);
}
public long insertEmployee(String name, String title, String phone, String email, long deptid) {
//<<<<<<<<<< optional check if deptid references a deptartment >>>>>>>>>>
//<<<<<<<<<< however INSERT OR IGNORE doesn't ignore FK conflicts >>>>>>>>>>
if (!ifDepertmentExists(deptid)) return -1;
ContentValues cv = new ContentValues();
cv.put(EMPLOYEE_NAME_COLUMN,name);
cv.put(EMPLOYEE_TITLE_COLUMN,title);
cv.put(EMPLOYEE_PHONE_COLUMN,phone);
cv.put(EMPLOYEE_EMAIL_COLUMN,email);
cv.put(EMPLOYEE_DEPT_COLUMN,deptid);
return EmpDept.insert(EMPLOYEE_TABLE,name,cv);
}
public long insertDepartment(String name) {
ContentValues cv = new ContentValues();
cv.put(DEPARTMENT_NAME_COLUMN,name);
return EmpDept.insert(DEPARTMENT_TABLE,null,cv);
}
public Cursor selectLikeEmployeesByNamePart(String namepart) {
String[] columns = new String[]{EMPLOYEE_NAME_COLUMN};
String whereclause = EMPLOYEE_NAME_COLUMN + "LIKE ?";
String[] whereargs = new String[] {"%" + namepart +"%"};
return EmpDept.query(EMPLOYEE_TABLE,columns,whereclause,whereargs,null,null,EMPLOYEE_NAME_COLUMN);
}
public ArrayList<Employee> getAllEmployees(String namepart) {
ArrayList<Employee> employees = new ArrayList<>();
String whereclause = null;
String[] wherargs = null;
if (namepart != null && namepart.length() > 0) {
whereclause = EMPLOYEE_NAME_COLUMN + " LIKE ?";
wherargs = new String[]{"%" + namepart + "%"};
}
Cursor csr = EmpDept.query(EMPLOYEE_TABLE + " JOIN " + DEPARTMENT_TABLE + " ON " + EMPLOYEE_DEPT_COLUMN + "=" + DEPARTMENT_DEPTID_COLUMN,
null,whereclause, wherargs, null, null, EMPLOYEE_NAME_COLUMN + "," + EMPLOYEE_DEPT_COLUMN);
while (csr.moveToNext()) {
employees.add( new Employee(
csr.getLong(csr.getColumnIndex(EMPLOYEE_EMPID_COLUMN)),
csr.getString(csr.getColumnIndex(EMPLOYEE_NAME_COLUMN)),
csr.getString(csr.getColumnIndex(EMPLOYEE_TITLE_COLUMN)),
csr.getString(csr.getColumnIndex(EMPLOYEE_PHONE_COLUMN)),
csr.getString(csr.getColumnIndex(EMPLOYEE_EMAIL_COLUMN)),
csr.getLong(csr.getColumnIndex(DEPARTMENT_DEPTID_COLUMN)),
csr.getString(csr.getColumnIndex(DEPARTMENT_NAME_COLUMN)))
);
}
csr.close();
return employees;
}
private boolean ifDepertmentExists(long deptid) {
String wherecluase = DEPARTMENT_DEPTID_COLUMN + "=?";
String[] whereargs = new String[]{String.valueOf(deptid)};
Cursor csr =EmpDept.query(DEPARTMENT_TABLE,null,wherecluase,whereargs,null,null,null);
int rowcount = csr.getCount();
csr.close();
return rowcount > 0;
}
}
The empDetails Activity namely empDetails.java
The empDetails Activity namely empDetails.java
public class empDetails extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_emp_details);
TextView name = (TextView)findViewById(R.id.textview7);
TextView title = (TextView)findViewById(R.id.textview8);
TextView phone = (TextView)findViewById(R.id.textview9);
TextView email = (TextView)findViewById(R.id.textview10);
TextView dept = (TextView)findViewById(R.id.textview11);
Button mDoneButton = this.findViewById(R.id.donebutton);
mDoneButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
finish();
}
});
name.setText(getIntent().getExtras().getString(DeptDPHelper.EMPLOYEE_NAME_COLUMN));
title.setText(getIntent().getExtras().getString(DeptDPHelper.EMPLOYEE_TITLE_COLUMN));
phone.setText(getIntent().getExtras().getString(DeptDPHelper.EMPLOYEE_PHONE_COLUMN));
email.setText(getIntent().getExtras().getString(DeptDPHelper.EMPLOYEE_EMAIL_COLUMN));
dept.setText(getIntent().getExtras().getString(DeptDPHelper.DEPARTMENT_NAME_COLUMN));
}
}
And lastly MainActivity.java
And lastly MainActivity.java
public class MainActivity extends AppCompatActivity {
DeptDPHelper mDBHlpr;
ArrayList<Employee> mEmployeeList;
ArrayAdapter mNameAdapter;
ListView namelist;
EditText mNamePart;
TextWatcher mTW;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
namelist = this.findViewById(R.id.namelist);
mNamePart = this.findViewById(R.id.searchbar);
mDBHlpr = new DeptDPHelper(this);
addSomeData();
setupOrRefreshNameList();
setupSearch();
}
private void setupOrRefreshNameList() {
String namepart = mNamePart.getText().toString();
if (namepart.length() < 1) namepart = null;
if (mEmployeeList != null) {
mEmployeeList.clear();
ArrayList<Employee> el = mDBHlpr.getAllEmployees(namepart);
mEmployeeList.addAll(el);
} else {
mEmployeeList = mDBHlpr.getAllEmployees(namepart);
}
if (mNameAdapter == null) {
mNameAdapter = new ArrayAdapter(this,android.R.layout.simple_list_item_1,mEmployeeList);
namelist.setAdapter(mNameAdapter);
setupListViewListeners();
} else {
mNameAdapter.notifyDataSetChanged();
}
}
//<<<<<<<<<< For testing >>>>>>>>>>
private void addSomeData() {
long dept1 = mDBHlpr.insertDepartment("Dept001");
long dept2 = mDBHlpr.insertDepartment("Dept002");
mDBHlpr.insertEmployee("Fred","Mr.","1234567890","fred.x.com",dept1);
mDBHlpr.insertEmployee("Fred","Mr.","0987654321","fred@z.com",dept2);
mDBHlpr.insertEmployee("Mary","Ms.","9999999999","mary@marymail.net",dept1);
mDBHlpr.insertEmployee("Albert","Sir ", "1113334445","lord@roayls.org",dept1);
}
private void setupListViewListeners() {
namelist.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
Employee e = (Employee) mNameAdapter.getItem(i);
Toast.makeText(
getApplicationContext(),
"You clicked on " +
e.getEmployeeTitle() + " " +
e.getEmployeeName() +
" ID=" + String.valueOf(e.getEmployeeId()) +
" Email=" + e.getEmployeeEmail() +
" Phone=" + e.getEmployeePhone() +
" Dept=" + e.getEmployeeDeptName()
,
Toast.LENGTH_SHORT)
.show();
Intent intent = new Intent(MainActivity.this,empDetails.class);
intent.putExtra(DeptDPHelper.EMPLOYEE_NAME_COLUMN,e.getEmployeeName());
intent.putExtra(DeptDPHelper.EMPLOYEE_EMPID_COLUMN,e.getEmployeeId());
intent.putExtra(DeptDPHelper.EMPLOYEE_TITLE_COLUMN,e.getEmployeeTitle());
intent.putExtra(DeptDPHelper.EMPLOYEE_PHONE_COLUMN,e.getEmployeePhone());
intent.putExtra(DeptDPHelper.EMPLOYEE_EMAIL_COLUMN,e.getEmployeeEmail());
intent.putExtra(DeptDPHelper.DEPARTMENT_NAME_COLUMN,e.getEmployeeDeptName());
startActivity(intent);
}
});
}
private void setupSearch() {
mTW = new TextWatcher() {
@Override
public void beforeTextChanged(CharSequence charSequence, int i, int i1, int i2) {
}
//<<<<<<<<<< Every time the text is changed refresh the ListView >>>>>>>>>>
@Override
public void onTextChanged(CharSequence charSequence, int i, int i1, int i2) {
setupOrRefreshNameList();
}
@Override
public void afterTextChanged(Editable editable) {
}
};
mNamePart.addTextChangedListener(mTW);
}
}
Note this dynamically alters the list as you enter text into the EditText
Note this dynamically alters the list as you enter text into the EditText
Note that the Mainifest includes <activity android:name=".empDetails"></activity>
Note that the Mainifest includes <activity android:name=".empDetails"></activity>
When first run you get :-
When first run you get :-
Entering A for example (note only applies to the name not the title part):-
Entering A for example (note only applies to the name not the title part):-
Clicking on Mary :-
Clicking on Mary :-
Done will r eturn to the MainActivity.
Done will return to the MainActivity.