课程笔记
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);
}
}
}