JDBCUtils
public class JDBCUtils {
// 定义成员变量DataSource， 可以切换不同的连接池
private static DataSource ds;
// 初始化配置
static {
Properties properties = new Properties();
try {
properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
} catch (IOException e) {
e.printStackTrace();
}
try {
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 释放资源
*/
public static void close(Statement stmt, Connection conn) {
close(null, stmt, conn);
}
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取连接池方法
*/
public static DataSource getDataSource() {
return ds;
}
}
druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/ljshuaige
username=root
password=root
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000
接口
public interface StudentDao
{
// 学生列表 查询所有学生
public List<Student> findAll();
// 保存某个学生
public void save(Student student);
// 删除某个学生
public void remove(Student student);
// 修改某个学生
public void update(Student student);
}
接口实现类
public class StudentDaoImpl implements StudentDao
{
public List<Student> findAll() {
Connection conn =null;
Statement stmt =null;
ResultSet rs =null;
ArrayList<Student> list=null;
try{
conn = JDBCUtils.getConnection();
String sql="select * from studentmanage";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
list=new ArrayList<>();
while(rs.next()){
int id=rs.getInt("id");
String name=rs.getString("name");
String gender=rs.getString("gender");
int score=rs.getInt("score");
String addr=rs.getString("addr");
int tel=rs.getInt("tel");
Student student=new Student(id,name,gender,score,addr,tel);
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs, stmt, conn);
}
return list;
}
@Override
public void save(Student student) {
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn= JDBCUtils.getConnection();
String sql= "insert into studentmanage values(null,?,?,?,?,?);";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,student.getName());
pstmt.setString(2, student.getGender());
pstmt.setInt(3, student.getScore());
pstmt.setString(4, student.getAddr());
pstmt.setInt(5, student.getTel());
int count=pstmt.executeUpdate();
System.out.println("count"+ count );
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(pstmt,conn);
}
}
@Override
public void remove(Student student) {
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn= JDBCUtils.getConnection();
String sql= "delete from studentmanage where id=?;";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,student.getId());
int count=pstmt.executeUpdate();
System.out.println("count"+ count );
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(pstmt,conn);
}
}
@Override
public void update(Student student) {
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn= JDBCUtils.getConnection();
String sql= "update studentmanage set name=?, gender =?, score=?, addr = ?, tel=? where id=?;";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,student.getName());
pstmt.setString(2, student.getGender());
pstmt.setInt(3, student.getScore());
pstmt.setString(4, student.getAddr());
pstmt.setInt(5, student.getTel());
pstmt.setInt(6, student.getId());
int count=pstmt.executeUpdate();
System.out.println("count"+ count );
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(pstmt,conn);
}
}
}
定义类
public class Student
{
private int id;
private String name;
private String gender;
private int score;
private String addr;
private int tel;
public Student()
{
}
public Student(int id, String name, String gender, int score, String addr, int tel)
{
this.id = id;
this.name = name;
this.gender = gender;
this.score = score;
this.addr = addr;
this.tel = tel;
}
public Student( String name, String gender, int score, String addr, int tel)
{
this.name = name;
this.gender = gender;
this.score = score;
this.addr = addr;
this.tel = tel;
}
public Student(int id)
{
this.id = id;
}
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public String getGender()
{
return gender;
}
public void setGender(String gender)
{
this.gender = gender;
}
public int getScore()
{
return score;
}
public void setScore(int score)
{
this.score = score;
}
public String getAddr()
{
return addr;
}
public void setAddr(String addr)
{
this.addr = addr;
}
public int getTel()
{
return tel;
}
public void setTel(int tel)
{
this.tel = tel;
}
@Override
public String toString()
{
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", score=" + score +
", addr='" + addr + '\'' +
", tel=" + tel +
'}';
}
}
实现类
public class MainStudent
{
public static void main(String[] args) {
System.out.println("-------- 欢迎登录low版学生管理系统---------");
StudentDaoImpl studentDao = new StudentDaoImpl();
System.out.println("----------学生列表------------------------");
List<Student> list = studentDao.findAll();
for (Student s:list){
System.out.println(s);
}
/* System.out.println("----------保存学生------------------------");
Student s2 = new Student("王五","男",70,"大连",150);
studentDao.save(s2);
List<Student> list1 = studentDao.findAll();
for (Student s:list1){
System.out.println(s);
}*/
// new Student
// studentDao.save();
// 修改
/*System.out.println("----------修改------------------------");
Student s1 = new Student(1,"李四","男",99,"锦州",139);
studentDao.update(s1);
List<Student> list2 = studentDao.findAll();
for (Student s:list2){
System.out.println(s);
}*/
// 删除
System.out.println("----------删除------------------------");
Student s3 = new Student(3);
studentDao.remove(s3);
List<Student> list3 = studentDao.findAll();
for (Student s:list3){
System.out.println(s);
}
}
}
