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());
}