import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.qd.domain.Student;
public class TestCURD {
private static Connection con = null;
private static PreparedStatement pst = null;
private static ResultSet rs = null;
/**增加学生**/
public static void add(Student stu){
String sql = "insert into student(name,sex) values(?,?)";
try{
con = DBCutil.getConnection();
//创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库
pst = con.prepareStatement(sql);
pst.setString(1,stu.getName());
pst.setString(2,stu.getSex());
//在此 PreparedStatement 对象中执行 SQL 语句,该语句必须是一个 SQL
//数据操作语言(Data Manipulation Language,DML)语句,比如 INSERT、
//UPDATE 或 DELETE 语句;或者是无返回内容的 SQL 语句,比如 DDL 语句。
pst.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(pst!=null)
pst.close();
if(con!=null)
pst.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
/**删除学生**/
public static void delete(int id){
String sql = "delete from student where id='"+id+"'";
try{
con = DBCutil.getConnection();
pst = con.prepareStatement(sql);
pst.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(pst!=null)
pst.close();
if(con!=null)
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
/**修改学生**/
public static void update(Student stu){
String sql = "update student set name=?,sex=? where id=?";
try{
con = DBCutil.getConnection();
pst = con.prepareStatement(sql);
pst.setString(1,stu.getName());
pst.setString(2,stu.getSex());
pst.setInt(3,stu.getId());
pst.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(pst!=null)
pst.close();
if(con!=null)
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
/**查找学生**/
public static List<Student> find(){
List<Student> list = new ArrayList<Student>();
String sql = "select * from student";
try{
con = DBCutil.getConnection();
pst = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = pst.executeQuery();
rs.last();//将光标移动到此 ResultSet 对象的最后一行。返回的是boolean值
int num = rs.getRow();//获取当前行编号。
if(num == 0){
return null;
}else{
rs.beforeFirst();// 将光标移动到此 ResultSet 对象的开头,正好位于第一行之前。
while(rs.next()){
Student stu = new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
list.add(stu);
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs!=null)
rs.close();
if(pst!=null)
pst.close();
if(con!=null)
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
return list;
}
/**查询学生通过id**/
public static Student findById(int id){
Student stu = new Student();
String sql = "select * from student where id='"+id+"'";
try{
con = DBCutil.getConnection();
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
while(rs.next()){
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
return stu;
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs!=null)
rs.close();
if(pst!=null)
pst.close();
if(con!=null)
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
return stu;
}
}