一直出现以下错误:com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'EmployeeId' cannot be null
因为题目要求主键自增,类型是varchar,所以我用了触发器进行自增
begin
set new.employeeid=concat('E',lpad(((select substring(employeeid,2,3) from epoint_employee where employeeid=(select employeeid from epoint_employee order by employeeid desc limit 1))+1),3,0));
end
以下是java代码:
Test
Employee
EmployeeImpl
import java.sql.SQLException;
public class Test {
public static void main(String[] args) throws SQLException {
// TODO Auto-generated method stub
EmployeeImpl ei=new EmployeeImpl();
System.out.println(ei.save("E001", "123", "SWEET", 22, 4325));
// System.out.println(ei.save("E002", "321", "TINA", 21, 4000));
// System.out.println(ei.save("E003", "111", "JACK", 21, 4000));
// System.out.println(ei.save("E004", "222", "MARK", 21, 4000));
// System.out.println(ei.save("E005", "123", "NICK", 22, 4325));
// System.out.println(ei.delete("004"));
// System.out.println(ei.update("001", "22222", "ZS", 22, 4325));
// ei.find("001");
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class EmployeeImpl implements Employee {
public static String driver = "com.mysql.jdbc.Driver";
public static String url = "jdbc:mysql://localhost:3306/epoint?characterEncoding=utf8&useSSL=true";
public static String user = "sa";
public static String pwd = "11111";
public String EmployeeId;// 员工账号
public String Password; // 员工密码
public String Name; // 姓名
public int Age; // 年龄
public double Salary; // 工资
public Connection getConnection() {
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, user, pwd);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public boolean save(String employeeid, String password, String name, int age, double salary){
Connection con = new EmployeeImpl().getConnection();
PreparedStatement pst=null;
// boolean b=true;
try {
pst=con.prepareStatement("insert into Epoint_EMPLOYEE(EmployeeId,Password,Name,Age,Salary) values(?,?,?,?,?)");//?: 占位符
//占位符有几个就写几个,否则会报错
pst.setString(1, employeeid);
pst.setString(2, password);
pst.setString(3, name);
pst.setInt(4, age);
pst.setDouble(5, salary);
// b=pst.execute();//返回Boolean类型,false表示执行CUD语句,true为R
int i=pst.executeUpdate();//返回受影响的行数
System.out.println(i);
// System.out.println(pst.getUpdateCount());//打印了几行
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return true;
}
public boolean update(String employeeid, String password, String name, int age, double salary){
Connection con = new EmployeeImpl().getConnection();
PreparedStatement pst=null;
boolean b=true;
try {
pst=con.prepareStatement("update Epoint_EMPLOYEE set password=? where employeeid=?");//?: 占位符
//占位符有几个就写几个,否则会报错
pst.setString(1, password);
pst.setString(2, employeeid);
b=pst.execute();//返回Boolean类型,false表示执行CUD语句,true为R
// int i=pst.executeUpdate();//返回受影响的行数
// System.out.println(i);
// System.out.println(pst.getUpdateCount());//打印了几行
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return b;
}
public boolean delete(String employeeid) {
Connection con = new EmployeeImpl().getConnection();
PreparedStatement pst=null;
boolean b=true;
try {
pst=con.prepareStatement("delete from Epoint_EMPLOYEE where employeeid=?");
//占位符有几个就写几个,否则会报错
pst.setString(1, employeeid);
b=pst.execute();//返回Boolean类型,false表示执行CUD语句,true为R
// int i=pst.executeUpdate();//返回受影响的行数
// System.out.println(i);
// System.out.println(pst.getUpdateCount());//打印了几行
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return b;
}
public void find(String employeeid){
Connection con = new EmployeeImpl().getConnection();
ResultSet rs = null;
PreparedStatement pst=null;
try {
pst=con.prepareStatement("select * from Epoint_EMPLOYEE where employeeid=?");
//占位符有几个就写几个,否则会报错
pst.setString(1, employeeid);
rs = pst.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
System.out.println(rs.getInt(4));
System.out.println(rs.getDouble(5));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (rs != null && !rs.isClosed()) {
rs.close();
}
if (pst != null && !pst.isClosed()) {
pst.close();
}
if (con != null && !con.isClosed()) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
import java.sql.SQLException;
public interface Employee {
public boolean save(String employeeid, String password,String name, int age, double salary);
public boolean update(String employeeid, String password,String name, int age, double salary);
public boolean delete(String employeeid);
public void find (String employeeid);
}