CREATE DATABASE `mysqlTest` DEFAULT CHARACTER SET utf8;

CREATE TABLE `test` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `num_id` int(11) DEFAULT NULL,

  `name` varchar(20) NOT NULL,

  `f_name` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

package sql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JDBCDemo{

    public static void main(String[] args) throws SQLException{

        insertFunction();

        updateFunction();

        queryFunction();

    }

    public static void queryFunction() throws SQLException{

        Connection temconn = getCon();

        String sql = "SELECT id,f_name FROM test where id BETWEEN 2 AND 5 ";

        try(PreparedStatement stmt = temconn.prepareStatement(sql)){

            ResultSet rs = stmt.executeQuery();

            while(rs.next()){

                System.out.println(rs.getInt(1)+"\t"+rs.getString(2));

                //或者用列名做参数,可读性好,性能差一些

                System.out.println(rs.getInt("id")+"\t"+rs.getString("f_name"));

            }

        }catch (SQLException e) {

            e.printStackTrace();

            // TODO: handle exception

        }

        temconn.close();

    }

    public static void updateFunction() throws SQLException{

        Connection temconn = getCon();

        String sql = "UPDATE test SET f_name='update2' WHERE id BETWEEN 5 AND 10 ";

        try(PreparedStatement stmt = temconn.prepareStatement(sql)){

            int counts = stmt.executeUpdate();

            System.out.println(counts);

        }catch (SQLException e) {

            e.printStackTrace();

            // TODO: handle exception

        }

        temconn.close();

    }

    public static void insertFunction() throws SQLException{

        Connection temconn = getCon();

        try {

            //关闭自动提交,即开启事务

            temconn.setAutoCommit(false);

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

                int id = i;

                String sql = "INSERT INTO test (num_id,name,f_name) VALUES(?,?,?);";

                try(PreparedStatement stmt = temconn.prepareStatement(sql)){

                    stmt.setInt(1, id);

                    stmt.setString(2, "娜娜"+id);

                    stmt.setString(3, "f娜娜3"+id);

                    stmt.execute();

                }catch(Exception e){

                    e.printStackTrace();

                }

            }

            //事务提交

            temconn.commit();

        } catch (SQLException e1) {

            e1.printStackTrace();

            //发生异常则回滚

            temconn.rollback();

        }

        temconn.close();

    }

    public static Connection getCon(){

        Connection tmpconn = null;

        System.out.println("初始化时执行了public static Connection getCon()");

        try{

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

            String url = "jdbc:mysql://127.0.0.1:3306/mysqlTest?characterEncoding=UTF-8";

            String UserName = "root";

            String Password = "password";

            tmpconn = DriverManager.getConnection(url, UserName, Password);

//            tmpconn.setAutoCommit(false);

        }catch(Exception e){

            e.printStackTrace();

        }

        return tmpconn;

    }

}

package sql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Random;

import java.util.concurrent.LinkedBlockingQueue;

public class LearnJDBC implements Runnable{

    private static LinkedBlockingQueue<Connection> conQueue = null;

    //线程数

    private static int threadCounts = 3;

    // 每个线程的循环数

    private static int forNumber = 3;

    public static void main(String[] args) throws InterruptedException{

        System.out.println("---------------------导入开始"+getNowTime()+"--------------");

        createConnections(threadCounts);

        LearnJDBC st = new LearnJDBC();

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

            new Thread(st,"线程:"+i).start();

        }

    }

    public static void insertFunction(String threadName) throws InterruptedException{

        Connection temconn = conQueue.take();

        int threadNumber = Integer.parseInt(threadName.split(":")[1]);

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

            int id = i;

            String sql = "INSERT INTO test (num_id,name,f_name) values(?,?,?);";

            try(java.sql.PreparedStatement stmt = temconn.prepareStatement(sql)){

                stmt.setInt(1, id*threadNumber);

                stmt.setString(2, "娜娜"+id+":"+threadName);

                stmt.setString(3, "f娜娜"+id+":"+threadName);

                stmt.execute();

            }catch(Exception e){

                e.printStackTrace();

            }

        }

        conQueue.put(temconn);

        System.out.println(threadName+"---导入结束"+getNowTime()+"--------------");

    }

    @Override

    public void run() {

        try {

            insertFunction(Thread.currentThread().getName());

        } catch (InterruptedException e) {

            e.printStackTrace();

        }

    }

    //    产生一个简易的连接池

    public static void createConnections(int num){

        try {

            conQueue = getconQueue(num);

        } catch (InterruptedException e) {

            e.printStackTrace();

        }

    }

    public static LinkedBlockingQueue<Connection> getconQueue(int num) throws InterruptedException {

        LinkedBlockingQueue<Connection> temconQueue = new LinkedBlockingQueue<Connection>();

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

            temconQueue.put(getCon());

        }

        return temconQueue;

    }

    public static Connection getCon(){

        Connection tmpconn = null;

        System.out.println("初始化时执行了public static Connection getCon()");

        try{

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

            String url = "jdbc:mysql://127.0.0.1:3306/mysqlTest?characterEncoding=UTF-8";

            String UserName = "root";

            String Password = "password";

            tmpconn = DriverManager.getConnection(url, UserName, Password);

//            tmpconn.setAutoCommit(false);

        }catch(Exception e){

            e.printStackTrace();

        }

        return tmpconn;

    }

    public static String getNowTime(){

        return (new SimpleDateFormat("HH:mm:ss")).format(new Date());

    }

    public static int getRandomNumber(int range){

        int number = new Random().nextInt(range) + 1;

        return number;

    }

}