连接数据库测试
连接数据库
以下JAVA代码显示如何连接到现有的数据库。
import java.sql.*;
public class TestConn{
public static void main(String[] args){
// 相关ip,port,database,user,password需进行替换
String url = "jdbc:postgresql://127.0.0.1:5432/vastbase";
String username = "test";
String password = "Aa123456";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username,password);
System.out.println("connect success!");
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
}
将JDBC驱动上传到脚本目录，脚本成功执行输出如下：
[root@localhost test]# ll
total 5452
-rw-r--r--. 1 root root 1744 Aug 9 03:17 TestConn.class
-rw-r--r--. 1 root root 1045 Aug 9 03:17 TestConn.java
-rw-r--r--. 1 root root 5570942 Aug 9 02:09 Vastbase-G100-2.0_2021072214.jar
[root@localhost test]# java -Djava.ext.dirs=. TestConn
connect success!
创建表
以下JAVA程序将用于在之前连接的数据库Vastbase中创建一个表：
import java.sql.*;
public class TestConn{
public static void main(String[] args){
String url = "jdbc:postgresql://127.0.0.1:5432/vastbase";
String username = "test";
String password = "Aa123456";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username,password);
System.out.println("connect success!");
String createSql = "CREATE TABLE COMPANY\n" +
" (ID INT PRIMARY KEY NOT NULL,\n" +
" NAME TEXT NOT NULL,\n" +
" AGE INT NOT NULL,\n" +
" ADDRESS CHAR(50),\n" +
" SALARY REAL)";
try (Statement stmt = connection.createStatement()) {
stmt.execute(createSql);
}
System.out.println("create table success!");
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
}
执行上述程序后，它将在Vastbase中创建一张company表：
[root@localhost test]# java -Djava.ext.dirs=. TestConn
connect success!
create table success!
INSERT操作
以下JAVA程序显示了如何在上述示例中创建的COMPANY表中创建/插入记录：
import java.sql.*;
public class TestConn{
public static void main(String[] args){
String url = "jdbc:postgresql://127.0.0.1:5432/vastbase";
String username = "test";
String password = "Aa123456";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username,password);
System.out.println("connect success!");
String insertSql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)\n" +
" VALUES (?,?,?,?,?)";
try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
pstmt.setInt(1, 1);
pstmt.setString(2, "Paul");
pstmt.setInt(3, 32);
pstmt.setString(4, "California");
pstmt.setFloat(5, 20000.00f);
pstmt.addBatch();
pstmt.setInt(1, 2);
pstmt.setString(2, "Allen");
pstmt.setInt(3, 25);
pstmt.setString(4, "Texas");
pstmt.setFloat(5, 15000.00f);
pstmt.addBatch();
pstmt.setInt(1, 3);
pstmt.setString(2, "Teddy");
pstmt.setInt(3, 23);
pstmt.setString(4, "Norway");
pstmt.setFloat(5, 20000.00f);
pstmt.addBatch();
pstmt.setInt(1, 4);
pstmt.setString(2, "Mark");
pstmt.setInt(3, 25);
pstmt.setString(4, "Rich-Mond");
pstmt.setFloat(5, 65000.00f);
pstmt.addBatch();
pstmt.executeBatch();
}
System.out.println("insert records success!");
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
}
执行以上程序后，它将在company表中插入给定的记录：
[root@localhost ~]# java -Djava.ext.dirs=. TestConn
connect success!
insert records success!
SELECT操作
以下JAVA程序显示了如何从上述示例中创建的COMPANY表中获取和显示记录：
import java.sql.*;
public class TestConn{
public static void main(String[] args){
String url = "jdbc:postgresql://127.0.0.1:5432/vastbase";
String username = "test";
String password = "Aa123456";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username,password);
System.out.println("connect success!");
String selectSql = "SELECT * FROM COMPANY";
try (Statement stmt = connection.createStatement()) {
try (ResultSet rs = stmt.executeQuery(selectSql)) {
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getInt(3)
+ "\t" + rs.getString(4) + "\t" + rs.getFloat(5));
}
}
}
System.out.println("select records success!");
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
}
脚本成功执行输出如下：
[root@localhost ~]# java -Djava.ext.dirs=. TestConn
connect success!
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
select records success!
UPDATE操作
JAVA代码显示了如何使用UPDATE语句来更新指定记录，然后从COMPANY表中获取和显示更新的记录：
import java.sql.*;
public class TestConn{
public static void main(String[] args){
String url = "jdbc:postgresql://127.0.0.1:5432/vastbase";
String username = "test";
String password = "Aa123456";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username,password);
System.out.println("connect success!");
String updateSql = "UPDATE COMPANY SET SALARY=? WHERE ID=?";
try (PreparedStatement pstmt = connection.prepareStatement(updateSql)) {
pstmt.setFloat(1, 25000.00f);
pstmt.setInt(2, 2);
pstmt.execute();
}
System.out.println("update records success!");
String selectSql = "SELECT * FROM COMPANY";
try (Statement stmt = connection.createStatement()) {
try (ResultSet rs = stmt.executeQuery(selectSql)) {
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getInt(3)
+ "\t" + rs.getString(4).trim() + "\t" + rs.getFloat(5));
}
}
}
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
}
脚本成功执行输出如下：
[root@localhost ~]# java -Djava.ext.dirs=. TestConn
connect success!
update records success!
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
2 Allen 25 Texas 25000.0
DELETE操作
JAVA代码显示了如何使用DELETE语句删除指定记录，然后从COMPANY表中获取并显示剩余的记录：
import java.sql.*;
public class TestConn{
public static void main(String[] args){
String url = "jdbc:postgresql://127.0.0.1:5432/vastbase";
String username = "test";
String password = "Aa123456";
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(url, username,password);
System.out.println("connect success!");
String deleteSql = "DELETE COMPANY WHERE ID=?";
try (PreparedStatement pstmt = connection.prepareStatement(deleteSql)) {
pstmt.setInt(1, 2);
pstmt.execute();
}
System.out.println("deleterecords success!");
String selectSql = "SELECT * FROM COMPANY";
try (Statement stmt = connection.createStatement()) {
try (ResultSet rs = stmt.executeQuery(selectSql)) {
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getInt(3)
+ "\t" + rs.getString(4).trim() + "\t" + rs.getFloat(5));
}
}
}
connection.close();
} catch (Exception e){
e.printStackTrace();
}
}
}
脚本成功执行输出如下：
[root@localhost ~]# java -Djava.ext.dirs=. TestConn
connect success!
delete records success!
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
