JDBC操作数据库实现增删改查

 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://localhost:3307/student?"
				+ "useUnicode=true"
				+ "&characterEncoding=utf8"
				+ "&serverTimezone=GMT%2B8&useSSL=false";
	private static final String USERNAME = "root";
	private static final String PASSWOED = "123456";
	// 2.static块加载驱动类
	static {
		try {
			Class.forName(DRIVER);
			System.out.println("Success loading Mysql Driver!");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.print("Error loading Mysql Driver!");
			e.printStackTrace();
		}
	}

	// 3.用DriverManager获取数据库连接对象Connection
	public Connection getConnection() {
		try {
			return DriverManager.getConnection(URL, USERNAME, PASSWOED);
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			System.out.println("Error Success connect Mysql server!");
			e.printStackTrace();
		}
		return null;
		
	}

	// 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();
					}
				}
			}
		}
	}
}

 Student.java文件

package cn.entit;

import java.util.Date;

/**
 * 学生表实体类
 * 
 * @author Administrator
 * 
 */
public class Student {
	private Integer studentNo;// 学号
	private String studentName;// 学生姓名

	public Student() {
		// TODO Auto-generated constructor stub
	}
	
	public Student(Integer studentNo, String studentName) {
		super();
		this.studentNo = studentNo;
		this.studentName = studentName;
	}

	public Integer getStudentNo() {
		return studentNo;
	}

	public void setStudentNo(Integer studentNo) {
		this.studentNo = studentNo;
	}


	public String getStudentName() {
		return studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

}

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

}

 

 StuedngDaoImpl.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;

	@Override
	public int addStudent(Student student) {
		String sql = "insert into student(studentNo, studentName)value (?,?)";
		Object[] params = { student.getStudentNo(), student.getStudentName() };
		return this.excuteDML(sql, params);
	}

	@Override
	public int delStudent(int studentNo) {
		String sql = "delete from student where studentNo=?";
		Object[] params = { studentNo };
		return this.excuteDML(sql, params);
	}

	@Override
	public int insStudent(Student student) {
		String sql = "update student set studentName=? where studentNo=?";
		Object[] params = { student.getStudentName(), student.getStudentNo() };
		return this.excuteDML(sql, params);
	}

	@Override
	public List<Student> selStudent() {
		List<Student> list = new ArrayList<Student>();
		try {
			conn = this.getConnection();// 1.获取数据库连接对象
			String sql = "select studentNo,studentName 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"));
				list.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}

}

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

}

 StudentServiceImpl.java

package cn.service.impl;

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

	@Override
	public int addStudeng(Student student) {
		int result = 0;// 影响行数
		if (student != null) {
			if (student.getStudentNo() != null) {
				// 当数据合法时，调用数据访问层，执行CRUD
				result = studentDao.addStudent(student);
			}
		}
		return result;
	}

	@Override
	public int delStudent(int studentNo) {
		int result = 0;// 影响行数
		if (studentNo >= 9999 && studentNo <= 100000) {
			// 当数据合法时，调用数据访问层，执行CRUD
			result = studentDao.delStudent(studentNo);
		}
		return result;
	}

	@Override
	public int insStudent(Student student) {
		int result = 0;// 影响行数
		if (student != null) {
			if (student.getStudentNo() != null) {
				// 当数据合法时，调用数据访问层，执行CRUD
				result = studentDao.insStudent(student);
			}
		}
		return result;
	}

	@Override
	public List<Student> selStudent() {
		return studentDao.selStudent();
	}

}

 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.查询");
			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;
			default:
				System.out.println("无效输入");
				break;
			}
		} while (true);

	}

	
	private static void selStudent() {
		List<Student> students = studentService.selStudent();
		for (Student s : students) {
			System.out.println("学号：" + s.getStudentNo() + "\t姓名"+ s.getStudentName() );
		}
	}

	// 修改
	private static void insStudent(Scanner input) {
		System.out.println("请输入要修改的学号：");
		Integer studentNo = input.nextInt();
		System.out.println("请输入要修改的姓名：");
		String studentName = input.next();

		Student student = new Student();
		student.setStudentNo(studentNo);
		student.setStudentName(studentName);

		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 studentName = input.next();
		Student student = new Student(studentNo, studentName);
		// 调用业务逻辑层添加学生信息，获取影响行数
		int result = studentService.addStudeng(student);
		if (result > 0) {
			System.out.println("添加成功");
		} else {
			System.out.println("添加失败");

		}
	}

}