3_32江_0524—MVC—MySQL增删改查测试
public class BandDao implements IBandDao {
@Override
public Boolean insert(Band band) {
boolean ret = false;
try {
Connection conn = MySqlDbUtil.getConnection();
String sql = "INSERT INTO band332.band(id,name,remark,status) VALUES(?,?,?,?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1,band.getId());
stmt.setString(2,band.getName());
stmt.setString(3,band.getRemark());
stmt.setByte(4,band.getStatus());
int i = stmt.executeUpdate();
if(i>0){
ret = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return ret;
}
@Override
public Boolean delete(Integer id) {
boolean ret = false;
try {
Connection conn = MySqlDbUtil.getConnection();
String sql = "DELETE FROM band WHERE id=?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);
int i = stmt.executeUpdate();
if(i>0){
ret = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return ret;
}
@Override
public Boolean update(Band band) {
boolean ret = false;
try {
Connection conn = MySqlDbUtil.getConnection();
String sql = "UPDATE band332.band set name=?,remark=?,status=? WHERE id=?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,band.getName());
stmt.setString(2,band.getRemark());
stmt.setByte(3,band.getStatus());
stmt.setInt(4,band.getId());
int i = stmt.executeUpdate();
if(i>0){
ret = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return ret;
}
@Override
public Band findById(Integer id) {
Band band = null;
try {
Connection conn = MySqlDbUtil.getConnection();
String sql = "SELECT * FROM band WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);
ResultSet rs = stmt.executeQuery();
if(rs.next()){
band = new Band();
band.setId(rs.getInt("id"));
band.setName(rs.getString("name"));
band.setRemark(rs.getString("remark"));
band.setStatus(rs.getByte("status"));
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return band;
}
@Override
public List<Band> findAll() {
List<Band> bands = new ArrayList<>();
try {
Connection conn = MySqlDbUtil.getConnection();
String sql = "SELECT * FROM band";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
Band band = new Band();
band.setId(rs.getInt("id"));
band.setName(rs.getString("name"));
band.setRemark(rs.getString("remark"));
band.setStatus(rs.getByte("status"));
bands.add(band);
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return bands;
}
@Override
public List<Band> find(Band band) {
List<Band> bands = new ArrayList<>();
List<Object> params = new ArrayList<>();
try {
Connection conn = MySqlDbUtil.getConnection();
String sql = "SELECT * FROM band WHERE 1=1";
if(band !=null){
if(band.getName() != null && !"".equals(band.getName())){
sql = sql + "AND name LIKE ?";
params.add("%"+band.getName()+"%");
}
if(band.getRemark() != null && !"".equals(band.getRemark())){
sql = sql + "AND remark LIKE ?";
params.add("%"+band.getRemark()+"%");
}
if(band.getStatus() != null && !"".equals(band.getStatus())){
sql = sql + "AND status = ?";
params.add(band.getStatus());
}
}
PreparedStatement stmt = conn.prepareStatement(sql);
for(int i = 0 ;i<params.size();i++){
stmt.setObject(i+1,params.get(i));
}
ResultSet rs = stmt.executeQuery();
while(rs.next()){
band = new Band();
band.setId(rs.getInt("id"));
band.setName(rs.getString("name"));
band.setRemark(rs.getString("remark"));
band.setStatus(rs.getByte("status"));
bands.add(band);
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return bands;
}
}