基于JDBC进行数据查询操作,分别实现顺序查询、条件查询、排序查询和模糊查询操作。数据库系统可选用Derby、Access、SQL Server、MySql等其中的一个建立数据库及其数据表。
时间: 2023-08-11 14:08:36 浏览: 85
好的,您的问题是如何基于JDBC进行数据查询操作,包括顺序查询、条件查询、排序查询和模糊查询操作。同时,您还需要建立一个数据库系统及其数据表。
首先,您需要下载并安装一个数据库系统,如Derby、Access、SQL Server、MySql等。我将以MySql为例进行说明。
1. 下载并安装MySql数据库系统。
2. 创建一个数据库和数据表。例如,我们创建一个名为“test”的数据库,并在其中创建一个名为“employee”的数据表,其中包含id、name、age和salary四个字段。
```sql
CREATE DATABASE test;
USE test;
CREATE TABLE employee (
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
age INT NOT NULL,
salary FLOAT NOT NULL,
PRIMARY KEY (id)
);
```
3. 在Java程序中连接MySql数据库。
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdbcDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
System.out.println("Database connected successfully...");
} catch (SQLException | ClassNotFoundException e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
```
4. 实现顺序查询。顺序查询即查询数据表中所有数据,可以使用SELECT语句实现。
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
System.out.println("Database connected successfully...");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employee");
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id")
+ ", Name: " + rs.getString("name")
+ ", Age: " + rs.getInt("age")
+ ", Salary: " + rs.getFloat("salary"));
}
} catch (SQLException | ClassNotFoundException e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
```
5. 实现条件查询。条件查询即根据某个条件查询符合条件的数据,可以使用WHERE子句实现。
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
System.out.println("Database connected successfully...");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employee WHERE age > 25");
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id")
+ ", Name: " + rs.getString("name")
+ ", Age: " + rs.getInt("age")
+ ", Salary: " + rs.getFloat("salary"));
}
} catch (SQLException | ClassNotFoundException e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
```
6. 实现排序查询。排序查询即根据某个字段对数据进行排序,可以使用ORDER BY子句实现。
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
System.out.println("Database connected successfully...");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employee ORDER BY salary DESC");
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id")
+ ", Name: " + rs.getString("name")
+ ", Age: " + rs.getInt("age")
+ ", Salary: " + rs.getFloat("salary"));
}
} catch (SQLException | ClassNotFoundException e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
```
7. 实现模糊查询。模糊查询即根据某个字段模糊匹配数据,可以使用LIKE子句实现。
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
System.out.println("Database connected successfully...");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employee WHERE name LIKE '%Tom%'");
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id")
+ ", Name: " + rs.getString("name")
+ ", Age: " + rs.getInt("age")
+ ", Salary: " + rs.getFloat("salary"));
}
} catch (SQLException | ClassNotFoundException e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
```
以上就是基于JDBC进行数据查询操作的实现过程。