Questions about SQL/Async

so i have been coding this server to use files only, and then today im like why dont i switch to sql and get rid of file systems. i have used sql before and i have been doing a little research about it, but i just have a few questions. Also my previous system was to load all the player data from the file when the user joins from their specific file. i store a ton of data per player like 150 lines per player file. then it sets the fields in their userdata to the data stored. so the actually plugin code doesnt ever touch yaml except to load and save. then of course i save when they leave.

Questions

Thread safety
If i load all the player data from the sql async do i have to add atomic or volatile or anything like that to it if its just loading and saving from the variables in the field and the rest of the calls are sync.

Connection Pooling
I heard the best way to access your data is to have a pool of connections where different threads can access different pools whats the best way to do this.

One Async thread or multiple
Are all the async runnables from bukkit on the same thread. would calling every async call with new BukkitRunnable slow down speeds since it would have to wait for each request to finish on the same thread.

 

1. Depends
2. http://docs.oracle.com/javase/jndi/tutorial/ldap/connect/pool.html
3.
- Depends
- No

 

xTrollxDudex ok thanks, i did that but now i just open connections like usual or how do i get the connection for the PreparedStatement

Code:

public class SQL {
    private static final SQL instance = new SQL();
    Hashtable<Object,Object> table = new Hashtable<Object,Object>(11);
 
 
    public static SQL getInstance(){
        return instance;
    }
 
