Java Game Server database table Access encapsulation _java

There are not many database tables involved in the project, but each SELECT, insert, UPDATE, and delete manual concatenation of strings is inefficient, especially if the structure is often modified. One of the goals of development is automation, where things can be done automatically, not manually; There is also a principle of single, that is, to ensure that data or logic is an entrance to an outlet. This requirement can be solved with some open source libraries, but because of the simplicity of the requirements and the clear objectives, there is no need to introduce redundant third-party libraries. So he wrote one, at least to meet the current needs.

The database table is encapsulated with two core classes, tables (table), and records (record). First you need a table class to hold a description of the database table structure, and automatically generate the appropriate SQL statement. Secondly, a record class is required to automatically set the SQL parameters and generate logical objects automatically from the return result set.

Table structure descriptions can have two sources, automatically fetched from the database, or loaded from the configuration table. Here choose the way to load from the configuration table, a simple implementation, and a wider application.

The following is an example of an Account table configuration (user.xml).

<table name= "user" primarykey= "user_id" primaryfield= "UserId" >
  <column name= "username" field= "username" "Type=" 2 "/>
  <column name=" password "field=" password "type=" 2 "/> <column name=
  " Salt "field=" salt "type=" 1 "/>
  <column name=" Reg_time "field=" Registertime "type=" 3 "/> <column name=
  " Last_login_ Time "field=" Lastlogintime "type=" 3 "/>
</Table>

Only one primary key is defined, and there is a need to expand on it. Each column name corresponds to the column name of the database table, the field corresponds to the member variable name of the logical object, type of the corresponding field, such as int, string, timestamp, and so on, with the name and type, you can use the reflection method to automatically get and set data.

The table class reads the configuration file to obtain a structure description of the datasheet.

public class Table<t> {public
  class TableField {public
    static final int type_integer = 1;
    public static final int type_string = 2;
    public static final int type_timestamp = 3;
    Public String columnName = "";
    Public String fieldName = "";
    public int type = 0;
  }
  Private String tablename = "";
  Private TableField Primaryfield = new TableField ();
  Private arraylist<tablefield> TableFields = new arraylist<tablefield> ();
  Private String Selectallsql = "";
  Private String Selectsql = "";
  Private String Insertsql = "";
  Private String Updatesql = "";
  Private String Deletesql = "";
  ...

It then generates a preprocessed SQL string for SELECT, insert, UPDATE, and delete read-and-write preparestatement. such as update:

private String Generateupdatesql () {
    String sql = "UPDATE" + tablename + "SET";
    int size = Tablefields.size ();
    for (int index = 0; index < size; ++index) {
      TableField TableField = tablefields.get (index);
      String conjunction = index = 0? "" : ",";
      String Colsql = tablefield.columnname + "=?";
      SQL = SQL + conjunction + colsql;
    }

    sql = SQL + "WHERE" + primaryfield.columnname + "=?";
    return SQL;
  }

The table class has so many functions, and the following is the key record class, which uses reflection to automatically access data.

public class Record<t> {
  private table<t> Table = null;
  Private T object = null;
  ...

Template parameter T is the logical object of a table record. In our example, the account data class:

public class UserData implements Serializable {
  //user ID public
  int userId = 0;
  User name public
  String username = "";
  Password public
  String password = "";
  ...

With SQL statements, you set the parameters before you can execute them. The primary key and the normal field are set separately.

 public int Setprimaryparams (int start, PreparedStatement pst) throws Exception {
    table<t>. TableField Primaryfield = Table.getprimaryfield ();
    Object value = GetFieldValue (Primaryfield);
    Value = Todbvalue (Primaryfield, value);
    Pst.setobject (start, value);
    return start + 1;
  }
  public int Setnormalparams (int start, PreparedStatement pst) throws Exception {
    arraylist<table<t>. tablefield> normalfields = Table.getnoramlfields ();
    final int size = Normalfields.size ();
    for (int index = 0; index < size; ++index) {
      table<t>. TableField TableField = normalfields.get (index);
      Object value = GetFieldValue (TableField);
      Value = Todbvalue (TableField, value);
      Pst.setobject (start + index, value);
    }
    return start + size;
  }

is to obtain the value of the corresponding field by reflection and then set it according to the table structure description.

 Private Object GetFieldValue (TABLE<T>. TableField TableField) throws Exception {
    Field field = Object.getclass (). Getdeclaredfield (Tablefield.fieldname); Return
    Field.get (object);
  

The Todbvalue function is to convert the Java logic type to the corresponding database type, such as time, in logic, long, and the database type is timestamp.

 Private Object Todbvalue (TABLE<T>. TableField TableField, Object value {
    if (Tablefield.type = = Tablefield.type_timestamp) {
      value = new TIMESTAMP ( (long) value);
    }
    return value;
  }

To set the update SQL parameter as an example:

 public void Setupdateparams (PreparedStatement pst) throws Exception {
    final int start = Setnormalparams (1, PST); 
   setprimaryparams (start, PST);
  

You can then execute the SQL statement. If the SELECT statement also returns the result set (ResultSet), it is a reverse process to automatically generate logical objects from the result set, in detail, see the code at the end of the article.

The following is a complete example of a use:

private static final table<userdata> udtable = new table<userdata> ();
...
Udtable.load ("User.xml");
...
public static Boolean Updateuserdata (UserData UserData) {
    Boolean result = false;
    Record<userdata> record = Udtable.createrecord ();
    Record.setobject (userData);
    PreparedStatement PST = NULL;
    try {
      String sql = Udtable.getupdatesql ();
      PST = Dbutil.openconnection (). preparestatement (SQL);
      Record.setupdateparams (PST);
      result = Pst.executeupdate () > 0;
    } catch (Exception e) {
      e.printstacktrace ();
    } finally {
      dbutil.closeconnection (null, PST);
    }
    return result;
  }

The code is easy to encapsulate, and more needs can be improved accordingly.