1.将删除id在前台拼成字符串传到后台；
2.后台进行分割循环删除；

if(delIdStatus!=null && !delIdStatus.equals("")){
                    String[] id = delIdStatus.substring(0,delIdStatus.length()-1).split(",");
                    if(id.length>0){
                        for (int i = 0; i < id.length; i++) {
                            if(!id[i].equals(" ") && !id[i].equals("")){
                                List<DaPing> dapList = microcosmicService.findDelById(Long.parseLong(id[i]));
                                attachmentService.delete(dapList.get(0).getFileId());
                                this.microcosmicService.deleteById(id[i]);
                            }
                        }
                    }
                }

   
   引言


   在项目需求中需要用到动态批量的删除数据库中的表，我们都知道在数据库中的数据是需要历史记录的所以我


们这所谓的删除其实只是给它重命名然后打上时间戳，这样就可以标志着这个数据表在数据库中已经没有用了，但是


其中的数据还是有用的，下面和大家分享这几天在项目中学习到的东西！！


   底层封装好的SQLhelper类，代码如下：



<span style="font-size:18px;">using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Transactions;

namespace ITOO.Library.Core.Common
{
    /// <summary>
    /// 数据库助手类
    /// </summary>
    public class SQLHelper
    {
        private SqlCommand cmd = null;
        private SqlConnection conn = null;
        private SqlDataReader sdr = null;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strDatabaseName"></param>
        /// <param name="IsGetNewConnection"></param>
        public SQLHelper(string strDatabaseName, bool IsGetNewConnection = false)
        {
            if (IsGetNewConnection == false)
            {
                conn = ConnectionFactory.GetCurrentConn(strDatabaseName);
            }
            else
            {
                conn = ConnectionFactory.GetNewConn(strDatabaseName);
            }
        }

        /// <summary>
        /// 无参构造函数
        /// </summary>
        public SQLHelper()
        {
        }

        private SqlConnection GetConnection(string strDatabaseName)
        {
            string strConnValue = ConfigHelper.ReadAppSetting(strDatabaseName);
            return new SqlConnection(strConnValue);
        }

