10种Java从数据库中取出数据的完整过程

从JDBC开始列出10种Java从数据库中取出数据的完整过程。每种方式都包含完整的代码示例。

---

1. 原生JDBC

使用JDBC直接操作数据库，手动管理连接和资源。

import java.sql.*;

public class JdbcExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "user";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM account")) {

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                float money = rs.getFloat("money");
                System.out.println("ID: " + id + ", Name: " + name + ", Money: " + money);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

2. Spring JDBC

使用Spring的JdbcTemplate简化JDBC操作。

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

public class SpringJdbcExample {
    public static void main(String[] args) {
        DataSource dataSource = new DriverManagerDataSource(
                "jdbc:mysql://localhost:3306/mydb", "user", "password");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        List<Map<String, Object>> accounts = jdbcTemplate.queryForList("SELECT * FROM account");
        for (Map<String, Object> account : accounts) {
            System.out.println("ID: " + account.get("id") +
                    ", Name: " + account.get("name") +
                    ", Money: " + account.get("money"));
        }
    }
}

---

3. MyBatis

使用MyBatis进行SQL映射。

（1）MyBatis配置文件（mybatis-config.xml）

<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
                <property name="username" value="user"/>
                <property name="password" value="password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="AccountMapper.xml"/>
    </mappers>
</configuration>

（2）Mapper文件（AccountMapper.xml）

<mapper namespace="AccountMapper">
    <select id="selectAll" resultType="map">
        SELECT * FROM account
    </select>
</mapper>

（3）Java代码

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class MyBatisExample {
    public static void main(String[] args) {
        InputStream inputStream = MyBatisExample.class.getResourceAsStream("/mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        try (SqlSession session = sqlSessionFactory.openSession()) {
            List<Map<String, Object>> accounts = session.selectList("AccountMapper.selectAll");
            for (Map<String, Object> account : accounts) {
                System.out.println("ID: " + account.get("id") +
                        ", Name: " + account.get("name") +
                        ", Money: " + account.get("money"));
            }
        }
    }
}

---

4. Hibernate

使用Hibernate进行ORM操作。

（1）Hibernate配置文件（hibernate.cfg.xml）

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mydb</property>
        <property name="hibernate.connection.username">user</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.show_sql">true</property>
        <mapping class="Account"/>
    </session-factory>
</hibernate-configuration>

（2）实体类（Account.java）

import javax.persistence.*;

@Entity
@Table(name = "account")
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String name;
    private float money;

    // Getters and Setters
}

（3）Java代码

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import java.util.List;

public class HibernateExample {
    public static void main(String[] args) {
        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
        try (Session session = sessionFactory.openSession()) {
            List<Account> accounts = session.createQuery("FROM Account", Account.class).list();
            for (Account account : accounts) {
                System.out.println("ID: " + account.getId() +
                        ", Name: " + account.getName() +
                        ", Money: " + account.getMoney());
            }
        }
    }
}

---

5. Spring Data JPA

使用Spring Data JPA简化数据库操作。

（1）实体类（Account.java）

import javax.persistence.*;

@Entity
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String name;
    private float money;

    // Getters and Setters
}

（2）Repository接口（AccountRepository.java）

import org.springframework.data.jpa.repository.JpaRepository;

public interface AccountRepository extends JpaRepository<Account, Integer> {
}

（3）Java代码

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@SpringBootApplication
public class SpringDataJpaExample {
    public static void main(String[] args) {
        SpringApplication.run(SpringDataJpaExample.class, args);
    }
}

@Service
class AccountService {
    @Autowired
    private AccountRepository accountRepository;

    public void printAccounts() {
        List<Account> accounts = accountRepository.findAll();
        for (Account account : accounts) {
            System.out.println("ID: " + account.getId() +
                    ", Name: " + account.getName() +
                    ", Money: " + account.getMoney());
        }
    }
}

---

6. Apache Commons DbUtils

