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)
数据库操作执行完毕以后要释放相关资源
- Connection
- Statement
- ResultSet
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语句条件更改了，变成了全查，导致账号密码不正确时业务逻辑也是登陆成功，解决方法是在特殊符号添加转义符,但是实现起来非常困难，所以有新的方法如下
使用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<Student> findAllStudent(); } -
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<Student> findAllStudent() { List<Student> slist = new ArrayList<Student>(); 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<Student> stulist(); } -
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<Student> stulist() { studentDao sd = new studentDaoImpl(); List<Student> slist = sd.findAllStudent(); 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<Student> stulist = ss.stulist(); 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<arge.length ;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<arge.length ;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<Student> findAllStudent(); }
-
-
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<Student> findAllStudent() { List<Student> slist = new ArrayList<Student>(); 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(); } }
1517
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
