以批量创建表和记录为例,汇总JDBC操作多种数据库代码。
下述代码实现的功能是:往数据库中创建10000张表,每50张表插入记录。
1、JDBC连接操作Oracle数据库
package demo;
import org.apache.commons.lang.RandomStringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;
public class OracleDemo{
public static void main(String[] args) {
Connection c = null;
// 注册驱动
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
c = DriverManager.getConnection(
"jdbc:oracle:thin:@ip:port:xe", "userName", "password");
c.setAutoCommit(false);
PreparedStatement ps;
System.out.println("开始插入数据-" + new Date());
Long beginTime = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
// 创建表
ps = c.prepareStatement("create table CLOUD_000" + i + "(" +
"C_ID NUMBER PRIMARY KEY, " +
"C_NAME VARCHAR(255)" +
")");
ps.execute();
// 插入数据
if (i % 50 == 0) {
String sql = "insert into CLOUD_00" + i + " values (?, ?)";
ps = c.prepareStatement(sql);
for (int y = 10001; y <= 11001; y++) {
ps.setInt(1, y);
ps.setString(2, RandomStringUtils.randomAlphanumeric(20));
ps.addBatch();
if (y == 11001) {
ps.executeBatch();
c.commit();
ps.clearBatch();
System.out.println("数据总数:" + (i * 100));
}
}
ps.executeUpdate();
}
System.out.println("创建表数目:" + i);
ps.close();
}
System.out.println("插入完成-" + new Date());
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
} catch(Exception e) {
e.getStackTrace();
}
}
}
2、JDBC连接操作MySQL数据库
package demo;
import org.apache.commons.lang.RandomStringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;
public class MySQLDemo{
public static void main(String[] args) {
Connection c = null;
// 注册驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
c = DriverManager.getConnection(
"jdbc:mysql://ip:port/database", "userName", "password");
PreparedStatement ps;
System.out.println("开始插入数据-" + new Date());
Long beginTime = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
ps = c.prepareStatement("create table cloud_000" + i + "(" +
"c_id int primary key , " +
"c_name VARCHAR(255)" +
")");
ps.execute();
if (i % 50 == 0) {
ps = c.prepareStatement("insert into cloud_000" + i + " values (?, ?)");
ps.setInt(1, i);
ps.setString(2, RandomStringUtils.randomAlphanumeric(20));
ps.executeUpdate();
}
System.out.println("创建表数目:" + i);
ps.close();
}
System.out.println("插入完成-" + new Date());
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
} catch(Exception e) {
e.getStackTrace();
}
}
}
3、JDBC连接操作SQLServer数据库
package demo;
import org.apache.commons.lang.RandomStringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;
public class MSSQLDemo{
public static void main(String[] args) {
Connection c = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
c = DriverManager.getConnection(
"jdbc:sqlserver://ip:port;DatabaseName=database;", "username", "password");
PreparedStatement ps;
System.out.println("开始插入数据-" + new Date());
Long beginTime = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
ps = c.prepareStatement("create table test.cloud_000" + i + "(" +
"c_id int primary key , " +
"c_name VARCHAR(255)" +
")");
ps.execute();
if (i % 50 == 0) {
ps = c.prepareStatement("insert into test.cloud_000" + i + " values (?, ?)");
ps.setInt(1, i);
ps.setString(2, RandomStringUtils.randomAlphanumeric(20));
ps.executeUpdate();
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
}
System.out.println("创建表数目:" + i);
ps.close();
}
System.out.println("插入完成-" + new Date());
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
} catch (Exception e) {
e.getStackTrace();
System.out.println(e.getMessage());
}
}
}
4、JDBC连接操作PostgreSQL数据库
package demo;
import org.apache.commons.lang.RandomStringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;
public class PostgreDemo {
public static void main(String[] args) {
Connection c = null;
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
c = DriverManager.getConnection(
"jdbc:postgresql://ip:port/database", "username", "password");
PreparedStatement ps;
System.out.println("开始插入数据-" + new Date());
Long beginTime = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
ps = c.prepareStatement("create table test.cloud_000" + i + "(" +
"c_id int primary key , " +
"c_name VARCHAR(255)" +
")");
ps.execute();
if (i % 50 == 0) {
ps = c.prepareStatement("insert into test.cloud_000" + i + " values (?, ?)");
ps.setInt(1, i);
ps.setString(2, RandomStringUtils.randomAlphanumeric(20));
ps.executeUpdate();
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
}
System.out.println("创建表数目:" + i);
ps.close();
}
System.out.println("插入完成-" + new Date());
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
} catch (Exception e) {
e.getStackTrace();
System.out.println(e.getMessage());
}
}
}
4、Java连接操作Mongo数据库
package demo;
import com.mongodb.MongoClient;
import com.mongodb.MongoClientURI;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;
import java.util.Date;
public class MongoDemo {
public static void main(String[] args) {
MongoClientURI uri = new MongoClientURI(
"mongodb://userName:password@ip:port/?authSource=admin&database=db");
// 创建连接
MongoClient client = new MongoClient(uri);
// 连接数据库
MongoDatabase database = client.getDatabase("db");
System.out.println("开始插入数据-" + new Date());
Long beginTime = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
// 创建集合
database.createCollection("cloud_000" + i);
if (i % 50 == 0) {
MongoCollection<Document> collection = database.getCollection("cloud_000" + i);
// 生成数据
Document document = new Document("stuId", "1").append("stuName", "Jack");
// 插入数据
collection.insertOne(document);
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
}
System.out.println("创建表数目:" + i);
}
System.out.println("插入完成-" + new Date());
System.out.println("耗时:" + ((beginTime - System.currentTimeMillis()) / 1000) + "秒");
}
}