package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import com.mysql.cj.util.StringUtils;
public class TestJDBC7 {
public static void main(String[] args) {
try {
//mysql 8.0以后的驱动为com.mysql.cj.jdbc.Driver, 而不是com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String lineNum = "";
String employeeIdIn = "";
String employeeName = "";
String departmentId = "";
while(true ) {
Scanner in = new Scanner(System.in);
System.out.println("1:输入每页显示条数！2：根据员工编号查询 3：根据员工姓名查询4：根据部门编号查询5:开始查询6:结束");
int i = in.nextInt();
if (i==1) {
System.out.println("请输入每页显示条数！");
lineNum = in.next();
} else if (i==2){
System.out.println("请输入员工编号！");
employeeIdIn = in.next();
} else if (i ==3) {
System.out.println("请输入员工姓名！");
employeeName = in.next();
} else if (i ==4) {
System.out.println("请输入部门编号！");
departmentId = in.next();
} else if (i==5) {
pageList(employeeIdIn,employeeName,departmentId, lineNum);
} else {
System.out.println("项目已退出");
System.exit(0);//关闭当前进程。
}
}
}
public static void pageList(String employeeId, String employeeName,String departmentId, String line) {
//连接你自己的数据库
try(Connection c = DriverManager.getConnection(
"jdbc:mysql://172.17.65.188:3306/ntms_db_v5?" +
"serverTimezone=GMT%2B8&&useSSL=false",
"test","123456");
Statement s = c.createStatement()) {
String sql2 = "select \r\n" +
"employee.employeeId,\r\n" +
"employee.employeeName,\r\n" +
"(select codeContent from tbl_code_class code\r\n" +
"where\r\n" +
"codeTypeId = ‘GENDER_ENUM‘\r\n" +
"and \r\n" +
"code.codeValue = employee.gender) gender\r\n" +
",\r\n" +
"department.departmentId,\r\n" +
"department.departmentName,\r\n" +
"employee.emailAddress\r\n" +
"from tbl_employee employee, tbl_department department\r\n" +
"where \r\n" +
"department.departmentId = employee.departmentId ";
if (employeeId!=null && employeeId !="") {
sql2 = sql2 + " and employee.employeeId = " + "‘"+employeeId+"‘";
}
if (employeeName!=null && employeeName !="") {
sql2 = sql2 + " and employee.employeeName = " + "‘"+employeeName+"‘";
}
if (departmentId!=null && departmentId !="") {
sql2 = sql2 + " and employee.departmentId = " + "‘"+departmentId+"‘";
}
if (line!=null && line !="") {
int lineNum = Integer.parseInt(line);
if (lineNum > 0) {
sql2 = sql2 + " limit " + 0 + "," + line;
}
}
ResultSet rs = s.executeQuery(sql2);
//System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\n","employeeId", "employeeName", "gender", "departmentId", "departmentName", "emailAddress");
while(rs.next()) {
String employeeIdRet = rs.getString(1);
String employeeNameRet = rs.getString(2);
String gender = rs.getString(3);
String departmentIdRet = rs.getString(4);
String departmentName = rs.getString(5);
String emailAddress = rs.getString(6);
//输出查询结果
System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\n",employeeIdRet,employeeNameRet, gender, departmentIdRet, departmentName, emailAddress);
}//不必关闭Connection和Statement, try块结束后 会自动关闭，而ResultSet
//会在Statement关闭时自动关闭！这种写法必须把Connection和Statement写在
//try(){} 的圆括号内；否则要在最后用finally进行关闭。
} catch (SQLException e) {
e.printStackTrace();
}
}
}