使用DbUtils简化JDBC操作。

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.util.List;

public class DbUtilsExample {
    public static void main(String[] args) {
        DataSource dataSource = // 初始化数据源
        QueryRunner runner = new QueryRunner(dataSource);

        try {
            List<Account> accounts = runner.query("SELECT * FROM account",
                    new BeanListHandler<>(Account.class));
            for (Account account : accounts) {
                System.out.println("ID: " + account.getId() +
                        ", Name: " + account.getName() +
                        ", Money: " + account.getMoney());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

7. JOOQ

使用JOOQ进行类型安全的SQL操作。

import org.jooq.*;
import org.jooq.impl.DSL;

import java.sql.Connection;
import java.sql.DriverManager;

public class JooqExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "user";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            DSLContext create = DSL.using(conn, SQLDialect.MYSQL);
            Result<Record> result = create.select().from("account").fetch();

            for (Record record : result) {
                System.out.println("ID: " + record.get("id") +
                        ", Name: " + record.get("name") +
                        ", Money: " + record.get("money"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

8. Ebean

使用Ebean进行ORM操作。

import io.ebean.Ebean;
import io.ebean.EbeanServer;
import io.ebean.EbeanServerFactory;
import io.ebean.config.ServerConfig;

import java.util.List;

public class EbeanExample {
    public static void main(String[] args) {
        ServerConfig config = new ServerConfig();
        config.setDataSourceConfig(new DataSourceConfig()
                .setUrl("jdbc:mysql://localhost:3306/mydb")
                .setUsername("user")
                .setPassword("password"));
        EbeanServer server = EbeanServerFactory.create(config);

        List<Account> accounts = server.find(Account.class).findList();
        for (Account account : accounts) {
            System.out.println("ID: " + account.getId() +
                    ", Name: " + account.getName() +
                    ", Money: " + account.getMoney());
        }
    }
}

---

9. R2DBC

使用R2DBC进行响应式数据库操作。

import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;
import io.r2dbc.spi.Result;
import reactor.core.publisher.Flux;

import static io.r2dbc.spi.ConnectionFactoryOptions.*;

public class R2dbcExample {
    public static void main(String[] args) {
        ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
                .option(DRIVER, "mysql")
                .option(HOST, "localhost")
                .option(PORT, 3306)
                .option(USER, "user")
                .option(PASSWORD, "password")
                .option(DATABASE, "mydb")
                .build());

        Flux.from(connectionFactory.create())
                .flatMap(connection -> Flux.from(connection.createStatement("SELECT * FROM account").execute()))
                .flatMap(result -> result.map((row, rowMetadata) -> row.get("id") + ", " + row.get("name") + ", " + row.get("money")))
                .subscribe(System.out::println);
    }
}

---

10. JPA（Java Persistence API）

使用JPA进行ORM操作。

（1）实体类（Account.java）

import javax.persistence.*;

@Entity
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String name;
    private float money;

    // Getters and Setters
}

（2）Java代码

import javax.persistence.*;
import java.util.List;

public class JpaExample {
    public static void main(String[] args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("my-pu");
        EntityManager em = emf.createEntityManager();

        List<Account> accounts = em.createQuery("SELECT a FROM Account a", Account.class).getResultList();
        for (Account account : accounts) {
            System.out.println("ID: " + account.getId() +
                    ", Name: " + account.getName() +
                    ", Money: " + account.getMoney());
        }

        em.close();
        emf.close();
    }
}

---

总结

以上10种方式展示了从JDBC到高级ORM框架的完整数据查询过程。每种方式都有其适用场景：

• JDBC：最基础，适合需要直接控制SQL的场景。
• ORM框架（如Hibernate、JPA）：适合需要对象映射和简化开发的场景。
• 工具库（如Spring JDBC、MyBatis）：适合需要简化JDBC操作的场景。
• 响应式编程（如R2DBC）：适合高并发、高性能的现代应用。

根据项目需求选择合适的方式！