org.apache.commons.dbutils.handlers.MapListHandler Java Examples

The following examples show how to use org.apache.commons.dbutils.handlers.MapListHandler. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: DbUtilsMapListHandler.java    From maven-framework-project with MIT License 8 votes vote down vote up
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);
	}
}
 
Example #2
Source File: EsJdbcManager.java    From tunnel with Apache License 2.0 6 votes vote down vote up
public static List<Map<String, Object>> query(String sql, String slot, String url, String username, String password) {

        QueryRunner qr = new QueryRunner();
        Connection connection = null;
        try {
            connection = getConnection(slot, url, username, password);
            List<Map<String, Object>> result = qr.query(connection, sql, new MapListHandler());
            if (CollectionUtils.isNotEmpty(result)) {
                return result;
            }
        } catch (SQLException e) {
            //
        } finally {
            close(connection);
        }
        return new ArrayList<>();
    }
 
Example #3
Source File: DbUtilsDemo.java    From JavaTutorial with Apache License 2.0 6 votes vote down vote up
/**
 * 将查询结果集转换成键值对列表返回。
 * 
 * @param ds JDBC连接池
 */
public void queryMapList(DataSource ds) {
    String sql = "select userId, userName, gender, age from student";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<List<Map<String, Object>>> handler = new MapListHandler();
    List<Map<String, Object>> result = null;
    try {
        result = run.query(sql, handler);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    if (null == result) {
        return;
    }
    for (Map<String, Object> map : result) {
        System.out.println(map);
    }
}
 
Example #4
Source File: MapListHandlerExample.java    From maven-framework-project with MIT License 6 votes vote down vote up
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);
		}

	}
 
Example #5
Source File: SybaseAseMetadataDialect.java    From obevo with Apache License 2.0 6 votes vote down vote up
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(jdbc.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);
        }
    });
}
 
Example #6
Source File: SybaseAseMetadataDialect.java    From obevo with Apache License 2.0 6 votes vote down vote up
@Override
public ImmutableCollection<DaRule> searchRules(final DaSchema schema, Connection conn) throws SQLException {
    // 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(jdbc.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);
        }
    });
}
 
Example #7
Source File: PostgresqlMetadataDialect.java    From obevo with Apache License 2.0 6 votes vote down vote up
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) {
    String sql = "SELECT t.typname \n" +
            "FROM        pg_type t \n" +
            "LEFT JOIN   pg_catalog.pg_namespace n ON n.oid = t.typnamespace \n" +
            "WHERE       (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) \n" +
            "AND     NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n" +
            "AND     n.nspname IN ('" + schema.getName() + "')";
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(jdbc.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("typname"), schema);
        }
    });
}
 
Example #8
Source File: HsqlMetadataDialect.java    From obevo with Apache License 2.0 6 votes vote down vote up
@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn) throws SQLException {
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(jdbc.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);
        }
    });
}
 
Example #9
Source File: MsSqlMetadataDialect.java    From obevo with Apache License 2.0 6 votes vote down vote up
@Override
public ImmutableCollection<DaRule> searchRules(final DaSchema schema, Connection conn) throws SQLException {
    // 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(jdbc.query(conn, sql, new MapListHandler())).toImmutable();

    return maps.collect(map -> new DaRuleImpl((String) map.get("RULE_NAME"), schema));
}
 
Example #10
Source File: SameSchemaDbChecksumDao.java    From obevo with Apache License 2.0 6 votes vote down vote up
@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();
        }
    });
}
 
Example #11
Source File: EsJdbcManager.java    From tunnel with Apache License 2.0 6 votes vote down vote up
public static List<Map<String, Object>> query(String sql, String slot, String url, String username, String password) {

        QueryRunner qr = new QueryRunner();
        Connection connection = null;
        try {
            connection = getConnection(slot, url, username, password);
            List<Map<String, Object>> result = qr.query(connection, sql, new MapListHandler());
            if (CollectionUtils.isNotEmpty(result)) {
                return result;
            }
        } catch (SQLException e) {
            //
        } finally {
            close(connection);
        }
        return new ArrayList<>();
    }
 
