JDBC 增删查改

时间:2021-10-15 16:18:09
public class MemberDaoImpl implements MemberDao {
private Connection conn = null;
public MemberDaoImpl(){
this.conn=ConnCreate.getConnection("jdbc:mysql://localhost:3306/supermarket?"
+ "useUnicode=true&characterEncoding=utf8", "root", "");
}
@Override
public int add(Member member) throws SQLException {
PreparedStatement pst=null;
int num=0;
try{
String sql="insert into member values(?,?,?,?)";
pst=conn.prepareStatement(sql);//准备执行清单
pst.setInt(1,member.getMemberid());
pst.setString(2,member.getMembername());
pst.setString(3,member.getOpendate());
pst.setInt(4,member.getIntegral());
pst.executeUpdate();//执行sql语句
} finally {
ConnCreate.close(conn, pst, null);//释放资源
num++;
}
return num;
} @Override
public int update(Member member, int num) throws SQLException {
PreparedStatement pst=null;
int count=0;
try{
String sql = "update member set memberid=?,membername=?,opendate=?,integral=? where memberid=?";
pst=conn.prepareStatement(sql);//准备执行清单
pst.setInt(1,member.getMemberid());
pst.setString(2,member.getMembername());
pst.setString(3,member.getOpendate());
pst.setInt(4,member.getIntegral());
pst.setInt(5,num);
pst.executeUpdate();//执行sql语句
} finally {
ConnCreate.close(conn, pst, null);//释放资源
count++;
}
return count;
} @Override
public int delete(int num) throws SQLException {
PreparedStatement st=null;
int count=0;
try {//获取执行语句
String sql = "delete from member where memberid=?";
st = conn.prepareStatement(sql);//创建执行语柄
st.setInt(1,num);//准备执行清单
st.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnCreate.close(null, st, null);//释放资源
count++;
}
return count;
} @Override
public List<Member> query(int number) throws SQLException {
List<Member> num=new LinkedList<Member>();
Statement st=null;
ResultSet rs = null;
try{
//创建执行句柄
st = conn.createStatement();
//执行sql语句
rs = st.executeQuery("select * from Member where memberid="+number);
while(rs.next()){
Member c=new Member();
c.setMemberid(rs.getInt(1));
c.setMembername(rs.getString(2));
c.setOpendate(rs.getString(3));
c.setIntegral(rs.getInt(4));
num.add(c);
}
} finally {
ConnCreate.close(conn, st, rs);//释放资源
}
return num;
} @Override
public List<Member> queryAll() throws SQLException {
List<Member> num=new LinkedList<Member>();
Statement st=null;
ResultSet rs = null;
try{
//创建执行句柄
st = conn.createStatement();
//执行sql语句
rs = st.executeQuery("select * from Member ");
while(rs.next()){
Member c=new Member();
c.setMemberid(rs.getInt(1));
c.setMembername(rs.getString(2));
c.setOpendate(rs.getString(3));
c.setIntegral(rs.getInt(4));
num.add(c);
}
} finally {
ConnCreate.close(conn, st, rs);//释放资源
}
return num;
} @Override
public int updatein(int num, int num1) throws SQLException {
PreparedStatement st=null;
int count=0;
try {//获取执行语句
String sql = "update member set integral=integral+? where memberid=?";
st = conn.prepareStatement(sql);//创建执行语柄
st.setInt(1,num);//准备执行清单
st.setInt(2,num1);
st.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnCreate.close(null, st, null);//释放资源
count++;
}
return count;
}
@Override
public int updatein1(int num, int num1) throws SQLException {
PreparedStatement st=null;
int count=0;
try {//获取执行语句
String sql = "update storage set depositnum=depositnum-? where id=?";
st = conn.prepareStatement(sql);//创建执行语柄
st.setInt(1,num);//准备执行清单
st.setInt(2,num1);
st.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnCreate.close(null, st, null);//释放资源
count++;
}
return count;
}
@Override
public List<Member> queryjifen(int num1) throws SQLException {
List<Member> num=new LinkedList<Member>();
Statement st=null;
ResultSet rs = null;
try{
//创建执行句柄
st = conn.createStatement();
//执行sql语句
rs = st.executeQuery("select * from Member where integral>"+num1);
while(rs.next()){
Member c=new Member();
c.setMemberid(rs.getInt(1));
c.setMembername(rs.getString(2));
c.setOpendate(rs.getString(3));
c.setIntegral(rs.getInt(4));
num.add(c);
}
} finally {
ConnCreate.close(conn, st, rs);//释放资源
}
return num;
} }