【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 blocke.printStackTrace();}}public  static Connection getconnection(){Connection conn = null;try {conn = DriverManager.getConnection(url,"root","root");} catch (SQLException e) {// TODO Auto-generated catch blocke.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 exceptione.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 exceptione.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 exceptione.printStackTrace();}finally{DBOperator.close(rs, st, conn);}return list;}public boolean add(Student stu) {// TODO Auto-generated method stubboolean 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 exceptione.printStackTrace();}finally{DBOperator.close(pst, conn);}return flag;}public boolean del(int id) {// TODO Auto-generated method stubboolean 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 exceptione.printStackTrace();}finally{DBOperator.close(pst, conn);}return flag;}public boolean update(Student stu) {// TODO Auto-generated method stubboolean 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 exceptione.printStackTrace();}finally{DBOperator.close(pst, conn);}return false;}public Student getStudentById(int id) {// TODO Auto-generated method stubStudent 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 exceptione.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 stubStudentManager 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 stubStudentManager 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 stubStudentManager 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 stubStudentManage 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 stubStudentManage 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("查询失败");}}}

致此，工程完结。