Example #12
Source File: QueryRunnerUtils.java    From webtau with Apache License 2.0 6 votes vote down vote up
static TableData runQuery(DataSource dataSource, String query) {
    QueryRunner run = new QueryRunner(dataSource);
    MapListHandler handler = new MapListHandler();

    try {
        List<Map<String, Object>> result = run.query(query, handler);
        if (result.isEmpty()) {
            return new TableData(Collections.emptyList());
        }

        List<String> columns = result.get(0).keySet().stream()
                .map(String::toUpperCase)
                .collect(Collectors.toList());

        TableDataHeader header = new TableDataHeader(columns.stream());
        TableData tableData = new TableData(header);
        result.forEach(row -> tableData.addRow(row.values().stream()));

        return tableData;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}
 
Example #13
Source File: DbConnect.java    From mcg-helper with Apache License 2.0 6 votes vote down vote up
/**
 * 带可变参数查询,返回执行结果
 * 
 * @param sql 查询sql
 * @param para 可变参数
 * @return
 */
public List<Map<String, Object>> querySql(String sql, Object... para) throws SQLException {
	logger.debug("查询Sql: {}, 查询参数: {}", sql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
	try {
		conn = getConnection();
		result = runner.query(conn, sql, new MapListHandler(), para);
	} catch (SQLException e) {
		logger.error("查询出错,异常信息: {}", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return result;
}
 
Example #14
Source File: DefaultDataAccessor.java    From smart-framework with Apache License 2.0 5 votes vote down vote up
@Override
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("查询出错!", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return fieldMapList;
}
 
Example #15
Source File: SongInformationAccessor.java    From beatoraja with GNU General Public License v3.0 5 votes vote down vote up
/**
 * 楽曲データベースを初期テーブルを作成する。 すでに初期テーブルを作成している場合は何もしない。
 */
private void createTable() {
	try {
		// songテーブル作成(存在しない場合)
		if (qr.query("SELECT * FROM sqlite_master WHERE name = ? and type='table';", new MapListHandler(),
				"information").size() == 0) {
			qr.update("CREATE TABLE [information] (" + "[sha256] TEXT NOT NULL," + "[n] INTEGER," + "[ln] INTEGER,"
					+ "[s] INTEGER," + "[ls] INTEGER," + "[total] REAL," + "[density] REAL," + "[peakdensity] REAL," + "[enddensity] REAL," + "[distribution] TEXT," + "PRIMARY KEY(sha256));");
		}
		if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%peakdensity%'", new MapListHandler()).size() == 0) {
			qr.update("ALTER TABLE information ADD COLUMN peakdensity [REAL]");
		}
		if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%enddensity%'", new MapListHandler()).size() == 0) {
			qr.update("ALTER TABLE information ADD COLUMN enddensity [REAL]");
		}
		if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%mainbpm%'", new MapListHandler()).size() == 0) {
			qr.update("ALTER TABLE information ADD COLUMN mainbpm [REAL]");
		}
		if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%speedchange%'", new MapListHandler()).size() == 0) {
			qr.update("ALTER TABLE information ADD COLUMN speedchange [TEXT]");
		}
		if(qr.query("SELECT * FROM sqlite_master WHERE name = 'information' AND sql LIKE '%lanenotes%'", new MapListHandler()).size() == 0) {
			qr.update("ALTER TABLE information ADD COLUMN lanenotes [TEXT]");
		}

	} catch (SQLException e) {
		Logger.getGlobal().severe("楽曲データベース初期化中の例外:" + e.getMessage());
	}
}
 
