package com.cxz.test;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC_Test {
static Connection conn;
static Statement stmt;
static PreparedStatement pstmt;
public static void main(String[] args) {
JDBC_Test test = new JDBC_Test();
conn = test.getConnection();
test.insert(conn);
test.insert2(conn);
test.insert3(conn);
test.insert4(conn);
test.insert5(conn);
test.query(conn);
try {
conn.close();
} catch (SQLException e) {
System.out.println("Close connection failed: " + e.getMessage());
}
}
public void insert(Connection conn) {
String sql = "INSERT INTO tb1 (id, value) values (1, 'a')";
int count = -1;
try {
stmt = conn.createStatement();
count = stmt.executeUpdate(sql);
} catch (SQLException e) {
System.out.println("insert data failed: "+e.getMessage());
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
}
System.out.println("insert into table tb1 "+count+" items.");
}
public void insert2(Connection conn) {
String sql = "INSERT INTO tb1 (id, value) values (?, ?)";
//int count = -1;
int BATCH_SIZE = 10;
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < BATCH_SIZE; i++) {
pstmt.clearParameters();
pstmt.setInt(1, 2);
pstmt.setString(2, "b");
pstmt.execute();
}
//count = pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println("insert data failed: "+e.getMessage());
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
}
System.out.println("insert into table tb1 "+1+" items.");
}
public void insert3(Connection conn) {
String sql = "INSERT INTO tb1 (id, value) values (?, ?)";
int count = -1;
int COMMIT_SIZE = 10;
try {
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < COMMIT_SIZE; i++) {
pstmt.clearParameters();
pstmt.setInt(1, 3);
pstmt.setString(2, "c");
count = pstmt.executeUpdate();
if (i % COMMIT_SIZE == 0) {
conn.commit();
}
}
conn.commit();
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("insert data failed: "+e.getMessage());
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
}
System.out.println("insert into table tb1 "+count+" items.");
}
public void insert4(Connection conn) {
String sql = "INSERT INTO tb1 (id, value) values (?, ?)";
int BATCH_SIZE = 10;
int COMMIT_SIZE = 10;
int i = 0;
try {
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
for (i = 0; i < 20; i += BATCH_SIZE) {
pstmt.clearBatch();
for (int j = 0; j < BATCH_SIZE; j++) {
pstmt.setInt(1, 4);
pstmt.setString(2, "d");
pstmt.addBatch();
}
pstmt.executeBatch();
if ((i + BATCH_SIZE) % COMMIT_SIZE == 0) {
conn.commit();
}
}
conn.commit();
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("insert data failed: "+e.getMessage());
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
}
System.out.println("insert into table tb1 "+20+" items.");
}
public void insert5(Connection conn) {
String sql = "load data local infile '' into table tb1 fields terminated by '#'";
StringBuilder sb = new StringBuilder();
int COMMIT_SIZE = 10;
try {
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < 20; i++) {
sb.append(5+"#"+"e"+"\n");
if (i % COMMIT_SIZE == 0) {
InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
((com.mysql.jdbc.Statement)pstmt).setLocalInfileInputStream(is);
pstmt.execute();
conn.commit();
sb.setLength(0);
}
}
InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
((com.mysql.jdbc.Statement)pstmt).setLocalInfileInputStream(is);
pstmt.execute();
conn.commit();
sb.setLength(0);
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("insert data failed: "+e.getMessage());
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
}
System.out.println("insert into table tb1 "+20+" items.");
}
public void query(Connection conn) {
String sql = "select id, value from tb1";
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
System.out.println("The query results as follows:");
while(rs.next()) {
int id = rs.getInt("id");
String value = rs.getString("value");
System.out.println(id + "\t" + value);
}
} catch (SQLException e) {
System.out.println("query data failed: "+e.getMessage());
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
}
}
public Connection getConnection() {
Connection conn = null;
String conn_uri = "jdbc:mysql://192.168.1.10:3306/cxz_db";
String username = "cxz";
String passwd = "cxz123";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e) {
System.out.println("Load jdbc driver failed: " + e.getMessage());
}
try {
conn = DriverManager.getConnection(conn_uri, username, passwd);
}catch(SQLException e) {
System.out.println("Connect to mysql DB failed!");
System.out.println("SQLException: " + e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("VendorError: " + e.getErrorCode());
}
return conn;
}
}
此程序参考了blog:
http://www.cnblogs.com/end/archive/2012/10/25/2738818.html
编写Mysql JDBC程序导入数据到mysql
最新推荐文章于 2022-08-01 08:35:40 发布