创建员工实体类employee
添加get、set、toString方法
public class Employee {
private int empid;
private String empname;
private String emppwd;
private double salary;
private Date birthdy;
public int getEmpid() {
return empid;
}
public void setEmpid(int empid) {
this.empid = empid;
}
public String getEmpname() {
return empname;
}
public void setEmpname(String empname) {
this.empname = empname;
}
public String getEmppwd() {
return emppwd;
}
public void setEmppwd(String emppwd) {
this.emppwd = emppwd;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
public Date getBirthdy() {
return birthdy;
}
public void setBirthdy(Date birthdy) {
this.birthdy = birthdy;
}
@Override
public String toString() {
return "Employee{" +
"empid=" + empid +
", empname='" + empname + '\'' +
", salary=" + salary +
'}';
}
}
创建CRUD(增删改查)实体类
1、通过静态代码块读取配置文件加载驱动
2、properties配置文件参考配置文件
3、获取连接方法getConnection()
4、关闭资源方法closeResource()
5、测试新增员工信息方法
使用Statement对象获取sql命令,缺点:sql语句必须使用字符串的拼接,使用起来比较麻烦。
6、测试员工登录
使用Statement执行sql命令测试登录时,由于SQL语句是通过拼接完成的,那么就有可能存在SQL注入,缺陷:当我们输入为’ or 1=1 #时,会避过用户名和密码的检测
7、增加、删除、修改、查询方法都类似,修改sql语句即可
- 测试员工登录方法时,使用@Test运行不出来,所以使用了main方法,知道原因的q一下。
public class CRUD {
static String driver;
static String url;
static String user;
static String password;
//通过读取配置文件加载驱动
static {
InputStream is = CRUD.class.getClassLoader().getResourceAsStream("dbconfig.properties");
//InputStream is = new FileInputStream("src/dbconfig.properties");
Properties prop = new Properties();
try {
prop.load(is);
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
password = prop.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
private Statement stmt;
//获取连接对象
public static Connection getConnection(){
try {
Class.forName(driver);
return DriverManager.getConnection(url,user,password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//关闭资源
public static void closeResource(Connection conn, Statement stmt, ResultSet resl){
if (resl != null){
try {
resl.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//测试新增员工信息
@Test
public void testAdd(){
//获取连接
Connection conn = CRUD.getConnection();
//获取sql命令对象
Statement stmt = null;
try {
stmt = conn.createStatement();
//创建员工类对象
Employee emp = new Employee();
emp.setEmpname("lisi");
emp.setEmppwd("1234");
emp.setSalary(8000d);
emp.setBirthdy(new Date(23512354L));
//新增员工的sql,使用sattement只能拼接sql
String sql = "INSERT INTO employee (`empname`,`emppwd`,`salary`,`birthday`) VALUES" +
"('" + emp.getEmpname() + "','" + emp.getEmppwd() + "'," + emp.getSalary() + ",NOW())";
//执行sql,stmt.executeUpdate(sql)返回成功执行得sql语句的条数
if (stmt.executeUpdate(sql)>0){
System.out.println("成功添加一个员工!");
}else {
System.out.println("新增失败!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
CRUD.closeResource(conn,stmt,null);
}
}
//测试登录
public static void main(String[] args) throws Exception{
Scanner sc = new Scanner(System.in);
System.out.print("请输入用户名:");
String userName = sc.nextLine();
System.out.print("请输入密码:");
String userPwd = sc.nextLine();
Connection conn = CRUD.getConnection();
Statement stmt = conn.createStatement();
String sql = "SELECT empid,empname,salary from employee WHERE empname='"+userName+"' AND emppwd='"+userPwd+"'";
//执行查询
ResultSet rs = stmt.executeQuery(sql);
//从结果集里取数据
Employee emp = null;
if (rs.next()){
emp = new Employee();
emp.setEmpid(rs.getInt(1));
emp.setEmpname(rs.getString(2));
emp.setSalary(rs.getDouble(3));
}
if (emp != null){
System.out.println("登录成功!");
System.out.println(emp);
}else {
System.out.println("登陆失败!");
}
CRUD.closeResource(conn,stmt,rs);
}
}
对于statement对象的缺陷,我们可以使用它的子类PreparedStatement,sql语句中我们就可以使用?表示需要的值,在使用PreparedStatement对象给?赋值
//获取连接
Connection conn = DBUtils.getConnection();
//创建sql脚本
String sql = "insert into employee (empname,emppwd,salary,birthday)" + "values (?,?,?,?)";
//创建PreparedStatement
PreparedStatement pstmt = conn.prepareStatement(sql);
//给sql语句的?号占位符赋值,下标从1开始
pstmt.setString(1,"Tom");
pstmt.setString(2,"1234");
pstmt.setDouble(3,5000);
pstmt.setDate(4,new Date(1234123L));
//执行SQL命令
int count = pstmt.executeUpdate();//返回影响的行数