/**
* Returns a list of saved queries
*
* @param criteria a multivalued map that has the filter criteria
* @param start Displacement from the start of the search result
* @param count Count of number of records required
* @return list of saved queries
* @throws LensException
*/
public ListResponse getList(MultivaluedMap<String, String> criteria, long start, long count)
throws LensException {
final StringBuilder selectQueryBuilder =
new StringBuilder("select * from " + SAVED_QUERY_TABLE_NAME);
final Set<String> availableFilterKeys = FILTER_KEYS.keySet();
final Sets.SetView<String> intersection =
Sets.intersection(availableFilterKeys, criteria.keySet());
if (intersection.size() > 0) {
final StringBuilder whereClauseBuilder = new StringBuilder(" where ");
final List<String> predicates = Lists.newArrayList();
for (String colName : intersection) {
predicates.add(
FILTER_KEYS.get(colName).resolveFilterExpression(colName, criteria.getFirst(colName)));
}
Joiner.on(" and ").skipNulls().appendTo(whereClauseBuilder, predicates);
selectQueryBuilder.append(whereClauseBuilder.toString());
}
final String listCountQuery =
"select count(*) as "
+ VALUE_ALIAS
+ " from ("
+ selectQueryBuilder.toString()
+ ") tmp_table";
selectQueryBuilder.append(" limit ").append(start).append(", ").append(count);
final String listQuery = selectQueryBuilder.toString();
try {
return new ListResponse(
start,
runner.query(listCountQuery, new SingleValuedResultHandler()),
runner.query(listQuery, new SavedQueryResultSetHandler()));
} catch (SQLException e) {
throw new LensException("List query failed!", e);
}
}
@Override
public <T> T selectOneBySql(String sql, Object[] params, Class<T> classz) {
QueryRunner queryRunner = new QueryRunner();
Connection conn = getDbConnectionFactory().getConnection();
printSQL(sql, params);
try {
if (classz.isAssignableFrom(Model.class)) {
List<T> list =
(List<T>) queryRunner.query(conn, sql, new AnnotationBeanListHandler(classz), params);
if (list.size() > 0) {
return list.get(0);
}
return null;
} else if (baseType.contains(classz)) {
return queryRunner.query(conn, sql, new ScalarHandler<T>(), params);
} else {
Map map = queryRunner.query(conn, sql, new MapHandler(), params);
return (T) map;
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
getDbConnectionFactory().releaseConnection(conn);
}
}
public static void main(String[] args) {
conn = getConnection();
QueryRunner qr = new QueryRunner();
try {
conn.setAutoCommit(false);
List<Syuser> al =
(List)
qr.query(
conn, "SELECT syuser.* FROM syuser", new BeanListHandler<Syuser>(Syuser.class));
for (Syuser u : al) {
List<Syrole> rl =
qr.query(
conn,
"SELECT syrole.* FROM syrole JOIN syuser_syrole ON syuser_syrole.SYROLE_ID = syrole.ID WHERE syuser_syrole.SYUSER_ID = ?",
new BeanListHandler<Syrole>(Syrole.class),
u.getId());
u.getSyroles().addAll(rl);
System.out.println(u.getLoginname());
System.out.println(JSON.toJSONString(u.getSyroles()));
}
} catch (SQLException e) {
e.printStackTrace();
try {
DbUtils.rollback(conn);
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public String loadSchema() {
save();
loadSchemaResult = false;
Connection conn = null;
String url =
"jdbc:mysql://"
+ dbconfig.getHost()
+ ":"
+ dbconfig.getPort()
+ "/"
+ dbconfig.getDatabase_name();
String sql_tables =
String.format(
"select TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA=\"%s\";",
dbconfig.getDatabase_name());
String sql_columns =
String.format(
"select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY, COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA=\"%s\";",
dbconfig.getDatabase_name());
String sql_keys =
String.format(
"select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA=\"%s\";",
dbconfig.getDatabase_name());
try {
DbUtils.loadDriver(driver);
conn = DriverManager.getConnection(url, dbconfig.getUsername(), dbconfig.getPassword());
QueryRunner qr = new QueryRunner();
List<Map<String, Object>> tables = qr.query(conn, sql_tables, new MapListHandler());
List<Map<String, Object>> columns = qr.query(conn, sql_columns, new MapListHandler());
List<Map<String, Object>> keys = qr.query(conn, sql_keys, new MapListHandler());
dbconfig.setTables_json(JSONUtil.serialize(tables, false));
dbconfig.setColumns_json(JSONUtil.serialize(columns, false));
dbconfig.setKeys_json(JSONUtil.serialize(keys, false));
DbUtils.closeQuietly(conn);
} catch (Exception ex) {
return ERROR;
}
loadSchemaResult = true;
SaveLog.saveLog(
new Users((long) 1),
"11",
"数据库映射",
new Date(),
"数据库-企业数据库-模板映射—导入数据库配置方案",
Constant.DATABASE_OPERATION,
this.getClass().getName());
return SUCCESS;
}
@SuppressWarnings({"unchecked", "rawtypes"})
public List<AnaMenu> anaListeGetir() throws ReadException {
List<AnaMenu> liste = new ArrayList<AnaMenu>();
Connection con = null;
try {
con = DAOBase.instance().getConnection();
con.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
liste =
(List<AnaMenu>)
runner.query(con, Sorgular._ANAMENULIST_.qry, new BeanListHandler(AnaMenu.class));
for (AnaMenu s : liste) {
s.setAltMenuler(altListeGetir(con, s.getMenuitemId()));
}
} catch (CreateException e) {
throw new ReadException(Messages._SQL_500_.getMesaj(), null);
} catch (SQLException e) {
throw new ReadException(Messages._SQL_500_.getMesaj(), null);
} finally {
DbUtils.closeQuietly(con);
}
return liste == null ? new ArrayList<AnaMenu>() : liste;
}
@SuppressWarnings({"unchecked", "rawtypes"})
public List<MenuItemDTO> listeGetir() {
List<MenuItemDTO> liste = new ArrayList<MenuItemDTO>();
Connection con = null;
try {
con = DAOBase.instance().getConnection();
} catch (CreateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
con.setAutoCommit(false);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
QueryRunner runner = new QueryRunner();
try {
liste =
(List<MenuItemDTO>)
runner.query(con, Sorgular._MENULIST_.qry, new BeanListHandler(MenuItemDTO.class));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DbUtils.closeQuietly(con);
}
return liste == null ? new ArrayList<MenuItemDTO>() : liste;
}
@Override
public void add(UserIdMap userIdMap) {
Connection conn = null;
Object[] params = null;
String sql = null;
QueryRunner queryRunner = new QueryRunner();
try {
conn = Conn.get();
sql = "select * from user_id_map where passengerId=? and clientId=? and channelId=?";
params = new Object[3];
params[0] = userIdMap.getPassengerId();
params[1] = userIdMap.getClientId();
params[2] = userIdMap.getChannelId();
UserIdMap oldMap =
queryRunner.query(conn, sql, new BeanHandler<UserIdMap>(UserIdMap.class), params);
if (oldMap == null) {
sql = "insert into user_id_map(localId,passengerId,clientId,channelId) values(?,?,?,?)";
params = new Object[4];
params[0] = IdGenerator.seq();
params[1] = userIdMap.getPassengerId();
params[2] = userIdMap.getClientId();
params[3] = userIdMap.getChannelId();
queryRunner.update(conn, sql, params);
}
} catch (SQLException e) {
logger.info(e.getMessage(), e);
}
}
/** {@inheritDoc} */
@Override
public synchronized String getFilePathForTypeDeclaration(
String className, String repository, List<String> asteriskImports)
throws DatabaseAccessException {
if (asteriskImports.isEmpty()) {
return null;
}
ResultSetHandler<String> handler = new SingleValueStringHandler();
StringBuilder importString = new StringBuilder();
for (String currentImport : asteriskImports) {
importString
.append("'")
.append(currentImport.substring(0, currentImport.length() - 1))
.append(className)
.append("',");
}
importString.deleteCharAt(importString.length() - 1);
importString.append(")");
QueryRunner run = new QueryRunner(dataSource);
try {
return run.query(
STMT_GET_FILE_PATH_FOR_TYPE_DECLARATION_WITH_PACKAGES + importString.toString(),
handler,
repository);
} catch (SQLException ex) {
throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
}
}
/** {@inheritDoc} */
@Override
@SuppressWarnings("unchecked")
public synchronized List<Usage> getUsagesForFile(String filePath, String repository)
throws DatabaseAccessException {
ResultSetHandler<byte[]> h = new SingleValueByteArrayHandler();
try {
QueryRunner run = new QueryRunner(dataSource);
byte[] result = run.query(STMT_GET_USAGES_FOR_FILE, h, filePath, repository);
if (result != null) {
ObjectInputStream regObjectStream = new ObjectInputStream(new ByteArrayInputStream(result));
return (List<Usage>) regObjectStream.readObject();
} else {
return null;
}
} catch (IOException ex) {
throw new DatabaseAccessException(
"The content of the blob storing the usages of the file "
+ filePath
+ " repository "
+ repository
+ " could not be parsed to an Object, the database content is probably corrupt");
} catch (ClassNotFoundException ex) {
throw new DatabaseAccessException(
"The content of the blob storing the usages of the file "
+ filePath
+ " repository "
+ repository
+ " could not be parsed to an Object, the database content is probably corrupt");
} catch (SQLException ex) {
throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
}
}
private List<Project> fetchAllActiveProjects(Connection connection)
throws ProjectManagerException {
QueryRunner runner = new QueryRunner();
ProjectResultHandler handler = new ProjectResultHandler();
List<Project> projects = null;
try {
projects = runner.query(connection, ProjectResultHandler.SELECT_ALL_ACTIVE_PROJECTS, handler);
for (Project project : projects) {
List<Triple<String, Boolean, Permission>> permissions =
fetchPermissionsForProject(connection, project);
for (Triple<String, Boolean, Permission> entry : permissions) {
if (entry.getSecond()) {
project.setGroupPermission(entry.getFirst(), entry.getThird());
} else {
project.setUserPermission(entry.getFirst(), entry.getThird());
}
}
}
} catch (SQLException e) {
throw new ProjectManagerException("Error retrieving all projects", e);
} finally {
DbUtils.closeQuietly(connection);
}
return projects;
}
public static UserAccount getUserAccountbyUserID(long userid) {
UserAccount useraccount = null;
if (SpyMemCacheClient.getInstance().IsConnected())
useraccount =
(UserAccount)
SpyMemCacheClient.getInstance()
.get(String.format(UserAccount.key_UserAccountByUserID, userid));
if (useraccount == null) {
String query = String.format(UserSQL.sqlGetUserAccountByUserID, userid);
System.out.println("User Acc Query : " + query);
try {
ResultSetHandler<UserAccount> h = new BeanHandler<UserAccount>(UserAccount.class);
Connection conn = PoolFactory.getPooling().getConnection();
QueryRunner run = new QueryRunner();
useraccount = run.query(conn, query, h);
System.out.println("User Account Details : " + useraccount.toString());
conn.close();
// System.out.println(useraccount.toString());
return useraccount;
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("Mem Cache used");
}
return useraccount;
}
@Override
public Map<String, Props> fetchProjectProperties(int projectId, int version)
throws ProjectManagerException {
QueryRunner runner = createQueryRunner();
ProjectPropertiesResultsHandler handler = new ProjectPropertiesResultsHandler();
try {
List<Pair<String, Props>> properties =
runner.query(
ProjectPropertiesResultsHandler.SELECT_PROJECT_PROPERTIES,
handler,
projectId,
version);
if (properties == null || properties.isEmpty()) {
return null;
}
HashMap<String, Props> props = new HashMap<String, Props>();
for (Pair<String, Props> pair : properties) {
props.put(pair.getFirst(), pair.getSecond());
}
return props;
} catch (SQLException e) {
throw new ProjectManagerException("Error fetching properties", e);
}
}
private static List<FirmaDTO> getUpdateFirmalar() throws ReadException {
QueryRunner runner = new QueryRunner();
Connection con = null;
List<FirmaDTO> list = new ArrayList<FirmaDTO>();
try {
con = dao.getConnection();
con.setAutoCommit(false);
list =
(List<FirmaDTO>)
runner.query(
con,
"select kod, unvan, adsoyad, gsm_telefon, il, ilce, adres, durumu, tescil_no,"
+ "uygunluk_belgesi, gecerlilik_suresi, servis_sozlesme, sozlesme_tarihi,"
+ "tse_belge_no, eposta,monte_eden as monteEden,"
+ "yetkili_servis as yetkiliServis,"
+ "tse_belgesi as tseBelgesi,"
+ "telefon_no as telefonNo,"
+ "telefon_no_dahili as dahili,"
+ "ce_belge_tipi as ceBelgeTipi FROM "
+ "akm.bakimci_firma WHERE durumu='1'",
new BeanListHandler<FirmaDTO>(FirmaDTO.class));
} catch (Exception e) {
throw new ReadException(e.getMessage(), null);
} finally {
DbUtils.closeQuietly(con);
}
return list;
}
private Project fetchProjectById(Connection connection, int id) throws ProjectManagerException {
QueryRunner runner = new QueryRunner();
// Fetch the project
Project project = null;
ProjectResultHandler handler = new ProjectResultHandler();
try {
List<Project> projects =
runner.query(connection, ProjectResultHandler.SELECT_PROJECT_BY_ID, handler, id);
if (projects.isEmpty()) {
throw new ProjectManagerException("No active project with id " + id + " exists in db.");
}
project = projects.get(0);
} catch (SQLException e) {
logger.error(ProjectResultHandler.SELECT_PROJECT_BY_ID + " failed.");
throw new ProjectManagerException("Query for existing project failed. Project " + id, e);
}
// Fetch the user permissions
List<Triple<String, Boolean, Permission>> permissions =
fetchPermissionsForProject(connection, project);
for (Triple<String, Boolean, Permission> perm : permissions) {
if (perm.getThird().toFlags() != 0) {
if (perm.getSecond()) {
project.setGroupPermission(perm.getFirst(), perm.getThird());
} else {
project.setUserPermission(perm.getFirst(), perm.getThird());
}
}
}
return project;
}
/**
* 查找自增主键并填充到队列中
*
* @param objs 填充对象
* @throws IllegalArgumentException exception
* @throws IllegalAccessException exception
* @throws SQLException exception
*/
public void find(Object... objs)
throws IllegalArgumentException, IllegalAccessException, SQLException {
Class<?> clz = objs[0].getClass();
Field[] fields = clz.getDeclaredFields();
for (Field field : fields) {
ORMColumn oc = field.getAnnotation(ORMColumn.class);
if (oc != null && oc.physicalPkFld() && oc.autoIncrement()) {
// 找到最后一个自增id
QueryRunner qr = new QueryRunner();
Object identityVal = qr.query(conn, selectKey.getQuery(), new ScalarHandler<Object>());
Long identity = NumberUtils.parseNumber(identityVal.toString(), Long.class);
field.setAccessible(true);
// 填充到所有当中
for (int i = objs.length - 1; i >= 0; i--) {
if (field.get(objs[i]) == null) { // 判null，要求主键都是包装类
// 类型转换
@SuppressWarnings("unchecked")
Object val =
NumberUtils.convertNumberToTargetClass(identity, ((Class<Number>) field.getType()));
field.set(objs[i], val);
identity--;
}
}
break;
}
}
}
public <T> List<T> executeTrimedString(boolean keepConnOpen, String aQuery, Class<T> clazz)
throws Exception {
List<T> result = new ArrayList<T>();
// No DataSource so we must handle Connections manually
QueryRunner run =
new QueryRunner() {
protected ResultSet wrap(ResultSet rs) {
return StringTrimmedResultSet.wrap(rs);
}
};
try {
/*
* Sembra che il like con i parametri ufficiali non funzioni, forse
* dovuto al fatto che son tutti object
*/
logger.debug(aQuery);
result = run.query(getConnection(), aQuery, getResultSetHandler(clazz));
} finally {
if (!keepConnOpen) close();
}
return result;
}
public <T> T executeTop1(boolean keepConnOpen, String aTop1Query, Class<T> clazz)
throws Exception {
T result = null;
// No DataSource so we must handle Connections manually
QueryRunner run = new QueryRunner();
try {
/*
* Sembra che il like con i parametri ufficiali non funzioni, forse
* dovuto al fatto che son tutti object
*/
logger.debug("Esecuzione di : " + aTop1Query);
List<T> listTop1 = run.query(getConnection(), aTop1Query, getResultSetHandler(clazz));
if (listTop1.size() > 0) {
logger.debug(String.format("Record Trovati: %s", listTop1.size()));
result = listTop1.get(0);
}
} finally {
// Use this helper method so we don't have to check for null
if (!keepConnOpen) close();
}
return result;
}
public User findByCode(String code) {
String sql = "select * from user where code=?";
try {
return qr.query(sql, new BeanHandler<User>(User.class), code);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 执行统计查询语句，语句的执行结果必须只返回一个数值
*
* @param sql
* @param params
* @return
* @throws DBException
*/
public long stat(String sql, Object... params) throws SQLException {
printIn(sql);
printIn("params length:" + params.length);
for (int i = 0; i < params.length; i++) {
printIn("params" + i + ":" + params[i]);
}
Number num = (Number) queryRunner.query(getConnection(), sql, scalarHandler, params);
return (num != null) ? num.longValue() : -1;
}
/**
* 调用queryrunner的query方法执行select语句并捕获异常
*
* @param sql
* @param rsh
* @param params
* @return
*/
@SuppressWarnings("hiding")
private <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) {
try {
return queryRunner.query(sql, rsh, params);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public List<Role> getAll() {
try {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from role";
return (List<Role>) qr.query(sql, new BeanListHandler(Role.class));
} catch (Exception e) {
throw new DaoException(e);
}
}
@Override
public int countResult(String dealNo) throws Exception {
int total = 0;
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select count(*) from MONEYDATA where mon=?";
Long temp = (Long) runner.query(sql, dealNo, new ScalarHandler());
total = temp.intValue();
return total;
}
/**
* 返回执行后的结果单条
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public Object getSingle(String sql, Object... params) throws SQLException {
printIn(sql);
printIn("params length:" + params.length);
for (int i = 0; i < params.length; i++) {
printIn("params" + i + ":" + params[i]);
}
Object object = queryRunner.query(getConnection(), sql, scalarHandler, params);
return object;
}
/** 부분 커밋하면서 입력한다. ex) batchInsert(dataSource,"QQ", params, 1000); */
public static void batchInsert(
DataSource dataSource, String tableName, List<Object[]> params, int commitInterval) {
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
StringAppender appender = new StringAppender();
appender.appendLine(
"SELECT a.TABLE_NAME,a.COLUMN_NAME,COMMENTS,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE");
appender.appendLine("FROM user_tab_columns a JOIN USER_COL_COMMENTS b");
appender.appendLine("ON a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_NAME = b.TABLE_NAME");
appender.appendLine("WHERE a.TABLE_NAME = ? ");
appender.appendLine("ORDER BY a.TABLE_NAME, COLUMN_ID ");
List<String> columnNames = Lists.newArrayList();
// 걍 한번 쓸거라 일케 함
List<Map<String, Object>> result =
runner.query(conn, appender.toString(), LIST_MAP_HANDLER, tableName);
for (Map<String, Object> value : result) {
columnNames.add(value.get("COLUMN_NAME").toString());
}
String sql =
"INSERT INTO "
+ tableName
+ " ("
+ StringUtil.join(columnNames, ",")
+ ") values ("
+ StringUtil.iterateStr("?", ",", columnNames.size())
+ ")";
List<List<Object[]>> splited = CollectionUtil.splitBySize(params, commitInterval);
for (List<Object[]> inputList : splited) {
Object[][] convertedList = new Object[inputList.size()][];
for (int i = 0; i < convertedList.length; i++) {
Object[] input = inputList.get(i);
Object[] converted = new Object[input.length];
for (int j = 0; j < input.length; j++) {
converted[j] = CONVERTER.convert(input[j]);
}
convertedList[i] = converted;
}
runner.batch(conn, sql, convertedList);
conn.commit();
}
} catch (SQLException e) {
throw new SQLRuntimeException(e);
} finally {
DbUtils.closeQuietly(conn);
}
}
public boolean valid(String email, String password) throws SQLException {
String sql = "select * from user where email = ?";
User result = queryRunner.query(sql, userBeanHandler, email);
if (result != null && result.getPassword().equals(password)) {
return true;
} else {
return false;
}
}
public List getAll() {
try {
Connection conn = JdbcUtils.getConnection();
QueryRunner runner = new QueryRunner();
String sql = "select * from category";
return (List) runner.query(conn, sql, new BeanListHandler(Category.class));
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public Role find(String id) {
try {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from role where id=?";
Role role = (Role) qr.query(sql, id, new BeanHandler(Role.class));
// 找出了一个角色后，找出角色拥有的权限
if (role != null) {
sql =
"select p.* from role_privilege rp,privilege p where rp.role_id=? and p.id=rp.privilege_id";
List list = (List) qr.query(sql, role.getId(), new BeanListHandler(Privilege.class));
role.getPrivileges().addAll(list);
}
return role;
} catch (Exception e) {
throw new DaoException(e);
}
}
public Category find(String id) {
try {
Connection conn = JdbcUtils.getConnection();
QueryRunner runner = new QueryRunner();
String sql = "select * from category where id=?";
return (Category) runner.query(conn, sql, id, new BeanHandler(Category.class));
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 通过mac获取device
*
* @param device
* @return
* @throws SQLException
*/
public Device findByMacAndDevId(Device device, String devId) throws SQLException {
List<Device> query =
qr.query(
"select * from client where ap_id =(select ap_id from ap where dev_id=?) and mac=?",
new BeanListHandler(Device.class),
new Object[] {devId, device.getMac()});
if (query.size() == 0) {
return null;
}
return query.get(0);
}
public Term getTerm(String name) {
QueryRunner runner = DBUtilsHelper.getRunner();
;
ResultSetHandler<Term> resultSetHandler = new BeanHandler<Term>(Term.class);
try {
return runner.query(SQL_GET_TERM_BY_NAME, resultSetHandler, name);
} catch (SQLException e) {
logger.error(e + "");
}
return null;
}
