MySQL创建表答案_mysql系列教材 （四）- 创建表

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

public class Test {

List cs = new ArrayList();

int size;

public Test(int size) {

this.size = size;

init();

}

public void init() {

//这里恰恰不能使用try-with-resource的方式，因为这些连接都需要是"活"的，不要被自动关闭了

try {

Class.forName("com.mysql.jdbc.Driver");

for (int i = 0; i < size; i++) {

Connection c = DriverManager

.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");

cs.add(c);

}

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public synchronized Connection getConnection() {

while (cs.isEmpty()) {

try {

this.wait();

} catch (InterruptedException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

Connection c = cs.remove(0);

return c;

}

public synchronized void returnConnection(Connection c) {

cs.add(c);

this.notifyAll();

}

public static void main(String[] args) {

Thread thread=null;

long tic =System.currentTimeMillis();

List ts = new ArrayList<>();

for(int i=0;i<100;i++)

{

thread =new Thread(){

public void run(){

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

try (

Connection c = DriverManager.getConnection

("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",

"root", "admin");//a mistake may happen because of the limit connection that

//Mysql can offer!

Statement s = c.createStatement();

)

{

String sql = "insert into hero values"

+ "(null," + "'hero"+"'," + 313.0f + "," + 50 + ")";

s.execute(sql);

System.out.println("1 insert a data!");

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

};

thread.start();

ts.add(thread);

}

for(Thread t:ts){//Wait until the thread go to an end!!!

try{

t.join();

}catch(InterruptedException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

long toc=System.currentTimeMillis();

System.out.println("The traditional method has cost "+

(toc-tic)+" milliseconds!");

//*************

Test test=new Test(10);

test.init();

tic=System.currentTimeMillis();

List ts1 = new ArrayList<>();

for(int i=0;i<100;i++)

{

thread =new Thread(){

public void run()

{

Connection c=test.getConnection();

String statement="insert into hero values"

+ "(null," + "'hero"+"'," + 313.0f + "," + 50 + ")";

try(Statement st=c.createStatement()){

st.execute(statement.toString());

System.out.println("2 insert a data!");

}catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

test.returnConnection(c);

}

};

thread.start();

ts1.add(thread);

}

for (Thread t : ts1) {//Wait until the thread go to an end!!!

try {

t.join();

} catch (InterruptedException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

toc=System.currentTimeMillis();

System.out.println("The connection pool cost "+(toc-tic)+" milliseconds!");

}

/*

Test cp = new Test(3);

for (int i = 0; i < 100; i++) {

new WorkingThread("working thread" + i, cp).start();

}*/

}

class WorkingThread extends Thread {

private Test cp;

public WorkingThread(String name, Test cp) {

super(name);

this.cp = cp;

}

public void run() {

Connection c = cp.getConnection();

System.out.println(this.getName()+ ":\t 获取了一根连接，并开始工作" );

try (Statement st = c.createStatement()){

//模拟时耗１秒的数据库ＳＱＬ语句

Thread.sleep(1000);

st.execute("select * from hero");

} catch (SQLException | InterruptedException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

cp.returnConnection(c);

}

}