BaseDao

 

 

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