JDBC是什么
JDBC(Java Database Connectivity)是JAVA访问数据库的解决方案
JDBC定义了一套标准接口,即访问数据库的通用API,实现不同的数据库厂商根据各自数据库的特点去实现这些接口
JDBC希望用相同的方式访问不同的数据库,让具体的数据库操作与数据库厂商实现无关,从而在不同数据库之间轻易的进行切换(跨平台性)
JDBC相关类和接口
驱动类:DriverManager
连接接口:Connection
语句对象接口:Statement
结果集接口:ResultSet
JDBC工作原理
下载驱动
-
下载对应的数据库的驱动(数据库官网)
mysql-connector-java-5.0.4-bin.jar
-
将驱动类加载到项目中(jar包导入到项目中)
Build Path 构建路径
1.加载驱动
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
- 加载驱动类
- 通过Class.forName( )方法(反射)完成驱动类的注册
2.连接接口 Connection
- 根据URL连接参数找到与之匹配的Driver对象,调用其方法获取连接
Connection conn =DriverManager.getConnection(url, user, password);
// Connection类名 conn对象名 url:数据库地址 user:数据库用户 password:密码
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
- dbc:mysql://:连接字符串
- localhost:3306:数据库地址及端口号
- myschool:库名
- serverTimezone:时区设置
注意::Connection只是接口!真正的实现是数据库厂商提供的驱动包完成的
3.statement接口
statement用于执行SQL语句
注意:不同的SQL语句要用不同的方法来执行
Statement执行DML
DML:insert、update、delete,返回值是被影响的行数,用int接受
// 创建sql语句
String sql = "insert into 表名 values(……)";
// 创建执行sql语句的对象
Statement statem = conn.createStatement();
// 执行sql语句
int res = statem.executeUpdate(sql);
Statement执行DQL
DQL:select,返回值是结果集,用ResultSet接收
// 创建sql语句
String sql = "select * from 表名";
// 创建执行sql语句的对象
Statement statem = conn.createStatement();
// 执行sql语句
ResultSet res = statem.executeQuery(sql);
4.ResultSet接口
查询结果存放在ResultSet对象的一系列行中
- ResultSet对象的最初位置在行首
- ResultSet.next()方法用来在行间移动
- ResultSet.getXXX()方法用来取得字段的内容
关闭并释放资源while(res.next()) { int sid = res.getInt("sid"); String name = res.getString("sname"); String bir = res.getString("birthday"); String ssex = res.getString("ssex"); int classid = res.getInt("classid"); System.out.println(sid+name+bir+ssex+classid); }
- 数据库操作执行完毕以后要释放相关资源
re.close();
statem.close();
conn.close();
注意:释放资源顺序,先得到的资源后释放
案例
添加数据insert
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertDemo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
// 3.0 创建sql语句
String sql = "insert into student(sname,birthday,ssex,classid) values ('曹贼','2000-1-1','男',1)";
// 3.1 创建执行sql语句对象
Statement statem = conn.createStatement();
// 3.2 执行sql语句
int ret = statem.executeUpdate(sql);
// 业务逻辑
if(ret >0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
// 4.释放资源
statem.close();
conn.close();
}
}
update
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class UpdateDemo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner input = new Scanner(System.in);
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT","root", "123456");
// 3.创建sql语句
System.out.println("请输入修改学生的编号");
int sid = input.nextInt();
System.out.println("请输入学生新的名字");
String sname = input.next();
System.out.println("请输入学生的新生日(yyyy-MMydd格式)");
String bir = input.next();
System.out.println("请输入学生的性别");
String ssex = input.next();
System.out.println("请输入学生的班级号");
int classid = input.nextInt();
String sql = "update student set sname='"+sname+"',birthday = '"+bir+"',ssex='"+ssex+"',classid='"+classid+"' where sid = "+ sid;
// 获取执行对象
Statement statem = conn.createStatement();
int ret = statem.executeUpdate(sql);
if(ret>0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
// 释放资源
statem.close();
conn.close();
}
}
delete
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class DeleteDemo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner input = new Scanner(System.in);
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
// 3.0 创建sql语句
System.out.println("请输出要删除的学生编号");
int sid = input.nextInt();
String sql = "delete from student where sid=" + sid;
// 3.1 创建执行sql语句对象
Statement statem = conn.createStatement();
// 3.2 执行sql语句
int ret = statem.executeUpdate(sql);
// System.out.println(ret);
// 业务逻辑
if(ret >0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
// 4.释放资源
statem.close();
conn.close();
}
}
select
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
public class SelectTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
// 3. 创建sql语句
String sql = "select sname,birthday,ssex,sid,classid from student";
// 3.1 执行对象
Statement statm = conn.createStatement();
// 3.2 执行
ResultSet rs = statm.executeQuery(sql);
while(rs.next()){
// 解析结果集
// 字段下标的方式(不推荐)
// int sid = rs.getInt(1);
// String sname = rs.getString(2);
// String bir = rs.getString(3);
// Date date = rs.getDate(3);
// String ssex = rs.getString(4);
// int classid = rs.getInt(5);
// 字段名的方式解析结果集
int sid = rs.getInt("sid");
int classid = rs.getInt("classid");
String sname = rs.getString("sname");
String ssex = rs.getString("ssex");
Date bir = rs.getDate("birthday");
System.out.println(sid+sname+bir+ssex+classid);
}
// 4. 释放资源
rs.close();
statm.close();
conn.close();
}
}
SQL注入
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class LoginTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner input = new Scanner(System.in);
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
System.out.println("请输入账号");
String username = input.nextLine();
System.out.println("请输入密码");
String userpwd = input.nextLine();
// 3. 登录 -- 将sql语句中的传参的地方用 ? 占位
String sql = "select * from login where lusername='"+username+"' + and luserpwd='"+userpwd+"'";
// 3.1 执行对象
Statement statm = conn.createStatement();
// 3.2 执行
// ' or 1=1 -- sql注入
ResultSet rs = statm.executeQuery(sql);
if(rs.next()) {
int lid = rs.getInt("lid");
String lusername = rs.getString("lusername");
String luserpwd = rs.getString("luserpwd");
String luname = rs.getString("luname");
String lusex = rs.getString("lusex");
String laddress = rs.getString("laddress");
System.out.println(lid+lusername+luserpwd+luname+lusex+laddress);
System.out.println("登录成功");
}else {
System.out.println("账号密码错误");
}
// 释放资源
rs.close();
// statm.close();
prestatm.close();
conn.close();
}
}
当输入正常的账号密码时,业务逻辑正确,但当账号输入
' or 1=1 --
此时,业务逻辑出现问题,原因是SQL语句的拼接,原本的SQL语句是
select * from login where lusername='……' and luserpwd='……'
但当账号输入特殊符号语句时,SQL语句变成
select * from login where lusername='' or 1=1 -- and luserpwd='……'
此账号把原本的SQL语句条件更改了,变成了全查,导致账号密码不正确时业务逻辑也是登陆成功,解决方法是在特殊符号添加转义符,但是实现起来非常困难
-
避免SQL注入问题不用Statement,使用Preparement
JDBC三层架构
-
DAO层
数据访问层,主要对数据库进行增删改查
-
service层
业务逻辑层,主要做业务逻辑
-
view层
视图层,主要与用户进行交互
-
JavaBean类
实体类,封装数据,数据传输的负载
注:数据来源于用户,最终也要反馈给用户
-
包结构
bean
package bean;
public class Teacher {
private int Tid;
private String Tname;
private String Tsex ;
private String Tbirthday ;
private String Taddress;
private String Temail ;
private String Tmoney;
public int getTid() {
return Tid;
}
public void setTid(int tid) {
Tid = tid;
}
public String getTname() {
return Tname;
}
public void setTname(String tname) {
Tname = tname;
}
public String getTsex() {
return Tsex;
}
public void setTsex(String tsex) {
Tsex = tsex;
}
public String getTbirthday() {
return Tbirthday;
}
public void setTbirthday(String tbirthday) {
Tbirthday = tbirthday;
}
public String getTaddress() {
return Taddress;
}
public void setTaddress(String taddress) {
Taddress = taddress;
}
public String getTemail() {
return Temail;
}
public void setTemail(String temail) {
Temail = temail;
}
public String getTmoney() {
return Tmoney;
}
public void setTmoney(String tmoney) {
Tmoney = tmoney;
}
public Teacher() {
}
public Teacher(int tid, String tname, String tsex, String tbirthday, String taddress, String temail, String tmoney) {
Tid = tid;
Tname = tname;
Tsex = tsex;
Tbirthday = tbirthday;
Taddress = taddress;
Temail = temail;
Tmoney = tmoney;
}
@Override
public String toString() {
return "Login [Tid=" + Tid + ", Tname=" + Tname + ", Tsex=" + Tsex + ", Tbirthday=" + Tbirthday + ", Taddress="
+ Taddress + ", Temail=" + Temail + ", Tmoney=" + Tmoney + "]";
}
}
dao
package dao;
import java.util.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;
import bean.Teacher;
public class TeacherDao {
public int registerD(Teacher to) {
int ret = 0;
try {
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获取连接协议
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?serverTimezone=GMT", "root",
"123456");
// 字符串形式
// 3.sql语句
String sql = "insert into teacher(Tname,Tsex,Tbirthday,Taddress,Temail,Tmoney) values(?,?,?,?,?,?)";
PreparedStatement prestatm = conn.prepareStatement(sql);
prestatm.setObject(1, to.getTname());
prestatm.setObject(2, to.getTsex());
prestatm.setObject(3, to.getTbirthday());
prestatm.setObject(4, to.getTaddress());
prestatm.setObject(5, to.getTemail());
prestatm.setObject(6, to.getTmoney());
ret = prestatm.executeUpdate();
// 释放资源
prestatm.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ret;
}
public List<Teacher> findD() {
List<Teacher> telist = new ArrayList<Teacher>();
try {
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获取连接协议
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?serverTimezone=GMT", "root",
"123456");
// 字符串形式
// 3.sql语句
String sql = "select * from teacher";
Statement statm = conn.createStatement();
ResultSet rs = statm.executeQuery(sql);
// 解析结果集
while (rs.next()) {
Teacher to = new Teacher();
to.setTid(rs.getInt("Tid"));
to.setTmoney(rs.getString("Tmoney"));
to.setTname(rs.getString("Tname"));
to.setTsex(rs.getString("Tsex"));
to.setTbirthday(rs.getString("tbirthday"));
to.setTaddress(rs.getString("Taddress"));
to.setTemail(rs.getString("Temail"));
}
// 释放资源
rs.close();
statm.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return telist;
}
public List<Teacher> updateD(){
List<Teacher> telist2 = new ArrayList<Teacher>();
try {
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
Scanner input = new Scanner(System.in);
System.out.println("请输入你的姓名");
String xingming = input.next();
System.out.println("请输入你的性别");
String xingbie =input.next();
System.out.println("请输入你的生日");
String bir =input.next();
System.out.println("请输入你的住址");
String adds = input.next();
System.out.println("请输入你的邮件");
String emials = input.next();
System.out.println("请输入你的薪资");
String xz = input.next();
// 2获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?serverTimezone=GMT", "root",
"123456");
// 3创建sql语句
// String sql = "update teacher set tname ='" + xingming + "',tbirthday='" + bir + "',tsex='" + xingbie
// + "',taddress='" + adds + "',temail='"+emials+"',tmoney='"+xz+"' where tid='3'";
String sql = "update teacher set tname ='"+xingming+ "',tbirhday='"+bir+"',tsex='"+xingbie+"',taddress='"+adds+"',temials='"+emials+"',tmoney='"+xz+"' where tid='3' ";
Statement state = conn.createStatement();
int ret = state.executeUpdate(sql);
if (ret > 0) {
System.out.println("学生信息修改成功");
} else {
System.out.println("修改失败");
}
// 3.释放资源 先使用的后释放资源
state.close();
conn.close();
input.close();
} catch (Exception e) {
// TODO: handle exception
}
return telist2;
}
public int deleteD(int tid){
int ret = 0;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// 2获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?serverTimezone=GMT", "root",
"123456");
// 3创建sql语句
String sql = "delete from teacher where tid='"+tid+"'";
Statement state = conn.createStatement();
ret = state.executeUpdate(sql);
// 3.释放资源 先使用的后释放资源
state.close();
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
return ret;
}
}
service业务逻辑层
package service;
import java.util.List;
import bean.Teacher;
import dao.TeacherDao;
public class TeacherService {
// 增
public boolean registerS(Teacher to) {
boolean isok = false;
TeacherDao td = new TeacherDao();
int ret = td.registerD(to);
if (ret >0) {
isok = true;
}
return isok;
}
public List<Teacher> finds(){
TeacherDao tod = new TeacherDao();
List<Teacher> tolist = tod.findD();
return tolist;
}
public List<Teacher> updates(){
TeacherDao tod2 =new TeacherDao();
List<Teacher> tdList = tod2.updateD();
return tdList;
}
public boolean deletes(int tid){
boolean isok = false;
TeacherDao tod2 =new TeacherDao();
int ret = tod2.deleteD(tid);
if (ret > 0) {
isok = true;
}
return isok;
}
}
view 视图层 用于和用户交互处理
package view;
import java.util.List;
import java.util.Scanner;
import bean.Teacher;
import dao.TeacherDao;
import service.TeacherService;
public class TeacherView {
public void register() {
Scanner input = new Scanner(System.in);
System.out.println("请输入你的姓名");
String xingming = input.next();
System.out.println("请输入你的性别");
String xingbie = input.next();
System.out.println("请输入你的生日");
String bir = input.next();
System.out.println("请输入你的住址");
String adds = input.next();
System.out.println("请输入你的邮件");
String emials = input.next();
System.out.println("请输入你的薪资");
String xz = input.next();
Teacher to = new Teacher();
to.setTname(xingming);
to.setTsex(xingbie);
to.setTbirthday(bir);
to.setTaddress(adds);
to.setTemail(emials);
to.setTmoney(xz);
TeacherService ts = new TeacherService();
boolean isok = ts.registerS(to);
if (isok) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
}
public void findv() {
TeacherService ts = new TeacherService();
List<Teacher> telist = ts.finds();
telist.forEach(System.out::println);
}
public void updatev() {
TeacherService tv = new TeacherService();
List<Teacher> tsList = tv.updates();
tsList.forEach(System.out::println);
}
public void deleteV() {
Scanner input = new Scanner(System.in);
System.out.println("请输入想删教师的编号");
int tid = input.nextInt();
TeacherService ts = new TeacherService();
boolean isok = ts.deletes(tid);
if (isok) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}
}
Test测试类
package Test;
import view.TeacherView;
public class Test {
public static void main(String[] args) {
TeacherView tv = new TeacherView();
// tv.register(); //添加 控制台添加
tv.findv(); // 寻找
// tv.updatev(); // 修改 通过 tname
// tv.deleteV(); // 删除通过编号 tid
}
}
三层架构DAO封装
在DAO层的实现接口类中,发现代码重复率高(如加载驱动,获取连接),此时就可以把相同的代码提取出来进行封装继承,达到提高代码复用率,降低冗余的效果
-
包结构
com.ape.bean:实体类
com.ape.dao:DAO层
BaseDao:执行sql语句对象
DaoUtil:加载驱动,获取连接,释放资源
IStudentDao:DAO层接口
com.ape.dap.impl:实现DAO接口的类
com.ape.test:测试类
代码实现
com.ape.bean:实体类
同上三层架构中的实体类
com.ape.dao:
BaseDao
package com.ape.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
//属性
protected Connection conn;
protected PreparedStatement prestatm;
protected ResultSet rs;
// 增删改 返回的是受影响的行数 是int类型
protected int update(String sql,Object...arge) {
int ret = 0;
try {
conn = DaoUtil.getConnection();
prestatm = conn.prepareStatement(sql);
if (arge != null) {
for(int i=0;i<arge.length;i++) {
prestatm.setObject(i+1, arge[i]);
}
}
ret = prestatm.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DaoUtil.closeRs(conn, prestatm, rs);
}
return ret;
}
// 查询 返回的是结果集
protected ResultSet query(String sql,Object...arge) {
ResultSet rs = null;
try {
conn = DaoUtil.getConnection();
prestatm = conn.prepareStatement(sql);
if (arge != null) {
for(int i = 0; i<arge.length;i++) {
prestatm.setObject(i+1, arge[i]);
}
}
rs =prestatm.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
public int deleteStudent(int sid) {
// TODO Auto-generated method stub
return 0;
}
}
增删改的返回值都是受影响行数为int类型,而查询的返回值是ResultSet对象
DaoUtil
package com.ape.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DaoUtil {
static {
// 1.加载驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
// 2.获取连接
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?serverTimezone=GMT","root", "123456");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
// 释放资源的封装 先使用的资源后释放
public static void closeRs(Connection conn,PreparedStatement prestatm,ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (prestatm != null) {
prestatm.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
驱动只加载一次就可以,可用静态代码块,随着类的加载执行一次
IStudentDao 接口
package com.ape.dao;
import java.util.List;
import com.aoe.bean.Student;
public interface IStudentDao {
//新增
public int insertStudent(Student s);
//修改
public int updateStudent(Student s);
//删除
public int deleteStudent(int sid);
//查找
public List<Student> selectStudent();
}
StudentDaoImpL
package com.ape.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.aoe.bean.Student;
public class StudentDaoImpl extends BaseDao implements IStudentDao {
@Override
public int insertStudent(Student s) {
int ret =0;
try {
Connection conn = DaoUtil.getConnection();
String sql = "insert into student(sname,birthday,ssex,classid) values(?,?,?,?)";
PreparedStatement prestatm = conn.prepareStatement(sql);
prestatm.setObject(1, s.getSname());
prestatm.setObject(2, s.getBirthday());
prestatm.setObject(3, s.getSsex());
prestatm.setObject(4, s.getClassid());
ret = prestatm.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ret;
}
@Override
public int updateStudent(Student s) {
String sql ="update student set classid =? where sid =?";
return update(sql, s.getClassid(),s.getSid());
}
@Override
public int deleteStudent(int sid) {
String sql ="delete from student where sid =?";
return update(sql, sid);
}
@Override
public List<Student> selectStudent() {
List<Student> slist = new ArrayList<Student>();
try {
String sql = "select * from student";
rs = query(sql, null);
while (rs.next()) {
Student st = new Student();
st.setClassid(rs.getInt("classid"));
st.setSname(rs.getString("sname"));
st.setBirthday(rs.getDate("birthday"));
st.setSsex(rs.getString("ssex"));
st.setSid(rs.getInt("sid"));
slist.add(st);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DaoUtil.closeRs(conn, prestatm, rs);
}
return slist;
}
}
通过继承BaseDao类和实现dao层接口,使得方法中的语句大大的减少