Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn= DriverManager.getConnection("jdbc:odbc:DDEmployer");
Statement stat=conn.createStatement();
String id=javax.swing.JOptionPane.showInputDialog(null,"请输入要删除的员工编号");
String sql="DELETE FROM T_EMPLOYER WHERE ID=id";
int i=stat.executeUpdate(sql);
System.out.println("成功删除"+i+"行");
如果执行这条语句，是删除了，但是删除的是全部啊？？怎么接？？
如果将SQL语句改为下面这个
String sql="DELETE FROM T_EMPLOYER WHERE T_EMPLOYER.ID=10";
则正常删除
String sql="DELETE FROM T_EMPLOYER WHERE ID=id";
这个只会删除ID=id的，不会全部删除，除非你的所有ID字段都相同，并且等于id
ID NAME SEX NUMBERANDPHONE REGISTERDATE ADDRESS
6 张三 男 432424 2016/3/29 星期二 甘肃兰州
7 女 432432 2016/3/29 星期二 北京石景山
8 女 432424 2016/3/24 星期四 上海昆山
9 男 43234324 2016/3/3 星期四 广州深圳
10 男 100000000 2016/3/26 星期六 广州深圳
上面是我的数据库内容
你把sql语句改成这样试试 DELETE FROM T_EMPLOYER WHERE ID=@id
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
namespace Common
{
public static class AccessHelper
{
#region 基本功能
private static string connectionString;
public static string ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString = value;
}
}
private static OleDbConnection GetOleDbConnection()
{
return new OleDbConnection(ConnectionString);
}
public static DataSet ExecuteDataSet(string cmdText, params OleDbParameter[] p)
{
OleDbDataAdapter da = new OleDbDataAdapter(cmdText, ConnectionString);
if (p != null)
{
da.SelectCommand.Parameters.AddRange(p);
}
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public static DataTable ExecuteDataTable(string cmdText, params OleDbParameter[] p)
{
OleDbDataAdapter da = new OleDbDataAdapter(cmdText, ConnectionString);
if (p != null)
{
da.SelectCommand.Parameters.AddRange(p);
}
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public static DataRow ExecuteDataRow(string cmdText, params OleDbParameter[] p)
{
DataTable dt = ExecuteDataTable(cmdText, p);
if (dt != null && dt.Rows.Count > 0)
return dt.Rows[0];
return null;
}
public static int ExecuteNonQuery(string cmdText, params OleDbParameter[] p)
{
OleDbConnection connection = GetOleDbConnection();
OleDbCommand command = new OleDbCommand(cmdText, connection);
connection.Open();
if (p != null)
{
command.Parameters.AddRange(p);
}
int i = command.ExecuteNonQuery();
connection.Close();
return i;
}
public static object ExecuteScalar(string cmdText, params OleDbParameter[] p)
{
OleDbConnection connection = GetOleDbConnection();
OleDbCommand cmd = new OleDbCommand(cmdText, connection);
connection.Open();
if (p != null)
{
cmd.Parameters.AddRange(p);
}
object obj = cmd.ExecuteScalar();
connection.Close();
return obj;
}
#endregion
#region 扩展功能
/// <summary>
/// 根据唯一ID获取Hashtable
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <param name="pkVal">字段值</param>
/// <returns></returns>
public static Hashtable GetHashtableByGUID(string tableName, string pkName, string pkVal)
{
string cmdText = "SELECT * FROM " + tableName + " WHERE " + pkName + " = @GUID值";
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@GUID值",pkVal)
};
DataTable dt = ExecuteDataTable(cmdText, parameters);
return DataTableHelper.DataTableToHashtable(dt);
}
public static Hashtable GetHashtableById(string tableName, string pkName, string pkVal)
{
string cmdText = "SELECT * FROM " + tableName + " WHERE " + pkName + " = @ID";
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("@ID",pkVal)
};
DataTable dt = ExecuteDataTable(cmdText, parameters);
return DataTableHelper.DataTableToHashtable(dt);
}
/// <summary>
/// 对象参数转换
/// </summary>
/// <param name="ht"></param>
/// <returns></returns>
private static OleDbParameter[] GetParameter(Hashtable ht)
{
OleDbParameter[] parameters = new OleDbParameter[ht.Count];
int i = 0;
foreach (string key in ht.Keys)
{
parameters[i] = new OleDbParameter("@" + key, ht[key]);
i++;
}
return parameters;
}
/// <summary>
/// 通过Hashtable插入数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">Hashtable</param>
/// <returns>int</returns>
public static int InsertByHashtable(string tableName, Hashtable ht)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(tableName);
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (string key in ht.Keys)
{
sb_prame.Append("," + key);
sp.Append(",@" + key);
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
return ExecuteNonQuery(sb.ToString(), GetParameter(ht));
}
/// <summary>
/// 通过Hashtable修改数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <param name="pkValue"></param>
/// <param name="ht">Hashtable</param>
/// <returns>int</returns>
public static int UpdateByHashtable(string tableName, string pkName, string pkVal, Hashtable ht)
{
StringBuilder sb = new StringBuilder();
sb.Append(" UPDATE ");
sb.Append(tableName);
sb.Append(" SET ");
bool isFirstValue = true;
foreach (string key in ht.Keys)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append("@" + key);
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append("@" + key);
}
}
string where = string.Format(" WHERE {0} = '{1}'",pkName,pkVal);
sb.Append(where);
OleDbParameter[] parameters = GetParameter(ht);
return ExecuteNonQuery(sb.ToString(), parameters);
}
/// <summary>
/// 表单提交：新增，修改
/// 参数：
/// tableName:表名
/// pkName：字段主键
/// pkVal：字段值
/// ht：参数
/// </summary>
/// <returns></returns>
public static bool AddOrEdit(string tableName, string pkName, string pkVal, Hashtable ht)
{
if (string.IsNullOrEmpty(pkVal))
{
if (InsertByHashtable(tableName, ht) > 0)
return true;
else
return false;
}
else
{
if (UpdateByHashtable(tableName, pkName, pkVal, ht) > 0)
return true;
else
return false;
}
}
/// <summary>
/// 根据主键删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">主键字段名（也可以不是主键）</param>
/// <param name="pkVal">字段值</param>
/// <returns></returns>
public static int DeleteData(string tableName, string pkName, string pkVal)
{
string cmdText = "DELETE FROM " + tableName + " WHERE " + pkName + " = @ID";
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter("ID",pkVal),
};
return ExecuteNonQuery(cmdText, parameters);
}
/// <summary>
/// 根据条件删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="where">条件</param>
/// <returns></returns>
public static int DeleteData(string tableName, string where)
{
string cmdText = "DELETE FROM " + tableName + " " + where;
return ExecuteNonQuery(cmdText, null);
}
/// <summary>
/// 批量删除
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">主键字段名（也可以不是主键）</param>
/// <param name="pkVal">字段值</param>
/// <returns></returns>
public static int BatchDeleteData(string tableName, string pkName, object[] pkValues)
{
OleDbParameter[] parameters = new OleDbParameter[pkValues.Length];
int index = 0;
string str = "@ID" + index;
StringBuilder sql = new StringBuilder("DELETE FROM " + tableName + " WHERE " + pkName + " IN (");
for (int i = 0; i < (parameters.Length - 1); i++)
{
object obj2 = pkValues[i];
str = "@ID" + index;
sql.Append(str).Append(",");
parameters[index] = new OleDbParameter(str, obj2);
index++;
}
str = "@ID" + index;
sql.Append(str);
parameters[index] = new OleDbParameter(str, pkValues[index]);
sql.Append(")");
return ExecuteNonQuery(sql.ToString(), parameters);
}
/// <summary>
/// 判断数据是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">主键字段名（也可以不是主键）</param>
/// <param name="pkVal">字段值</param>
/// <returns></returns>
public static bool IsExist(string tableName, string pkName, string pkVal)
{
string cmdText = "SELECT COUNT(1) FROM " + tableName + " WHERE " + pkName + " = @" + pkName;
OleDbParameter[] parameters = {
new OleDbParameter("@" + pkName,pkVal)};
return (int)ExecuteScalar(cmdText, parameters) == 1;
}
/// <summary>
/// 判断数据是否存在
/// </summary>
/// <param name="tableName"></param>
/// <param name="where"></param>
/// <returns></returns>
public static bool IsExist(string tableName, string where)
{
string cmdText = "SELECT COUNT(1) FROM " + tableName + " " + where;
return (int)ExecuteScalar(cmdText, null) == 1;
}
/// <summary>
/// 批量拷贝数据
/// </summary>
/// <param name="maplist">列名映射</param>
/// <param name="tableName">目标表名</param>
/// <param name="dt">源数据源</param>
/// <returns></returns>
public static bool SqlBulkCopyImport(List<string> maplist, string tableName, DataTable dt)
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = tableName;
foreach (string a in maplist)
{
bulkCopy.ColumnMappings.Add(a, a);
}
bulkCopy.BatchSize = 1000;
bulkCopy.WriteToServer(dt);
conn.Close();
return true;
}
public static void insertByDataTable(string tableName, DataTable dt)
{
for (int i = 1; i < dt.Rows.Count; i++)
{
string sb = " insert into " + tableName + "(";
string columns = string.Empty;
string values = string.Empty;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][dt.Columns[j].ColumnName].ToString()))
{
columns += dt.Columns[j].ColumnName + ",";
if (dt.Columns[j].ColumnName.Contains("时间"))
{
values += "#" + dt.Rows[i][dt.Columns[j].ColumnName] + "#,";
}
else
{
values += "'" + dt.Rows[i][dt.Columns[j].ColumnName] + "',";
}
}
}
sb = sb + columns.TrimEnd(',') + ") values(" + values.TrimEnd(',') + ")";
ExecuteNonQuery(sb, null);
}
}
public static bool IsExitColumn(string[] columns, string column)
{
bool flag = false;
for (int i = 0; i < columns.Length; i++)
{
if (columns[i] == column)
{
flag = true;
break;
}
}
return flag;
}
#endregion
}
}