Home  >  Article  >  Database  >  How to implement batch deletion of data in database (code example)

How to implement batch deletion of data in database (code example)

青灯夜游
青灯夜游forward
2018-10-17 15:40:076547browse

This article will introduce to you how to delete data in batches from the database. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

package com.mescs.baseinfo.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;/**
 * 
 * 类名:DatabaseUtil  <br />
 *
 * 功能:数据库操作
 *
 */
 public class DatabaseUtil {    
    private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class);    
    private static final String DRIVER = "org.firebirdsql.jdbc.FBDriver";    
    private static final String URL = "jdbc:firebirdsql:embedded:mescs.fdb?lc_ctype=UTF8";    
    private static final String USERNAME = "root";    
    private static final String PASSWORD = "root";    
    private static final String SQL = "DELETE FROM ";// 数据库数据库表内容清空
    
    static {        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            LOGGER.error("数据库驱动加载失败===>>>", e);
        }
    }    /**
     * 获取数据库连接
     *
     * @return     */
    public static Connection getConnection() {
        Connection conn = null;        
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            LOGGER.error("数据库连接失败===>>>", e);
        }        return conn;
    }    /**
     * 关闭数据库连接
     * @param conn     */
    public static void closeConnection(Connection conn) {        
       if(conn != null) {            
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("数据库关闭失败===>>>", e);
            }
        }
    }    /**
     * 获取数据库下的所有表名     */
    public static List<String> getTableNames() {
        List<String> tableNames = new ArrayList<String>();
        Connection conn = getConnection();
        ResultSet rs = null;        
        try {       //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();  //从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[] { "TABLE" });            
            while(rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            LOGGER.error("获取数据库表名失败===>>>", e);
        } finally {            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                LOGGER.error("关闭数据库失败===>>>", e);
            }
        }        return tableNames;
    }    /*
     * 清空数据库表     */
    public static void emptyDatabase() throws SQLException {
        List<String> tableNames = getTableNames();
        Connection conn = getConnection();
        PreparedStatement pStemt = null;        
        for(String table:tableNames){            
             if(!table.equals("SYSPARAM") && !table.equals("T_CLEAR_DATA")){
                String tableSql = SQL + table;                
                try {
                    pStemt = conn.prepareStatement(tableSql);
                    Statement stmt=conn.createStatement();
                    stmt.executeUpdate(tableSql);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        conn.close();
    }
}

Summary: The Firebird database used in the database here can of course be used in mainstream databases such as Mysql and Oracle. The method is just a simple list of deletions, additions, deletions, modifications and searches, and the same method.

Related recommendations:

mysql database graphic tutorial

MySQL video tutorial

bootstrap video tutorial

The above is the detailed content of How to implement batch deletion of data in database (code example). For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:cnblogs.com. If there is any infringement, please contact admin@php.cn delete