目录
3. PreparedStatement和Statement
1. JDBC概述
java程序连接数据库 (MySQL、SQL server、Oracle....)，JDBC是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它由一组用Java语言编写的类和接口组成。
问题：不同的数据库实现方式有所不同，那么java要连接不同的数据库，需要写不同实现细节(很麻烦,对于语言开发者来说不太友好)
解决（怎么来解决java程序连接不同的数据库问题呢）：
-
java语言开发者 制定出了一套标准的访问数据库接口 (程序对数据库的操作一般有 (新增,修改,删除 DML), 查询 （ save update delete query） )
-
由不同的数据库开发商来具体实现，实现java提供的标准对数据库操作的接口中的方法（save update delete query）
-
作为程序开发者,只需要学习标准接口的功能即可
JDBC API
供程序员调用的接口与类，集成在 java.sql 包中：
DriverManager 类：管理各种不同的JDBC驱动
Connection 接口 ：实际返回的mysql 的实现类对象
Statement 接口 ：执行sql
PreparedStatement 接口 ：执行sql
ResultSet 接口 ：接收查询结果
mysql 开发商将连接 mysql 具体的实现功能封装到 mysql-connector-java-8.0.16.jar文件中 我们连接 mysql 时,只需要将此包导入到项目中即可
2. JDBC搭建
-
导入mysql驱动包(实现了java标准接口的实现类)
-
注册驱动
-
建立与mysql的连接通道
-
向mysql发送sql句
-
接收返回结果
-
关闭与数据库的连接
import java.sql.*;
public class Demo1 {
public static void main(String[] args) {
/*try {
DriverManager.registerDriver(new Driver());
} catch (SQLException throwables) {
throwables.printStackTrace();
}*/
//1.注册JDBC驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");//以java反射机制来创建此类方法
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = "jdbc:mysql://127.0.0.1:3306/school?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
Connection conn = null;
try {
//建立与MySQL连接
//Connection 接口 实际返回的mysql 的实现类对象
conn = DriverManager.getConnection(url,"root","123456");
//发送sql
Statement st = conn.createStatement();
//更新 st.executeUpdate();
st.executeUpdate("insert into course values(6,'张三','物理',60)");
//查询 st.executeQuery();
//关闭与数据库的链接通道
st.close();
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}3. PreparedStatement和Statement
Statement
public class StatementDemo {
public static void main(String[] args) {
try {
save();//添加操作
delete();//删除操作
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
public static void delete() throws SQLException, ClassNotFoundException {
String id = "7 or 1=1"; //sql攻击 sql注入
Connection connection = null;
Statement st = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/school?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","123456");
st = connection.createStatement();
st.executeUpdate("delete from student where id =" + id);//直接拼接到sql中 不安全
} finally {
if(st!=null){
st.close();
}
if(connection!=null){
connection.close();
}
}
}
public static void save() throws ClassNotFoundException, SQLException {
int num = 107;
String name = "李四";
String gender = "男";
String birthday = "2001-9-1";
float height = 1.78f;
Connection connection = null;
Statement st = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/school?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","123456");
st = connection.createStatement();
st.executeUpdate("insert into student(num,sname,gender,birthday,height)" +
"values("+num+",'"+name+"','"+gender+"','"+birthday+"',"+height+") ");
}finally {
if(st!=null){
st.close();
}
if(connection!=null){
connection.close();
}
}
}
}
Statement
Statement 传参是时直接将变量拼接到字符中,书写比较复杂,不能防止sql注入 st.executeUpdate("insert into student(num,sname,gender,birthday,height)" + "values("+num+",'"+name+"','"+gender+"','"+birthday+"',"+height+") ");
PerparedStatement
import java.sql.*;
public class PerparedStatementDemo {
public static void main(String[] args) throws SQLException {
try {
save();
delete();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
public static void delete() throws SQLException, ClassNotFoundException {
String id = "8 or 1=1";
Connection connection = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/School?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","123456");
ps = connection.prepareStatement("delete from student where id = ?");
ps.setObject(1, id);//在赋值一个?对应一个值,不能传入其他关键字,防止sql注入攻击 安全性好
ps.executeUpdate();
} finally {
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
}
}
public static void save() throws ClassNotFoundException, SQLException {
int sno = 10;
String sname = "张三";
String cname = "数学";
int grade = 80;
Connection connection = null;
PreparedStatement ps = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/School?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","123456");
//使用PreparedStatement发送sql 建议使用的
//预编译sql 在此只是将sql编译到prepareStatement对象中 , ?称为占位符表示此处需要传入一个值
ps = connection.prepareStatement("insert into Course(sno,sname,cname,grade)" +
"values(?,?,?,?) ");
//向sql中的占位符赋值 赋值时可以对值进行检测
ps.setObject(1, sno);
ps.setObject(2, sname);
ps.setObject(3, cname);
ps.setObject(4, grade);
ps.executeUpdate(); //执行sql
}finally {
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
}
}
}PreparedStatement与Statement
-
PreparedStatement输入方式优雅,增强了代码的可读性和可维护性 ps = connection.prepareStatement("insert into student(num,sname,cname,grade)" + "values(?,?,?,?) "); //向sql中的占位符赋值 赋值时可以对值进行检测 ps.setObject(1, num); ps.setObject(2, sname); ps.setObject(3, cname); ps.setObject(4, grade);
-
可以防止SQL注入，提高了代码的可读性
ps.setObject(1, id);//在赋值一个?对应一个值,不能传入其他关键字,防止sql注入攻击 安全性好
4. 结果集处理
使用ResultSet集合
Course类
public class Course {
private int id;
private String sname;
private String cname;
private int grade;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public Course(){
}
public Course(int id, String sname, String cname, int grade) {
this.id = id;
this.sname = sname;
this.cname = cname;
this.grade = grade;
}
@Override
public String toString() {
return "Course{" +
"id=" + id +
", sname='" + sname + '\'' +
", cname='" + cname + '\'' +
", grade=" + grade +
'}';
}
}import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Demo3 {
public static void main(String[] args) {
try {
List<Course> list = query();
System.out.println(list);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static List<Course> query() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");//以java反射机制来创建此类方法
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = "jdbc:mysql://127.0.0.1:3306/school?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<Course> list = new ArrayList<>();
try {
//Connection 接口 实际返回的mysql 的实现类对象
conn = DriverManager.getConnection(url,"root","123456");
ps = conn.prepareStatement("select sno,sname,cname,grade from course");
rs = ps.executeQuery();//执行查询语句,接收返回的结果 在java中存储结果
//循环 ResultSet rs.next()
while (rs.next()){
Course course = new Course();
course.setId(rs.getInt("sno"));
course.setSname(rs.getString("sname"));
course.setCname(rs.getString("cname"));
course.setGrade(rs.getInt("grade"));
list.add(course);
}
return list;
} finally {
if(rs!=null){
rs.close();
}
if(ps!= null){
ps.close();
}
if(conn!= null){
conn.close();
}
}
}
}
7789
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
