JDBC: 数据库链接及操作

工具: mysql, eclipse;

1. 连接数据库工具类:SqlConnection;

package com.jsp.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 连接数据库工具类
 * 
 * @author Administrator
 *
 */
public class SqlConnection {

	/**
	 * 获取数据库连接
	 * 
	 * @return java.sql.Connection
	 */
	public static final Connection getSqlConn() {

		try {
			// 1.加载mysql数据库驱动
			/* 动态加载一个class文件: Class.forName("com.jdbc01.test.Users"); */
			Class.forName("com.mysql.jdbc.Driver");
			// 2.设置mysql数据库用户账户(用户名和密码)
			String user = "root";
			String pwd = "123456";
			// 3.设置数据库地址或URL
			String url = "jdbc:mysql://127.0.0.1:3306/j2ee?useUnicode=true&characterEncoding=utf-8";
			// 4.获取数据库连接对象
			Connection conn = DriverManager.getConnection(url, user, pwd);
			// System.out.println(conn);
			return conn;
		} catch (Exception e) {
			// e.printStackTrace();
			System.out.println("数据库连接失败!");
		}
		return null;
	}

	/**
	 * 关闭资源
	 * 
	 * @param conn
	 * @param state
	 * @param rs
	 */
	public static final void closeConn(Connection conn, Statement state, ResultSet rs) {
		// 关闭资源,ResultSet->Statement->Connection
		try {
			if (rs != null) {
				rs.close();
			}
			if (state != null) {
				state.close();
			}
			conn.close();
		} catch (Exception e) {
			System.out.println("关闭资源异常");
			e.printStackTrace();
		}
	}

}

2. 实体类:Employee;

package com.jsp.model;

/**
 * 员工类, 用于存储和封装员工数据
 * 
 * @author Administrator
 *
 */
public class Employee {

	private String emp_no; // 员工编号
	private String emp_name; // 员工姓名
	private String mobile; // 员工手机号
	private int sex; // 性别
	private String birthday; // 出生日期
	private int salary; // 薪水
	private String dep_name; // 部门名称
	private String homeplace; // 籍贯

	public Employee() {

	}

	public Employee(String emp_no, String emp_name, String mobile, int sex, String birthday, int salary,
			String dep_name, String homeplace) {
		super();
		this.emp_no = emp_no;
		this.emp_name = emp_name;
		this.mobile = mobile;
		this.sex = sex;
		this.birthday = birthday;
		this.salary = salary;
		this.dep_name = dep_name;
		this.homeplace = homeplace;
	}

	public String getEmp_no() {
		return emp_no;
	}

	public void setEmp_no(String emp_no) {
		this.emp_no = emp_no;
	}

	public String getEmp_name() {
		return emp_name;
	}

	public void setEmp_name(String emp_name) {
		this.emp_name = emp_name;
	}

	public String getMobile() {
		return mobile;
	}

	public void setMobile(String mobile) {
		this.mobile = mobile;
	}

	public int getSex() {
		return sex;
	}

	public void setSex(int sex) {
		this.sex = sex;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public int getSalary() {
		return salary;
	}

	public void setSalary(int salary) {
		this.salary = salary;
	}

	public String getDep_name() {
		return dep_name;
	}

	public void setDep_name(String dep_name) {
		this.dep_name = dep_name;
	}

	public String getHomeplace() {
		return homeplace;
	}

	public void setHomeplace(String homeplace) {
		this.homeplace = homeplace;
	}

	@Override
	public String toString() {
		return "Employee [emp_no=" + emp_no + ", emp_name=" + emp_name + ", mobile=" + mobile + ", sex=" + sex
				+ ", birthday=" + birthday + ", salary=" + salary + ", dep_name=" + dep_name + ", homeplace="
				+ homeplace + "]";
	}
}

3. 数据库操作类: EmployeeDao;

package com.jsp.dao;

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

import com.jsp.model.Employee;
import com.jsp.util.SqlConnection;

/**
 * 实现员工表增删改查
 * 
 * @author Administrator
 *
 */
public class EmployeeDao {

