目录
PreparedStatement和Statement 区别
JDBC概述
jdbc(Java DataBase Connectivity)java语言连接数据库
本模块中，java提供了一组用来连接数据库的类和接口
java语言开发者，本身没有提供如何具体连接数据库的功能，只是定义了一组java程序连接数据库的访问接口。
1、连接到数据库
2、向数据库发送增删改这一类的sql
3、发送查询sql
java语言只需要定义连接数据库的接口，具体的实现交给了不同的数据库开发商
java只需要定义一套接口，就可以连接不同的接口
JDBC搭建
1、导入mysql开发商提供的jar包
2、注册驱动
两种方式：
1、反射方式加载驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
2 、DriverManager.registerDriver(new Driver());
3、与数据库连接
获得Satement执行sql语句
发送sql语句
关闭与数据库的连接
注解：
url: jdbc:mysql://127.0.0.1:3306：（端口（3306））
/schooldb（数据库名）
?serverTimezone=Asia/Shanghai"
user:用户名（root）
password:密码
Statement：
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//2、注册驱动
DriverManager.registerDriver(new Driver());
//3、建立与数据库的连接
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);//com.mysql.cj.jdbc.ConnectionImpl@25359ed8
//发送sql
Statement statement = connection.createStatement();
System.out.println(statement);//com.mysql.cj.jdbc.StatementImpl@6166e06f
statement.executeUpdate("insert into major(name)value ('数学')");
//关闭与数据库的连接
statement.close();
connection.close();
}
}
运行
PreparedStatement：
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
public class Demo3 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//2、注册驱动
//反射方式加载驱动类
//Class.forName("com.mysql.cj.jdbc.Driver");
DriverManager.registerDriver(new Driver());
//3、建立与数据库的连接
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
//发送sql
//？ 占位符 表示要插入一个参数
PreparedStatement ps = connection.prepareStatement("insert into major(name)value (?)");
//1向第一个占位符的位置插入数据
ps.setObject(1, "智能");
ps.executeUpdate();
//关闭与数据库的连接
ps.close();
connection.close();
}
}
运行
PreparedStatement和Statement
Statement
增
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo2 {
public static void main(String[] args) throws SQLException {
Demo2.insert("元神启","动", "2018-8-23", "12345678910", 1.4);
}
public static void insert(String name,String gender,String birthday,String phone,double height) throws SQLException {
//注册驱动
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
//建立与数据库的连接
Connection connection = DriverManager.getConnection(url, user, password);
//发送sql
Statement statement = connection.createStatement();
statement.executeUpdate("insert into student(name,gender,birthday,phone,height)value('"+name+"','"+gender+"','"+birthday+"','"+phone+"',"+height+")");
//关闭与数据库的连接
statement.close();
connection.close();
}
}
运行
删
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo2 {
public static void main(String[] args) throws SQLException {
Demo2.delete(6);
}
public static void delete(int number) throws SQLException {
//注册驱动
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
//建立与数据库的连接
Connection connection = DriverManager.getConnection(url, user, password);
//发送sql
Statement statement = connection.createStatement();
//直接将字符串拼接到sql中，不能防止sql注入攻击"or 1=1"
statement.executeUpdate("delete from student where number = "+number);
//关闭与数据库的连接
statement.close();
connection.close();
}
}
运行
改
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo2 {
public static void main(String[] args) throws SQLException {
Demo2.update(3,"爱丽西娅","女", "2024-4-14", "123456789", 1.72);
}
public static void update(int number,String name,String gender,String birthday,String phone,double height) throws SQLException {
//注册驱动
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
//建立与数据库的连接
Connection connection = DriverManager.getConnection(url, user, password);
//发送sql
Statement statement = connection.createStatement();
statement.executeUpdate("update student set name='"+name+"',gender='"+gender+"',birthday='"+birthday+"',phone='"+phone+"',height="+height+" where number ="+number);
//关闭与数据库的连接
statement.close();
connection.close();
}
}
运行
PreparedStatement
增
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
public class Demo4 {
public static void main(String[] args) throws SQLException {
Demo4.insert("小姨","鹤", "2018-8-23", "123456789", 1.4);
}
public static void insert(String name,String gender,String birthday,String phone,double height) throws SQLException {
//注册驱动
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
//建立与数据库的连接
Connection connection = DriverManager.getConnection(url, user, password);
//发送sql
PreparedStatement ps = connection.prepareStatement("insert into student(name,gender,birthday,phone,height)value(?,?,?,?,?)");
ps.setObject(1,name);
ps.setObject(2,gender);
ps.setObject(3,birthday);
ps.setObject(4,phone);
ps.setObject(5,height);
ps.executeUpdate();
//关闭与数据库的连接
ps.close();
connection.close();
}
}
运行
删
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
public class Demo4 {
public static void main(String[] args) throws SQLException {
Demo4.delete(15);
}
public static void delete(int number) throws SQLException {
//注册驱动
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
//建立与数据库的连接
Connection connection = DriverManager.getConnection(url, user, password);
//发送sql
//封装sql
PreparedStatement ps = connection.prepareStatement("delete from student where number = ?");
//向sql中传参 传参时会进行验证，防止sql注入攻击，更安全
ps.setObject(1, number);
ps.executeUpdate();
//关闭与数据库的连接
ps.close();
connection.close();
}
}
运行
改
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
public class Demo4 {
public static void main(String[] args) throws SQLException {
Demo4.update(3,"申鹤","女", "2024-4-14", "12345678910", 1.72);
}
public static void update(int number,String name,String gender,String birthday,String phone,double height) throws SQLException {
//注册驱动
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
//个人数据库密码
String password = "root";
//建立与数据库的连接
Connection connection = DriverManager.getConnection(url, user, password);
//发送sql
PreparedStatement ps = connection.prepareStatement("update student set name=?,gender=?,birthday=?,phone=?,height=? where number =?");
ps.setObject(1, name);
ps.setObject(2, gender);
ps.setObject(3, birthday);
ps.setObject(4, phone);
ps.setObject(5, height);
ps.setObject(6, number);
ps.executeUpdate();
//关闭与数据库的连接
ps.close();
connection.close();
}
}
运行
PreparedStatement和Statement 区别
1、代码的可读性和可维护性. 虽然用PreparedStatement来代替Statement会使代码多出几行,但这样的代码无 论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次。
2、最重要的一点是极大地提高了安全性.
防止sql注入
Stringsql= “ delete from user where id = ”+num;
如果我们把[or 1=1]作为id传入进来?
delete from tb_name where id = 1 or 1 = 1;
因为‘1’ = ‘1’肯定成立
而如果你使用预编译语句.你传入的任何内容就不会和原来的语句发生任何匹 配的关系.
预编译模式中每个占位符处,只能插入一个值,而会过滤其他语句。
结果集处理
PreparedStatement和Statement中的executeQuery()方法中会返回一 个ResultSet对象,查询结果就封装在此对象中.
使用ResultSet中的next()方法获得下一行数据
使用getXXX(String name)方法获得值
package com.ffyc.jdbcpro;
import java.util.Date;
public class Student {
private int number;
private String name;
private String gender;
private Date birthday;
private String phone;
private double height;
private Date ref_time;
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public double getHeight() {
return height;
}
public void setHeight(double height) {
this.height = height;
}
public Date getRef_time() {
return ref_time;
}
public void setRef_time(Date ref_time) {
this.ref_time = ref_time;
}
@Override
public String toString() {
return "Student{" +
"number=" + number +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", birthday=" + birthday +
", phone='" + phone + '\'' +
", height=" + height +
", ref_time=" + ref_time +
'}';
}
}
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
public class Demo5 {
public static void main(String[] args) throws SQLException {
Student student = Demo5.findStudentByNumber(16);
System.out.println(student);
}
public static Student findStudentByNumber(int number) throws SQLException {
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
PreparedStatement ps = connection.prepareStatement("select number,name,gender,birthday,height,phone,reg_time from student where number = ?");
ps.setObject(1, number);
//执行查询后，mysql将数据封装到一个ResultSet对象中
ResultSet rs = ps.executeQuery();
System.out.println(rs);//com.mysql.cj.jdbc.result.ResultSetImpl@64d2d351
//将ResultSet中的数据包装到自己的对象中，这样用起来更加方便
//结果集中有没有数据 如果有返回true,否则返回false
Student stu = new Student();
while (rs.next()){
stu.setNumber(rs.getInt("number"));
stu.setName(rs.getString("name"));
stu.setGender(rs.getString("gender"));
stu.setBirthday(rs.getDate("birthday"));
stu.setHeight(rs.getDouble("height"));
stu.setPhone(rs.getString("phone"));
stu.setRef_time(rs.getTimestamp("reg_time"));
}
return stu;
}
}
运行
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
import java.util.ArrayList;
public class Demo6 {
public static void main(String[] args) throws SQLException {
ArrayList<Student> students = findStudent();
System.out.println(students);
}
public static ArrayList<Student> findStudent() throws SQLException {
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
PreparedStatement ps = connection.prepareStatement("select number,name,gender,birthday,height,phone,reg_time from student");
ResultSet rs = ps.executeQuery();
//创建一个集合对象用来封装多个学生对象
ArrayList<Student> students = new ArrayList<>();
while (rs.next()){
//每循环一次，创建一个学生对象
Student stu = new Student();
stu.setNumber(rs.getInt("number"));
stu.setName(rs.getString("name"));
stu.setGender(rs.getString("gender"));
stu.setBirthday(rs.getDate("birthday"));
stu.setHeight(rs.getDouble("height"));
stu.setPhone(rs.getString("phone"));
stu.setRef_time(rs.getTimestamp("reg_time"));
//把当前学生对象返回到集合中
students.add(stu);
}
return students;
}
}
运行
代码
package com.ffyc.jdbcpro;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
public class Demo7 {
public static void main(String[] args) throws SQLException {
int res = checkNum(16);
System.out.println(res);
}
public static int checkNum(int number) throws SQLException {
DriverManager.registerDriver(new Driver());
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
PreparedStatement ps = connection.prepareStatement("select count(*) c from student where number = ?");
ps.setObject(1, number);
ResultSet resultSet = ps.executeQuery();
if(resultSet.next()){
return resultSet.getInt("c");
}else{
return 0;
}
}
}
运行
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
