java 视频学习，删改查的实现 2015年9月19日
学习
0 条评论

昨天是弄了增的操作，剩下删改查。

一、代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
package com.imooc.dao;
 
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import com.imooc.db.dbutil;
import com.imooc.model.Goddness;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
 
public class GoddnessDao {
 
public void addGoddess(Goddness g) throws Exception {
 
Connection conn = dbutil.getConnection();//获得链接
String sql = ""+
            "insert into myfriend"+
"(user_name,sex,age,birthday,email,mobile,"+
    "create_user,create_date,update_user,update_date,isdel)"+
"values("+
    "?,?,?,?,?,?,?,current_date(),?,current_date(),?)";//编写sql语句
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译
ptmt.setString(1, g.getUser_name());//传参
ptmt.setInt(2, g.getSex());
ptmt.setInt(3, g.getAge());
ptmt.setDate(4, new Date(g.getBirthday().getTime()));
ptmt.setString(5, g.getEmail());
ptmt.setString(6, g.getMobile());
ptmt.setString(7, g.getCreate_user());
ptmt.setString(8, g.getUpdate_user());
ptmt.setInt(9, g.getIsdel());//最后调用execute方法来执行，非常有条理
ptmt.execute();
}
 
public void updateGoddess(Goddness g) throws SQLException {
 
Connection conn = dbutil.getConnection();//获得链接
String sql = ""+
            " update myfriend "+
" set user_name = ?,sex = ?,age = ?,birthday = ?,email = ?,mobile = ?, "+
    " update_user = ?,update_date = current_date(),isdel = ? "+
    " where id = ? ";//编写sql语句
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译
ptmt.setString(1, g.getUser_name());//传参
ptmt.setInt(2, g.getSex());
ptmt.setInt(3, g.getAge());
ptmt.setDate(4, new Date(g.getBirthday().getTime()));
ptmt.setString(5, g.getEmail());
ptmt.setString(6, g.getMobile());
ptmt.setString(7, g.getUpdate_user());
ptmt.setInt(8, g.getIsdel());
ptmt.setInt(9, g.getId());
ptmt.execute();//最后调用execute方法来执行，非常有条理
}
 
public void delGoddess(Integer id) throws SQLException  {
Connection conn = dbutil.getConnection();//获得链接
String sql = ""+
            " delete from myfriend "+
    " where id = ? ";//编写sql语句
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译
ptmt.setInt(1, id);
ptmt.execute();//最后调用execute方法来执行，非常有条理
 
}
 
public List<Goddness> query() throws SQLException // 就是这里出错了
{
Connection conn = dbutil.getConnection();
 
Statement stmt = (Statement) conn.createStatement();
ResultSet rs = stmt.executeQuery("select user_name,age from myfriend");
 
List<Goddness> gs = new ArrayList<Goddness>();
Goddness g = null;
 
while (rs.next()) {
g = new Goddness();
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
 
gs.add(g);
}
return gs;
}
 
public Goddness get(Integer id) throws SQLException // 不知道为什么可以这样定义
{
Goddness g = null;
Connection conn = dbutil.getConnection();//获得链接
String sql = ""+
            " select * from myfriend "+
    " where id = ? ";//编写sql语句
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译
ptmt.setInt(1, id);
ResultSet rs = ptmt.executeQuery();//查询这里不再用execute操作，execute针对数据的修改，增删改
//查询的话用executeQuery()查询操作
//因为要输出查询结果，所以要和上面一样，写一个输出的转换
while(rs.next()) //遍历结果集
{
g = new Goddness();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
g.setSex(rs.getInt("sex"));
g.setBirthday(rs.getDate("birthday"));
g.setEmail(rs.getString("email"));
g.setMobile(rs.getString("mobile"));
g.setCreate_date(rs.getDate("create_date"));
g.setCreate_user(rs.getString("create_user"));
g.setUpdate_date(rs.getDate("update_date"));
g.setUpdate_user(rs.getString("update_user"));
g.setIsdel(rs.getInt("isdel"));
}
return g;
}
}

（1）改操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public void updateGoddess(Goddness g) throws SQLException {
 
Connection conn = dbutil.getConnection();//获得链接
String sql = ""+
            " update myfriend "+
" set user_name = ?,sex = ?,age = ?,birthday = ?,email = ?,mobile = ?, "+
    " update_user = ?,update_date = current_date(),isdel = ? "+
    " where id = ? ";//编写sql语句
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译
ptmt.setString(1, g.getUser_name());//传参
ptmt.setInt(2, g.getSex());
ptmt.setInt(3, g.getAge());
ptmt.setDate(4, new Date(g.getBirthday().getTime()));
ptmt.setString(5, g.getEmail());
ptmt.setString(6, g.getMobile());
ptmt.setString(7, g.getUpdate_user());
ptmt.setInt(8, g.getIsdel());
ptmt.setInt(9, g.getId());
ptmt.execute();//最后调用execute方法来执行，非常有条理
}

