JDBC逐步简化

最新推荐文章于 2021-11-08 11:29:34 发布

happyInHappy

最新推荐文章于 2021-11-08 11:29:34 发布

阅读量230

点赞数

分类专栏：增删改查文章标签： java jdbc

本文链接：https://blog.csdn.net/happyInHappy/article/details/108961956

版权

增删改查专栏收录该内容

2 篇文章 1 订阅

订阅专栏

JDBC

一、定义

JDBC Java DataBase Connectivity java数据库连接

首先是由Java提供的一套接口不同的数据库厂商去实现这些接口从而实现和数据库的连接

接口 java.sql包中

反射通过包名 + 类名来获取一个类类型的对象

必须使用与之对应的数据库厂商提供的相关的类 jar

二、增删改查

1.步骤

1.导入jar包

2.使用反射的方式加载驱动

3.通过驱动管理类获取一个连接对象

4.有了连接对象还需要操作sql的对象

5.编写sql语句

6.通过statement对象来执行sql语句

7.将数据从结果集中取出来

8.关闭资源

2.增加

package com.qfedu.test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Date;
import java.util.Scanner;

public class TestInsert {
	public static void main(String[] args) {
		Connection conn =  null;
		Statement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");

			statement = conn.createStatement();

			Scanner input = new Scanner(System.in);
			String name = "赵四";
			String address = "象牙山";
			String sex = "男";
			Date date = new Date(System.currentTimeMillis());
			int age = 22;
			String email = "124578@163.com";
			String sql = "insert into stu(sname,saddress,sex,sborndate,sage,semail) values('"+name +"','"+ address+"', '"+ sex+"','"+ date+"', "+ age+",'"+ email+"')";
			
			int result = statement.executeUpdate(sql);
			
			if(result == 1) {
				System.out.println("插入成功");
			}else {
				System.out.println("插入失败");
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

3.删除

根据id进行删除

package com.qfedu.test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class TestDeleteByID {
	public static void main(String[] args) {
		Connection conn =  null;
		Statement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
			
			statement = conn.createStatement();
			
			Scanner input = new Scanner(System.in);
			System.out.println("请输入你要删除的编号");
			int sid = input.nextInt();
			
			String sql = "delete from stu where sid =" + sid; 
			
			int resultRowCount = statement.executeUpdate(sql);
			if(resultRowCount == 1) {
				System.out.println("删除成功");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}					
		}
	}
}

根据name进行删除

package com.qfedu.test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class TestDeleteByName {
	public static void main(String[] args) {
		Connection conn =  null;
		Statement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");

			statement = conn.createStatement();

			Scanner input = new Scanner(System.in);
			System.out.println("请输入你要删除的名字");
			String name  = input.next();


			String sql = "delete from stu where sname ='" + name + "'" ;
			int resultRowCount = statement.executeUpdate(sql);
			if(resultRowCount == 1) {
				System.out.println("删除成功");
			}else {
				System.out.println("删除失败");
			}

		}catch(Exception e){

		}
	}
}

4.修改

package com.qfedu.test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class TestUpdateByID {
	public static void main(String[] args) {
		Connection conn =  null;
		Statement statement = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
			
			statement = conn.createStatement();
			Scanner input = new Scanner(System.in);
			System.out.println("请输入你要修改谁？");
			String oldName = input.next();
			System.out.println("请输入你要改为什么名字");
			String newName = input.next();
			String sql = "update stu set sname = '" + newName + "'" + "where sname ='" + oldName + "'";
			
			int result = statement.executeUpdate(sql);
			if(result == 1) {
				System.out.println("修改成功");
			}else {
				System.out.println("修改失败");
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//关闭资源
		}
	}
}

5.登录

package com.qfedu.test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class TestLogin {
	public static void main(String[] args) {
		// 登录  输入用户名 +  密码
		
		Connection conn =  null;
		Statement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
			statement = conn.createStatement();
			Scanner input = new Scanner(System.in);
			System.out.println("请输入用户名");
			// 因为需要拼接sql语句 所以使用 nextLine 获取一整行输入的内容
			String name = input.nextLine();
			System.out.println(name);
			System.out.println("请输入密码");
			String pwd = input.nextLine();
			
			String sql = "select * from stu where sname ='" + name + "' and pwd ='" + pwd + "'"; 
			ResultSet rs = statement.executeQuery(sql);
			boolean flag = false;
			while(rs.next()) {
				flag = true;
			}
			
			
			System.out.println(flag ==true ? "登录成功" : "登录失败");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
}

三、优化增删改查

1.PreparedStatement

之前我们使用Statement对象操作sql语句可以实现功能但是有一些弊端

1.在传输参数的时候我们需要加很多单引号

2.这种方式不能避免 sql注入对系统有很大的安全隐患

所以我们换一种方式来实现对数据库的操作

JDK提供的有另外一个接口 PreparedStatement 预编译的sql执行对象

PreparedStatement属于Statement的子接口

查询优化

package com.qfedu.test1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import com.mysql.jdbc.Driver;

/**
 * 	之前我们使用Statement对象操作sql语句 可以实现功能 但是有一些弊端
 * 	1.在传输参数的时候 我们需要加很多单引号 
 * 	2.这种方式不能避免 sql注入 对系统有很大的安全隐患
 * 	所以我们换一种方式来实现对数据库的操作 
 * 	JDK提供的有另外一个接口 PreparedStatement 预编译的sql执行对象
 * 	PreparedStatement属于Statement的子接口 
 * @author WHD
 *
 */
public class TestQuery {
	public static void main(String[] args) {
		try {
			// 加载驱动  加载当前类到JVM虚拟机中 
//			Drvier d = new Driver();
			Class.forName("com.mysql.jdbc.Driver");
			
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
			Scanner input = new Scanner(System.in);
			System.out.println("请输入你要查询的名字");
			String name = input.nextLine();
			// 占位符 占一个位置 用于给还未传入的参数 占定一个位置 等待我们在执行sql之前将参数传入 
			String sql = "select * from stu where sname = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			
			
			System.out.println(ps);
			
			ps.setString(1, name);
			
			System.out.println(ps);
			
			
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
//				rs.getInt(1);
				System.out.println(rs.getInt("sid") 
						+ "\t" + rs.getString(2) + "\t" 
						+ rs.getString("saddress") 
						+ rs.getString("sex") 
						+ "\t" + rs.getDate("sborndate")+ "\t"
						+ rs.getInt("sage") + "\t"
						+ rs.getString("semail")+ "\t"
						+ rs.getString("pwd"));
			}
			
			
			
			
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			// 关闭资源
		}
	}
}

增加优化

package com.qfedu.test1;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
			String sql = "insert into stu(sname,saddress,sex,sborndate,sage,semail,pwd) values(?,?,?,?,?,?,?)";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setString(1, "蔡徐坤");
			ps.setString(2,"篮球场");
			ps.setString(3, "男");
			ps.setDate(4, new Date(System.currentTimeMillis()));
			ps.setInt(5, 17);
			ps.setString(6, "1235@163.com");
			ps.setString(7, "8956zz");
			
			
			int result = ps.executeUpdate();
			System.out.println(result == 1 ? "成功" : "失败");
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			// 关闭资源
		}
	}
}

