JDBC开发步骤:
1、加载驱动Class.forName("");
2、获得连接对象Connection
3、写sql语句
4、创建Statement(一艘船)
5、执行sql语句
(1) 更新类(更改了表里面数据):delete/update/insert executeUpdate()
返回值:int,表示你影响的行数
(2)查询(没有改变表里面数据): select executeQuery()
返回值:结果集ResultSet
6、关闭连接
mysql_connection jar包
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(预编译) vs Statement:
1、语法不同:
PreparedStatement可以使用预编译的sql,只需要发送一次sql语句,后面只要发送参数即可,公用一个sql语句。
Statement只能使用静态的sql。
delete from student where id=1;
2、效率不同:PreparedStatement使用了sql缓冲区,效率要比Statement高。
3、安全性不同:PreparedStatement可以有效的防止sql注入,而Statement不能防止sql注入
代码实现:
JDBCUtil.java
package com.situ.day12;
import java.sql.*;
public class JDBCUtil {
private JDBCUtil(){
}
//1.加载驱动Class.forName("");
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet){
try {
if(resultSet!=null) resultSet.close();
if(statement!=null) statement.close();
if(connection!=null) connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
//2.获得连接对象Connection
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/" +
"java?useUnicode=true&characterEncoding=UTF-8", "root", "root");
return connection;
}
}
package com.situ.day12;
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;
public class JDBCDemo {
@Test
public void test1() {
int selectId = 5;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
// //1.加载驱动Class.forName("");
// Class.forName("com.mysql.jdbc.Driver");
// //2.获得连接对象Connection
// connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/" +
// "java?useUnicode=true&characterEncoding=UTF-8", "root", "root");
//3.写sql语句
connection = JDBCUtil.getConnection();
String sql = "select id,name,age,gender from student ";
//4.创建Statement(一艘船)
// Statement statement = connection.createStatement();
statement = connection.prepareStatement(sql);
//天上?占位符的值
// statement.setInt(1,selectId);
System.out.println(statement);
//5.执行sql语句
//(1)更新类(更改了表里面数据):delete/update/insert/exeucteUpdate()
// ResultSet resultSet = statement.executeQuery(sql);
resultSet = statement.executeQuery();
ArrayList<Student> 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();
}finally{
//先打开的后关闭
// if(resultSet != null){
// try {
// resultSet.close();
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
// }
// try {
// statement.close();
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
// try {
// connection.close();
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
// try {
// if(resultSet!=null) resultSet.close();
// if(statement!=null) statement.close();
// if(connection!=null) connection.close();
// } catch (SQLException ex) {
// ex.printStackTrace();
// }
JDBCUtil.close(connection,statement,resultSet);
}
}
@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,4);
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 testInsert(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtil.getConnection();
String sql ="insert into student (id,name,age,gender)value(?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,7);
preparedStatement.setString(2,"张哥");
preparedStatement.setInt(3,15);
preparedStatement.setString(4,"男");
System.out.println(preparedStatement);
int count = preparedStatement.executeUpdate();
System.out.println("count"+count);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JDBCUtil.close(connection,preparedStatement,null);
}
}
}
数据库:
create table student(
id int primary key AUTO_increment,
name char(10),
age int,
gender char(10)
);