学习笔记之jdbc模板

课程笔记

ConnectionManager

package jmu.db;

import java.sql.*;
public class ConnectionManager {

	private static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
	private static final String DATABASE_URL = "jdbc:mysql://127.0.0.1:3306/test? characterEncoding=utf8 & useSSL=false & serverTimezone=UTC & rewriteBatchedStatements=true";
	private static final String DATABASE_USRE = "root";
	private static final String DATABASE_PASSWORD = "24365769";

	/** 返回连接 */
	public static Connection getConnection() {
		Connection dbConnection = null;
		try {
			Class.forName(DRIVER_CLASS);
			dbConnection = DriverManager.getConnection(DATABASE_URL,
					DATABASE_USRE, DATABASE_PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return dbConnection;
	}

	/** 关闭连接*/
	public static void closeConnection(Connection dbConnection) {
		try {
			if (dbConnection != null && (!dbConnection.isClosed())) {
				dbConnection.close();
			}
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		}

	}

	/**关闭结果集*/
	public static void closeResultSet(ResultSet res) {
		try {
			if (res != null) {
				res.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/** 关闭语句*/
	public static void closeStatement(PreparedStatement pStatement) {
		try {
			if (pStatement != null) {
				pStatement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void closeStatement(Statement statement) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

StudentsDB6

package jmu.db;

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

import jmu.vo.Students;

public class StudentsDB6 {
	public List<Students> getAllStudents() {
		List<Students> allStudents = new ArrayList<Students>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = ConnectionManager.getConnection();
			String sql = "select * from students";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			Students stu = null;
			while (rs.next()) {
				String studentId = rs.getString(1);
				String studentName = rs.getString(2);
				String studentPwd = rs.getString(3);
				String sex = rs.getString(4);
				int classId = rs.getInt(5);
				stu = new Students(studentId, studentName, studentPwd, sex, classId);
				allStudents.add(stu);

			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}

		return allStudents;
	}

	public int deleteStudents(String studentId) {
		int row = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ConnectionManager.getConnection();
			String sql = "delete from students where studentId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentId);
			row = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}
		return row;
	}

	public Students getStuById(String studentId) {
		Students stu = null;
		Connection con = null;
		PreparedStatement pStatement = null;
		ResultSet resSet = null;
		try {
			con = ConnectionManager.getConnection(); // 1.创建连接
			String strSql = "select * from students where studentId=?";
			pStatement = con.prepareStatement(strSql); // 2.创建预处理语句总管
			pStatement.setString(1, studentId);
			resSet = pStatement.executeQuery(); // 3.语句总管执行SQL语句
			if (resSet.next()) {
				String studentName = resSet.getString(2);
				String studentPwd = resSet.getString(3);
				String sex = resSet.getString(4);
				int classId = resSet.getInt(5);
				stu = new Students(studentId, studentName, studentPwd, sex, classId);
			}
		} catch (SQLException sqlE) {
			sqlE.printStackTrace();
		} finally {
			ConnectionManager.closeStatement(pStatement);// 4.关闭语句总管、连接
			ConnectionManager.closeConnection(con);
		}
		return stu;
	}

	public int updateStudents(Students stu) {
		int row = 0;
		Connection con = null;
		PreparedStatement pStatement = null;
		try {
			con = ConnectionManager.getConnection(); // 1.创建连接
			String strSql = "update students set studentName=?,studentPwd=?,sex=?,classId=? where studentId=?";
			pStatement = con.prepareStatement(strSql); // 2.创建预处理语句总管
			pStatement.setString(1, stu.getStudentName());
			pStatement.setString(2, stu.getStudentPwd());
			pStatement.setString(3, stu.getSex());
			pStatement.setInt(4, stu.getClassId());
			pStatement.setString(5, stu.getStudentId());
			row = pStatement.executeUpdate(); // 3.语句总管执行SQL语句
		} catch (SQLException sqlE) {
			sqlE.printStackTrace();
		} finally {
			ConnectionManager.closeStatement(pStatement);// 4.关闭语句总管、连接
			ConnectionManager.closeConnection(con);
		}
		return row;
	}
	public int insertStudents(Students stu){
    	int flag=0;
        Connection con = null;
	    PreparedStatement pStatement = null;
	    try {
	      con = ConnectionManager.getConnection();  //1.创建连接
	      String strSql = "insert into students values(?,?,?,?,?)";  
	      pStatement = con.prepareStatement(strSql);  //2.创建预处理语句总管
	      pStatement.setString(1, stu.getStudentId());
	      pStatement.setString(2, stu.getStudentName());
	      pStatement.setString(3, stu.getStudentPwd());
	      pStatement.setString(4, stu.getSex());
	      pStatement.setInt(5, stu.getClassId());
	      flag=pStatement.executeUpdate();  //3.语句总管执行SQL语句
	    } catch (SQLException sqlE) {
	       sqlE.printStackTrace();
	    } finally {
	       ConnectionManager.closeStatement(pStatement);//4.关闭语句总管、连接
	       ConnectionManager.closeConnection(con);
	    }
	    return flag;
    }
	public List<Students> getStuByLike(String studentId,String studentName,String className){
		List<Students> stuLike = new ArrayList<Students>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionManager.getConnection();
			String sql="select studentId,studentName,studentPwd,sex,students.classId from students,classes where classes.classId=students.classId";
			String appndsql = "";
			if(!studentId.equals(""))
				appndsql=appndsql+" and studentId like '%"+studentId+"%'";
			if(!studentName.equals(""))
				appndsql=appndsql+" and studentName like '%"+studentName+"%'";
			if(!className.equals(""))
				appndsql=appndsql+" and classes.className like '%"+className+"%'";
			sql=sql+appndsql;
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			Students stu = null;
			while (rs.next()) {
				String stuId = rs.getString(1);
				String stuName = rs.getString(2);
				String stuPwd = rs.getString(3);
				String sex = rs.getString(4);
				int classId = rs.getInt(5);
				stu = new Students(stuId, stuName, stuPwd, sex, classId);
				stuLike.add(stu);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closeStatement(pstmt);
			ConnectionManager.closeConnection(conn);
		}
		return stuLike;
	}
	public static void main(String[] args) {
		StudentsDB6 db6 = new StudentsDB6();
		List<Students> list=db6.getStuByLike("", "", "软件1711");
		for(Students s : list) {
			System.out.println(s);
		}
	}
}