1、分别采用基于JDBC-ODBC桥接器和数据库专用驱动程序建立数据库连接,请分别给出完整测试代码。数据库系统可选用SQL Server。 2.基于JDBC进行数据查询操作,分别实现顺序查询、条件查询、排序查询和模糊查询操作。数据库系统可选用SQL Server等其中的一个建立数据库及其数据表。
时间: 2024-02-26 12:53:44 浏览: 16
1. 基于JDBC-ODBC桥接器建立数据库连接的测试代码:
```
import java.sql.*;
public class JDBC_ODBC_Test {
public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:SQLServerDataSource", "username", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
System.out.println(rs.getString("column1") + " " + rs.getString("column2"));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
```
其中,SQLServerDataSource 是ODBC数据源名称,username 和 password 分别是连接数据库所需的用户名和密码。
2. 基于数据库专用驱动程序建立数据库连接的测试代码:
```
import java.sql.*;
public class DatabaseDriver_Test {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=mydatabase", "username", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
System.out.println(rs.getString("column1") + " " + rs.getString("column2"));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
```
其中,localhost:1433 是数据库服务器的地址和端口号,mydatabase 是数据库名称,username 和 password 分别是连接数据库所需的用户名和密码。
3. 数据查询操作示例代码:
(1)顺序查询:
```
import java.sql.*;
public class Sequential_Query_Test {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=mydatabase", "username", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
System.out.println(rs.getString("column1") + " " + rs.getString("column2"));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
```
(2)条件查询:
```
import java.sql.*;
public class Conditional_Query_Test {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=mydatabase", "username", "password");
PreparedStatement ps = con.prepareStatement("SELECT * FROM mytable WHERE column1=?");
ps.setString(1, "value1");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("column1") + " " + rs.getString("column2"));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
```
其中,value1 是要查询的条件。
(3)排序查询:
```
import java.sql.*;
public class Ordered_Query_Test {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=mydatabase", "username", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable ORDER BY column1");
while (rs.next()) {
System.out.println(rs.getString("column1") + " " + rs.getString("column2"));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
```
(4)模糊查询:
```
import java.sql.*;
public class Fuzzy_Query_Test {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=mydatabase", "username", "password");
PreparedStatement ps = con.prepareStatement("SELECT * FROM mytable WHERE column1 LIKE ?");
ps.setString(1, "%value%");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("column1") + " " + rs.getString("column2"));
}
con.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
```
其中,%value% 是要查询的模糊条件,% 表示任意字符。