jdbc大量插入数据(prepareStatement)

来源：互联网发布：php b2c商城系统二次开编辑：程序博客网时间：2024/05/16 01:21

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,哈防护,计算机学院

阅读全文

0 0