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( )方法（反射）完成驱动类的注册
Connection接口(2)
根据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");
url：“jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT”
dbc:mysql://：连接字符串localhost:3306：数据库地址及端口号myschool：库名serverTimezone：时区设置
注意：:Connection只是接口！真正的实现是数据库厂商提供的驱动包完成的
Statement接口(3)
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);
ResultSet接口(4)
查询结果存放在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);
}
关闭并释放资源(5)
数据库操作执行完毕以后要释放相关资源
ConnectionStatementResultSet
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
使用Preparement对象，创建SQL语句写参数时，需要用占位符？占位参数，再调用Preparement对象的setObject(parameterIndex, x)方法，把参数传给SQL语句
parameterIndex：SQL语句中参数的位置，所有和SQL有关的索引都从1开始x：传入参数的名
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class LoginDemo1 {
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 = ? and luserpwd = ?";
// 3.1 创建执行SQL语句的Preparement对象
PreparedStatement prestatem = conn.prepareStatement(sql);
// 3.2 传参
prestatem.setObject(1,username);
prestatem.setObject(2, userpwd);
// 3.3 执行SQL语句
ResultSet res = prestatem.executeQuery();
if(res.next()) {
String lusername = res.getString("lusername");
String luserupwd = res.getString("luserpwd");
String luname = res.getString("luname");
String lusex = res.getString("lusex");
String laddress = res.getString("laddress");
System.out.println(lusername+luserupwd+luname+lusex+laddress);
System.out.println("登录成功");
}else{
System.out.println("账号或密码错误");
}
// 释放资源
prestatem.close();
conn.close();
input.close();
}
}
注意：Statement存在SQL注入的问题，已成为过去时，弃用，都用Preparement
JDBC三层架构
DAO层
数据访问层，主要对数据库进行增删改查
service层
业务逻辑层，主要做业务逻辑
view层
视图层，主要与用户进行交互
JavaBean类
实体类，封装数据，数据传输的负载
注：数据来源于用户，最终也要反馈给用户
案例
包结构
com.ape.bean：实体类包com.ape.dao：dao层包，dao层接口com.ape.dao.impl：com.ape.dao包下的包，实现dao接口的类com.ape.service：service层包，service层接口com.ape.service.impl：com.ape.service包下的包，实现service接口的类com.ape.view：view层包，view层接口com.ape.view.impl：com.ape.view包下的包，实现view接口的类com.ape.test：测试类
代码
com.ape.bean
package com.ape.bean;
import java.util.Date;
public class Student {
private int sid;
private String sname;
private Date birthday;
private String ssex;
private int classid;
// 无参构造方法
public Student() {
super();
}
// 全参构造方法
public Student(int sid, String sname, Date birthday, String ssex, int classid) {
super();
this.sid = sid;
this.sname = sname;
this.birthday = birthday;
this.ssex = ssex;
this.classid = classid;
}
// 重写toString方法
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", ssex=" + ssex + ", classid="
+ classid + "]";
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
public int getClassid() {
return classid;
}
public void setClassid(int classid) {
this.classid = classid;
}
}
com.ape.dao
package com.ape.dao;
import java.util.List;
import com.ape.bean.Student;
public interface studentDao {
// 新增
public int addStudent(Student s);
// 更新
public int updateStudent(Student s);
// 删除
public int deleteStudent(Student s);
// 查看
public Student findStudentBySid(int sid);
public List
}
com.ape.dao.impl
package com.ape.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ape.bean.Student;
import com.ape.dao.studentDao;
import com.mysql.cj.protocol.Resultset;
import com.mysql.cj.xdevapi.Result;
public class studentDaoImpl implements studentDao{
@Override
public int addStudent(Student s) {
int res =0;
Connection conn = null;
PreparedStatement prepstatem = null;
try {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 获取连接
conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
// 创建sql语句
String sql = "insert into student(sname,birthday,ssex,classid) values(?,?,?,?)";
// 创建执行sql语句的对象
prepstatem = conn.prepareStatement(sql);
prepstatem.setObject(1, s.getSname());
prepstatem.setObject(2, s.getBirthday());
prepstatem.setObject(3, s.getSsex());
prepstatem.setObject(4, s.getClassid());
res = prepstatem.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(prepstatem != null) {
try {
prepstatem.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 res;
}
// 修改
@Override
public int updateStudent(Student s) {
// TODO Auto-generated method stub
return 0;
}
// 删除
@Override
public int deleteStudent(Student s) {
// TODO Auto-generated method stub
return 0;
}
// 通过sid查找学生
@Override
public Student findStudentBySid(int sid) {
Student s = null;
Connection conn = null;
PreparedStatement prepstatem =null;
ResultSet res = null;
try {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
// 创建sql语句
String sql = "select * from student where sid = ?";
// 创建执行sql语句的对象
prepstatem = conn.prepareStatement(sql);
prepstatem.setObject(1, sid);
res = prepstatem.executeQuery();
if(res.next()) {
s = new Student();
s.setSid(res.getInt("sid"));
s.setBirthday(res.getDate("birthday"));
s.setSname(res.getString("sname"));
s.setSsex(res.getString("ssex"));
s.setClassid(res.getInt("classid"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(res != null) {
try {
res.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(prepstatem !=null) {
try {
prepstatem.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;
}
@Override
public List
List
Connection conn = null;
PreparedStatement prepstatem = null;
ResultSet res = null;
try {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
// 创建sql语句
String sql = "select * from student";
// 创建执行sql语句的对象
prepstatem = conn.prepareStatement(sql);
// 执行sql语句
res = prepstatem.executeQuery();
while(res.next()) {
Student s = new Student();
s.setSid(res.getInt("sid"));
s.setSname(res.getString("sname"));
s.setBirthday(res.getDate("birthday"));
s.setSsex(res.getString("ssex"));
s.setClassid(res.getInt("classid"));
slist.add(s);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(res !=null) {
try {
res.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(prepstatem != null) {
try {
prepstatem.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 slist;
}
}
com.ape.service
package com.ape.service;
import java.util.List;
import com.ape.bean.Student;
public interface studentService {
// 注册
public boolean register(Student s);
// 信息修改
// 详细信息
public Student info(int sid);
// 全部学生列表
public List
}
com.ape.service.impl
package com.ape.service.impl;
import java.util.List;
import com.ape.bean.Student;
import com.ape.dao.studentDao;
import com.ape.dao.impl.studentDaoImpl;
import com.ape.service.studentService;
public class studentServiceImpl implements studentService{
@Override
public boolean register(Student s) {
boolean isok = false;
studentDao sd = new studentDaoImpl();
int res = sd.addStudent(s);
if(res>0) {
isok = true;
}
return isok;
}
@Override
public Student info(int sid) {
studentDao sd = new studentDaoImpl();
Student s = sd.findStudentBySid(sid);
return s;
}
@Override
public List
studentDao sd = new studentDaoImpl();
List
return slist;
}
}
com.ape.view
package com.ape.view;
public interface studentView {
//学生的注册
public void showstuadd();
// 学生个人信息
public void showstuinfo();
// 所有学生的信息
public void showstulist();
// 学生模块菜单
public void showstumenu();
}
com.ape.view.impl
package com.ape.view.impl;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Scanner;
import com.ape.bean.Student;
import com.ape.service.studentService;
import com.ape.service.impl.studentServiceImpl;
import com.ape.view.studentView;
public class studentViewImpl implements studentView {
@Override
public void showstuadd() {
Scanner input = new Scanner(System.in);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
System.out.println("添加学生：按照提示信息输入");
System.out.println("请输入学生姓名");
String sname = input.next();
System.out.println("请输入学生的生日");
String bir = input.next();
System.out.println("请输入学生的性别");
String sex = input.next();
System.out.println("请输入学生的班级");
int classid = input.nextInt();
Student s = new Student();
try {
s.setBirthday(sdf.parse(bir));
} catch (ParseException e) {
e.printStackTrace();
}
s.setSname(sname);
s.setSsex(sex);
s.setClassid(classid);
studentService ss = new studentServiceImpl();
boolean isok = ss.register(s);
if(isok) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
}
@Override
public void showstuinfo() {
Scanner input = new Scanner(System.in);
System.out.println("请输入需要查找的学生编号");
int sid = input.nextInt();
studentService ss = new studentServiceImpl();
Student s = ss.info(sid);
System.out.println(s);
}
@Override
public void showstulist() {
studentService ss = new studentServiceImpl();
List
stulist.forEach(System.out::println);
}
@Override
// 选项菜单
public void showstumenu() {
Scanner input = new Scanner(System.in);
int key = -1;
System.out.println("欢迎使用学生管理系统");
do {
System.out.println("输入数字1：添加学生，2：查看一个学生的信息，3：查看所有学生，4：修改学生信息，5：开除学生，0：退出程序");
if(input.hasNextInt()) {
key = input.nextInt();
switch(key) {
case 1:
{
showstuadd();
}
break;
case 2:{
showstuinfo();
}
break;
case 3:{
showstulist();
}
break;
case 4:
{
System.out.println("功能未开发");
}
break;
case 5:
{
System.out.println("功能未开发");
}
break;
default:
System.out.println("请输入正确编号");
break;
}
}else {
System.out.println("请输入菜单中的编号,敲雷瓦");
input.next();
}
}while(key != 0);
System.out.println("感谢使用，Bye！");
input.close();
}
}
com.ape.test
package com.ape.test;
import com.ape.view.studentView;
import com.ape.view.impl.studentViewImpl;
public class Test01 {
// 测试
public static void main(String[] args) {
studentView sv = new studentViewImpl();
sv.showstumenu();
}
}
三层架构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 prepstatem;
protected ResultSet res;
// 增删改
public int exeUpdate(String sql , Object...arge) {
int ret = 0;
try {
conn = DaoUtil.getConn();
prepstatem = conn.prepareStatement(sql);
if(arge != null) {
for(int i = 0; i prepstatem.setObject(i+1, arge[i]); } } ret = prepstatem.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DaoUtil.closeResourse(conn, prepstatem, res); } return ret; } // 查询 public ResultSet exeQuery(String sql,Object...arge) { ResultSet rs = null; try { conn = DaoUtil.getConn(); prepstatem = conn.prepareStatement(sql); if(arge != null) { for(int i = 0; i prepstatem.setObject(i+1, arge[i]); } } rs = prepstatem.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rs; } } 增删改的返回值都是受影响行数为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{ 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 closeResourse(Connection conn , PreparedStatement prepstatem,ResultSet res) { if(res != null) { try { res.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(prepstatem != null) { try { prepstatem.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(); } } } } 驱动只加载一次就可以，可用静态代码块，随着类的加载执行一次 IStudentDao package com.ape.dao; import java.util.List; import com.ape.bean.Student; public interface IStudentDao { // 新增 public int addStudent(Student s); // 更新 public int updateStudent(Student s); // 删除 public int deleteStudent(Student s); // 查看 public Student findStudentBySid(int sid); public List } com.ape.dap.impl package com.ape.dao.impl; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.ape.bean.Student; import com.ape.dao.BaseDao; import com.ape.dao.IStudentDao; public class StudentDaoImpl extends BaseDao implements IStudentDao{ @Override public int addStudent(Student s) { String sql = "insert into student(sname,birthday,ssex,classid) values (?,?,?,?)"; return exeUpdate(sql, s.getSname(),s.getBirthday(),s.getSsex(),s.getClassid()); } // 修改 @Override public int updateStudent(Student s) { String sql = "update student set sname =?,birthday=?,ssex=?,classid=? where sid = ?"; return exeUpdate(sql, s.getSname(),s.getBirthday(),s.getSsex(),s.getClassid(),s.getSid()); } // 删除 @Override public int deleteStudent(Student s) { String sql = "delete from student where sid = ?"; return exeUpdate(sql, s.getSid()); } // 通过sid查找学生 @Override public Student findStudentBySid(int sid) { Student s = null; try { String sql = "select * from student where sid = ?"; res = exeQuery(sql, sid); // 解析结果 if(res.next()) { s = new Student(); s.setSid(res.getInt("sid")); s.setSname(res.getString("sname")); s.setBirthday(res.getDate("birthday")); s.setSsex(res.getString("ssex")); s.setClassid(res.getInt("classid")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return s; } @Override public List List try { String sql = "select * from student"; res = exeQuery(sql); // 解析结果 while(res.next()) { Student s = new Student(); s.setSid(res.getInt("sid")); s.setSname(res.getString("sname")); s.setBirthday(res.getDate("birthday")); s.setSsex(res.getString("ssex")); s.setClassid(res.getInt("classid")); slist.add(s); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return slist; } } 通过继承BaseDao类和实现dao层接口，使得方法中的语句大大的减少 com.ape.test package com.ape.test; import java.text.ParseException; import java.text.SimpleDateFormat; import com.ape.bean.Student; import com.ape.dao.impl.StudentDaoImpl; public class Test { public static void main(String[] args) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); StudentDaoImpl sdi = new StudentDaoImpl(); //新增学生 Student s = new Student(); s.setSname("王忠"); s.setSsex("男"); try { s.setBirthday(sdf.parse("1999-7-7")); } catch (ParseException e) { // TODO Auto-generated catch block e.printStackTrace(); } s.setClassid(3); System.out.println(sdi.addStudent(s)); // 修改：略 // 删除：略 // 通过id查询学生个人信息 System.out.println(sdi.findStudentBySid(2)); // 查询所有学生信息 sdi.findAllStudent().forEach(System.out::println); } } 连接池技术 数据库连接池 概念：其实就是一个容器(集合)，存放数据库连接的容器。 当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。 好处： 节约资源 用户访问高效 实现： 标准接口：DataSource javax.sql包下的 方法： 获取连接：getConnection() 归还连接：Connection.close()。如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接了。而是归还连接 一般我们不去实现它，有数据库厂商来实现 1. C3P0：数据库连接池技术 2. Druid：数据库连接池实现技术，由阿里巴巴提供的 C3P0 步骤 导入jar包 (两个) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar ，*不要忘记导入数据库驱动jar包 定义配置文件： 名称： c3p0.properties 或者 c3p0-config.xml文件名称不可以修改 路径：直接将文件放在src目录下即可，路径不可修改 创建核心对象 数据库连接池对象 ComboPooledDataSource 获取连接： getConnection 源代码实现 DaoUtilC3P0 package com.ape.test; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DaoUtilC3P0 { private static DataSource ds; static { ds = new ComboPooledDataSource(); } public Connection getConn() { Connection conn = null; try { conn=ds.getConnection(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } } Test package com.ape.test; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class Test { public static void main(String[] args) throws SQLException { // 连接池 DataSource ds = new ComboPooledDataSource(); Connection conn = ds.getConnection(); System.out.println(conn); conn.close();// 还到池子中 } } druid（德鲁伊） Druid：数据库连接池实现技术，由阿里巴巴提供的 步骤： 导入jar包 druid-1.0.9.jar 定义配置文件： 是properties形式的 可以叫任意名称，可以放在任意目录下 加载配置文件。Properties 获取数据库连接池对象：通过工厂来来获取 DruidDataSourceFactory获取连接：getConnection 代码实现 package com.ape.test; import java.io.FileInputStream; import java.sql.Connection; import java.util.Properties; import javax.sql.DataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; public class test { public static void main(String[] args) throws Exception { // 读取配置文件 Properties pro = new Properties(); FileInputStream input = new FileInputStream("./src/druid.properties"); pro.load(input); // 创建连接池 -- 德鲁伊 DataSource ds = DruidDataSourceFactory.createDataSource(pro); Connection conn = ds.getConnection(); System.out.println(conn); conn.close(); } }