以实际代码示例说明
方式:java方式,sql方式
方式一,java方式
package cn.com.cintel.yafs;
import lombok.extern.log4j.Log4j;
import org.junit.Test;
import java.sql.*;
import java.util.Random;
/*
导入jar包 .
JDBC 六步
1. 注册驱动.Class.forName("com.mysql.jdbc.Driver");
2. 获得连接.
DriverManager.getConnection(url, user, password)
url:表示你想要连接的数据库的地址 . jdbc:mysql://localhost:3306/banji
user:登录用户名 root
password:登录密码. root / 123
3. 获得语句执行者
4. 执行sql语句
5. 处理结果
6. 释放资源.
*/
@Log4j
public class Test_jdbc {
static String DRIVER_NAME="com.mysql.jdbc.Driver";
static String url="jdbc:mysql://192.168.2.207:3306/test_han";
static String user="root";
static String password="123456";
static int insert_count=6000000;
public static void main(String[] args) throws Exception {
// 1.注册驱动 2.获取连接
Class.forName(DRIVER_NAME);
Connection con = DriverManager.getConnection(url, user, password);
log.info(con);
System.out.println(con); // com.mysql.jdbc.JDBC4Connection@be8464
insert(con);
// query(con);
con.close();
}
public static void insert(Connection con) throws Exception {
//3.获取语句执行者
Statement stat = con.createStatement();
log.info("begin--");
// INSERT INTO `test_han`.`w_victim_warning_number` (
// `id`, `warning_called_number`, `warning_number`, `create_time`, `update_time`, `feedback_state`, `feedback_police`, `feedback_police_time`, `longitude`, `latitude`, `lac`, `ci`, `judgetype`, `judgemana`, `creator_type`)
// VALUES (
// '21700091', '16620803417', '2', '2019-01-24 08:56:57', '2019-02-13 21:31:24', '0', NULL, NULL, '109.24982', '18.58291', '58661', '29220', NULL, NULL, '6');
Random random = new Random();//+"++"+
for(int index=1;index<insert_count;index++){
int month =1+ random.nextInt(11);
int day =1+ random.nextInt(28);
String sql = "INSERT INTO `test_han`.`w_victim_warning_number` (" +
"`id`, `warning_called_number`, `warning_number`, `create_time`, `update_time`, " +
"`feedback_state`, `feedback_police`, `feedback_police_time`, `longitude`, `latitude`, `lac`, `ci`, `judgetype`, `judgemana`, `creator_type`) " +
"VALUES ('"+index+"', '"+index+"', '"+month +
"', '2019-"+month+"-"+day+" 08:00:57', " +
"'2019-"+month+"-"+day+" 08:56:57', " +
"'0', NULL, NULL, '109.24982', '18.58291', '58661', '29220', NULL, NULL, '6')"
;
//4.执行 sql 语句 定义sql //String sql1 = "insert update delelte "; // 都是使用executeUpdate() 去执行 .
int u = stat.executeUpdate(sql);
// System.out.print(u+" ");//执行sql完毕 1
if(index%60000==0){
log.info("已插入条数:"+index);
}
}
// 5. 处理 结果集 . // 6. 关闭资源 . 后开的先关 . 先开的后关 .
stat.close();
}
// private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
// private String user = "root";
// private String password = "123456";
@Test
public void insertBatch(){
Connection conn = null;
PreparedStatement pstm =null;
ResultSet rt = null;
String DRIVER_NAME="com.mysql.jdbc.Driver";
String url="jdbc:mysql://192.168.2.160:3307/test_big";
String user="root";
String password="cintel123";
int beginValue=100000;
int counts=100000;
/**条数-耗时
*10000-9896ms
*100000-87465ms
*
* */
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
// String sql = "INSERT INTO myTable values(?,?)";
String sql = "INSERT INTO table1 ( `name1`, `name2`, `name3`, `name4`, `name5`," +
"`name6`, `name7`, `name8`, `name9`, `name10`, `name11`, `name12`, `name13`, `name14`, `name15`," +
"`name16`, `name17`, `name18`, `name19`, `name20`, `name21`, `name22`, `name23`, `name24`) " +
"values(?,?,?,?,?," +
"?,?,?,?,?," +
"?,?,?,?,?," +
"?,?,?,?,?," +
"?,?,?,? )";
pstm = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
conn.setAutoCommit(false);
for (int count=counts+beginValue; beginValue <= count; beginValue++) {
// pstm.setInt(1, i);
pstm.setString(1,"name_1_"+beginValue);
pstm.setString(2,"name_2_"+beginValue);
pstm.setString(3,"name_3_"+beginValue);
pstm.setString(4,"name_4_"+beginValue);
pstm.setString(5,"name_5_"+beginValue);
pstm.setString(6,"name_6_"+beginValue);
pstm.setString(7,"name_7_"+beginValue);
pstm.setString(8,"name_8_"+beginValue);
pstm.setString(9, "name_9_"+beginValue);
pstm.setString(10, "name_10_"+beginValue);
pstm.setString(11, "name_11_"+beginValue);
pstm.setString(12, "name_12_"+beginValue);
pstm.setString(13, "name_13_"+beginValue);
pstm.setString(14, "name_14_"+beginValue);
pstm.setString(15, "name_15_"+beginValue);
pstm.setString(16, "name_16_"+beginValue);
pstm.setString(17, "name_17_"+beginValue);
pstm.setString(18, "name_18_"+beginValue);
pstm.setString(19, "name_19_"+beginValue);
pstm.setString(20, "name_20_"+beginValue);
pstm.setString(21, "name_21_"+beginValue);
pstm.setString(22, "name_22_"+beginValue);
pstm.setString(23, "name_23_"+beginValue);
pstm.setString(24, "name_24_"+beginValue);
pstm.addBatch();
}
pstm.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("用时:" + (endTime - startTime)+"ms");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
// INSERT INTO `test_big`.`table1` (`id`, `name1`, `name2`, `name3`, `name4`, `name5`,
// `name6`, `name7`, `name8`, `name9`, `name10`, `name11`, `name12`, `name13`, `name14`, `name15`,
// `name16`, `name17`, `name18`, `name19`, `name20`, `name21`, `name22`, `name23`, `name24`) VALUES
// (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
// NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
public static void query(Connection con) throws Exception {
//3.获取语句执行者
Statement stat = con.createStatement();
// select 查询操作 .
String sql2 = "SELECT * FROM student"; // 查询表的所有列.
ResultSet rs = stat.executeQuery(sql2); // executeQuery()执行 查询语句,返回的内容 ,被装在 ResultSet 结果集中
System.out.println(rs);
// 5.处理
while (rs.next()) { //判断 是否 有下个.
Object id = rs.getObject("id");
Object name = rs.getObject("sname");
Object sal = rs.getObject("salary");
System.out.println("学生id:" + id+"\t\t名字:" + name +"\t薪水:" + sal) ;
}
// boolean next = rs.next(); // next() 可以理解为 迭代器中 hashNext().
// System.out.println(next); // true 表示 有一行数据 . 4 科比 40 60 3组
// int int1 = rs.getInt(1);
// System.out.println(int1);
// String name = rs.getString(2);
// System.out.println(name);
//
// Object object = rs.getObject(3);
// System.out.println(object);
// 6.关闭
rs.close();
stat.close(); // 执行者
}
}
方式二:sql方式
使用navicat,mysql函数,mysql存储过程
CREATE TABLE `table1` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`name1` varchar(255) DEFAULT NULL,
`name2` varchar(255) DEFAULT NULL,
`name3` varchar(255) DEFAULT NULL,
`name4` varchar(255) DEFAULT NULL,
`name5` varchar(255) DEFAULT NULL,
`name6` varchar(255) DEFAULT NULL,
`name7` varchar(255) DEFAULT NULL,
`name8` varchar(255) DEFAULT NULL,
`name9` varchar(255) DEFAULT NULL,
`name10` varchar(255) DEFAULT NULL,
`name11` varchar(255) DEFAULT NULL,
`name12` varchar(255) DEFAULT NULL,
`name13` varchar(255) DEFAULT NULL,
`name14` varchar(255) DEFAULT NULL,
`name15` varchar(255) DEFAULT NULL,
`name16` varchar(255) DEFAULT NULL,
`name17` varchar(255) DEFAULT NULL,
`name18` varchar(255) DEFAULT NULL,
`name19` varchar(255) DEFAULT NULL,
`name20` varchar(255) DEFAULT NULL,
`name21` varchar(255) DEFAULT NULL,
`name22` varchar(255) DEFAULT NULL,
`name23` varchar(255) DEFAULT NULL,
`name24` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=210015 DEFAULT CHARSET=utf8;
CREATE DEFINER=`root`@`%` FUNCTION `rand_num`() RETURNS int(5)
BEGIN
#Routine body goes here...
DECLARE i INT DEFAULT 0;
SET i = FLOOR(RAND()*10000+RAND()*1000+RAND()*100+RAND()*10);
RETURN i;
END
CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(`in_1` int) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < in_1 DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END
CREATE DEFINER=`root`@`%` PROCEDURE `insert_table`(IN `in_1` int,IN `in_2` int)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
#INSERT INTO `test_big`.`test` (`id`, `name`) VALUES ((in_1+i) ,rand_string(8));
INSERT INTO `test_big`.`table1` (
#`id`,
`name1`, `name2`, `name3`, `name4`, `name5`,
`name6`, `name7`, `name8`, `name9`, `name10`,
`name11`, `name12`, `name13`, `name14`, `name15`,
`name16`, `name17`, `name18`, `name19`, `name20`,
`name21`, `name22`, `name23`, `name24`)
VALUES (
#(in_1+i),
CONCAT('name_',rand_string(6)),CONCAT('name_',rand_string(7)),CONCAT('name_',rand_string(8)),CONCAT('name_',rand_string(9)),CONCAT('name_',rand_string(10))
,CONCAT('name_',rand_string(6)),CONCAT('name_',rand_string(7)),CONCAT('name_',rand_string(8)),CONCAT('name_',rand_string(9)),CONCAT('name_',rand_string(10))
,CONCAT('name_',rand_string(6)),CONCAT('name_',rand_string(7)),CONCAT('name_',rand_string(8)),CONCAT('name_',rand_string(9)),CONCAT('name_',rand_string(10))
,CONCAT('name_',rand_string(6)),CONCAT('name_',rand_string(7)),CONCAT('name_',rand_string(8)),CONCAT('name_',rand_string(9)),CONCAT('name_',rand_string(10))
,CONCAT('name_',rand_string(6)),CONCAT('name_',rand_string(7)),CONCAT('name_',rand_string(8)),CONCAT('name_',rand_string(9))
);
UNTIL i = in_2
END REPEAT;
COMMIT;
END
时间: 01:18.25
Procedure executed successfully
受影响的行: 0
Parameters: IN `in_1` int,IN `in_2` int
110010,100000
Return values: 110010, 100000
时间: 13:18.39
Procedure executed successfully
受影响的行: 0
Parameters: IN `in_1` int,IN `in_2` int
110010,1000000
Return values: 110010, 1000000