jdbc大量插入数据(prepareStatement)

PrepareStatement适用于较小规模的数据插入处理,大规模的使用Statement比较好(防止sql注入问题:可以使用字符串过滤)

DBUTtil.java:数据库连接类


/**
 * @version
 * @description
 */
package com.xasmall.txt;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 数据库连接类
 * @author 26248
 *
 */
public class DBUTtil {
    public static Connection jdbaload() {
        Properties prop=new Properties();
        Connection conn=null;
        try {
            prop.load(DBUTtil.class.getClassLoader().getResourceAsStream("config.properties"));
        } catch (IOException e1) {
            System.out.println("文件为读取成功!");
        }
        String url=prop.getProperty("url");
        String driver=prop.getProperty("driver");
        String user=prop.getProperty("user");
        String password=prop.getProperty("password");
        try {
             Class.forName(driver);
             conn=DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            System.out.println("未加载mysql驱动!");
        } catch (SQLException e) {
            System.out.println("未连接mysql!");
        }
        if(conn!=null) {
            return conn;
        }
        else {
            throw new Error("数据库连接错误!");
        }
    }
}


User.java:用户数据类


/**
 * @version
 * @description
 */
package com.xasmall.txt;

/**
 * @author 26248
 *
 */
public class User {
    private int id;
    private String name;
    private String academy;
    /**
     * @return the id
     */
    public int getId() {
        return id;
    }
    /**
     * @param id the id to set
     */
    public void setId(int id) {
        this.id = id;
    }
    /**
     * @return the name
     */
    public String getName() {
        return name;
    }
    /**
     * @param name the name to set
     */
    public void setName(String name) {
        this.name = name;
    }
    /**
     * @return the academy
     */
    public String getAcademy() {
        return academy;
    }
    /**
     * @param academy the academy to set
     */
    public void setAcademy(String academy) {
        this.academy = academy;
    }
}

DealTxt.java:处理文本数据类:
/**
 * @version
 * @description
 */
package com.xasmall.txt;

import java.io.BufferedReader;
import java.io.FileReader;
import java.util.ArrayList;

/**
 * 将读取到的每一行处理,得到用户数据
 * @author 26248
 *
 */
public class DealTxt {
    public static ArrayList<String> readerLine(String fileName) throws Exception {
        FileReader filed=new FileReader(fileName);
        ArrayList<String> list=new ArrayList<String>();
        BufferedReader bf=new BufferedReader(filed);
        String str=null;
        while((str=bf.readLine())!=null) {
            list.add(str);
        }
        bf.close();
        return list;
    }
    public static User dealTxt(String line) {
        User user=new User();
        String[] str=line.split(",");
        user.setId(Integer.parseInt(str[0]));
        user.setName(str[1]);
        user.setAcademy(str[2]);
        return user;
    }
}

UserDAO.java:数据库插入实现类:
/**
 * @version
 * @description
 */
package com.xasmall.txt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author 26248
 *
 */
public class UserDAO {
    public static void insert(Connection conn,ArrayList<User> list) {
        try {
            conn=DBUTtil.jdbaload();
            conn.setAutoCommit(false);
            String sql="insert into studentinfo(id,username,academy) value(?,?,?)";
            PreparedStatement psmt=conn.prepareStatement(sql);
            for(User user:list) {
                psmt.setObject(1, user.getId());
                psmt.setObject(2, user.getName());
                psmt.setObject(3, user.getAcademy());
                psmt.addBatch();
            }
            psmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("SQLException error!");
        }
    }
}

Test.java:测试类:
/**
 * @version
 * @description
 */
package com.xasmall.txt;

import java.sql.Connection;
import java.util.ArrayList;

/**
 * @author 26248
 *  
 */
public class Test {
    public static void main(String[] args) throws Exception {
        ArrayList<User> user=new ArrayList<User>();
        ArrayList<String> list=DealTxt.readerLine("src/data.txt");
        for(String line:list) {
            user.add(DealTxt.dealTxt(line));
        }
        for(User us:user) {
            System.out.println("user_id:-->"+us.getId()+"user_name:--->"+us.getName()+"user_academy:--->"+us.getAcademy());
        }
        Connection conn = null;
        UserDAO.insert(conn, user);
    }
}

文本数据:
config.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/?
user=root
password=?
data.txt:
1008611,张三,计算机学院
1008511,李四,计算机学院
1003233,元丰,计算机学院
784327,哈防护,计算机学院