    public void loadConnectionPool(){
        table.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.ldap.LdapCtxFactory");
        table.put(Context.PROVIDER_URL, "localhost:389");
        table.put("com.sun.jndi.ldap.connect.pool", "true");
        try {
            DirContext ctx = new InitialDirContext(table);
 
            //it tells me to do something useful with it here.
            ctx.close();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
    public String getString(String player, String column) throws SQLException {
        Connection con = DriverManager.getConnection("localhost:389");// this seems wrong
        PreparedStatement get = con.prepareStatement("SELECT " + column +  " FROM `player_data` WHERE player=?");
        get.setString(1, player);
        ResultSet get2 = get.executeQuery();
        get2.next();
        String string = get2.getString(column);
        get.close();
        get2.close();
 
            return string;
    }
}

also how many pooled connections would i need for lets say 200 - 300 people on at one time

 

ResultStatic

Probably 2 at most, 3 if you call it all the time.

That doesn't look asynchronous to me, try again.

 

mythbusterma of course i know to make it async none of this is even being called from the main thread is it?. not sure if you are trying to but every post i see from you, you seem to come across as condescending, im sorry but i just cant stand when people arnt helpful.

does this look async to you?

Code:

    public void run(){
        new BukkitRunnable(){
            public void run(){
                try {
                    String kit = getString("result", "kit");
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }.runTaskAsynchronously(PvPDojo.getInstance());
    }

 

ResultStatic
You might want to set the 'instance' field to final for thread safety, volatile wouldn't fit here as the field isn't intended to change. Also, if you were trying to have a singleton design for that class you've failed at doing such. The instance field is private and final for singletons, and to access the field you have a static getter method.

 

mazentheamazin i changed it thanks

 

ResultStatic

It is, but how do you intend to extract data from that.

Also, I can be condescending and helpful at the same time, they're not mutually exclusive, so you can calm down.

 

ok i have been working on this for hours now and i cant find an answer. everyone says to use connection pooling over just one connection object but no one knows how to do it. i get an error on the code below saying the server does not support the driver. they have another driver called "ldap" instead of rmi but i cant get it to work

Code:

public class SQL {
    private static final SQL instance = new SQL();
    MysqlConnectionPoolDataSource dataSource;
 
    public static SQL getInstance(){
        return instance;
    }
 
    public SQL(){
    try {
          startRegistry();
          dataSource = new MysqlConnectionPoolDataSource();
          dataSource.setUser("user");
          dataSource.setPassword("password");
          dataSource.setServerName("name");
          dataSource.setPort(port);
          dataSource.setDatabaseName("name");
          dataSource.setUrl("url");
          InitialContext context = createContext();
          context.rebind("HrDS", dataSource);
 
        } catch (Exception e) {
          System.out.println("SetupJNDIDataSource err: " + e.getMessage());
          e.printStackTrace();
        }
      }
 
      private static void startRegistry() throws RemoteException {
        LocateRegistry.createRegistry(1099);
     
        System.out.println("RMI registry ready.");
 
    }
 
      private static InitialContext createContext() throws NamingException {
        Properties env = new Properties();
        env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.rmi.registry.RegistryContextFactory");
        env.put(Context.PROVIDER_URL, "rmi://url");
        InitialContext context = new InitialContext(env);
        return context;
      }
   
      public void addRow(){
          try {
              Connection connection = dataSource.getConnection();
                if (connection != null && !connection.isClosed()) {
                PreparedStatement newPlayer = connection.prepareStatement("INSERT INTO `users` values(1,1);");
                newPlayer.setString(1, "user");
                newPlayer.execute();
                newPlayer.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
 
      }
    }        

Code:

[02:05:50 WARN]: javax.naming.CommunicationException [Root exception is java.rmi
.ConnectIOException: JRMP StreamProtocol not supported by server]
[02:05:50 WARN]:        at com.sun.jndi.rmi.registry.RegistryContext.rebind(Unkn
own Source)
[02:05:50 WARN]:        at com.sun.jndi.rmi.registry.RegistryContext.rebind(Unkn
own Source)
[02:05:50 WARN]:        at javax.naming.InitialContext.rebind(Unknown Source)
[02:05:50 WARN]:        at com.pvpdojo.util.SQL.<init>(SQL.java:36)
[02:05:50 WARN]:        at com.pvpdojo.util.SQL.<clinit>(SQL.java:18)
[02:05:50 WARN]:        at com.pvpdojo.main.PvPDojo$1.run(PvPDojo.java:89)
[02:05:50 WARN]:        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftTask.ru
n(CraftTask.java:58)
[02:05:50 WARN]:        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftAsyncTa
sk.run(CraftAsyncTask.java:53)
[02:05:50 WARN]:        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unk
nown Source)
[02:05:50 WARN]:        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Un
known Source)
[02:05:50 WARN]:        at java.lang.Thread.run(Unknown Source)
[02:05:50 WARN]: Caused by: java.rmi.ConnectIOException: JRMP StreamProtocol not
supported by server
[02:05:50 WARN]:        at sun.rmi.transport.tcp.TCPChannel.createConnection(Unk
nown Source)
[02:05:50 WARN]:        at sun.rmi.transport.tcp.TCPChannel.newConnection(Unknow
n Source)
[02:05:50 WARN]:        at sun.rmi.server.UnicastRef.newCall(Unknown Source)
[02:05:50 WARN]:        at sun.rmi.registry.RegistryImpl_Stub.rebind(Unknown Sou
rce)
[02:05:50 WARN]:        ... 11 more
[02:05:50 WARN]: Exception in thread "Craft Scheduler Thread - 0"
[02:05:50 WARN]: org.apache.commons.lang.UnhandledException: Plugin PvPDojo2.5 v
2.5.0 generated an exception while executing task 6
        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftAsyncTask.run(CraftAsyn
cTask.java:56)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NullPointerException
        at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlData
Source.java:428)
        at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlData
Source.java:138)
        at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlData
Source.java:108)
        at com.pvpdojo.util.SQL.addRow(SQL.java:61)
        at com.pvpdojo.main.PvPDojo$1.run(PvPDojo.java:89)
        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftTask.run(CraftTask.java
:58)
        at org.bukkit.craftbukkit.v1_7_R3.scheduler.CraftAsyncTask.run(CraftAsyn
cTask.java:53)
        ... 3 more

nvm i got it working turns out env.put(Context.PROVIDER_URL, "rmi://url"); needs to be set to localhost:1099 as the url and not ur database

EDIT by Moderator: merged posts, please use the edit button instead of double posting.