Schnittstelle Database

Alle Superschnittstellen:
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
1public 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}
  • Methodenübersicht

    Modifizierer und Typ
    Methode
    Beschreibung
    void
    Closes this connection.
    void
    Executes the given SQL statement, for example a CREATE or DELETE statement.
    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
    Executes the given SQL statement, which may be an INSERT, UPDATE or DELETE statement.
    Gets the underlying Connection object, which represents the connection to the specific database.
    Gets the database type.
  • Methodendetails

    • getType

      DatabaseType getType()
      Gets the database type.
      Gibt zurück:
      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;
      Gibt zurück:
      a Connection object.
      Example: Create a PreparedStatement and insert a value in an existing database
      1//Get connection
      2Connection connection = database.getConnection();
      3
      4//Use a try-with-resources statement (since Java 7), this closes the
      5//PreparedStatement automatically upcon completion
      6try(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.
      Parameter:
      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)
      3Database sqlite = getSQLiteConnection(getPath() + "/database/config.db");
      4
      5//Create table "Prices" (only if it does not exist)
      6sqlite.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
      9sqlite.executeUpdate("INSERT INTO `Prices` (Itemname, Price) VALUES ('pickaxe', '400');");
      10
      11//Once you don't need the connection anymore, close it!
      12sqlite.close();
    • executeUpdate

      void executeUpdate(String sql)
      Executes the given SQL statement, which may be an INSERT, UPDATE or DELETE statement.
      Parameter:
      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
      3sqlite.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
      2Database sqlite = getSQLiteConnection(getPath() + "/myDB.db");
      3
      4//Create table "playerdata"
      5sqlite.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"
      10sqlite.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
      13sqlite.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).
      Parameter:
      sql - the SQL statement, typically a SELECT statement.
      Gibt zurück:
      a ResultSet object containing the data produced by the given SQL statement.
      Löst aus:
      SQLException - If a database access error occurs.
      Siehe auch:
      • 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)
        3Database 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
        7try(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}
        16catch(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"
        2Database db = getMySQLConnection("MyDatabase", "127.0.0.1", 3306, "username", "password");
        3
        4//Here we want to store some Strings (just an example)
        5ArrayList<String> list = new ArrayList<>();
        6
        7//Use a try-with-resources statement (this closes the ResultSet automatically
        8//upon completion) when executing the query
        9try(ResultSet result = db.executeQuery("SELECT * FROM `MyTable`")){
        10 while(result.next()){
        11 String text = result.getString("Text");
        12 list.add(text);
        13 }
        14}
        15catch(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.
      Angegeben von:
      close in Schnittstelle AutoCloseable