一、JDBC概述
1.概述
● JDBC(Java DataBase Connectivity) java数据库连接● 是一种用于执行SQL语句的Java API,可以为多种关系型数据库提供统一访问,它 由一组用Java语言编写的类和接口组成● 有了JDBC,java开发人员只需要编写一次程序,就 可以访问不同的数据库
java程序可以连接不同的数据库的,但是不同的数据库具体的连接细节不同, 那么连接数据库的细节应该由谁来实现?
应该由数据库开发商实现
由java设计出一系列连接数据库的接口规范,然后由不同的数据库开发商去实现)
2.JDBC API
供程序员调用的接口与类,集成在 java.sql 包中
DriverManager类作用: 管理各种不同的jDBC驱动
Connection 接口: 与特定数据库的连接Statement 接口 : 执行sqlPreparedStatement接口: 执行sqlResultSet接口 : 接收查询结果
二、JDBC连接数据库步骤
1.在项目中添加jar文件
首先创建一个新的项目,在该项目文件下新建lib文件夹,在该目录下放置需要使用的第三方的jar包
mysql-connector-java-8.0.16.jar 就是由mysql官方开发实现的,具体连接数据库的功能代码, 把该jar包复制粘贴到lib目录下方
然后右键选择Add as Library
选择OK
这样就算添加好啦!
接着在src文件夹目录下新建包,新建类,以Demo1为例
2.加载驱动类 Class.forName("com.mysql.cj.jdbc.Driver")
这需要初始化驱动程序,这样就可以打开与数据库的通信信道方式一:Class.forName(“com.mysql.cj.jdbc.Driver”); //反射实现或者方式二:DriverManager.registerDriver(new Driver());
3.建立与数据库的连接,获得连接对象
这需要使用DriverManager.getConnection()方法 来创建一个 Connection对象,它代表一个物理连接的数据库Connection conn = DriverManager.getConnection(URL,USER,PASS);URL: jdbc:mysql://ip(127.0.0.1): 端口 (3306)/ 数据库 名?serverTimezone=Asia/ShanghaiUSER: 用户名 (root)PASS: 密码
4.发送sql
两种方式,这里先以Statement为例
5.关闭与数据库连接
每次操作完成后关闭所有与数据库交互的通道st.close();rs.close();conn.close();ps.close();
!!! 举例
方式一:Statement向数据库发送sql
①新增保存学生信息
package comffyc.jdbc;
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) {
Demo2 demo2 = new Demo2();
try {
demo2.save("丽丽","女", "2003-02-01", "13892345678","陕西渭南",3);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// jdbc方式一:新增保存学生
public void save(String name,String gender,String birthday,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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 st = connection.createStatement();
st.executeUpdate("INSERT INTO student(NAME,gender,birthday,phone,address,reg_time,majorid)"+
"VALUES('"+name+"','"+gender+"','"+birthday+"','"+phone+"','"+address+"',now(),"+majorid+")");
//关闭与数据库的连接
st.close();
connection.close();
}
}
运行后在mysql中更新就可以得到刚才存入的学生信息
②修改学生信息
package comffyc.jdbc;
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) {
Demo2 demo2 = new Demo2();
try {
demo2.update(110,"tom1","男", "2004-2-2", "15233334222","汉中1");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// jdbc方式二:修改学生信息
public void update(int num,String name,String gender,String birthday,String phone,String address) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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 st = connection.createStatement();
st.executeUpdate("update student set name ='"+name+"',gender='"+gender+"',birthday = '"+birthday+"',phone = '"+phone+"',reg_time = now() where num = "+ num);
//关闭与数据库的连接
st.close();
connection.close();
}
}
运行后在mysql中更新就可以得到刚才修改的学生信息
③删除学生信息
package comffyc.jdbc;
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) {
Demo2 demo2 = new Demo2();
try {
demo2.delete(111);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// jdbc方式三:删除学生信息
public void delete(String num) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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 st = connection.createStatement();
st.executeUpdate("delete from student where num = "+ num);
//关闭与数据库的连接
st.close();
connection.close();
}
}
可以看到num为111的学生信息被删除了
方式二:PreparedStatement向数据库发送sql
①新增保存学生信息
package comffyc.jdbc;
import java.sql.*;
public class Demo3 {
public static void main(String[] args) {
Demo3 demo3 = new Demo3();
try {
demo3.save("丽丽","男", "2004-2-2", "15233334444","汉中",2);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// jdbc方式二:新增保存学生信息
public void save(String name,String gender,String birthday,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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
Statement st = connection.createStatement();
PreparedStatement ps = connection.prepareStatement("INSERT INTO student(name,gender,birthday,phone,address,reg_time,majorid)VALUES (?,?,?,?,?,now(),?)");
ps.setObject(1, name);
ps.setObject(2, gender);
ps.setObject(3, birthday);
ps.setObject(4, phone);
ps.setObject(5, address);
ps.setObject(6, majorid);
//真正执行
ps.executeUpdate();
//关闭与数据库的连接
ps.close();
connection.close();
}
}
②修改学生信息
package comffyc.jdbc;
import java.sql.*;
public class Demo3 {
public static void main(String[] args) {
Demo3 demo3 = new Demo3();
try {
demo3.update(110,"tom1","男", "2004-2-2", "15233334222","汉中1");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// jdbc方式二:修改学生信息
public void update(int num,String name,String gender,String birthday,String phone,String address) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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("updata student set name=?,gender=?,birthday=?,phone=?,address=? where num=?");
ps.setObject(1, name);
ps.setObject(2, gender);
ps.setObject(3, birthday);
ps.setObject(4, phone);
ps.setObject(5, address);
ps.setObject(6, num);
//真正执行
ps.executeUpdate();
//关闭与数据库的连接
ps.close();
connection.close();
}
}
③删除学生信息
package comffyc.jdbc;
import java.sql.*;
public class Demo3 {
public static void main(String[] args) {
Demo3 demo3 = new Demo3();
try {
demo3.delete("111");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// jdbc方式二:删除学生信息
public void delete(String num) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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
Statement st = connection.createStatement();
PreparedStatement ps = connection.prepareStatement("delete from student where num=?");
ps.setObject(1, num);
//真正执行
ps.executeUpdate();
//关闭与数据库的连接
ps.close();
connection.close();
}
}
三、PreparedStatement和Statement的区别
相同点:都是向数据库发送sql
Satement:将参数直接拼接到sql中,要拼接字符串写起来很麻烦,安全性差 (可以在参数中拼接 or 1=1)
PrepareStatement:先用?占位,然后通过setObject方法赋值,写起来不用拼接字符串,安全可靠的(在赋值时进行检测,可防止sql注入攻击)
1、代码的可读性和可维护性
虽然用PreparedStatement来代替Statement会使代码多出几行,但这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次stmt.executeUpdate("insert into tb_name (col1,col2,col2,col4) values('"+var1+"' , '"+var2+"' , "+var3+" , '"+var4+"')");perstmt = con.prepareStatement("insert into tb_name (col1,col2,col2,col4)values (?,?,?,?)");perstmt.setString(1,var1);perstmt.setString(2,var2);perstmt.setString(3,var3);perstmt.setString(4,var4);perstmt.executeUpdate(); //prestmt是 PreparedStatement 对象实例
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) 方法获得值
!!! 以查询学生个信息为例
①通过学号查询学生信息
新建两个类,以我的为例Demo4和student(用来定义查询结果中的各个属性,方便获取)
package comffyc.jdbc;
import java.util.Date;
public class Student {
private int num;
private String name;
private String gender;
private Date birthday;
private String phone;
private Date regTime;
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
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 Date getRegTime() {
return regTime;
}
public void setRegTime(Date regTime) {
this.regTime = regTime;
}
@Override
public String toString() {
return "Student{" +
"num=" + num +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", birthday=" + birthday +
", phone='" + phone + '\'' +
", regTime=" + regTime +
'}';
}
}
package comffyc.jdbc;
import java.sql.*;
import java.util.ArrayList;
public class Demo4 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Demo4 demo4 = new Demo4();
Student student = demo4.findStudentByNum(2);
System.out.println(student);
}
// 通过学号查询学生信息
public Student findStudentByNum(int num) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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("select num,name,gender,birthday,phone,reg_time from student where num = ? ");
ps.setObject(1,num);
//查询操作
ResultSet rs = ps.executeQuery(); //将查询结果封装到一个ResultSet对象中, 需要将ResultSet对象中的数据封装到对应的对象中
//next() 如果结果集中有数据返回true,否则返回false
Student student = null;
while (rs.next()){
student = new Student();
student.setNum(rs.getInt("num"));
student.setName(rs.getString("name"));
student.setGender(rs.getString("gender"));
student.setBirthday(rs.getDate("birthday"));
student.setPhone(rs.getString("phone"));
student.setRegTime(rs.getTimestamp("reg_time"));
}
//关闭与数据库的连接
rs.close();
ps.close();
connection.close();
return student;
}
}
输入想要查询的学号,就可以查询到该学号学生的信息
②通过性别查询学生信息
因为查询性别出来的结果比较多,不是的唯一的,故添加到集合中
package comffyc.jdbc;
import java.sql.*;
import java.util.ArrayList;
public class Demo4 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Demo4 demo4 = new Demo4();
ArrayList<Student> students = demo4.findStudentByGender("男");
System.out.println(students);
}
// 通过性别查询学生信息
public ArrayList<Student> findStudentByGender(String gender) throws ClassNotFoundException, SQLException {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.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("select num,name,gender,birthday,phone,reg_time from student where gender = ? ");
ps.setObject(1,gender);
ArrayList<Student> students = new ArrayList<>();//创建学生集合,用来存储查询的多个学生对象
//查询操作
ResultSet rs = ps.executeQuery();
while (rs.next()){
//每循环一次,拿到一个学生对象
Student student = new Student();
student.setNum(rs.getInt("num"));
student.setName(rs.getString("name"));
student.setGender(rs.getString("gender"));
student.setBirthday(rs.getDate("birthday"));
student.setPhone(rs.getString("phone"));
student.setRegTime(rs.getTimestamp("reg_time"));
students.add(student);//将学生对象存入到学生集合中
}
//关闭与数据库的连接
rs.close();
ps.close();
connection.close();
return students;
}
}
输入自己想查询的性别,这样就可以查询到该性别的学生信息