Example #16
Source File: ScoreLogDatabaseAccessor.java    From beatoraja with GNU General Public License v3.0 5 votes vote down vote up
public void createTable() {
	String sql = "SELECT * FROM sqlite_master WHERE name = ? and type='table';";
	// scorelogテーブル作成(存在しない場合)
	try {
		if (qr.query(sql, new MapListHandler(), "scorelog").size() == 0) {
			qr.update("CREATE TABLE [scorelog] ([sha256] TEXT NOT NULL," + "[mode] INTEGER," + "[clear] INTEGER," + "[oldclear] INTEGER,"
					+ "[score] INTEGER," + "[oldscore] INTEGER," + "[combo] INTEGER,"  + "[oldcombo] INTEGER," 
					+ "[minbp] INTEGER," + "[oldminbp] INTEGER," + "[date] INTEGER);");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
 
Example #17
Source File: ScoreDataImporter.java    From beatoraja with GNU General Public License v3.0 5 votes vote down vote up
public void importFromLR2ScoreDatabase(String path, SongDatabaseAccessor songdb) {
    final int[] clears = { 0, 1, 4, 5, 6, 8, 9 };
    scoredb.createTable();

    try (Connection con = DriverManager.getConnection("jdbc:sqlite:" + path)) {
        QueryRunner qr = new QueryRunner();
        MapListHandler rh = new MapListHandler();
        List<Map<String, Object>> scores = qr.query(con, "SELECT * FROM score", rh);

        List<ScoreData> result = new ArrayList<ScoreData>();
        for (Map<String, Object> score : scores) {
            final String md5 = (String) score.get("hash");
            SongData[] song = songdb.getSongDatas(new String[] { md5 });
            if (song.length > 0) {
                ScoreData sd = new ScoreData();
                sd.setEpg((int) score.get("perfect"));
                sd.setEgr((int) score.get("great"));
                sd.setEgd((int) score.get("good"));
                sd.setEbd((int) score.get("bad"));
                sd.setEpr((int) score.get("poor"));
                sd.setMinbp((int) score.get("minbp"));
                sd.setClear(clears[(int) score.get("clear")]);
                sd.setPlaycount((int) score.get("playcount"));
                sd.setClearcount((int) score.get("clearcount"));
                sd.setSha256(song[0].getSha256());
                sd.setNotes(song[0].getNotes());
                result.add(sd);
            }
        }
        
        this.importScores(result.toArray(new ScoreData[result.size()]), "LR2");
    } catch (Exception e) {
        Logger.getGlobal().severe("スコア移行時の例外:" + e.getMessage());
    }
}
 
Example #18
Source File: DBUtil.java    From util with Apache License 2.0 5 votes vote down vote up
/**
 * 查询sql语句。
 * @param sql 被执行的sql语句
 * @return List<Map<String,Object>>
 * @throws SQLException 
 */
public List<Map<String,Object>> query(String sql) throws SQLException {
	List<Map<String,Object>> results = null;
	Connection conn = null;
	try {
		conn = getConnection();
		QueryRunner qr = new QueryRunner();
		results =qr.query(conn, sql, new MapListHandler());
	}  finally {
		Close(conn);
	}
	return results;
}
 
Example #19
Source File: DBUtil.java    From util with Apache License 2.0 5 votes vote down vote up
/**
 * 根据参数查询sql语句
 * @param sql sql语句
 * @param param 参数
 * @return List<Map<String,Object>>
 * @throws SQLException 
 */
public List<Map<String,Object>> query(String sql, Object param) throws SQLException {
	List<Map<String,Object>> results = null;
	Connection conn = null;
	try {
		conn = getConnection();
		QueryRunner qr = new QueryRunner();
		results = (List<Map<String,Object>>) qr.query(conn, sql, param, new MapListHandler());
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		Close(conn);
	}
	return results;
}
 
Example #20
Source File: DBUtil.java    From util with Apache License 2.0 5 votes vote down vote up
/**
 * 根据参数查询sql语句
 * @param sql sql语句
 * @param param 参数数组
 * @return List<Map<String,Object>>
 * @throws SQLException 
 */
public List<Map<String,Object>> query(String sql, Object... param) throws SQLException {
	List<Map<String,Object>> results = null;
	Connection conn = null;
	try {
		conn = getConnection();
		QueryRunner qr = new QueryRunner();
		results = (List<Map<String,Object>>) qr.query(conn, sql, param, new MapListHandler());
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		Close(conn);
	}
	return results;
}
 
Example #21
Source File: MsSqlMetadataDialect.java    From obevo with Apache License 2.0 5 votes vote down vote up
@Override
public ImmutableCollection<DaRoutine> searchExtraRoutines(final DaSchema schema, String procedureName, Connection conn) throws SQLException {
    String nameClause = procedureName != null ? " and ROUTINE_NAME = '" + procedureName + "'\n" : " ";

    String query = "SELECT" +
            "    ROUTINE_CATALOG," +
            "    ROUTINE_SCHEMA," +
            "    ROUTINE_NAME," +
            "    SPECIFIC_NAME," +
            "    ROUTINE_TYPE," +
            "    OBJECT_DEFINITION(OBJECT_ID(ROUTINE_CATALOG + '.' + ROUTINE_SCHEMA + '.' + ROUTINE_NAME)) AS ROUTINE_DEFINITION" +
            " FROM INFORMATION_SCHEMA.ROUTINES" +
            " WHERE ROUTINE_CATALOG = '" + schema.getName() + "'" +
            " AND ROUTINE_SCHEMA = '" + schema.getSubschemaName() + "'" +
            nameClause;
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(jdbc.query(conn, query, new MapListHandler())).toImmutable();

    return maps.collect(object -> {
        DaRoutineType routineType = DaRoutineType.valueOf(((String) object.get("ROUTINE_TYPE")).toLowerCase());
        return new DaRoutinePojoImpl(
                (String) object.get("ROUTINE_NAME"),
                schema,
                routineType,
                (String) object.get("SPECIFIC_NAME"),
                (String) object.get("ROUTINE_DEFINITION")
        );
    });
}
 
Example #22
Source File: DefaultDataAccessor.java    From smart-framework with Apache License 2.0 5 votes vote down vote up
@Override
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("查询出错!", e);
        throw new RuntimeException(e);
    }
    printSQL(sql);
    return fieldMapList;
}
 
Example #23
Source File: DbUtilsTest2.java    From maven-framework-project with MIT License 5 votes vote down vote up
public static void main(String[] args) {
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

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

	List<Map<String, Object>> userMaps = null;
	try {
		// Loading the Driver using DbUtils static method
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT * FROM user");
		MapListHandler mapListHandler = new MapListHandler();
		userMaps = mapListHandler.handle(rs);

		for (Map<String, Object> mapObj : userMaps) {
			System.out.println("User Object::  " + mapObj.get("userId")
					+ "\t" + mapObj.get("lastName") + "\t"
					+ mapObj.get("phoneNo"));
		}

	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		// Closing the connection quietly, means it will handles the
		// SQLException
		DbUtils.closeQuietly(conn);
	}
}
 
Example #24
Source File: EsPublisher.java    From tunnel with Apache License 2.0 5 votes vote down vote up
private List<Map<String, Object>> execute(String sql, InvokeContext context) {
    try (Connection conn = getConnection(context)) {
        QueryRunner qr = new QueryRunner();
        List<Map<String, Object>> list = qr.query(conn, sql, new MapListHandler());
        if (list == null) {
            list = new LinkedList<>();
        }
        return list;
    } catch (Exception e) {
        //
    }
    return new LinkedList<>();
}
 
Example #25
Source File: DbUtilsUnitTest.java    From tutorials with MIT License 5 votes vote down vote up
@Test
public void givenResultHandler_whenExecutingQuery_thenExpectedList() throws SQLException {
    MapListHandler beanListHandler = new MapListHandler();

    QueryRunner runner = new QueryRunner();
    List<Map<String, Object>> list = runner.query(connection, "SELECT * FROM employee", beanListHandler);

    assertEquals(list.size(), 5);
    assertEquals(list.get(0).get("firstname"), "John");
    assertEquals(list.get(4).get("firstname"), "Christian");
}
 
Example #26
Source File: EsPublisher.java    From tunnel with Apache License 2.0 5 votes vote down vote up
private List<Map<String, Object>> execute(String sql, InvokeContext context) {
    try (Connection conn = getConnection(context)) {
        QueryRunner qr = new QueryRunner();
        List<Map<String, Object>> list = qr.query(conn, sql, new MapListHandler());
        if (list == null) {
            list = new LinkedList<>();
        }
        return list;
    } catch (Exception e) {
        //
    }
    return new LinkedList<>();
}
 
Example #27
Source File: JDBCExample.java    From wisp with Apache License 2.0 5 votes vote down vote up
public static String resultSetToJson(String query) {
    Connection connection = null;
    List<Map<String, Object>> listOfMaps = null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306?"
                + "useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries"
                + "=true", "root", "123456");
    } catch (Exception ex) {
        System.err.println("***exception trying to connect***");
        ex.printStackTrace();
    }

    try {
        QueryRunner queryRunner = new QueryRunner();
        listOfMaps = queryRunner.query(connection, query, new MapListHandler());
    } catch (SQLException se) {
        throw new RuntimeException("Couldn't query the database.", se);
    } finally {
        DbUtils.closeQuietly(connection);
    }

    try {
        return new ObjectMapper().writeValueAsString(listOfMaps);

    } catch (JsonProcessingException e) {
        System.out.println(e.toString());
    }

    return null;
}
 
