作业二

     基于java+mysql实现JDBC对学生信息进行增删改查

 连接数据库

# db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/menagerie?serverTimezone=UTC
jdbc.user=root
jdbc.password=0000
initSize=1
maxActive=2

工具类DBUtil（对jdbc进行封装）

package util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

/**
* 连接池版本的数据库连接管理工具类
* 适合于并发场合
* @author LvChaoZhang
*
*/
public class DbUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
private static int initSize;
private static int maxActive;
private static BasicDataSource bs;
static {
//连接池
bs=new BasicDataSource();
Properties cfg=new Properties();
try {
InputStream in =DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
cfg.load(in);
//初始化参数
driver=cfg.getProperty("jdbc.driver");
url=cfg.getProperty("jdbc.url");
username=cfg.getProperty("jdbc.user");
password=cfg.getProperty("jdbc.password");
initSize=Integer.parseInt(cfg.getProperty("initSize"));
maxActive=Integer.parseInt(cfg.getProperty("maxActive"));
in.close();
//初始化连接池
bs.setDriverClassName(driver);
bs.setUrl(url);
bs.setUsername(username);
bs.setPassword(password);
bs.setInitialSize(initSize);
bs.setMaxActive(maxActive);
} catch (Exception e) {
throw new RuntimeException(e);
}
}

public static Connection getConnection() {
try {
//getConnection()从连接池中获取重用的连接，如果连接池满了，则等待，如果有连接归还，则获取重用的连接
Connection conn = bs.getConnection();
return conn;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException();
}

}
public static void rollback(Connection conn) {
if(conn!=null) {
try {
conn.rollback();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(Connection conn) {
if(conn!=null) {
try {
//将用过的连接归还到连接池中
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

dao：主要调用DBUtil操作相应的model——增删改查

package dao;
import java.util.List;

import entity.Student;
//学生接口类
public interface StudentDao {
//抽象方法,查询学生，返回整个学生
public List<Student> findStudent();
//保存学生,返回值为空，传进去需要保存的学生信息
public void save(Student stu);
//通过stuId查询学生的信息，返回学生对象
public Student findById(int stuId);
//修改操作的更新操作，传进来一个学生对象，无返回值
public void updateStudent(Student student);
//删除操作,根据stuId来删除
public void deleteStudent(int stuId);
}

package dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.Student;
import sun.management.snmp.util.SnmpListTableCache;
import util.DbUtils;

public class StudentDaoImpl implements Serializable, StudentDao {

/**
*
*/
private static final long serialVersionUID = 1L;

public List<Student> findStudent() {
Connection conn=null;
try {
//获取连接
conn=DbUtils.getConnection();
//执行查询语句
String sql="select * from Student";
Statement sm = conn.createStatement();
//获得查询结果
ResultSet rs = sm.executeQuery(sql);
//生成list集合用来存储student对象
List<Student> list=new ArrayList<Student>();
while(rs.next()) {
//生成student对象
Student student=new Student();
//存储学生的各个信息
student.setStuId(rs.getInt("stuId"));
student.setStuName(rs.getString("stuName"));
student.setStuSex(rs.getString("stuSex"));
student.setStuTel(rs.getString("stuTel"));
student.setStuDisc(rs.getString("stuDisc"));
student.setScore(rs.getString("score"));
//将学生存入集合中
list.add(student);
}
return list;
} catch (Exception e) {
throw new RuntimeException("找不到学生",e);
}finally {
//关闭连接
DbUtils.close(conn);
}
}
//保存学生信息
public void save(Student stu) {
Connection conn=null;
try {
//获取连接
conn=DbUtils.getConnection();
String sql="insert into student values(?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, stu.getStuId());
ps.setString(2, stu.getStuName());
ps.setString(3, stu.getStuSex());
ps.setString(4, stu.getStuTel());
ps.setString(5, stu.getStuDisc());
ps.setString(6, stu.getScore());
ps.executeUpdate();//执行插入语句
} catch (Exception e) {
throw new RuntimeException("无法保存学生信息",e);
}finally {
DbUtils.close(conn);
}
}

//通过stuId来查询学生
public Student findById(int stuId) {
Connection conn=null;

try {
conn=DbUtils.getConnection();
String sql="select *from student where stuId=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, stuId);
//执行sql语句
ResultSet rs = ps.executeQuery();
Student student=new Student();
if(rs.next()) {
student.setStuId(rs.getInt("stuId"));
student.setStuName(rs.getString("stuName"));
student.setStuSex(rs.getString("stuSex"));
student.setStuTel(rs.getString("stuTel"));
student.setStuDisc(rs.getString("stuDisc"));
student.setScore(rs.getString("score"));
}
return student;
} catch (Exception e) {
throw new RuntimeException("通过id查询失败",e);
}finally {
DbUtils.close(conn);
}
}
//更新操作
public void updateStudent(Student student) {
Connection conn=null;
try {
conn=DbUtils.getConnection();
String sql="update student set stuName=?,stuSex=?,stuTel=?,stuDisc=?,score=? where stuId=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, student.getStuName());
ps.setString(2, student.getStuSex());
ps.setString(3, student.getStuTel());
ps.setString(4, student.getStuDisc());
ps.setInt(5, student.getStuId());
ps.setString(6, student.getScore());
//更新操作
ps.executeUpdate();
} catch (Exception e) {
throw new RuntimeException("更新失败",e);
}finally {
DbUtils.close(conn);
}
}
//删除操作
public void deleteStudent(int stuId) {
Connection conn=null;
try {
conn=DbUtils.getConnection();
String sql="delete from student where stuId=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, stuId);
//执行操作
ps.executeUpdate();
} catch (Exception e) {
throw new RuntimeException("删除失败",e);
}finally {
DbUtils.close(conn);
}
}
/*测试删除方法
public static void main(String[] args) {
StudentDao dao=new StudentDaoImpl();
dao.deleteStudent(2018004);

}
*/
/*测试通过stuId方法
public static void main(String[] args) {
StudentDao dao=new StudentImpl();
Student student = dao.findById(2018001);
System.out.println(student.getStuName());
}
*/
/*测试保存
public static void main(String[] args) {
StudentDao dao=new StudentImpl();
Student stu=new Student();
stu.setStuId(2018003);
stu.setStuName("小花");
stu.setStuSex("F");
stu.setStuTel("123");
stu.setStuDisc("小花是个好姑娘");
dao.save(stu);
}
*/
/*测试一下
public static void main(String[] args) {
StudentDao dao=new StudentImpl();
List<Student> students = dao.findStudent();
for(Student student:students) {
System.out.println(student.getStuName());
}
}
*/

}