java中的基本jdbc中mvc基本示例

数据库:

包文件:

Student.java

 1 package com.model; 2  3 public class Student { 4     PRivate int id; 5     private String name; 6     private int age; 7     private String address; 8     public int getId() { 9         return id;10     }11     public void setId(int id) {12         this.id = id;13     }14     public String getName() {15         return name;16     }17     public void setName(String name) {18         this.name = name;19     }20     public int getAge() {21         return age;22     }23     public void setAge(int age) {24         this.age = age;25     }26     public String getAddress() {27         return address;28     }29     public void setAddress(String address) {30         this.address = address;31     }32 }

DBUtil.java

 1 package com.db; 2  3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8  9 public class DBUtil {10     private static final String URL="jdbc:MySQL://127.0.0.1:3306/jdbc";11     private static final String USER="root";12     private static final String PASSWord="gys";13     private static Connection conn=null;14     static{15         try {16             //加载驱动程序17             Class.forName("com.mysql.jdbc.Driver");18             //获取数据库连接19             conn= DriverManager.getConnection(URL, USER, PASSWORD);20         } catch (ClassNotFoundException e) {21             // TODO Auto-generated catch block22             e.printStackTrace();23         } catch (SQLException e) {24             // TODO Auto-generated catch block25             e.printStackTrace();26         }27     }28     29     public static Connection getConnection(){30         return conn;31     }32     33     public static void main(String[] args) throws Exception{34         35         //创建statement对象36         Statement stmt=conn.createStatement();37         ResultSet rs=stmt.executeQuery("select * from student");38          39         while(rs.next()){40             System.out.println(rs.getInt("id")+","+rs.getString("name")+","+rs.getInt("age")+","+rs.getString("address"));41         }42         43     }44 }

StudentDao.java

  1 package com.dao;  2   3 import java.sql.Connection;  4 import java.sql.PreparedStatement;  5 import java.sql.ResultSet;  6 import java.sql.SQLException;  7 import java.sql.Statement;  8 import java.util.ArrayList;  9 import java.util.List; 10 import java.util.Map; 11  12 import com.db.DBUtil; 13 import com.model.Student; 14  15 /* 16  * 17  */ 18 public class StudentDao { 19     // 添加 20     public void addStudent(Student s) throws Exception { 21         Connection conn = DBUtil.getConnection(); 22         String sql = "insert into student(name,age,address) values(?,?,?)"; 23         PreparedStatement ptmt = conn.prepareStatement(sql); 24         ptmt.setString(1, s.getName()); 25         ptmt.setInt(2, s.getAge()); 26         ptmt.setString(3, s.getAddress()); 27         ptmt.execute(); 28     } 29  30     // 更新 31     public void updateStudent(Student s) throws Exception { 32         Connection conn = DBUtil.getConnection(); 33         String sql = " update student set name=?,age=?,address=? where id=? "; 34         PreparedStatement ptmt = conn.prepareStatement(sql); 35         ptmt.setString(1, s.getName()); 36         ptmt.setInt(2, s.getAge()); 37         ptmt.setString(3, s.getAddress()); 38         ptmt.setInt(4, s.getId()); 39         ptmt.execute(); 40         System.out.println("更新完毕."); 41  42     } 43  44     // 删除 45     public void delStudent(Integer id) throws SQLException { 46         Connection conn = DBUtil.getConnection(); 47         String sql = " delete from student where id=? "; 48         PreparedStatement ptmt = conn.prepareStatement(sql); 49         ptmt.setInt(1, id); 50         ptmt.execute(); 51         System.out.println("删除成功."); 52     } 53  54     // 查询list 55     public List<Student> query() throws Exception { 56         Connection conn = DBUtil.getConnection(); 57         Statement stmt = conn.createStatement(); 58         ResultSet rs = stmt.executeQuery("select * from student"); 59         List<Student> list = new ArrayList<Student>(); 60         Student student = null; 61         while (rs.next()) { 62             student = new Student(); 63             student.setId(rs.getInt("id")); 64             student.setName(rs.getString("name")); 65             student.setAge(rs.getInt("age")); 66             student.setAddress(rs.getString("address")); 67             list.add(student); 68         } 69         return list; 70     } 71  72     // 查询list 73     public List<Student> query(String name) throws Exception { 74         Connection conn = DBUtil.getConnection(); 75         StringBuilder sb = new StringBuilder(); 76         sb.append("select * from student "); 77         sb.append(" where name=?"); 78         PreparedStatement ptmt = conn.prepareStatement(sb.toString()); 79         ptmt.setString(1, name); 80         ResultSet rs = ptmt.executeQuery(); 81         List<Student> list = new ArrayList<Student>(); 82         Student student = null; 83         while (rs.next()) { 84             student = new Student(); 85             student.setId(rs.getInt("id")); 86             student.setName(rs.getString("name")); 87             student.setAge(rs.getInt("age")); 88             student.setAddress(rs.getString("address")); 89             list.add(student); 90         } 91         return list; 92     } 93  94     // 查询list 95     public List<Student> query(List<Map<String, Object>> params) 96             throws Exception { 97         Connection conn = DBUtil.getConnection(); 98         StringBuilder sb = new StringBuilder(); 99         sb.append("select * from student where 1=1 ");100         //sb.append(" where name=?");101         if(params !=null&&params.size()>0){102             for(int i=0;i<params.size();i++){103                 Map<String,Object> map=params.get(i);104                 sb.append(" and "+map.get("name")+" "+map.get("rela")+" "+map.get("value"));105             }106         }107         PreparedStatement ptmt = conn.prepareStatement(sb.toString());108         //ptmt.setString(1, name);109         ResultSet rs = ptmt.executeQuery();110         List<Student> list = new ArrayList<Student>();111         Student student = null;112         while (rs.next()) {113             student = new Student();114             student.setId(rs.getInt("id"));115             student.setName(rs.getString("name"));116             student.setAge(rs.getInt("age"));117             student.setAddress(rs.getString("address"));118             list.add(student);119         }120         return list;121     }122 123     // 查询单个124     public Student getStudent(Integer id) throws SQLException {125         Connection conn = DBUtil.getConnection();126         String sql = " select * from student where id=? ";127         PreparedStatement ptmt = conn.prepareStatement(sql);128         ptmt.setInt(1, id);129         // Statement stmt=conn.createStatement();130         ResultSet rs = ptmt.executeQuery();131         Student student = null;132         while (rs.next()) {133             student = new Student();134             student.setId(rs.getInt("id"));135             student.setName(rs.getString("name"));136             student.setAge(rs.getInt("age"));137             student.setAddress(rs.getString("address"));138         }139         return student;140     }141 }

StudentAction.java

 1 package com.action; 2  3 import java.sql.SQLException; 4 import java.util.ArrayList; 5 import java.util.HashMap; 6 import java.util.List; 7 import java.util.Map; 8  9 import com.dao.StudentDao;10 import com.model.Student;11 12 /*13  * 控制层14  */15 public class StudentAction {16     public void add(Student student) throws Exception{17         StudentDao dao=new StudentDao();18         dao.addStudent(student);19     }20     public void edit(Student student) throws Exception{21         StudentDao dao=new StudentDao();22         dao.updateStudent(student);23     }24     public Student getStudent(Integer id) throws SQLException{25         StudentDao dao=new StudentDao();26         return dao.getStudent(id);27     }28     public void del(Integer id) throws SQLException{29         StudentDao dao=new StudentDao();30         dao.delStudent(id);31     }32     public List<Student> query() throws Exception{33         StudentDao dao=new StudentDao();34         return dao.query();35     }36     public List<Student> query(List<Map<String,Object>> params) throws Exception{37         StudentDao dao=new StudentDao();38         return dao.query(params);39     }    40     //public static void main(String[] args) throws Exception {41         //StudentDao studentDao=new StudentDao();42         43         //添加学生44         /*Student student=new Student();45         student.setName("嘎嘎");46         student.setAge(26);47         student.setAddress("安徽省");48         studentDao.addStudent(student);*/49         50         //查询学生list51         /*List<Student> list=studentDao.query();52         for(Student s:list){53             System.out.println(s.getId()+","+s.getName()+","+s.getAge()+","+s.getAddress());54         }*/55         56         /*List<Student> list=studentDao.query("嘎嘎");57         for(Student s:list){58             System.out.println(s.getId()+","+s.getName()+","+s.getAge()+","+s.getAddress());59         }*/60         61     /*    List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();62         Map<String,Object> param=new HashMap<String,Object>();63         param.put("name","name");64         //param.put("rela","=");65         param.put("rela"," like ");66         param.put("value","'嘎嘎'");67         params.add(param);68         69         List<Student> list=studentDao.query(params);70         for(Student s:list){71             System.out.println(s.getId()+","+s.getName()+","+s.getAge()+","+s.getAddress());72         }*/73         //更新数据74         /*Student student=new Student();75         student.setId(3);76         student.setName("hee");77         student.setAge(100);78         student.setAddress("月球上");79         studentDao.updateStudent(student);*/80         81         //查询单个学生82 //        Student student=studentDao.getStudent(2);83 //        System.out.println(student.getName()+";"+student.getAge()+","+student.getAddress());84         85         //删除86         //studentDao.delStudent(3);87     //}88 }