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>
