1 JDBC入门
1.1 JDBC的概念
JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它是由一组用Java语言编写的类和接口组成的。
1.2jdbc的本质
其实就是java官方提供的一套规范(接口)。用于帮助开发人员快速实现不同关系型数据库的连接！
1.3 jdbc快速入门程序
(1)导入jar包（idea建MAVEN项目在pom.xml下）
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>数据库版本号</version>
</dependency>
PS：所有的<dependency> </dependency>在俩个<dependencies></dependencies>之间
(2)注册驱动
Class.forName("com.mysql.jdbc.Driver");
注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。在jar包中，存在一个java.sql.Driver配置文件，文件中指定了com.mysql.jdbc.Driver(3)获取连接
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");(4)获取执行者对象
Statement stat = con.createStatement();(5)执行sql语句，并接收返回结果
String sql = "SELECT * FROM user";
ResultSet rs = stat.executeQuery(sql);(6)处理结果
while(rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
}(7)释放资源
con.close();
stat.close();
rs.close();2 jdbc 功能实现
2.1 DriverManager：驱动管理对象
(1)作用：注册驱动(告诉程序该使用哪一个数据库驱动)
(2)真正写代码能使用：Class.forName("com.mysql.jdbc.Driver");
(3)拓展展示源码：
PS：
返回值：Connection数据库连接对象
参数：
- url：指定连接的路径。语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
- user：用户名
- password：密码
2.2 Connection：数据库连接对象
2.3 Statement：执行sql语句的对象
2.4 ResultSet：结果集对象
3 案例
数 据 准 备
-- 创建db14数据库
CREATE DATABASE db14;
-- 使用db14数据库
USE db14;
-- 创建student表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
birthday DATE -- 学生生日
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'1999-09-23'),(NULL,'李四',24,'1998-08-10'),(NULL,'王五',25,'1996-06-06'),(NULL,'赵六',26,'1994-10-20');/*
实体类
Student类，成员变量对应表中的列
注意：所有的基本数据类型需要使用包装类，以防null值无法赋值
*/
package com.bukaedu02.domain;
import java.util.Date;
public class Student {
private Integer sid;
private String name;
private Integer age;
private Date birthday;
public Student() {
}
public Student(Integer sid, String name, Integer age, Date birthday) {
this.sid = sid;
= name;
this.age = age;
this.birthday = birthday;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
= name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
}3.1 查询全部数据内容
dao 层
public class dao extends Service {
public static ArrayList<Student> findAll() {
ArrayList<Student> list = new ArrayList<>();
Connection con = null;
Statement stat = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.jdbc.Driver"); //C是大写的
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
//后两个为数据库账号和密码
stat = con.createStatement();
String sql = "SELECT * FROM student";
rs = stat.executeQuery(sql);
while(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
Student stu = new Student(sid,name,age,birthday);
list.add(stu);
}
} catch(Exception e) {
e.printStackTrace();
} finally {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
}
service层
public class Service {
public static ArrayList<Student> findAll() {
return dao.findAll();
}
}
controller层
public class Controller {
public static void main(String[] args) {
ArrayList<Student> list = Service.findAll();
for(Student stu : list) {
System.out.println(stu);
}
}
}3.2 条件查询
Dao层
public class Dao {
static Student FindById(Integer cid){
Student student =new Student();
Connection con =null;
Statement stat = null;
ResultSet rs =null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
stat = con.createStatement();
String sql ="SELECT * FROM student WHERE sid ='" + cid + "'"; //字符串的拼接
rs=stat.executeQuery(sql);
while(rs.next()){
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
student.setSid(sid);
student.setName(name);
student.setAge(age);
student.setBirthday(birthday);
}
} catch (Exception e) {
e.printStackTrace();
}
finally{
try {
con.close();
stat.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return student;
}
}
Service层
public class Service {
public static Student FindById(Integer cid) {
return Dao.FindById(cid);
};
}
Controller层
public class Controller {
public static void main(String[] args) {
Student stu =Service.FindById(3);
System.out.println(stu);
}
}3.3 新增数据
Dao 层
public class Dao {
public static int insert (Student stu){
Connection con =null;
Statement stat =null;
int result =0;
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
//获取执行者对象
stat= con.createStatement();
//执行SQL语句并且接收返回的结果集
Date d =stu.getBirthday();
SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyy-MM-dd");
String birthday =simpleDateFormat.format(d);
String sql ="INSERT INTO student VALUES ('" +stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result =stat.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
con.close();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
}
Service层
public class Service {
public static int insert (Student stu){
return Dao.insert(stu);
}
}
Controller层
public class Controller {
public static void main(String[] args) {
Student student =new Student(6,"钱老爷",88,new Date());
int result =Service.insert(student);
if(result != 0) {
System.out.println("新增成功");
}else {
System.out.println("新增失败");
}
}
}3.4 修改数据
Dao 层
public class Dao {
public static int update(Student student){
Connection con =null;
Statement stat =null;
int result =0;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
stat = con.createStatement();
Date d = student.getBirthday();
SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyy-MM-dd");
String birthday =simpleDateFormat.format(d);
String sql = "UPDATE student SET sid='"+student.getSid()+"',name='"+student.getName()+"',age='"+student.getAge()+"',birthday='"+birthday+"' WHERE sid='"+student.getSid()+"'";
result = stat.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
con.close();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
}
Service层
public class Service {
public static int update(Student student){
return Dao.update(student);
}
}
Controller层
public class Controller {
public static void main(String[] args) {
Student stu = JDBC1.Demo2.Service.FindById(5);
stu.setName("周七");
int result = Service.update(stu);
if(result != 0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
}
}3.5 删除数据
Dao层
public class Dao {
public static int delete(Integer id){
Connection con =null;
Statement stat =null;
int result = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db15", "root", "8Gd_GFI=*jo&");
stat = con.createStatement();
String sql ="DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
con.close();
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
}
Service层
public class Service {
public static int delete(Integer id){
return Dao.delete(id);
}
}
Controller层
public class Controller {
public static void main(String[] args) {
int result = Service.delete(6);
if(result != 0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}
}4 JDBC工具类
工具类的作用：把一个通用的功能包装在一个类中，让它可以在不同的地方重用，这样子我们在编程时可以简化书写，避免重复太多代码。
4.1 配置文件（用于连接数据库）配置文件(在src下创建config.properties)
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db14
username=root
password=8Gd_GFI=*jo&
PS：数据库账号（username）密码（password）根据自己的数据库的账号密码4.2JDBC工具类
/*
JDBC工具类
*/
public class JDBCUtils {
//1.私有构造方法
private JDBCUtils(){};
//2.声明配置信息变量
private static String driverClass;
private static String url;
private static String username;
private static String password;
private static Connection con;
//3.静态代码块中实现加载配置文件和注册驱动
static{
try{
//通过类加载器返回配置文件的字节流
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
//创建Properties集合，加载流对象的信息
Properties prop = new Properties();
prop.load(is);
//获取信息为变量赋值
driverClass = prop.getProperty("driverClass");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
//注册驱动
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
//4.获取数据库连接的方法
public static Connection getConnection() {
try {
con = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//5.释放资源的方法
public static void close(Connection con, Statement stat, ResultSet rs) {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection con, Statement stat) {
close(con,stat,null);
}
}4.3 使用工具类优化student表的CRUD（增删改查）
Student类
public class Student {
private Integer sid;
private String name;
private Integer age;
private Date birthday;
public Student() {
}
public Student(Integer sid, String name, Integer age, Date birthday) {
this.sid = sid;
= name;
this.age = age;
this.birthday = birthday;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
= name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
}
JDBCUtils工具类
public class JDBCUtils {
//1.私有构造方法
private JDBCUtils(){};
//2.声明配置信息变量
private static String driverClass;
private static String url;
private static String username;
private static String password;
private static Connection con;
//3.通过静态代码块中实现加载配置文件和注册驱动
static {
try {
//通过类加载器返回配置文件的字节流。
InputStream is =JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
//创建properties集合，加载流对象信息。
Properties prop =new Properties();
prop.load(is);
//获取信息为变量赋值
driverClass = prop.getProperty("driverClass");
url = prop.getProperty("url");
username=prop.getProperty("username");
password=prop.getProperty("password");
//注册驱动
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
//4.获取数据库连接方式
public static Connection getConnection(){
try {
con = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//释放资源的方式
public static void close(Connection con, Statement stat, ResultSet rs){
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection con, Statement stat) {
close(con,stat,null);
}
}
DAO 层
/*
查询所有学生信息
*/
public class Dao {
public static ArrayList<Student> findAll() {
Connection con = null;
Statement stat = null;
ResultSet rs = null;
ArrayList<Student> list = new ArrayList<>();
try {
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
String sql = "SELECT * FROM student";
rs = stat.executeQuery(sql);
//4.处理结果，将每条记录封装成一个Student对象。将多个Student对象保存到集合中
while(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
Student stu = new Student(sid,name,age,birthday);
list.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.释放资源
JDBCUtils.close(con,stat,rs);
}
return list;
}
/*
条件查询，根据id查询学生信息
*/
public static Student findById(Integer id) {
Connection con = null;
Statement stat = null;
ResultSet rs = null;
Student stu = new Student();
try {
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
String sql = "SELECT * FROM student WHERE sid='"+id+"'";
rs = stat.executeQuery(sql);
//4.处理结果，将记录封装成一个Student对象。
if(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
stu.setSid(sid);
stu.setName(name);
stu.setAge(age);
stu.setBirthday(birthday);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5.释放资源
JDBCUtils.close(con,stat,rs);
}
return stu;
}
/*
新增学生信息
*/
public static int insert(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
Date date = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(date);
String sql = "INSERT INTO student VALUES (null,'"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result = stat.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//4.释放资源
JDBCUtils.close(con,stat);
}
return result;
}
/*
修改学生信息
*/
public static int update(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
Date date = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(date);
String sql = "UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//4.释放资源
JDBCUtils.close(con,stat);
}
return result;
}
/*
删除学生信息
*/
public static int delete(Integer id) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
//1.获取连接
con = JDBCUtils.getConnection();
//2.获取执行者对象
stat = con.createStatement();
//3.执行sql语句，并接收结果
String sql = "DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//4.释放资源
JDBCUtils.close(con,stat);
}
return result;
}
}
Service层
public class Service {
public static ArrayList<Student> findAll() {
return Dao.findAll();
}
public static Student findById(Integer id) {
return Dao.findById(id);
}
public static int insert(Student stu) {
return Dao.insert(stu);
}
public static int update(Student stu) {
return Dao.update(stu);
}
public static int delete(Integer id) {
return Dao.delete(id);
}
}
Controller层
PS：只列举了查。
public class Controller {
public static void main(String[] args) {
ArrayList<Student> list = Service.findAll();
for (Student stu:list){
System.out.println(stu);
}
}
}5.SQL注入攻击（了解）
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
