基于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());

}

}

*/

}