Java对MySQL数据进行增删改查和模糊查询

需要创建JavaBeen类和jdbc，这里为了方便各位查看就不将JavaBeen类写出来，并将功能放进Util类里。

package com;

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

import com.pojo.Emp;

public class DatabaseUntil {

	/*
	 * 1.导入架包 2.加载驱动 3.连接数据库 4.操作数据库
	 */

	private String userName = "root";
	private String password = "123456";
	private String url = "jdbc:mysql://localhost:3306/作业?useSSL=true";
	private Connection connection;

	{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("驱动加载成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("驱动加载失败");
		}
	}

	public DatabaseUntil() {
		// TODO Auto-generated constructor stub
		try {
			this.connection = DriverManager.getConnection(url, userName, password);
			System.out.println("连接成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("连接失败");
		}
	}
        
        //增
	public void insertEmp(Emp emp) {
		// 写SQL语句
		String sql = "INSERT emp VALUES (?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			// 从数据库连接操作句柄
			ps = connection.prepareStatement(sql);
			// 设置参数
			ps.setInt(1, emp.getEmpno());
			ps.setString(2, emp.getEname());
			ps.setString(3, emp.getJob());
			ps.setInt(4, emp.getMgr());
			ps.setDate(5, emp.getHiredate());
			ps.setInt(6, emp.getSal());
			ps.setInt(7, emp.getComm());
			ps.setInt(8, emp.getDeptno());
			int len = ps.executeUpdate();
			System.out.println("成功插入了" + len + "行数据");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
        
        //改
	public void updateEmp(Emp emp) {
		/*
		 * 写SQL语句,
		 */
		String sql = "UPDATE emp SET ename = ?, job = ?, mgr = ?, hiredate = ?, sal = ?, comm = ?, deptno = ? WHERE empno = 7399";
		// 从数据库连接操作句柄
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			// 修改参数
			ps.setString(1, emp.getEname());
			ps.setString(2, emp.getJob());
			ps.setInt(3, emp.getMgr());
			ps.setDate(4, emp.getHiredate());
			ps.setInt(5, emp.getSal());
			ps.setInt(6, emp.getComm());
			ps.setInt(7, emp.getDeptno());
			int len = ps.executeUpdate();
			System.out.println("成功插入了" + len + "行数据");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
        
        //删
	public void deleteEmp(int empno) {
		// 写SQL语句
		String sql = "DELETE FROM emp WHERE empno = ?";
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setInt(1, empno);
			int len = ps.executeUpdate();
			System.out.println("删除了" + len + "行数据");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

        //查
	public Emp selectEmp(int empno) {
		// 写SQL 语句
		String sql = "SELECT * FROM emp WHERE empno = ?";
		// 从数据库连接，操作句柄
		PreparedStatement ps = null;
		ResultSet set = null;
		Emp result = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setInt(1, empno);
			set = ps.executeQuery();
			while (set.next()) {
				result = new Emp();
				result.setEmpno(set.getInt("empno"));
				result.setEname(set.getString("ename"));
				result.setJob(set.getString("job"));
				result.setMgr(set.getInt("mgr"));
				result.setHiredate(set.getDate("hiredate"));
				result.setSal(set.getInt("sal"));
				result.setComm(set.getInt("comm"));
				result.setDeptno(set.getInt("deptno"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
        //模糊查询
	public List<Emp> likeEmp(String str) {
		String sql = "SELECT * FROM emp WHERE ename LIKE ?";
		PreparedStatement ps = null;
		List<Emp> emps = null;
		ResultSet set = null;
		Emp emp = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, "%" + str + "%");
			set = ps.executeQuery();
			while (set.next()) {
				if (emps == null) {
					emps = new ArrayList<Emp>();
				}
				emp = new Emp();
				emp.setEmpno(set.getInt("empno"));
				emp.setEname(set.getString("ename"));
				emp.setJob(set.getString("job"));
				emp.setMgr(set.getInt("mgr"));
				emp.setHiredate(set.getDate("hiredate"));
				emp.setSal(set.getInt("sal"));
				emp.setComm(set.getInt("comm"));
				emp.setDeptno(set.getInt("deptno"));
				emps.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return emps;
	}

	public void closeConnection() {
		try {
			if (this.connection != null) {
				this.connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}