        /// <summary>
        /// 获得数据库连接
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <param name="conn">数据库连接</param>
        public static void Close(SqlConnection conn)
        {
            if (conn != null)
            {
                try
                {
                    conn.Close();
                }
                catch (SqlException e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行带参数的增删改命令
        /// </summary>
        /// <param name="cmmText"></param>
        /// <param name="para"></param>
        /// <param name="cmmType"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmmText, SqlParameter[] para, CommandType cmmType)
        {
            cmd = new SqlCommand(cmmText, GetConnection());
            cmd.CommandType = cmmType;

            try
            {
                cmd.Parameters.AddRange(para);
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 执行不带参数的增删改命令
        /// </summary>
        /// <param name="cmmText"></param>
        /// <param name="cmmType"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmmText, CommandType cmmType)
        {
            cmd = new SqlCommand(cmmText, GetConnection());
            cmd.CommandType = cmmType;

            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 执行带参数的查询命令
        /// </summary>
        /// <param name="cmmText"></param>
        /// <param name="para"></param>
        /// <param name="cmmType"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmmText, SqlParameter[] para, CommandType cmmType)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmmText, GetConnection());
            cmd.CommandType = cmmType;
            cmd.Parameters.AddRange(para);

            try
            {
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                    return dt;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 执行不带参数的查询命令
        /// </summary>
        /// <param name="cmmText"></param>
        /// <param name="cmmType"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmmText, CommandType cmmType)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmmText, GetConnection());
            cmd.CommandType = cmmType;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        /// <summary>
        /// 执行创建表的方法
        /// 先读取文件，以GO为段落，把每一个GO的段落当做一条SQL命令，在通过事务一起执行。
        /// </summary>
        /// <param name="varSqlList">传入执行的sql脚本</param>
        public void ExecuteCommand(ArrayList varSqlList)
        {
            SqlTransaction varTrans = GetConnection().BeginTransaction();
            cmd = new SqlCommand();
            cmd.Connection = GetConnection();
            cmd.Transaction = varTrans;
            try
            {
                foreach (string varcommandText in varSqlList)
                {
                    cmd.CommandText = varcommandText;
                    cmd.ExecuteNonQuery();
                }
                varTrans.Commit();
            }
            catch (Exception ex)
            {
                varTrans.Rollback();
                throw ex;
            }
        }

        #region 批量导入DataTable

        /// <summary>
        /// 批量导入DataTable
        /// </summary>
        /// <param name="strDatabaseName">配置文件key</param>
        /// <param name="dt">datatable名称</param>
        /// <param name="tableName">表名称</param>
        /// <param name="dtColum">所有列（dt.columns)</param>
        /// <returns>返回true，or false</returns>
        public Boolean InsertTable(string strDatabaseName, DataTable dt, string tableName, DataColumnCollection dtColum)
        {
            using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required))
            {
                using (SqlBulkCopy sqlBC = new SqlBulkCopy(
                           GetConnection(strDatabaseName).ConnectionString, SqlBulkCopyOptions.KeepIdentity))
                {
                    sqlBC.BatchSize = 1000;
                    sqlBC.DestinationTableName = tableName;

                    // Write from the source to the destination.
                    // This should fail with a duplicate key error.
                    for (int i = 0; i < dtColum.Count; i++)
                    {
                        sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString());
                    }
                    try
                    {
                        //批量写入
                        sqlBC.WriteToServer(dt);
                        scope1.Complete();
                        return true;
                    }
                    catch
                    {
                        throw new Exception("导入数据失败！");
                    }
                }
            }
        }

        #endregion 批量导入DataTable
    }</span>




     删除 数据表的方法



<span style="font-size:18px;">  /// <summary>
        /// 批量假删除数据表（重命名，打上时间戳）
        /// </summary>
        /// <param name="db">指定的数据库</param>
        /// <param name="listDt">要删除的数据库表集合</param>
        /// <param name="connKey">数据库连接串</param>
        /// <returns>执行是否成功，true表示成功，false表示失败</returns>
        public bool FalseDropTable(string db, List<string> listDt, string connKey)
        {
            SQLHelper helper = null;
            try
            {
                 helper = new SQLHelper("ITOOExamEntities");
            }
            catch (Exception e)
            {

                throw e;
            }
           

            string connToMaster = ConfigurationManager.ConnectionStrings[connKey].ToString();

            //判断数据库是否存在
            if (IsDBExist(db, connKey) == false)
            {
                throw new Exception("数据库不存在！");
            }

            string timeStamp = DateTime.Now.ToString();            // 2008-9-4 20:02:10

            for (int i = 0; i < listDt.Count(); i++)
            {
                //如果数据库表存在，则抛出错误
                if (IsTableExist(db, listDt[i], connKey) == false)
                {
                    //如果数据库表已经删除，则跳过该表
                    continue;
                }
                else//数据表存在，则进行删除数据表
                {
                    //其后判断数据表是否存在，然后创建数据表
                    //string createTableStr = "use " + db + " drop table " + listDt[i] + " ";
                    string createTableStr = "use " + db + " exec sp_rename '" + listDt[i] + "','" + listDt[i] + "_" + timeStamp + "'";
                    helper.ExecuteNonQuery(createTableStr, CommandType.Text);
                }
            }
            return true;
        }</span>


    判断指定的数据库是否存在



 
<span style="font-size:18px;">/// <summary>
        /// 判断数据库是否存在
        /// </summary>
        /// <param name="db">数据库的名称</param>
        /// <param name="connKey">数据库的连接Key</param>
        /// <returns>true:表示数据库已经存在；false，表示数据库不存在</returns>
        public Boolean IsDBExist(string db, string connKey)
        {
            SQLHelper helper = new SQLHelper("ITOOExamEntities");

            string connToMaster = ConfigurationManager.ConnectionStrings[connKey].ToString();
            string createDbStr = " select * from master.dbo.sysdatabases where name " + "= '" + db + "'";

            DataTable dt = helper.ExecuteQuery(createDbStr, CommandType.Text);
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }</span>



  判断数据库中的指定的表是否存在



<span style="font-size:18px;">/// <summary>
        /// 判断数据库表是否存在
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="tb">数据表名</param>
        /// <param name="connKey">连接数据库的key</param>
        /// <returns>true:表示数据表已经存在；false，表示数据表不存在</returns>
        public Boolean IsTableExist(string db, string tb, string connKey)
        {
            SQLHelper helper = new SQLHelper("ITOOExamEntities");

            string connToMaster = ConfigurationManager.ConnectionStrings[connKey].ToString();
            string createDbStr = "use " + db + " select 1 from  sysobjects where  id = object_id('" + tb + "') and type = 'U'";

            //在指定的数据库中  查找 该表是否存在
            DataTable dt = helper.ExecuteQuery(createDbStr, CommandType.Text);
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            else
            {
                return true;
            }

        }</span>


    以上是小编在刚接手的项目中学习到的一些对于自己来说比较新的东西，在这分享给大家，希望能给大家带来帮


助！！

mybatis动态Sql实现批量添加和批量删除：

新建一个domainMapper接口：

接下来写：domainMapper.xml配置，在本利中即：UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace:命名空间 -> 要求:一个项目中命名空间必须唯一;
一般:domain的路径来唯一
-->
<mapper namespace="cn.itsource.mybatis.batch_options.UserMapper">
    <!--
      select:标签名字:  CRUD的时候对应的名字
      id:这个语句的唯一标识;   【必须和接口的方法名字一样】;
      parameterType:传入参数类型
      resultType:返回值类型
      #{id}:参数的接收

      collection=""  遍历集合
      index=""  遍历时候的索引
      item=""   每此遍历得到的对象
      open=""   以什么开始
      close=""  以什么结束
      separator="" 以什么作为分割符
    -->
    <select id="saveBatch" parameterType="list">

        INSERT INTO user (username,password) VALUES

        <foreach collection="list" item="user" separator=",">
            (#{user.username},#{user.password})
        </foreach>

    </select>


    <delete id="deleteBatch" parameterType="list">

        delete from user where id in

        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>

    </delete>
</mapper>

注意在mybatis的主配置文件中，引入该UserMapper.xml文件：

自定义工具类获得SqlSession对象：


测试：

批量添加
xxMapper.xml里面的代码
<!--
    批量添加
     insert into employee (name,age,sex) values
             ("小小良",45,false),("大二良",45,true)
    foreach:循环
        collection:遍历的集合
        item:每次遍历拿到的对象
        separator:分隔符(每个值都使用,隔开)
-->
<insert id="batchSave" parameterType="list">
    insert into employee (name,age,sex) values
    <foreach collection="list" item="e" separator=",">
        (#{e.name},#{e.age},#{e.sex})
    </foreach>
</insert>

测试方法
   @Test
    public void testBatchSave() throws Exception{
        //准备数据
        List<Employee> list = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            Employee employee = new Employee();
            employee.setName("小王"+i);
            employee.setAge(14+i);
            employee.setSex(true);
            list.add(employee);
        }

        SqlSession session = MyBatisUtil.openSession();
        session.insert("cn.itsource.dao.EmployeeMapper.batchSave",list);
        session.commit();
    }

批量删除
xxMapper.xml里的代码
<!--
    批量删除:DELETE FROM employee WHERE id in (?,?,?,...)
    传 : List<Long> ids
     foreach:循环
        collection:遍历的集合
        item:每次遍历拿到的对象
        separator:分隔符(每个值都使用,隔开)
        open:以什么开头
        close:以什么结尾
        index:遍历的索引
-->
<delete id="batchDelete" parameterType="list">
  delete from employee where  id in
  <foreach collection="list" item="id" separator="," open="(" close=")">
      #{id}
  </foreach>
</delete>

测试代码
 @Test
    public void testBatchDelete() throws Exception{
        //准备数据
        List<Long> ids = Arrays.asList(12L,14L);

        SqlSession session = MyBatisUtil.openSession();
        session.delete("cn.itsource.dao.EmployeeMapper.batchDelete",ids);
        session.commit();
    }

动态修改
xxxMapper.xml代码
<!--
        动态修改:只修改有值的数据
-->
<update id="dynamicUpdate" parameterType="employee">
    update employee
    <set>
        <if test="name!=null">
            name=#{name},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
        <if test="sex!=null">
            sex=#{sex},
        </if>
    </set>
    where id=#{id}
</update>

测试代码
  @Test
    public void testDynamicUpdate() throws Exception{
        //准备数据
        Employee employee = new Employee();
        employee.setId(3L);
        employee.setName("良阿");
//        employee.setSex(true);
        employee.setAge(22);

        SqlSession session = MyBatisUtil.openSession();
        session.update("cn.itsource.dao.EmployeeMapper.dynamicUpdate",employee);
        session.commit();
    }