2.工具类

1.编写一个用户获取连接对象的方法

2.关闭资源的方法

①连接对象

②statement对象

③结果集对象

先进行非null判断

遵循一个先用后关

工具类的编写

package com.qfedu.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 	1.编写一个用户获取连接对象的方法  
 * 	2.关闭资源的方法  
 * @author WHD
 *
 */
public class ConnectionUtil {
	
	/**
	 * 	返回连接对象
	 * @return
	 */
	public static Connection getConn() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	
	/**
	 * 	关闭资源  资源分为三种
	 * 	连接对象 
	 * 	Statement 对象
	 * 	结果集对象
	 */
	public static void closeResource(Connection conn , Statement statement , ResultSet rs) {
		// 先进行非null判断 
		// 遵循一个 先用 后关  
		try {
			if(rs != null) {
				rs.close();
			}
			
			if(statement != null) {
				statement.close();
			}
			
			if(conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}	
}

工具类的测试

查询名字

package com.qfedu.test2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import com.mysql.jdbc.Driver;
import com.qfedu.util.ConnectionUtil;

public class TestQueryUseUtil {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 加载驱动  加载当前类到JVM虚拟机中 
//			Driver d = new Driver();
//			Class.forName("com.mysql.jdbc.Driver");
			
//			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
			
			conn = ConnectionUtil.getConn();
			Scanner input = new Scanner(System.in);
			System.out.println("请输入你要查询的名字");
			String name = input.nextLine();
			// 占位符 占一个位置 用于给还未传入的参数 占定一个位置 等待我们在执行sql之前将参数传入 
			String sql = "select * from stu where sname = ?";
			ps = conn.prepareStatement(sql);
			
			
			System.out.println(ps);
			
			ps.setString(1, name);
			
			System.out.println(ps);
			
			
			rs = ps.executeQuery();
			if(rs.next()) {
//				rs.getInt(1);
				System.out.println(rs.getInt("sid") 
						+ "\t" + rs.getString(2) + "\t" 
						+ rs.getString("saddress") 
						+ rs.getString("sex") 
						+ "\t" + rs.getDate("sborndate")+ "\t"
						+ rs.getInt("sage") + "\t"
						+ rs.getString("semail")+ "\t"
						+ rs.getString("pwd"));
			}
			
		}  catch (SQLException e) {
			e.printStackTrace();
		}finally {
			// 关闭资源
			
			ConnectionUtil.closeResource(conn, ps, rs);
		}
	}
}

