Jdbc_作业

作业

package jdbc;

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 org.junit.Test;

import day01.Emp;

//添加方法里传什么？

public class HomeWork {
	public static int insert(Emp emp) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/yunjiaen?characterEncoding=UTF-8&useUnicode=true";
		String user = "root";
		String password = "root";
		Connection conn = DriverManager.getConnection(url, user, password);
		PreparedStatement ps = conn.prepareStatement("insert into emp values(?,?,?,?,?,?,?,?)");
		ps.setInt(1, emp.getEmpno());
		ps.setString(2, emp.getEname());
		ps.setString(3, emp.getJob());
		ps.setInt(4, emp.getMgr());
		ps.setString(5, emp.getHiredate());
		ps.setInt(6, emp.getSal());
		ps.setInt(7, emp.getComm());
		ps.setInt(8, emp.getDeptno());
		int result = ps.executeUpdate();
		System.out.println(result);
		ps.close();
		conn.close();

		return result;
	}

// 修改方法里传什么

public static int update(Emp emp) throws ClassNotFoundException, SQLException {
	Class.forName("com.mysql.jdbc.Driver");
	String url = "jdbc:mysql://localhost:3306/yunjiaen?characterEncoding=UTF-8&useUnicode=true";
	String user = "root";
	String password = "root";
	Connection conn = DriverManager.getConnection(url, user, password);
	PreparedStatement ps = conn
			.prepareStatement("update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?");

	ps.setString(1, emp.getEname());
	ps.setString(2, emp.getJob());
	ps.setInt(3, emp.getMgr());
	ps.setString(4, emp.getHiredate());
	ps.setInt(5, emp.getSal());
	ps.setInt(6, emp.getComm());
	ps.setInt(7, emp.getDeptno());
	ps.setInt(8, emp.getEmpno());
	int result = ps.executeUpdate();
	System.out.println(result);
	ps.close();
	conn.close();

	return result;
}

// 模糊查询怎么写？ 比如查询姓名里包含s的人员信息

public List<Emp> likeName1(String s) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/yunjiaen?characterEncoding=UTF-8&useUnicode=true";
		String user = "root";
		String password = "root";
		Connection conn = DriverManager.getConnection(url, user, password);
		PreparedStatement ps = conn.prepareStatement("select * from emp where ename like ?");
		ps.setString(1, '%'+s+'%');
		ResultSet rs = ps.executeQuery();
		List<Emp> empList = new ArrayList();
		while (rs.next()) {
			Emp emp = new Emp();
			emp.setEmpno(rs.getInt("empno"));
			emp.setEname(rs.getString("ename"));
			emp.setComm(rs.getInt("comm"));// 有可能会异常
			emp.setDeptno(rs.getInt("deptno"));
			emp.setHiredate(rs.getString("hiredate"));
			emp.setJob(rs.getString("job"));
			emp.setMgr(rs.getInt("mgr"));
			emp.setSal(rs.getInt("sal"));
			empList.add(emp);
		}
		ps.close();
		conn.close();
		return empList;
	}

// 添加

@Test
public void myTest01() throws ClassNotFoundException, SQLException {
	Emp emp = new Emp(8888, "yunjia", "CLERK", 7698, "2020-09-27", 20000, 200, 10);
	insert(emp);

}

//修改

@Test
public void myTest02() throws ClassNotFoundException, SQLException {
	Emp emp1 = new Emp(8888, "yunjiaen", "CLERK", 7698, "2020-09-27", 2000, 200, 10);
	update(emp1);
}

//模糊查询

@Test
	public void myTest03() throws ClassNotFoundException, SQLException {
		String s="s";
		List<Emp> empList = new ArrayList();
		empList=likeName1(s);
		System.out.println(empList);
	}
}