Java-Sqlite动态创建SQL语句和映射属性

发表于 | 分类于 | |

Java利用映射机制来动态创建SQL语句。

原理

Java有映射机制，可以根据Class来获取其属性的名称，类型以及值。

例如：

Field[] fs = this.getClass().getDeclaredFields();
for(int index = 0; index < fs.length; ++index){
    Field f = fs[index];
   f.setAccessible(true);
   String name = f.getName();
   String type = f.getType().toString(); 
   Object value = f.get(this);
}

注意：继承的属性获取不到。

Sqlite语法有一定的规律。

(1)建表：

CREATE TABLE database_name.table_name(
    column1 datatype  PRIMARY KEY(one or more columns),
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
);

(2)插入：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

(3)更新：

UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];

(4)查询：

SELECT * FROM table_name WHERE [condition];

(5)删除：

DELETE FROM table_name WHERE [condition];

结合

将两者结合在一起，即可以实现在Java Class中自动管理Sqlite数据库，包括建表，增删改查等，具体思路参考下文。

思路

一个Class管理一个表，彼此之间实现解耦，但是由于语法基本一致，可以直接在一个基类中定义好大部分的SQL语句模板，而在运行时利用多态，获取到子类的属性，并添加到SQL语句模板中，从而给Sqlite底层调用。

考虑到，还需要定义一些索引和约束(主外键等)，这些只能在子类中进行自定义配置，然后基类进行获取。

反过来，从Sqlite获取到值后，还需要一一反映射到子类的属性中。

实现

实现基类

定义基类

public abstract class BaseDto {
    public Integer id;
    public abstract String tableName();
    protected abstract HashMap<String, String> fieldConstraints();
    protected abstract String tableConstraint();

    //后续方法见下文
}
  • 由于每个表都需要id，所以直接在基类中定义好，子类继承即可；
  • 表名需要子类配置，实现为虚函数；
  • fieldConstraints是用于表属性的额外配置，例如，标题是唯一的，fieldConstraints.put(“title”, “UNIQUE”);
  • tableConstraint是用于表的约束，例如，外键，”foreign key(vehicle) references vehicle(id)”;

建表SQL

public String tableString(){
    String tableStr = "CREATE TABLE IF NOT EXISTS " + this.tableName() + "(id INTEGER PRIMARY KEY, ";
    try{
        HashMap<String, String> fieldConstraints = fieldConstraints();
        String tableConstraint = tableConstraint();
        Field[] fs = this.getClass().getDeclaredFields();
        for(int index = 0; index < fs.length; ++index){
               Field f = fs[index];
               f.setAccessible(true);
               tableStr += f.getName() + " ";
               String type = f.getType().toString(); 
               if(type.endsWith("String"))
                   tableStr += "TEXT ";
               else if(type.endsWith("int") || type.endsWith("Integer") ||
                       type.endsWith("boolean") || type.endsWith("Boolean"))
                   tableStr += "INTEGER ";
               else if(type.endsWith("float") || type.endsWith("Float"))
                   tableStr += "REAL ";
               else if(type.endsWith("double") || type.endsWith("Double"))
                   tableStr += "REAL ";
               else if(type.endsWith("Date"))
                   tableStr += "DATETIME ";
               else 
                   throw new Exception("tableString format tranfer error..");
               if(null != fieldConstraints && fieldConstraints.containsKey(f.getName()))
                   tableStr += fieldConstraints.get(f.getName());
               if(index < fs.length - 1)
                   tableStr += ", ";
               else if(null != tableConstraint && !tableConstraint.isEmpty())
                   tableStr += ", " + tableConstraint;
        }
        tableStr +=  " );";
    }catch(Exception ex){
        ex.printStackTrace();
    }
    return tableStr;
}

这里，需要注意的是，sqlite与Java支持的数据类型是不同的，需要进行一下类型转换。

插入SQL

public String insertString(){
    String insertStr = "INSERT OR IGNORE INTO " + this.tableName() + " VALUES (" + id + ", ";
    try{
        Field[] fs = this.getClass().getDeclaredFields();  
        for(int index = 0; index < fs.length; ++index){
               Field f = fs[index];
               f.setAccessible(true);
               String type = f.getType().toString(); 
               if(type.endsWith("String"))
                   insertStr += "'" + f.get(this) + "'";
               else if(type.endsWith("int"))
                   insertStr += f.getInt(this);
               else if(type.endsWith("boolean"))
                   insertStr += f.getBoolean(this) ? 1 : 0;
               else if(type.endsWith("float"))
                   insertStr += f.getFloat(this);
               else if(type.endsWith("double"))
                   insertStr += f.getDouble(this);
               else if(type.endsWith("Integer") || type.endsWith("Float") || type.endsWith("Double") || type.endsWith("Boolean"))
                   insertStr += f.get(this);
               else if(type.endsWith("Date"))
                   insertStr += "'" + TimeUtil.dateToString((Date)f.get(this)) + "'";
               else 
                   throw new Exception("insertStr format tranfer error..");
               if(index < fs.length - 1)
                   insertStr += ", ";
           }
        insertStr += ");";
    }catch(Exception ex){
        ex.printStackTrace();
    }
    return insertStr;
}

