使用jdbc对mysql进行增删改查

建立数据库和数据表

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

简单的demo

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;
    }
}

 

 

多线程安全的插入demo

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;
    }

}