JAVA基础：jdbc (学习笔记)

基础操作

任何一种jdbc操作，都是由7步完成的

手动加载数据库驱动类{反射}
获得连接对象
写sql语句
获得执行对象
执行sql语句，同时获得结果
处理结果
关闭资源

功能一：添加表里的数据

  public static void main4(String[] args) throws ClassNotFoundException, SQLException {Scanner input = new Scanner(System.in);//1.手动加载数据库驱动类Class.forName("com.mysql.cj.jdbc.Driver");//2.获得连接对象String url = "jdbc:mysql://127.0.0.1:3306/demodvd?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";//localhost也可以写成-----127.0.0.1String username = "root";String password = "root";//密码Connection con = DriverManager.getConnection(url, username, password);System.out.println(con);//3.写sql语句String sql = "insert into dvd values(?,?,0,'',0)";//4.获得执行对象PreparedStatement ps = con.prepareStatement(sql);ps.setString(1, UUID.randomUUID().toString().substring(0, 10));System.out.println("请输入dvd的名称：");ps.setString(2, input.next());//5.执行sql语句，同时获得结果int i = ps.executeUpdate();//6.处理结果if (i > 0) {System.out.println("添加成功！");} else {System.out.println("添加失败！");}//7.关闭资源ps.close();con.close();}

功能二：删除表里的数据

 public static void main2(String[] args) throws ClassNotFoundException, SQLException {//1.手动加载数据库驱动类Class.forName("com.mysql.cj.jdbc.Driver");//2.获得连接对象String url = "jdbc:mysql://127.0.0.1:3306/demodvd?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";//localhost也可以写成-----127.0.0.1String username = "root";String password = "root";//密码Connection con = DriverManager.getConnection(url, username, password);System.out.println(con);//写sql语句String sql = "delete from dvd where id=?";//4.获得执行对象PreparedStatement ps = con.prepareStatement(sql);System.out.println("请输入要删除的DVD编号：");ps.setString(1, new Scanner(System.in).next());//5.执行删除操作，同时获得结果int i = ps.executeUpdate();//用来处理增，删，改操作//6.处理结果if (i > 0) {System.out.println("删除成功！");} else {System.out.println("删除失败！");}//7.关闭资源ps.close();con.close();}

功能三：修改表里的数据

 public static void main5(String[] args) throws ClassNotFoundException, SQLException {Scanner input = new Scanner(System.in);//1.手动加载数据库驱动类Class.forName("com.mysql.cj.jdbc.Driver");//2.获得连接对象String url = "jdbc:mysql://127.0.0.1:3306/demodvd?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";//localhost也可以写成-----127.0.0.1String username = "root";String password = "root";//密码Connection con = DriverManager.getConnection(url, username, password);System.out.println(con);//3.写sql语句String sql = "update dvd set state=1,lendDate=?,lendCount=lendcount+1 where id=?";//4.获得执行对象PreparedStatement ps = con.prepareStatement(sql);System.out.println("请输入要借的图书编号：");ps.setString(2, input.next());System.out.println("请输入借出时间：");ps.setString(1, input.next());//5.执行sql语句，同时获得结果int i = ps.executeUpdate();//6.处理结果if (i > 0) {System.out.println("修改成功！");} else {System.out.println("修改失败！");}//7.关闭资源ps.close();con.close();}

功能四：查询表里的所有数据

 public static void main(String[] args) throws ClassNotFoundException, SQLException {//1.手动加载数据库驱动类{反射}Class.forName("com.mysql.cj.jdbc.Driver");//2.获得连接对象String url = "jdbc:mysql://127.0.0.1:3306/demodvd?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";//localhost也可以写成-----127.0.0.1String username = "root";String password = "root";//密码Connection con = DriverManager.getConnection(url, username, password);System.out.println(con);//3.写sql语句String sql = "select * from dvd";//4.获得执行对象PreparedStatement ps = con.prepareStatement(sql);//5.执行sql语句，同时获得结果ResultSet rs = ps.executeQuery();//用来处理查询操作//ps.executeUpdate();  用来处理增，删，改操作//ps.execute(); 增删改查都可以，但是返回布尔值，用来判断sql的执行//6.处理结果System.out.println("编号\t名称\t状态\t借出时间\t借出次数");//方式一while (rs.next()) {System.out.println(rs.getString(1) + "\t"+ rs.getString(2) + "\t"+ rs.getInt(3) + "\t"+ rs.getString(4) + "\t"+ rs.getInt(5));}//方式二/* while (rs.next()){System.out.println(rs.getString(1)+"\t" +rs.getString("dname")+"\t" +(rs.getInt("state")==1?"已借出":"未借出")+"\t" +rs.getString("lendDate")+"\t" +rs.getInt("lendCount"));}*///7.关闭资源rs.close();ps.close();con.close();}