查询数据库是否有某表的存在,主要用的就是Connection对象对元数据的操作,代码很简单,贴出来大家参考
- /**
- * 查询数据库是否有某表
- * @param cnn
- * @param tableName
- * @return
- * @throws Exception
- */
- @SuppressWarnings("unchecked")
- public boolean getAllTableName(String tableName) throws Exception {
- Connection conn = jdbcTemplate.getDataSource().getConnection();
- ResultSet tabs = null;
- try {
- DatabaseMetaData dbMetaData = conn.getMetaData();
- String[] types = { "TABLE" };
- tabs = dbMetaData.getTables(null, null, tableName, types);
- if (tabs.next()) {
- return true;
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally{
- tabs.close();
- conn.close();
- }
- return false;
- }
然后判断是有某表,如果没有,调用创建
- /**
- * 根据表名称创建一张表
- * @param tableName
- */
- public int createTable(String tableName){
- StringBuffer sb = new StringBuffer("");
- sb.append("CREATE TABLE `" + tableName + "` (");
- sb.append("`id` int(11) NOT NULL AUTO_INCREMENT ,");
- sb.append("`alertId` int(11) NULL DEFAULT NULL ,");
- sb.append("`alertTime` int(11) NULL DEFAULT NULL ,");
- sb.append("`alertLevel` int(11) NULL DEFAULT NULL ,");
- sb.append("`deviceMark` int(11) NULL DEFAULT NULL ,");
- sb.append("`carNo` int(11) NULL DEFAULT NULL ,");
- sb.append("`updateTime` varchar(255) DEFAULT NULL ,");
- sb.append("PRIMARY KEY (`id`)");
- sb.append(") CHARACTER SET=utf8 COLLATE=utf8_general_ci;");
- try {
- jdbcTemplate.update(sb.toString());
- return 1;
- } catch (Exception e) {
- e.printStackTrace();
- }
- return 0;
- }
这两个方法可以公用,至于是按月还是按天还是按周,取决于你对表名称的生成
- /**
- * 保存
- */
- @Override
- public int saveAlertMessLog(AlertMessLog alertMessLog) {
- SimpleDateFormat format = new SimpleDateFormat("yyyy_MM");
- String tableName = "nm_alertmesslog_" + format.format(new Date());
- try {
- boolean isHave = getAllTableName(tableName);
- if(isHave){
- return saveObject(alertMessLog,tableName);
- }else{
- if(createTable(tableName) == 1){
- return saveObject(alertMessLog,tableName);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- return 0;
- }
我的格式化方法决定了是按照月来进行生成,如果有直接保存,如果没有,先生成再保存!
<script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>