Package net.risingworld.api.database

Interface Database

All Superinterfaces:

AutoCloseable

public interface Database
extends AutoCloseable

Database interface which represents a connection to an SQLite or MySQL database.

Example: Full example of how you may use an SQLite database

1	public class MyPlugin extends Plugin implements Listener {
2
3	//Cache the db connection
4	public Database database;
5
6	@Override
7	public void onEnable() {
8	//We only create a db connection once and reuse it
9	database = getSQLiteConnection(getPath() + "/database.db");
10
11	//Create new table if it does not yet exist
12	database.execute("CREATE TABLE IF NOT EXISTS `MyTable` (`playeruid` VARCHAR(255) PRIMARY KEY NOT NULL, `timestamp` BIGINT);");
13
14	//Register event listener
15	registerEventListener(this);
16	}
17
18	@Override
19	public void onDisable() {
20	//When our plugin gets disabled, close the database connection
21	if(database != null) database.close();
22	}
23
24	@EventMethod
25	public void onPlayerConnect(PlayerConnectEvent evt) {
26	Player player = evt.getPlayer();
27
28	//If player is a new player (connect for the first time), we insert a new data set
29	if(evt.isNewPlayer()) {
30	//Insert data through our cached db connection
31	database.executeUpdate("INSERT INTO `MyTable` (playeruid, timestamp) VALUES ('" + player.getUID() + ", " + System.currentTimeMillis() + ");");
32	}
33	}
34
35	@EventMethod
36	public void onPlayerCommand(PlayerCommandEvent evt) {
37	Player player = evt.getPlayer();
38
39	if(evt.getCommand().startsWith("/testcommand")) {
40	//This "try-with-resources" block closes the ResultSet automatically
41	try(ResultSet result = database.executeQuery("SELECT * FROM `MyTable` WHERE `playeruid` = '" + player.getUID() + "';")){
42	while(result.next()) {
43	//Do something with the data
44	long timestamp = result.getLong("timestamp");
45	player.sendTextMessage("Stored timestamp: " + timestamp);
46	}
47	}
48	catch(Exception e) {
49	e.printStackTrace();
50	}
51	}
52	}
53
54	}

Method Summary

Modifier and Type	Method	Description
void	close()	Closes this connection.
void	execute(String sql)	Executes the given SQL statement, for example a CREATE or DELETE statement.
ResultSet	executeQuery(String sql)	Executes the given SQL statement and returns a ResultSet object. Remember to close the ResultSet once you're ready (or use a try-with-resources statement).
void	executeUpdate(String sql)	Executes the given SQL statement, which may be an INSERT, UPDATE or DELETE statement.
Connection	getConnection()	Gets the underlying Connection object, which represents the connection to the specific database.
DatabaseType	getType()	Gets the database type.

Method Details

getType

DatabaseType getType()

Gets the database type.

Returns:

the database type, SQLite or MySQL

getConnection

Connection getConnection()

Gets the underlying Connection object, which represents the connection to the specific database. This provides full access to the database. Be careful when changing any settings of this connection;

Returns:

a Connection object.

Example: Create a PreparedStatement and insert a value in an existing database

1	//Get connection
2	Connection connection = database.getConnection();
3
4	//Use a try-with-resources statement (since Java 7), this closes the
5	//PreparedStatement automatically upcon completion
6	try(PreparedStatement prep = connection.prepareStatement("INSERT INTO `MyTable` (`ID`, `Text`, `Active`) VALUES (?, ?, ?)")){
7	prep.setInt(1, 42);
8	prep.setString(2, "Hello World");
9	prep.setBoolean(3, true);
10	prep.executeUpdate();
11	//Note: if AutoCommit is set to false on this connection (by default it is set to true),
12	//you'll have to call "prep.commit();" to commit the changes
13	}

execute

void execute(String sql)

Executes the given SQL statement, for example a CREATE or DELETE statement.

Parameters:

sql - the SQL statement.

Example: Create a custom SQLite connection (creates new database if it does not exist)

