Select or Read
Basic example of a query to get all the data in a table
public class SelectExample {
public static void main(String[] args) {
// URL to the PHP file on the server
String url = "http://localhost/www/projects/Java_Php_DBConnector/RequestHandler.php";
// Get a database instance (singleton)
Database db = Database.getInstance(url);
// Set Secure protocol ignored to true because we use HTTP protocol in
// this example (for security use HTTPS protocol)
db.setSecureProtocolIgnored(true);
// Table Names
String tableOne = "tableOne";
// Query
try {
// We obtain the returned data in a DataSet
// All parameters are null except the name of the table to get all columns of all rows.
DataSet set = db.query(tableOne, null, null, null, null, null, null, null);
if(set.rowCount() > 0){
// Show data
for (ColumnSet row : set) {
System.out.println("ROW"));
System.out.println("FieldOne => " + row.getInt("to_fieldOne"));
System.out.println("FieldTwo => " + row.getString("to_fieldTwo"));
System.out.println("FieldThree => " + row.getBoolean("to_fieldThree"));
System.out.println("FieldFour => " + row.getDouble("to_fieldFour"));
}
}
} catch (SQLException e) {
// Handle exception
} catch (DatabaseException e) {
// Handle exception
}
}
}
+----------------------------------------------------------------------------------+
| Output:
+----------------------------------------------------------------------------------+
| ROW
| FieldOne => 1
| FieldTwo => Field Two Value (Row 1)
| FieldThree => true
| FieldFour => 15.2
| ROW
| FieldOne => 2
| FieldTwo => Field Two Value (Row 2)
| FieldThree => false
| FieldFour => 84516.52
| ROW
| FieldOne => 3
| FieldTwo => Field Two Value (Row 3)
| FieldThree => false
| FieldFour => 359.52
| ROW
| FieldOne => 4
| FieldTwo => Field Two Value (Row 4)
| FieldThree => true
| FieldFour => 20.7
+----------------------------------------------------------------------------------+
Go to top
Example of a parameterized query with projection columns and "WHERE" clause with "WHERE ARGUMENTS"
public class SelectExample {
public static void main(String[] args) {
// URL to the PHP file on the server
String url = "http://localhost/www/projects/Java_Php_DBConnector/RequestHandler.php";
// Get a database instance (singleton)
Database db = Database.getInstance(url);
// Set Secure protocol ignored to true because we use HTTP protocol in
// this example (for security use HTTPS protocol)
db.setSecureProtocolIgnored(true);
// Table Names
String tableOne = "tableOne";
// Projection with only three columns
String[] projection = { "to_fieldOne", "to_fieldTwo", "to_fieldFour" };
// Where clause
String where = "to_fieldOne = :to_fieldOne";
// Where arguments (mapping values with ":")
ColumnSet whereArgs = new ColumnSet();
whereArgs.put("to_fieldOne", 2);
// Group by clause
String groupBy = null;
// Having clause
String having = null;
// OrderBy clause
String orderBy = null;
// limit clause
Integer limit = null;
// Query
try {
// We obtain the returned data in a DataSet
DataSet set = db.query(tableOne, projection, where, whereArgs, groupBy, having, orderBy, limit);
if(set.rowCount() > 0){
// Show data
for (ColumnSet row : set) {
System.out.println("FieldOne => " + row.getInt("to_fieldOne"));
System.out.println("FieldTwo => " + row.getString("to_fieldTwo"));
System.out.println("FieldFour => " + row.getDouble("to_fieldFour"));
}
}
} catch (SQLException e) {
// Handle exception
} catch (DatabaseException e) {
// Handle exception
}
}
}
+----------------------------------------------------------------------------------+
| Output:
+----------------------------------------------------------------------------------+
| FieldOne => 2
| FieldTwo => Field Two Value (Row 2)
| FieldFour => 84516.52
+----------------------------------------------------------------------------------+
Go to top
Example of a parameterized query with projection columns with two tables and join operation
public class SelectExample {
public static void main(String[] args) {
// URL to the PHP file on the server
String url = "http://localhost/www/projects/Java_Php_DBConnector/RequestHandler.php";
// Get a database instance (singleton)
Database db = Database.getInstance(url);
// Set Secure protocol ignored to true because we use HTTP protocol in
// this example (for security use HTTPS protocol)
db.setSecureProtocolIgnored(true);
// Table Names
String tableOne = "tableOne";
String tableTwo = "tableTwo";
// join tables
String join = tableOne + " join " + tableTwo + " on to_fieldOne = tt_fieldThree";
// projection 2 fields from tableOne and 2 fields from tableTwo
String[] projection = { "to_fieldOne", "tt_fieldThree", "to_fieldTwo", "tt_fieldFour" };
// where clause (mapping values with ":")
String where = "to_fieldOne = :to_fieldOne";
// where Args for parameterized query
ColumnSet whereArgs = new ColumnSet();
whereArgs.put("to_fieldOne", 2);
// Query
try {
// We obtain the returned data in a DataSet
DataSet set = db.query(join, projection, where, whereArgs, null, null, null, null);
for (ColumnSet row : set) {
System.out.println("FieldOne (tableOne) => " + row.getInt("to_fieldOne"));
System.out.println("FieldThree (tableTwo) => " + row.getString("tt_fieldThree"));
System.out.println("FieldTwo (tableOne) => " + row.getString("to_fieldTwo"));
System.out.println("FieldFour (tableTwo) => " + row.getString("tt_fieldFour"));
}
} catch (SQLException e) {
// handle exception
} catch (DatabaseException e) {
// handle exception
}
}
}
+----------------------------------------------------------------------------------+
| Output:
+----------------------------------------------------------------------------------+
| FieldOne (tableOne) => 2
| FieldThree (tableTwo) => 2
| FieldTwo (tableOne) => Field Two Value (Row 2)
| FieldFour (tableTwo) => Field Four Value (Row 2)
+----------------------------------------------------------------------------------+
Go to top
Insert or Create
Example to insert data into a table without returning the inserted id (returns the number of rows affected)
public class InsertExample {
public static void main(String[] args) {
// URL to the PHP file on the server
String url = "http://localhost/www/projects/Java_Php_DBConnector/RequestHandler.php";
// Get a database instance (singleton)
Database db = Database.getInstance(url);
// Set Secure protocol ignored to true because we use HTTP protocol in
// this example (for security use HTTPS protocol)
db.setSecureProtocolIgnored(true);
// table name
String table = "tableOne";
// values to insert
ColumnSet values = new ColumnSet();
values.put("to_fieldOne", null); // Null because is an auto_increment id (this is not necessary with auto_increment keys)
values.put("to_fieldTwo", "Text value");
values.put("to_fieldThree", true);
values.put("to_fieldFour", 3589.64);
// returnId => If true return the last inserted id (only work with numeric id, otherwise return 0). If false, return the number of rows affected
boolean returnId = false;
// Insert values
try {
// get result of insert values (Integer with number of rows because returnId is false)
int result = db.insert(table, values, returnId);
// check result value
if (result > 0){
// values inserted
}
else{
// values not inserted
}
} catch (SQLException e) {
// handle exception
} catch (DatabaseException e) {
// handle exception
}
}
}
Go to top
Example to insert data into a table with returning the inserted id (not returns the number of rows affected)
public class InsertExample {
public static void main(String[] args) {
// URL to the PHP file on the server
String url = "http://localhost/www/projects/Java_Php_DBConnector/RequestHandler.php";
// Get a database instance (singleton)
Database db = Database.getInstance(url);
// Set Secure protocol ignored to true because we use HTTP protocol in
// this example (for security use HTTPS protocol)
db.setSecureProtocolIgnored(true);
// table name
String table = "tableOne";
// values to insert
ColumnSet values = new ColumnSet();
values.put("to_fieldOne", null); // Null because is an auto_increment id (this is not necessary with auto_increment keys)
values.put("to_fieldTwo", "Text value");
values.put("to_fieldThree", true);
values.put("to_fieldFour", 3589.64);
// returnId => If true return last inserted id (only work with numeric id, otherwise return 0). If false, return the number of rows affected
boolean returnId = false;
// Insert values
try {
// get result of insert values (Integer with number of rows because returnId is false)
int result = db.insert(table, values, returnId);
// check result value
if (result > 0){
// values inserted
}
else{
// values not inserted
}
} catch (SQLException e) {
// handle exception
} catch (DatabaseException e) {
// handle exception
}
}
}
Go to top
Update
Example to update data with parameterized values
public class UpdateExample {
public static void main(String[] args) {
// URL to the PHP file on the server
String url = "http://localhost/www/projects/Java_Php_DBConnector/RequestHandler.php";
// Get a database instance (singleton)
Database db = Database.getInstance(url);
// Set Secure protocol ignored to true because we use HTTP protocol in
// this example (for security use HTTPS protocol)
db.setSecureProtocolIgnored(true);
// table name
String table = "tableOne";
// values to update
ColumnSet values = new ColumnSet();
values.put("to_fieldTwo", "Text value (updated)");
values.put("to_fieldThree", false);
values.put("to_fieldFour", 3589.65);
// Where clause (mapping values with ":")
String where = "to_fieldOne = :to_fieldOne";
// Where Args
ColumnSet whereArgs = new ColumnSet();
whereArgs.put("to_fieldOne", 2);
// Update values
try {
// get result of update values (Integer with number of rows affected)
int result = db.update(table, values, where, whereArgs);
// check result value
if (result > 0) {
// values updated
} else {
// values not updated
}
} catch (SQLException e) {
// handle exception
} catch (DatabaseException e) {
// handle exception
}
}
}
Go to top
Delete
Example to delete data with parameterized values
public class DeleteExample {
public static void main(String[] args) {
// URL to the PHP file on the server
String url = "http://localhost/www/projects/Java_Php_DBConnector/RequestHandler.php";
// Get a database instance (singleton)
Database db = Database.getInstance(url);
// Set Secure protocol ignored to true because we use HTTP protocol in
// this example (for security use HTTPS protocol)
db.setSecureProtocolIgnored(true);
// table name
String table = "tableOne";
// Where clause (mapping values with ":")
String where = "to_fieldOne = :to_fieldOne";
// Where Args
ColumnSet whereArgs = new ColumnSet();
whereArgs.put("to_fieldOne", 3);
// Delete values
try {
// get result of delete rows (Integer with number of rows affected)
int result = db.delete(table, where, whereArgs);
// check result value
if (result > 0) {
// values deleted
} else {
// values not delete
}
} catch (SQLException e) {
// handle exception
} catch (DatabaseException e) {
// handle exception
}
}
}
Go to top