public class BaseDao { private static Log logger = LogFactory.getLog(BaseDao.class); // 查询数据 public void selectSql(String sql, Object[] obj) { try { PreparedStatement stmt = null; Connection conn = null; conn = ConnectionTools.getConn(); conn.setAutoCommit(false); stmt = conn.prepareStatement(sql); if (obj != null) { for (int i = 0; i < obj.length; i++) { int key = i + 1; stmt.setObject(key, obj[i]); } } stmt.executeUpdate(); conn.commit(); stmt.close(); } catch (Exception e) { logger.error("查询出错:", e); } } public void executeUpdate(String sql) { executeUpdate(sql, null); } public void executeUpdate(String sql, Object obj[]) { try { PreparedStatement stmt = null; Connection conn = null; conn = ConnectionTools.getConn(); conn.setAutoCommit(false); stmt = conn.prepareStatement(sql); if (obj != null) { for (int i = 0; i < obj.length; i++) { int temp = i + 1; stmt.setObject(temp, obj[i]); } } stmt.executeUpdate(); conn.commit(); stmt.close(); } catch (Exception e) { logger.error("保存出错:", e); } } public int addDate(String sql, Object[] obj) { int key = 0; try { PreparedStatement stmt = null; Connection conn = null; conn = ConnectionTools.getConn(); conn.setAutoCommit(false); stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (obj != null) { for (int i = 0; i < obj.length; i++) { int temp = i + 1; stmt.setObject(temp, obj[i]); } } stmt.executeUpdate(); ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()) { key = rs.getInt(1); } conn.commit(); stmt.close(); } catch (Exception e) { logger.info("保存出错:", e); } return key; } // 删除表 public void deleteTable(String tableName) { StringBuffer dropTableSql = new StringBuffer(); dropTableSql.append("DROP TABLE IF EXISTS `" + tableName + "`"); executeUpdate(dropTableSql.toString()); } // 创建一个表 public void makeTableSql(String tableName, Map<String, String> cloumNames) { StringBuffer createTableSQL = new StringBuffer(); createTableSQL.append("CREATE TABLE " + tableName + " ("); createTableSQL.append(" id int(11) NOT NULL AUTO_INCREMENT,"); Set<String> cs = cloumNames.keySet(); for (String e : cs) { String t = cloumNames.get(e); e = e.replaceAll("(?i)[^a-zA-Z0-9\u4E00-\u9FA5]", ""); if (e.trim().isEmpty()) { continue; } if (e.contains("detailurl")) { createTableSQL.append(e + " varchar(255) DEFAULT NULL,"); // } else if (t.length() > 10 && t.length() < 255) { // // createTableSQL.append(e + " varchar(255) DEFAULT NULL,"); // } else { createTableSQL.append(e + " text DEFAULT NULL,"); } } createTableSQL.append("TIME datetime NOT NULL , "); createTableSQL.append(" PRIMARY KEY (id) , UNIQUE KEY (detailurl)"); createTableSQL.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;"); executeUpdate(createTableSQL.toString()); } // 插入数据 public void insertSql(String tableName, Map<String, String> cloumNames) { StringBuffer insertSQL = new StringBuffer(); insertSQL.append("insert into " + tableName + " ("); Set<String> cs = cloumNames.keySet(); StringBuffer keys = new StringBuffer(); StringBuffer values = new StringBuffer(); for (String e : cs) { String value = cloumNames.get(e); e = e.replaceAll("(?i)[^a-zA-Z0-9\u4E00-\u9FA5]", ""); keys.append(e + ","); values.append("‘" + value + "‘,"); } insertSQL.append(keys.toString()); insertSQL.append(" TIME) values ("); insertSQL.append(values.toString()); insertSQL.append(" NOW())"); executeUpdate(insertSQL.toString()); }
原文:http://www.cnblogs.com/tomcattd/p/3793206.html