Java Db类代码示例

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public void generateMappingModel(Object projectId, String templatePath) {
  if ( StrKit.isBlank(templatePath) ) templatePath = MAVEN_BASE + "gen/config/4jfinalmappingmodel.btl";
  Template t_pojo = gt.getTemplate(templatePath);
  List<Record> list = Db.find("select a.*, b.name primary_name, b.java_type, c.package, c.module_name from w_db_model a, w_db_model_item b, w_generate c where a.id = b.w_model_id and c.w_model_id = a.id and b.is_primary = 1 and a.project_id = ?", projectId);
  t_pojo.binding("models", list);
  File file = getConfigGenerateFile(
          File.separator + "org" +
                  File.separator + "hacker" +
                  File.separator + "core" +
                  File.separator + "config",
          "MappingModel.java");
  System.out.println(file.getAbsolutePath());
  try {
    FileKit.write(t_pojo.render(), file);
  } catch ( FileNotFoundException | BeetlException e ) {
    e.printStackTrace();
  }
  System.out.println("############getGenerateConfig success############");
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public void generateMappingRoute(Object projectId, String templatePath) {
  if ( StrKit.isBlank(templatePath) ) templatePath = MAVEN_BASE + "gen/config/4jfinalmappingroute.btl";
  Template t_pojo = gt.getTemplate(templatePath);
  List<Record> list = Db.find("select a.*, b.name primary_name, b.java_type, c.package, c.module_name from w_db_model a, w_db_model_item b, w_generate c where a.id = b.w_model_id and c.w_model_id = a.id and b.is_primary = 1 and a.project_id = ?", projectId);
  t_pojo.binding("models", list);
  File file = getConfigGenerateFile(
          File.separator + "org" +
                  File.separator + "hacker" +
                  File.separator + "core" +
                  File.separator + "config",
          "MappingRoute.java");
  System.out.println(file.getAbsolutePath());
  try {
    FileKit.write(t_pojo.render(), file);
  } catch ( FileNotFoundException | BeetlException e ) {
    e.printStackTrace();
  }
  System.out.println("############getGenerateConfig success############");
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public void test_controller() {
  int id = 3;
  gt.registerFunction("firstCharToLowerCase", new FirstCharToLowerCase());
   DbModel model = DbModel.dao.findById(id);
   List<DbModelItem> columns = DbModelItem.dao.find("select * from w_db_model_item where w_model_id = ? order by serial", id);
   Generate generate = Generate.dao.findFirst("select * from w_generate where w_model_id = ?", id);

   // 查找出跟model有关的list
   List<Record> master = Db.find("select b.class_name, a.*, c.package, c.module_name from w_db_model_mapping a, w_db_model b, w_generate c where a.master_id = b.id and a.slaves_id = ? and a.master_id = c.w_model_id", id);
   // 查找出跟model相关的从表
   List<Record> slaves = Db.find("SELECT a.class_name, b.*, c.package, c.module_name FROM w_db_model a, w_db_model_mapping b, w_generate c WHERE a.id = b.slaves_id AND b.master_id = ? and c.w_model_id = b.slaves_id", id);

   Template t = gt.getTemplate("gen/web/4Jfinalcontroller.btl");
   t.binding("model", model);
   t.binding("columns", columns);

   t.binding("generate", generate);
   t.binding("master", master);
   t.binding("slaves", slaves);
   // 是否使用驼峰命名
   t.binding("camelName", true);
   System.out.println(t.render());
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public void test_service() {
  int id = 3;
  gt.registerFunction("firstCharToLowerCase", new FirstCharToLowerCase());
  DbModel model = DbModel.dao.findById(id);
  List<DbModelItem> columns = DbModelItem.dao.find("select * from w_db_model_item where w_model_id = ? order by serial", id);
  Generate generate = Generate.dao.findFirst("select * from w_generate where w_model_id = ?", id);

  // 查找出跟model有关的list
  List<Record> master = Db.find("select b.class_name, a.*, c.package, c.module_name from w_db_model_mapping a, w_db_model b, w_generate c where a.master_id = b.id and a.slaves_id = ? and a.master_id = c.w_model_id", id);
  // 查找出跟model相关的从表
  List<Record> slaves = Db.find("SELECT a.class_name, b.*, c.package, c.module_name FROM w_db_model a, w_db_model_mapping b, w_generate c WHERE a.id = b.slaves_id AND b.master_id = ? and c.w_model_id = b.slaves_id", id);

  Template t = gt.getTemplate("gen/web/4curd&webservice.btl");
  t.binding("model", model);
  t.binding("columns", columns);

  t.binding("generate", generate);
  t.binding("master", master);
  t.binding("slaves", slaves);
  // 是否使用驼峰命名
  t.binding("camelName", true);
  System.out.println(t.render());
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public void test_sqlmd() {
  int id = 3;
  gt.registerFunction("firstCharToLowerCase", new FirstCharToLowerCase());
  DbModel model = DbModel.dao.findById(id);
  List<DbModelItem> columns = DbModelItem.dao.find("select * from w_db_model_item where w_model_id = ? order by serial", id);
  Generate generate = Generate.dao.findFirst("select * from w_generate where w_model_id = ?", id);

  // 查找出跟model有关的list
  List<Record> master = Db.find("select b.class_name, a.*, c.package, c.module_name from w_db_model_mapping a, w_db_model b, w_generate c where a.master_id = b.id and a.slaves_id = ? and a.master_id = c.w_model_id", id);
  // 查找出跟model相关的从表
  List<Record> slaves = Db.find("SELECT a.class_name, b.*, c.package, c.module_name FROM w_db_model a, w_db_model_mapping b, w_generate c WHERE a.id = b.slaves_id AND b.master_id = ? and c.w_model_id = b.slaves_id", id);

  Template t = gt.getTemplate("gen/web/4mysqlmd.btl");
  t.binding("model", model);
  t.binding("columns", columns);

  t.binding("generate", generate);
  t.binding("master", master);
  t.binding("slaves", slaves);
  // 是否使用驼峰命名
  t.binding("camelName", true);
  System.out.println(t.render());
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public Page<Record> query_slice( int tabletype,int pageNum, int numPerPage) {
	Page<Record> recpage;
	List<Object> sqlParam = new ArrayList<Object>();
	String sql = null;

		switch(tabletype){
		case 1:{
			sql=" from score  m1  JOIN  (SELECT m2.id,m2.version,count(DISTINCT version) from score m2 GROUP BY m2.version) as m2 on m1.id=m2.id  join (SELECT m3.id,m3.version,count(DISTINCT version) from score m3 GROUP BY m3.version) as m3 ON m1.id=m3.id  " ;
		      break;
		}
		case 2:{
			sql="";
			  break;
		} 
		case 3:{
			sql="";
			  break;
		} 
		case 4:{
			sql="";
			  break;
		} 
		}
		recpage  = Db.paginate(pageNum, numPerPage,"SELECT m1.projectType,m1.version  ", sql,sqlParam.toArray());
	return recpage;
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
@SuppressWarnings({ "rawtypes", "unchecked" })
public static int[] batchSave(List<? extends Model> data, int batchSize) {
	Model model = data.get(0);
	Map<String, Object> attrs = (Map<String, Object>) ReflectionUtils.getFieldValue(model, "attrs");
	Class<? extends Model> modelClass = model.getClass();
	Table table = TableMapping.me().getTable(modelClass);
	StringBuilder sql = new StringBuilder();
	List<Object> paras = new ArrayList<Object>();
	DbKit.getConfig().getDialect().forModelSave(table, attrs, sql, paras);
	Object[][] batchPara = new Object[data.size()][attrs.size()];
	for (int i = 0; i < data.size(); i++) {
		int j = 0;
		for (String key : attrs.keySet()) {
			batchPara[i][j++] = data.get(i).get(key);
		}
	}
	return Db.batch(sql.toString(), batchPara, batchSize);
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
@Override
public void intercept(final Invocation inv) {
	Db.tx(new IAtom() {
		@Override
		public boolean run() throws SQLException {
			try {
				inv.invoke();
			} catch (Exception e) {
				if (inv.getTarget() instanceof ControllerExt) {
					ControllerExt controller = inv.getTarget();
					controller.onExceptionError(e);
				}
				return false;
			}
			return true;
		}
	});
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
@SuppressWarnings("rawtypes")
public static int[] batchSave(List<? extends Model> data, int batchSize) {
    Model model = data.get(0);
    Map<String, Object> attrs = Reflect.on(model).field("attrs").get();
    Class<? extends Model> modelClass = model.getClass();
    Table tableInfo = TableMapping.me().getTable(modelClass);
    StringBuilder sql = new StringBuilder();
    List<Object> paras = Lists.newArrayList();
    DbKit.getConfig().getDialect().forModelSave(tableInfo, attrs, sql, paras);
    Object[][] batchPara = new Object[data.size()][attrs.size()];
    for (int i = 0; i < data.size(); i++) {
        int j = 0;
        for (String key : attrs.keySet()) {
            batchPara[i][j++] = data.get(i).get(key);
        }
    }
    return Db.batch(sql.toString(), batchPara, batchSize);
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
/**
 * 根据多个数据通过主键批量删除数据
 *
 * @param ids        要删除的数据值数组
 * @param modelClass 对应的Model的Class
 * @return 是否删除成功
 */
public static <M extends Model> boolean deleteByIds(Serializable[] ids, Class<M> modelClass) {
    final Table table = TableMapping.me().getTable(modelClass);
    final String[] primaryKey = table.getPrimaryKey();
    if (primaryKey == null || primaryKey.length < 1 || ids == null) {
        throw new IllegalArgumentException("需要删除的表数据主键不存在，无法删除!");
    }
    // 暂时支持单主键的，多主键的后续在说吧。
    final String question_mark =
            StringUtils.repeat(StringPool.QUESTION_MARK, StringPool.COMMA, ids.length);
    String deleteSql = "DELETE FROM "
            + table.getName()
            + " WHERE "
            + primaryKey[0]
            + " IN ("
            + question_mark
            + ")";
    return Db.update(deleteSql, ids) >= 0;
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
/**
 * Paging retrieve, default sorted by id, you need to specify the datatables request parameters.
 *
 * @param sqlPaginatePrefix 分页搜索前缀
 * @param pageDto           required parameter.
 * @return Paging data.
 */
public static Page<Record> paginate(String sqlPaginatePrefix,
                                    PageDto pageDto) {
    SqlNode sqlNode = SqlKit.sqlNode(sqlPaginatePrefix + ".paginate");
    Preconditions.checkNotNull(sqlNode, "[" + sqlPaginatePrefix + ".paginate]分页Sql不存在,无法执行分页");
    String where = sqlNode.whereSql;
    int pageSize = pageDto.pageSize;
    int p = pageDto.page;
    int start = ((p - 1) * pageSize) + 1;
    final List<RequestParam> params = pageDto.params;
    final List<Object> query_params = pageDto.query_params;
    if ((params.isEmpty()) && (query_params.isEmpty())) {
        return Db.paginate(start, pageSize, sqlNode.selectSql, where);
    } else {

        if (!params.isEmpty()) {
            where += (sqlNode.condition ? StringPool.SPACE : " WHERE 1=1 ");
        }
        for (RequestParam param : pageDto.params) {
            where += param.toSql();
        }
        return Db.paginate(start, pageSize, sqlNode.selectSql, where, query_params.toArray());
    }
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public static List<Record> getResult(String id, Map<String, Object> paras){
	String _sql = get(id, paras);
	Object[] objs = new Object[paras.size()];
	Iterator<String> it = paras.keySet().iterator();
	int index = 0;
	while (it.hasNext()) {
		String key = it.next();
		Object obj = paras.get(key);
		objs[index++] = obj;
	}
	return Db.find(_sql, objs);
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
public void test_pojo() {
    // 基础目录
//    String basePath = "";
    int id = 5;
    gt.registerFunction("camelNameConvert", new CamelNameConvert());
    gt.registerFunction("firstCharToLowerCase", new FirstCharToLowerCase());
    gt.registerFunction("toLowerCase", new ToLowerCase());

    DbModel model = DbModel.dao.findById(id);
    List<DbModelItem> columns = DbModelItem.dao.find("select * from w_db_model_item where w_model_id = ? order by serial", id);
    Generate generate = Generate.dao.findFirst("select * from w_generate where w_model_id = ?", id);

    // 查找出跟model有关的list
    List<Record> master = Db.find("select b.class_name, a.*, c.package, c.module_name from w_db_model_mapping a, w_db_model b, w_generate c where a.master_id = b.id and a.slaves_id = ? and a.master_id = c.w_model_id", id);
    // 查找出跟model相关的从表
    List<Record> slaves = Db.find("SELECT a.class_name, b.*, c.package, c.module_name FROM w_db_model a, w_db_model_mapping b, w_generate c WHERE a.id = b.slaves_id AND b.master_id = ? and c.w_model_id = b.slaves_id", id);

    Template t = gt.getTemplate("gen/pojo/4ActiveRecordEnhance.btl");
    t.binding("model", model);
    t.binding("columns", columns);

    t.binding("importNotNull", true);
    t.binding("importLength", true);
    t.binding("importNotBlank", true);

    t.binding("generate", generate);
    t.binding("master", master);
    t.binding("slaves", slaves);
    // 是否使用驼峰命名
    t.binding("camelName", true);
    System.out.println(t.render());
  }
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
@Override
protected void validate(Controller c) {
	validateRequiredString("user.username", "category_error", "用户名不能为空");
	int userId = 0;

	String email = c.getPara("user.email");
	String username = c.getPara("user.username");
	Integer[] is = c.getParaValuesToInt("user_role");
	int id = 0;
	if (email.length() != 0) {
		validateEmail("user.email", "text_error", "email格式不正确");
	}
	if (is == null) {
		addError("role_error", "必须选择一个角色");
	}
	if (userId == 0) {
		validateRequiredString("user.password", "text_error", "密码不能为空");
		validateEqualField("user.password", "vpassword", "text_error", "密码与确认密码不匹配");
	}

	if (username.length() != 0) {
		Record count = null;
		count = Db
				.findFirst("select count(id) as count from user where username='"
						+ username + "' and id<>" + id);
		if (count.getLong("count") != 0) {
			addError("codeRepeat_error", "已存在此用户");
		}
		;
	}
}
 

import com.jfinal.plugin.activerecord.Db; //导入依赖的package包/类
/**
 * 判断是否count 是否为0
 * 
 * @param tableName
 * @param whereSql
 * @param Object
 *            ...parameter
 */
protected boolean CheckCount(String tableName, String whereSql,
		Object... parameter) {
	boolean counts = false;
	String sql = "select count(id) as count from " + tableName + " where "
			+ whereSql;
	Record count = Db.findFirst(sql, parameter);
	if (count.getLong("count") == 0) {
		counts = true;
	}
	return counts;
}