Day 2 Java数据库设计与实现

String sql = "SELECT * FROM users WHERE username = ? AND password = ?"; try (Connection conn = DriverManager.getConnection(url, user, pass); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, username); pstmt.setString(2, password); try (ResultSet rs = pstmt.executeQuery()) { while (rs.next()) { // 处理结果 } } }

String sql = "INSERT INTO orders (product_id, quantity) VALUES (?, ?)"; try (Connection conn = dataSource.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) { for (Order order : orders) { pstmt.setInt(1, order.getProductId()); pstmt.setInt(2, order.getQuantity()); pstmt.addBatch(); // 添加到批处理 if (i % BATCH_SIZE == 0) { pstmt.executeBatch(); // 执行批处理 } } pstmt.executeBatch(); // 执行剩余批处理 }

spring: datasource: url: jdbc:mysql://localhost:3306/mydb username: root password: securepass driver-class-name: com.mysql.cj.jdbc.Driver hikari: connection-timeout: 30000 maximum-pool-size: 20 minimum-idle: 5 idle-timeout: 600000 max-lifetime: 1800000

@Configuration public class DruidConfig { @Bean public ServletRegistrationBean statViewServlet() { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); // 设置监控页面访问参数 Map params = new HashMap<>(); params.put("loginUsername", "admin"); params.put("loginPassword", "admin123"); params.put("allow", ""); // 允许所有访问 bean.setInitParameters(params); return bean; } }

<select id="findUsers" parameterType="map" resultType="User"> SELECT * FROM users <where> <if test="username != null"> AND username = #{username} </if> <if test="email != null"> AND email LIKE CONCAT(#{email}, '%') </if> <if test="statusList != null and statusList.size() > 0"> AND status IN <foreach item="status" collection="statusList" open="(" separator="," close=")"> #{status} </foreach> </if> </where> ORDER BY create_time DESC </select>

@Entity @Table(name = "employees") public class Employee { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "full_name", nullable = false, length = 100) private String fullName; @Column(unique = true) private String email; @Enumerated(EnumType.STRING) private Department department; @OneToMany(mappedBy = "employee", cascade = CascadeType.ALL) private List projects = new ArrayList<>(); // Getters and setters }

@Service public class OrderService { @Autowired private OrderRepository orderRepository; @Autowired private InventoryService inventoryService; @Transactional( propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, rollbackFor = {BusinessException.class, SQLException.class}, timeout = 30 ) public void placeOrder(Order order) { // 1. 创建订单 orderRepository.save(order); // 2. 扣减库存 inventoryService.reduceStock(order.getProductId(), order.getQuantity()); // 3. 其他业务操作... } }

# my.cnf配置 [mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 # 超过1秒记录 log_queries_not_using_indexes = ON

spring: shardingsphere: datasource: names: master, slave1, slave2 master: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://master-host:3306/db username: root password: masterpass slave1: # ...类似配置 slave2: # ...类似配置 rules: readwrite-splitting: data-sources: readwrite_ds: write-data-source-name: master read-data-source-names: slave1, slave2 load-balancer-name: round_robin load-balancers: round_robin: type: ROUND_ROBIN