BD

package com.rzk.utils;


import com.rzk.pojo.Student;

import javax.management.openmbean.OpenMBeanConstructorInfo;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * dao工具类
 * 这个类封装的方法：
 * 1.获取数据库连接 的方法
 * 2.关闭连接，释放资源的方法
 * 3.设置参数的方法
 * 4.增删改的方法
 * 5.查询的方法
 */
public class BaseDao {
    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    private static final String URL="jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String USERNAME="system";
    private static final String PWD="RZKruizhukai123";
    static Connection conn = null;
    static PreparedStatement pstmt = null;
    static ResultSet rs = null;
    static Student student = null;
    //ͨ�õ��������ݿⷽ��
    public static Connection getConnection() {
        try {
            Class.forName(DRIVER);
            conn  = DriverManager.getConnection(URL,USERNAME,PWD);
            return conn;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }



    public static void setParams(PreparedStatement pstmt, Object[] params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
        }
    }




   /**
    * @param sql
    * @param params
    * @return
    */
   public static Integer executeUpdate(String sql, Object[] params) throws SQLException {
       try {
           conn.setAutoCommit(false);
           //连接数据库
           conn = BaseDao.getConnection();
           //创建预处理命令
           pstmt = conn.prepareStatement(sql);
           //为预处理命令传递参数
           BaseDao.setParams(pstmt, params);
           //执行增删改

           Integer count = pstmt.executeUpdate();
           conn.commit();
           return count;
       } catch (SQLException e) {
           conn.rollback();
           e.printStackTrace();
       } finally {
           BaseDao.Close(conn, pstmt, null);
       }
       return null;
   }


   //释放资源
   public static boolean Close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
       boolean flag = true;
       if (rs != null) {
           try {
               rs.close();
               rs = null;
           } catch (SQLException e) {
               e.printStackTrace();
               flag = false;
           }
       }
       if (pstmt != null) {
           try {
               pstmt.close();
               pstmt = null;
           } catch (SQLException e) {
               e.printStackTrace();
               flag = false;
           }
       }
       if (conn != null) {
           try {
               conn.close();
               conn = null;
           } catch (SQLException e) {
               e.printStackTrace();
               flag = false;
           }
       }
       return flag;
   }


   public static  List<Map<String, Object>>  executeQuery(String sql, Object[]... params) {
//       List<Map> list = new ArrayList<Map>();
       List<Map<String,Object>>list = new ArrayList<>();
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;

       try {
           //连接数据库
           conn = BaseDao.getConnection();
           //创建预处理命令
           pstmt = conn.prepareStatement(sql);
           //为预处理命令设置参数
           BaseDao.setParams(pstmt, params);
           //执行查询
           rs = pstmt.executeQuery();
           //获取元数据
           ResultSetMetaData metaData = rs.getMetaData();
           //获取返回的结果集有多少列
           int count = metaData.getColumnCount();
           while (rs.next()) {
               //声明map  存储一行的数据
               Map map = new HashMap();
               for (int i = 1; i <= count; i++) {
                   //获取列名 列名的下标从1 开始
                    String columnName = metaData.getColumnName(i);
                   // 获取该列的值
                    Object value = rs.getObject(columnName);
                   //把该行每一列的值放置到map中，其中key是列名 value是该列对应的值


                   map.put(metaData.getColumnName(i), rs.getObject(i));
               }
               list.add(map);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           BaseDao.Close(conn, pstmt, rs);
       }
       return list;
   }

   public static void main(String[] args) throws SQLException {
       String sql = "select * from student";
       List<Map<String, Object>> maps = BaseDao.executeQuery(sql, null);
       for (Map map : maps) {
           System.out.println(map);
       }

   }


}




-----------------------------------------------------------


public interface StudentDao {
    //查询所有学生<很多学生>
    //查询
    public List<Student> queryAllStudents();
    //查询单个学生
    public Student queryStudentBySno(int sno) throws SQLException;
}




public class StudentDaoImpl implements StudentDao {

    @Override
    public List<Student> queryAllStudents() {
        String sql = "select sno,sname,sage,saddress from student";
//        Object[] params = {};

        List<Map<String, Object>> maps = BaseDao.executeQuery(sql, null);
        List<Student> list = new ArrayList<Student>();
        for (Map map : maps) {
            Student student = new Student();
            student.setSno(Integer.valueOf(map.get("SNO").toString()));
            student.setSname(String.valueOf(map.get("SNAME").toString()));
            student.setSage(Integer.valueOf(map.get("SAGE").toString()));
            student.setSaddress(map.get("SADDRESS").toString());
            list.add(student);
        }
//        for (Map map : maps) {
//            Student student = new Student();
//            student.setSno(Integer.valueOf(map.get("SNO").toString()));
//
//            list.add(student);
//        }
        return list;
    }

    @Override
    public Student queryStudentBySno(int sno) {
        String sql = "select * from student where sno = ?";
        Object[] params = {sno};
        List<Map<String, Object>> result =   BaseDao.executeQuery(sql,params);
        Student student = new Student();

        // 根据id 查询 for 只执行一次
        for (Map<String,Object> map:result){
            System.out.println(map);
        }
        return student;
    }

  public static void main(String[] args) throws SQLException {
//        String sql = "select * from student where sno=?";
//        Object[] params = {3};
//        List<Map<String, Object>> list = BaseDao.executeQuery(sql, params);
//        for (Map<String, Object> map : list) {
//            System.out.println(map);
//        }
//        String sql = "select * from student";
////        Object[] params = {3};
//        List<Map<String, Object>> list = BaseDao.executeQuery(sql, null);
//        for (Map<String, Object> map : list) {
//            System.out.println(map);
////        }
      StudentDao studentDao = new StudentDaoImpl();
//      List<Student> students = studentDao.queryAllStudents();
//      for (Student student : students) {
//          System.out.println(student);
//      }
      boolean b = studentDao.insertStudent(new Student(99, "民航", 4, "ddd"));
      if (b=true){
          System.out.println("插入成功");
      }


  }
----------------------------------
public interface StudentService {
    //查询全部学生
    public List<Student> queryAllStudents();

    //插入學生
    boolean updateStudent(Student student);
}

public class StudentServiceImpl implements StudentService {
    StudentDaoImpl studentDaoImpl = new StudentDaoImpl();

    @Override
    public List<Student> queryAllStudents(){
        return studentDaoImpl.queryAllStudents();

    }
}


------------------------------
@WebServlet(urlPatterns = "/queryAllStudent")
public class QueryAllStudentServlet  extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        StudentService studentService = new StudentServiceImpl();
        List<Student> students = studentService.queryAllStudents();
        System.out.println(students);
        req.setAttribute("students",students);
        req.getRequestDispatcher("studentAll.jsp").forward(req,resp);

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       doGet(req, resp);
    }
}
-------------------------------------
<%--
  Created by IntelliJ IDEA.
  User: asus
  Date: 2020/3/25
  Time: 20:58
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <table border="1px">
        <thead>
            <tr>
                <td>sno</td>
                <td>saddress</td>
                <td>sname</td>
                <td>sage</td>
                <td></td>
            </tr>
        </thead>
        <c:forEach items="${students}" var="student">
            <tr>
                <td><c:out value="${student.sno}"/></td>
                <td><c:out value="${student.saddress}"/></td>
                <td><c:out value="${student.sname}"/></td>
                <td><c:out value="${student.sage}"/></td>
                <td><a href="personal.jsp">编辑</a></td>
                <td><a href="personal.jsp">删除</a></td>
            </tr>

        </c:forEach>
    </table>

</body>
</html>