JDBC简介

1、JDBC定义Java数据库连接（Java Database Connectivity，简称JDBC）：是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。JDBC原理：由sun提供的一套访问数据库的规范（一组接口）。各个数据库厂商就会遵守这一套规范，提供一套访问自己公司数据库的程序，这套程序就叫数据库驱动。2、主要用途与数据库建立连接发送 SQL 语句处理结果3、JDBC常用接口和类DriverManager类：驱动管理类，主要作用，注册JDBC驱动从而获取连接Connection接口：连接对象，主要作用，建立与数据库的连接Statement接口：SQL编译器，主要作用，向数据库发送SQL语句，返回执行结果ResultSet接口：结果集对象，主要作用，执行查询操作时，接受结果4、mysql驱动下载地址：https://dev.mysql.com/downloads/connector/j/

JDBC的开发步骤

1、导入驱动jar包，用于连接数据库 
2、书写jdbc代码：(1)、注册驱动 (2)、获取访问数据库的连接 (3)、获取执行sql的语句平台(Statement PreparedStatement)即创建SQL编译器 (4)、编写SQL语句(5)、执行sql语句 executeQuery()-->只执行查询操作-->返回结果是结果集ResultSetexecuteUpdate()-->只执行增删改操作-->返回结果是受影响的行数int(6)、处理sql语句执行的结果(7)、释放资源

完整代码