Example #28
Source File: SybaseAseMetadataDialect.java    From obevo with Apache License 2.0 5 votes vote down vote up
@Override
public ImmutableCollection<ExtraIndexInfo> searchExtraConstraintIndices(DaSchema schema, String tableName, Connection conn) throws SQLException {
    // Do not use ANSI JOIN as it does not work in Sybase 11.x - the SQL below works across all versions
    String tableClause = tableName == null ? "" : " AND tab.name = '" + tableName + "'";
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(jdbc.query(conn,
            "select tab.name TABLE_NAME, ind.name INDEX_NAME, status2 & 8 IS_CONSTRAINT, status2 & 512 IS_CLUSTERED " +
                    "from " + schema.getName() + "..sysindexes ind" +
                    ", " + schema.getName() + "..sysobjects tab " +
                    ", " + schema.getName() + "..sysusers sch " +
                    "where ind.id = tab.id " +
                    "and tab.uid = sch.uid and sch.name = '" + schema.getSubschemaName() + "'\n" +
                    tableClause,
            new MapListHandler()
    )).toImmutable();

    return maps.collect(new Function<Map<String, Object>, ExtraIndexInfo>() {
        @Override
        public ExtraIndexInfo valueOf(Map<String, Object> map) {
            return new ExtraIndexInfo(
                    (String) map.get("TABLE_NAME"),
                    (String) map.get("INDEX_NAME"),
                    (Integer) map.get("IS_CONSTRAINT") != 0,
                    (Integer) map.get("IS_CLUSTERED") != 0
            );
        }
    });
}
 
