【JAVA基础】增，删，改，查

增删改查（JDBC连接数据库）

       今天，叙述用Eclipse写增删改查地具体实施方法。首先 建立工程之后，需要导入一个与mysql连接的jar包，这是mysql提供的一个jar。官网上可以下载。

导入包之后，我们就要与数据库建立连接。先建一个DBOpreator的class。

                                                               

在这个class下面实现与数据库的链接：

public class DBOperator {

	protected final static String driver = "com.mysql.jdbc.Driver";
	protected final static String url = "jdbc:mysql://localhost:3306/db_students";
	
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public  static Connection getconnection(){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url,"root","root");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return conn;
		
	}
	public static void  close (ResultSet rs, Statement st, Connection conn){
		try {
			if (rs!=null){
				rs.close();
			}
			if(st!=null){
				st.close();
			}
			if(conn!=null){
				conn.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	public static void close (PreparedStatement pst, Connection conn){
		try {
			if(pst!=null){
				pst.close();
				
			}
			if(conn!=null){
				conn.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
}

接下来就是MVC框架，想吧JAVABeen写下好，以便我们以后使用，这是一种规范，虽然看起来麻烦，但是于别人阅读代码，于程序的执行都是百利而无一害。

这个例子是对学生进行增删改查，我们要对学生这一对象，建立相应的属性，id，name，tel等：

public class Student {

	protected int id;
	protected String name;
	protected String tel;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	
}

建立学生对象之后，来写实现增删改查地方法，首先建立一个接口，把增删改查地空方法写在里面，再写一个类去实现这个接口。

这样写的好处无非也是精简代码，便于阅读，增加执行代码的流畅度。

public interface StudentManager {
	
	public List<Student> getStudents();
	public boolean add(Student stu);
	public boolean del( int id);
	public boolean update (Student stu);
	public Student getStudentById(int id);

}

public class StudentManagerImpl implements StudentManager{

	public List<Student> getStudents() {
		
		List<Student> list = new ArrayList<Student>();
		
		Connection conn =null;
		Statement st =null;
		ResultSet rs =null;
		try {
			conn = DBOperator.getconnection();
			String sql = "select * from students";
			st = (Statement) conn.createStatement();
			rs = (ResultSet) st.executeQuery(sql);
			if (rs!=null){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setTel(rs.getString("tel"));
				
				list.add(stu);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DBOperator.close(rs, st, conn);
		}
		
		
		return list;
	}

	public boolean add(Student stu) {
		// TODO Auto-generated method stub
		boolean flag = false;
		Connection conn =null;
		PreparedStatement pst =null;
		try {
			
			conn= DBOperator.getconnection();
			String sql = "insert into students(name,tel) values(?,?)";
			pst= conn.prepareStatement(sql);
			pst.setString(1, stu.getName());
			pst.setString(2, stu.getTel());
			
			int wos= pst.executeUpdate();
			if(wos>0){
				flag= true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DBOperator.close(pst, conn);
		}
		
		
		return flag;
	}

	public boolean del(int id) {
		// TODO Auto-generated method stub
		boolean flag = false;
		
		Connection conn =null;
		PreparedStatement pst =null;
		try {
			conn = DBOperator.getconnection();
			String sql = "delete from students where id=?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			
			int rows = pst.executeUpdate();
			if(rows>0){
				flag= true;
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DBOperator.close(pst, conn);
		}
		
		
		return flag;
	}

	public boolean update(Student stu) {
		// TODO Auto-generated method stub
		
		boolean flag = false;
		Connection conn =null;
		PreparedStatement pst =null;
		try {
			
			conn = DBOperator.getconnection();
			String sql = "update students set name=?,tel=? where id=?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, stu.getName());
			pst.setString(2, stu.getTel());
			pst.setInt(3, stu.getId());
			
			int rows = pst.executeUpdate();
			if (rows>0){
				flag=true;
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DBOperator.close(pst, conn);
		}
		
		return false;
	}

	public Student getStudentById(int id) {
		// TODO Auto-generated method stub
		
		Student stu = new Student();
		Connection conn = null;
		Statement st =null;
		ResultSet rs = null;
		try {
			conn = DBOperator.getconnection();
			String sql = "select *  from students by id="+id;
			st = (Statement) conn.createStatement();
			rs= (ResultSet) st.executeQuery(sql);
			if(rs.next()){
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setTel(rs.getString("tel"));
			}
			
			
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DBOperator.close(rs, st, conn);
		}
		
		return stu;
	}
	
	

}

这样我们就完成了工程的绝大部分功能。

最后写一个Test去测试我们写的是否正确：

增：（add）

public class ADD {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		StudentManager dao = new StudentManagerImpl();
		Student stu = new Student();
		stu.setName("李四");
		stu.setTel("555555");
		
		boolean flag = dao.add(stu);
		if(flag){
			System.out.println("添加成功");
		}else{
			System.out.println("添加失败");
		}
		

	}

}

删：（del）

public class Del {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		StudentManager dao = new StudentManagerImpl();
		Student stu = new Student();
		boolean flag = dao.del(19);
		if(flag){
			System.out.println("删除成功");
		}else {
			System.out.println("删除失败");
		}
	}

}

更新（update）：

public class Update {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		StudentManager dao = new StudentManagerImpl();
		Student stu = dao.getStudentById(5);
		stu.setName("任我行");
		stu.setTel("666666");
		boolean flag = dao.update(stu);
		if(flag){
			System.out.println("更新成功");
		}
		

	}

}

查询：（整列，list）

public class GetList {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		StudentManage dao = new StudentManagerImpl();
		List<Student> list =dao.getStudents();
		for (Student s : list ){
			System.out.println("id："+s.getId());
			System.out.println("姓名："+s.getName());
			System.out.println("电话："+s.getTel());
			System.out.println(".............");
		}
	}

查询：（某个学生，ById）

 

public class Get {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		StudentManage dao = new StudentManagerImpl();
		Student stu = dao.getStudentById(1);
		
		if(stu!=null){
			System.out.println("查询成功");
			System.out.println("id："+stu.getId());
			System.out.println("name："+stu.getName());
			System.out.println("tel："+stu.getTel());
		}else{
			System.out.println("查询失败");
		}

	}

}

致此，工程完结。