Java game server database table access package

There are not many database tables involved in the project, but it is inefficient to manually concatenate strings for every select, insert, update, and delete, especially if the structure is constantly being modified. One of the goals of development is automation, that is, things that can be done automatically don't do it manually; There is also the principle of unitary, that is, try to ensure that the data or logic is one entrance one exit. This requirement can be addressed using some open source libraries, but because the requirements are simple and targeted, there is no need to introduce additional third-party libraries. So I wrote one, at least for the moment.

The encapsulation of database tables, the core class has two, Table and Record. First, you need a Table class to hold a description of the database Table structure and automatically generate the corresponding SQL statements. Next, a Record class is needed to automatically set SQL parameters and automatically generate logical objects from the returned result set.

The table class table structure description can have two sources, either automatically fetched from the database or loaded from the configuration table. Here choose to load from the configuration table, a simple implementation, and a wider application.

The following is a configuration example of an account table (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, which can be extended if necessary. Each column name corresponds to the column name of the database table, the field corresponds to the member variable name of the logical object, and the type corresponds to the type of the field, such as int, string, timestamp, etc. With the name and type, you can use reflection to automatically get and set data.

The Table class reads the configuration file for a structural description of the data Table.

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 preprocessed SQL strings for select, insert, update, and delete that are read and written in a PrepareStatement manner. If the 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;
  }

So much for the Table class. Below is the key Record class, which USES reflection to access data automatically.

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

The template parameter T is a logical object corresponding to a table record. In our example, the account data class:

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

With SQL statements, parameters are set before execution. The primary key is set separately from the normal field.

 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;
  }

That is, according to the table structure description, get the value of the corresponding field through reflection and then set it.

 private Object getFieldValue(Table<T>.TableField tableField) throws Exception {
    Field field = object.getClass().getDeclaredField(tableField.fieldName);
    return field.get(object);
  }

The role of toDBValue is to convert the Java logical type to the corresponding database type, such as time, which is logically 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;
  }

Take the setting of update SQL parameters as an example:

 public void setUpdateParams(PreparedStatement pst) throws Exception {
    final int start = setNormalParams(1, pst);
    setPrimaryParams(start, pst);
  }

Then execute the SQL statement. If it is a select statement will also return a ResultSet (ResultSet), the automatic generation of logical objects from the ResultSet principle is similar, is a reverse process, see the end of the code for details.

Here's a complete example of how to use it:

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 simple to encapsulate, and there are more requirements to improve upon.