代码和add差不多，只是改了sql语句而已。

通过Connection conn = dbutil.getConnection();获得链接。

1
2
3
4
5
String sql = ""+
" update myfriend "+
" set user_name = ?,sex = ?,age = ?,birthday = ?,email = ?,mobile = ?, "+
" update_user = ?,update_date = current_date(),isdel = ? "+
" where id = ? ";

编写sql语句。

这里要特别注意sql语句要加空格，因为查询没有括号()，需要空格来分开语句。

1
2
3
4
5
6
7
8
9
10
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1, g.getUser_name());
ptmt.setInt(2, g.getSex());
ptmt.setInt(3, g.getAge());
ptmt.setDate(4, new Date(g.getBirthday().getTime()));
ptmt.setString(5, g.getEmail());
ptmt.setString(6, g.getMobile());
ptmt.setString(7, g.getUpdate_user());
ptmt.setInt(8, g.getIsdel());
ptmt.setInt(9, g.getId());

使用set方法传参。

（2）删除方法

1
2
3
4
5
6
7
8
9
10
11
12
13
public void delGoddess(Integer id) throws SQLException  {
Connection conn = dbutil.getConnection();//获得链接
String sql = ""+
            " delete from myfriend "+
    " where id = ? ";//编写sql语句
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译
ptmt.setInt(1, id);
ptmt.execute();//最后调用execute方法来执行，非常有条理
 
}

同上，这里我们根据id删除数据，所以只需要传入id即可。

（3）查询（重点）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public Goddness get(Integer id) throws SQLException // 不知道为什么可以这样定义
{
Goddness g = null;
Connection conn = dbutil.getConnection();//获得链接
String sql = ""+
            " select * from myfriend "+
    " where id = ? ";//编写sql语句
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);//预编译
ptmt.setInt(1, id);
ResultSet rs = ptmt.executeQuery();//查询这里不再用execute操作，execute针对数据的修改，增删改
//查询的话用executeQuery()查询操作
//因为要输出查询结果，所以要和上面一样，写一个输出的转换
while(rs.next()) //遍历结果集
{
g = new Goddness();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
g.setSex(rs.getInt("sex"));
g.setBirthday(rs.getDate("birthday"));
g.setEmail(rs.getString("email"));
g.setMobile(rs.getString("mobile"));
g.setCreate_date(rs.getDate("create_date"));
g.setCreate_user(rs.getString("create_user"));
g.setUpdate_date(rs.getDate("update_date"));
g.setUpdate_user(rs.getString("update_user"));
g.setIsdel(rs.getInt("isdel"));
}
return g;
}

ResultSet rs = ptmt.executeQuery();

查询这里不再用execute操作，execute针对数据的修改，增删改。查询用executeQuery()。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 遍历结果集
while(rs.next()) {
   g = new Goddness();
   g.setId(rs.getInt("id"));
   g.setUser_name(rs.getString("user_name"));
   g.setAge(rs.getInt("age"));
   g.setSex(rs.getInt("sex"));
   g.setBirthday(rs.getDate("birthday"));
   g.setEmail(rs.getString("email"));
   g.setMobile(rs.getString("mobile"));
   g.setCreate_date(rs.getDate("create_date"));
   g.setCreate_user(rs.getString("create_user"));
   g.setUpdate_date(rs.getDate("update_date"));
   g.setUpdate_user(rs.getString("update_user"));
   g.setIsdel(rs.getInt("isdel"));
  }
return g;

这里要把值传出去，通过rs.getxxx方法获得值，然后给到g.的godness.setxxx方法，让godness.id = 表里的id，注意返回的应该是一个对象，所以要写g = new Godness()。

之后在godness里写一个tostring方法用来传递值：

1
2
3
4
5
6
public String toString() {
return "Goddness [user_name=" + user_name + ", sex=" + sex + ", age=" + age + ", birthday=" + birthday
+ ", email=" + email + ", mobile=" + mobile + ", create_user=" + create_user + ", update_user="
+ update_user + ", create_date=" + create_date + ", update_date=" + update_date + ", isdel=" + isdel
+ ", id=" + id + "]";
}

最后通过：

1
2
Goddness g2 = g.get(2);
System.out.println(g2.toString());

就可以打印查询结果。

二、总结

记录一下。

Post Views: 74