So I'm back on Android Development, still getting to know stuff again, and this is the first time I make an app that connects to a database in a server.
So far I have used this code to be able to connect to the server and it works by showing that the "connection is true"
The only thing I want to know now is how I can use this same class to throw queries to do things like "SELECT * FROM TABLE" etc.
I know that it bad practice to do what I'm doing by connecting direct to a db, but this is just a very small app that will not do important stuff but just so I can understand things better in Android.
package com.example.databasetest;
import java.sql.Connection;
import java.sql.DriverManager;
public class Database {
private Connection connection;
private final String host = "xxxxx";
private final String database = "xxxx";
private final int port = "xxxx";
private final String user = "xxxx";
private final String pass = "xxxx";
private String url = "jdbc:postgresql://%s:%d/%s";
private boolean status;
public Database() {
this.url = String.format(this.url, this.host, this.port, this.database);
connect();
//this.disconnect();
System.out.println("connection status:" status);
}
private void connect() {
Thread thread = new Thread(new Runnable() {
@Override
public void run() {
try {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(url, user, pass);
status = true;
System.out.println("connected:" status);
} catch (Exception e) {
status = false;
System.out.print(e.getMessage());
e.printStackTrace();
}
}
});
thread.start();
try {
thread.join();
} catch (Exception e) {
e.printStackTrace();
this.status = false;
}
}
public Connection getExtraConnection(){
Connection c = null;
try {
Class.forName("org.postgresql.Driver");
c = DriverManager.getConnection(url, user, pass);
} catch (Exception e) {
e.printStackTrace();
}
return c;
}
}
This is my MainActivity:
package com.example.databasetest
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
val db = Database()
}
}
Edit:
Ok, so I tried to replicate the "private void connect()" function into a "public void load()" function which sends sql queries to the db.
public void load() {
Thread thread = new Thread(new Runnable() {
@Override
public void run() {
try {
Connection con;
Statement stmt;
String sql = "SELECT * FROM INVENTORY";
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(url, user, pass);
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()){
int id = rs.getInt("ID");
String description = rs.getString("DESCRIPTION");
String amount = rs.getString("AMOUNT");
String local = rs.getString("LOCAL");
System.out.print("ID: " id);
System.out.print("Description: " description);
System.out.print("Amount: " amount);
System.out.print("Local: " local);
con.close();
}
} catch (Exception e) {
status = false;
System.out.print(e.getMessage());
e.printStackTrace();
}
}
});
thread.start();
try {
thread.join();
} catch (Exception e) {
e.printStackTrace();
this.status = false;
}
}
The problem now is that in the Logcat this warning appears:
"I/Choreographer: Skipped 83 frames! The application may be doing too much work on its main thread."
It worked for one time, but then now it just gives me this error. The database doesn't even have that much info, it has like 12 rows.
How can I multithread (?) this function when accessing the database?
This is where I call db.load() in my MainActivity:
override fun onOptionsItemSelected(item: MenuItem): Boolean {
val id = findViewById<EditText>(R.id.ID)
return when (item.itemId) {
R.id.load -> {
val db = Database()
db.load()
true
}
CodePudding user response：
You have got the instance of Connection, does the Connection contain a method to execute SQL queries (or sth. like that)?
public class Database {
...
public Cursor query(String sql) { //cursor is nullable
// TODO connection.xxxx
}
}
In the class MainActivity, you can access the method query to get the Cursor...
[Update]
public class Database {
private Connection connection;
private final String host = "xxxxx";
private final String database = "xxxx";
private final int port = 9999;//needs int
private final String user = "xxxx";
private final String pass = "xxxx";
private final String SQL_DRIVER = "org.postgresql.Driver";
private String url = "jdbc:postgresql://%s:%d/%s";
private boolean status;
public Database() {
this.url = String.format(this.url, this.host, this.port, this.database);
System.out.println("the final url is " this.url);
}
public Connection getConnection() {
Connection c = null;
try {
Class.forName(SQL_DRIVER);
c = DriverManager.getConnection(url, user, pass);
status = true;
System.out.println("connected:" status);
} catch (Exception e) {
status = false;
System.out.print(e.getMessage());
e.printStackTrace();
}
return c;
}
public void load() {
Thread thread = new Thread(new Runnable() {
Connection con;
@Override
public void run() {
try {
Statement stmt;
String sql = "SELECT * FROM INVENTORY";
Class.forName(SQL_DRIVER);
con = DriverManager.getConnection(url, user, pass);
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
status = true;
while (rs.next()) {
int id = rs.getInt("ID");
String description = rs.getString("DESCRIPTION");
String amount = rs.getString("AMOUNT");
String local = rs.getString("LOCAL");
System.out.print("ID: " id);
System.out.print("Description: " description);
System.out.print("Amount: " amount);
System.out.print("Local: " local);
}
} catch (Exception e) {
try {
con.close();
} catch (SQLException exp) {
exp.printStackTrace();
}
status = false;
System.out.print(e.getMessage());
e.printStackTrace();
}
}
});
thread.start();
// try to comment
//try {
// thread.join();
//} catch (Exception e) {
// e.printStackTrace();
// this.status = false;
//}
}
}