	/**
	 * 登录
	 * 
	 * @param mobile
	 * @param pwd
	 * @return
	 */
	public boolean login(String mobile, String pwd) {

		Connection conn = SqlConnection.getSqlConn();
		if (conn != null) {
			// 执行SQL语句Statement
			PreparedStatement ps = null;
			// 查询到的结果集初始化null
			ResultSet rs = null;
			String sql = "select emp_no from employee where mobile = ? and pwd = ?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, mobile);
				ps.setString(2, pwd);
				rs = ps.executeQuery();
				if (rs.next()) {
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
			SqlConnection.closeConn(conn, ps, rs);
		}
		return false;
	}

	/**
	 * 查询所有
	 * 
	 * @return
	 */
	public List<Employee> queryEmp() {

		List<Employee> list = new ArrayList<Employee>();
		Employee emp = new Employee();
		Connection conn = SqlConnection.getSqlConn();
		if (conn != null) {
			// 执行SQL语句Statement
			Statement state = null;
			// 查询到的结果集初始化null
			ResultSet rs = null;
			String sql = "select emp_no as no, emp_name, mobile, sex, birthday, salary, dep_name, homeplace from employee";
			try {
				state = conn.createStatement();
				rs = state.executeQuery(sql);
				while (rs.next()) {
					// 有别名的字段,根据别名获取内容
					String emp_no = rs.getString("no");
					// 根据字段序列号获取内容
					String emp_name = rs.getString(2);
					String mobile = rs.getString("mobile");
					int sex = rs.getInt("sex");
					String birthday = rs.getString("birthday");
					int salary = rs.getInt("salary");
					String dep_name = rs.getString("dep_name");
					String homeplace = rs.getString("homeplace");
					emp = new Employee(emp_no, emp_name, mobile, sex, birthday, salary, dep_name, homeplace);
					list.add(emp);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
			SqlConnection.closeConn(conn, state, rs);
		}
		return list;
	}

	/**
	 * 添加员工(注册)
	 * 
	 * @param emp_no
	 * @param emp_name
	 * @param mobile
	 * @param pwd
	 * @param sex
	 * @param birthday
	 * @param salary
	 * @param dep_name
	 * @param homeplace
	 */
	public boolean addEmployee(String emp_no, String emp_name, String mobile,String pwd, int sex, String birthday, int salary,
			String dep_name, String homeplace) {
		// 1.获取数据库连接对象
		Connection conn = SqlConnection.getSqlConn();
		// 3.创建PreparedStatement对象
		PreparedStatement ps = null;
		// 2.定义SQL语句
		String sql = "insert into employee(emp_no,emp_name,mobile,pwd,sex,birthday,salary,dep_name,homeplace) values(?,?,?,?,?,?,?,?,?)";
		try {
			// 并预编译SQL语句
			ps = conn.prepareStatement(sql);
			// 4.给占位符赋值
			ps.setString(1, emp_no);
			ps.setString(2, emp_name);
			ps.setString(3, mobile);
			ps.setString(4, pwd);
			ps.setInt(5, sex);
			ps.setString(6, birthday);
			ps.setInt(7, salary);
			ps.setString(8, dep_name);
			ps.setString(9, homeplace);
			// 5.执行SQL语句
			int rows = ps.executeUpdate();
			System.out.println("insert操作影响行数:" + rows);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		SqlConnection.closeConn(conn, ps, null);
		return true;
	}

	/**
	 * 根据员工编号删除员工信息
	 * 
	 * @param emp_no
	 */
	public void delEmployee(String emp_no) {
		// 1.获取数据库连接对象
		Connection conn = SqlConnection.getSqlConn();
		// 2.定义SQL语句
		String sql = "delete from employee where emp_no = ?;";
		// 3.通过Connection创建PreparedStatement对象
		PreparedStatement ps = null;
		try {
			// 并预编译SQL语句
			ps = conn.prepareStatement(sql);
			// 4.给占位符赋值
			ps.setString(1, emp_no);
			// 5.执行SQL语句
			int rows = ps.executeUpdate();
			System.out.println("delete操作影响行数:" + rows);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		SqlConnection.closeConn(conn, ps, null);
	}

	/**
	 * 根据员工编号修改员工的手机号码、薪水、部门名称
	 * 
	 * @param emp_no
	 * @param mobile
	 * @param salary
	 * @param dep_name
	 */
	public void updateEmployee(String emp_no, String mobile, int salary, String dep_name) {
		// 1.获取数据库连接
		Connection conn = SqlConnection.getSqlConn();
		// 2.定义SQL语句
		String sql = "update employee set mobile = ?, salary = ?, dep_name = ? where emp_no = ?;";
		// 3.通过Connection创建PreparedStatement对象
		PreparedStatement ps = null;
		try {
			// 并预编译SQL语句
			ps = conn.prepareStatement(sql);
			// 4.赋值
			ps.setString(1, mobile);
			ps.setInt(2, salary);
			ps.setString(3, dep_name);
			ps.setString(4, emp_no);
			// 5.执行SQL语句
			int rows = ps.executeUpdate();
			System.out.println("update操作影响行数:" + rows);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		SqlConnection.closeConn(conn, ps, null);
	}

	/**
	 * 根据员工编号获取员工信息
	 * 
	 * @param emp_no
	 * @return
	 */
	public Employee getEmployee(String emp_no) {

		Employee emp = new Employee();
		// 1.开启资源,连接数据库
		Connection conn = SqlConnection.getSqlConn();
		// 2.定义SQL语句
		String sql = "select emp_no, emp_name, mobile, sex, birthday, salary, dep_name, homeplace from employee where emp_no = ?";
		// 3.定义PreparedStatement和ResultSet
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 4.准备SQL语句
			ps = conn.prepareStatement(sql);
			// 5.给占位符赋值
			ps.setString(1, emp_no);
			// 6.执行
			rs = ps.executeQuery();
			// 7.处理结果集
			while (rs.next()) {
				String emp_name = rs.getString(2);
				String mobile = rs.getString("mobile");
				int sex = rs.getInt("sex");
				String birthday = rs.getString("birthday");
				int salary = rs.getInt("salary");
				String dep_name = rs.getString("dep_name");
				String homeplace = rs.getString("homeplace");
				emp = new Employee(emp_no, emp_name, mobile, sex, birthday, salary, dep_name, homeplace);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 8.关闭资源
		SqlConnection.closeConn(conn, ps, rs);
		return emp;
	}

}

4. 测试类: test01;

package com.jsp.test;

import java.util.List;

import com.jsp.dao.EmployeeDao;
import com.jsp.model.Employee;

public class Test01 {

	public static void main(String[] args) {
		EmployeeDao empdao = new EmployeeDao();
		List<Employee> list = empdao.queryEmp();
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i).toString());
		}
	}
}