JDBC逐步简化

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() { } }