目录
JDBC是什么
A Java Database Connectivity:Java访问数据库的解 决方案
B JDBC定义了一套标准接口,即访问数据库的通用API, 不同的数据库厂商根据各自数据库的特点去实现这些接口。
C JDBC希望用相同的方式访问不同的数据库,让具体的数据库操作与数据库厂商实现无关,从而在不同数据库之间轻易的进行切换。
JDBC相关类与接口
1.驱动管理类 DriverManager
2.连接接口 Connection
3.语句对象接口 Statement
4.结果集接口 ResultSet
JDBC工作原理
应用程序 1.Class.forName()加载驱动 数据库服务器
2.DriverManager获取Connection连接 获取Connection连接
3.创建Statement 执行SQL语句
3.返回ResultSet 查询结果
4.释放资源
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DaoUtil {
// 加载驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 获取链接对象
public static Connection getConn() {
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
// 释放资源
public static void closeSource(Connection conn, Statement statm, ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(statm != null) {
try {
statm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
进行数据的增删查改
public class StudentGan {
public List<Student> findAllStudent(){
List<Student> slist = null;
Connection conn = null;
Statement statm = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
statm = conn.createStatement();
rs = statm.executeQuery("select * from student");
if(rs != null) {
slist = new ArrayList<Student>();
while(rs.next()) {
Student s = new Student();
s.setBirthday(rs.getDate("birthday"));
s.setClassid(rs.getInt("classid"));
s.setSid(rs.getInt("sid"));
s.setSname(rs.getString("sname"));
s.setSsex(rs.getString("ssex"));
slist.add(s);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs != null) {
rs.close();
}
if(statm != null) {
statm.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return slist;
}
public boolean addStudent(Student s) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
boolean istrue = false;
Connection conn = null;
Statement statm = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
statm = conn.createStatement();
String sql = "insert into student(sname,birthday,ssex,classid) "
+ "values('"+s.getSname()+"','"+ sdf.format(s.getBirthday()) +"','"+s.getSsex()+"',"+s.getClassid()+")";
int ret = statm.executeUpdate(sql);
if(ret > 0) {
istrue = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(statm != null) {
statm.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return istrue;
}
}
将上层的代码分成三层
Dao层
接口
public interface IStudentDao {
// 新增
public int addStudent(Student s);
// 删除
public int delStudent(int sid);
// 修改
public int updateStudent(Student s);
// 查询
public List<Student> findAllStudent();
public Student findStduentBySid(int sid);
}
具体实现类
public class StudentDao implements IStudentDao {
// 查询
public Student findStudentBySid(int sid) {
Connection conn = null;
Statement statm = null;
ResultSet rs = null;
Student s = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
statm = conn.createStatement();
rs = statm.executeQuery("select * from student where sid = "+sid);
if(rs != null) {
s = new Student();
if(rs.next()) {
s.setBirthday(rs.getDate("birthday"));
s.setClassid(rs.getInt("classid"));
s.setSid(rs.getInt("sid"));
s.setSname(rs.getString("sname"));
s.setSsex(rs.getString("ssex"));
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(statm != null) {
try {
statm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return s;
}
Service层
public class StudentService {
// 展示学生
public Student showStu(int sid) {
StudentDao sd = new StudentDao();
return sd.findStudentBySid(sid);
}
// 学生的注册
public boolean zhuce(Student s) {
boolean istrue = false;
IStudentDao sd = new StudentDao();
int ret = sd.addStudent(s);
if(ret > 0) {
istrue = true;
}
return istrue;
}
}
View层
public class StudentView {
public void show() {
Scanner input = new Scanner(System.in);
System.out.println("请输入要查看的学生编号");
StudentService ss = new StudentService();
Student s = ss.showStu(input.nextInt());
System.out.println(s);
}
}
因为大量重复代码,所以对Dao层进行封装
分为了DaoUtil和BaseUtil
public class DaoUtil {
// 加载驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 获取链接对象
public static Connection getConn() {
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
// 释放资源
public static void closeSource(Connection conn, Statement statm, ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(statm != null) {
try {
statm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public class BaseDao {
protected Connection conn;
protected Statement statm;
protected ResultSet rs;
// 增 删 改
protected int update(String sql) {
int ret = 0;
try {
conn = DaoUtil.getConn();
statm = conn.createStatement();
ret = statm.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DaoUtil.closeSource(conn, statm, rs);
}
return ret;
}
}
连接池
连接池基本的思想是在系统初始化的时候,将数据库连接作为对象存储在内存中,当用户需要访问数 据库时,并非建立一个新的连接,而是从连接池中取出一个已建立的空闲连接对象。使用完毕后,用户也并非将连接关闭,而是将连接放回连接池中,以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时,还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数 以及每个连接的最大使用次数、最大空闲时间等等,也可以通过其自身的管理机制来监视数据库连接的 数量、使用情况等。
常见的第三方连接池
Druid: 德鲁伊,alibaba提供,稳定,性能好等。
C3P0 : hibernate 框架底层使用。(等效框架MyBatis)
Druid连接
public class Test {
public static void main(String[] args) throws Exception {
// 读取配置文件
Properties pro = new Properties();
FileInputStream fis = new FileInputStream("./src/druid.properties");
pro.load(fis);
// 构建池子
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
Connection conn = ds.getConnection();
System.out.println(conn);
conn.close();
}
}
C3P0连接池
public class Test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//构建池子
DataSource ds = new ComboPooledDataSource();
Connection conn = ds.getConnection();
System.out.println(conn);
// 归还到连接池中
conn.close();
}
}
JDBC登陆代码
public class Test {
public static void main(String[] args) {
/*
* sql 注入 最基本的要求防止sql注入
*
*/
Scanner input = new Scanner(System.in);
System.out.println("请输入账号:");
String username = input.nextLine();
System.out.println("请输入密码:");
String userpwd = input.nextLine();
ILoginDao ild = new LoginDaoImpl();
Login lo = ild.denglu(username, userpwd);
if(lo != null) {
System.out.println("登录成功");
System.out.println(lo);
}else {
System.out.println("登录失败");
}
}
}
SQL注入简介
SQL注入是比较常见的网络攻击方式之一,它不是利用操作系统的BUG来实现攻击,而是针对程序员编写时的疏忽,通过SQL语句,实现无账号登录,甚至篡改数据库。 Sql 注入攻击是通过将恶意的 Sql 查询或添加语句插入到应用的输入参数中,再在后台 Sql 服务器上解析执行进行的攻击,它目前黑客对数据库进行攻击的最常用手段之一
防止Sql注入
public class Demo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner input = new Scanner(System.in);
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
// Statement statm = conn.createStatement();
// ResultSet rs = statm.executeQuery(sql);
System.out.println("请输入用户名:");
String username = input.nextLine();
System.out.println("请输入密码");
String userpwd = input.nextLine();
String sql = "select * from login where lname= ? and lpwd = ?";
// PreparedStatement 可以防止sql注入的执行器
// sql语句预处理
PreparedStatement perstatm = conn.prepareStatement(sql);
// 传参
perstatm.setObject(1, username);
perstatm.setObject(2, userpwd);
ResultSet rs = perstatm.executeQuery();
if(rs != null) {
if(rs.next()) {
System.out.println(rs.getInt("lid")+rs.getString("lname")+rs.getString("lpwd")+rs.getString("lsex"));
}else {
System.out.println("啥也没有");
}
}
rs.close();
perstatm.close();
//statm.close();
conn.close();
}
}
进行多张表操作时,有以下关系
一对一关系:
public class StudentDaoImpl extends BaseDao implements IStudentDao {
@Override
public List<Student> findAllStudentAndBanji() {
List<Student> slist = null;
String sql = "select sid,sname,birthday,ssex,class.classid,classname "
+ "from student left join class on student.classid = class.classid";
rs = query(sql);
if(rs != null) {
slist = new ArrayList<Student>();
try {
while(rs.next()) {
// 学生的数据
Student s = new Student();
s.setBirthday(rs.getDate("birthday"));
s.setClassid(rs.getInt("classid"));
s.setSid(rs.getInt("sid"));
s.setSname(rs.getString("sname"));
s.setSsex(rs.getString("ssex"));
// 班级数据
Banji bj = new Banji();
bj.setClassid(rs.getInt("classid"));
bj.setClassname(rs.getString("classname"));
s.setBj(bj);
slist.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DaoUtil.closeSource(conn, prestatm, rs);
}
}
return slist;
}
}
一对多关系
public class BanjiDaoImpl extends BaseDao implements IBanjiDao {
@Override
public List<Banji> findAllBanjiAndStu() {
List<Banji> bjlist = null;
String sql = "select class.classid, classname, sid,sname, "
+ "birthday,ssex from class left join student on class.classid = student.classid";
rs = query(sql);
if(rs != null) {
bjlist = new ArrayList<Banji>();
try {
while(rs.next()) {
if(bjlist.size() == 0) {
// 班级
Banji bj = new Banji();
bj.setClassid(rs.getInt("classid"));
bj.setClassname(rs.getString("classname"));
// 构建学生
List<Student> slist = new ArrayList<Student>();
Student s = new Student();
s.setBirthday(rs.getDate("birthday"));
s.setClassid(rs.getInt("classid"));
s.setSid(rs.getInt("sid"));
s.setSname(rs.getString("sname"));
s.setSsex(rs.getString("ssex"));
slist.add(s);
bj.setDuostu(slist);
bjlist.add(bj);
}else {
boolean isok = true;
for(int i = 0; i<bjlist.size();i++) {
if(bjlist.get(i).getClassid() == rs.getInt("classid")) {
isok = false; // 有这个班级
Student s = new Student();
s.setBirthday(rs.getDate("birthday"));
s.setClassid(rs.getInt("classid"));
s.setSid(rs.getInt("sid"));
s.setSname(rs.getString("sname"));
s.setSsex(rs.getString("ssex"));
bjlist.get(i).getDuostu().add(s);
}
}
if(isok) { // 没有找到相同的班级编号
Banji bj = new Banji();
bj.setClassid(rs.getInt("classid"));
bj.setClassname(rs.getString("classname"));
// 构建学生
List<Student> slist = new ArrayList<Student>();
Student s = new Student();
s.setBirthday(rs.getDate("birthday"));
s.setClassid(rs.getInt("classid"));
s.setSid(rs.getInt("sid"));
s.setSname(rs.getString("sname"));
s.setSsex(rs.getString("ssex"));
slist.add(s);
bj.setDuostu(slist);
bjlist.add(bj);
}
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DaoUtil.closeSource(conn, prestatm, rs);
}
}
return bjlist;
}
}