JDBC
JDBC基本流程如下:
面向接口编程:
在JDBC里面Java这个公司只是提供了一套接口Connection、Statement、ResultSet,每个数据库厂商实现了这套接口,例如MySQL公司实现了:MySQL驱动程序里面实现了这套接口,Java程序员只要调用实现了这些方法就可以实现对MySQL数据库的增删改查。
Connection connection = 获得连接;
JDBC开发步骤:
-
加载驱动Class.forName(“”);
-
获得连接对象Connection
-
写sql语句
-
创建Statement(一艘船)
-
执行sql语句
(1)更新类(更改了表里面的数据):delete/update/insert
executeUpdate()
返回值:int,表示受影响的行数
(2)查询(没有改变表里面的数据):select
executeQuery()
返回值:结果集ResultSet
-
关闭连接
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() : 获取列的值
学生实体类:
public class Student {
private Integer id;
private String name;
private Integer age;
private String gender;
public Student() {
}
public Student(Integer id, String name, Integer age, String gender) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
'}';
}
}
方式一:
public class JdbcDemo {
@Test
public void test01() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//用户信息和url
String url = "jdbc:mysql://localhost:3306/java2207?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
//获得连接
connection = DriverManager.getConnection(url, username, password);
//写sql语句
String sql = "select id,name,age,gender from student";
//创建statement
statement = connection.createStatement();
//结果集
resultSet = statement.executeQuery(sql);
List<Student> list = new ArrayList<>();
while (resultSet.next()) { //如果有下一个返回true,指向下一个
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) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
// 关闭资源、先开后关
if (resultSet != null) { // 先判断的目的是防止空指针异常,因为如果上面的加载驱动出现异常之后直接执行catch和finally的内容,connection、statement、resultset还没来得及赋值
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
方式二:
方式一代码看起来太乱了,应该将连接资源、关闭资源进行单独封装
public class JdbcDemo {
@Test
public void test01() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
String sql = "select id,name,age,gender from student";
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
List<Student> list = new ArrayList<>();
while (resultSet.next()) { //如果有下一个返回true,指向下一个
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 e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.close(connection, statement, resultSet);
}
}
}
public class JDBCUtil {
private static String url = "jdbc:mysql://localhost:3306/java2207?useUnicode=true&characterEncoding=utf8";
private static String username = "root";
private static String password = "123456";
public JDBCUtil() {} //工具类一般将构造方法私有,防止外界创建对象
//驱动不需要重复加载,将其放在静态代码块可以有效的防止其多次加载,因为静态代码块只加载一次
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url, username, password);
return connection;
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
// 关闭资源、先开后关
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
PreparedStatement(预编译) vs Statement:
- 语法不同:
PreparedStatement可以使用预编译的sql,只需要发送一次sql语句,后面只要发送参数即可,公用一个sql语句。
Statement只能使用静态的sql。
delete from student where id=1;
- 效率不同:PreparedStatement使用了sql缓冲区,效率要比Statement高。
- 安全性不同:PreparedStatement可以有效的防止sql注入,而Statement不能防止sql注入。
PreparedStatement是Statement的子类
(1)查找语句:
public void test02() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtil.getConnection();
String sql = "select id,name,age,gender from student";
statement = connection.prepareStatement(sql);
System.out.println(statement); //可以打印执行的sql语句
resultSet = statement.executeQuery();
List<Student> list = new ArrayList<>();
while (resultSet.next()) { //如果有下一个返回true,指向下一个
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 e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection, statement, resultSet);
}
}
(2)插入语句:
public void insertTest() {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "insert into student(name,age,gender) values(?, ?, ?)";
statement = connection.prepareStatement(sql);
statement.setString(1, "张张");
statement.setInt(2, 21);
statement.setString(3, "男");
System.out.println(statement);
int count = statement.executeUpdate(); //返回受影响的行数
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection, statement, null);
}
}
(3)删除语句:
public void testDelete() {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "delete from student where id=?";
statement = connection.prepareStatement(sql);
statement.setInt(1,3);
System.out.println(statement);
int count = statement.executeUpdate();
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection, statement, null);
}
}
(4)更新语句:
public void testUpdate() {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = JDBCUtil.getConnection();
String sql = "update student set gender=? where id=?";
statement = connection.prepareStatement(sql);
statement.setString(1, "女");
statement.setInt(2, 2);
System.out.println(statement);
int count = statement.executeUpdate();
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(connection, statement, null);
}
}
SQL注入问题: