与数据库连接:JDBC
@
目录
JDBC概述
JDBC（Java DataBase Connectivity）java数据库连接
是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它由一组用Java语言编写的类和接口组成。
有了JDBC,java开发人员只需要编写一次程序,就可以访问不同的数据库.
JDBC搭建
jdbc搭建步骤
1.导入数据库开发商提供的数据库连接实现类的jar文件
2.加载驱动
3.建立与数据库的连接
4.向数据库发送信息
5.执行完毕后,关闭与数据库的连接
package javajdbc;
import java.sql.*;
import java.util.Properties;
import java.util.logging.Logger;
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) {
//java反射机制,动态加载类
/* Class.forName("com.mysql.cj.jdbc.Driver");
DriverManager.registerDriver(new Driver());*/
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
try {
//建立与数据库的连接,返回connection对象
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
//statement用于向数据库发送sql语句
Statement st = connection.createStatement();
st.executeUpdate("insert into grade (name) value('四年级')");
st.close();
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
//与数据库键连接
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
try {
//获取statement
Statement st = connection.createStatement();
//executeUpdate发送sql到数据库,返回所操作的行数
//int res = st.executeUpdate("create table test(id int)");//可执行ddl,dml
//int res = st.executeUpdate("insert into test(id) value (1)");//可执行ddl,dml
int res = st.executeUpdate("delete from test where id");//可执行ddl,dml
System.out.println(res);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
PreparedStatement和Statement
1.代码的可读性和可维护性.
Statement：
1. 静态SQL执行，没操作一次向数据库编译发送一次，效率低
2.将变量以字符串形式拼接进去，书写麻烦
3.不能防止sql注入，安全性低
PreparedStatement：
1.预先将sql语句编译到PreparedStatement对象中可以重复使用，效率高
2.使用set方法向占位符处进行设置值，书写方便
3.可以防止SQL注入，安全性高
statement
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo3 {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
//与数据库键连接
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
try {
//获取statement
Statement st = connection.createStatement();
String name = "jim";
String sex = "女";
String birthday = "1999-05-23";
int grade = 4;
int score = 90;
String phone = "15333333333";
String adress = "西安";
st.executeUpdate("insert into student(name,sex,birthday,grade,score,phone,address,reg_time) "
+"values('"+name+"','"+sex+"','"+birthday+"',"+grade+","+score+",'"+phone+"','"+adress+"',new DATE ())");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
PreparedStatement
import java.sql.*;
import java.util.Date;
public class Demo4 {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
//与数据库键连接
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
try {
//获取statement
Statement st = connection.createStatement();
String name = "tom";
String sex = "女";
String birthday = "1999-05-23";
int grade = 4;
int score = 90;
String phone = "15333333333";
String adress = "西安";
String sql = "insert into student(name,sex,birthday,grade,score,phone,address,reg_time) "
+ "values(?,?,?,?,?,?,?,?)";//?表示占位符，表示一个值的位置
//预先将sql语句编译到PreparedStatement对象中
PreparedStatement ps = connection.prepareStatement(sql);
//向sql中占位符进行赋值操作
ps.setString(1, name);
ps.setString(2, sex);
ps.setString(3, birthday);
ps.setInt(4, grade);
ps.setInt(5, score);
ps.setString(6, phone);
ps.setString(7, adress);
ps.setObject(8, new Date());
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
2、最重要的一点是极大地提高了安全性. 防止sql注入
statement
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
//与数据库键连接
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
try {
//获取statement
Statement st = connection.createStatement();
String name = "'陕西省' or 1=1";//暴力的sql注入，直接将值拼接到字符串中的，没有做任何处理，不能防止sql注入
int res = st.executeUpdate("delete from t_area where name = " + name);
System.out.println(res);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
PreparedStatement
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
//与数据库键连接
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
try {
//获取statement
Statement st = connection.createStatement();
String name = "'陕西省' or 1=1";//暴力的sql注入，直接将值拼接到字符串中的，没有做任何处理，不能防止sql注入
PreparedStatement ps = connection.prepareStatement("delete from t_area where name = ?");
ps.setString(1, name);
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
结果集处理
这里是引用PreparedStatement和Statement中的executeQuery()方法中会返回一个ResultSet对象,查询结果就封装在此对象中.
使用ResultSet中的next()方法获得下一行数据
使用getXXX(String name)方法获得值
创建Student类
package com.ff.javajdbc;
import java.util.Date;
public class Student {
private int num;
private String name;
private String sex;
private Date birthday;
private int grade;
private String phone;
private String address;
private Date reg_time;
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getReg_time() {
return reg_time;
}
public void setReg_time(Date reg_time) {
this.reg_time = reg_time;
}
@Override
public String toString() {
return "Student{" +
"num=" + num +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", grade=" + grade +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", reg_time=" + reg_time +
'}';
}
}
ResultSet中的next()方法获得下一行数据
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
//与数据库键连接
try {
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
int num = 3 ;
String sql = "select num,name,sex,birthday,grade,phone,address,reg_time from student where num = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1,num);
//executeQuery()用于执行查询语句，将查询到的结果封装到ResultSet对象中
ResultSet rs = ps.executeQuery();
//可以将ResultSet中的对象，获取并转化为我们自己定义对象中
Student student = new Student();
//next() 判断结果集中是否还包含数据，如果包含返回true，将指针指向下一个数据
while(rs.next()){
//将结果集中获得数据,设置到student对象
/* 1对应的查询数据的第一列
student.setNum(rs.getInt(1));
student.setName(rs.getString(2));*/
student.setNum(rs.getInt("num"));
student.setName(rs.getString("name"));
student.setSex(rs.getString("sex"));
student.setBirthday(rs.getDate("birthday"));
student.setGrade(rs.getInt("grade"));
student.setPhone(rs.getString("phone"));
student.setAddress(rs.getString("address"));
student.setReg_time(rs.getTimestamp("reg_time"));
}
System.out.println(student);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
获得一个表中的所有数据
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
//与数据库键连接
try {
Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
String sql = "select num,name,sex,birthday,grade,phone,address,reg_time from student ";
PreparedStatement ps = connection.prepareStatement(sql);
//executeQuery()用于执行查询语句，将查询到的结果封装到ResultSet对象中
ResultSet rs = ps.executeQuery();
ArrayList<Student> arrayList = new ArrayList<>();
//可以将ResultSet中的对象，获取并转化为我们自己定义对象中
//next() 判断结果集中是否还包含数据，如果包含返回true，将指针指向下一个数据
while (rs.next()) {
Student student = new Student();
student.setNum(rs.getInt("num"));
student.setName(rs.getString("name"));
student.setSex(rs.getString("sex"));
student.setBirthday(rs.getDate("birthday"));
student.setGrade(rs.getInt("grade"));
student.setPhone(rs.getString("phone"));
student.setAddress(rs.getString("address"));
student.setReg_time(rs.getTimestamp("reg_time"));
arrayList.add(student);
}
for (Student student: arrayList) {
System.out.println(student);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
