1.先在数据库中创建一个学生表
2.创建对应的学生实体类,名字和类型要跟学生表里的一样
package com.briup.student;public class Student {private int id;private String name;private int age;public Student() {}public Student(int id, String name, int age) {this.id = id;this.name = name;this.age = age; }public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";}}
3.创建一个JDBCUtil类,用来连接数据库,和执行对应的SQL语句,或者处理对应的结果集
package com.briup.util;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/*** jdbc封装* @author MECHREVO**/
public class JDBCUtil {//jdbc四要素//驱动：决定连接的是何种类型的数据库private static String driver = "oracle.jdbc.OracleDriver";//url: 决定连接的是哪个主机上的具体的数据库private static String url = "jdbc:oracle:thin:@localhost:1521:XE";private static String user="cyg";private static String password = "cyg";public static Connection getConnection() {Connection conn = null;try {//1.注册驱动Class.forName(driver);//2.建立连接conn = DriverManager.getConnection(url, user, password); } catch (Exception e) {e.printStackTrace();}return conn;}/*** 使用statement对象执行DML语句*/public static void stmt_DML(String sql) {Connection conn = null;Statement stmt = null;try {conn = getConnection();//3.创建Statement对象stmt = conn.createStatement();//4.执行sql语句stmt.execute(sql);//6.关闭资源close(stmt,conn);} catch (Exception e) {e.printStackTrace();}}/*** 使用PreparedStatement执行SQL语句*/public static void ps_DML(String sql,Work work) {try {Connection conn = getConnection();PreparedStatement ps = conn.prepareStatement(sql);//设置ps对象 ps.setXxx()//5.操作结果集work.setPs(ps);ps.execute();//6.关闭资源close(ps, conn);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/*** Statement对象执行select语句* @param sql* @param work*/public static void stmt_select(String sql,Work work) {try {Connection conn = getConnection();Statement stmt =conn.createStatement();ResultSet rs = stmt.executeQuery(sql);work.doResultSet(rs);close(rs, stmt, conn);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/*** PreparedStatement对象执行select语句*/public static void ps_select(String sql,Work work) {try {Connection conn = getConnection();PreparedStatement ps = conn.prepareStatement(sql);work.setPs(ps);ResultSet rs = ps.executeQuery();work.doResultSet(rs);close(rs, ps, conn);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void close(Statement stmt,Connection conn) {close(null, stmt, conn);}public static void close(ResultSet rs,Statement stmt,Connection conn) {if(rs!=null) {try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(stmt!=null) {try {stmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(conn!=null) {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}
}
4.创建一个Main类,里面用来写对应的方法
package com.briup.student;import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;import com.briup.util.JDBCUtil;
import com.briup.util.WorkAdapter;public class Main {//输出菜单的方法showpublic static void show() {System.out.println("************************************");System.out.println(" 1.查询所有学生信息");System.out.println(" 2.新增学生信息");System.out.println(" 3.更新学生信息");System.out.println(" 4.删除学生信息");System.out.println(" 5.查询指定学生信息");System.out.println(" 6.退出系统");System.out.println(" 7.显示菜单");System.out.println("************************************");}//查询所有学生信息的方法select_allpublic static void select_all() {String sql = "select * from a_student order by id";JDBCUtil.stmt_select(sql, new WorkAdapter() {@Overridepublic void doResultSet(ResultSet rs) {try {while(rs.next()) {int id = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);Student stu = new Student(id, name, age);System.out.println(stu);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}});}//添加学生信息的方法add_stupublic static void add_stu() {System.out.println("请按照id:name:age的格式录入学生信息");Scanner sc = new Scanner(System.in);String[] s2;int id;int age;while(true) {String s1 = sc.next();s2= s1.split(":");try {id = Integer.valueOf(s2[0]);age = Integer.valueOf(s2[2]);break;} catch (Exception e) {System.out.println("输入格式错误请重新输入");}}String name = s2[1];if(age<0 || age>200) {System.out.println("年龄输入错误,自动归0");age = 0;}if(id_is(id)==true) { System.out.println("录入失败:ID已经存在");return;}String sql = "insert into a_student values("+id+","+"'"+name+"'"+","+age+")";JDBCUtil.stmt_DML(sql);System.out.println("成功录入id为 "+id+"的学生");}//修改学生信息的方法alter_stupublic static void alter_stu(){System.out.println("请输入要更新信息的学生id");Scanner sc = new Scanner(System.in);int id ;while(true) {try {id = sc.nextInt();break;} catch (Exception e) {System.out.println("输入格式错误,请重新输入");sc.nextLine();}}while(true) {if(id_is(id)==false) { System.out.println("您输入的id不存在，请重新输入");while(true) {try {id = sc.nextInt();break;} catch (Exception e) {System.out.println("输入格式错误,请重新输入");sc.nextLine();}}}else {break;}}System.out.println("请输入要更改的信息格式为name:age");String s1 = sc.next();String[] s2 = s1.split(":");String name = s2[0];int age = Integer.valueOf(s2[1]);if(age<0 || age>200) {System.out.println("年龄输入错误,自动归0");age = 0;}String sql = "update a_student set name='"+name+"',age="+age+" where id="+id;//System.out.println(sql);JDBCUtil.stmt_DML(sql);System.out.println("成功更新id为 "+id+"的学生");}//删除学生信息的方法delete_stupublic static void delete_stu(){System.out.println("请输入要删除信息的学生id");int id;id = int_is();while(true) {if(id_is(id)==false) { System.out.println("您输入的id不存在，请重新输入");id = int_is();}else {break;}}String sql = "delete from a_student where id ="+id;JDBCUtil.stmt_DML(sql);System.out.println("成功删除id为 "+id+"的学生");}//查询指定学生信息的方法select_stupublic static void select_stu() {System.out.println("请输入要查询信息的学生id");Scanner sc = new Scanner(System.in);int id;id = int_is();while(true) {if(id_is(id)==false) { System.out.println("您输入的id不存在，请重新输入");id = int_is();}else {break;}}String sql = "select * from a_student where id = "+id;JDBCUtil.stmt_select(sql, new WorkAdapter() {@Overridepublic void doResultSet(ResultSet rs) {try {while(rs.next()) {int id = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);Student stu = new Student(id, name, age);System.out.println(stu);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}});}//判断学号是否存在的方法id_ispublic static boolean id_is(int id) {List<Integer> list = new ArrayList<>();String sql = "select id from a_student";JDBCUtil.stmt_select(sql, new WorkAdapter() {@Overridepublic void doResultSet(ResultSet rs) {try {while(rs.next()) {int s_id = rs.getInt(1);list.add(s_id);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}});if(list.contains(id)) {return true;}else {return false;}}//判断输入的是否是int类型的方法int_ispublic static int int_is() {Scanner sc = new Scanner(System.in);while(true) {try {int id = sc.nextInt();return id;} catch (Exception e) {System.out.println("输入格式错误,请重新输入");sc.nextLine();}}}
}
5.创建Test类用来调用方法和实现菜单选择
package com.briup.student;import java.util.Scanner;public class Test {public static void main(String[] args) {Scanner sc = new Scanner(System.in);Main.show();while(true) {System.out.println("请输入您要执行操作的序号");int s ;while(true) {try {s = sc.nextInt();break;} catch (Exception e) {System.out.println("输入格式错误,请重新输入");sc.nextLine();}}switch (s) {case 1:Main.select_all();break;case 2:Main.add_stu();break;case 3:Main.alter_stu();break;case 4:Main.delete_stu();break;case 5:Main.select_stu();break;case 6:System.out.println("是否要退出系统(Y/N)");String string = sc.next();if(string.equals("Y")||string.equals("y")) {System.out.println("已经成功退出系统!!!");return;}else if (string.equals("N")||string.equals("n")) {System.out.println("已取消!");break;}else {System.out.println("输入错误自动退回菜单");Main.show();break;}case 7:Main.show();break;default:System.out.println("输入编号错误");break;}}}}
注意:java连接数据库没有对应的jdbc的jar包的需要引入,并在其下面找到自己的dirver(前面的博客中也说过,不知道的可以看看),user和password是自己创建表的所用用户的user和password