net.risingworld.api.database

Interface Database

All Superinterfaces:

java.lang.AutoCloseable

public interface Database
extends java.lang.AutoCloseable

Method Summary

Modifier and Type	Method and Description
void	close() Closes this connection.
void	execute(java.lang.String sql) Executes the given SQL statement, for example a CREATE or DELETE statement.
java.sql.ResultSet	executeQuery(java.lang.String sql) Executes the given SQL statement and returns a ResultSet object.
void	executeUpdate(java.lang.String sql) Executes the given SQL statement, which may be an INSERT, UPDATE or DELETE statement.
java.sql.Connection	getConnection() Gets the underlying Connection object, which represents the connection to the specific database.
DatabaseType	getType() Gets the database type.

Method Detail

getType

DatabaseType getType()

Gets the database type.

Returns:

the database type, SQLite or MySQL

getConnection

java.sql.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(java.lang.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(java.lang.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.

executeQuery

java.sql.ResultSet executeQuery(java.lang.String sql)
                         throws java.sql.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:

java.sql.SQLException - If a database access error occurs.

See Also:

Try-with-resources statement

Example #1: 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 #2: 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 java.lang.AutoCloseable