使用JDBC连接MySQL数据库并且完成增删改查完整代码

完整路径截图：

//BaseDao.java文件
package cn.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {

/** * 1.四个常量 driver 驱动 url 连接地址 username数据库登录名 password 密码 */private static final String DRIVER = "com.mysql.jdbc.Driver";private static final String URL = "jdbc:mysql://127.0.0.1:3306/myschool?characterEncoding=utf-8";private static final String USERNAME = "root";private static final String PASSWOED = "root";// 2.static块加载驱动类static {    try {        Class.forName(DRIVER);    } catch (ClassNotFoundException e) {        // TODO Auto-generated catch block        e.printStackTrace();    }}// 3.用DriverManager获取数据库连接对象Connectionpublic Connection getConnection() throws SQLException {    return DriverManager.getConnection(URL, USERNAME, PASSWOED);}// 4.用connection对象获取preparedStatement预编译执行对象public PreparedStatement createPreparedStatement(Connection conn,        String sql, Object... params) throws SQLException {    PreparedStatement pstmt = conn.prepareStatement(sql);    if (params != null && params.length > 0) {        int i = 1;// 标识“？”占位符的位置，默认为1，表示第一个？        for (Object p : params) {            pstmt.setObject(i, p);            i++;        }    }    return pstmt;}// 5.公用的DML语句执行方法，执行增、删、改，返回影响行数public int excuteDML(String sql, Object... params) {    // 获取Connection连接对象    Connection conn = null;    // 获取编译执行对象    PreparedStatement pstmt = null;    int result = 0;    try {        conn = this.getConnection();        pstmt = this.createPreparedStatement(conn, sql, params);        result = pstmt.executeUpdate();    } catch (SQLException e) {        // TODO Auto-generated catch block        e.printStackTrace();    } finally {        this.closeAll(conn, pstmt, null);    }    return result;}protected void closeAll(Connection conn, PreparedStatement stmt,        ResultSet rs) {    if (rs != null) {        try {            rs.close();// 关闭结果集        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } finally {            // 关闭连接对象和执行对象            this.closeConnAndPstmt(conn, stmt);        }    } else {        this.closeConnAndPstmt(conn, stmt);    }}private void closeConnAndPstmt(Connection conn, PreparedStatement stmt) {    if (stmt != null) {        try {            stmt.close();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } finally {            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }        }    }}

}
//StudentServlet.java文件
package cn.servlet;

import java.util.List;
import java.util.Scanner;

import cn.entit.Student;
import cn.service.StudentService;
import cn.service.impl.StudentServiceImpl;

