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驱动下载地址：/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.Driverurl=jdbc:mysql://localhost:3306/school?serverTimezone=GMT%2B8&characterEncoding=UTF-8user=rootpwd=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数据库，库中建表studentCREATE 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@");insert("千里眼",22,"男","qly@");insert("顺风耳",21,"男","sfe@");insert("喷火娃",20,"男","phw@");insert("吐水娃",19,"男","tsw@");insert("隐身娃",18,"男","ysw@");insert("宝葫芦娃",17,"男","bhlw@");insert("蛇精",30,"女","sj@");insert("蝎子精",25,"女","xzj@");insert("琵琶精",18,"女","ppj@");}//删除表格中指定学号的学生的信息的测试方法@Testpublic void remove() {int sid = 2;delete(sid);}//对某同学的信息进行修改的测试方法@Testpublic void change() {update(10,"琵琶精",28,"女","11111@");}//给表格插入数据的方法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);}}}
