public long addThesisUnique(ThesisUnique thesisUnique) throws StorageException {
if (getThesisUniqueByContent(thesisUnique.getContent()) == null) {
try {
simpleJdbcTemplate
.getJdbcOperations()
.update(
"INSERT INTO thesis_unique (content, frequency, last_scan, positivity, importance) VALUES(?,?,?,?,?)",
new Object[] {
thesisUnique.getContent(),
thesisUnique.getFrequency(),
thesisUnique.getLastScan(),
thesisUnique.getPositivity(),
thesisUnique.getImportance()
},
new int[] {Types.VARCHAR, Types.INTEGER, Types.DATE, Types.DOUBLE, Types.DOUBLE});
long lastId =
simpleJdbcTemplate.getJdbcOperations().queryForLong("SELECT LAST_INSERT_ID()");
return lastId;
} catch (DataAccessException e) {
log.error(
"Error while inserting thesis_unique (probably not enough permissions): "
+ thesisUnique,
e);
throw new StorageException();
}
} else {
updateThesisUniqueByContent(thesisUnique.getContent(), thesisUnique.getFrequency());
// TODO must we fix this?
return -1;
}
}
/**
* Checks if the specified table exists
*
* @param template
* @param tableName
* @return
*/
private boolean tableExists(
SimpleJdbcTemplate template, final String schema, final String tableName) {
try {
DataSource ds = ((JdbcAccessor) template.getJdbcOperations()).getDataSource();
return (Boolean)
JdbcUtils.extractDatabaseMetaData(
ds,
new DatabaseMetaDataCallback() {
public Object processMetaData(DatabaseMetaData dbmd)
throws SQLException, MetaDataAccessException {
ResultSet rs = null;
try {
rs = dbmd.getTables(null, schema, tableName.toLowerCase(), null);
boolean exists = rs.next();
rs.close();
if (exists) {
return true;
}
rs = dbmd.getTables(null, schema, tableName, null);
return rs.next();
} finally {
if (rs != null) {
rs.close();
}
}
}
});
} catch (MetaDataAccessException e) {
return false;
}
}
public List<ThesisUnique> getAllThesesUnique() {
List<ThesisUnique> products =
simpleJdbcTemplate
.getJdbcOperations()
.query("SELECT * FROM thesis_unique", thesisUniqueMapper);
return products;
}
private boolean isModuleAlreadyInstalled(SimpleJdbcTemplate template) {
if (!appliedIds.isEmpty()) {
// Some changes have already been applied
return true;
}
return checkTableName == null
|| DatabaseUtils.tableExists(template.getJdbcOperations(), new Table(checkTableName));
}
@Before
public void onSetUp() throws Exception {
getMessages(); // drain queue
simpleJdbcTemplate.getJdbcOperations().execute("delete from T_BARS");
jmsTemplate.convertAndSend("queue", "foo");
provider =
new ItemReader<String>() {
public String read() {
String text = (String) jmsTemplate.receiveAndConvert("queue");
list.add(text);
return text;
}
};
retryTemplate = new RetryTemplate();
}
/* (non-Javadoc)
* @see com.qm.frame.dbtool.dialect.IDialect#getTables(FbrpDbtoolDs ds)
*/
@Override
public Map<String, FbrpDbtoolTable> getTables(final FbrpDbtoolDs ds) {
// JDBC
SimpleJdbcTemplate simpleJdbcTemplate = this.getDsService().getSimpleJdbcTemplate(ds.getId());
if (simpleJdbcTemplate == null) {
return null;
}
// 表信息列表
Map<String, FbrpDbtoolTable> tableInfo =
simpleJdbcTemplate
.getJdbcOperations()
.execute(
new ConnectionCallback<Map<String, FbrpDbtoolTable>>() {
public Map<String, FbrpDbtoolTable> doInConnection(Connection conn)
throws SQLException, DataAccessException {
Map<String, FbrpDbtoolTable> map = new HashMap<String, FbrpDbtoolTable>();
String[] types = {"TABLE"};
ResultSet rs = null;
try {
DatabaseMetaData dmd = conn.getMetaData();
String url = (String) ds.getUrl();
url = url.substring(url.indexOf("=") + 1, url.indexOf(","));
rs = dmd.getTables("", url, "%", types);
while (rs.next()) {
FbrpDbtoolTable dbtable = new FbrpDbtoolTable();
// 表名
dbtable.setName(rs.getObject("TABLE_NAME").toString());
Object remark = rs.getObject("REMARKS");
dbtable.setRemark(remark == null ? "" : rs.getObject("REMARKS").toString());
if (!dbtable.getName().startsWith("BIN$")) {
map.put(dbtable.getName(), dbtable);
}
}
} catch (Exception exe) {
log.error("", exe);
} finally {
JdbcUtils.closeResultSet(rs);
}
return map;
}
});
return tableInfo;
}
/**
* Checks if the database schema is present, if missing it generates it
*
* @param template
*/
public void initializeTables(String schema, SimpleJdbcTemplate template) {
String prefix;
if (schema == null) {
prefix = "";
} else {
prefix = schema + ".";
}
for (String table : TABLE_CREATION_MAP.keySet()) {
if (!tableExists(template, schema, table)) {
for (String command : TABLE_CREATION_MAP.get(table)) {
command = command.replace("${schema}", prefix);
template.getJdbcOperations().execute(command);
}
}
}
}
public ThesisUnique getThesisUniqueByContent(String content) {
List<ThesisUnique> theses =
simpleJdbcTemplate
.getJdbcOperations()
.query(
"SELECT * FROM thesis_unique WHERE content = ?",
new Object[] {content},
new int[] {Types.VARCHAR},
thesisUniqueMapper);
// TODO is it good practice?
if (theses.size() > 0) {
return theses.get(0);
} else {
return null;
}
}
private Long getLastInsertId() {
final List<Long> id = new ArrayList<Long>(1);
jdbcTemplate
.getJdbcOperations()
.query(
"select last_insert_id()",
new RowCallbackHandler() {
public void processRow(final ResultSet rs) throws SQLException {
id.add(rs.getLong(1));
}
});
if (id.isEmpty()) {
throw new IllegalStateException("No inserted id");
}
return id.get(0);
}
public void updateThesisUniqueByContent(String content, int frequency) throws StorageException {
ThesisUnique thesisUnique = getThesisUniqueByContent(content);
frequency += thesisUnique.getFrequency();
log.info("thesis = " + thesisUnique.getContent());
try {
simpleJdbcTemplate
.getJdbcOperations()
.update(
"UPDATE thesis_unique SET frequency = ? WHERE content = ?",
new Object[] {frequency, content});
} catch (DataAccessException e) {
log.error(
"Error while updating thesis_unique (probably not enough permissions) with content : "
+ content,
e);
throw new StorageException();
}
}
/*
* (non-Javadoc)
*
* @see
* com.qm.frame.dbtool.dialect.impl.AbstractDialect#getTableColInfo
* (com.qm.frame.dbtool.vo.FbrpDbtoolDsVO, java.lang.String)
*/
@Override
public Map<String, FbrpDbtoolTableCol> getTableColInfo(
final FbrpDbtoolDs ds, final String tableName) {
if (tableName == null) {
return null;
}
// jdbc
SimpleJdbcTemplate simpleJdbcTemplate = this.getDsService().getSimpleJdbcTemplate(ds.getId());
if (simpleJdbcTemplate == null) {
return null;
}
// 查询表的字段信息
Map<String, FbrpDbtoolTableCol> tableColInfo =
simpleJdbcTemplate
.getJdbcOperations()
.execute(
new ConnectionCallback<Map<String, FbrpDbtoolTableCol>>() {
public Map<String, FbrpDbtoolTableCol> doInConnection(Connection conn)
throws SQLException, DataAccessException {
Map<String, FbrpDbtoolTableCol> dbtableColInfoVOs =
new HashMap<String, FbrpDbtoolTableCol>();
ResultSet rs = null;
try {
DatabaseMetaData dmd = conn.getMetaData();
String url = (String) ds.getUrl();
url = url.substring(url.indexOf("=") + 1, url.indexOf(","));
// 主键字段
rs = dmd.getPrimaryKeys("", url, tableName);
List<String> keyColumns = new ArrayList<String>();
while (rs.next()) {
keyColumns.add(rs.getObject("COLUMN_NAME").toString());
}
// 所有字段
rs = dmd.getColumns(conn.getCatalog(), url, tableName, "%");
String columnName = null;
// 排序号
int sortNo = 0;
while (rs.next()) {
FbrpDbtoolTableCol dntableColinfo = new FbrpDbtoolTableCol();
// 字段名
columnName = rs.getObject("COLUMN_NAME").toString();
dntableColinfo.setName(columnName);
// 是否是主键
if (keyColumns.contains(columnName)) {
dntableColinfo.setPrimaryKeyFlag(
Long.valueOf(BaseConstants.YESORNO_TYPE_YES));
} else {
dntableColinfo.setPrimaryKeyFlag(
Long.valueOf(BaseConstants.YESORNO_TYPE_NO));
}
// 允许为空
dntableColinfo.setNullable(
Long.valueOf(rs.getObject("NULLABLE").toString()));
// 长度
String length = String.valueOf(rs.getObject("COLUMN_SIZE").toString());
if (rs.getObject("DECIMAL_DIGITS") != null
&& ((Integer) rs.getObject("DECIMAL_DIGITS")) != 0) {
length += "," + rs.getObject("DECIMAL_DIGITS").toString();
}
dntableColinfo.setLength(length);
// 备注
dntableColinfo.setRemark((String) rs.getObject("REMARKS"));
// 类型
dntableColinfo.setColType(rs.getObject("TYPE_NAME").toString());
// 默认值
dntableColinfo.setDefaultValue((String) rs.getObject("COLUMN_DEF"));
// 排序号
dntableColinfo.setSortNo(Long.valueOf(sortNo++));
dbtableColInfoVOs.put(dntableColinfo.getName(), dntableColinfo);
}
} catch (Exception exe) {
// TODO
// com.teradata.jdbc.jdbc_4.util.JDBCException:
// throw new ServiceException("没有对DBC.UDTInfo的SELECT权限");
// [Teradata Database][TeraJDBC 13.10.00.10][Error 3523][SQLState 42000]
// The user does not have SELECT access to
// DBC.UDTInfo.
log.error("", exe);
} finally {
JdbcUtils.closeResultSet(rs);
}
return dbtableColInfoVOs;
}
});
return tableColInfo;
}