大数据之路week05--day01（JDBC 初识之实现一个系统 实现用户选择增删改查 未优化版本）

要求，实现用户选择增删改查。

给出mysql文件，朋友们可以自己运行导入到自己的数据库中：

/*
Navicat MySQL Data Transfer

Source Server         : mysql
Source Server Version : 50557
Source Host           : 127.0.0.1:3306
Source Database       : school

Target Server Type    : MYSQL
Target Server Version : 50557
File Encoding         : 65001

Date: 2019-12-02 21:20:56
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `sid` int(11) NOT NULL,
  `sname` varchar(10) NOT NULL,
  `gender` varchar(2) NOT NULL,
  `Classid` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  UNIQUE KEY `UK_SNAME` (`sname`),
  KEY `UF_SCLASS` (`Classid`),
  CONSTRAINT `UF_SCLASS` FOREIGN KEY (`Classid`) REFERENCES `t_class` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES ('1604093001', '埼玉', '男', '6');
INSERT INTO `t_student` VALUES ('1604093002', '李宏灿', '男', '5');
INSERT INTO `t_student` VALUES ('1604093005', '娘口三三', '男', '6');
INSERT INTO `t_student` VALUES ('1604093024', '路飞', '男', '5');
INSERT INTO `t_student` VALUES ('1604093025', '齐博源', '男', '6');
INSERT INTO `t_student` VALUES ('1604093026', '比较好', '男', '6');
INSERT INTO `t_student` VALUES ('1604093029', '赵以浩', '男', '6');
INSERT INTO `t_student` VALUES ('1604093030', '大雄', '男', '6');

系统实现代码：

  1 package MySQL测试连接;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.util.Scanner;
  9 
 10 /**
 11  * 
 12  * 创建时间：2019年12月2日 上午10:11:28
 13  * 
 14  * 项目名称：MySQL_Test
 15  * 
 16  * @author WYH
 17  * 
 18  * @version 1.0
 19  * 
 20  * @since JDK 1.8.0
 21  * 
 22  *        文件名称：Test5.java
 23  * 
 24  *        类说明：
 25  *            对数据库操作的增删改查系统1.0版本
 26  */
 27 
 28 public class Test5 {
 29     private static Connection conn = null;
 30     private static PreparedStatement ps = null;
 31     private static ResultSet rs = null;
 32 
 33     // 添加
 34     public static void insertSql(String sql, Connection conn, int sid,
 35             String sname, String gender, int Classid) {
 36         try {
 37             ps = conn.prepareStatement(sql);
 38             ps.setInt(1, sid);
 39             ps.setString(2, sname);
 40             ps.setString(3, gender);
 41             ps.setInt(4, Classid);
 42             int i = ps.executeUpdate();
 43             System.out.println("有" + i + "记录被影响,成功添加一条数据！");
 44         } catch (Exception e) {
 45             System.out.println("添加失败！！");
 46         }
 47 
 48     }
 49 
 50     // 查询
 51     public static void selectSql(String SelectSql, Connection conn) {
 52         try {
 53             ps = conn.prepareStatement(SelectSql);
 54             // ps.setString(1, "男");
 55             rs = ps.executeQuery();
 56             System.out.println("学号\t\t姓名\t性别\t所在班级");
 57             while (rs.next()) {
 58                 System.out.println(rs.getString(1) + "\t" + rs.getString(2)
 59                         + "\t" + rs.getString(3) + "\t" + rs.getString(4));
 60             }
 61         } catch (Exception e) {
 62             System.out.println("查询失败!!");
 63         }
 64 
 65     }
 66 
 67     // 删除
 68     public static void deleteSql(String DeleteSql, Connection conn, int sid1) {
 69         try {
 70             ps = conn.prepareStatement(DeleteSql);
 71             ps.setInt(1, sid1);
 72             int j = ps.executeUpdate();
 73             System.out.println("有" + j + "条数据被影响,成功的删除" + sid1 + "数据");
 74         } catch (Exception e) {
 75             System.out.println("删除失败!!");
 76         }
 77     }
 78 
 79     // 更新
 80     public static void updateSql(String UpdateSql, Connection conn,
 81             String sname2, int sid2) {
 82         try {
 83             ps = conn.prepareStatement(UpdateSql);
 84             ps.setString(1, sname2);
 85             ps.setInt(2, sid2);
 86             int i = ps.executeUpdate();
 87             System.out.println("有" + i + "条数据被影响,成功更新数据。。");
 88         } catch (Exception e) {
 89             System.out.println("更新失败!!");
 90         }
 91     }
 92 
 93     public static void start() {
 94         boolean flag = true;
 95         Scanner sc = new Scanner(System.in);
 96         System.out.println("===========欢迎使用t_student表的操作系统===========");
 97         while (flag) {
 98             System.out.println("1、添加数据\n2、查询数据\n3、更新数据\n4、删除数据\n5、退出");
 99             System.out.print("请选择您需要的操作:");
100             int choice = sc.nextInt();
101 
102             switch (choice) {
103             case 1:
104                 // 添加数据
105                 String sql = "insert into t_student(sid,sname,gender,Classid) values(?,?,?,?)";
106                 System.out.print("请输入您要添加学号:");
107                 int sid = sc.nextInt();
108 
109                 System.out.print("请输入您添加学生的姓名:");
110                 String sname = sc.next();
111 
112                 System.out.print("请输入您添加学生的性别:");
113                 String gender = sc.next();
114 
115                 System.out.print("请输入您添加学生的班级号:");
116                 int Classid = sc.nextInt();
117                 insertSql(sql, conn, sid, sname, gender, Classid);
118 
119                 System.out.println("添加后的数据为:");
120                 selectSql("select * from t_student", conn);
121                 System.out.println();
122                 System.out.println();
123                 break;
124 
125             case 2:
126                 // 查询数据
127                 String SelectSql = "select * from t_student";
128                 selectSql(SelectSql, conn);
129                 System.out.println();
130                 System.out.println();
131                 break;
132 
133             case 3:
134                 // 更新数据
135                 String UpdateSql = "update t_student set sname=? where sid=? ";
136                 System.out.print("请输入要更新姓名的学生学号:");
137                 int sid2 = sc.nextInt();
138 
139                 System.out.print("请输入要更新该学号的新姓名:");
140                 String sname2 = sc.next();
141                 updateSql(UpdateSql, conn, sname2, sid2);
142 
143                 System.out.println("更新后的数据为:");
144                 selectSql("select * from t_student", conn);
145                 System.out.println();
146                 System.out.println();
147                 break;
148 
149             case 4:
150                 // 删除
151                 String DeleteSql = "delete from t_student where sid=?";
152                 System.out.print("请输入要删除的学生学号:");
153                 int sid1 = sc.nextInt();
154                 deleteSql(DeleteSql, conn, sid1);
155 
156                 System.out.println("删除后的数据为:");
157                 selectSql("select * from t_student", conn);
158                 System.out.println();
159                 System.out.println();
160                 break;
161             case 5:
162                 System.out.println("谢谢使用");
163                 flag = false;
164                 break;
165             default:
166                 System.out.println("输入有误。。请重新输入");
167                 System.out.println();
168                 System.out.println();
169                 break;
170             }
171         }
172 
173     }
174 
175     public static void main(String[] args) {
176         try {
177             Class.forName("com.mysql.jdbc.Driver");
178             conn = DriverManager.getConnection(
179                     "jdbc:mysql://127.0.0.1:3306/school", "root", "root");
180             System.out.println(conn);
181             start();
182 
183         } catch (ClassNotFoundException e) {
184             e.printStackTrace();
185         } catch (SQLException e) {
186             e.printStackTrace();
187         } finally {
188             if (rs != null) {
189                 try {
190                     rs.close();
191                 } catch (SQLException e) {
192                     e.printStackTrace();
193                 }
194             }
195             if (ps != null) {
196                 try {
197                     ps.close();
198                 } catch (SQLException e) {
199                     e.printStackTrace();
200                 }
201             }
202             if (conn != null) {
203                 try {
204                     conn.close();
205                 } catch (SQLException e) {
206                     e.printStackTrace();
207                 }
208             }
209         }
210 
211     }
212 
213 }

运行结果：

posted @ 2019-12-02 21:24  Xiaohu_BigData  阅读(152)  评论(0)  编辑  收藏  举报