package com.xxu.java;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.sql.*;
/**
* 连接数据库的工具类
*
* @version 1.0
*/
public class DBUtil {
//连接的数据库的路径
private static final String URL = "jdbc:mysql://localhost:3306/student";
//数据库的账号
private static final String USERNAME = "root";
//数据库的密码
private static final String PASSWORD = "NYY250418";
private DBUtil() {
}
//通过静态代码块注册驱动
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 获得数据库连接
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
// 根据id号进行查询
public static String findById(String id) {
String stuId = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
int count = 0;
//获取连接
conn = DBUtil.getConnection();
//根据姓名查询
String sql = "select * from student_message where stu_id = ?";
ps = conn.prepareStatement(sql);
//给占位符赋值
ps.setString(1, id);
//执行SQL语句,返回结果集
rs = ps.executeQuery();
while (rs.next()) {
/*
将查询的结果给返回
如果查询到了返回id号
如果没有查询到,返回null
*/
stuId = rs.getString("stu_id");
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
closeConnection(rs, ps, conn);
}
return stuId;
}
// 添加同学信息
public static void addStudent(Student student) {
//获取数据库连接
Connection connection = DBUtil.getConnection();
// 生成一条sql语句
String sql = "insert into student_message (stu_id,stu_name,stu_sex,stu_age,stu_qq,stu_phone,stu_hobby,stu_email) values(?,?,?,?,?,?,?,?)";
PreparedStatement preparedStatement = null;
try {
//创建一个prepareStatement对象
preparedStatement = connection.prepareStatement(sql);
//为sql中的?赋值
preparedStatement.setString(1, student.getId());
preparedStatement.setString(2, student.getName());
preparedStatement.setString(3, student.getSex());
preparedStatement.setString(4, student.getAge());
preparedStatement.setString(5, student.getQQ());
preparedStatement.setString(6, student.getTel());
preparedStatement.setString(7, student.getHobby());
preparedStatement.setString(8, student.getEmail());
int count = preparedStatement.executeUpdate();
if (count == 1) {
new AddSuccess();
} else {
new AddDefeat();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
// 根据id删除学生
public static void deleteStudent(String id) {
//注册驱动
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtil.getConnection();
//要执行的SQL语句
String sql = "delete from student_message where stu_id = ?";
//预编译
ps = conn.prepareStatement(sql);
ps.setString(1, id);
int count = ps.executeUpdate();
if (count == 1) {
new DeleteSuccess();
} else {
new DeleteDefeatFrame();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
// 根据学号修改学生信息
public static void modifyStudent(Student student) {
Connection conn = null;
PreparedStatement ps = null;
conn = DBUtil.getConnection();
//根据id号进行修改
String sql = "update student_message set stu_name = ?,stu_sex = ?,stu_age = ?,stu_qq = ?,stu_phone = ?,stu_hobby = ?,stu_email = ? where stu_id = ?";
try {
ps = conn.prepareStatement(sql);
//为占位符赋值
ps.setString(1, student.getName());
ps.setString(2, student.getSex());
ps.setString(3, student.getAge());
ps.setString(4, student.getQQ());
ps.setString(5, student.getTel());
ps.setString(6, student.getHobby());
ps.setString(7, student.getEmail());
ps.setString(8, student.getId());
int count = ps.executeUpdate();
if (count == 1) {
new ModifySuccess();
} else {
new ModifyDefeat();
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
//关闭数据库
public static void closeConnection(ResultSet rs, PreparedStatement pstmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
- 1
- 2
前往页