package com
.cissst
.dao
.impl;
import java
.sql.Connection;
import java
.sql.PreparedStatement;
import java
.sql.ResultSet;
import java
.sql.SQLException;
import java
.util
.ArrayList;
import java
.util
.List;
import com
.cissst
.dao
.IAdminDao;
import com
.cissst
.entity
.Admin;
import com
.cissst
.util
.DBUtil;
public class AdminDaoImpl implements IAdminDao {
@Override
public List<Admin> getAllAdmin
() throws
SQLException {
String
sql = "select * from admin order by name";
try
(Connection conn = DBUtil
.getConnection
();
PreparedStatement stmt = conn
.prepareStatement
(sql);
ResultSet
rs = stmt
.executeQuery
()) {
List<Admin> list = new ArrayList<>
();
while
(rs.next
()) {
Admin a = new Admin
();
a
.setId
(rs.getInt
("id"
));
a
.setName
(rs.getString("name"
));
a
.setPassword
(rs.getString("password"
));
a
.setAge
(rs.getInt
("age"
));
a
.setSex
(rs.getString("sex"
));
a
.setTel
(rs.getString("tel"
));
a
.setPhone
(rs.getString("phone"
));
a
.setAddr
(rs.getString("addr"
));
a
.setMemo
(rs.getString("memo"
));
list
.add
(a
);
}
return list;
}
}
@Override
public void save
(Admin a
) throws
SQLException {
String
sql = "insert into admin
(NAME,PASSWORD,SEX,AGE,TEL,PHONE,ADDR,MEMO
) values
(?,?,?,?,?,?,?,?
) ";
try
(Connection conn = DBUtil
.getConnection
();
PreparedStatement stmt = conn
.prepareStatement
(sql)) {
stmt
.setString
(1, a
.getName
());
stmt
.setString
(2, a
.getPassword
());
stmt
.setString
(3, a
.getSex
());
stmt
.setInt
(4, a
.getAge
());
stmt
.setString
(5, a
.getTel
());
stmt
.setString
(6, a
.getPhone
());
stmt
.setString
(7, a
.getAddr
());
stmt
.setString
(8, a
.getMemo
());
stmt
.executeUpdate
();
}
}
@Override
public Admin getAdminById
(String id
) throws
SQLException {
String
sql = "select * from admin where id = ?";
Admin a = null;
try
(Connection conn = DBUtil
.getConnection
();
PreparedStatement stmt = conn
.prepareStatement
(sql)) {
stmt
.setString
(1, id
);
try
(ResultSet
rs = stmt
.executeQuery
()) {
while
(rs.next
()) {
a = new Admin
();
a
.setId
(rs.getInt
("id"
));
a
.setName
(rs.getString("name"
));
a
.setPassword
(rs.getString("password"
));
a
.setAge
(rs.getInt
("age"
));
a
.setSex
(rs.getString("sex"
));
a
.setTel
(rs.getString("tel"
));
a
.setPhone
(rs.getString("phone"
));
a
.setAddr
(rs.getString("addr"
));
a
.setMemo
(rs.getString("memo"
));
}
}
}
return a;
}
@Override
public void update
(Admin a
) throws
SQLException {
String
sql = " update admin set NAME = ?,PASSWORD = ?,SEX = ?,AGE = ?, TEL = ?,PHONE = ?,ADDR = ?,MEMO = ? where id = ? ";
try
(Connection conn = DBUtil
.getConnection
();
PreparedStatement stmt = conn
.prepareStatement
(sql)) {
stmt
.setString
(1, a
.getName
());
stmt
.setString
(2, a
.getPassword
());
stmt
.setString
(3, a
.getSex
());
stmt
.setInt
(4, a
.getAge
());
stmt
.setString
(5, a
.getTel
());
stmt
.setString
(6, a
.getPhone
());
stmt
.setString
(7, a
.getAddr
());
stmt
.setString
(8, a
.getMemo
());
stmt
.setInt
(9, a
.getId
());
stmt
.executeUpdate
();
}
}
@Override
public void delete
(String id
) throws
SQLException {
String
sql = "delete from admin where id= ?";
try
(Connection conn = DBUtil
.getConnection
();
PreparedStatement stmt = conn
.prepareStatement
(sql)) {
stmt
.setString
(1, id
);
stmt
.executeUpdate
();
}
}
@Override
public Admin getAdminBynp
(String name, String password
) throws
SQLException {
String
sql = "select * from admin where name = ? and password = ?";
Admin a = null;
try
(Connection conn = DBUtil
.getConnection
()) {
assert conn != null;
try
(PreparedStatement stmt = conn
.prepareStatement
(sql)) {
stmt
.setString
(1, name
);
stmt
.setString
(2, password
);
try
(ResultSet
rs = stmt
.executeQuery
()) {
while
(rs.next
()) {
a = new Admin
();
a
.setId
(rs.getInt
("id"
));
a
.setName
(rs.getString("name"
));
a
.setPassword
(rs.getString("password"
));
a
.setAge
(rs.getInt
("age"
));
a
.setSex
(rs.getString("sex"
));
a
.setTel
(rs.getString("tel"
));
a
.setPhone
(rs.getString("phone"
));
a
.setAddr
(rs.getString("addr"
));
a
.setMemo
(rs.getString("memo"
));
}
}
}
}
return a;
}
}at com
.cissst
.dao
.impl
.AdminDaoImpl
.getAdminBynp
(AdminDaoImpl
.java:
116
)
本文讨论了在Java中使用PreparedStatement执行SQL语句时可能遇到的问题，特别是当在一个事务中先后执行了查询和更新操作，并尝试再次从原先的查询结果中获取数据时会发生的错误。文章提供了解决方案，建议在执行新的SQL操作前保存所需的查询结果。
扫一扫
2080
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