public class StudentServlet {
// 定义业务逻辑层接口，指向业务逻辑层实现类
private static StudentService studentService = new StudentServiceImpl();

/** * 表示控制层：学生表 *  * @param args *            作用：传递用户输入的信息，调用后台业务逻辑层代码，实现增删改查 *  */public static void main(String[] args) {    Scanner input = new Scanner(System.in);    do {        System.out.println("请选择操作类型：1.添加2.删除3.修改4.查询5.学号查询");        int choose = input.nextInt();        switch (choose) {        case 1:            addStudent(input);// 添加            break;        case 2:            delStudent(input);// 删除            break;        case 3:            insStudent(input);// 修改            break;        case 4:            selStudent();// 查询            break;        case 5:            // 学号查询            selectStudentByNo(input);// 查询            break;        default:            System.out.println("无效输入");            break;        }    } while (true);}private static void selectStudentByNo(Scanner input) {    System.out.println("请输入您要查询的学号:");    Integer studentNo = input.nextInt();    List<Student> students = studentService.selectStudentByNo(studentNo);    for (Student s : students) {        System.out.println("学号：" + s.getStudentNo() + "\t姓名"                + s.getStudentName() + "\t手机号：" + s.getPhone() + "\t生日"                + s.getBornData());    }}private static void selStudent() {    List<Student> students = studentService.selStudent();    for (Student s : students) {        System.out.println("学号：" + s.getStudentNo() + "\t姓名"                + s.getStudentName() + "\t手机号：" + s.getPhone() + "\t生日"                + s.getBornData());    }}// 修改private static void insStudent(Scanner input) {    System.out.println("请输入要修改的学号：");    Integer studentNo = input.nextInt();    System.out.println("请输入要修改的密码：");    String loginPwd = input.next();    Student student = new Student();    student.setStudentNo(studentNo);    student.setLoginPwd(loginPwd);    int result = studentService.insStudent(student);    if (result > 0) {        System.out.println("更新成功");    } else {        System.out.println("更新失败");    }}// 删除private static void delStudent(Scanner input) {    System.out.println("请输入要删除的学号");    int studentNo = input.nextInt();    int result = studentService.delStudent(studentNo);    if (result > 0) {        System.out.println("删除成功");    } else {        System.out.println("删除失败");    }}// 添加private static void addStudent(Scanner input) {    // Scanner input = new Scanner(System.in);    System.out.println("请输入学号：");    Integer studentNo = input.nextInt();    System.out.println("请输入登录密码：");    String loginPwd = input.next();    System.out.println("请输入学生姓名：");    String studentName = input.next();    System.out.println("请输入性别：");    String sex = input.next();    System.out.println("请输入年级编号：");    Integer gradelD = input.nextInt();    Student student = new Student(studentNo, loginPwd, studentName, sex,            gradelD);    // 调用业务逻辑层添加学生信息，获取影响行数    int result = studentService.addStudeng(student);    if (result > 0) {        System.out.println("添加成功");    } else {        System.out.println("添加失败");    }}

}
//StudentService.java文件
package cn.service;

import java.util.List;

import cn.entit.Student;

public interface StudentService {

int addStudeng(Student student);int delStudent(int studentNo);int insStudent(Student student);List<Student> selStudent();List<Student> selectStudentByNo(Integer studentNo);

}
//StudentServiceImpl.java文件
package cn.service.impl;

import java.util.ArrayList;
import java.util.List;

import cn.dao.StudentDao;
import cn.dao.impl.StudengDaoImpl;
import cn.entit.Student;
import cn.service.StudentService;

/**
* 业务逻辑层实现类：学生表 作用：传递的参数合法性，及业务逻辑分支判断，调用数据访问层实现CRUD
*/
public class StudentServiceImpl implements StudentService {
// 创建数据访问层接口对象，指向数据访问层实现类
private StudentDao studentDao = new StudengDaoImpl();

@Overridepublic int addStudeng(Student student) {    int result = 0;// 影响行数    if (student != null) {        if (student.getStudentNo() != null) {            // 当数据合法时，调用数据访问层，执行CRUD            result = studentDao.addStudent(student);        }    }    return result;}@Overridepublic int delStudent(int studentNo) {    int result = 0;// 影响行数    if (studentNo >= 9999 && studentNo <= 100000) {        // 当数据合法时，调用数据访问层，执行CRUD        result = studentDao.delStudent(studentNo);    }    return result;}@Overridepublic int insStudent(Student student) {    int result = 0;// 影响行数    if (student != null) {        if (student.getStudentNo() != null) {            // 当数据合法时，调用数据访问层，执行CRUD            result = studentDao.insStudent(student);        }    }    return result;}@Overridepublic List<Student> selStudent() {    return studentDao.selStudent();}@Overridepublic List<Student> selectStudentByNo(Integer studentNo) {    List<Student> list=new ArrayList<Student>();    if (studentNo >= 9999 && studentNo <= 100000) {        // 当数据合法时，调用数据访问层，执行CRUD        list = studentDao.selectStudentByNo(studentNo);    }    return list;}

}
//Student.java文件
package cn.entit;

import java.util.Date;

/**
* 学生表实体类
*
* @author Administrator
*
*/
public class Student {
private Integer studentNo;// 学号
private String loginPwd;// 登录密码
private String studentName;// 学生姓名
private String sex;// 性别
private Integer gradelD;// 年级编号
private String phone;// 手机号
private String address;// 地址
private Date bornData;// 出生日期
private String email;// 邮箱
private String identityCard;// 身份证号

public Student() {    // TODO Auto-generated constructor stub}public Student(Integer studentNo, String loginPwd, String studentName,        String sex, Integer gradelD, String phone, String address,        Date bornData, String email, String identityCard) {    super();    this.studentNo = studentNo;    this.loginPwd = loginPwd;    this.studentName = studentName;    this.sex = sex;    this.gradelD = gradelD;    this.phone = phone;    this.address = address;    this.bornData = bornData;    this.email = email;    this.identityCard = identityCard;}public Student(Integer studentNo, String loginPwd, String studentName,        String sex, Integer gradelD) {    super();    this.studentNo = studentNo;    this.loginPwd = loginPwd;    this.studentName = studentName;    this.sex = sex;    this.gradelD = gradelD;}public Integer getStudentNo() {    return studentNo;}public void setStudentNo(Integer studentNo) {    this.studentNo = studentNo;}public String getLoginPwd() {    return loginPwd;}public void setLoginPwd(String loginPwd) {    this.loginPwd = loginPwd;}public String getStudentName() {    return studentName;}public void setStudentName(String studentName) {    this.studentName = studentName;}public String getSex() {    return sex;}public void setSex(String sex) {    this.sex = sex;}public Integer getGradelD() {    return gradelD;}public void setGradelD(Integer gradelD) {    this.gradelD = gradelD;}public String getPhone() {    return phone;}public void setPhone(String phone) {    this.phone = phone;}public String getAddress() {    return address;}public void setAddress(String address) {    this.address = address;}public Date getBornData() {    return bornData;}public void setBornData(Date bornData) {    this.bornData = bornData;}public String getEmail() {    return email;}public void setEmail(String email) {    this.email = email;}public String getIdentityCard() {    return identityCard;}public void setIdentityCard(String identityCard) {    this.identityCard = identityCard;}

}
//StudentDao.java文件
package cn.dao;

import java.util.List;

import cn.entit.Student;

public interface StudentDao {

int addStudent(Student student);int delStudent(int studentNo);int insStudent(Student student);List<Student> selStudent();List<Student> selectStudentByNo(Integer studentNo);

}
//StudengDaoImpl.java文件
package cn.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.dao.StudentDao;
import cn.entit.Student;
import cn.utils.BaseDao;

/**
* 数据访问层实现类：学生表
*
* @author Administrator 作用：调用jdbc连接，执行增删改查方法，实现数据持久化操作
*/
public class StudengDaoImpl extends BaseDao implements StudentDao {
// 全局的数据库3个对象，连接、执行、结果集
private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;

@Overridepublic int addStudent(Student student) {    String sql = "insert into student(studentNo, loginPwd, studentName, sex,gradelD)value (?,?,?,?,?)";    Object[] params = { student.getStudentNo(), student.getLoginPwd(),            student.getStudentName(), student.getSex(),            student.getGradelD() };    return this.excuteDML(sql, params);}@Overridepublic int delStudent(int studentNo) {    String sql = "delete from student where studentNo=?";    Object[] params = { studentNo };    return this.excuteDML(sql, params);}@Overridepublic int insStudent(Student student) {    String sql = "update student set loginPwd=? where studentNo=?";    Object[] params = { student.getLoginPwd(), student.getStudentNo() };    return this.excuteDML(sql, params);}@Overridepublic List<Student> selStudent() {    List<Student> list = new ArrayList<Student>();    try {        conn = this.getConnection();// 1.获取数据库连接对象        String sql = "select studentNo,studentName,phone,bornData from student";        pstmt = this.createPreparedStatement(conn, sql);// 2.获取预编译执行对象        rs = pstmt.executeQuery();// 3.执行查询语句DQL        while (rs.next()) {            Student student = new Student();            student.setStudentNo(rs.getInt("studentNo"));            student.setStudentName(rs.getString("studentName"));            student.setPhone(rs.getString("phone"));            student.setBornData(rs.getDate("bornData"));            list.add(student);        }    } catch (SQLException e) {        // TODO Auto-generated catch block        e.printStackTrace();    } finally {        this.closeAll(conn, pstmt, rs);    }    return list;}@Overridepublic List<Student> selectStudentByNo(Integer studentNo) {    List<Student> list = new ArrayList<Student>();    try {        conn = this.getConnection();// 1.获取数据库连接对象        String sql = "select studentNo,studentName,phone,bornData from student where studentNo=?";        pstmt = this.createPreparedStatement(conn, sql);// 2.获取预编译执行对象        pstmt.setInt(1, studentNo);        rs = pstmt.executeQuery();// 3.执行查询语句DQL        while (rs.next()) {            Student student = new Student();            student.setStudentNo(rs.getInt("studentNo"));            student.setStudentName(rs.getString("studentName"));            student.setPhone(rs.getString("phone"));            student.setBornData(rs.getDate("bornData"));            list.add(student);        }    } catch (SQLException e) {        // TODO Auto-generated catch block        e.printStackTrace();    } finally {        this.closeAll(conn, pstmt, rs);    }    return list;}

}
//数据库student表截图

本文原创与：米折网特卖会，请勿转载。