数据库连接

      发布在:未分类      评论:0 条评论

1.查询表是否存在

JdbcTemplate jdbcTemplate = DataUtil.getJdbcTemplate(DATASOURCE_MERIT);

Connection conn = null;

try {

//查询表是否存在

conn = jdbcTemplate.getDataSource().getConnection();

DatabaseMetaData dbMetaData = conn.getMetaData();

ResultSet tabs = dbMetaData.getTables(null, null, infoTable, new String[]{"TABLE"});

//表存在

if (tabs.next()) {

String sql = "MERIT_CODE=" + meritCode;

List<MeritFieldInfo> fieldList = MeritFieldInfoDao.listBySql(sql);

success = insertToDynamicTable(meritCode, jdbcTemplate, fieldList, parserSql);

}

tabs.close();

} catch (SQLException e) {

throw new MeritMapSqlException(e.getMessage());

} finally {

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

throw new MeritMapSqlException("连接未关闭" + e);

}

}

}

2.查询字段是否存在

conn = jdbcTemplate.getDataSource().getConnection();

DatabaseMetaData dbMetaData = conn.getMetaData();

ResultSet tabs = dbMetaData.getTables(null, null, infoTable, new String[]{"TABLE"});

//表存在

if (tabs.next()) {

ResultSet rs = dbMetaData.getColumns(null, "%", infoTable, "%");

while (rs.next()) {

//类名

String colName = rs.getString("COLUMN_NAME");

colNameSet.add(colName);

}

Set<String> addSet = new HashSet<String>();

addSet.addAll(codeSet);

addSet.removeAll(colNameSet);

if (addSet.size() > 0 && colNameSet.size()>0) {

StringBuffer updateSql = new StringBuffer();

updateSql.append("ALTER TABLE " + infoTable);

for (String add : addSet) {

updateSql.append(" ADD COLUMN ")

.append(add)

.append(" varchar(64),");

}

updateSql.deleteCharAt(updateSql.length() - 1);

updateSql.append(";");

try {

jdbcTemplate.execute(updateSql.toString());

} catch (DataAccessException e) {

throw new MeritInfoException("新增" + infoTable + "字段错误:" + e);

}

}

}

Responses