Java MapListHandler类代码示例

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
public static void main(String[] args) {
	Connection conn = null;

	final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
	final String driver = "org.h2.Driver";
	final String usr = "sa";
	final String pwd = "";

	try {
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		QueryRunner query = new QueryRunner();
		List<Map<String, Object>> mapList = (List<Map<String, Object>>) query
				.query(conn, "select * from user", new MapListHandler());
		for (int i = 0; i < mapList.size(); i++) {
			Map<String, Object> map = (Map<String, Object>) mapList.get(i);
			System.out.println("------> " + map.get("userId") + "\t"
					+ map.get("firstName") + "\t" + map.get("emailId"));
		}
	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);
		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------

		try {
			List<Map<String, Object>> maps = run.query(conn,
					"SELECT * FROM employee", new MapListHandler());
			System.out.println(maps);
		} finally {
			DbUtils.close(conn);
		}

	}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public ImmutableCollection<ChecksumEntry> getPersistedEntries(final PhysicalSchema physicalSchema) {
    return sqlExecutor.executeWithinContext(physicalSchema, new ThrowingFunction<Connection, ImmutableCollection<ChecksumEntry>>() {
        @Override
        public ImmutableCollection<ChecksumEntry> safeValueOf(Connection conn) throws Exception {
            return ListAdapter.adapt(jdbc.query(conn, "SELECT * FROM " + platform.getSchemaPrefix(physicalSchema) + checksumTableName, new MapListHandler())).collect(new Function<Map<String, Object>, ChecksumEntry>() {
                @Override
                public ChecksumEntry valueOf(Map<String, Object> result) {
                    return ChecksumEntry.createFromPersistence(
                            physicalSchema,
                            (String) result.get(objectTypeColumnName),
                            (String) result.get(objectName1ColumnName),
                            blankToNull((String) result.get(objectName2ColumnName)),
                            (String) result.get(checksumColumnName)
                    );
                }
            }).toImmutable();
        }
    });
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public ImmutableCollection<DaRule> searchRules(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();  // using queryRunner so that we can reuse the connection

    // Do not use ANSI JOIN as it does not work in Sybase 11.x - the SQL below works across all versions
    String sql = "SELECT rul.name as RULE_NAME\n" +
            "FROM " + schema.getName() + "..sysobjects rul\n" +
            "    , sys.schemas sch\n" +
            "WHERE rul.type = 'R'\n" +
            "    and rul.uid = sch.schema_id and sch.name = '" + schema.getSubschemaName() + "' " +
            "and not exists (\n" +
            "\t-- Ensure that the entry is not attached to a table; otherwise, it is a regular table constraint, and will already be dropped when the table is dropped\n" +
            "\tselect 1 from " + schema.getName() + "..sysconstraints c\n" +
            "\twhere c.constid = rul.id\n" +
            ")\n";
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn, sql, new MapListHandler())).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaRule>() {
        @Override
        public DaRule valueOf(Map<String, Object> map) {
            return new DaRuleImpl((String) map.get("RULE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();

    String sql = "SELECT DOMAIN_NAME as USER_TYPE_NAME " +
            "FROM INFORMATION_SCHEMA.DOMAINS " +
            "WHERE DOMAIN_CATALOG = '" + schema.getName() + "' " +
            "AND DOMAIN_SCHEMA = '" + schema.getSubschemaName() + "'";
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn, sql, new MapListHandler())).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaUserType>() {
        @Override
        public DaUserType valueOf(Map<String, Object> map) {
            return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();

    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn,
            "select dom.DOMAIN_NAME AS USER_TYPE_NAME\n" +
                    "from INFORMATION_SCHEMA.DOMAINS dom\n" +
                    "WHERE dom.DOMAIN_SCHEMA = ucase('" + schema.getName() + "')\n",
            new MapListHandler()
    )).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaUserType>() {
        @Override
        public DaUserType valueOf(Map<String, Object> map) {
            return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public ImmutableCollection<DaRule> searchRules(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();  // using queryRunner so that we can reuse the connection

    // Do not use ANSI JOIN as it does not work in Sybase 11.x - the SQL below works across all versions
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn,
            "SELECT rul.name as RULE_NAME\n" +
                    "FROM " + schema.getName() + "..sysobjects rul\n" +
                    "    , " + schema.getName() + "..sysusers sch\n" +
                    "WHERE rul.type = 'R'\n" +
                    "    and rul.uid = sch.uid and sch.name = '" + schema.getSubschemaName() + "' " +
                    "and not exists (\n" +
                    "\t-- Ensure that the entry is not attached to a table; otherwise, it is a regular table constraint, and will already be dropped when the table is dropped\n" +
                    "\tselect 1 from " + schema.getName() + "..sysconstraints c\n" +
                    "\twhere c.constrid = rul.id\n" +
                    ")\n",
            new MapListHandler()
    )).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaRule>() {
        @Override
        public DaRule valueOf(Map<String, Object> map) {
            return new DaRuleImpl((String) map.get("RULE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    QueryRunner query = new QueryRunner();

    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(query.query(conn,
            "SELECT s1.name as USER_TYPE_NAME\n" +
                    "FROM " + schema.getName() + "..systypes s1\n" +
                    "    , " + schema.getName() + "..sysusers sch\n" +
                    "WHERE s1.usertype>100 " +
                    "AND s1.uid = sch.uid and sch.name = '" + schema.getSubschemaName() + "' "
            , new MapListHandler()
    )).toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaUserType>() {
        @Override
        public DaUserType valueOf(Map<String, Object> map) {
            return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema);
        }
    });
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public <T> T createEntityNet(SqlBoxContext ctx, boolean loadKeyOnly, Object... configObjects) {
	if (configObjects == null || configObjects.length == 0)
		throw new TinyNetException("LoadNet() does not support empty netConfigs parameter");
	TableModel[] models = EntityNetSqlExplainer.objectConfigsToModels(ctx, configObjects);
	EntityNet net = new TinyNet();
	String starOrSharp = loadKeyOnly ? ".##" : ".**";
	for (TableModel t : models) {
		List<Map<String, Object>> mapList = null;
		String alias = t.getAlias();
		if (StrUtils.isEmpty(alias))
			alias = t.getTableName();
		try {
			mapList = ctx.nQuery(new MapListHandler(SqlBoxContext.netProcessor(t)),
					"select " + alias + starOrSharp + " from " + t.getTableName() + " as " + alias);
		} finally {
			EntityNetSqlExplainer.removeBindedTableModel(mapList);
		}
		net.addMapList(mapList, t);
	}
	return (T) net;
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Test
public void testJoinFields() {
	System.out.println("==============testJoinFields================ ");
	new User().put("id", "u1").put("userName", "user1").insert();
	new User().put("id", "u2").put("userName", "user2").insert();
	TinyNet net = ctx.netLoadSketch(User.class);
	Assert.assertEquals(2, net.size());
	List<User> users = net.getAllEntityList(User.class);
	Assert.assertNull(users.get(0).getUserName());

	User u = new User();
	u.tableModel().setAlias("u");
	List<Map<String, Object>> listMap = ctx.nQuery(new MapListHandler(),
			netConfig(u) + "select u.id as u_id, u.userName as u_userName from usertb as u");
	ctx.netJoinList(net, listMap);// not userName be joined

	Assert.assertEquals(2, net.size());
	users = net.getAllEntityList(User.class);
	Assert.assertNotNull(users.get(0).getUserName());
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Test
public void testJoinParents() {
	System.out.println("==============testJoinParents================ ");
	new User().put("id", "u1").put("userName", "user1").insert();
	new User().put("id", "u2").put("userName", "user2").insert();
	new Email().putFields("id", "emailName", "userId");
	new Email().putValues("e1", "email1", "u1").insert();
	new Email().putValues("e2", "email2", "u1").insert();
	List<Map<String, Object>> listMap = ctx.nQuery(new MapListHandler(),
			netConfig(new Email().alias("e")) + "select e.id as e_id from emailtb e");
	TinyNet net = (TinyNet) ctx.netCreate(listMap);
	Assert.assertEquals(2, net.size());
	Node emailNode = net.getOneNode(Email.class, "e1");
	Assert.assertNull(emailNode.getParentRelations());// e1 have no userId
														// field

	List<Map<String, Object>> listMap2 = ctx.nQuery(new MapListHandler(),
			netConfig(Email.class) + "select e.** from emailtb e");
	ctx.netJoinList(net, listMap2);

	Assert.assertEquals(2, net.size());
	emailNode = net.getOneNode(Email.class, "e1");
	Assert.assertNotNull(emailNode.getParentRelations());
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Test
public void testManualLoadAndJoin() {
	insertDemoData();
	List<Map<String, Object>> mapList1 = ctx.nQuery(new MapListHandler(netProcessor(User.class, Address.class)),
			"select u.**, a.** from usertb u, addresstb a where a.userId=u.id");
	TinyNet net = ctx.netCreate(mapList1);
	Assert.assertEquals(10, net.size());

	List<Map<String, Object>> mapList2 = ctx.nQuery(new MapListHandler(),
			netConfig(Email.class) + "select e.** from emailtb as e");
	ctx.netJoinList(net, mapList2);
	Assert.assertEquals(15, net.size());

	List<Map<String, Object>> mapList3 = ctx.nQuery(
			new MapListHandler(netProcessor(Role.class, UserRole.class, RolePrivilege.class, Privilege.class)),
			"select r.**, ur.**, rp.**, p.** from roletb r, userroletb ur, RolePrivilegetb rp, privilegetb p");
	Assert.assertEquals(900, mapList3.size());
	ctx.netJoinList(net, mapList3);
	Assert.assertEquals(37, net.size());
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
@Override
public <T> List<T> selectBySql(String sql, Object[] params, Class<T> modelType) {
    QueryRunner queryRunner = new QueryRunner();

    Connection conn = getDbConnectionFactory().getConnection();

    printSQL(sql, params);
    try {
        if (Model.class.isAssignableFrom(modelType)) {
            return (List<T>) queryRunner.query(conn, sql, new AnnotationBeanListHandler(modelType), params);
        } else if (Map.class.isAssignableFrom(modelType)) {
            return (List<T>) queryRunner.query(conn, sql, new MapListHandler(), params);
        } else {
            throw new RuntimeException("未知的映射类型:" + modelType);
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        getDbConnectionFactory().releaseConnection(conn);
    }
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
/**
 * //TODO 分页查询List<Map>
 * 
 * @param sql
 * @param page
 * @param pageSize
 * @param params
 * @return
 */
public static List<Map<String, Object>> findPage(String sql, Integer page, Integer pageSize, Object... params) {
    List<Map<String, Object>> fieldMapList;
    try {
        sql = sql + " LIMIT ?,?";
        List<Object> list = null;
        if (!CollectionUtil.isEmpty(params)) {
        	list = CollectionUtil.newArrayList(params.length + 2);
        	list.addAll(Arrays.asList(params));
        } else{
        	list = CollectionUtil.newArrayList(2);
        }
        list.add(page);
        list.add(pageSize);
        params = list.toArray();
        fieldMapList = getQueryRunner().query(sql, new MapListHandler(), params);
    } catch (SQLException e) {
        logger.error("queryMapListPage.map数据分页查询错误：" + sql, e);
        throw new QueryException(e);
    }
    logger.debug("sql：" + sql + "\n  params：" + Arrays.toString(params));
    return fieldMapList;
}
 

import org.apache.commons.dbutils.handlers.MapListHandler; //导入依赖的package包/类
/**
 * 查询对应的数据，返回多条记录（列名 => 数据）
 */
public List<Map<String, Object>> queryMapList(String sql, Object... params) {
    List<Map<String, Object>> fieldMapList;
    try {
        fieldMapList = queryRunner.query(sql, new MapListHandler(), params);
    } catch (SQLException e) {
        logger.error("查询出错！");
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return fieldMapList;
}