增加

package com.qfedu.test2;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.qfedu.util.ConnectionUtil;

public class TestInsertUseUtil {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
//			Class.forName("com.mysql.jdbc.Driver");
//			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/zz206", "root", "9999");
			
			conn  = ConnectionUtil.getConn();
			String sql = "insert into stu(sname,saddress,sex,sborndate,sage,semail,pwd) values(?,?,?,?,?,?,?)";
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, "蔡徐坤");
			ps.setString(2,"篮球场");
			ps.setString(3, "男");
			ps.setDate(4, new Date(System.currentTimeMillis()));
			ps.setInt(5, 17);
			ps.setString(6, "1235@163.com");
			ps.setString(7, "8956zz");
			
			
			int result = ps.executeUpdate();
			System.out.println(result == 1 ? "成功" : "失败");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			// 关闭资源
			ConnectionUtil.closeResource(conn, ps, null);
		}
	}
}

3.实体类

实体类包命名 entity pojo

此包中存放的是和数据库中表一一对应的实体类

每个实体类相当于数据的载体

要求：

1.表名和类名要一致

2.列名和属性名要一致

package com.qfedu.entity;

import java.util.Date;

/**
 * 	实体类 包命名 entity  pojo 
 * 	此包中存放的是和数据库中表一一对应的实体类   
 * 	每个实体类相当于数据的载体  
 * 	要求：
 * 	1.表名和类名要一致
 * 	2.列名和属性名要一致 
 * @author WHD
 *
 */
