可以货代从哪些网站开发客户,小学校园网站建设简介,做支付宝二维码网站,wordpress 二次开发【有道云笔记】十二 3.28 JDBC https://note.youdao.com/s/HsgmqRMw
一、JDBC简介 面向接口编程
在JDBC里面Java这个公司只是提供了一套接口Connection、Statement、ResultSet#xff0c;每个数据库厂商实现了这套接口#xff0c;例如MySql公司实现了#xff1a;MySql驱动…【有道云笔记】十二 3.28 JDBC https://note.youdao.com/s/HsgmqRMw
一、JDBC简介 面向接口编程
在JDBC里面Java这个公司只是提供了一套接口Connection、Statement、ResultSet每个数据库厂商实现了这套接口例如MySql公司实现了MySql驱动程序里面实现了这套接口Java程序员只要调用实现了这些方法就可以实现对 MySql数据库的增删改查。
ConnectIon connection 获得连接 二、JDBC开发步骤
1、加载驱动Class.forName(); 2、获得连接对象Connection
3、写sql语句
4、创建Statement(一艘船)
5、执行sql语句
(1) 更新类更改了表里面数据delete/update/insert executeUpdate()
返回值int表示你影响的行数
(2)查询没有改变表里面数据: select executeQuery()
返回值结果集ResultSet
6、关闭连接 //1、加载驱动Class.forName(); Class.forName(com.mysql.cj.jdbc.Driver); //2、获得连接对象Connection Connection connection DriverManager.getConnection(jdbc:mysql://localhost:3306/java230701?useSSLfalseuseUnicodetruecharacterEncodingutf8serverTimezoneGMT%2b8, root, 1234); Test public void test1() { try { //1、加载驱动Class.forName(); Class.forName(com.mysql.cj.jdbc.Driver); //2、获得连接对象Connection Connection connection DriverManager.getConnection(jdbc:mysql://localhost:3306/java230701?useSSLfalseuseUnicodetruecharacterEncodingutf8serverTimezoneGMT%2b8, root, 1234); //3、写sql语句 String sql SELECT id,name,age,gender FROM student; //4、创建Statement(一艘船) Statement statement connection.createStatement(); //5、执行sql语句 // (1) 更新类更改了表里面数据delete/update/insert executeUpdate() // 返回值int表示你影响的行数 // (2)查询没有改变表里面数据: select executeQuery() // 返回值结果集ResultSet ResultSet resultSet statement.executeQuery(sql); ListStudent list new ArrayList(); while (resultSet.next()) {//判断下一个有没有如果返回true而且指向下一个没有返回false //每遍历一行就封装一个学生对象 int id resultSet.getInt(id); String name resultSet.getString(name); int age resultSet.getInt(age); String gender resultSet.getString(gender); Student student new Student(id, name, age, gender); list.add(student); } for (Student student : list) { System.out.println(student); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { //6、关闭连接 } } Test public void test2() { Connection connection null; Statement statement null; ResultSet resultSet null; try { Class.forName(com.mysql.cj.jdbc.Driver); connection DriverManager.getConnection(jdbc:mysql://localhost:3306/java230701?useSSLfalseuseUnicodetruecharacterEncodingutf8serverTimezoneGMT%2b8, root, 1234); String sql SELECT id,name,age,gender FROM student; statement connection.createStatement(); resultSet statement.executeQuery(sql); ListStudent list new ArrayList(); while (resultSet.next()) {//判断下一个有没有如果返回true而且指向下一个没有返回false int id resultSet.getInt(id); String name resultSet.getString(name); int age resultSet.getInt(age); String gender resultSet.getString(gender); Student student new Student(id, name, age, gender); list.add(student); } for (Student student : list) { System.out.println(student); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { if (resultSet ! null) { try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (statement ! null) { try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (connection ! null) { try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } Test public void testPreparedStatement() { Connection connection null; PreparedStatement preparedStatement null; ResultSet resultSet null; try { connection JDBCUtil.getConnection(); String sql SELECT id,name,age,gender FROM student; //预编译 preparedStatement connection.prepareStatement(sql); resultSet preparedStatement.executeQuery(); ListStudent list new ArrayList(); while (resultSet.next()) {//判断下一个有没有如果返回true而且指向下一个没有返回false int id resultSet.getInt(id); String name resultSet.getString(name); int age resultSet.getInt(age); String gender resultSet.getString(gender); Student student new Student(id, name, age, gender); list.add(student); } for (Student student : list) { System.out.println(student); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JDBCUtil.close(connection, preparedStatement, resultSet); } } Test public void testInsert() { Connection connection null; PreparedStatement preparedStatement null; try { connection JDBCUtil.getConnection(); //? 占位符 String sql insert into student(name,age,gender) values(?,?,?); preparedStatement connection.prepareStatement(sql); preparedStatement.setString(1, 张三); preparedStatement.setInt(2, 23); preparedStatement.setString(3, 女); System.out.println(preparedStatement); int count preparedStatement.executeUpdate(); System.out.println(count: count); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JDBCUtil.close(connection, preparedStatement, null); } } Test public void testDelete() { Connection connection null; PreparedStatement preparedStatement null; try { connection JDBCUtil.getConnection(); String sql delete from student where id?; preparedStatement connection.prepareStatement(sql); preparedStatement.setInt(1, 10); System.out.println(preparedStatement); int count preparedStatement.executeUpdate(); System.out.println(count: count); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JDBCUtil.close(connection, preparedStatement, null); } } Test public void testUpdate() { Connection connection null; PreparedStatement preparedStatement null; try { connection JDBCUtil.getConnection(); String sql update student set name?,age?,gender? where id?; preparedStatement connection.prepareStatement(sql); preparedStatement.setString(1, 小张); preparedStatement.setInt(2, 23); preparedStatement.setString(3, 男); preparedStatement.setInt(4, 9); System.out.println(preparedStatement); int count preparedStatement.executeUpdate(); System.out.println(count: count); } catch (SQLException throwables) { throwables.printStackTrace(); } } Test public void testLike() { Connection connection null; PreparedStatement preparedStatement null; ResultSet resultSet null; try { connection JDBCUtil.getConnection(); String sql select id,name,age,gender from student where name like ?; preparedStatement connection.prepareStatement(sql); preparedStatement.setString(1, %张%); System.out.println(preparedStatement); resultSet preparedStatement.executeQuery(); ListStudent list new ArrayList(); while (resultSet.next()) { int id resultSet.getInt(id); String name resultSet.getString(name); int age resultSet.getInt(age); String gender resultSet.getString(gender); Student student new Student(id, name, age, gender); list.add(student); } for (Student student : list) { System.out.println(student); } } catch (SQLException throwables) { throwables.printStackTrace(); } } Test public void test122() { Connection connection null; PreparedStatement preparedStatement null; String sql1 UPDATE account SET moneymoney-1000 WHERE name张三; String sql2 UPDATE account SET moneymoney1000 WHERE name李四; try { connection JDBCUtil.getConnection(); // 为false表示禁用自动提交默认情况是true connection.setAutoCommit(false); preparedStatement connection.prepareStatement(sql1); System.out.println(preparedStatement); preparedStatement.executeUpdate(); // ArithmeticException: / by zero int i 3 / 0; preparedStatement connection.prepareStatement(sql2); System.out.println(preparedStatement); preparedStatement.executeUpdate(); // setAutoCommit(false)改成false之后不会提交数据库只有调用connection.commit()才提交 connection.commit(); } catch (Exception e) { e.printStackTrace(); try { connection.rollback(); } catch (SQLException throwables) { throwables.printStackTrace(); } } finally { JDBCUtil.close(connection, preparedStatement, null); } }
三、JDBC接口核心的API
|- DriverManager类 驱动管理器类用于管理所有注册的驱动程序
|-registerDriver(driver) : 注册驱动类对象
|-Connection getConnection(url,user,password); 获取连接对象
|- Connection接口 表示java程序和数据库的连接对象。
|- Statement createStatement() 创建Statement对象
|- PreparedStatement prepareStatement(String sql) 创建PreparedStatement对象
|- CallableStatement prepareCall(String sql) 创建CallableStatement对象(调用写好的存储过程)
|- Statement接口 用于执行静态的sql语句
|- int executeUpdate(String sql) 执行静态的更新sql语句
|- ResultSet executeQuery(String sql) 执行的静态的查询sql语句
|-PreparedStatement接口用于执行预编译sql语句
|- int executeUpdate() 执行预编译的更新sql语句
|-ResultSet executeQuery() 执行预编译的查询sql语句
|- ResultSet接口用于封装查询出来的数据
|- boolean next() 将光标移动到下一行
|-getXX() : 获取列的值
四、PreparedStatement预编译)和Statement区别
1、语法不同
PreparedStatement可以使用预编译的sql只需要发送一次sql语句后面只要发送参数即可公用一个sql语句。
Statement只能使用静态的sql。
delete from student where id1;
2、效率不同PreparedStatement使用了sql缓冲区效率要比Statement高。
3、安全性不同PreparedStatement可以有效的防止sql注入而Statement不能防止sql注入。
CREATE TABLE users( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), password VARCHAR(10) ); INSERT INTO users(name, password) VALUES(lisi,123); SELECT * FROM users WHERE 11; -- 11 true SELECT * FROM users WHERE namelisi AND password123; -- zhangsan OR 11 -- y SELECT * FROM users WHERE namezhangsan OR 11 -- y AND password343;
