1.向数据库中添加,删除,修改数据。
- Connection(数据库连接类)
- PreparedStatement(数据库操作类)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Demo01 {
public static void main(String[] args) {
String sql = "INSERT INTO employee_info(emp_name,emp_salary,emp_dept,id_card_no,phone_number)VALUES(?,?,?,?,?)";
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://数据库的IP地址/数据库名称?useSSL=false&serverTimezone=UCT",
"数据库用户名",
"数据库的密码");
PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)){
ps.setString(1, "诸葛亮");
ps.setDouble(2, 1235.14);
ps.setString(3, "行政部");
ps.setString(4, "612545123698745512");
ps.setString(5, "13354874152");
int rows = ps.executeUpdate();
System.out.println("改变行数为: " + rows);
ResultSet rs = ps.getGeneratedKeys();
if(rs.next()) {
System.out.println("添加的这条信息时自动生成的信息为:" + rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- 注意:插入数据的时候需要返回插入后自动生成信息的时候,可以使用getGeneratedKeys();方法,但是必须事先在构建PreparedStatement对象的时候对第二个参数声明Statement.RETURN_GENERATED_KEYS
2.查找数据库中的数据。
- Connection(数据库连接类)
- PreparedStatement(数据库操作类)
- ResultSet(数据存储类)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import com.apesource.deam02.Employee;
public class Demo02 {
@Override
public List<Employee> queryEmployeeListByDept(String dept) {
List<Employee> employeeList = new ArrayList<Employee>();
String sql = "SELECT emp_id,emp_name,emp_salary,emp_dept,id_card_no,phone_number FROM employee_info WHERE emp_dept like ?";
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://数据库的IP地址/数据库名称?useSSL=false&serverTimezone=UCT",
"数据库用户名",
"数据库的密码");
PreparedStatement ps = conn.prepareStatement(sql)){
ps.setString(1, dept);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Employee emp = new Employee(rs.getString(2), rs.getDouble(3), rs.getString(4), rs.getString(5), rs.getString(6));
emp.setEmployeeId(rs.getInt(1));
employeeList.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return employeeList;
}
}
3.批量添加数据,删除数据,修改数据。
- 批量操作指的是一次性选中多条语句执行。
- Connection(数据库连接类)
- PreparedStatement(数据库操作类)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import com.apesource.deam02.Employee;
public class Demo03 {
public static void instrtEmployee(List<Employee> emplist) {
String sql = "INSERT INTO employee_info(emp_name,emp_salary,emp_dept,id_card_no,phone_number)VALUES(?,?,?,?,?)";
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://数据库的IP地址/数据库名称?useSSL=false&serverTimezone=UCT",
"数据库用户名",
"数据库的密码");
PreparedStatement ps = conn.prepareStatement(sql)){
for (int i = 0; i < emplist.size(); i++) {
Employee employee = emplist.get(i);
ps.setString(1, employee.getEmployeeName());
ps.setDouble(2, employee.getSalary());
ps.setString(3, employee.getDept());
ps.setString(4, employee.getIdCardNo());
ps.setString(5, employee.getPhoneNumber());
ps.addBatch();
if((i+1)%6==0) {
ps.executeBatch();
ps.clearBatch();
}
}
int rows[] = ps.executeBatch();
System.out.println("改变行数: " + Arrays.toString(rows));
} catch (SQLException e) {
e.printStackTrace();
}
}
}