Example #29
Source File: MergeService.java    From jforgame with Apache License 2.0 5 votes vote down vote up
private void mergeTableCross(MergeServer parentServer, MergeServer childServer, String tableName) throws SQLException {
    logger.info("开始合并[{}]服{}表到{}服", childServer.getServerId(), tableName, parentServer.getServerId());
    Connection parentConn = JdbcUtils.getConnection(parentServer);
    Connection childConn = JdbcUtils.getConnection(childServer);
    MergeTable mergeTable = MergedTableRegistry.getInstance().getTableMergeBehavior(tableName);
    List<Map<String, Object>> map1 = new QueryRunner().query(parentConn, "SELECT * FROM " + tableName, new MapListHandler());
    List<Map<String, Object>> map2 = new QueryRunner().query(childConn, "SELECT * FROM " + tableName, new MapListHandler());

    List<String> sqls = mergeTable.merge(map1, map2);
    parentConn.setAutoCommit(false);
    for (String sql : sqls) {
        parentConn.createStatement().executeUpdate(sql);
    }
    parentConn.commit();
}
 
Example #30
Source File: Db2MetadataDialect.java    From obevo with Apache License 2.0 5 votes vote down vote up
@Override
public ImmutableCollection<DaRoutine> searchExtraRoutines(final DaSchema schema, String procedureName, Connection conn) throws SQLException {
    String procedureClause = procedureName == null ? "" : " AND R.ROUTINENAME = '" + procedureName + "'";
    final String sql = "SELECT ROUTINENAME, SPECIFICNAME, TEXT FROM SYSCAT.ROUTINES R WHERE R.ROUTINETYPE = 'F'\n" +
            "AND R.ROUTINESCHEMA = '" + schema.getName() + "'\n" + procedureClause;
    LOG.debug("Executing function metadata query SQL: {}", sql);

    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(jdbc.query(conn,
            sql,
            new MapListHandler()
    )).toImmutable();

    if (LOG.isDebugEnabled()) {
        LOG.debug("Results:");
        for (Map<String, Object> map : maps) {
            LOG.debug("ROW: {}", map.toString());
        }
    }

    return maps.collect(map -> new DaRoutinePojoImpl(
            (String) map.get("ROUTINENAME"),
            schema,
            DaRoutineType.function,
            (String) map.get("SPECIFICNAME"),
            clobToString((Clob) map.get("TEXT"))
    ));
}