1	//Establish connection to "config.db" database in the plugin subfolder "database"
2	//(if database does not exist, it will be created automatically)
3	Database sqlite = getSQLiteConnection(getPath() + "/database/config.db");
4
5	//Create table "Prices" (only if it does not exist)
6	sqlite.execute("CREATE TABLE IF NOT EXISTS `Prices` (`ID` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `Itemname` VARCHAR(64), `Price` INTEGER);");
7
8	//Insert new value into "Prices" table
9	sqlite.executeUpdate("INSERT INTO `Prices` (Itemname, Price) VALUES ('pickaxe', '400');");
10
11	//Once you don't need the connection anymore, close it!
12	sqlite.close();

executeUpdate

void executeUpdate(String sql)

Executes the given SQL statement, which may be an INSERT, UPDATE or DELETE statement.

Parameters:

sql - the SQL statement, INSERT, UPDATE or DELETE.

Example: Update an entry in an existing database

1	//We assume that there is a table "npcs" which already contains
2	//an entry for our npc. We just want to update the entry
3	sqlite.executeUpdate(UPDATE `npcs` SET owner=" + player.getDbID() + ", health=" + npc.getHealth() + ", lastupdate=" + System.currentTimeMillis() + " WHERE id=" + npc.getGlobalID() +";");

Example: Create and insert or update an entry (UPSERT)

1	//Establish database connection in plugin folder
2	Database sqlite = getSQLiteConnection(getPath() + "/myDB.db");
3
4	//Create table "playerdata"
5	sqlite.execute("CREATE TABLE IF NOT EXISTS `playerdata` (`ID` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `UID` VARCHAR(64), `Key` VARCHAR(32), `Value` INTEGER);");
6
7	...
8
9	//At a later point, perform an "UPSERT"
10	sqlite.executeUpdate("INSERT INTO `playerdata` (`UID`, `Key`, `Value`) VALUES (" + player.getUID() + ", 'Health', " + player.getHealth() + ") ON CONFLICT(`UID`) DO UPDATE SET `Value`=" + player.getHealth() + ";");
11
12	//If you no longer need the connection, close it
13	sqlite.close();

executeQuery

ResultSet executeQuery(String sql)
                throws SQLException

Executes the given SQL statement and returns a ResultSet object.
Remember to close the ResultSet once you're ready (or use a try-with-resources statement).

Parameters:

sql - the SQL statement, typically a SELECT statement.

Returns:

a ResultSet object containing the data produced by the given SQL statement.

Throws:

SQLException - If a database access error occurs.

See Also:

• Try-with-resources statement
  
  Example: Read data from database

  1	//Open connection to "config.db" database in the plugin subfolder "database"
  2	//(if database does not exist, it will be created automatically)
  3	Database db = getSQLiteConnection(getPath() + "/database/config.db");
  4
  5	//Use a try-with-resources statement (this closes the ResultSet automatically
  6	//upon completion) when executing the query
  7	try(ResultSet result = db.executeQuery("SELECT * FROM `MyTable` WHERE `Name` = 'Testplayer'")){
  8	if(result.next()){
  9	int valueA = result.getInt("ValueA");
  10	int valueB = result.getInt("ValueB");
  11	boolean valueC = result.getBoolean("ValueC");
  12	String valueD = result.getString("ValueD");
  13	//...
  14	}
  15	}
  16	catch(SQLException e){
  17	//this happens when an SQL exception occurs. You have to catch this
  18	//exception. If you just want to print the error, you could call
  19	//e.printStackTrace();
  20	}

  
  
  Example: Get all data from a table and store it in an ArrayList

  1	//Open connection to a MySQL database called "MyDatabase"
  2	Database db = getMySQLConnection("MyDatabase", "127.0.0.1", 3306, "username", "password");
  3
  4	//Here we want to store some Strings (just an example)
  5	ArrayList<String> list = new ArrayList<>();
  6
  7	//Use a try-with-resources statement (this closes the ResultSet automatically
  8	//upon completion) when executing the query
  9	try(ResultSet result = db.executeQuery("SELECT * FROM `MyTable`")){
  10	while(result.next()){
  11	String text = result.getString("Text");
  12	list.add(text);
  13	}
  14	}
  15	catch(SQLException e){
  16	//this happens when an SQL exception occurs. You have to catch this
  17	//exception. If you just want to print the error, you could call
  18	//e.printStackTrace();
  19	}

close

void close()

Closes this connection. Call this method if the connection isn't needed anymore.

Specified by:

close in interface AutoCloseable