数据库:
包文件:
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 block
22 e.printStackTrace();
23 } catch (SQLException e) {
24 // TODO Auto-generated catch block
25 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&¶ms.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();