public class Student {
	private Integer sid;
	private String sName;
	private String sex;
	private Date sBornDate;
	private Integer sAge;
	private String sEmail;
	private String pwd;
	private Integer cityId;
	public Integer getSid() {
		return sid;
	}
	public void setSid(Integer sid) {
		this.sid = sid;
	}
	public String getsName() {
		return sName;
	}
	public void setsName(String sName) {
		this.sName = sName;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getsBornDate() {
		return sBornDate;
	}
	public void setsBornDate(Date sBornDate) {
		this.sBornDate = sBornDate;
	}
	public Integer getsAge() {
		return sAge;
	}
	public void setsAge(Integer sAge) {
		this.sAge = sAge;
	}
	public String getsEmail() {
		return sEmail;
	}
	public void setsEmail(String sEmail) {
		this.sEmail = sEmail;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public Integer getCityId() {
		return cityId;
	}
	public void setCityId(Integer cityId) {
		this.cityId = cityId;
	}
	public Student() {
	}
	public Student(Integer sid, String sName, String sex, Date sBornDate, Integer sAge, String sEmail, String pwd,
			Integer cityId) {
		this.sid = sid;
		this.sName = sName;
		this.sex = sex;
		this.sBornDate = sBornDate;
		this.sAge = sAge;
		this.sEmail = sEmail;
		this.pwd = pwd;
		this.cityId = cityId;
	}
	@Override
	public String toString() {
		return "Student [sid=" + sid + ", sName=" + sName + ", sex=" + sex + ", sBornDate=" + sBornDate + ", sAge="
				+ sAge + ", sEmail=" + sEmail + ", pwd=" + pwd + ", cityId=" + cityId + "]";
	}	
}

4.配置文件

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/zz206?useSSL=false
username = root
password = 9999

工具类连配置文件

package com.qfedu.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 	获取连接对象 改变为从DB_Info.properties 文件中读取连接信息 
 * 	关闭资源  
 * @author WHD
 *
 */
public class ConnectionUtilUseProperties {
	private static final Properties DB_INFO_FILE = new Properties();
	static {
		
		try {
			InputStream is = ConnectionUtilUseProperties.class.getResourceAsStream("/DB_Info.properties");
			DB_INFO_FILE.load(is);
			Class.forName(DB_INFO_FILE.getProperty("driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 	获取连接对象
	 * @return
	 */
	public static Connection getConn() {
		try {
			return DriverManager.getConnection(DB_INFO_FILE.getProperty("url"), DB_INFO_FILE.getProperty("username"), DB_INFO_FILE.getProperty("password"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * 	关闭资源  资源分为三种
	 * 	连接对象 
	 * 	Statement 对象
	 * 	结果集对象
	 */
	public static void closeResource(Connection conn , Statement statement , ResultSet rs) {
		// 先进行非null判断 
		// 遵循一个 先用 后关  
		try {
			if(rs != null) {
				rs.close();
			}
			
			if(statement != null) {
				statement.close();
			}
			
			if(conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}	
	
}

5.Dao层

DAO Data Access Object 对象访问模型

此类中提供只有功能关于某一个表的增删改查功能

此类中只有抽象方法

方法命名

动作 + 对象 + 条件

获取内容 get

删除 remove delete

修改 update modify

新增 add

By + 条件

Dao层类构造

package com.qfedu.dao;

import java.util.List;

import com.qfedu.entity.Student;

/**
 * 	DAO Data Access Object 对象访问模型  
 * 	此类中提供只有功能 关于某一个表的增删改查功能 
 * 	此类中只有抽象方法
 * 	方法命名  
 * 	动作 + 对象 + 条件 
 * 	获取内容 get
 * 	删除 remove  delete 
 * 	修改 update modify 
 * 	新增 add 
 *  By + 条件 
 * @author WHD
 *
 */
public interface StudentDao {
	Student getStudentByName(String sName);//查询学生姓名
	
	List<Student> getAllStudent();//获取所有学生信息
	
	int addStudent(Student stu);//增加一行学生信息
	
	int deleteStudentById(Integer sId);	//根据id删除一行学生信息
}

Dao层类构造的增加

package com.qfedu.dao;

import java.util.List;

import com.qfedu.entity.Student;

/**
 * 	DAO Data Access Object 对象访问模型  
 * 	此类中提供只有功能 关于某一个表的增删改查功能 
 * 	此类中只有抽象方法
 * 	方法命名  
 * 	动作 + 对象 + 条件 
 * 	获取内容 get
 * 	删除 remove  delete 
 * 	修改 update modify 
 * 	新增 add 
 *  By + 条件 
 * @author WHD
 *
 */
public interface StudentDao {
	Student getStudentByName(String sName);
	
	List<Student> getAllStudent();
	
	int addStudent(Student stu);
	
	int deleteStudentById(Integer sId);
	
	List<Student> getStudentByCityId(Integer cityId);	
}
public interface CityDao {
	int deleteCityById(Integer cityId);
}

Dao层类实现

package com.qfedu.dao.impl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.qfedu.dao.StudentDao;
import com.qfedu.entity.Student;
import com.qfedu.util.ConnectionUtil;
import com.qfedu.util.ConnectionUtilUseProperties;

public class StudentDaoImpl implements StudentDao{
	//查询学生姓名
	@Override
	public Student getStudentByName(String sName) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtilUseProperties.getConn();
			// 占位符 占一个位置 用于给还未传入的参数 占定一个位置 等待我们在执行sql之前将参数传入 
			String sql = "select * from student where sname = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, sName);

			rs = ps.executeQuery();
			Student stu = new Student();
			if(rs.next()) {
				stu.setSid(rs.getInt("sid")); 
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
			}
			return stu;
		}  catch (SQLException e) {
			e.printStackTrace();
		}finally {
			// 关闭资源
			ConnectionUtil.closeResource(conn, ps, rs);
		}
		return null;
	}
    
	//获取所有学生信息
	@Override
	public List<Student> getAllStudent() {
		List<Student> list = new ArrayList<Student>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = ConnectionUtilUseProperties.getConn();
			ps = conn.prepareStatement("select * from student");
			rs = ps.executeQuery();
			while(rs.next()) {
				Student stu = new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
				list.add(stu);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			ConnectionUtilUseProperties.closeResource(conn, ps, rs);
		}
		return null;
	}
    
	//增加一行学生信息
	@Override
	public int addStudent(Student stu) {
		Connection conn = ConnectionUtilUseProperties.getConn();
		String sql = "insert into student(sname,sex,sborndate,sage,semail,pwd,cityid) values(?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu.getsName());
			ps.setString(2, stu.getSex());
			ps.setDate(3, new Date(stu.getsBornDate().getTime()));
			ps.setInt(4, stu.getsAge());
			ps.setString(5, stu.getsEmail());
			ps.setString(6, stu.getPwd());
			ps.setInt(7, stu.getCityId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			ConnectionUtilUseProperties.closeResource(conn, ps, null);
		}
		return 0;
	}

    //根据id删除一行学生信息
	@Override
	public int deleteStudentById(Integer sId) {
		Connection conn = ConnectionUtilUseProperties.getConn();
		String sql = "delete from student where sid = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, sId);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			ConnectionUtilUseProperties.closeResource(conn, ps, null);
		}
		return 0;
	}

}

Dao层类构造增加的实现

学生

package com.qfedu.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qfedu.dao.BaseDao;
import com.qfedu.dao.StudentDao;
import com.qfedu.entity.Student;

public class StudentDaoImpl extends BaseDao  implements StudentDao{

	@Override
	public Student getStudentByName(String sName) {
		// 占位符 占一个位置 用于给还未传入的参数 占定一个位置 等待我们在执行sql之前将参数传入 
		String sql = "select * from student where sname = ?";
		Object [] obj = {sName};
		ResultSet rs = this.getData(sql, new Object[] {sName});
		Student stu = new Student();
		try {
			if(rs.next()) {
				stu.setSid(rs.getInt("sid")); 
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return stu;
	}

	@Override
	public List<Student> getAllStudent() {
		List<Student> list = new ArrayList<Student>();
		try {
			String sql = "select * from student";
			ResultSet rs = this.getData(sql);
			while(rs.next()) {
				Student stu = new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
				list.add(stu);
			}
		}catch(SQLException e)  {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return list;
	}

	@Override
	public int addStudent(Student stu) {
		String sql;
		try {
			sql = "insert into student(sname,sex,sborndate,sage,semail,pwd,cityid) values(?,?,?,?,?,?,?)";
			Object[] obj={stu.getsName(),stu.getSex(),stu.getsBornDate(),stu.getsAge(),stu.getsEmail(),stu.getPwd(),stu.getCityId()};
			return this.modifyData(sql, obj);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return 0;
	}

	@Override
	public int deleteStudentById(Integer sId) {
		try {
			String sql = "delete from student where sid = ?";
			return this.modifyData(sql, new Object[] {sId});
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return 0;
	}

	@Override
	public List<Student> getStudentByCityId(Integer cityId) {
		String sql = "select * from student where cityid = ?";
		ResultSet rs = this.getData(sql, new Object[] {cityId});
		List<Student> list = new ArrayList<Student>();
		try {
			while(rs.next()) {
				Student stu = new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
				list.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return list;
	}

}

城市

package com.qfedu.dao.impl;

import com.qfedu.dao.BaseDao;
import com.qfedu.dao.CityDao;

public class CityDaoImpl extends BaseDao implements CityDao{

	@Override
	public int deleteCityById(Integer cityId) {
		int result = 0;
		try {
			String sql = "delete from city where cid = ?";
			result = this.modifyData(sql, new Object[] {cityId});
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return result;
	}

}

Dao层类的父类

本类作为dao类的基类也就是父类

本类中提供两个方法

1.查询的方法 executeQuery();

2.增删改查的方法 executeUpdate();

分析不管是什么情况下的查询都需要sql语句也需要参数但是参数的个数、类型是不同所以我们可以编写一个用于

所有条件的查询的方法从而简化我们dao实现类的代码所有的查询都会返回一个 ResultSet结果集

package com.qfedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.qfedu.util.ConnectionUtilUseProperties;

/**
 * 	本类作为dao类的基类  也就是 父类 
 * 	本类中提供两个方法
 * 	1.查询的方法  		executeQuery();
 * 	2.增删改查的方法 	executeUpdate();
 * 	分析 不管是什么情况下的查询 都需要sql语句 也需要参数 但是参数的个数、类型是不同 所以我们可以编写一个用于
 * 	所有条件的查询的方法 从而简化我们dao实现类的代码  所有的查询都会返回一个 ResultSet结果集
 * @author WHD
 *
 */
public class BaseDao {
	
	public ResultSet getData(String sql , Object ...args) {
		Connection conn = ConnectionUtilUseProperties.getConn();
		ResultSet rs  = null;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			// 以上代码我们获取到一个 ps 预编译执行sql对象 
			// 1. sql语句不需要传入 任何的参数 比如  select * from student
			// 2. sql语句需要传入不确定的参数  
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	
	
	
	
}

Dao层类父类的优化

package com.qfedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.qfedu.util.ConnectionUtilUseProperties;

/**
 * 	本类作为dao类的基类  也就是 父类 
 * 	本类中提供两个方法
 * 	1.查询的方法  		executeQuery();
 * 	2.增删改的方法 	executeUpdate();
 * 	分析 不管是什么情况下的查询 都需要sql语句 也需要参数 但是参数的个数、类型是不同 所以我们可以编写一个用于
 * 	所有条件的查询的方法 从而简化我们dao实现类的代码  所有的查询都会返回一个 ResultSet结果集
 * @author WHD
 *
 */
public class BaseDao {
	private Connection conn = null;
	private ResultSet rs = null;
	PreparedStatement ps = null;
	public ResultSet getData(String sql , Object ...args) {
		conn = ConnectionUtilUseProperties.getConn();
		rs = null;
		try {
			ps = conn.prepareStatement(sql);
			// 以上代码我们获取到一个 ps 预编译执行sql对象 
			// 1. sql语句不需要传入 任何的参数 比如  select * from student
			// 2. sql语句需要传入不确定的参数  
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	
	public int modifyData(String sql , Object [] args) {
		conn = ConnectionUtilUseProperties.getConn();
		int result = 0;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			 result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}	
	public void closeResource() {
		ConnectionUtilUseProperties.closeResource(conn, ps, rs);
	}
	
}

Dao层类实现的优化

继承Dao的父类BaseDao

package com.qfedu.dao.impl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qfedu.dao.BaseDao;
import com.qfedu.dao.StudentDao;
import com.qfedu.entity.Student;
import com.qfedu.util.ConnectionUtil;
import com.qfedu.util.ConnectionUtilUseProperties;

public class StudentDaoImpl extends BaseDao  implements StudentDao{

	@Override
	public Student getStudentByName(String sName) {
		// 占位符 占一个位置 用于给还未传入的参数 占定一个位置 等待我们在执行sql之前将参数传入 
		String sql = "select * from student where sname = ?";
		Object [] obj = {sName};
		ResultSet rs = this.getData(sql, new Object[] {sName});
		Student stu = new Student();
		try {
			if(rs.next()) {
				stu.setSid(rs.getInt("sid")); 
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stu;
	}

	@Override
	public List<Student> getAllStudent() {
		List<Student> list = new ArrayList<Student>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = ConnectionUtilUseProperties.getConn();
			ps = conn.prepareStatement("select * from student");
			rs = ps.executeQuery();
			while(rs.next()) {
				Student stu = new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
				list.add(stu);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			ConnectionUtilUseProperties.closeResource(conn, ps, rs);
		}
		return null;
	}

	@Override
	public int addStudent(Student stu) {
		Connection conn = ConnectionUtilUseProperties.getConn();
		String sql = "insert into student(sname,sex,sborndate,sage,semail,pwd,cityid) values(?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu.getsName());
			ps.setString(2, stu.getSex());
			ps.setDate(3, new Date(stu.getsBornDate().getTime()));
			ps.setInt(4, stu.getsAge());
			ps.setString(5, stu.getsEmail());
			ps.setString(6, stu.getPwd());
			ps.setInt(7, stu.getCityId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			ConnectionUtilUseProperties.closeResource(conn, ps, null);
		}
		return 0;
	}

	@Override
	public int deleteStudentById(Integer sId) {
		Connection conn = ConnectionUtilUseProperties.getConn();
		String sql = "delete from student where sid = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, sId);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			ConnectionUtilUseProperties.closeResource(conn, ps, null);
		}
		return 0;
	}

}

Dao层类实现的进一步优化

package com.qfedu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qfedu.dao.BaseDao;
import com.qfedu.dao.StudentDao;
import com.qfedu.entity.Student;
import com.qfedu.util.ConnectionUtil;
import com.qfedu.util.ConnectionUtilUseProperties;

public class StudentDaoImpl extends BaseDao  implements StudentDao{

	@Override
	public Student getStudentByName(String sName) {
		// 占位符 占一个位置 用于给还未传入的参数 占定一个位置 等待我们在执行sql之前将参数传入 
		String sql = "select * from student where sname = ?";
		Object [] obj = {sName};
		ResultSet rs = this.getData(sql, new Object[] {sName});
		Student stu = new Student();
		try {
			if(rs.next()) {
				stu.setSid(rs.getInt("sid")); 
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return stu;
	}

	@Override
	public List<Student> getAllStudent() {
		List<Student> list = new ArrayList<Student>();
		try {
			String sql = "select * from student";
			ResultSet rs = this.getData(sql);
			while(rs.next()) {
				Student stu = new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setsName(rs.getString(2));  
				stu.setSex(rs.getString("sex")); 
				stu.setsBornDate(rs.getDate("sborndate"));
				stu.setsAge(rs.getInt("sage"));
				stu.setsEmail(rs.getString("semail"));
				stu.setPwd(rs.getString("pwd"));
				stu.setCityId(rs.getInt("cityid"));
				list.add(stu);
			}
		}catch(SQLException e)  {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return list;
	}

	@Override
	public int addStudent(Student stu) {
		String sql;
		try {
			sql = "insert into student(sname,sex,sborndate,sage,semail,pwd,cityid) values(?,?,?,?,?,?,?)";
			Object[] obj={stu.getsName(),stu.getSex(),stu.getsBornDate(),stu.getsAge(),stu.getsEmail(),stu.getPwd(),stu.getCityId()};
			return this.modifyData(sql, obj);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return 0;
	}

	@Override
	public int deleteStudentById(Integer sId) {
		try {
			String sql = "delete from student where sid = ?";
			return this.modifyData(sql, new Object[] {sId});
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeResource();
		}
		return 0;
	}

}

Dao层类的测试

package com.qfedu.test;

import java.util.Date;
import java.util.List;

import com.qfedu.dao.StudentDao;
import com.qfedu.dao.impl.StudentDaoImpl;
import com.qfedu.entity.Student;

public class Test {
	public static void main(String[] args) {
		StudentDao sd = new StudentDaoImpl();
		Student stu = sd.getStudentByName("赵四");
		System.out.println(stu);
//		System.out.println("========================");
//		List<Student> list = sd.getAllStudent();
//		System.out.println(list);
//		
//		
//		int result = sd.addStudent(new Student("建国", "女", new Date(),18, "12dsahdsa@163.com", "xxn8956", 2));
//		System.out.println(result);
//		
		
//		System.out.println(sd.deleteStudentById(3));
		
	}
}

server层

server层的编写

为什么我们要编写一个service层

mvc模式不属于设计模式是前辈长期开发过程中总结出来的一套用于web开发的经验流程

m model 数据模型层 dao层 dao data access object(对象访问模型) 负责每个表的增删改查

service层不属于mvc层内直接指定的一层但是实际开发都会有主要用于业务逻辑的处理

v view 视图层 (jsp,html) 展示数据给用户看的将dao层获取的数据显示在页面上但是不是直接去dao层获取

c control 控制器 (servlet,controller)

package com.qfedu.service;

import java.util.List;

import com.qfedu.entity.Student;

/**
 * 	为什么我们要编写一个service层	
 * 	mvc模式 不属于设计模式 是前辈长期开发过程中总结出来的一套用于web开发的经验流程 
 * 	m model 数据模型层 dao层  dao  data access object(对象访问模型)  负责每个表的增删改查
 * 	service层不属于mvc层内直接指定的一层  但是实际开发都会有  主要用于业务逻辑的处理 
 * 	
 * 	v view  视图层 (jsp,html) 展示数据 给用户看的 将dao层获取的数据显示在页面上 但是不是直接去dao层获取
 * 	c control  控制器 (servlet,controller) 
 * @author WHD
 *
 */

public interface StudentService {
	boolean deleteCityAndGetStudent(Integer cityId);

	Student getStudentByName(String sName);

	List<Student> getAllStudent();

	int addStudent(Student stu);

	int deleteStudentById(Integer sId);

	List<Student> getStudentByCityId(Integer cityId);




}

异常

序列化是用于保证序列化和反序列的唯一标识·

package com.qfedu.exception;

public class RemoveCityException extends RuntimeException{
	// 序列化是用于保证序列化和反序列的唯一标识·
	private static final long serialVersionUID = 1L;

	public RemoveCityException(String message) {
		super(message);
	}
}

server层的实现

第一种实现方式使用boolean类型作为返回值

第二种实现方式如果不能删除抛出一个异常

package com.qfedu.service.impl;

import java.util.List;

import com.qfedu.dao.CityDao;
import com.qfedu.dao.StudentDao;
import com.qfedu.dao.impl.CityDaoImpl;
import com.qfedu.dao.impl.StudentDaoImpl;
import com.qfedu.entity.Student;
import com.qfedu.exception.RemoveCityException;
import com.qfedu.service.StudentService;

public class StudentServiceImpl implements StudentService{

	StudentDao sd = new StudentDaoImpl();
	CityDao cd = new CityDaoImpl();
		
	@Override
	public boolean deleteCityAndGetStudent(Integer cityId) {
		// 第一种实现方式 使用boolean类型作为返回值
//		List<Student> list = sd.getStudentByCityId(cityId);
//		int result = 0;
//		if(list.size() == 0) {
//			result = cd.deleteCityById(cityId);
//		}
//		if(result != 0) {
//			return true;
//		}
//		return false;
		// 第二种实现方式 如果不能删除 抛出一个异常 
		List<Student> list = sd.getStudentByCityId(cityId);
		
		if(list.size() != 0) {
			throw new RemoveCityException("删除城市下还有学生");
		}
		int result = 0;
		result = cd.deleteCityById(cityId);
		return result != 0;
	}

	@Override
	public Student getStudentByName(String sName) {
		return sd.getStudentByName(sName);
	}

	@Override
	public List<Student> getAllStudent() {
		return sd.getAllStudent();
	}

	@Override
	public int addStudent(Student stu) {
		return sd.addStudent(stu);
	}

	@Override
	public int deleteStudentById(Integer sId) {
		return sd.deleteStudentById(sId);
	}

	@Override
	public List<Student> getStudentByCityId(Integer cityId) {
		return sd.getStudentByCityId(cityId);
	}

}

测试类

junit

@Test必写

package com.qfedu.test;

import java.util.List;

import org.junit.Test;

import com.qfedu.entity.Student;
import com.qfedu.service.StudentService;
import com.qfedu.service.impl.StudentServiceImpl;

/**
 * 	右键 new一个 junit测试用例   单元测试
 * @author WHD
 *
 */
public class MyTest {
	StudentService ss = new StudentServiceImpl();
	@Test
	public void test1() {
		List<Student> list = ss.getAllStudent();
		System.out.println(list);
	}
	
	@Test
	public void test2() {
		Student stu = ss.getStudentByName("赵四");
		System.out.println(stu);
	}	
	@Test
	public void test3() {
		
	}

}

happyInHappy

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
JDBC逐步简化

JDBC一、定义JDBC Java DataBase Connectivity java数据库连接首先是由Java提供的一套接口不同的数据库厂商去实现这些接口从而实现和数据库的连接接口 java.sql包中反射通过包名 + 类名来获取一个类类型的对象必须使用与之对应的数据库厂商提供的相关的类 jar二、增删改查1.步骤1.导入jar包2.使用反射的方式加载驱动3.通过驱动管理类获取一个连接对象4.有了连接对象还需要操作sql的对象5.编写sql
复制链接

扫一扫