1.首先在项目中添加连接数据库的驱动
新建一个Project,点击File->Project Structure->Project Settings->Modules, 然后选择其中的Dependencies选项卡,然后点击右上角绿色的”+”号,选择”JARs or directories”, 选中你刚刚放到lib目录中的mysql-connector-java-8.0.11.jar,然后点击”OK”。
2.创建连接数据库类
public class DatabaseUtilImpl {
private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
private static final String USER = "root";
private static final String PASSWORD = "123456";
public static Connection getConnection() throws SQLException {
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}
}
3.创建用户操作类
通过调用数据库类接口,然后实现增删改查
public class CustomerDaoImpl implements CustomerDao {
private DatabaseUtilImpl databaseUtil;
public CustomerDaoImpl() {
databaseUtil = new DatabaseUtilImpl();
}
@Override
public void addCustomer(Customer customer) {
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = databaseUtil.getConnection();
String sql = "INSERT INTO customer (id, name, phone, address) VALUES (?, ?, ?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, customer.getId());
pstmt.setString(2, customer.getName());
pstmt.setString(3, customer.getAddress());
pstmt.setString(4, customer.getPhone());
pstmt.executeUpdate();
} catch (Exception e){
e.printStackTrace();
System.out.println("数据库连接失败!");
}
}
//通过id删除用户
@Override
public void deleteCustomer(int customerId) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = databaseUtil.getConnection();
String sql = "DELETE FROM customer WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, customerId);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void updateCustomer(Customer customer) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = databaseUtil.getConnection();
String sql = "UPDATE customer SET name = ?, phone = ?, address = ? WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, customer.getId());
pstmt.setString(2, customer.getName());
pstmt.setString(3, customer.getAddress());
pstmt.setString(4, customer.getPhone());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public Customer getCustomer(int customerId) {
Customer cus = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Customer> userList = new ArrayList<>();
try {
conn = databaseUtil.getConnection();
String sql = "SELECT * FROM customer WHERE id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, customerId);
rs = pstmt.executeQuery();
if (rs.next()) {
cus = new Customer();
cus.setId(rs.getInt("id"));
cus.setName(rs.getString("name"));
cus.setPhone(rs.getString("phone"));
cus.setAddress(rs.getString("address"));
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("查询用户失败!");
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return cus;
}
4.然后通过main主函数执行,不过一般不通过这种方式进行操作,仅供个人学习参考!!!
public static void main(String[] args) {
CustomerDao dao = new CustomerDaoImpl();
// Test insert
Customer customer = new Customer();
customer.setId(1);
customer.setName("John Doe");
customer.setPhone("555-5555");
customer.setAddress("123 Main St");
customer.setId(2);
customer.setName("John bob");
customer.setPhone("555-6666");
customer.setAddress("456 Main St");
dao.addCustomer(customer);
// Test update
customer.setName("Jane Smith");
dao.updateCustomer(customer);
// Test delete
dao.deleteCustomer(1);
// Test select
// 获取ID为1的客户
Customer customers = dao.getCustomer(2);
// 输出客户信息
System.out.println(customers);
}