JDBC
JDBC
JDBC是什么:
Java database connectivity (java数据库 连接)
JDBC能做什么:
- 能让java与数据库建立连接(底层是套接字)
a) 套接字 = ip地址+端口号!
b) 比如:192.168.1.222:8080
- 能让java把sql语句发送给数据库服务器
- 数据库服务器能把执行sql的结果发回给java
- Java还能继续处理数据库服务器发回来的结果。
先骑JDBC这辆自行车!!
- 先为项目导入数据库驱动.
- 加载数据库驱动
- 获取连接
- 准备sql语句
- 获取Statement对象,用来给服务器发送sql语句
- 从Statement对象中获取服务器发回来的结果
- 关闭连接
- 输出结果!
容易出错的地方
|
下面完成了增加 删除 修改操作
下面单独讲解查询,查询与增删改不一样!
问题
|
下面是jdbc连接mysql的代码
体会
- 与连接oracle代码几乎一样
- 只有加载驱动,url的写法不一样
package com.chinasofti.demo2;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;
public class JdbcTest { public static void main(String[] args) throws Exception { // save(); // delete(); // update(); read(); } private static void save() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/test"; Connection conn = DriverManager.getConnection(url,"root","123"); Statement stmt = conn.createStatement(); String sql = "insert into users values(null,'xx')"; stmt.execute(sql); int rowCount = stmt.getUpdateCount(); conn.close(); System.out.println("操作成功,影响" + rowCount + "行"); } private static void delete() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/test"; Connection conn = DriverManager.getConnection(url,"root","123"); Statement stmt = conn.createStatement(); String sql = "delete from users where id = 4"; stmt.execute(sql); int rowCount = stmt.getUpdateCount(); conn.close(); System.out.println("操作成功,影响" + rowCount + "行"); } private static void update() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/test"; Connection conn = DriverManager.getConnection(url,"root","123"); Statement stmt = conn.createStatement(); String sql = "update users set name = 'andy' where id = 3"; stmt.execute(sql); int rowCount = stmt.getUpdateCount(); conn.close(); System.out.println("操作成功,影响" + rowCount + "行"); } private static void read() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/test"; Connection conn = DriverManager.getConnection(url,"root","123"); Statement stmt = conn.createStatement(); String sql = "select * from users"; stmt.execute(sql); ResultSet rs = stmt.getResultSet(); while(rs.next()) { System.out.println(rs.getObject(1) + " " + rs.getObject(2)); } conn.close(); } }
|
通过JDBC解释接口作用:
Statement的execute方法的返回值
- 类型是boolean
- 返回true表示, 有结果集产生
- 返回false表示,没有结果集产生。
Sql注入漏洞演示:
问题:
|
使用PreparedStatement,解决注入漏洞问题!!
问题
|
一套完整的使用PreparedStament完成CRUD的例子
package com.chinasofti.demo3;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;
public class JdbcTest { public static void main(String[] args) throws Exception { // save(); // delete(); // update(); read(); }
private static void save() throws ClassNotFoundException, SQLException { Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@172.3.15.2:1521:orcl"; Connection conn = DriverManager.getConnection(url,"scott","ORCL"); String sql = "insert into dept values(?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, 36); ps.setString(2, "少林"); ps.setString(3, "河南嵩山"); // executeUpdate做2件事, 该方法是专门用于执行 DML操作(insert delete update) // a. 发送sql语句给服务器 // b. 直接返回影响行数 // ps.execute(); // int rowCount = ps.getUpdateCount(); int rowCount = ps.executeUpdate(); conn.close(); System.out.println("操作成功, 影响:" + rowCount + "行"); }
private static void delete() throws ClassNotFoundException, SQLException { Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@172.3.15.2:1521:orcl"; Connection conn = DriverManager.getConnection(url,"scott","ORCL"); String sql = "delete from dept where deptno = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, 36); // a. 发送sql语句给服务器 // b. 直接返回影响行数 // ps.execute(); // int rowCount = ps.getUpdateCount(); int rowCount = ps.executeUpdate(); conn.close(); System.out.println("操作成功, 影响:" + rowCount + "行"); }
private static void update() throws ClassNotFoundException, SQLException { Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@172.3.15.2:1521:orcl"; Connection conn = DriverManager.getConnection(url,"scott","ORCL"); String sql = "update dept set dname=?,loc=? where deptno=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, "武当"); ps.setString(2, "湖北"); ps.setInt(3, 16); // a. 发送sql语句给服务器 // b. 直接返回影响行数 // ps.execute(); // int rowCount = ps.getUpdateCount(); int rowCount = ps.executeUpdate(); conn.close(); System.out.println("操作成功, 影响:" + rowCount + "行"); }
private static void read() throws ClassNotFoundException, SQLException { Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@172.3.15.2:1521:orcl"; Connection conn = DriverManager.getConnection(url,"scott","ORCL"); String sql = "select * from dept"; PreparedStatement ps = conn.prepareStatement(sql); // a. 发送sql语句给服务器 // b. 直接返回影响行数 // ps.execute(); // ResultSet rs = ps.getResultSet(); ResultSet rs = ps.executeQuery(); while(rs.next()) { System.out.println(rs.getInt("deptno") + " " + rs.getString("dname") + " " + rs.getString("loc")); }
conn.close(); } }
|
测试用表
create table users ( id number primary key, name varchar2(20), birthday date, money number(10,2) ) /
|