JDBC全集 JDBC 类和接口工作原理连接数据库案列，SQL注入，JDBC三层架构，连接池技术

Fonck HAO

于 2023-11-14 23:32:11 发布

阅读量68

点赞数

文章标签：数据库 sql 架构

本文链接：https://blog.csdn.net/m0_73857228/article/details/134397973

版权

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层接口，使得方法中的语句大大的减少

Fonck HAO

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
JDBC全集 JDBC 类和接口工作原理连接数据库案列，SQL注入，JDBC三层架构，连接池技术

JDBC是什么JDBC（Java Database Connectivity）是JAVA访问数据库的解决方案JDBC定义了一套标准接口，即访问数据库的通用API，实现不同的数据库厂商根据各自数据库的特点去实现这些接口JDBC希望用相同的方式访问不同的数据库，让具体的数据库操作与数据库厂商实现无关，从而在不同数据库之间轻易的进行切换（跨平台性）
复制链接

扫一扫