1. Java数据库的连接及操作
1)数据库连接工具类
1 /**
2 * 数据库连接工具类
3 * @author vanguard
4 *
5 */
6 public class JDBCUtils {
7
8 private static Connection conn;
9 private static String driverClass;
10 private static String url;
11 private static String username;
12 private static String password;
13 private static InputStream in;
14
15 static {
16 try {
17 in = JDBCUtils.class.getClassLoader().getResourceAsStream("database.properties");
18 Properties prop = new Properties();
19 prop.load(in);
20 driverClass = prop.getProperty("driverClass");
21 url = prop.getProperty("url");
22 username = prop.getProperty("username");
23 password = prop.getProperty("password");
24 Class.forName(driverClass);
25 conn = DriverManager.getConnection(url, username, password);
26 } catch (IOException e) {
27 System.out.println("加载配置文件失败");
28 e.printStackTrace();
29 } catch (ClassNotFoundException e) {
30 System.out.println("驱动包加载失败");
31 e.printStackTrace();
32 } catch (SQLException e) {
33 System.out.println("数据库连接失败");
34 e.printStackTrace();
35 }
36 }
37
38 /**
39 * 获取连接的方法
40 * @return
41 */
42 public static Connection getConnection() {
43 return conn;
44 }
45
46 /**
47 * 关闭连接 释放资源
48 * @param conn
49 * @param sm
50 * @throws SQLException
51 */
52 public static void close(Connection conn, PreparedStatement ps) throws SQLException {
53 if(conn != null) {
54 conn.close();
55 }
56 if(ps != null) {
57 ps.close();
58 }
59 }
60
61 /**
62 * 关闭所有连接
63 * @param conn
64 * @param st
65 * @param rs
66 * @throws SQLException
67 */
68 public static void close(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException {
69 if(conn != null) {
70 conn.close();
71 }
72 if(ps != null) {
73 ps.close();
74 }
75 if(rs != null) {
76 rs.close();
77 }
78 }
79
80 }
2)JDBC读取数据表sort,每行数据封装到Sort类的对象中很多个Sort类对象,存储到List集合中
1 package com.java.demo02;
2 /**
3 * sort实体类
4 * @author vanguard
5 *
6 */
7 public class Sort {
8 private int sid;
9 private String sname;
10 private String sprice;
11 private String sdesc;
12
13 public Sort() {}
14 public Sort(int sid, String sname, String sprice, String sdesc) {
15 super();
16 this.sid = sid;
17 this.sname = sname;
18 this.sprice = sprice;
19 this.sdesc = sdesc;
20 }
21 public int getSid() {
22 return sid;
23 }
24 public void setSid(int sid) {
25 this.sid = sid;
26 }
27 public String getSname() {
28 return sname;
29 }
30 public void setSname(String sname) {
31 this.sname = sname;
32 }
33 public String getSprice() {
34 return sprice;
35 }
36 public void setSprice(String sprice) {
37 this.sprice = sprice;
38 }
39 public String getSdesc() {
40 return sdesc;
41 }
42 public void setSdesc(String sdesc) {
43 this.sdesc = sdesc;
44 }
45
46 @Override
47 public String toString() {
48 return "Sort [sid=" + sid + ", sname=" + sname + ", sprice=" + sprice
49 + ", sdesc=" + sdesc + "]";
50 }
51 }
1 /**
2 * JDBC读取数据表sort,每行数据封装到Sort类的对象中
3 * 很多个Sort类对象,存储到List集合中
4 * @author vanguard
5 *
6 */
7 public class JDBCDemo {
8 public static void main(String[] args) throws SQLException {
9 //调用数据库链接工具类方法获取连接
10 Connection conn = JDBCUtils.getConnection();
11 //定义sql语句
12 String sql = "SELECT * FROM sort";
13 //获取预编译对象
14 PreparedStatement ps = conn.prepareStatement(sql);
15 //执行查询操作获取结果集
16 ResultSet rs = ps.executeQuery();
17 //定义list集合，存放Sort对象
18 List<Sort> list = new ArrayList<Sort>();
19 //遍历结果集
20 while(rs.next()) {
21 int sid = rs.getInt("sid");
22 String sname = rs.getString("sname");
23 String sprice = rs.getString("sprice");
24 String sdesc = rs.getString("sdesc");
25 Sort s = new Sort(sid, sname, sprice, sdesc);
26 list.add(s);
27 }
28 JDBCUtils.close(conn, ps, rs);
29 //遍历list集合
30 for(Sort s : list) {
31 System.out.println(s.getSid() + " " + s.getSname() + " " + s.getSprice() + " " + s.getSdesc());
32 }
33 }
34 }
2.map中有如下数据(用户名=密码) [liuyan=123456,wangbaoqiang=123321,fangbian=abcd,miejueshitai=123abc321]
在ip为127.0.0.1数据库名为stdb,连接数据库的用户名和密码为:admin和123456中有一个userinfo表相关字段为(id,username,password)
(1)将map中的手机号码取出来打印到控制台上
* 直接使用map集合的keySet()方法获取所有的key组成的Set集合,并遍历
(2)判断map中所有的用户名在userinfo表中是否存在存在则输出"该用户已注册",如果不存在将该用户名及对应的密码存入到userinfo表中
(map中的数据不需要修改)
* 连接到数据库
* 创建表
drop database stdb;
create database stdb;
use stdb;
create table userinfo(
id int(10) primary key auto_increment,
username varchar(200),
password varchar(200)
);
1 /**
2 * 1.map中有如下数据(用户名=密码)
3 [liuyan=123456,wangbaoqiang=123321,fangbian=abcd,miejueshitai=123abc321]
4 在ip为127.0.0.1数据库名为stdb,连接数据库的用户名和密码为:admin和123456中有一个userinfo表相关字段为(id,username,password)
5 (1)将map中的数据取出来打印到控制台上
6 * 直接使用map集合的keySet()方法获取所有的key组成的Set集合,并遍历
7 (2)判断map中所有的用户名在userinfo表中是否存在存在则输出"该用户已注册",如果不存在将该用户名及对应的密码存入到userinfo表中
8 (map中的数据不需要修改)
9 * 连接到数据库
10 * 创建表
11 drop database stdb;
12 create database stdb;
13 use stdb;
14 create table userinfo(
15 id int(10) primary key auto_increment,
16 username varchar(200),
17 password varchar(200)
18 );
19 * @author vanguard
20 *
21 */
22 public class Demo01 {
23 private static Connection conn;
24 private static PreparedStatement ps;
25 private static ResultSet rs;
26 public static void main(String[] args) throws SQLException {
27 //定义Map集合
28 HashMap<String, String> userInfo = new HashMap<String, String>();
29 //Map集合中添加数据
30 userInfo.put("liuyan", "123456");
31 userInfo.put("wangbaoqiang", "123321");
32 userInfo.put("fangbian", "abcd");
33 userInfo.put("miejueshitai", "123abc321");
34 //(1)将map中的数据取出来打印到控制台上
35 printUserInfo(userInfo);
36 Set<String> userSet = userInfo.keySet();
37 //获取数据库连接
38 conn = JDBCUtils.getConnection();
39 //(2)判断map中所有的用户名在userinfo表中是否存在存在则输出"该用户已注册",
40 //如果不存在将该用户名及对应的密码存入到userinfo表中
41 for(String username : userSet) {
42 if(findByUsername(username)) {
43 System.out.println(username + "该用户已存在");
44 } else {
45 registUser(username, userInfo.get(username));
46 System.out.println("成功注册用户" + username);
47 }
48 }
49 JDBCUtils.close(conn, ps, rs);
50 }
51
52 /**
53 * 查询用户名在userinfo表中是否存在
54 * @param username
55 * @return
56 * @throws SQLException
57 */
58 private static boolean findByUsername(String username) throws SQLException {
59 String sql = "SELECT * FROM userinfo where username=?";
60 ps = conn.prepareStatement(sql);
61 ps.setString(1, username);
62 rs = ps.executeQuery();
63 if(rs.next()) {
64 return true;
65 }
66 return false;
67 }
68
69 /**
70 * 注册用户
71 * @param username
72 * @param password
73 * @return
74 * @throws SQLException
75 */
76 private static void registUser(String username, String password) throws SQLException {
77 String sql = "INSERT INTO userinfo(username,password) values(?,?)";
78 ps = conn.prepareStatement(sql);
79 ps.setString(1, username);
80 ps.setString(2, password);
81 ps.executeUpdate();
82 }
83
84 /**
85 * 遍历Map集合，将数据打印到控制台
86 * @param userInfo
87 */
88 private static void printUserInfo(HashMap<String, String> userInfo) {
89 Set<String> userSet = userInfo.keySet();
90 for(String username : userSet) {
91 String password = userInfo.get(username);
92 System.out.println(username + "..." + password);
93 }
94 }
95
96 }
3.一个数据库stdb,用户名为admin 密码为123456 已存在一个表student中有五个学生的信息,姓名,性别,年龄,分数.
id(varchar(20)) name(varchar(20)) sex(varchar(20)) score(int(10))
1 李少荣 女 80
2 邵凯 男 75
3 周强 男 95
4 王晓婷 女 55
5 张秀花 女 68
6 顾会 女 50
7 赵天一 男 32
(1)查询女性,成绩80以上的学生数量
(2)将姓张的男同学的的成绩改为100
(3)查询成绩大于60的女性,显示姓名,性别,成绩
(4)分别统计所有男同学的平均分，所有女同学的平均分及总平均分
(5)按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score),并将分数大于总平均分的学员信息(按照分数从小到大的顺序)(id-name-sex-score)写入到studentInfo.txt文件中(写入格式：id-name-sex-score)
(6)定义查询所有学生的方法public List<Student> getAllStudent(){}
(7)定义根据id查询学生的方法public Student getStudentById(String id){}
(8)定义根据id删除学生的方法public int deleteStudentById(String id){}//注意只有数据库中有才能删除，没有无法删除
(9)定义添加学员的方法public int addStudent(){}//注意只有数据库中没有有才能添加，有无法添加
(10)定义根据id修改学员的信息public int updateStudentById(String id){}//注意只有数据库中有才能修改，没有无法修改
1 public class Student {
2 private int id;
3 private String name;
4 private String sex;
5 private int score;
6
7 public Student() {
8 }
9
10 public Student(int id, String name, String sex, int score) {
11 super();
12 this.id = id;
13 this.name = name;
14 this.sex = sex;
15 this.score = score;
16 }
17
18 public int getId() {
19 return id;
20 }
21
22 public void setId(int id) {
23 this.id = id;
24 }
25
26 public String getName() {
27 return name;
28 }
29
30 public void setName(String name) {
31 this.name = name;
32 }
33
34 public String getSex() {
35 return sex;
36 }
37
38 public void setSex(String sex) {
39 this.sex = sex;
40 }
41
42 public int getScore() {
43 return score;
44 }
45
46 public void setScore(int score) {
47 this.score = score;
48 }
49
50 @Override
51 public String toString() {
52 return "Student [id=" + id + ", name=" + name + ", sex=" + sex
53 + ", score=" + score + "]";
54 }
55
56
57 }
1 /**
2 * 2.一个数据库stdb,用户名为admin 密码为123456 已存在一个表student中有
3 * 五个学生的信息,姓名,性别,年龄,分数.
4 id(varchar(20)) name(varchar(20)) sex(varchar(20)) score(int(10))
5 1 李少荣 女 80
6 2 邵凯 男 75
7 3 周强 男 95
8 4 王晓婷 女 55
9 5 张秀花 女 68
10 6 顾会 女 50
11 7 赵天一 男 32
12 (1)查询女性,成绩80以上的学生数量
13 (2)将姓张的男同学的的成绩改为100
14 (3)查询成绩大于60的女性,显示姓名,性别,成绩
15 (4)分别统计所有男同学的平均分，所有女同学的平均分及总平均分
16 (5)按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score),并将分数大于总平均分的学员信息(按照分数从小到大的顺序)
(id-name-sex-score)写入到studentInfo.txt文件中(写入格式：id-name-sex-score) 17 (6)定义查询所有学生的方法public List<Student> getAllStudent(){}
18 (7)定义根据id查询学生的方法public Student getStudentById(String id){}
19 (8)定义根据id删除学生的方法public int deleteStudentById(String id){}//注意只有数据库中有才能删除，没有无法删除
20 (9)定义添加学员的方法public int addStudent(){}//注意只有数据库中没有有才能添加，有无法添加
21 (10)定义根据id修改学员的信息public int updateStudentById(String id){}//注意只有数据库中有才能修改，没有无法修改
22 * @author vanguard
23 *
24 */
25 public class Demo02 {
26 private static Connection conn;
27 private static PreparedStatement ps;
28 private static ResultSet rs;
29
30 public static void main(String[] args) throws SQLException, IOException {
31 conn = JDBCUtils.getConnection();
32 //(1)查询女性,成绩80以上的学生数量
33 //getWomenGradeCount();
34 //(2)将姓张的男同学的的成绩改为100
35 //updateGrade();
36 //(3)查询成绩大于60的女性,显示姓名,性别,成绩
37 //getWomenGrade();
38 //(4)分别统计所有男同学的平均分，所有女同学的平均分及总平均分
39 //getAvgScore();
40 //(5)按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score),
41 //并将分数大于总平均分的学员信息(按照分数从小到大的顺序)(id-name-sex-score)
42 //写入到studentInfo.txt文件中(写入格式：id-name-sex-score)
43 //saveUserInfoTofile();
44 //定义根据id查询学生
45 // Student s = getStudentById("1");
46 // System.out.println(s);
47 // JDBCUtils.close(conn, ps, rs);
48 //查询所有的学生信息
49 List<Student> students = getAllStudent();
50 for(Student s : students) {
51 System.out.println(s.getId() + " " + s.getName() + " " + s.getSex() + " " + s.getScore());
52 }
53 //根据id修改学生信息
54 int i = updateStudentById("2");
55 if(i != 0) {
56 System.out.println("修改成功！");
57 } else {
58 System.out.println("修改失败！");
59 }
60 }
61 /**
62 * 根据id修改学员的信息
63 * @param id
64 * @return
65 * @throws SQLException
66 */
67 public static int updateStudentById(String id) throws SQLException{
68 String sql = "UPDATE student SET name=?,sex=?,score=? WHERE id=?";
69 ps = conn.prepareStatement(sql);
70 Scanner sc = new Scanner(System.in);
71 System.out.println("请输入要修改学员的姓名：");
72 String name = sc.next();
73 System.out.println("请输入要修改学员的性别：");
74 String sex = sc.next();
75 System.out.println("请输入要修改学员的成绩：");
76 int score = sc.nextInt();
77 ps.setString(1, name);
78 ps.setString(2, sex);
79 ps.setInt(3, score);
80 ps.setString(4, id);
81 int count = ps.executeUpdate();
82 return count;
83 }
84
85 /**
86 * 添加学员
87 * @return
88 * @throws SQLException
89 */
90 public static int addStudent() throws SQLException{
91 String sql = "INSERT INTO student(name,sex,score) VALUES(?,?,?)";
92 ps = conn.prepareStatement(sql);
93 Scanner sc = new Scanner(System.in);
94 System.out.println("请输入要添加学员的姓名：");
95 String name = sc.next();
96 System.out.println("请输入要添加学员的性别：");
97 String sex = sc.next();
98 int score = sc.nextInt();
99 System.out.println("请输入要添加学员的成绩：");
100 ps.setString(1, name);
101 ps.setString(2, sex);
102 ps.setInt(3, score);
103 int count = ps.executeUpdate();
104 return count;
105 }
106 /**
107 *
108 * @param id
109 * @return
110 * @throws SQLException
111 */
112 public static int deleteStudentById(String id) throws SQLException{
113 String sql = "DELETE FROM student WHERE id=?";
114 ps = conn.prepareStatement(sql);
115 int count = ps.executeUpdate();
116 return count;
117 }
118 /**
119 * 根据id查询学生
120 * @param id
121 * @return
122 * @throws SQLException
123 */
124 public static Student getStudentById(String id) throws SQLException{
125 String sql = "SELECT * FROM student WHERE id=?";
126 ps = conn.prepareStatement(sql);
127 ps.setString(1, id);
128 rs = ps.executeQuery();
129 Student s = null;
130 if(rs.next()) {
131 int sid = Integer.parseInt(id);
132 String name = rs.getString("name");
133 String sex = rs.getString("sex");
134 int score = rs.getShort("score");
135 s = new Student(sid, name, sex, score);
136 }
137 return s;
138 }
139 /**
140 * 查询所有的学生信息
141 * @return
142 * @throws SQLException
143 */
144 public static List<Student> getAllStudent() throws SQLException{
145 List<Student> students = new ArrayList<Student>();
146 String sql = "SELECT * FROM student";
147 ps = conn.prepareStatement(sql);
148 rs = ps.executeQuery();
149 while(rs.next()) {
150 int id = rs.getInt("id");
151 String name = rs.getString("name");
152 String sex = rs.getString("sex");
153 int score = rs.getInt("score");
154 Student s = new Student(id, name, sex, score);
155 students.add(s);
156
157 }
158 return students;
159 }
160 /**
161 * 按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score),
162 * 并将分数大于总平均分的学员信息(按照分数从小到大的顺序)(id-name-sex-score)
163 * 写入到studentInfo.txt文件中(写入格式：id-name-sex-score)
164 * @throws SQLException
165 * @throws IOException
166 */
167 public static void saveUserInfoTofile() throws SQLException, IOException {
168 String sql = "SELECT * FROM student WHERE score>(SELECT AVG(score) FROM student) ORDER BY score";
169 ps = conn.prepareStatement(sql);
170 rs = ps.executeQuery();
171 //定义打印流
172 PrintWriter pw = new PrintWriter(new FileWriter("studentInfo.txt"));
173 //遍历结果集
174 while(rs.next()) {
175 String id = rs.getString("id");
176 String name = rs.getString("name");
177 String sex = rs.getString("sex");
178 String score = rs.getString("score");
179 pw.println(id + "-" + name + "-" + sex + "-" + score);
180 }
181 pw.close();
182 }
183
184 /**
185 * (4)分别统计所有男同学的平均分，所有女同学的平均分及总平均分
186 * @throws SQLException
187 */
188 public static void getAvgScore() throws SQLException {
189 String sql = "SELECT AVG(score),sex FROM student GROUP BY sex";
190 ps = conn.prepareStatement(sql);
191 rs = ps.executeQuery();
192 while(rs.next()) {
193 String sex = rs.getString("sex");
194 String avg = rs.getString("AVG(score)");
195 System.out.println(sex + " " + avg);
196 }
197 }
198
199 /**
200 * (3)查询成绩大于60的女性,显示姓名,性别,成绩
201 * @throws SQLException
202 */
203 public static void getWomenGrade() throws SQLException {
204 String sql = "SELECT name,sex,score FROM student where sex=? and score>?";
205 ps = conn.prepareStatement(sql);
206 ps.setString(1, "女");
207 ps.setInt(2, 60);
208 rs = ps.executeQuery();
209 while(rs.next()) {
210 String name = rs.getString("name");
211 String sex = rs.getString("sex");
212 String score = rs.getString("score");
213 System.out.println("姓名：" + name + " 性别：" + sex + " 成绩：" + score);
214 }
215 }
216 /**
217 * //(1)查询女性,成绩80以上的学生数量
218 * @throws SQLException
219 */
220 public static void getWomenGradeCount() throws SQLException {
221 String sql = "SELECT count(*) FROM student WHERE sex=? and score>=?";
222 ps = conn.prepareStatement(sql);
223 ps.setString(1, "女");
224 ps.setInt(2, 80);
225 rs = ps.executeQuery();
226 String count = null;
227 while(rs.next()) {
228 count = rs.getString(1);
229 }
230 System.out.println("女性,成绩80以上的学生数量为" + count);
231 }
232 /**
233 * (2)将姓张的男同学的的成绩改为100
234 * @throws SQLException
235 */
236 public static void updateGrade() throws SQLException {
237 String sql = "UPDATE student SET score=? WHERE name like ? and sex=?";
238 ps = conn.prepareStatement(sql);
239 ps.setInt(1, 100);
240 ps.setString(2, "张%");
241 ps.setString(3, "男");
242 ps.executeUpdate();
243
244 }
245 }