作业二

阳光穿透心脏的1/2处 2022-01-07 13:55 281阅读 0赞

基于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());  
\}  
\}  
\*/  
  
\}

转载于:https://www.cnblogs.com/gs-34/p/10944147.html