package com.offcn.demo;
import java sql.Connection;
import java. sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDetail {public static void main(String[] args) {Connnection conn = null;Statement stmt = null;ResultSet rs = null;try {//加载驱动Class.forName("com.mysql.cj.jdbc.Driver");//创建连接String url = "jdbc:mysql://localhost:3306/db01?serverTimezone=GMT%2B8&characterEncoding=UTF-8";String username = "root";String passward = "000";conn = DriverManager.getConnection(url,username,passward);//创建SQL编译器stmt = conn.createStatement();//编写SQL语句String sql = "select sname,sage from student";//执行SQL语句并返回结果rs = stmt.executeQuery(sql);//解析结果集while(rs.next()){//读取当前数据//方式一：通过查询到的数据的列名及其数据类型来获取System.out.println(rs.getString("sname")+rs.getInt("sage"));//方式二;通过查询到的数据的列的顺序及其数据类型来获取System.out.println(rs.getString(1)+rs.getInt(2));}}catch(Evception e) {e.printStaclTrace();}finally {try {//释放资源，先开后关if(rs!=null) {rs.close();}if(stmt!=null) {stmt.close();}if(conn!=null) {conn.close();}} catch(SQLException e) {e.printStackTrace();}}}
}

单元测试

导入单元测试的jar包
右键项目-->Build Path-->Configure Build Path-->Libraries-->Add Libraries-->JUnit-->Next-->Apply and Close

SQL注入问题

如出现如下情形：
public void select(String sname) {...String sql = "select * from student where saname = '" + sname + "'";
}
当传入的参数：String sname = "999' or '1'='1";
实际拼接成的SQL语句是：select * from student where sname='999' or '1'='1'
where后的筛选条件恒成立，结果会查询出所有数据，数据安全有隐患
这种SQL注入攻击值对Statement有效，所以改换用PreparedStatement

JDBC封装工具类

在src目录中创建jdbc.properties文件：

后缀properties是一种属性文件。这种文件以key=value格式存储内容。Java中可以使用Properties类来读取这个文件  String value=p.getProperty(key);文件内容如下：
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?serverTimezone=GMT%2B8&characterEncoding=UTF-8
user=root
pwd=000

创建JDBCUtil.java文件：

package com.offcn.demo;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtil {static String driver = "";static String url = "";static String user = "";static String pwd = "";static {try {InputStream in = new FileInputStream("src/jdbc.properties");Properties prop = new Properties();//将流中的数据加载成键值对的类型prop.load(in);//根据key获取valuedriver = prop.getProperty("driver");url = prop.getProperty("url");user = prop.getProperty("user");pwd = prop.getProperty("pwd");} catch (Exception e) {e.printStackTrace();}}//封装获取连接的方法public static Connection getConn() {Connection conn = null;try {Class.forName(driver);conn = DriverManager.getConnection(url, user, pwd);} catch (Exception e) {e.printStackTrace();}return conn;}//封装释放资源的方法public static void closeResources(ResultSet rs,PreparedStatement pstmt,Connection conn) {try {if(rs!=null) {rs.close();}if(pstmt!=null) {pstmt.close();}if(conn!=null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}
}

PreparedStatement实现CRUD操作

先在SQLyog中创建school数据库，库中建表student
CREATE DATABASE school;
USE school;
CREATE TABLE student(
sid INT(6) PRIMARY KEY  AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
sage INT(3),
ssex CHAR(1)  DEFAULT  '男',
semail VARCHAR(20) UNIQUE
);

package com.ujiuye;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;import org.junit.jupiter.api.Test;public class CRUD {//给数据中的表格插入数据的测试方法@Testpublic void makeTable() {insert("大力娃",23,"男","dlw@qq.com");insert("千里眼",22,"男","qly@qq.com");insert("顺风耳",21,"男","sfe@qq.com");insert("喷火娃",20,"男","phw@qq.com");insert("吐水娃",19,"男","tsw@qq.com");insert("隐身娃",18,"男","ysw@qq.com");insert("宝葫芦娃",17,"男","bhlw@qq.com");insert("蛇精",30,"女","sj@qq.com");insert("蝎子精",25,"女","xzj@qq.com");insert("琵琶精",18,"女","ppj@qq.com");}//删除表格中指定学号的学生的信息的测试方法@Testpublic void remove() {int sid = 2;delete(sid);}//对某同学的信息进行修改的测试方法@Testpublic void change() {update(10,"琵琶精",28,"女","11111@qq.com");}//给表格插入数据的方法public void insert(String sname,int sage,String ssex,String semail) {Connection conn = JDBCUtil.getConn();PreparedStatement pstmt = null;String sql = "insert into student(sname,sage,ssex,semail) values(?,?,?,?) ";try {pstmt = conn.prepareStatement(sql);pstmt.setString(1, sname);pstmt.setInt(2, sage);pstmt.setString(3, ssex);pstmt.setString(4, semail);int rows = pstmt.executeUpdate();System.out.println(rows);} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtil.closeResources(null, pstmt, conn);}}//删除表格中指定学号的学生的信息的方法public void delete(int sid) {Connection conn = JDBCUtil.getConn();PreparedStatement pstmt = null;String sql = "delete from student where sid=? ";try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1, sid);int rows = pstmt.executeUpdate();System.out.println(rows);} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtil.closeResources(null, pstmt, conn);}}//对某同学的信息进行修改的方法public void update(int sid,String sname,int sage,String ssex,String semail) {Connection conn = JDBCUtil.getConn();PreparedStatement pstmt = null;String sql = "update student set sname=?,sage=?,ssex=?,semail=? where sid=?";try {pstmt = conn.prepareStatement(sql);pstmt.setString(1, sname);pstmt.setInt(2, sage);pstmt.setString(3, ssex);pstmt.setString(4, semail);pstmt.setInt(5, sid);int rows = pstmt.executeUpdate();System.out.println(rows);} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtil.closeResources(null, pstmt, conn);}}//查询出班上所有的女生的信息，并打印到控制台@Testpublic void select1() {Connection conn = JDBCUtil.getConn();PreparedStatement pstmt = null;ResultSet rs = null;String sql = "select * from student where ssex='女'";try {pstmt = conn.prepareStatement(sql);rs = pstmt.executeQuery();while(rs.next()) {System.out.println("学号："+rs.getInt("sid")+"  姓名："+rs.getString("sname")+"  年龄："+rs.getInt("sage")+"  性别："+rs.getString("ssex")+"  邮箱："+rs.getString("semail"));}} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtil.closeResources(rs, pstmt, conn);}}//查询出班上年龄大于21岁的男同学的姓名，并打印到控制台@Testpublic void select2() {Connection conn = JDBCUtil.getConn();PreparedStatement pstmt = null;ResultSet rs = null;String sql = "select * from student where ssex='男' and sage>21";try {pstmt = conn.prepareStatement(sql);rs = pstmt.executeQuery();while(rs.next()) {System.out.println("学号："+rs.getInt("sid")+"  姓名："+rs.getString("sname")+"  年龄："+rs.getInt("sage")+"  性别："+rs.getString("ssex")+"  邮箱："+rs.getString("semail"));}} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtil.closeResources(rs, pstmt, conn);}}
}