关于ms-sql数据库的,数据库学艺不精啊,本来一个方法可以搞定却写了两个方法,代码如下: public void updateUserMailAccount(String[] userNoArray, String mailAccountNo){
Connection connection = SqlConnection.getConnection();
PreparedStatement preparedStatement = null; String sql = "delete from TUserMailAccount where mailAccountNo = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, mailAccountNo);
preparedStatement.executeUpdate();
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
preparedStatement.close();
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
saveUserMailAccount(userNoArray, mailAccountNo);//这里再调用下面的方法,我想在这个方法里实现此功能
} public void saveUserMailAccount(String[] userNoArray, String mailAccountNo) {
Connection connection = SqlConnection.getConnection();
PreparedStatement preparedStatement = null;
String sql = "insert into TUserMailAccount(userNo,mailAccountNo) values(?,?)";
try {
for (int i = 0; i < userNoArray.length; i++) {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, userNoArray[i]);
preparedStatement.setString(2, mailAccountNo);
preparedStatement.executeUpdate();
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
preparedStatement.close();
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
数据库如下:
create table TUserMailAccount
(
userMailAccountNo int identity (1,1) not null,--id
userNo varchar(50) not null,--用户编号
mailAccountNo varchar(50) not null,--邮箱帐户编号
constraint PK_userNomailAccountNo primary key(userNo,mailAccountNo)--联合主键
)说明:这两个方法是实现一个删除数据,一个插入数据的,其实就是更新数据,由于要用循环,这两个方法合并为一个不大好写,请高人请点下吧.达到的效果是只留updateUserMailAccount这个方法,不要saveUserMailAccount方法,要达到同样的效果.还有,最好循环要在sql语句中写(这个是难点),先谢了.
Connection connection = SqlConnection.getConnection();
PreparedStatement preparedStatement = null; String sql = "delete from TUserMailAccount where mailAccountNo = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, mailAccountNo);
preparedStatement.executeUpdate();
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
preparedStatement.close();
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
saveUserMailAccount(userNoArray, mailAccountNo);//这里再调用下面的方法,我想在这个方法里实现此功能
} public void saveUserMailAccount(String[] userNoArray, String mailAccountNo) {
Connection connection = SqlConnection.getConnection();
PreparedStatement preparedStatement = null;
String sql = "insert into TUserMailAccount(userNo,mailAccountNo) values(?,?)";
try {
for (int i = 0; i < userNoArray.length; i++) {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, userNoArray[i]);
preparedStatement.setString(2, mailAccountNo);
preparedStatement.executeUpdate();
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
preparedStatement.close();
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
数据库如下:
create table TUserMailAccount
(
userMailAccountNo int identity (1,1) not null,--id
userNo varchar(50) not null,--用户编号
mailAccountNo varchar(50) not null,--邮箱帐户编号
constraint PK_userNomailAccountNo primary key(userNo,mailAccountNo)--联合主键
)说明:这两个方法是实现一个删除数据,一个插入数据的,其实就是更新数据,由于要用循环,这两个方法合并为一个不大好写,请高人请点下吧.达到的效果是只留updateUserMailAccount这个方法,不要saveUserMailAccount方法,要达到同样的效果.还有,最好循环要在sql语句中写(这个是难点),先谢了.
Connection connection = SqlConnection.getConnection();
PreparedStatement preparedStatement = null;
String sql = "delete from TUserMailAccount where mailAccountNo = ?";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, mailAccountNo);
preparedStatement.executeUpdate();
//插入操作
sql = "insert into TUserMailAccount(userNo,mailAccountNo) values(?,?)";
for (int i = 0; i < userNoArray.length; i++) {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, userNoArray[i]);
preparedStatement.setString(2, mailAccountNo);
preparedStatement.executeUpdate();
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
preparedStatement.close();
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
public void updateUserMailAccount(String[] userNoArray, String mailAccountNo){
Connection connection = SqlConnection.getConnection();
PreparedStatement preparedStatement = null;
String sql = "delete from TUserMailAccount where mailAccountNo = ? ";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, mailAccountNo);
preparedStatement.executeUpdate();
//插入操作
sql = "insert into TUserMailAccount(userNo,mailAccountNo) values(?,?) ";
for (int i = 0; i < userNoArray.length; i++) {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, userNoArray[i]);
preparedStatement.setString(2, mailAccountNo);
preparedStatement.executeUpdate();
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
preparedStatement.close();
connection.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
这样写是没问题的,但总觉得不大优雅啊,循环在sql里如何写呢?还有,最好也有用到事务比较好点,这里删除与插入是一件事啊,不可分的,如果删除了没插入成功,那会出现问题的,请继续指教.(就是对那个事务和循环不是很熟,不知道怎么入手)
其实这贴我主要是想看看在sql语句里怎么实现循环操作,而不要在程序里用for,据说只能用while,可不大好写.