更新SQL

public String updateString(){
    String updateStr = "UPDATE " + this.tableName() + " SET id = " + id + ", ";
    try{
        Field[] fs = this.getClass().getDeclaredFields();  
        for(int index = 0; index < fs.length; ++index){
               Field f = fs[index];
               f.setAccessible(true);
               updateStr += f.getName() + " = ";
               String type = f.getType().toString(); 
               if(type.endsWith("String"))
                   updateStr += "'" + f.get(this) + "'";
               else if(type.endsWith("int"))
                   updateStr += f.getInt(this);
               else if(type.endsWith("boolean"))
                   updateStr += f.getBoolean(this) ? 1 : 0;
               else if(type.endsWith("float"))
                   updateStr += f.getFloat(this);
               else if(type.endsWith("double"))
                   updateStr += f.getDouble(this);
               else if(type.endsWith("Integer") || type.endsWith("Float") || type.endsWith("Double") || type.endsWith("Boolean"))
                   updateStr += f.get(this);
               else if(type.endsWith("Date"))
                   updateStr += "'" + TimeUtil.dateToString((Date)f.get(this)) + "'";
               else 
                   throw new Exception("updateStr format tranfer error..");
               if(index < fs.length - 1)
                   updateStr += ", ";
           }
        updateStr += " WHERE id = " + id + ";";
    }catch(Exception ex){
        ex.printStackTrace();
    }
    return updateStr;
}

删除SQL

public String deleteString(){
    String deleteString = "DELETE FROM " + this.tableName() + " WHERE id = " + id + ";";
    return deleteString;
}

从Sqlite的ResultSet取值反映射到属性中

public void fromResultSet(ResultSet rs){
    try{
        id = (Integer) rs.getObject("id");
        Field[] fs = this.getClass().getDeclaredFields();  
        for(int index = 0; index < fs.length; ++index){
               Field f = fs[index];
               f.setAccessible(true);
               String type = f.getType().toString(); 
               if(type.endsWith("String"))
                   f.set(this, rs.getString(f.getName()));
               else if(type.endsWith("int"))
                   f.set(this, rs.getInt(f.getName()));
               else if(type.endsWith("boolean"))
                   f.set(this, rs.getBoolean(f.getName()));
               else if(type.endsWith("float"))
                   f.set(this, rs.getFloat(f.getName()));
               else if(type.endsWith("double"))
                   f.set(this, rs.getDouble(f.getName()));
               else if(type.endsWith("Integer") || type.endsWith("Float") || type.endsWith("Double") || type.endsWith("Boolean"))
                   f.set(this, rs.getObject(f.getName()));
               else if(type.endsWith("Date")){
                   f.set(this, TimeUtil.stringToDate(rs.getString(f.getName())));
               }
               else 
                   throw new Exception("fromResultSet format tranfer error..");
           }
    }catch(Exception ex){
        ex.printStackTrace();
    }
}

定义子类

子类与数据库表一一对应，例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public class SSDto extends BaseDto{

	public Integer vehicle;
	public String name;
	public String address;
	public Double lat;
	public Double lng;
	public String city;
	public String tel;
	
	
	@Override
	public String tableName() {
		return "ss";
	}

	@Override
	protected HashMap<String, String> fieldConstraints() {
		HashMap<String, String> fieldConstraints = new HashMap<String, String>();
		fieldConstraints.put("address", "UNIQUE");
		return fieldConstraints;
	}

	@Override
	protected String tableConstraint() {
		return "foreign key(vehicle) references vehicle(id)";
	}

}

定义DAO

DAO层在Database和Dto之间搭建桥梁，并封装一层，给外部调用。Sqlite连接参考Java-Sqlite连接。

例如：

private void create(BaseDto dto){
    DBManager.manager().executeSQL(dto.tableString());
}

public void add(BaseDto dto){
    DBManager.manager().executeSQL(dto.insertString());
}

public void update(BaseDto dto){
    DBManager.manager().executeSQL(dto.updateString());
}

public void delete(BaseDto dto){
    DBManager.manager().executeSQL(dto.deleteString());
}

public BaseDto query(BaseDto dto, String sql){
    ArrayList<BaseDto> dtos = queryAll(dto, sql);
    if(!dtos.isEmpty())
        return dtos.get(0);
    return null;
}

运行

public void main(String[] s){
    Dao.dao().create(new SSDto());
}

用Sqlite管理软件打开db文件，可以看到表已经创建成功了。增删改查这里就不一一列举了。

JAVA-SQLITE-CREATE